Midterms - syntax. joins, checks Flashcards
Syntax
Adding Pk constraint to an existing table using ALTER
ALTER TABLE Employee ADD CONSTRAINT empPk PRIMARY KEY (empId);
Syntax
Composite Primary Key
ALTER TABLE Employee ADD CONSTRAINT empSkillPk PRIMARY KEY (empId, SkillId);
Syntax
Adding Fk constraint to existing table using ALTER
ALTER TABLE Employee ADD CONSTRAINT empFk FOREIGN KEY (deptId) REFERENCES Department (deptId);
Syntax
Insert
INSERT INTO Employee (empId, salaryCode, lastName) VALUES (62, 11, 'Halrpert');
Syntax
Update
UPDATE Employee SET phone = '0928 - 960 - 1234' WHERE empId = 29;
Syntax
Delete
DELETE FROM Employee WHERE empId = 29;
Syntax
Select
SELECT empName FROM Employee WHERE empId = 33;
Identification
A query pulls information from one or more relations and temporarily creates a new relation
Result set
Identification
Dependent on the outer query. References columns or relies on a value from the outer query. Is executed repeatedly, once for each row in the outer query.
Correlated
Identification
Independent of the outer query. Does not reference any value or columns from the outer query. Executed once and the result is used by the outer query.
Non-correlated
Identification
Another way of combining data from multiple tables
Joins
Syntax
Inner Join
SELECT empName, deptName FROM Employee e INNER JOIN Department d ON e.deptId = d.deptId;
Syntax
Left Outer Join
SELECT empName, deptName FROM Employee e LEFT OUTER JOIN Department d ON e.deptId = d.deptId;
Syntax
Right Outer Join
SELECT empName, deptName FROM Employee e RIGHT OUTER JOIN Department d ON e.deptId = d.deptId;
Syntax
Full Outer Join
SELECT empName, deptName FROM Employee e FULL OUTER JOIN Department d ON e.deptId = d.deptId;
Syntax
Union
SELECT e.empName, d.deptName FROM employee e LEFT OUTER JOIN department d ON e.deptId = d.deptId UNION SELECT e.empName, d.deptName FROM employee e RIGHT OUTER JOIN department d ON e.deptId = d.deptId;
Syntax
Union All
SELECT e.empName, d.deptName FROM employee e LEFT OUTER JOIN department d ON e.deptId = d.deptId UNION ALL SELECT e.empName, d.deptName FROM employee e RIGHT OUTER JOIN department d ON e.deptId = d.deptId;
Syntax
Alter and Drop
ALTER TABLE Employee DROP CONSTRAINT empFk;
Syntax
Deleting a Database
DROP TABLE Employee;
Syntax
Check Constraint
ALTER TABLE Employee
ADD CONSTRAINT length
CHECK (LENGHT(deptId) >=3);
Identification
Retrieves records that have matching values in both tables based on a specified condition, only the rows with matches in both tables are included in the result set
Inner Join
Identification
Returns all records from the left table and any matching records from the right table. If there’s no match, NULL values are returned for the right table’s columns.
Left Outer Join
Identification
Returns all records from the right table along with any matching records from the left table. If there’s no match, NULL values are returned for the left table’s columns.
Right Outer Join
Identification
combines the results of two or more SELECT queries into a single result set, removing duplicate rows
Union
Identification
combines the results of two or more SELECT queries into a single result set, including all duplicate rows
Union All
Identification
Returns all records from both tables, combining rows with matching pairs. If no match exists, the missing values in both tables are filled with NULL
Full Outer Join
Identification
is used to ensure that values in a column meet a specific condition before they are accepted into the database
Check Constraint
Define
Result set
A query pulls information from one or more relations and temporarily creates a new relation
Define
Correlated
Dependent on the outer query. References columns or relies on a value from the outer query. Is executed repeatedly, once for each row in the outer query.
Define
Non-correlated
Independent of the outer query. Does not reference any value or columns from the outer query. Executed once and the result is used by the outer query
Define
Joins
Another way of combining data from multiple tables
Define
Inner Join
Retrieves records that have matching values in both tables based on a specified condition, only the rows with matches in both tables are included in the result set
Define
Left Outer Join
Returns all records from the left table and any matching records from the right table. If there’s no match, NULL values are returned for the right table’s columns.
Define
Right Outer Join
Returns all records from the right table along with any matching records from the left table. If there’s no match, NULL values are returned for the left table’s columns.
Define
Union
combines the results of two or more SELECT queries into a single result set, removing duplicate rows
Define
Union All
combines the results of two or more SELECT queries into a single result set, including all duplicate rows
Define
Full Outer Join
Returns all records from both tables, combining rows with matching pairs. If no match exists, the missing values in both tables are filled with NULL
Define
Check Constraint
is used to ensure that values in a column meet a specific condition before they are accepted into the database
