• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL test answers

 
Dilshan De Silva
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We ask people to cite the sources of questions. This looks like it is from here.

1) I don't think that is correct. Think about which fields could be derived from other fields.

2) Yes

3) Yes

4) Yes. 'co' should probably be uppercase since state abbreviations are upper case

5) That works. Do you know how to write it with in an "in" clause?

6) Yes

7) You aren't using a union

8) Yes

9) Yes

11) Look at the fields you are returning vs the fields they asked for

Note: I didn't run them so "yes" means "it looks right"

 
Dilshan De Silva
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne Boyarsky wrote:We ask people to cite the sources of questions. This looks like it is from here.

1) I don't think that is correct. Think about which fields could be derived from other fields.

2) Yes

3) Yes

4) Yes. 'co' should probably be uppercase since state abbreviations are upper case

5) That works. Do you know how to write it with in an "in" clause?

6) Yes

7) You aren't using a union

8) Yes

9) Yes

11) Look at the fields you are returning vs the fields they asked for

Note: I didn't run them so "yes" means "it looks right"



Thanks. Any suggestions for 1 and 10th quetions?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I gave you a hint for #1. For #10, try a join.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic