Deck 5: SQL

Question 1

STUDENT
Observe the table STUDENT:
STUDENT

$$\begin{array} { l c l l } \text { StudentID } & \text { SSN } & \text { Nane } & { \text { Class } } \\\hline D 111 & 111 - 11 - 1111 & \text { Connor } & \text { Freshman } \\D 222 & 222 - 22 - 2121 & \text { Trevor } & \text { Freshman } \\D 333 & 333 - 33 - 3333 & \text { Connor } & \text { Sophomore } \\D 444 & 444 - 44-4444 & \text { Parker } & \text { Sophomore } \\D 555 & 555 - 55 - 5555 & \text { Connor } & \text { Sophomore }\end{array}$$

This table will be used for the following questions citing table STUDENT.

-How many records will the following query on table STUDENT return?
SELECT * FROM student WHERE name LIKE '%or%';

A)0
B)2
C)3
D)4

Answer 1

4

Question 2

Which of the following statements would be used to change a value in a record of a table?

A)CREATE TABLE
B)ALTER TABLE
C)INSERT INTO
D)UPDATE

Answer 2

D

Question 3

What is the result of the following SQL query?
SELECT ename,esalary * 1.1 FROM employee;

A)For each employee,the query will display the name,the salary,and all other columns from the table employee
B)For each employee,the query will display the name,the salary,all other columns from the table employee,and the number 1.1
C)For each employee,the query will display the name and the salary increased by 10%
D)For each employee,the query will display the name and 10% of the salary

Answer 3

C

Question 4

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
$$\begin{array} { l l c l } \text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \\\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \\\text { C222 } & \text { Kain } & A 1 & \text { Bill } \\\text { C333 } & \text { Cole } & A 2 & \text { Amy } \\\text { C444 } & \text { Dorothy } & A 2 & \\\text { C555 } & \text { Anay } & A 3 & \text { Amy } \\\text { C666 } & \text { Tina } & A 3 & \text { Matt } \\\text { C777 } & \text { Christina } & A 4 & \text { Mike }\end{array}$$ AGENT
$$\begin{array} { l l l l l c } \text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearofHire } & \text { SupervisedBy } \\\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \\\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { Al } \\\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \\\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }\end{array}$$ AREA
$$\begin{array} { l l l } \text { ArealD } & \text { AreaName } & \text { AreaHQ } \\1 & \text { East } & \text { Boston } \\2 & \text { West } & \text { San Francisco } \\3 & \text { Central } & \text { Chicago }\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.



-What will be retrieved by the following query on the table CLIENT?
SELECT DISTINCT clientname FROM client WHERE clientspousename = 'Amy';

A)Name Amy twice
B)Name Amy
C)Name Cole
D)Names Andy and Cole

Question 5

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
$$\begin{array} { l l c l } \text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \\\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \\\text { C222 } & \text { Kain } & A 1 & \text { Bill } \\\text { C333 } & \text { Cole } & A 2 & \text { Amy } \\\text { C444 } & \text { Dorothy } & A 2 & \\\text { C555 } & \text { Anay } & A 3 & \text { Amy } \\\text { C666 } & \text { Tina } & A 3 & \text { Matt } \\\text { C777 } & \text { Christina } & A 4 & \text { Mike }\end{array}$$ AGENT
$$\begin{array} { l l l l l c } \text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearofHire } & \text { SupervisedBy } \\\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \\\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { Al } \\\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \\\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }\end{array}$$ AREA
$$\begin{array} { l l l } \text { ArealD } & \text { AreaName } & \text { AreaHQ } \\1 & \text { East } & \text { Boston } \\2 & \text { West } & \text { San Francisco } \\3 & \text { Central } & \text { Chicago }\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.



-How many records will be retrieved by the following query on table AGENT?
SELECT a.agentname,s.agentname
FROM agent a,agent s
WHERE a.supervisedby = s.agentid;

A)0
B)1
C)2
D)4

Question 6

What is the result of the following SQL statement?
UPDATE employee
SET salary = 50000
WHERE salary < 50000;

