![Page 1: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/1.jpg)
Structured Query Language
NEU – CCIS – CSU430Tony
![Page 2: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/2.jpg)
2
Content
Select queries on one table Select queries on multiple tables Manipulate data and table
structure
![Page 3: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/3.jpg)
3
0. References
CAPS database on textbook P27-28 http://www.sql.org/
![Page 4: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/4.jpg)
4
1. Select queries on one table
Simple queries Distinct Aggregate functions Sorting query results Like\Between … And Group Alias
![Page 5: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/5.jpg)
5
1.1 Simple queries
List all records in products select * from products
List all records in records in customers
select * from customers
![Page 6: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/6.jpg)
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)
![Page 7: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/7.jpg)
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
![Page 8: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/8.jpg)
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
![Page 9: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/9.jpg)
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)
![Page 10: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/10.jpg)
10
1.2 Distinct (cont)
List all distinct agent names
select aname from agents
select distinct aname from agents
![Page 11: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/11.jpg)
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'
![Page 12: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/12.jpg)
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'
![Page 13: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/13.jpg)
13
1.3 Aggregate functions (cont)
Most frequent-used aggregate functions
SumAvgCountMinMax… see online documents
![Page 14: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/14.jpg)
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
![Page 15: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/15.jpg)
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%'
![Page 16: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/16.jpg)
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
![Page 17: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/17.jpg)
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’ ……
![Page 18: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/18.jpg)
18
1.6 Group (cont)
Find the total expense of each client
Select cid,sum(dollars) from orders group by cid
![Page 19: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/19.jpg)
19
1.6 Group (cont)
Find total sales amount of each agent
Select aid,sum(dollars) from orders group by aid
![Page 20: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/20.jpg)
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
![Page 21: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/21.jpg)
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
![Page 22: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/22.jpg)
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).
![Page 23: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/23.jpg)
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
![Page 24: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/24.jpg)
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
![Page 25: Structured Query Language NEU – CCIS – CSU430 Tony](https://reader036.vdocuments.us/reader036/viewer/2022062313/56649cfa5503460f949cbc5f/html5/thumbnails/25.jpg)
25
1.7 Alias (cont)
Find all orders more expensive than 1000 dollars.
Select * from orders o where o.dollars > 1000