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, lName)
Value (5, 1, ‘arianne’);
Syntax
Update
Update employee
Set lName = ‘Rivera’
Where empId = 5;
Syntax
DELETE
DELETE FROM employee
WHERE empId = 1;
Syntax
SELECT
SELECT lName
FROM employee
WHERE empId = 2;
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;
Identify:
A query pulls information from one or more relations and temporarily creates a new relation
Result Set
Identify:
Dependent on the outer query. References column or relies on a value from the outer query. Is executed repeatedly, once for each row in the outer query.
Correlated
Identify:
Independent of the outer query. Does not references any value or column from the outer query. Executed once and the result used by the outer query.
Non-correlated
Identify:
Another way of combining data from multiple tables
Joins
Syntax:
Full Outer Join
SELECT empName, deptName
FROM employee e
FULL OUTER JOIN department d
ON e.deptId = d.deptId;
Syntax:
Union
SELECT empName, deptName
FROM employee e
LEFT OUTER JOIN department d
ON e.deptId = d.deptId
UNION
SELECT empName, deptName
FROM employee e
RIGHT OUTER JOIN department d
ON e.deptId = d.deptId;
Syntax:
Union All
SELECT empName, deptName
FROM employee e
LEFT OUTER JOIN department d
ON e.deptId = d.deptId
UNION ALL
SELECT empName, 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 lenght
CHECK (LENGH(deptId) >=3);
Identify:
Retrieves record 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
Identify:
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 column
Left Outer Join
Identify:
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 column.
Right Outer Join
Combines the results of two or more SELECT queries into a single result set, removing duplicate rows
Union