A)The statement sets the salary for each employee to below 50,000
B)The statement sets the salary of each employee to 50,000 and ensures that the salary cannot drop below 50,000
C)The statement increases salary to 50,000 for each employee whose salary is below 50,000
D)The statement sets the salary of each employee to 50,000

Question 7

Which of the following is an invalid SQL keyword?

A)Select
B)SE_LECT
C)selectT
D)SeLeCt

Question 8

STUDENT
Observe the table STUDENT:
STUDENT

$$\begin{array} { l c l l } \text { StudentID } & \text { SSN } & \text { Nane } & { \text { Class } } \\\hline D 111 & 111 - 11 - 1111 & \text { Connor } & \text { Freshman } \\D 222 & 222 - 22 - 2121 & \text { Trevor } & \text { Freshman } \\D 333 & 333 - 33 - 3333 & \text { Connor } & \text { Sophomore } \\D 444 & 444 - 44-4444 & \text { Parker } & \text { Sophomore } \\D 555 & 555 - 55 - 5555 & \text { Connor } & \text { Sophomore }\end{array}$$

This table will be used for the following questions citing table STUDENT.

-Which of the following queries on table STUDENT will return number 2 as a result?

A)SELECT COUNT(*)FROM student;
B)SELECT COUNT(name)FROM student;
C)SELECT COUNT(class)FROM student;
D)SELECT COUNT(DISTINCT class)FROM student;

Question 9

Which of the following statements would be used to drop a column from the table?

A)CREATE TABLE
B)ALTER TABLE
C)DROP TABLE
D)UPDATE

Question 10

What is the result of the following SQL query?
SELECT ename
FROM employee
WHERE esalary = (SELECT MAX(salary)FROM employee);

A)The query displays the number that represents how many employees share the highest salary
B)The query displays the salary of the employee (or employees)with the highest salary
C)The query displays the name and the salary of the employee (or employees)with the highest salary
D)The query displays the name of the employee (or employees)with the highest salary

Question 11

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
$$\begin{array} { l l c l } \text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \\\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \\\text { C222 } & \text { Kain } & A 1 & \text { Bill } \\\text { C333 } & \text { Cole } & A 2 & \text { Amy } \\\text { C444 } & \text { Dorothy } & A 2 & \\\text { C555 } & \text { Anay } & A 3 & \text { Amy } \\\text { C666 } & \text { Tina } & A 3 & \text { Matt } \\\text { C777 } & \text { Christina } & A 4 & \text { Mike }\end{array}$$ AGENT
$$\begin{array} { l l l l l c } \text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearofHire } & \text { SupervisedBy } \\\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \\\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { Al } \\\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \\\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }\end{array}$$ AREA
$$\begin{array} { l l l } \text { ArealD } & \text { AreaName } & \text { AreaHQ } \\1 & \text { East } & \text { Boston } \\2 & \text { West } & \text { San Francisco } \\3 & \text { Central } & \text { Chicago }\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.



-Which of the following sequences of CREATE TABLE statements for HAPPY INSURANCE database is in correct order?

A)CREATE TABLE area
CREATE TABLE client
CREATE TABLE agent

B)CREATE TABLE area
CREATE TABLE agent
CREATE TABLE client

C)CREATE TABLE client
CREATE TABLE agent
CREATE TABLE area

D)CREATE TABLE agent
CREATE TABLE client
CREATE TABLE area

Question 12

Which of the following is a part of a DDL statement?

A)CREATE
B)INSERT
C)DELETE
D)SELECT

Question 13

Which of the following is an invalid SQL statement?

A)SELECT
*
FROM
Employee;
B)SELECT *
FROM employee;
C)SELECT * FROM employee;
D)SELECT *,
FROM employee;

Question 14

STUDENT
Observe the table STUDENT:
STUDENT

