STUDENT

Observe the table STUDENT:

$1.$

How many records will the following query on table STUDENT return?

SELECT $*$ FROM student WHERE name LIKE $\text{'}\%$or$\%\text{'}$;

A$)0$

B$)2$

C$)3$

D$)4$

$2.$

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$

StudentID SSN Name Class

D$111111-11-1111$ Connor Freshman

D$222222-22-2222$ Trevor Freshman

D$333333-33-3333$ Connor Sophomore

D$444444-44-4444$ Parker Sophomore

D$555555-55-5555$ Connor Sophomore

Created by Deepak Agrawal

HAPPY INSURANCE

Observe the HAPPY INSURANCE DB:

CLIENT

AGENT

AREA

$3.$

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

ClientID ClientName ClientAgent ClientSpouseName

C$111$ Tom A$1$ Jenny

C$222$ Karin A$1$ Bill

C$333$ Cole A$2$ Amy

C$444$ Dorothy A$2$

C$555$ Andy A$3$ Amy

C$666$ Tina A$3$ Matt

C$777$ Christina A$4$ Mike

AgentID AgentName AgentArea AgentRating AgentYearOf

Hire

SupervisedB

y

A$1$ Kate $11011990$

A$2$ Amy $2922009$ A$1$

A$3$ Luke $31001992$

A$4$ James $3902010$ A$3$

AreaID AreaName AreaHQ

$1$ East Boston

$2$ West San Francisco

$3$ Central Chicago

Created by Deepak Agrawal

$4.$

Write an SQL query for the HAPPY INSURANCE database that will list the agent ID and

agent name for each agent hired before the year $2000.$

$5.$

Write an SQL query for the HAPPY INSURANCE database that will display the average

rating for all agents.

$6.$

Write an SQL query for the HAPPY INSURANCE database that will for each area display the

area ID and the number of agents in the area.

$7.$

Write an SQL statement for the HAPPY INSURANCE database that adds the column

ClientPhone to the table CLIENT.

$8.$

Write an SQL query for the HAPPY INSURANCE database that will, for each agent, retrieve

the name of the agent and the name of his or her supervisor. $($The name of the agent will be

retrieved even if he or she has no supervisor.$)$