Preparing Database and table
CREATE TABLE product (
prod_id VARCHAR(6) PRIMARY KEY,
prod_name VARCHAR(30),
sup_id VARCHAR(6),
cat_id INT,
price money,
inStock INT,
onOrder INT,
reOrder INT
) |
create table product (
prod_id varchar(6) primary key,
prod_name varchar(30),
sup_id varchar(6),
cat_id int,
price money,
inStock int,
onOrder int,
reOrder int
)
File Data ‘import.txt’
p0001,Tofu,s0031,2,23.00,33,7,10
p0002,Seaweed,s0031,2,6.00,24,11,12
p0003,Biscuits Almond,s0005,3,55.00,17,3,8
p0004,Ipoh coffee,s0005,1,108.00,11,6,5
p0005,Earl grey tea,s0011,1,90.00,15,5,7
p0006,Herbal Tea,s0011,1,25.00,21,1,17
p0007,Sparkle,s0033,5,107.00,14,3,9
p0008,Hershey's,s0112,3,35.00,21,5,12
p0009,Seaweed,s0107,2,28.00,16,3,
p0010,Fruit jellies,s0052,3,32.00,9,0,6
p0011,Syoss Shampoo,s0050,5,78.00,12,2,8
p0012,Drink Water,s0021,1,20.00,76,32,28
Import data script
BULK INSERT product
FROM 'c:\import.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
) |
BULK INSERT product
FROM 'c:\import.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Homework
/*1*/
SELECT SUM(inStock) AS 'InStock'
FROM product
WHERE sup_id='s0011'
/*2*/
SELECT cat_id,SUM(onOrder) AS 'Sum Order'
FROM product
GROUP BY cat_id
HAVING avg(inStock) > 10
ORDER BY SUM(onOrder) DESC;
/*3*/
SELECT cat_id,MAX(onOrder) AS 'MAX ORDER'
FROM product
GROUP BY cat_id
/*4*/
SELECT MAX(price) AS 'MAX S0031' , MIN(price) AS 'MIN S0031'
FROM product
WHERE sup_id='s0031'
/*5*/
SELECT inStock FROM product WHERE price > 80;
/*6*/
SELECT cat_id,avg(onOrder) AS 'Average onOrder'
FROM product
GROUP BY cat_id
ORDER BY avg(onOrder) ASC
/*7*/
SELECT cat_id,MAX(onOrder) AS 'Max Order'
FROM product
GROUP BY cat_id
HAVING cat_id!=3
/*8*/
SELECT sup_id,avg(onOrder) AS 'Average onOrder' ,avg(inStock) AS 'Average inStock'
FROM product
GROUP BY sup_id
ORDER BY avg(inStock) DESC |
/*1*/
select sum(inStock) as 'InStock'
from product
where sup_id='s0011'
/*2*/
select cat_id,sum(onOrder) as 'Sum Order'
from product
group by cat_id
having avg(inStock) > 10
order by sum(onOrder) DESC;
/*3*/
select cat_id,max(onOrder) as 'MAX ORDER'
from product
group by cat_id
/*4*/
select max(price) as 'MAX S0031' , min(price) as 'MIN S0031'
from product
where sup_id='s0031'
/*5*/
select inStock from product where price > 80;
/*6*/
select cat_id,avg(onOrder) as 'Average onOrder'
from product
group by cat_id
order by avg(onOrder) ASC
/*7*/
select cat_id,max(onOrder) as 'Max Order'
from product
group by cat_id
having cat_id!=3
/*8*/
select sup_id,avg(onOrder) as 'Average onOrder' ,avg(inStock) as 'Average inStock'
from product
group by sup_id
order by avg(inStock) DESC