$$\begin{array} { l c l l } \text { StudentID } & \text { SSN } & \text { Nane } & { \text { Class } } \\\hline D 111 & 111 - 11 - 1111 & \text { Connor } & \text { Freshman } \\D 222 & 222 - 22 - 2121 & \text { Trevor } & \text { Freshman } \\D 333 & 333 - 33 - 3333 & \text { Connor } & \text { Sophomore } \\D 444 & 444 - 44-4444 & \text { Parker } & \text { Sophomore } \\D 555 & 555 - 55 - 5555 & \text { Connor } & \text { Sophomore }\end{array}$$

This table will be used for the following questions citing table STUDENT.

-Which of the following queries on table STUDENT will return three values as a result?

A)SELECT name FROM student WHERE class != 'Sophomore';
B)SELECT name FROM student;
C)SELECT DISTINCT name FROM student;
D)SELECT class FROM student;

Question 15

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
$$\begin{array} { l l c l } \text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \\\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \\\text { C222 } & \text { Kain } & A 1 & \text { Bill } \\\text { C333 } & \text { Cole } & A 2 & \text { Amy } \\\text { C444 } & \text { Dorothy } & A 2 & \\\text { C555 } & \text { Anay } & A 3 & \text { Amy } \\\text { C666 } & \text { Tina } & A 3 & \text { Matt } \\\text { C777 } & \text { Christina } & A 4 & \text { Mike }\end{array}$$ AGENT
$$\begin{array} { l l l l l c } \text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearofHire } & \text { SupervisedBy } \\\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \\\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { Al } \\\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \\\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }\end{array}$$ AREA
$$\begin{array} { l l l } \text { ArealD } & \text { AreaName } & \text { AreaHQ } \\1 & \text { East } & \text { Boston } \\2 & \text { West } & \text { San Francisco } \\3 & \text { Central } & \text { Chicago }\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.



-How many records will be retrieved by the following query on tables AGENT and AREA?
SELECT *
FROM agent ag,area a
WHERE ag.agentarea = a.areaid;

A)0
B)3
C)4
D)12

Question 16

STUDENT
Observe the table STUDENT:
STUDENT

$$\begin{array} { l c l l } \text { StudentID } & \text { SSN } & \text { Nane } & { \text { Class } } \\\hline D 111 & 111 - 11 - 1111 & \text { Connor } & \text { Freshman } \\D 222 & 222 - 22 - 2121 & \text { Trevor } & \text { Freshman } \\D 333 & 333 - 33 - 3333 & \text { Connor } & \text { Sophomore } \\D 444 & 444 - 44-4444 & \text { Parker } & \text { Sophomore } \\D 555 & 555 - 55 - 5555 & \text { Connor } & \text { Sophomore }\end{array}$$

This table will be used for the following questions citing table STUDENT.

-What will be the result of the following query on table STUDENT?
SELECT * from STUDENT;

A)The entire relation STUDENT will be displayed
B)StudentID column of the relation STUDENT will be displayed
C)First record of the relation STUDENT will be displayed
D)Last record of the relation STUDENT will be displayed

Question 17

STUDENT
Observe the table STUDENT:
STUDENT

$$\begin{array} { l c l l } \text { StudentID } & \text { SSN } & \text { Nane } & { \text { Class } } \\\hline D 111 & 111 - 11 - 1111 & \text { Connor } & \text { Freshman } \\D 222 & 222 - 22 - 2121 & \text { Trevor } & \text { Freshman } \\D 333 & 333 - 33 - 3333 & \text { Connor } & \text { Sophomore } \\D 444 & 444 - 44-4444 & \text { Parker } & \text { Sophomore } \\D 555 & 555 - 55 - 5555 & \text { Connor } & \text { Sophomore }\end{array}$$

This table will be used for the following questions citing table STUDENT.

-What will be the result of the following query on table STUDENT?
SELECT COUNT( *)FROM student GROUP BY class;

A)Number 5
B)Number 2
C)Numbers 2 and 3
D)Numbers 1 and 1

Question 18

Which of the following is a part of a DML statement?

A)CREATE
B)ALTER
C)DROP
D)SELECT

Question 19

