CISC4322 Database Worksheet (due: Thursday, April 23)
This worksheet requires that you use the
"Simple MySQL commands")
found on our class web-page.
It also may contain material and questions covered in class - it is a worksheet, you can ask
questions...
Table Salespeople
| Snum |
Sname |
City |
Comm |
| 1001 |
Peel |
London |
.12 |
| 1002 |
Serres |
San Jose |
.13 |
| 1004 |
Motika |
London |
.11 |
| 1007 |
Rifkin |
Barcelona |
.15 |
| 1003 |
Axelrod |
New York |
.10 |
Table Customers
| Cnum |
Cname |
City |
Rating |
Snum |
| 2001 |
Hoffman |
London |
100 |
1001 |
| 2002 |
Giovanni |
Rome |
200 |
1003 |
| 2003 |
Mark |
San Jose |
200 |
1002 |
| 2004 |
Karen |
Berlin |
300 |
1002 |
| 2006 |
Nigel |
London |
100 |
1001 |
| 2008 |
Marcelo |
San Jose |
300 |
1007 |
| 2007 |
Bella |
Rome |
100 |
1004 |
Table Orders
| Onum |
Amt |
Odate |
Cnum |
Snum |
| 3001 |
18.69 |
10/03/2006 |
2008 |
1007 |
| 3003 |
767.19 |
10/03/2006 |
2001 |
1001 |
| 3002 |
1900.10 |
10/03/2006 |
2007 |
1001 |
| 3005 |
5160.45 |
10/03/2006 |
2003 |
1004 |
| 3006 |
1098.16 |
10/03/2006 |
2008 |
1007 |
| 3009 |
1713.23 |
10/04/2006 |
2002 |
1003 |
| 3007 |
75.77 |
10/04/2006 |
2004 |
1002 |
| 3008 |
4723.00 |
10/05/2006 |
2006 |
1001 |
| 3010 |
1309.95 |
10/06/2006 |
2004 |
1002 |
| 3022 |
9891.88 |
10/06/2006 |
2006 |
1001 |
1. Consider the above tables, what is the single column primary key of "Customers"?
2. What is another word for row ?
For
Column?
3. Write a SELECT command that produces the order number, amount and date for all the rows in the order table.
4. Write a query that produces all rows from the custom table for which the salepersons's number is 1001.
5. Write a SELECT command that produces the rating followed by the name of each customer in San Jose.
6. Write a SELECT command that produces the City and commission for Mrs. Peel and Mr. Rifkin, for the Salespeople table.
** Note:: SELECT DISTINCT snum FROM Orders; Will create a select ion without duplicates...
7. Write a query that will produce the "city" values from the "customers" table without any repeats.
8. What is the SQL command for creating a table called "BlackJack" that has "Id" and a primary key (automatically incremneting),
a first name, last name, and winnings for each player, the winnings value can be negative if they has lost money.
9. Show this table "BlackJack" with appropriate data for 5 people:
10. Say we are done with the "SalesPeople" table, what is the command to remove the table.
11. Write the SQL command to add a coulmn "Cash" to the table "BlackJack" , set it to NULL.
12. Write the SQL command to delete "Marcelo" from the customers table.
13. Write the SQL command to display all customers who have orders over $1000.
14. Show the SQL command to show all tables in a database.
15. Show the SQL command to show all columns of the Customers table.