Databases | SQL | Oracle | MS Access Answers

What is S an alias of in the query below?
select orderid, salespersonname
from
(select salespersonid, salespersonname, SalespersonTelephone
from Salesperson_T where SalesTerritoryID = 1) S
join Order_T O
on O.SalespersonID = S.SalespersonID


An Outer Join


A Derived Table


A Union


A Join

In the query below, what is the statement in between parenthesis?

SELECT CUSTOMERNAME
FROM CUSTOMER_T
WHERE CUSTOMERID IN
(SELECT DISTINCT CUSTOMERID FROM ORDER_T)


a list


A correlated subquery


A non-correlated subquery


A correlated insert statement

Table Customer_T has customer info including the CustomerID and CustomerName, while table Payment_T has every payment made by each CustomerID.

What statement would provide all records of payments made showing customer name?


SELECT PAYMENT.*, CUSTOMERNAME FROM PAYMENT_T


SELECT PAYMENT.*, CUSTOMERNAME FROM PAYMENT_T JOIN CUSTOMER_T ON PAYMENTID = CUSTOMERID


SELECT PAYMENT.*, CUSTOMERNAME FROM PAYMENT_T JOIN CUSTOMER_T ON CUSTOMERID = CUSTOMERID


SELECT PAYMENT.*, CUSTOMERNAME FROM PAYMENT_T P JOIN CUSTOMER_T C ON P.CUSTOMERID = C.CUSTOMERID

Assume there are two tables, A and B, with a one to many relationship. What join type would you use to get records from A and only those from B that match.


INNER JOIN


FULL JOIN


LEFT JOIN


OUTER JOIN

Select the statement that is equivalent to the one below. Equivalent means that it would return the same records.

SELECT *

FROM ARRIVAL

WHERE ORIGIN = 'DUS'

OR ORIGIN = 'JFK'

OR ORIGIN = 'DFW'




SELECT *
FROM ARRIVAL
WHERE ORIGIN IN ('DUS', 'JFK', 'DFW')

SELECT *
FROM ARRIVAL
WHERE ORIGIN OR ('DUS', 'JFK', 'DFW')

SELECT *
FROM ARRIVAL
WHERE ORIGIN = ('DUS', 'JFK', 'DFW')

SELECT *
FROM ARRIVAL
WHERE ORIGIN AND ('DUS', 'JFK', 'DFW')

For table Customer_T used in class for the PineValleyFurniture database, what does the following statement do:

SELECT CustomerState, count(CustomerState)
FROM Customer_T
GROUP BY CustomerState
HAVING count(CustomerState) > 1



Retrieves the States with more than one customer


Retrieves the Customers with more than one state


Retrieves the States where there are more customers


Retrieves all the names of the customers with more states

Select the statement with the correct syntax below.



SELECT [CustomerID]
,[SalespersonID]
,count(*)
FROM [dbo].[Order_T]
GROUP BY [CustomerID]
,[SalespersonID]

SELECT [CustomerID]
,[SalespersonID]
,count(*)
FROM [dbo].[Order_T]
GROUP BY [CustomerID]


SELECT [CustomerID]
,[SalespersonID]
,count(*)
FROM [dbo].[Order_T]
ORDER BY [CustomerID]
,[SalespersonID]

SELECT [CustomerID]
,[SalespersonID]
,count(*)
FROM [dbo].[Order_T]

Assume a table SALES with sales of a product across all states in USA. Assume there is a column State in that table and that values are the USPS two character state abbreviation. Now select the statement that will give retrieve the sales from Connecticut (CT) and New York (NY).



SELECT *

FROM SALES

WHERE NOT STATE = 'CT'

OR STATE = 'NY'


SELECT *

FROM SALES

WHERE STATE = 'CT'

OR STATE = 'NY'


SELECT *

FROM SALES

WHERE STATE = 'CT'

AND STATE = 'NY'


SELECT *

FROM SALES

WHERE STATE = 'CT'

AND NOT STATE = 'NY'

What is the query below retrieving?

SELECT CUSTOMER_STATE

FROM CUSTOMER_T

GROUP BY CUSTOMER_STATE

HAVING COUNT(*) = 1



record for CUSTOMERID = 1


States with more than 1 customer


States with one customer


States with ID = 1

Table CUSTOMER has all customers, and it has a column LASTNAME for the Customer's last name. What statement below would return all customers whose last name begins with 'EST'?

SELECT *

FROM CUSTOMER

WHERE LASTNAME LIKE 'EST%'


SELECT *

FROM CUSTOMER

WHERE LASTNAME = 'EST%'


SELECT *

FROM CUSTOMER

WHERE LASTNAME = 'EST'


SELECT *

FROM CUSTOMER

WHERE LASTNAME = 'EST'

OR LASTNAME = 'ESTA'