SAMPLE
QUERY EXAMPLE WITH SOLUTION
Q1.
Create a database with your name and create tables
·
student(rollno,
name, branch, sem, contactno)
·
book(bookno,
title, author, publisher, cost)
·
transaction(bookno,
issuedto, issuedon, duedate,remarks)
write
the queries to:
1.
find
all the students of the same branch
2.
find
all the students of same sem
3.
find
all the books of same title
4.
find
all the books of same author
5.
find
all the books with cost<300
6.
find
all the books having a particular author and cost <300
7.
find
all the students having a particular book issued in their name
8.
find
all the students having book issued on a particular date
9.
find
all the students having a particular book issued on a particular date
10.find
all the books issued on a particular date from a particular publisher
SOLUTIONS:
mysql> source
mitali001.txt;
Query OK, 0 rows
affected (0.19 sec)
mysql> describe
student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| rollno | int(11) |
| | 0 |
|
| name | varchar(20) | |
| | |
| branch | varchar(5) |
| | |
|
| sem | int(11) |
| | 0 |
|
| contactno |
int(11) | |
| 0 | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.39
sec)
mysql> source
mitali002.txt;
Query OK, 1 row
affected (0.06 sec)
Query OK, 1 row
affected (0.03 sec)
Query OK, 1 row
affected (0.01 sec)
Query OK, 1 row
affected (0.03 sec)
Query OK, 1 row
affected (0.01 sec)
mysql> select *
from student;
+--------+---------+--------+-----+-----------+
| rollno | name | branch | sem | contactno |
+--------+---------+--------+-----+-----------+
| 7001 | aman | cse
| 5 | 4321098 |
| 7002 | anu | cse
| 5 | 4321398 |
| 7003 | srishti | cse | 5
| 4323578 |
| 7004 | sakshi | cse
| 5 | 4323688 |
| 7005 | mitali | cse
| 5 |
4321088 |
+--------+---------+--------+-----+-----------+
5 rows in set (0.03
sec)
mysql> source
mitali003.txt;
Query OK, 0 rows
affected (0.11 sec)
mysql> describe
book;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bookno | int(11) |
| | 0 |
|
| title | varchar(10) | |
| | |
| author | varchar(20) | |
| | |
| publisher |
varchar(30) | | |
| |
| cost | int(11) |
| | 0 |
|
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00
sec)
mysql> source
mitali004.txt;
Query OK, 1 row
affected (0.39 sec)
Query OK, 1 row
affected (0.02 sec)
Query OK, 1 row
affected (0.03 sec)
Query OK, 1 row
affected (0.01 sec)
Query OK, 1 row
affected (0.03 sec)
mysql> select
*from book;
+--------+-------+------------+--------------+------+
| bookno | title |
author | publisher | cost |
+--------+-------+------------+--------------+------+
| 101 | dbms
| korth | tata macgraw | 450 |
| 102 | dbms
| P.Bhatiya | ashok |
350 |
| 103 | daa
| Cormen | tata macgraw | 250 |
| 104 | daa
| N.Upadhaya | prachi | 195 |
| 105 | cg
| Baker | tata macgraw | 600 |
+--------+-------+------------+--------------+------+
5 rows in set (0.00
sec)
mysql> source
mitali005.txt;
Query OK, 0 rows
affected (0.09 sec)
mysql> describe
transcation;
+----------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+------------+-------+
| bookno | int(11)
| | | 0 | |
| issuedto |
int(11) | |
| 0 | |
| issuedon |
date | |
| 0000-00-00 | |
| duedate | date
| | | 0000-00-00 | |
| remark | varchar(20) | |
| | |
+----------+-------------+------+-----+------------+-------+
5 rows in set (0.00
sec)
mysql> source
mitali006.txt;
Query OK, 1 row
affected (0.05 sec)
Query OK, 1 row
affected (0.01 sec)
Query OK, 1 row
affected (0.02 sec)
Query OK, 1 row
affected (0.03 sec)
mysql> select *
from transcation;
+--------+----------+------------+------------+---------+
| bookno | issuedto |
issuedon | duedate | remark
|
+--------+----------+------------+------------+---------+
| 103 | 7002 | 2010-08-17 | 2010-08-27 | anu |
| 104 |
7005 | 2010-08-18 | 2010-08-28 | mitali
|
| 101 |
7003 | 2010-08-19 | 2010-08-29 | srishti |
| 105 |
7004 | 2010-08-19 | 2010-08-29 | sakshi
|
+--------+----------+------------+------------+---------+
4 rows in set (0.00
sec)
mysql> select *
from student where branch="cse";
+--------+---------+--------+-----+-----------+
| rollno | name | branch | sem | contactno |
+--------+---------+--------+-----+-----------+
| 7001 | aman | cse
| 5 | 4321098 |
| 7002 | anu | cse
| 5 | 4321398 |
| 7003 | srishti | cse | 5
| 4323578 |
| 7004 | sakshi | cse
| 5 | 4323688 |
| 7005 | mitali | cse
| 5 | 4321088 |
+--------+---------+--------+-----+-----------+
5 rows in set (0.38
sec)
mysql> select *
from student where sem=5;
+--------+---------+--------+-----+-----------+
| rollno | name | branch | sem | contactno |
+--------+---------+--------+-----+-----------+
| 7001 | aman | cse
| 5 |
4321098 |
| 7002 | anu | cse
| 5 | 4321398 |
| 7003 | srishti | cse | 5
| 4323578 |
| 7004 | sakshi | cse
| 5 | 4323688 |
| 7005 | mitali | cse
| 5 | 4321088 |
+--------+---------+--------+-----+-----------+
5 rows in set (0.00
sec)
mysql> select *
from book where title="dbms";
+--------+-------+-----------+--------------+------+
| bookno | title |
author | publisher | cost |
+--------+-------+-----------+--------------+------+
| 101 | dbms
| korth | tata macgraw | 450 |
| 102 | dbms
| P.Bhatiya | ashok | 350 |
+--------+-------+-----------+--------------+------+
2 rows in set (0.00
sec)
mysql> select *
from book where author="korth";
+--------+-------+--------+--------------+------+
| bookno | title |
author | publisher | cost |
+--------+-------+--------+--------------+------+
| 101 | dbms
| korth | tata macgraw | 450 |
+--------+-------+--------+--------------+------+
1 row in set (0.00
sec)
mysql> select *
from book where cost<300;
+--------+-------+------------+--------------+------+
| bookno | title |
author | publisher | cost |
+--------+-------+------------+--------------+------+
| 103 | daa
| Cormen | tata macgraw | 250 |
| 104 | daa
| N.Upadhaya | prachi | 195 |
+--------+-------+------------+--------------+------+
2 rows in set (0.00
sec)
mysql> select *
from book where cost<300 and author="cormen";
+--------+-------+--------+--------------+------+
| bookno | title |
author | publisher | cost |
+--------+-------+--------+--------------+------+
| 103 | daa
| Cormen | tata macgraw | 250 |
+--------+-------+--------+--------------+------+
1 row in set (0.00
sec)
mysql> select *
from student where rollno in (select bookno from book where
title="dbms");
Empty set (0.34 sec)
mysql> select *
from student where rollno in (select issuedto from transcation where bookno in
(select bookno from book where
title="dbms"));
+--------+---------+--------+-----+-----------+
| rollno | name | branch | sem | contactno |
+--------+---------+--------+-----+-----------+
| 7003 | srishti | cse | 5
| 4323578 |
+--------+---------+--------+-----+-----------+
1 row in set (0.00
sec)
mysql> select *
from student where rollno in (select rollno from transcation where
issuedon="2010-08-19");
+--------+---------+--------+-----+-----------+
| rollno | name | branch | sem | contactno |
+--------+---------+--------+-----+-----------+
| 7001 | aman | cse
| 5 | 4321098 |
| 7002 | anu | cse
| 5 | 4321398 |
| 7003 | srishti | cse | 5
| 4323578 |
| 7004 | sakshi | cse
| 5 | 4323688 |
| 7005 | mitali | cse
| 5 | 4321088 |
+--------+---------+--------+-----+-----------+
5 rows in set (0.00
sec)
mysql> select *
from student where rollno in (select issuedto from transcation where
issuedon="2010-08-19");
+--------+---------+--------+-----+-----------+
| rollno | name | branch | sem | contactno |
+--------+---------+--------+-----+-----------+
| 7003 | srishti | cse | 5
| 4323578 |
| 7004 | sakshi | cse
| 5 | 4323688 |
+--------+---------+--------+-----+-----------+
2 rows in set (0.00
sec)
mysql> select *
from student where rollno in (select issuedto from transcation where issuedon="2010-08-19"
in(select bookno from book
where
title="dbms"));
Empty set (0.02 sec)
mysql> select *
from student where rollno in (select issuedto from transcation where
issuedon="2010-08-19" and bookno in(select
bookno from book
where title="dbms"));
+--------+---------+--------+-----+-----------+
| rollno | name | branch | sem | contactno |
+--------+---------+--------+-----+-----------+
| 7003 | srishti | cse | 5
| 4323578 |
+--------+---------+--------+-----+-----------+
1 row in set (0.00
sec)
mysql> select *
from book where bookno in (select bookno from transcation where
issuedon="2010-08-18" and bookno in(select bookno
from book where
author="korth"));
Empty set (0.00 sec)
mysql> select *
from book where bookno in (select bookno from transcation where
issuedon="2010-08-17" and bookno in(select bookno
from book where
author="Cormen"));
+--------+-------+--------+--------------+------+
| bookno | title |
author | publisher | cost |
+--------+-------+--------+--------------+------+
| 103 | daa
| Cormen | tata macgraw | 250 |
+--------+-------+--------+--------------+------+
1 row in set (0.00
sec)
mysql> exit
Q2.
Consider the following relational database and write queries for each of the
following
·
Employee
(person-name, street, city)
·
Works
(person name, company name, salary)
·
Company
(Company name, city)
·
Managers
(person name, manager-name)
(a)
Find the names of all employees who work for First Bank Corporation.
(b)
Find the names and cities of residences of all employees who work for First
Bank Corporation.
(c)
Find the names of all the employees who do not work for First Bank Corporation.
(d)
Find names of all employees who earn more than $40000 per annum.
(e)
Find names of all employees who earn more than every employee of Small Bank
Corporation.
SOLUTIONS:
mysql> create
table employee(person_name varchar(30) not null,street varchar(50) not
null,city varchar(20) not null);
Query OK, 0 rows
affected (0.14 sec)
mysql> describe
employee;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| person_name |
varchar(30) | NO | |
| |
| street | varchar(50) | NO | |
| |
| city | varchar(20) | NO |
| | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.13
sec)
mysql> source
employee.txt;
Query OK, 10 rows
affected (0.08 sec)
Records: 10 Duplicates: 0
Warnings: 0
mysql> select *
from employee;
+-----------------+-----------------+------------+
| person_name | street | city |
+-----------------+-----------------+------------+
| Aunja Dixit | Baradari Road | Patiala
|
| Akash Mehra | D.F.Road | Patiala |
| Shambhavi Sinha |
G.T.Road | Ludhiana |
| Srishti Jain | Civil Line | Ludhiana |
| Sakshi Goel | Chandigarh Road | Ludhiana |
| Siddharth Anand |
Chandigarh Road | Ludhiana |
| Mirnalini Sinha |
Baradari Road | Patiala |
| Kunal Mishra | H.T.Road | Jalandhar |
| Zyana Sharma | G.B.Road | Chandigarh |
| Rozy Saluja | S.T.Street | Chandigarh |
+-----------------+-----------------+------------+
10 rows in set (0.05
sec)
mysql> show
tables;
+------------------+
| Tables_in_mitali |
+------------------+
| employee |
+------------------+
1 row in set (0.08
sec)
mysql> create
table works(person_name varchar(30) not null,comapny_name varchar(30) not
null,salary int not null);
Query OK, 0 rows
affected (0.48 sec)
mysql> show
tables;
+------------------+
| Tables_in_mitali |
+------------------+
| employee |
| works |
+------------------+
2 rows in set (0.00
sec)
mysql> describe
works;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| person_name | varchar(30) | NO |
| |
|
| comapny_name |
varchar(30) | NO | |
| |
| salary | int(11) | NO
| | |
|
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.06
sec)
mysql> create
table company(company_name varchar(30) not null,city varchar(20) not null);
Query OK, 0 rows
affected (0.11 sec)
mysql> show
tables;
+------------------+
| Tables_in_mitali |
+------------------+
| company |
| employee |
| works |
+------------------+
3 rows in set (0.00
sec)
mysql> describe
company;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| company_name |
varchar(30) | NO | |
| |
| city | varchar(20) | NO |
| | |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.02
sec)
mysql> source
company.txt;
Query OK, 8 rows affected
(0.08 sec)
Records: 8 Duplicates: 0
Warnings: 0
mysql> source
works.txt;
Query OK, 10 rows
affected (0.06 sec)
Records: 10 Duplicates: 0
Warnings: 0
mysql> create
table managers(person_name varchar(30) not null,manager_name varchar(30) not
null);
Query OK, 0 rows
affected (0.47 sec)
mysql> describe
managers;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| person_name | varchar(30) | NO |
| | |
| manager_name |
varchar(30) | NO | |
| |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.03
sec)
mysql> source
company.txt;
Query OK, 8 rows
affected (0.08 sec)
Records: 8 Duplicates: 0
Warnings: 0
mysql> select *
from company;
+------------------------+------------+
| company_name | city |
+------------------------+------------+
| Small Bank
corporation | Ludhiana |
| Infosis | New Delhi |
| Reliance | Chandigarh |
| BSNL | Noida |
| Hindustan
Petroleum | Gurgaon |
| Wipro | New Delhi |
| Tata Steel | Kanpur |
| First bank
Corporation | Chandigarh |
+------------------------+------------+
8 rows in set (0.00
sec)
mysql> source
works.txt;
Query OK, 10 rows
affected (0.08 sec)
Records: 10 Duplicates: 0
Warnings: 0
mysql> select *
from works;
+-----------------+------------------------+--------+
| person_name | comapny_name | salary |
+-----------------+------------------------+--------+
| Anuja Dixit | Wipro | 30000 |
| Akash Mehra | First Bank Corporation | 50000 |
| Shambhavi Sinha |
Hindustan Petroleum | 80000 |
| Srishti Jain | First Bank Corporation | 56000 |
| Sakshi Goel | Reliance | 62000 |
| Siddharth Anand |
Small Bank Corporation | 38000 |
| Mirnalini Sinha |
Tata Steel | 48000 |
| Kunal Mishra | BSNL | 39000 |
| Zyana Sharma | First Bank Corporation | 78000 |
| Rozy Saluja | Small Bank Corporation | 28000 |
+-----------------+------------------------+--------+
10 rows in set (0.00
sec)
mysql> select
person_name from works where comapny_name="First Bank corporation";
+--------------+
| person_name |
+--------------+
| Akash Mehra |
| Srishti Jain |
| Zyana Sharma |
+--------------+
3 rows in set (0.00
sec)
mysql> select
person_name and city from employee where person_name in(select person_name from
works where comapny_name="First Bank corporation");
+----------------------+
| person_name and
city |
+----------------------+
| 0 |
| 0 |
| 0 |
+----------------------+
3 rows in set (0.00
sec)
mysql> select
person_name, city from employee where person_name in(select person_name from
works where comapny_name="First Bank corporation");
+--------------+------------+
| person_name | city
|
+--------------+------------+
| Akash Mehra | Patiala
|
| Srishti Jain |
Ludhiana |
| Zyana Sharma |
Chandigarh |
+--------------+------------+
3 rows in set (0.00
sec)
mysql> select
person_name from works where comapny_name!="First Bank corporation";
+-----------------+
| person_name |
+-----------------+
| Anuja Dixit |
| Shambhavi Sinha |
| Sakshi Goel |
| Siddharth Anand |
| Mirnalini Sinha |
| Kunal Mishra |
| Rozy Saluja |
+-----------------+
7 rows in set (0.00
sec)
mysql> select
person_name from works where salary>40000;
+-----------------+
| person_name |
+-----------------+
| Akash Mehra |
| Shambhavi Sinha |
| Srishti Jain |
| Sakshi Goel |
| Mirnalini Sinha |
| Zyana Sharma |
+-----------------+
6 rows in set (0.00
sec)
mysql> select
person_name from works where salary>(select max(salary) from works where
comapny_name="small bank corporation");
+-----------------+
| person_name |
+-----------------+
| Akash Mehra |
| Shambhavi Sinha |
| Srishti Jain |
| Sakshi Goel |
| Mirnalini Sinha |
| Kunal Mishra |
| Zyana Sharma |
+-----------------+
7 rows in set (0.06
sec)
Q3.
Consider the following relational database and write queries for each of the
following
·
S(sno,
sname, city,status)
·
P(Pno,pname,
color,weight)
·
SP(Sno,Pno,Qty)
(a)
Find all the suppliers of ‘PATIALA’.
(b)
Get color of parts supplied by ‘S1’
(c)
Get supplier no who supplies maximum quantity
(d)
Find all suppliers who supply more than average quantity
(e)
Increase the quantity of part P1 by 10%
SOLUTION:
mysql> create
table S(sno varchar(10) not null primary key,sname varchar(30) not null,city
varchar(20) not null,status varchar(50) not null);
Query OK, 0 rows
affected (0.08 sec)
mysql> create
table P(pno varchar(10) not null primary key,color varchar(30) not null,weight
int not null);
Query OK, 0 rows
affected (0.09 sec)
mysql> describe S;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sno | varchar(10) | NO | PRI | |
|
| sname | varchar(30) | NO |
| | |
| city | varchar(20) | NO |
| | |
| status | varchar(50)
| NO | |
| |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00
sec)
mysql> source
S.txt;
Query OK, 8 rows
affected (0.08 sec)
Records: 8 Duplicates: 0
Warnings: 0
mysql> select *
from S;
+-----+----------------+------------+-------------------+
| sno | sname | city | status |
+-----+----------------+------------+-------------------+
| S1 | R K Singh | Patiala | advanced received |
| S2 | P K Singh | Ludhiana | full received |
| S3 | R Y Gill | Chandigarh | 75% received |
| S4 | R M Tripathi | Patiala
| advanced received |
| S5 | Aarti Singla | Patiala
| 60% received |
| S6 | Deepika Thakur | Amritsar | advanced received |
| S7 | Abhinav Sharma | Jalandhar | 38% received |
| S8 | Sumandeep Kaur | Patiala | advanced received |
+-----+----------------+------------+-------------------+
8 rows in set (0.00
sec)
mysql> describe P;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| pno | varchar(10) | NO | PRI | |
|
| color | varchar(30) | NO |
| | |
| weight |
int(11) | NO |
| | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.38
sec)
mysql> source
P.txt;
Query OK, 8 rows
affected (0.06 sec)
Records: 8 Duplicates: 0
Warnings: 0
mysql> select *
from P;
+-----+-------------+--------+
| pno | color | weight |
+-----+-------------+--------+
| P1 | Red
| 60 |
| P2 | Magenta
| 60 |
| P3 | Cyan
| 60 |
| P4 | Aqua
| 60 |
| P5 | Purple
| 60 |
| P6 | Olive Green | 60 |
| P7 | Tan
| 60 |
| P8 | Black
| 60 |
+-----+-------------+--------+
8 rows in set (0.00
sec)
mysql> create
table SP(sno varchar(10) not null,pno varchar(10) not null,qty int not null);
Query OK, 0 rows
affected (0.45 sec)
mysql> describe
SP;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(10) | NO |
| |
|
| pno | varchar(10) | NO |
| | |
| qty | int(11)
| NO | |
| |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00
sec)
mysql> source
SP.txt;
Query OK, 8 rows
affected (0.05 sec)
Records: 8 Duplicates: 0
Warnings: 0
mysql> select *
from SP;
+-----+-----+-----+
| sno | pno | qty |
+-----+-----+-----+
| S1 | P1
| 24 |
| S2 | P2
| 17 |
| S3 | P3
| 20 |
| S4 | P4
| 23 |
| S5 | P5
| 8 |
| S6 | P6
| 5 |
| S7 | P7
| 29 |
| S8 | P8
| 9 |
+-----+-----+-----+
8 rows in set (0.00
sec)
mysql> select
sname from S where city="Patiala";
+----------------+
| sname |
+----------------+
| R K Singh |
| R M Tripathi |
| Aarti Singla |
| Sumandeep Kaur |
+----------------+
4 rows in set (0.00
sec)
mysql> delete from
SP;
Query OK, 8 rows
affected (0.08 sec)
mysql> source
SP.txt;
Query OK, 8 rows
affected (0.06 sec)
Records: 8 Duplicates: 0
Warnings: 0
mysql>
mysql> select *
from SP;
+-------+-----+-----+
| sno | pno | qty |
+-------+-----+-----+
| S1 | P1
| 24 |
| S2,S1 | P2 | 17 |
| S3 | P3
| 20 |
| S4,S1 | P4 | 23 |
| S5 | P5
| 8 |
| S6 | P6
| 5 |
| S7,S2 | P7 | 29 |
| S8,S4 | P8 | 9 |
+-------+-----+-----+
8 rows in set (0.00
sec)
mysql> select
color from P where pno in(select pno from SP where sno like "%S1%");
+---------+
| color |
+---------+
| Red |
| Magenta |
| Aqua |
+---------+
3 rows in set (0.05
sec)
mysql> select color
from P where pno in(select pno from SP where sno like "%S1%");
+---------+
| color |
+---------+
| Red |
| Magenta |
| Aqua |
+---------+
3 rows in set (0.05
sec)
mysql> exit;
Comments
Post a Comment