Assignment # $4-$ Advanced Interactive SQL $($use same table as assignment $3)$

$1)$ List all the orders in descending order by dollar $($ORDER BY$).$

$2)$ The price of the most expensive product $($Functions$).$

$3)$ Name of customers that ordered product $$p$07($SQL Query with JOIN$)$

$4)$ Find all $($cid$,$ aid, pid$)$ triples for a customer, agent, product combinations that are all in the same city. Nothing about orders is involved in this selection $($Join$).$

$5)$ Find all $($cid$,$ aid, pid$)$ triples for customer, agent, product combinations that are not all in the same city $($any two may be$).($Join$).$

$6)$ Get cities of agents booking an order from customer $$c$002($Join$,$ Projection and Restriction$).$

$7)$ Get pids of products ordered through any agent who makes at least one order for a customer in Kyoto. NOTE: this is not the same as asking for pids of products ordered by a customer in Kyoto $($hint: Customers Table once and Orders table twice$).$

$8)$ Find cids of customers who did not place an order through $$a$03($hint: use NOT EXISTS or NOT IN; May also see adbc, Misconceptions: do not use NOT $=)$

$9)$ Name of products that were not ordered through agents $\_$a$04($hint: use NOT EXISTS or NOT IN; May also see adbc, Misconceptions: do not use NOT $=)$

$10)$ Find cids of customers who have the largest discount and those who have the smallest discount. Must be only one query. $($Hint: Use UNION$).$

$11)$ Find aids and anames of agents with aname beginning with the letter $$J$$ or $$B$$ who do not place orders for a Product located in New York $($Hint: Use LIKE clause or $<mi></mi>$ and $>)$

$12)$ List the cname and the sum of dollars that each customer ordered. $($Functions$,$ GROUP BY$)$

$13)$ List the cname and the sum of dollars for each customer that has ordered more than US$1,000($ HAVING$)$

$14)$ List the customers with the two smallest discounts $($Hint: use NOT EXISTS$).$

$15)$ List customers that didn' t have any orders $($Hint: use NOT IN$).$

$16)$ List the name each customers and the total amount ordered by the customers

If the customers didn$$t place an order, list them also. $($see Functions, Group By and Outer Join$)$

$17)$ List name of customers that ordered all the products. $($Animating SQL through Procedural Code $$ Basic, Division$)$

$18)$ List all pair of products that are in the same city $($Join with $1$ table$)$

$19)$ List the three most expensive products $($Use NOT EXISTS or rownum$)$

$20)$ Increase by $10\%$ the price of all products located in $$Dallas$($Data Definition Language and Data Manipulation Language$)$

$21)$ Make cid the primary key of customers $($ALTER TABLE command. See Book or Web for SYNTAX$).$

$22)$ Make cid the foreign key of orders $($ALTER TABLE command. See Book or Web for SYNTAX$).$

$23)$ delete all rows from the agents table. $($Data Definition Language and Data Manipulation Language$)$

$24)$ List the agent table with no rows

Rollback the agent tables $($just type in ROLLBACK; $)$

List the agents again

$25)$ a$)$ Create a table called SWCustomers with the same structure as the Customers table.

b$)$ With one sql command Insert all the customers that are located in Dallas or Houston from the Customers table into the SWCustomers table.

$$

$26)$ Add a Primary Key cid to the Customers table $($use ALTER TABL CUSTOHERS

AGENTS

Prooucts

ORDERSE$).$

$27)$Add a Primary Key pid to the Products table $($use ALTER TABLE$).$

$28)$ Add Foreign Keys pid to the orders table referencing Products $($use ALTER TABLE$).$ Try to delete Product p$01.$

What happens $?$

$29)$ Add Foregin Key cid to the orders table referencing Products with the Cascade clause $($use ALTER TABLE$).$ Try to delete products $$p$01$ from the Products table. View the Orders table $($with SELECT $*$ FROM Orders;$).$ What happened $?$

$30)$

a$)$ Create a table called SWCustomers with the same structure as the Customers table.

b$)$ With one sql command Insert all the customers that are located in Dallas or Houston from the Customers table into the SWCustomers table.