Week 2 Homework
The following practice may need to be completed using the databases hosted on wmc3317-2 using WB. Use WB whether you are running queries or creating a relational schema (a data model, essentially). 1. Database: ‘bigpvfc’. Copy/paste your SQL queries below after testing that they work. a) List each customer name, address and the number of orders each has placed. (use tables: customer_t, order_t)
SELECT customeraddress, customername, COUNT(*) AS number_of_orders FROM customer_t, order_t WHERE customer_t.customerid = order_t.customerid GROUP BY customername
ORDER BY customer_t.customerid; b) List only name and address of customers who have placed more than two orders.
SELECT customeraddress, customername, COUNT(*) AS number_of_orders FROM customer_t, Order_t WHERE customer_t.customerid = order_t.customerid GROUP BY customername HAVING number_of_orders > 2;

c) List the running total of the number of total number of orders placed till date as they come in (use table: order_t).
Select orderid, orderdate, (select count(*) from order_t p where co.orderd ate >= p.orderdate) as runtotal
FRom order_t co Order by runtotal;

2. Database: ‘sakila’. Copy/paste your SQL queries below after testing that they work.
While an employee at Sakila, you have been asked to merge the data in the ‘film’ table with an additional list of movies from http://www.hometheaterinfo.com/dvdlist.htm (dvd_csv.zip) because Sakila is expecting a large shipment of additional movies and wants add it to their rental catalog. Import the entire file from the above URL (.csv format) and store it as a separate table in your database. Then select a set of movies from the imported list based on your judgment and update the ‘film’ table with the additional movies you have selected. This is an open-ended question. The entire procedure should be saved