STUDENT
Observe the table STUDENT:
STUDENT

$$\begin{array} { l c l l } \text { StudentID } & \text { SSN } & \text { Nane } & { \text { Class } } \\\hline D 111 & 111 - 11 - 1111 & \text { Connor } & \text { Freshman } \\D 222 & 222 - 22 - 2121 & \text { Trevor } & \text { Freshman } \\D 333 & 333 - 33 - 3333 & \text { Connor } & \text { Sophomore } \\D 444 & 444 - 44-4444 & \text { Parker } & \text { Sophomore } \\D 555 & 555 - 55 - 5555 & \text { Connor } & \text { Sophomore }\end{array}$$

This table will be used for the following questions citing table STUDENT.

-What will be the result of the following query on table STUDENT?
SELECT COUNT( *)FROM student GROUP BY class HAVING COUNT(*)<3;

A)Number 5
B)Number 2
C)Numbers 2 and 3
D)Numbers 1 and 1

Question 20

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
$$\begin{array} { l l c l } \text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \\\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \\\text { C222 } & \text { Kain } & A 1 & \text { Bill } \\\text { C333 } & \text { Cole } & A 2 & \text { Amy } \\\text { C444 } & \text { Dorothy } & A 2 & \\\text { C555 } & \text { Anay } & A 3 & \text { Amy } \\\text { C666 } & \text { Tina } & A 3 & \text { Matt } \\\text { C777 } & \text { Christina } & A 4 & \text { Mike }\end{array}$$ AGENT
$$\begin{array} { l l l l l c } \text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearofHire } & \text { SupervisedBy } \\\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \\\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { Al } \\\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \\\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }\end{array}$$ AREA
$$\begin{array} { l l l } \text { ArealD } & \text { AreaName } & \text { AreaHQ } \\1 & \text { East } & \text { Boston } \\2 & \text { West } & \text { San Francisco } \\3 & \text { Central } & \text { Chicago }\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.



-Which of the following sequences of DROP TABLE statements for HAPPY INSURANCE database is in correct order?

A)DROP TABLE area
DROP TABLE client
DROP TABLE agent

B)DROP TABLE area
DROP TABLE agent
DROP TABLE client

C)DROP TABLE client
DROP TABLE agent
DROP TABLE area

D)DROP TABLE agent
DROP TABLE client
DROP TABLE area

Question 21

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return 4 values:
SELECT DISTINCT type FROM vehicle;

Question 22

What is the purpose of DROP TABLE command?

Question 23

What is the purpose of DDL statements?

Question 24

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return 4 values:
SELECT make FROM vehicle;

Question 25

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return two numbers:
SELECT COUNT(*)from vehicle GROUP BY ownername;

Question 26

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return 2 values:
SELECT DISTINCT ownerid,ownername FROM vehicle;

Question 27

What is the purpose of a ";" (semicolon)in the SQL statement?

Question 28

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return 4 values:
SELECT vehicleid FROM vehicle;

Question 29

A query retrieving the data from one relation can be used to populate another relation.

Question 30

What is the purpose of the CREATE TABLE command?

Question 31

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return two numbers:
SELECT COUNT(*)from vehicle GROUP BY ownerid;

Question 32

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return 4 values:
SELECT ownerid FROM vehicle;

Question 33

What is the purpose of DML statements?

Question 34

TCL commands facilitate the process of data access control.

Question 35

DCL commands are used to manage database transactions.

Question 36

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return 2 values:
SELECT DISTINCT ownername FROM vehicle;

Question 37

It is possible to join two tables without joining a foreign key column in one table with a primary key column in another table.

Question 38

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return 4 values:
SELECT type FROM vehicle;

Question 39

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return two numbers:
SELECT COUNT(*)from vehicle GROUP BY ownername HAVING COUNT(*)> 1;

