Archive

Archive for the ‘SQL’ Category

SQL – N rows

January 19, 2016 Leave a comment

Fetch starting N rows

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num < N+1 with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num < N+1

Some DBs has function like Top and limit

Fetch Nth row

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num = N with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num = N

Fetch Nth to Mth rows

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num between 1003 and 1118 with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num between 1003 and 1118
Advertisements
Categories: SQL Tags: , ,

SQL exercise – Learn by practice

January 19, 2016 1 comment

There is an example , some questions, and corresponding queries. These queries are written in Oracle. But it’ll give you clear logic about how to write SQL queries in any sort of SQL like DB2, TSQL, MySql etc. I constructed this exercise during my training. Although Some questions do not having solution. So those will be an exercise for you. If i get time then will update them later.

First of all you need to create tables in your database. Use above “ready to use” code.

--################ DDL #######################
 create table stock
 (item_no varchar2(5) primary key,
 item_name varchar2(15) not null,
 Quoted_price number(6,2),
 Category varchar2(5));
create table category
 (c_code varchar2(5) primary key,
 c_name varchar2(15) not null unique);
alter table stock
 add constraint s_fk Foreign key(category) references Category(c_code) on delete cascade ;
create table purchase
 (p_no varchar2(5) PRIMARY KEY,
 item_no varchar2(5) references stock(item_no),
 p_price number(6,2) not null,
 p_date date,
 p_qty number(3));
create table sale
 (s_no varchar2(5) PRIMARY KEY,
 item_no varchar2(5) references stock(item_no),
 s_price number(6,2) not null,
 s_date date,
 s_qty number(3),
 cust_name varchar2(15)) ;

Now you need to populate your tables with some dummy but meaningful data. So you can test your queries.

--############### DML ##################### BEGIN insert into CATEGORY VALUES('C101','FOOD'); insert into CATEGORY VALUES('C102','SPORT'); insert into CATEGORY VALUES('C103','ELEX'); insert into CATEGORY VALUES('C104','CLOTH'); insert into CATEGORY VALUES('C105','OTHER'); END ;
BEGIN
 insert into stock values('i101','BREAD',17,'C101');
 insert into stock values('i102','BUTTER',67,'C101');
 insert into stock values('i103','BISCUIT',87,'C101');
 insert into stock values('i104','GEM',56,'C101');
 insert into stock values('i105','CHOCOLATE',17,'C101');
 insert into stock values('i106','EGG',5,'C101');
 insert into stock values('i107','SALT',8,'C101');
 insert into stock values('i108','BALL',27,'C102');
 insert into stock values('i109','BAT',217,'C102');
 insert into stock values('i110','FOOTBALL',117,'C102');
 insert into stock values('i111','WICKETS',97,'C102');
 insert into stock values('i112','CAP',111,'C102');
 insert into stock values('i113','TSHIRT',1011,'C104');
 END
BEGIN
 insert into PURCHASE values('P101','i101',15,'01-nov-2008',16);
 insert into PURCHASE values('P102','i102',65,'01-nov-2008',34);
 insert into PURCHASE values('P103','i103',85,'01-nov-2008',12);
 insert into PURCHASE values('P104','i104',56,'01-nov-2008',35);
 insert into PURCHASE values('P105','i105',17,'01-nov-2008',23);
 insert into PURCHASE values('P106','i106',4,'01-nov-2008',34);
 insert into PURCHASE values('P107','i107',7,'01-oct-2008',23);
 insert into PURCHASE values('P108','i108',25,'01-oct-2008',56);
 insert into PURCHASE values('P109','i109',201,'01-sep-2008',67);
 insert into PURCHASE values('P110','i110',100,'01-oct-2008',54);
 insert into PURCHASE values('P111','i111',90,'01-mar-2008',23);
 insert into PURCHASE values('P112','i112',100,'01-feb-2008',12);
 insert into PURCHASE values('P113','i113',800,'01-mar-2008',6);
 END;
BEGIN
 insert into SALE values('S101','i101',19,'01-DEC-2008',6,'AMIT');
 insert into SALE values('S102','i102',65,'02-DEC-2008',14,'ROHIT');
 insert into SALE values('S103','i103',86,'05-DEC-2008',2,'AMIT');
 insert into SALE values('S104','i104',63,'27-nov-2008',12,'SHYAM');
 insert into SALE values('S105','i105',21,'16-nov-2008',3,'KAMAL');
 insert into SALE values('S106','i106',6,'13-DEC-2008',4,'PREETI');
 insert into SALE values('S107','i103',88,'17-nov-2008',2,'RAHUL');
 insert into SALE values('S108','i104',57,'19-nov-2008',5,'RAHUL');
 insert into SALE values('S109','i105',19,'28-nov-2008',2,'SOHAN');
 insert into SALE values('S110','i111',115,'12-DEC-2008',3,'SHAALU');
 insert into SALE values('S111','i112',145,'17-NOV-2008',8,'SHAALU');
 insert into SALE values('S113','i113',1300,'17-FEB-2008',2,'UMESH');
 insert into SALE values('S112','i112',120,'01-MAR-2008',4,'SHYAM');
 insert into SALE values('S114','i112',130,'06-OCT-2008',2,'KAMAL');
 END;

Now this is the time to practice. See given examples and solved queries. First try all queries given below. Then login to this article-stack to find out their solution.

1) LIST OF ITEMS OF ‘SPORT’ CATEGORY

A)

SELECT *
FROM STOCK , (SELECT * FROM CATEGORY
WHERE CATEGORY.C_NAME='SPORT') CAT
WHERE CAT.C_CODE=STOCK.CATEGORY;

B)

SELECT * FROM STOCK JOIN CATEGORY
ON (STOCK.CATEGORY=CATEGORY.C_CODE
AND CATEGORY.C_NAME='SPORT')

