IN and NOT IN
The IN predicate select those rows for which a specified value appears in a list of values enclosed in parenthesis
A in (x,y,x) is equivelent to
a=x or a=y or a=z
---
---
Using Group BY with Aggregate Functions:
the group by clause enables you to aggregate functions to anwser more complext managerial questions such as :
What is the average salaary of employees in each department?
What is the average salaary of employees in each department?
How many employees were in each department
SELECT item_id, count(*) as NOR, SUM(quantity) AS TOTAL
FROM orders
GROUP BY item_id
Sql output
Item id NOR Total
I01 3 90
I02 1 20
I03 2 100
I04 1 60
I05 2 110
select item_id, count (*) AS NOR , SUM(quantity) AS TOTAL
FROM orders
WHERE cust_id IN (‘AAA, ‘CCC’, ‘EEE’)
GROUP BY item_id
SELECT item_id, count(*) AS NOR
FROM orders
WHERE Count(*)>=2
GROUP BY item_id
HAVING vs WHERE
When both HAVING and Where are called, SQL process the query as follows
The WHERE condition is applied first to filter individual rows
Then the groups are formed from the remaining tows according to the GROUP BY clause
Then the HAVING condition is applied to filter these groups.
Remember too that aggregate functions are allowed in a HAVING condition but not in a WHERE condition
SELECT item_id , count (*) AS NOR
FROM orders
GROUP BY item_id
HAVING count(*) >=2
Fancy fruits wants to list all customers whose combined total quantity order for all items is greater than 75 units excluding item I02 from the calculation of total quantity order
SELECT cust_id, sum(quantity) AS TOTAL
FROM orders
WHERE item_id <> ‘I02’ !=
GROUP BY cust_id
HAVING sum(quantity)>75
Fancy fruits want to show the total quantity by each customer along with the average quantity for each item ordered by each customer
select cust_id, item_id, avg(quantity)
from orders
group by cust_id, item_id
what items are priced less than item I04
1. Find the price of item 104
SELECT price
FROM stocl
WHERE item_id=’104’;
2.
Use the result from the first queriy above to
SELECT item_id
FROM stock
Where price >400
--
SELECT item_id
FROM stock
WHERE price <
(SELECT price
FROM stock
WHERE item_id=’I05’),
http://mysite.verizon.net/
http://homepages.rpi.edu/~
http://www.studydroid.com/
http://www.w3schools.com/sql/
http://marriottschool.net/
No comments:
Post a Comment