SQL EXAMPLES ( from the handout “ Databases and Queries”)



SQL EXAMPLES ( from the handout “ Databases and Queries”)

|Orders(OrderID,CID,SID,ODate,Amount) |[pic] |

|Customers(CID,Name,Phone,Street,City,AccountBalance) | |

|Salespeople(SID,Name,YearHired,Phone,Commission) | |

|ItemsSold(OrderID,ItemID,Quantity) | |

|Items(ItemID,Category,Description,Price) | |

1. Find the total amount of the orders;

SELECT SUM(Amount)

FROM Orders;

|Expr1000 |

|$2,416.84 |

2. Find total amount of orders in dollars for each customer. Display the amounts;

SELECT SUM(Amount)

FROM Orders;

GROUP BY CID;

|Expr1000 |

|$549.39 |

|$255.32 |

|$415.39 |

|$509.93 |

|$686.81 |

3. Find total amount of orders in dollars for each customer. Display CID and Amount;

SELECT CID, SUM (Amount)

FROM Orders

GROUP BY CID;

|CID |Expr1001 |

|12345 |$549.39 |

|28764 |$255.32 |

|29587 |$415.39 |

|44453 |$509.93 |

|87535 |$686.81 |

4. Find total amount of the orders for the customer whose ID is 12345. Display the total amount;

SELECT SUM (Amount)

FROM Orders

WHERE CID=12345;

|Expr1000 |

|$549.39 |

5. Find total amount of the orders for the customer whose ID is 12345. Display customer ID and

the total amount;

SELECT CID,SUM(Amount)

FROM Orders

WHERE CID=12345;

This fails; need the group by, even though only one cid is being reported

SELECT CID, SUM (Amount)

FROM Orders

WHERE CID=12345

GROUP BY CID;

|CID |Expr1001 |

|12345 |$549.39 |

JOINING THE TABLES

6. For salesperson "Bird", display the total amount of Sales dollars sold by "Bird”.

a. Which tables? Need: Salespeople to get the Name, need Orders to get the Amount. These tables can join, since they have a common column.