Question 40

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE
$$\begin{array} { l l l c l } \text { VehiclelD } & \text { Type } & \text { Make } & \text { OwnerID } & \text { OwnerName } \\111 & \text { Car } & \text { Ford } & O 1 & \text { Pam } \\222 & \text { Car } & \text { Ford } & O2 & \text { Pat } \\333 & \text { Thuck } & \text { Honda } & O 3 & \text { Pan } \\444 & \text { Car } & \text { Honda } & \text { O1 } & \text { Pan }\end{array}$$

This table will be used for the following questions citing table VEHICLE.

-The following query on table VEHICLE will return one number:
SELECT COUNT(*)from vehicle;

Question 41

What is an outer query?

Question 42

What is the purpose of the HAVING keyword?

Question 43

When are two sets of columns union compatible?

Question 44

What is a view?

Question 45

In which case will the result of the statement in the following form
COUNT(column_name)FROM table
not be equal to the number of records in the table?

Question 46

What is the purpose of the GROUP BY keyword?

Question 47

What is the purpose of the DISTINCT keyword?

Question 48

What is the purpose of the ORDER BY keyword?

Question 49

What is determined by the WHERE condition in the SELECT statement?

Question 50

What is the purpose of the UPDATE command in SQL?

Question 51

When does a circular foreign key dependency occur?

Question 52

What is the purpose of the ALTER TABLE command?

Question 53

List the SQL aggregate functions.

Question 54

What is the purpose of the DELETE command in SQL?

Question 55

What is a self-JOIN?

Question 56

List the SQL set operators.

Question 57

What is the purpose of the JOIN operation?

Question 58

Why is the following query invalid:
SELECT productname,COUNT (*)
FROM PRODUCT;

Question 59

What is a nested query?

Question 60

What is the purpose of the LIKE keyword?

Question 61

STUDENT
Observe the table STUDENT:
STUDENT

Write an SQL query on the table STUDENT that lists records (all columns)for all freshman students.

Question 62

STUDENT
Observe the table STUDENT:
STUDENT

Write an SQL query on the table STUDENT that for each class lists the class and the number of students in the class.

Question 63

STUDENT
Observe the table STUDENT:
STUDENT

Write an SQL query on the table STUDENT that lists the student ID for each student whose name is Connor.

Question 64

When can the NOT operator be used?

Question 65

What is a correlated query and how is it used in conjunction with the EXISTS operator?

Question 66

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
AGENT
AREA

Show the CREATE TABLE statements for the table AGENT (assume that non-primary key columns can be optional. )

Question 67

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
AGENT
AREA

Show the CREATE TABLE statements for the table AREA (assume that non-primary key columns can be optional. )

Question 68

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE

Write the SQL query on the table VEHICLE that lists the owner ID and owner name for each owner who owns a Ford vehicle or any type of truck.

Question 69

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
AGENT
AREA

Show the INSERT INTO statements for the table AREA.

Question 70

When is the IS NULL comparison used?

Question 71

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE

Write the SQL query on the table VEHICLE that lists once the owner ID and owner name of each owner.

Question 72

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE

Show the INSERT INTO statements for the table VEHICLE.

Question 73

What is an OUTER JOIN?

Question 74

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE

Write the SQL query on the table VEHICLE that lists for each owner the OwnerID,OwnerName,and the number of vehicles each owner owns,sorted by the number of vehicles each owner owns (in ascending order. )

Question 75

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
AGENT
AREA

Show the CREATE TABLE statements for the table CLIENT (assume that non-primary key columns can be optional. )

Question 76

STUDENT
Observe the table STUDENT:
STUDENT

Show the INSERT INTO statements for the table STUDENT.

Question 77

List the three variations of OUTER JOIN?

Question 78

STUDENT
Observe the table STUDENT:
STUDENT

Show the CREATE TABLE statements for the table STUDENT (assume that non-primary keys are optional. )

Question 79

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE

Show the CREATE TABLE statements for the table VEHICLE (assume that non-primary key columns can be optional. )

Question 80

VEHICLE
Observe the table VEHICLE that contains the following four records:
VEHICLE

Write the SQL query on the table VEHICLE that lists the owner ID and owner name for each owner who owns a Ford vehicle.