Structured Query Language

1. Structured Query Language NEU – CCIS – CSU430 Tony

2. Content • Select queries on one table • Select queries on multiple tables • Manipulate data and table structure

3. 0. References • CAPS database on textbook P27-28 • http://www.sql.org/

4. 1. Select queries on one table • Simple queries • Distinct • Aggregate functions • Sorting query results • Like\Between … And • Group • Alias

5. 1.1 Simple queries • List all records in products select * from products • List all records in records in customers select * from customers

6. 1.1 Simple queries (cont) • List all product names in the product table select pname from products product[pname] (Projection) ?? • List all customer names in the customers table select cname from customers customers[cname] (Projection)

7. 1.1 Simple query (cont) • List all customer records in Dallas select * from customers where city = 'Dallas‘ customers where city = ‘Dallas` • List all order records costs more than 800 select * from orders where dollars > 800 orders where dollars > 800

8. 1.1 Simple query (cont) • List all customer names in Dallas select cname from customers where city = 'Dallas‘ • List the order number, quantity of orders cost more than 800 select ordno, qty from orders where dollars > 800

9. 1.2 Distinct • List all customer names in the customers table select cname from customers customers[cname] (Not Projection) select distinct cname from customers customers[cname] (Projection)

10. 1.2 Distinct (cont) • List all distinct agent names select aname from agents select distinct aname from agents

11. 1.3 Aggregate functions • List the total cost of all orders select sum(dollars) from orders • List the total cost of client c001 select sum(dollars) from orders where cid = 'c001'

12. 1.3 Aggregate functions (cont) • List the average discount of all customers select avg(discnt) from customers • List the average discount of customers in Duluth area select avg(discnt) from customers where city = 'Duluth'

13. 1.3 Aggregate functions (cont) • Most frequent-used aggregate functions Sum Avg Count Min Max … see online documents

14. 1.4 Sort query results • List the product names and prices in price ascending order select pname, price from products order by price • List the product names and prices in price descending order select pname, price from products order by price desc

15. 1.5 Like \ Between … And • Find all customers in the cities start with letter “D”. select * from customers where city like 'D%‘ • Find all products whose name end with letter “r”. select * from products where pname like '%r‘ • Find all the agents whose names contain letter “r”. select * from agents where aname like '%r%'

16. 1.5 Like \ Between … And (cont) • List all orders that cost between 500 and 1000 select * from orders where dollars between 500 and 1000 • List all customers whose discount between 5 and 10 select * from customers where discnt between 5 and 10

17. 1.6 Group • Find the total expense of each client Straightforward solutions: Select sum(dollars) from orders where cid = ‘c001’ Select sum(dollars) from orders where cid = ‘c002’ ……

18. 1.6 Group (cont) • Find the total expense of each client Select cid,sum(dollars) from orders group by cid

19. 1.6 Group (cont) • Find total sales amount of each agent Select aid,sum(dollars) from orders group by aid

20. 1.6 Group (cont) • Find total sales amount of each agent whose total sales amount is more than 1000 dollars. Select aid,sum(dollars) from orders group by aid where sum(dollars) > 1000 Select aid,sum(dollars) from orders group by aid having sum(dollars) > 1000

21. 1.6 Group (cont) • Find total expense of each client whose total expense is more than 1000 dollars. Select cid,sum(dollars) from orders group by cid having sum(dollars) > 1000

22. 1.6 Group (cont) • Find the total expense of each client on each product • Select pid,cid,sum(dollars) from orders group by cid • Fields cannot have multi-values attributes or have any internal structures (First Normal Form Rule).

23. 1.6 Group (cont) • Find the total expense of each client on each product • Select pid,cid,sum(dollars) from orders group by pid,cid

24. 1.7 Alias and calculated field • Find total expense of each client whose total expense is more than 1000 dollars • Select cid as client_id, sum(dollars) as total_cost_per_client from orders group by cid having sum(dollars) > 1000

25. 1.7 Alias (cont) • Find all orders more expensive than 1000 dollars. • Select * from orders o where o.dollars > 1000