Syntax.Joins, Checks

Question 1

Syntax

Adding Pk constraint to an existing table using ALTER

Answer 1

ALTER TABLE employee
ADD CONSTRAINT empPk PRIMARY KEY
(empId);

Question 2

Syntax

Composite Primary Key

Answer 2

ALTER TABLE employee
ADD CONSTRAINT empSkillPk
PRIMARY KEY(empId, skillId);

Question 3

Syntax

Adding Fk Constraint to existing table using ALTER

Answer 3

ALTER TABLE employee
ADD CONSTRAINT empFk FOREIGN KEY
(deptId) REFERENCES department (deptId);

Question 4

Syntax

Insert

Answer 4

INSERT INTO employee(empId, salaryCode, lName)

Value (5, 1, ‘arianne’);

Question 5

Syntax

Update

Answer 5

Update employee
Set lName = ‘Rivera’
Where empId = 5;

Question 6

Syntax

DELETE

Answer 6

DELETE FROM employee
WHERE empId = 1;

Question 7

Syntax

SELECT

Answer 7

SELECT lName
FROM employee
WHERE empId = 2;

Question 8

Syntax

Inner Join

Answer 8

SELECT empName, deptName
FROM employee e INNER JOIN department d
ON e.deptId = d.deptId;

Question 9

Syntax

Left Outer Join

Answer 9

SELECT empName, deptName
FROM employee e LEFT OUTER JOIN department d
ON e.deptId = d.deptId;

Question 10

Syntax:

Right Outer Join

Answer 10

SELECT empName, deptName
FROM employee e
RIGHT OUTER JOIN department d
ON e.deptId = d.deptId;

Question 11

Identify:

A query pulls information from one or more relations and temporarily creates a new relation

Answer 11

Result Set

Question 12

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.

Answer 12

Correlated

Question 13

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.

Answer 13

Non-correlated

Question 14

Identify:

Another way of combining data from multiple tables

Answer 14

Joins

Question 15

Syntax:

Full Outer Join

Answer 15

SELECT empName, deptName
FROM employee e
FULL OUTER JOIN department d
ON e.deptId = d.deptId;

Question 16

Syntax:

Union

Answer 16

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;

Question 17

Syntax:

Union All

Answer 17

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

Question 18

Syntax:

Alter and Drop

Answer 18

ALTER TABLE employee DROP
CONSTRAINT empFk;

Question 19

Syntax:

Deleting a Database

Answer 19

DROP TABLE employee;

Question 20

Syntax:

Check Constraint

Answer 20

ALTER TABLE employee
ADD CONSTRAINT lenght
CHECK (LENGH(deptId) >=3);

Question 21

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

Answer 21

Inner Join

Question 22

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

Answer 22

Left Outer Join

Question 23

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.

Answer 23

Right Outer Join

Question 24

Combines the results of two or more SELECT queries into a single result set, removing duplicate rows

Answer 24

Union

Question 25

Combines the results of two or more SELECT queries into a single result set, including all duplicate rows.

Answer 25

Union All