facebook twitter youtube
in How & Why - 28 Apr, 2012
by Amit Gupta - 2 comments
database


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.

8 ) LIST OF ITEMS THOSE ARE SOLD TO GAIN MAX PROFIT.
This area is protected to registered users only.

9) LIST OF ‘FOOD’ ITEMS PURCHASED 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.

11) LIST OF CUSTOMERS WHO PURCHASED ‘FOOD’ ITEMS BUT NOT ‘SPORT’ OR ‘CLOTH’ ITEMS.
This area is protected to registered users only.

12) LIST OF CUSTOMERS WHO PURCHASE ITEMS OF SINGLE CATEGORY ONLY.
This area is protected to registered users only.

13) LIST OF SOLD ITEMS WHOESE QUOTED PRICE > 30.
This area is protected to registered users only.

14) LIST OF ITEMS OF ‘SPORT’ CATEGORY, whose PRICE IS greater than average PRICE of all items.
This area is protected to registered users only.

15) LIST OF SOLD ‘SPORT’ ITEMS.
This area is protected to registered users only.

Amit Gupta

Hey! this is Amit Gupta (amty). By profession, I am a Software Eng. And teaching is my passion. Sometimes I am a teacher, as you can see many technical tutorials on my site, sometimes I am a poet, And sometime just a friend of friends...

Leave a Reply

captcha

  • Dimitris Kalogirou //22 Jun 2012

    Hi amty,

    Great blog!
    Is there any email to contact you in private?

    Regards,
    Dimitris

  • Amit Gupta //02 Jul 2012

    you can directly contact me through contact us page. Since I am going on long vacation this time. So can can check all communications after 20july