Midterms - 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, lastName)
VALUES (62, 11, 'Halrpert');

Question 5

Syntax

Update

Answer 5

UPDATE Employee
SET phone = '0928 - 960 - 1234'
WHERE empId = 29;

Question 6

Syntax

Delete

Answer 6

DELETE FROM Employee
WHERE empId = 29;

Question 7

Syntax

Select

Answer 7

SELECT empName
FROM Employee
WHERE empId = 33;

Question 8

Identification

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

Answer 8

Result set

Question 9

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.

Answer 9

Correlated

Question 10

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.

Answer 10

Non-correlated

Question 11

Identification

Another way of combining data from multiple tables

Answer 11

Joins

Question 12

Syntax

Inner Join

Answer 12

SELECT empName, deptName
FROM Employee e INNER JOIN Department d ON e.deptId = d.deptId;

Question 13

Syntax

Left Outer Join

Answer 13

SELECT empName, deptName
FROM Employee e LEFT OUTER JOIN Department d ON e.deptId = d.deptId;

Question 14

Syntax

Right Outer Join

Answer 14

SELECT empName, deptName
FROM Employee e RIGHT OUTER JOIN Department d ON e.deptId = d.deptId;

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 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;

Question 17

Syntax

Union All

Answer 17

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;

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 length
CHECK (LENGHT(deptId) >=3);

Question 21

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

Answer 21

Inner Join

Question 22

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.

Answer 22

Left Outer Join

Question 23

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.

Answer 23

Right Outer Join

Question 24

Identification

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

Answer 24

Union

Question 25

Identification

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

Answer 25

Union All

Question 26

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

Answer 26

Full Outer Join

Question 27

Identification

is used to ensure that values in a column meet a specific condition before they are accepted into the database

Answer 27

Check Constraint

Question 28

Define

Result set

Answer 28

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

Question 29

Define

Correlated

Answer 29

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.

Question 30

Define

Non-correlated

Answer 30

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

Question 31

Define

Joins

Answer 31

Another way of combining data from multiple tables

Question 32

Define

Inner Join

Answer 32

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

Question 33

Define

Left Outer Join

Answer 33

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.

Question 34

Define

Right Outer Join

Answer 34

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.

Question 35

Define

Union

Answer 35

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

Question 36

Define

Union All

Answer 36

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

Question 37

Define

Full Outer Join

Answer 37

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

Question 38

Define

Check Constraint

Answer 38

is used to ensure that values in a column meet a specific condition before they are accepted into the database