1. SQL

1. 1. SQL • Header of tables: • Head(Custoemrs) = (cid, cname, city, discnt) • Head(Orders) = (ordno, month, cid, pid, qty, dollars) • Head(Products) = (pid, pname, city, quantity, price) • Head(Agents) = (aid, aname, city, percent)

2. 1.1 SQL basic - projection • List all customer name • Select cname from customers • Select distinct cname from customers

3. 1.1 SQL basic - where • List all customers who live in Dallas • Select cid, cname from customers where city = 'Dallas' • List all customers who live in Dallas and discount is bigger than 5. • Select cid, cname from customers where city = ‘Dallas’ and discnt > 5

4. 1.1 SQL basic - join • List all customer (cid and cname) who ordered comb • Select distinct c.cid, c.cname from customers c, products p, orders o where c.cid = o.cid and p.pid = o.pid and p.pname = 'comb' • List all agents (aid, aname) who have customers who live in ‘Dallas’ • Select distinct a.aid, a.aname from customers c, agents a, orders o where c.cid = o.cid and a.aid = o.aid and c.city = 'Dallas'

5. 1.1 SQL basic - group by • List the total order amount (dollars) of each customer (cid) • Select cid, sum(dollars) from orders group by cid • List total order amount (dollars) of each customer (cid, cname) • Select o.cid, c.cname, sum(o.dollars) from customers c, orders o where c.cid = o.cid group by o.cid , c.cname

6. 1.1 SQL basic - group by • List total order amount (dollars) of each customer (cid, cname) whose total order amount is more than 1500 • Select o.cid, c.cname, sum(o.dollars) as total_sales from customers c, orders o where c.cid = o.cid group by o.cid, c.cname having sum(dollars) > 1500

7. 1.2 SQL advanced - join • For each agent (aid, aname) count the number of customers who place orders with them. • Select a.aid, a.aname, count(cid) as number_of_customers from agents a, orders o where a.aid = o.aid group by a.aid, a.aname

8. 1.2 SQL advanced - join • For each agent (aid, aname) count the number of customers who place orders with them. If an agent does not have any orders with any customer, his/her info should also be included in the result. • Select a.aid, a.aname, count(cid) as number_of_customers from agents a left join orders o on a.aid = o.aid group by a.aid, a.aname

9. 1.2 SQL advanced – MAX/MIN • List agents (aid, aname) who sold most products (dollars). • select a.aid, a.aname from agents a, orders o where a.aid = o.aid group by a.aid, aname having sum(dollars) >= all (select sum(dollars) from orders group by aid )

10. 1.2 SQL advanced – MAX/MIN • List agents (aid, aname) who sold most products (dollars) in jan. • select a.aid, a.aname from agents a, orders o where a.aid = o.aid and o.month = 'jan' group by a.aid, aname having sum(dollars) >= all (select sum(dollars) from orders where month = 'jan' group by aid)

11. 1.2 SQL advanced – MAX/MIN • List products (pid, pname) that are ordered most (qty) by customers who live in Dallas • select p.pid, p.pname from products p, orders o, customers c • where p.pid = o.pid and c.cid = o.cid and c.city = 'Dallas' • group by p.pid, p.pname • having sum(qty) >= all • (select sum(qty) from orders o, customers c where c.cid = o.cid and c.city = 'Dallas' group by o.pid)

12. 1.2 SQL advanced – All • List agents (aid, aname) who place orders with all customers in Dallas • Select a.aid, a.aname from agents a where not exists (select * from customers c where c.city = 'dallas' and not exists (select * from orders o where o.cid = c.cid and o.aid = a.aid))

13. 1.2 SQL advanced – All • List customers (cid, cname) who order all the products produced in Newark • select c.cid, c.cname from customers c where not exists (select * from products p where city = 'Newark' and not exists (select * from orders o where o.pid = p.pid and o.cid = c.cid))

14. 2. Advance SQL topics • Transact SQL • Stored Procedure • User Defined Function • Cursor • Trigger • View

15. 2.2 Stored Procedure create proc count_customer_by_city @city char(10), @counter int output as select @counter = count(*) from customers where city = @city return • declare @mycounter int • exec count_customer_by_city 'Dallas', @mycounter output • select @mycounter

16. 2.3 UDF create function total_dollars(@customerid char(4)) returns money as begin declare @sum money select @sum = sum(dollars) from orders where cid = @customerid return @sum End • SELECT *, zhoupf.total_dollars(cid) as total_order_amount FROM customers • select * from zhoupf.total_dollars('c001') • exec zhoupf.total_dollars('c001')

17. 2.3 UDF • CREATE FUNCTION total_avg_orders (@customerid char(4) ) • RETURNS @total_avg_orders_tab TABLE • ( • total_order money, • avg_order money • ) • AS • BEGIN • INSERT @total_avg_orders_tab • SELECT sum(dollars), avg(dollars) • FROM orders • WHERE cid = @customerid • RETURN • END • select * from total_avg_orders('c001') //This is correct because the return value is a table

18. 2.5 Trigger • create table Boston_Customers ( • cid char(4) not null, • cname varchar(15), • city varchar(15), discnt decimal(10,2), primary key(cid) );

19. 2.5 Trigger • create trigger insert_customer • on customers • after insert • as • insert into Boston_customers select * from inserted I where I.city = ‘Boston’ • insert into customers values ('c019', 'Tony', 'Boston', 16) • select * from boston_customers

20. 3. ER diagram • Entity (has attributes) • Relationship (can also has attributes) • Many-to-many relationship • Many-to-one relationship • One-to-one relationship

21. 3. ER diagram • NU library • Each borrower has a borrower_id, name. • Each library staff has a staff_id, name. • Each (type) book has a ISBN, name, author, number_of_copies. • Each borrower can borrow multiple books. Each (type) of book can be borrowed by multiple borrowers. • When a borrower borrows a book from a staff, the borrow date and return date will be recorded.