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
This area is protected to registered users only.
2) LIST OF ITEMS OF ‘SPORT’ & ‘FOOD’ CATEGORIES
This area is protected to registered users only.
3) LIST OF ITEMS OF ‘SPORT’ CATEGORY, whose PRICE IS greater than average PRICE of all items.
This area is protected to registered users only.
4) LIST OF SOLD ‘SPORT’ ITEMS.
This area is protected to registered users only.
5) LIST OF ITEMS SOLD TO ‘AMIT’.
This area is protected to registered users only.
6) LIST OF ITEMS SOLD IN LAST WEEK.
This area is protected to registered users only.
7) LIST OF ‘FOOD’ ITEMS SOLD IN LAST WEEK.
This area is protected to registered users only.
LIST OF ITEMS THOSE ARE SOLD TO GAIN MAX PROFIT. 9) LIST OF ‘FOOD’ ITEMS PURCHSED 1 MONTH AGO.
This area is protected to registered users only.
10) LIST OF ‘FOOD’ ITEMS THOSE ARE NOT SOLD IN LAST WEEK.
This area is protected to registered users only.
14) LIST OF CUSTOMERS WHO PURCHASED ‘FOOD’ ITEMS BUT NOT ‘SPORT’ OR ‘CLOTH’ ITEMS. 15) LIST OF CUSTOMERS WHO PURCHASE ITEMS OF SINGLE CATEGORY ONLY. 16) LIST OF SOLD ITEMS WHOESE QUOTED PRICE > 30.
This area is protected to registered users only.
I’ll try to update more queries in my other articles.