SELECT SUM(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID

WHERE Salespeople.Name = "Bird";

|Expr1000 |

|$533.25 |

b. In this case, you cannot display the Name, since if using an aggregate function and you want to display something other than the aggregate function result, you must use GROUP BY. The next example shows how to display both his Name and his SUM.

GROUP BY

a. GROUP BY is required when both are true:

[1] [using aggregate function]

AND [2] [want to display something in addition to the aggregate result]

b. GROUP BY may also be needed in other circumstances: Suppose you want to find the sum of the orders by City: you would need GROUP BY City; so the database would be able to combine information for each city.

7. For salesperson "Bird", display his Name and the total amount of Sales dollars sold by "Bird."

a. What tables? Need: Salespeople to get the Name, need Orders to get the Amount. These tables can join.

SELECT Salespeople.Name,SUM(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID

WHERE Salespeople.Name = "Bird"

GROUP BY Salespeople.Name;

|Name |Expr1001 |

|Bird |$533.25 |

b. Since we're displaying something in addition to the aggregate function result, we must use GROUP BY, and it must follow the WHERE if that clause is included.

8. We have several salespeople. We could find the average amount of sales each made using

WHERE (if we didn't display some column other than the aggregate function result) or using

GROUP BY

a. Using no condition (no WHERE to restrict which rows get displayed): simply find the average Amount

SELECT AVG(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID;

|Expr1000 |

|$201.40 |

1. This gives the average of all 12 entries.

b. Using WHERE: find the average Amount for only "Bird" but don't display his name.

SELECT AVG(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID

WHERE Salespeople.Name = "Bird";

|Expr1000 |

|$133.31 |

1. This is the average of "Bird's" 4 sales.

2. This uses WHERE only. We don't need GROUP BY since no name is displayed.

c. Find the average Amount for only "Bird" and display his name.

SELECT Salespeople.Name,AVG(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID

WHERE Salespeople.Name = "Bird"

GROUP BY Salespeople.Name;

|Name |Expr1001 |

|Bird |$133.31 |

1. This is the average of "Bird's" 4 sales.

2. Since something other than the aggregate function value was displayed, GROUP BY was required

d. Find the average Amount for each salesperson, and display those values (without names)

SELECT AVG(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID

GROUP BY Salespeople.Name;

|Expr1000 |

|$133.31 |

|$311.68 |

|$271.59 |

|$148.49 |

1. This gives the average for each salesperson, but no names are displayed.

e. Find the average Amount for each salesperson, and display those values and the names.

SELECT Salespeople.Name,AVG(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID

GROUP BY Salespeople.Name;

|Name |Expr1001 |

|Bird |$133.31 |

|Jabbar |$311.68 |

|Johnson |$271.59 |

|West |$148.49 |

1. We had to use GROUP BY since something other than the aggregate function value was displayed.

2. We did not use WHERE to restrict which rows were displayed, so the name and average for each salesperson was displayed.

f. Display the name and display the average Amount of only "Bird's" sales.

SELECT Salespeople.Name,AVG(Orders.Amount)

FROM Salespeople INNER JOIN Orders

ON Salespeople.SID=Orders.SID

WHERE Salespeople.Name = "Bird"

GROUP BY Salespeople.Name;

|Name |Expr1001 |

|Bird |$133.31 |

1. NOTE: WHERE must come before GROUP BY if WHERE is used

2. GROUP BY is needed because something other than the aggregate function value was displayed.

3. Use WHERE to display only "Bird" sales; to restrict which rows are displayed.

JOINING THE TABLES USING AN INTERMADIATE TABLE

9. List salespeople (sorted by Name, ascending), and the customer names who placed orders with those

salespeople. Don't display duplicate records. Display the names of the salespeople and the customers.

a. Note: it takes 3 tables:

1. Customer Name comes from Customers

2. Salesperson's Name comes from Salespeople

3. But table Customers can't join with table Salespeople, since they have no common column. Hence, the table Orders must be used as an intermediary.

SELECT DISTINCT Customers.Name, Salespeople.Name

FROM Customers INNER JOIN

(Orders INNER JOIN Salespeople ON Orders.SID=Salespeople.SID)

ON Customers.CID=Orders.CID;

output:

|Customers.Name |Salespeople.Name |

|Adamz |Bird |

|James |Bird |

|James |Johnson |

|Jones |Jabbar |

|Jones |Johnson |

|Kolke |Bird |

|Kolke |West |

|Smitz |Jabbar |

|Smitz |West |

10. Display each salesperson's name and how many different items each salesperson sold. Sort

the results from most number sold to least number sold.

a. What tables? Need: ItemsSold to get the ItemID's, need Salespeople to get the Names, but those two tables don't join, so you need Orders as an intermediate table, since it connects ItemsSold and Salespeople,

SELECT Salespeople.Name,COUNT(ItemsSold.ItemID)

FROM ItemsSold INNER JOIN

(Orders INNER JOIN Salespeople ON Orders.SID=Salespeople.SID)

ON ItemsSold.OrderID=Orders.OrderID

GROUP BY Salespeople.Name

ORDER BY COUNT(ItemsSold.ItemID) DESC;

|Name |Expr1001 |

|Johnson |6 |

|West |5 |

|Bird |5 |

|Jabbar |3 |

b. Must use GROUP BY since something is displayed other than the aggregate function value.

c. Sort DESC on the number of items sold.

11. Display the Salespeople Names, the Names of the customers they sold to, the dates of the sales, and the amounts. Sort the records by Salesperson Names, in Ascending order.

a. Which tables:

Salespeople to get the salesperson's Name

Customers to get the customer's Name (and these two tables don't join, so some other table must be used to retrieve information out of these two tables)

Orders to get the order date and the order amount (and also to join Salespeople and Customers.

b. SQL

SELECT Salespeople.Name,Customers.Name,Orders.ODate,Orders.Amount

FROM Salespeople INNER JOIN

(Orders INNER JOIN Customers ON Orders.CID=Customers.CID)

ON Salespeople.SID=Orders.SID

ORDER BY Salespeople.Name;

|Salespeople.Name |Customers.Name |ODate |Amount |

|Bird |Adamz |10/11/2001 |$0.00 |

|Bird |Adamz |6/27/2004 |$255.32 |

|Bird |Kolke |5/1/2004 |$154.39 |

|Bird |James |4/4/2004 |$123.54 |

|Jabbar |Smitz |5/8/2004 |$325.46 |

|Jabbar |Jones |4/9/2004 |$297.89 |

|Johnson |James |6/9/2004 |$563.27 |

|Johnson |Jones |5/23/2004 |$193.58 |

|Johnson |Jones |3/3/2004 |$57.92 |

|West |Kolke |6/9/2004 |$154.15 |

|West |Kolke |6/9/2004 |$201.39 |

|West |Smitz |5/6/2004 |$89.93 |

Note: These examples given above are related to data in a database supplied in the Prentice Hall Anderson&Post, 3rd. The name of the database is C06Ex15.mdb. (You can find this database on Blackboard in course documents sections) Thus you can open this database and try these SQL statements.

How to execute an SQL command in Access.:

a. Under Objects, click Queries, click the button New, click Design View, click OK

b. The window labeled Show Table appears: Close this window without doing anything with it.

c. At the top left below the File menu item is an icon which either shows SQL or it shows an icon called the Design View Button.

1. If you see SQL there, click on the button.

2. If you see the Design View button, click on the arrow to its right and select SQL View.

d. Then you'll be in the SQL window, which will already have the default "SELECT" statement and the ending semi-colon. The semi-colon is the last thing in your SQL statement. Thus you could now type any of the SQL statements shown below.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download