structured query language neu – ccis – csu430 tony
TRANSCRIPT
Structured Query Language
NEU – CCIS – CSU430Tony
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 tableselect pname from productsproduct[pname] (Projection) ??
List all customer names in the customers table
select cname from customerscustomers[cname] (Projection)
7
1.1 Simple query (cont)
List all customer records in Dallasselect * from customers where city =
'Dallas‘customers where city = ‘Dallas`
List all order records costs more than 800select * from orders where dollars > 800orders where dollars > 800
8
1.1 Simple query (cont)
List all customer names in Dallasselect 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 customerscustomers[cname] (Not Projection)
select distinct cname from customerscustomers[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 ordersselect sum(dollars) from orders
List the total cost of client c001select 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
SumAvgCountMinMax… see online documents
14
1.4 Sort query results List the product names and prices in price
ascending orderselect 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
1000select * 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