I have done some online SQL
test for practice... But i cant fine any correct answers for the test. Can any one mark my answers for correctness?
MH Software, Inc.
SQL Test
The purpose of this test is to assess your general knowledge of SQL and database concepts. Please don’t
worry about a particular kind of syntax for a particular SQL Engine. Focus on getting the data required.
The table shown below depicts a typical database with 4 tables in it. The questions on this test are based on
these database structures. As a warning, some of these questions are extremely hard. Please do the best you
can and answer as many questions as you can. The test is solely to give us an idea on your level of SQL
expertise.
Good Luck!
Customer Invoice LineItem Product
Customer_ID (PK) Int
Name
Address
City
State
Zip
Phone
Fax
Invoice_No (PK) Int
Customer_ID (FK)
Invoice_Date
SubTotal
Tax
Shipping
Total
Invoice_No
LineItem_No
Part_No (FK)
Qty
Price
Ext_Price
Part_No (PK) Int
Description
Cost
Qty_OH
Price
1) Strictly speaking, the Invoice table contains two fields it shouldn’t. Identify the unnecessary fields.
tax
shipping
2) Write an SQL Statement to list all customers sorted by name.
select customerid from customer order by name
3) Marketing wants to target computer companies for a direct mailing. Write a query that lists all
companies with the words “Computer” or “Software” in their name. Assume the database is not
case-sensitive for
string comparisons.
select c.name from customer c where c.name like '%Computer%' OR like '%Software%'
4) Write an SQL Statement to list all invoices for customers who are located in the state of Colorado
(CO). Required output fields are: Customer ID, Customer Name, Invoice No, Sub-Total, Total.
Sort the results by City and then Zip cod
select c.customerid,c.name,i.ino,istot,i.tot
from customer c join invoice i
on c.customerid=i.customerid
where c.state='co'
order by c.city,c.zip
5) Write an SQL statement that lists all invoices for customers located in CO,CA,AZ,MT,WY,
WA,OR,ID, NM or UT, and the invoice date is within a specified date range known as TStart to
TEnd. Required fields and sort criteria are the same as question 3.
select c.customerid,c.name,i.ino,istot,i.tot
from customer c join invoice i
on c.customerid=i.customerid
where c.state=('co' or 'ca' or 'az' or 'MT'or 'WY' or 'WA' or 'OR' or 'ID' or 'NM' or 'UT'
) AND i.invoicedate >=to_date('Tstart','dd/mm/yyyy') and i.invoicedate <=to_date('TEnd','dd/mm/yyyy')
order by c.city,c.zip
6) Write an SQL Statement to list the following items: Customer ID, Customer Name, number of
invoices, sum of total for invoices. Ensure that all customers are returned in the result set.
select c.customerid,c.name,count(i.ino),sum(i.tot)from customer c left join invoice ion c.customerid=i.customeridgroup by c.customerid
7) Re-write the statement from question 6 using a UNION.7) Re-write the statement from question 6 using a UNION.write the statement without using a UNION.
select c.customerid,c.name,count(i.ino),sum(i.tot)
from customer c, invoice i
where c.customerid=i.customerid
group by c.customerid
8) Write an SQL Statement to list the following items: Customer ID, Customer Name, Number of
invoices, sum of total for invoices for all customers with more than $50,000 in total sales.
select c.customerid,c.name,count(i.ino),sum(i.tot)
from customer c left join invoice i
on c.customerid=i.customerid
group by c.customerid
having (sum(i.tot)>50000)
9) Referential integrity constraints were accidentally destroyed for the relationship between the
LineItem table and the Product table; as a result, parts were deleted from the product table that
should not have been. Write a query that will list the distinct part numbers in the LineItem table
that have no corresponding entry in the Product table.
select *
from lineitems l
where l.part_no NOT IN (select p.part_no
from product p)
10) Research has determined that when product 1234 and 2345 are shipped together, product 2345
loses its potency. Write an SQL statement that lists all invoices that have both part numbers on the
same invoice.
no idea
11) Because of poor data entry policies there are a lot of duplicate customers in the database. Write a
query that will list all customers where there exists another customer with the same phone number.
Displayed fields should be: Customer Name, City, State, Phone, DupeCustomer Name,
DupeCustomer City, DupeCustomer State, DupeCustomer Phone.
select c.name,c.city,c.ctate
from customer c
group by (c.phone)
having (count(c.phone) > 1)
