Joining Tables and SQL Constraints
JOINING TABLES:
(i) Joins between tables are usually based on primary / foreign keys.
(ii) Make sure joins between all tables in the from clause exist.
(iii) List joins between tables before other selection elements.
(a) Left outer join keyword : It returns all rows from the left table (table 1), with the matching rows in the right table (table 2). The result is NULL in the right side when there is no match.
(b) Right outer join keyword : It returns all rows from the right table (table 2) , with the matching rows in the left table (table 1). The result is NULL in the left side when there is no match.
(c) Full outer join keyword : It returns all rows from the left table (table 1) and from the right table (table 2). It combines the result of both LEFT and RIGHT joins.
SQL constraints:
(i) SQL constraints are used to specify rules for the data in a table.
(ii) If there is any violation between the constraint and the data action,the action is aborted by the constraint.
(ii) Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).
(iv) In SQL, we have the following constraints:
(a) NOT NULL : Indicates that a column cannot store NULL value.It enforces a field to always contain a value.This means that one cannot insert a new record , or update a record insert a new record, or update a record without adding a value to this field.
(b) UNIQUE :
(d) FOREIGN KEY : Ensures the referential integrity of the data in one table to match values in another table.
(e) CHECK : Ensures that the value in a column meets a specific condition.It is used to limit the value range that can placed in a column.
(f) DEFAULT : Specifies a default value when specified none for this column.
ALTER TABLE statement:
(i) It is used to add , delete , or modify columns in an existing table.
(ii) Syntax:
(a) To add a column in a table:
ALTER TABLE table_name
ADD column_name datatype
(b) To delete a column from a table:
ALTER TABLE table_name
DROP COLUMN column_name
(i) Joins between tables are usually based on primary / foreign keys.
(ii) Make sure joins between all tables in the from clause exist.
(iii) List joins between tables before other selection elements.
(a) Left outer join keyword : It returns all rows from the left table (table 1), with the matching rows in the right table (table 2). The result is NULL in the right side when there is no match.
SQL constraints:
(i) SQL constraints are used to specify rules for the data in a table.
(ii) If there is any violation between the constraint and the data action,the action is aborted by the constraint.
(ii) Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).
(iv) In SQL, we have the following constraints:
(a) NOT NULL : Indicates that a column cannot store NULL value.It enforces a field to always contain a value.This means that one cannot insert a new record , or update a record insert a new record, or update a record without adding a value to this field.
(b) UNIQUE :
- Ensures that each row for a column must have unique value.
- The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
- A PRIMARY KEY constraints automatically has a UNIQUE constraints defined on it.
(d) FOREIGN KEY : Ensures the referential integrity of the data in one table to match values in another table.
(e) CHECK : Ensures that the value in a column meets a specific condition.It is used to limit the value range that can placed in a column.
(f) DEFAULT : Specifies a default value when specified none for this column.
ALTER TABLE statement:
(i) It is used to add , delete , or modify columns in an existing table.
(ii) Syntax:
(a) To add a column in a table:
ALTER TABLE table_name
ADD column_name datatype
(b) To delete a column from a table:
ALTER TABLE table_name
DROP COLUMN column_name
No comments