1 / 25

Structured Query Language

Structured Query Language. NEU – CCIS – CSU430 Tony. Content. Select queries on one table Select queries on multiple tables Manipulate data and table structure. 0. References. CAPS database on textbook P27-28 http://www.sql.org/. 1. Select queries on one table. Simple queries Distinct

Download Presentation

Structured Query Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related