2) LIST OF ITEMS OF ‘SPORT’ & ‘FOOD’ CATEGORIES

A)

SELECT * FROM STOCK JOIN CATEGORY
ON (STOCK.CATEGORY=CATEGORY.C_CODE
AND CATEGORY.C_NAME in ('SPORT','FOOD'))

3) LIST OF ITEMS OF ‘SPORT’ CATEGORY, whose PRICE IS greater than average PRICE of all items.

select * from (select * from STOCK
WHERE STOCK.QUOTED_PRICE > ( SELECT AVG(QUOTED_PRICE)
FROM STOCK)) STK
JOIN CATEGORY ON (CATEGORY.C_CODE=STK.CATEGORY
AND CATEGORY.C_NAME='SPORT')

4) LIST OF SOLD ‘SPORT’ ITEMS.

a)

select * from sale NATURAL JOIN
(select * from stock JOIN category ON
(stock.category=category.c_code
and category.c_name='SPORT'));

b)

select *
from sale JOIN STOCK
ON (SALE.ITEM_NO=STOCK.ITEM_NO) JOIN CATEGORY
ON (stock.category=category.c_code and category.c_name='SPORT');


C)

SELECT *
FROM SALE NATURAL JOIN STOCK
JOIN CATEGORY
ON (CATEGORY.C_CODE=STOCK.CATEGORY
AND CATEGORY.C_NAME='FOOD')

5) LIST OF ITEMS SOLD TO ‘AMIT’.

A)

SELECT *
FROM STOCK JOIN SALE
ON ( STOCK.ITEM_NO=SALE.ITEM_NO AND SALE.CUST_NAME='AMIT');

B)

SELECT *
FROM STOCK NATURAL JOIN SALE
WHERE SALE.CUST_NAME='AMIT'

6) LIST OF ITEMS SOLD IN LAST WEEK.

SELECT *
FROM SALE NATURAL JOIN STOCK
WHERE SALE.S_DATE > (CURRENT_DATE-7)

7) LIST OF ‘FOOD’ ITEMS SOLD IN LAST WEEK.

SELECT *
FROM SALE NATURAL JOIN STOCK
JOIN CATEGORY
ON (CATEGORY.C_CODE=STOCK.CATEGORY
AND CATEGORY.C_NAME='FOOD')
WHERE SALE.S_DATE > (CURRENT_DATE-7)

8 ) LIST OF ITEMS THOSE ARE SOLD TO GAIN MAX PROFIT.

with price_diff as
(
Select s.item_no item_no,(s_price - p_price) diff
from
	(select ITEM_NO,avg(S_PRICE) s_price
	from sale
	group by ITEM_NO) s
join
	(select ITEM_NO,avg(P_PRICE) p_price
	from purchase
	group by ITEM_NO) p
on (p.item_no = s.item_no)
)
select item_no,diff
from price_diff
where diff = (select max(diff) from price_diff)
;

9) LIST OF ‘FOOD’ ITEMS PURCHASED 1 MONTH AGO.

 SELECT *
 FROM PURCHASE NATURAL JOIN STOCK
 WHERE PURCHASE.P_DATE < ADD_MONTHS(CURRENT_DATE,-1)

10) LIST OF ‘FOOD’ ITEMS THOSE ARE NOT SOLD IN LAST WEEK.

SELECT *
FROM SALE NATURAL JOIN STOCK
JOIN CATEGORY
ON (CATEGORY.C_CODE=STOCK.CATEGORY
AND CATEGORY.C_NAME='FOOD')
WHERE SALE.S_DATE < (CURRENT_DATE-7)

11) LIST OF CUSTOMERS WHO PURCHASED ‘FOOD’ ITEMS BUT NOT ‘SPORT’ OR ‘CLOTH’ ITEMS.

A)

select distinct cust_name
from sale s1 
join
	(select *
	from stock join category
	on (Category= c_code and c_name in ('FOOD'))
	) s2
on (s1.item_no = s2.item_no )
where cust_name not in (
	select distinct cust_name
	from sale s1 
	join
		(select *
		from stock 
		join category
		on (Category= c_code and c_name in ('SPORT','CLOTH'))
		) s2
	on (s1.item_no = s2.item_no )
	)

B)

with cust_cat as
(select cust_name, c_name
from sale s1 
join
	(select item_no,c_name
	from stock join category
	on (Category= c_code)
	) s2
on (s1.item_no = s2.item_no )
group by cust_name, c_name
)
select cust_name
from cust_cat
where c_name = 'FOOD' and cust_name not in
	(select cust_name
	from cust_cat
	where c_name in ('SPORT','CLOTH')
	);

12) LIST OF CUSTOMERS WHO PURCHASE ITEMS OF SINGLE CATEGORY ONLY.

select cust_name
from sale s1 
join
	(select *
	from stock join category
	on (Category= c_code)
	) s2
on (s1.item_no = s2.item_no )
group by cust_name
having count(c_name) = 1

13) LIST OF SOLD ITEMS WHOESE QUOTED PRICE > 30.

SELECT *
FROM SALE NATURAL JOIN STOCK
WHERE STOCK.QUOTED_PRICE > 30;

14) LIST OF ITEMS OF ‘SPORT’ CATEGORY, whose PRICE IS greater than average PRICE of all items.

select *
from stock join category
on (Category = c_code and c_name in ('SPORT') )
where Quoted_price > (select avg(Quoted_price) from stock);

15) LIST OF SOLD ‘SPORT’ ITEMS.

Select *
from sale s1 join
(select *
from stock join category
on (Category= c_code and c_name in ('SPORT')) ) s2
on (s1.item_no = s2.item_no);
Categories: SQL, Tutorial Tags: ,
%d bloggers like this: