----Group By and
Having Clauses-----using SH schema
SELECT * FROM products
SELECT * FROM sales
---Group By----
select avg(amount_sold)
from sales s,
products p
where s.prod_id = p.prod_id
AVG(AMOUNT_SOLD)
106.879881775232547889030008390987361279
===============================================================
select avg(amount_sold)
from sales s,
products p
where s.prod_id = p.prod_id
and prod_category =
'Electronics';
AVG(AMOUNT_SOLD)
----------------
125.551667
=======================================================
=============================================================
select
prod_category,avg(amount_sold)
from sales s,
products p
where s.prod_id = p.prod_id
group by prod_category
PROD_CATEGORY, AVG(AMOUNT_SOLD)
Software/Other 34.13139968569673435453112317776884154329
Hardware 1344.50775542098066028521195546005079117
Electronics 125.551666594992560227751640620296386765
Peripherals and Accessories 108.8245878918458352684822728717144663
Photo 188.064642075615910542461966935053241056
====================================================================
SELECT
prod_subcategory, avg(amount_sold)
from sales s,
products p
where s.prod_id = p.prod_id
and prod_category =
'Electronics'
group by prod_subcategory;
PROD_SUBCATEGORY AVG(AMOUNT_SOLD)
--------------------------------------------------
----------------
Game Consoles
300.523928
Y Box Accessories
18.7803303
Home Audio
582.175922
Y Box Games
22.640670
----------------------------------------
-------------------------------------------------
------ Having
select prod_subcategory, avg(amount_sold)
from sales s,
products p
where s.prod_id = p.prod_id
and prod_category =
'Electronics'
group by prod_subcategory
having avg(amount_sold) > 300;
PROD_SUBCATEGORY AVG(AMOUNT_SOLD)
--------------------------------------------------
----------------
Game Consoles
300.523928
Home Audio
582.175922
-------------------------------------------------------------------------
----------------------------------------------------------------------
--more practice
about both of them
select
prod_subcategory, prod_name, avg(amount_sold)
from sales s,
products p
where s.prod_id = p.prod_id
and prod_category =
'Electronics'
group by prod_subcategory,
prod_name;
====================================================================
select
prod_subcategory, prod_name, avg(amount_sold)
from sales natural join
products
where prod_category =
'Electronics'
and prod_subcategory !=
'Home Audio'
group by prod_subcategory,
prod_name;
=====================================================================
select
prod_subcategory, prod_name, max(amount_sold), avg(amount_sold)
from sales natural join
products
where prod_category =
'Electronics'
and prod_subcategory !=
'Home Audio'
group by prod_subcategory,
prod_name;
==================================================================================================================================================================================================================
select substr(prod_subcategory,1,18),
substr(prod_name,1,25),
max(amount_sold),
avg(amount_sold)
from sales natural join
products
where prod_category =
'Electronics'
and prod_subcategory !=
'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
============================================================
select substr(prod_subcategory,1,18) Subcategory,
substr(prod_name,1,25) Product_Name,
max(amount_sold)
Max_Amt_Sold,
round(avg(amount_sold),2) AvgAmt
from sales natural join
products
where prod_category =
'Electronics'
and prod_subcategory !=
'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
=======================================================================
select substr(prod_subcategory,1,18) Subcategory,
substr(prod_name,1,25) Product_Name,
max(amount_sold)
Max_Amt_Sold,
round(avg(amount_sold),2) AvgAmt
from sales natural join
products
where prod_category =
'Electronics'
and prod_subcategory !=
'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25)
having max(amount_sold) > 10
and avg(amount_sold) > 10
order by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
SUBCATEGORY PRODUCT_NAME MAX_AMT_SOLD AVGAMT
------------------
------------------------ ------------ ------
Game Consoles Y Box 326.39 300.52
Y Box Accessories Xtend Memory 29.8 24.15
Y Box Games Adventures with
Numbers 17.03 13.78
Y Box Games Bounce 25.55 21.13
Y Box Games Comic Book Heroes 25.76 22.14
Y Box Games Endurance Racing 42.58 34.29
Y Box Games Finding Fido 16.6 12.79
Y Box Games Martial Arts Champions 25.76
22.14
Y Box Games Smash up Boxing 38.64 33.2
==========================
select
prod_category, prod_subcategory, prod_name,
prod_list_price
from products3
where prod_list_price >
100
order by prod_category,
prod_subcategory, prod_name;
====================================================================================
.
The SQL
statement that will return
the aggregate amount sold for every product category and
subcategory using the ANSI SQL natural join is shown here:
select
prod_category, prod_subcategory, sum(amount_sold)
from products natural join
sales
group by prod_category,
prod_subcategory;
==================================================================================
The list of all customer IDs and last names for
customers that only had one sale is returned by the following SQL statement:
select c.cust_id,
cust_last_name, count(*)
from customers c,
sales s
where c.cust_id = s.cust_id
group by c.cust_id, cust_last_name
having count(*) = 1;
select
prod_category,count(prod_category)
from sales s,
products p
where s.prod_id = p.prod_id
group by prod_category
PROD_CATEGORY, COUNT(PROD_CATEGORY)
Software/Other 405341
Hardware 15357
Electronics 116267
Peripherals and Accessories 286369
Photo 95509
select
PROD_SUBCATEGORY,count(PROD_SUBCATEGORY)
from sales s,
products p
where s.prod_id = p.prod_id
group by PROD_SUBCATEGORY
No comments:
Post a Comment