STRUCTURE

QUERY
LANGUAGE
(SQL)
 STRUCTURE QUERY LANGUAGE:

Assignment Assignment Signature

No Name Date &
Remarks

1 Assignment 1

2 Assignment 2

3 Assignment 3

4 Assignment 4

5 Assignment 5
 ASSIGNMENT-1
DATE-

PROBLEM:

Consider the following schema of a relational database:

EMPLOYEE(EMP_NO,EMP_NAME,MANAGER_NO,SALARY)

Create table through appropriate SQL command. Define all integrity constraints and
enter sufficient data through user friendly from design. Write SQL command and
output.
1. Retrieve the names of the employees and names of their
respective managers from the employee table.
2. Retrieve the names of the employee who is earning second
maximum salary.
3. Retrieve the names of the employees whose salary is greater than
the salary of all employees whose manager no. is 7.
4. Get the details of all employees whose salary is greater than the
average salary of the employee.

TABLE CREATION:

SQL> CREATE TABLE EMPLOYEE (EMP_NO NUMBER(4),EMP_NAME

VARCHAR2(30),MANAGER_NO NUMBER(4),SALARY NUMBER(10));
Table created.
SQL> DESCRIBE EMPLOYEE;

Name Null? Type

----------- ---------- -----------------
EMP_NO NUMBER (4)
EMP_NAME VARCHAR2 (30)
MANAGER_NO NUMBER (4)
SALARY NUMBER (10)

TABLE AFTER INSERTION OF THE VALUES:

SQL> SELECT * FROM EMPLOYEE;

EMP_NO EMP_NAME MANAGER_NO SALARY

1 [Link] 1 80000
2 M. Datta 7 56000
3 D. Das 5 50000
4 M. Deb 1 60000
5 T. Sikdar 5 47000
6 A. Datta 1 71000
7 A. Jana 7 53000
8 K. Manna 5 59000
9 M. Bose 7 53000
10 M. Banerjee 1 79000

10 rows selected.

QUERIES:
(1) Retrieve The Names Of The Employees And Names Of Their Respective
Managers From The Employee Table.

SQL> SELECT EMPLOYEE.EMP_NAME,A.EMP_NAME AS MANAGER_NAME FROM

EMPLOYEE,(SELECT EMP_NO,EMP_NAME FROM EMPLOYEE WHERE EMP_NO IN MANAGER_NO)A
WHERE EMPLOYEE.MANAGER_NO=A.EMP_NO;

EMP_NAME MANAGER_NAME
------------------ ---------------------------
A. Paul A. Paul
M. Deb A. Paul
A. Datta A. Paul
M. Banerjee A. Paul
D. Das T. Sikdar
K. Manna T. Sikdar
T. Sikdar T. Sikdar
M. Datta A. Jana
M. Bose A. Jana
A. Jana A. Jana

10 rows selected.
(2) Retrieve The Names Of The Employee Who Is Earning Second Maximum Salary.

SQL> SELECT EMP_NAME FROM EMPLOYEE WHERE SALARY= ( SELECT MAX(SALARY) FROM
EMPLOYEE WHERE SALARY<(SELECT MAX(SALARY) FROM EMPLOYEE));

EMP_NAME
------------------
M. Banerjee
(3) Retrieve the names of the employees whose salary is greater than the salary of
all employees whose manager no. is=7.

SQL> SELECT EMP_NAME FROM EMPLOYEE WHERE SALARY>(SELECT MAX(SALARY) FROM

EMPLOYEE WHERE MANAGER_NO=7 GROUP BY MANAGER_NO);

EMP_NAME
---------------
A. Paul M. Deb A. Datta
K. Manna M. Banerjee

(4) Get the details of all employees whose salary is greater than the average salary
of the employee.

SQL> SELECT * FROM EMPLOYEE WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEE);

EMP_NO EMP_NAME MANAGER_NO SALARY

1 A. Paul 1 80000
6 A. Datta 1 71000
1 M. Banerjee 1 79000

________________
Teacher Signature
 ASSIGNMENT-2
DATE-

PROBLEM:

Consider the following schema of a relational database:

EMPLOYEE (EMP_NO, EMP_NAME, DEPT_NO, MANAGER)
DEPARTMENT (DEPT_NO, DEPT_NAME, DEPT_HEAD)
PROJECT(PRO_CODE,PRO_NAME,START_DATE, END_DATE,PRO_MANAGER)
WORKSON (EMP_NO, PRO_CODE)

Create table through appropriate SQL command. Define all integrity constraints
and enter sufficient data through user friendly from design. Write SQL command
and output.

1. Display the name of project manager who are also department

head.
2. Display all projects sorted by project name. Specify who manages each
project, which employee work on it and what department the project
manager is in. Within a project sort employees by employee name.
3. List the employees who have no project management
responsibilities.
TABLE CREATION:

SQL> CREATE TABLE DEPARTMENT (DEPT_NO NUMBER(5)PRIMARY

KEY,DEPT_NAME VARCHAR2(30),DEPT_HEAD VARCHAR2(40));
Table created.
SQL> CREATE TABLE EMPLOYEE(EMP_NO NUMBER(5)PRIMARY
KEY,EMP_NAME VARCHAR2(40),DEPT_NO NUMBER(5)REFERENCES
DEPARTMENT1(DEPT_NO),MANAGER VARCHAR2(40));
Table created.
SQL> CREATE TABLE PROJECT (PRO_CODE NUMBER(5)PRIMARY
KEY,PRO_NAME VARCHAR2(60),START_DATE DATE,END_DATE
DATE,PRO_MANAGER VARCHAR2(40));
Table created.
SQL> CREATE TABLE WORKSON(EMP_NO NUMBER(5)REFERENCES
EMPLOYEE(EMP_NO),PRO_CODE NUMBER(5)REFERENCES
PROJECT(PRO_CODE));
Table created.
SQL> DESCRIBE DEPARTMENT;

Name Null? Type

DEPT_NO NOT NULL NUMBER(5)
DEPT_NAME NOT NULL VARCHAR2(30)
DEPT_HEAD NOT NULL VARCHAR2(40)

SQL> DESCRIBE EMPLOYEE;

Name Null? Type

EMP_NO NOT NULL NUMBER(5)
EMP_NAME NOT NULL VARCHAR2(40)
DEPT_NO NOT NULL NUMBER(5)
MANAGER NOT NULL VARCHAR2(40)

SQL> DESCRIBE PROJECT;

Name Null? Type

PRO_CODE NOT NULL NUMBER(5)
PRO_NAME NOT NULL VARCHAR2(60)
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
PRO_MANAGER NOT NULL VARCHAR2(40)

SQL> DESCRIBE WORKSON;

Name Null? Type

EMP_NO NOT NULL NUMBER(5)
PRO_CODE NOT NULL NUMBER(5)

TABLE AFTER INSERTION OF THE VALUES:

SQL> SELECT * FROM DEPARTMENT;

DEPT_NO DEPT_NAME DEPT_HEAD

1 Computer Science A. D. K.
2 Physics A. Datta
3 Electronics D. Singha
4 Geography M. Datta
5 Chemistry S. Paul
 SQL> SELECT * FROM EMPLOYEE;

EMP_NO EMP_NAME DEPT_NO MANAGER

1 M. Deb 1 S. Hazra
2 P. Roy 1 S. Hazra
3 D. Das 3 A. Jana
4 T. Sikdar 1 S. Hazra
5 A. Saha 2 A. Roy
6 B. Roy 1 S. Hazra
7 K. Majumder 3 A. Jana
8 P. Saha 1 S. Hazra
9 P. Hazra 1 S. Hazra
10 P. Bedi 2 A. Roy
11 K. Manna 3 A. Jana
12 A. Roy 2 B. Jana
13 S. Bose 4 D. Roy
14 A. Jana 3 P. Bose
15 P. Mishra 2 A. Roy
16 T. Paul 2 A. Roy
17 N. Mukherjee 5 A. Roy
18 S. Sarkar 4 D. Roy
19 R. Bose 5 N. Mukherjee
20 D. Roy 4 P. Roy
21 S. Thakur 5 N. Mukherjee
22 J. Verma 3 A. Jana
23 S. paul 4 D. Roy
24 D. Bindra 5 N. Mukherjee
25 R. Roy 5 N. Mukherjee
26 J. Deb 4 D. Roy
27 D. Paul 5 N. Mukherjee

27 rows selected.
SQL> SELECT * FROM PROJECT;

PRO_CODE PRO_NAME
PRO_NAMESTART_DATE
START_DATE END_DATE PRO_MANAGER
10 Visual Basic 25-JAN-07 30-SEP-07 A. D. K.
50 Flip Flop 15-OCT-08 01-JAN-09 A. Jana
22 C++ 10-MAR-08 13-DEC-08 A. Paul
76 Chemistry 04-MAR-09 15-NOV-09 R. Bose
100 Optics 03-APR-09 10-MAY-09 T. Paul
56 Environment 10-JAN-10 05-MAR-10 S. Paul
 89 Astro Physics 24-MAY-10 30-JUL-10 A. Saha
60 GIS 10-SEP-10 25-JAN-11 D. Roy
15 Bonding 14-JAN-10 27-MAR-10 N. Mukherjee
32 Microprocessor 12-DEC-11 01-JAN-12 D. Das

10 rows selected.

QUERIES:

(1) Display the name of project manager who are also department
head.
SQL> SELECT P.PRO_MANAGER FROM PROJECT P, DEPARTMENT1 D WHERE
D.DEPT_HEAD=P.PRO_MANAGER;

PRO_MANAGER
----------------------
A. D. K.

(2) Display all projects sorted by project name. Specify who manages each
project, which employee work on it and what department the project
manager is in. Within a project sort employees by employee name.

SQL> SELECT P.PRO_NAME, P.PRO_MANAGER, E.EMP_NAME, D.DEPT_NAME FROM

EMPLOYEE E, PROJECT P, WORKSON W, DEPARTMENT1 D WHERE
P.PRO_CODE=W.PRO_CODE AND W.EMP_NO=E.EMP_NO AND
D.DEPT_NO=E.DEPT_NO ORDER BY PRO_NAME, EMP_NAME;

PRO_NAME PRO_MANAGER EMP_NAME DEPT_NAME

Bonding N. Mukherjee R. Roy Chemistry Visual Basic
Programming Language A. Paul A. Paul Computer Science
Programming Language A. Paul A. Jana Physics Organic
Environment M. Deb P. Bedi Geography
GIS S. paul D. Paul Geography Flip Flop
Microprocessor D. Roy T. Sikdar Electronics
Chemistry D. Das K. Manna Chemistry

10 rows selected.

(3) List the employees who have no project management

responsibilities.
SQL> SELECT EMP_NAME FROM EMPLOYEE WHERE EMP_NAME NOT
IN(SELECT E.EMP_NAME FROM EMPLOYEE E,WORKSON W WHERE
E.EMP_NO=W.EMP_NO);

EMP_NAME
----------------
A. Saha B. Roy K.
Majumder P. Saha S.
Hazra A. Roy
S. Bose A. Jana P.
Mishra T. Paul
N. Mukherjee

EMP_NAME
----------------
S. Sarkar R. Bose S.
Thakur J. Verma D.
Bindra J. Deb
17 rows selected.

_______________
Teacher Signature
 ASSIGNMENT-3
DATE-
PROBLEM:
Consider the following schema of a relational database:
EMPLOYEE (E_CODE, E_NAME, STREET, CITY, SALARY)
COMPANY (C_CODE, C_NAME, CITY)
WORKS (E_CODE, C_CODE)
MANAGES (E_CODE, E_NAME, MANAGER_NAME)
Create table through appropriate SQL command. Define all integrity constraints and
enter sufficient data through user friendly from design. Write SQL command and
output.

1. Find the names of all employees who work for FIRST BANK
CORPORATION.
2. Find the names and cities of residence of all companies who work for
FIRST BANK CORPORATION.
3. Find the names, street and cities of residence of all employees who
work for FIRST BANK CORPORATION and earn more than 50000 per
month.
4. Find all employees who live in the city where the company for
which they work is located.
5. Find all employees who live in the same city and on the same street as
their manager.
6. Find the employees in the database who earn more than every
employee of SMALL BANK CORPORATION.
7. Find all employees in the database who don’t work for FIRST BANK
CORPORATION.
8. Assume that the companies may be located in several cities. Find all
companies located in every city in which FIRST BANK CORPORATION is
located.
TABLE CREATION:

SQL> CREATE TABLE EMPLOYEE(E_CODE NUMBER(5)PRIMARY KEY,E_NAME

VARCHAR2(40),STREET VARCHAR2(40),CITY VARCHAR2(40),SALARY NUMBER(20));
Table created.
SQL> CREATE TABLE COMPANY (C_CODE NUMBER(5)PRIMARY KEY,C_NAME
VARCHAR2(40),CITY VARCHAR2(40));
Table created.
SQL>CREATETABLEWORKS(E_CODENUMBER(5)REFERENCES
EMPLOYEE0(E_CODE),C_CODENUMBER(5)REFERENCES COMPANY(C_CODE));
Table created.
SQL> CREATE TABLE MANAGES (E_CODE NUMBER(5)REFERENCES
EMPLOYEE0(E_CODE),E_NAME VARCHAR2(40),MANAGER_NAME
VARCHAR2(40));
Table created.
SQL> DESCRIBE EMPLOYEE;

Name Null? Type

E_CODE NOT NULL NUMBER(5)
E_NAME NOT NULL VARCHAR2(40)
STREET NOT NULL VARCHAR2(40)
CITY NOT NULL VARCHAR2(40)
SALARY NOT NULL NUMBER(20)

SQL> DESCRIBE COMPANY;

Name Null? Type

C_CODE NOT NULL NUMBER(5)
C_NAME NOT NULL VARCHAR2(40)
CITY NOT NULL VARCHAR2(40)

SQL> DESCRIBE MANAGES;

Name Null? Type

E_CODE NOT NULL NUMBER(5)
E_NAME NOT NULL VARCHAR2(40)
MANAGER_NAME NOT NULL VARCHAR2(40)

TABLE AFTER INSERTION OF THE VALUES:

SQL> SELECT * FROM EMPLOYEE;

E_NAM STREET CITY SALARY
E_CODE
E
1 A. Paul Bidhan Nagar Road Kolkata 50000
2 M. Deb Chilly Road Delhi 45000
3 D. Das Mrinalini Road Kolkata 30000
4 T. Sikdar Rajarhaat Street Bangalore 50000
5 S. Das RK Narayana Street Mumbai 30000
6 A. Datta Bidhan Nagar Road Kolkata 60000
7 K. Manna AKB Road Delhi 48000
8 N. Roy Ashutosh Sarani Kolkata 57000
9 J. Dey Fabe Road Mumbai 38000
10 D. Paul Kestopur Road Kolkata 56000
11 M. Raut Chilly Road Delhi 35000
12 E. Sheik Mall Road Mumbai 49000
12 rows selected.

SQL> SELECT * FROM COMPANY;

C_CODE C_NAME CITY

100 FIRST BANK CORPORATION Kolkata
101 SMALL BANK CORPORATION Delhi
102 COGNIZANT Mumbai
103 IBM Kolkata

SQL> SELECT * FROM WORKS;

E_CODE C_CODE
1 100
3 102
5 103
2 101
10 100
11 101
6 100
9 102
4 101
7 103
8 102
12 100

12 rows selected.

SQL> SELECT * FROM MANAGES;

E_CODE E_NAME MANAGER_NAME

2 M. Deb M. Raut
1 A. Paul A. Pal
9 J. Dey D. Das
7 K. Manna M. Chatterjee
12 E. Sheik A. Paul
4 T. Sikdar M. Raut
8 N. Roy D. Das
10 D. Paul A. Paul
11 M. Raut P. Roy
5 S. Das K. Manna
3 D. Das B. Dutta
6 A. Datta A. Paul
12 rows selected.
QUERIES:

(1) Find the names of all employees who work for FIRST BANK
CORPORATION.

SQL> SELECT E_NAME FROM EMPLOYEE, COMPANY, WORKS WHERE

EMPLOYEE.E_CODE=WORKS.E_CODE AND COMPANY.C_CODE=WORKS.C_CODE AND
COMPANY.C_NAME='FIRST BANK CORPORATION';

E_NAME
--------------
A. Paul D. Paul A.
Datta E. Sheik

(2) Find the names and cities of residence of all companies who work for FIRST
BANK CORPORATION.

SQL> SELECT E_NAME, [Link] FROM EMPLOYEE, COMPANY, WORKS WHERE

EMPLOYEE.E_CODE=WORKS.E_CODE AND COMPANY.C_CODE=WORKS.C_CODE AND
COMPANY.C_NAME='FIRST BANK CORPORATION';

E_NAME CITY
A. Paul Kolkata
D. Paul Kolkata
A. Datta Kolkata
E. Sheik Mumbai

(3) Find the names, street and cities of residence of all employees who work for
FIRST BANK CORPORATION and earn more than 50000 per month.

SQL> SELECT E_NAME, STREET, [Link] FROM EMPLOYEE, COMPANY,

WORKS WHERE EMPLOYEE.E_CODE=WORKS.E_CODE AND
COMPANY.C_CODE=WORKS.C_CODE AND COMPANY.C_NAME='FIRST BANK
CORPORATION' AND SALARY>50000;

E_NAME STREET CITY

D. Paul Kestopur Road Kolkata
A. Datta Haatiyara Street Kolkata

(4) Find all employees who live in the city where the company for
which they work is located.
SQL> SELECT E_NAME FROM EMPLOYEE, COMPANY, WORKS WHERE
EMPLOYEE.E_CODE=WORKS.E_CODE AND COMPANY.C_CODE=WORKS.C_CODE AND
[Link]=[Link];

E_NAME
-------------
A. Paul M. Deb D. Paul
M. Raut A. Datta J. Dey
6 rows selected.

(5) Find all employees who live in the same city and on the same street as their
manager.

SQL> SELECT P.E_NAME FROM(SELECT

E.E_NAME,M.MANAGER_NAME,[Link],[Link] FROM EMPLOYEE E,MANAGES M
WHERE M.E_NAME=E.E_NAME)P,(SELECT DISTINCT(E.E_NAME),[Link],[Link]
FROM EMPLOYEE E,MANAGES M WHERE M.MANAGER_NAME=E.E_NAME)Q
WHERE P.MANAGER_NAME=Q.E_NAME AND [Link]=[Link] AND
[Link]=[Link];

E_NAME
---------------
A. Datta M. Deb

(6) Find the employees in the database who earn more than every employee of
SMALL BANK CORPORATION.

SQL> SELECT E_NAME FROM EMPLOYEE WHERE SALARY>ALL(SELECT SALARY

FROM EMPLOYEE,COMPANY,WORKS WHERE
EMPLOYEE.E_CODE=WORKS.E_CODE AND COMPANY.C_CODE=WORKS.C_CODE
AND C_NAME='SMALL BANK CORPORATION');

E_NAME
-------------
A. Datta N. Roy D.
Paul

(7) Find all employees in the database who don’t work for FIRST BANK
CORPORATION.

SQL> SELECT E_NAME FROM EMPLOYEE, COMPANY, WORKS WHERE

EMPLOYEE.E_CODE=WORKS.E_CODE AND COMPANY.C_CODE=WORKS.C_CODE
AND COMPANY.C_NAME! ='FIRST BANK CORPORATION';
E_NAME
-------------
D. Das S. Das M. Deb
M. Raut J. Dey T.
Sikdar K. Manna N.
Roy

8 rows selected.

(8) Assume that the companies may be located in several cities. Find
all companies located in every city in which FIRST BANK CORPORATION
is located.

SQL> SELECT C_NAME FROM COMPANY where CITY IN(SELECT [Link]

FROM COMPANY WHERE C_NAME='FIRST BANK CORPORATION') AND C_NAME
NOT IN('FIRST BANK CORPORATION');

C_NAME
-------------
IBM

_______________
Teacher Signature
 ASSIGNMENT-4
DATE-

PROBLEM:

Consider the following schema of a relational database:

BOOK(ACC_NO,TITLE,AUTHOR,PUBLISHER_NAME,PRICE)
STUDENT (CARD_NO, NAME, ADDRESS)
BORROW (CARD_NO, ACC_NO, DATE_OF_ISSUE,DATE_OF_RETURN)
Create table through appropriate SQL command. Define all integrity constraints and
enter sufficient data through user friendly from design. Write SQL command and
output.

1. List the name of all books where price is less than 300.00.
2. Print the name of all students who have not borrowed any books.
3. List the name of the author who has maximum number of books.
4. Print the name and number of copies of the books.

TABLE CREATION:

SQL> CREATE TABLE BOOK(ACC_NO NUMBER(20)PRIMARY KEY,TITLE

VARCHAR2(30),AUTHOR VARCHAR2(30),PUBLISHER_NAME
VARCHAR2(30),PRICE NUMBER(10));
Table created.

SQL> CREATE TABLE STUDENT (CARD_NO NUMBER(20)PRIMARY KEY,NAME

VARCHAR2(30),ADDRESS VARCHAR2(100));
Table created.

SQL> CREATE TABLE BORROW(CARD_NO NUMBER(20)REFERENCES

STUDENT(CARD_NO),ACC_NO NUMBER(20)REFERENCES
BOOK1(ACC_NO),DATE_OF_ISSUE DATE,DATE_OF_RETURN DATE);
Table created.
SQL> DESCRIBE BOOK;

Name Null? Type

ACC_NO NOT NULL NUMBER(20)
TITLE NOT NULL VARCHAR2(30)
AUTHOR NOT NULL VARCHAR2(30)
PUBLISHER_NAME NOT NULL VARCHAR2(30)
PRICE NOT NULL NUMBER(10)
 SQL> DESCRIBE STUDENT;

Name Null? Type

CARD_NO NOT NULL NUMBER(20)
NAME NOT NULL VARCHAR2(30)
ADDRESS NOT NULL VARCHAR2(100)

SQL> DESCRIBE BORROW;

Name Null? Type

CARD_NO NOT NULL NUMBER(20)
ACC_NO NOT NULL NUMBER(20)
DATE_OF_ISSUE NOT NULL DATE
DATE_OF_RETURN NOT NULL DATE

TABLE AFTER INSERTION OF THE VALUES:

SQL> SELECT * FROM BOOK;

ACC_NO TITLE AUTHOR PUBLISHER_NAME PRICE

121435 FIVE POINT CHETAN BHAGAT SUNMOON 200
122214 TWILIGHT STEPHENIE CELLINA 600
123127 MICROPROCESSOR [Link] PENRAM 400
132445 MICROPROCESSOR [Link] PENRAM 400
323121 HARRY POTTER [Link] PENGUIN 800
345231 FIVE POINT CHETAN BHAGAT SUNMOON 200
423431 NEW MOON STEPHENIE MEYER CELLINA 550

7 rows selected.

SQL> SELECT * FROM STUDENT;

CARD_NO NAME ADDRESS

101 M. DEB SODEPUR
102 D. DAS KESHTOPUR
103 [Link] RAJARHAT
104 A. PAUL SALTLAKE
105 K. MUKHERJEE DUMDUM
106 S. NAGRANI BARRACKPORE
107 R. BOSE BOSEPUKUR
108 S. BISWAS SODEPUR
109 A. DATTA SALTLAKE
110 R. PRASAD BARRACKPORE
10 rows selected.
SQL> SELECT * FROM BORROW;

CARD_NO ACC_NO DATE_OF_ISSUE DATE_OF_RETURN

101 122214 02-JAN-11 07-JAN-11
102 345231 30-JAN-11 15-FEB-11
110 132445 24-MAR-11 10-APR-11
105 23127 08-APR-11 15-FEB-11
104 423431 14-MAY-11 15-FEB-11
107 122214 10-MAY-11 26-MAY-11
110 345231 07-JUN-11 15-FEB-11
105 132445 16-JUL-11 02-AUG-11

8 rows selected.

QUERIES:

(1) List the name of all books where price is less than 300.00.

SQL> SELECT TITLE FROM BOOK WHERE PRICE<300;

TITLE
------------------------------
FIVE POINT SOMEONE FIVE POINT
SOMEONE

(2) Print the name of all students who have not borrowed any books.

SQL> SELECT NAME FROM STUDENT WHERE CARD_NO NOT IN(SELECT CARD_NO
FROM BORROW);

NAME
-------------------------
T. SIKDAR S. NAGRANI S.
BISWAS A. DATTA

(3) List the name of the author who has maximum number of books.

SQL> SELECT AUTHOR FROM(SELECT TITLE,AUTHOR,COUNT(*)AS NOC FROM

BOOK GROUP BY TITLE,AUTHOR)A WHERE [Link]=(SELECT MAX(COUNT(*))FROM
BOOK GROUP BY TITLE,AUTHOR);
AUTHOR
-------------------------
CHETAN BHAGAT
[Link]

(4) Print the name and number of copies of the books.

SQL> SELECT TITLE, COUNT (*) FROM BOOK GROUP BY TITLE, AUTHOR;

TITLE COUNT (*)

------------------------------ ---------------
FIVE POINT SOMEONE 2
HARRY POTTER 1
MICROPROCESSOR 2
NEW MOON 1
TWILIGHT 1

_________________
Teacher Signature
 ASSIGNMENT-5
DATE-
PROBLEM:
Consider the following schema of a relational database:
SAILORS (S_ID, S_NAME, RATING, AGE)
S_ID Must Be Between 100 And 10000.
BOATS (B_ID, B_NAME, COLOUR)
B_ID Must Be Between 100 And 300, And Colour Must Be Red, Green, Yellow
And Blue.
RESERVED (S_ID, B_ID, DAY)

Create table through appropriate SQL command. Define all integrity constraints and
enter sufficient data through user friendly from design. Write SQL command and
output.
1. Find the average age of the sailor (who are at least 25 years old) for
each rating level that has at least two such sailors.
2. Find the S_ID’s of all sailors who have reserved RED boats but not
GREEN boats.
3. Finds the names of sailors who have reserved boat 203.
4. Find the name of the sailors who have reserved a red boat.
5. Find the sailors whose rating is better than some sailor named
ADITI.
6. Find the name of sailors who have reserved both RED and GREEN
boat.
7. Find the name of sailors who are older than the oldest sailor with a
rating of 8.
8. Find the name of sailors who have reserved all boats.

TABLE CREATION:
SQL> CREATE TABLE SAILORS(S_ID NUMBER(5)CHECK(S_ID BETWEEN 100 AND
10000)PRIMARY KEY,S_NAME VARCHAR2(30),RATING NUMBER(5),AGE
NUMBER(5));
Table created.

SQL> CREATE TABLE BOATS(B_ID NUMBER(3)CHECK(B_ID BETWEEN 100 AND

300)PRIMARY KEY,B_NAME VARCHAR2(30),COLOUR VARCHAR2(10)CHECK(COLOUR
IN('RED','GREEN','BLUE','YELLOW')));
Table created.

SQL> CREATE TABLE RESERVED(S_ID NUMBER(5)REFERENCES

SAILORS(S_ID)ON DELETE CASCADE,B_ID NUMBER(5)REFERENCES
BOATS(B_ID)ON DELETE CASCADE,DAY NUMBER(5),PRIMARY KEY(S_ID,B_ID));
 Table created.

SQL> DESCRIBE SAILORS;

Name Null? Type

S_ID NOT NULL NUMBER(5)
S_NAME NOT NULL VARCHAR2(30)
RATING NOT NULL NUMBER(5)
AGE NOT NULL NUMBER(5)

SQL> DESCRIBE BOATS;

Name Null? Type

B_ID NOT NULL NUMBER(3)
B_NAME NOT NULL VARCHAR2(30)
COLOUR NOT NULL VARCHAR2(10)

SQL> DESCRIBE RESERVED;

Name Null? Type

S_ID NOT NULL NUMBER(5)
B_ID NOT NULL NUMBER(5)
DAY NOT NULL NUMBER(5)

BACKEND CONNECTION:
VB CODE:

Dim conn As New [Link] Dim

rs As New [Link] Dim rs1 As
New [Link]
Private Sub boats_Click ()
[Link]
Unload Me
End Sub
Private Sub reserved_Click ()
[Link]
Unload Me
End Sub
Private Sub edit1_Click ()
a = InputBox("Enter S_ID")
[Link] "Provider=[Link]; User ID=system;
password=student; Persist Security Info=True"
[Link] "select * from SAILORS where S_ID='" & a & "'", conn,
adOpenStatic, adLockOptimistic
[Link] = [Link](0).Value
[Link] = [Link](1).Value
[Link] = [Link](2).Value
[Link] = [Link](3).Value
[Link]
[Link] End
Sub
Private Sub Save1_Click ()
[Link] "Provider=[Link]; User ID=system;
password=student; Persist Security Info=True"
[Link] "select * from SAILORS ", conn, adOpenStatic,
adLockOptimistic
[Link] "select * from SAILORS where S_ID='" & [Link] & "'", conn,
adOpenStatic, adLockOptimistic
If [Link] = False Then
a = MsgBox("This S_ID already exist. If You want to Update the
Database Please Click 'YES'", vbYesNo, "Query")
If a = vbYes Then
[Link]
[Link]
[Link]
[Link](0).Value = [Link]
Else
[Link](1).Value = [Link] [Link](2).Value = [Link]
[Link](3).Value = [Link] [Link]
 End If Else
[Link]
[Link]
[Link](0).Value = [Link]
[Link](1).Value = [Link]
[Link](2).Value = [Link]
[Link](3).Value = [Link]
[Link]
[Link]
[Link]
a = MsgBox("database updated") End If
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
End Sub

TABLE AFTER INSERTION OF THE VALUES:

SQL> SELECT * FROM SAILORS;

S_ID S_NAME RATING AGE

101 T. Sikdar 9 22
102 A. Paul 8 28
103 M. Deb 7 29
104 D. Das 5 30
105 M. Pandey 8 35
106 A. Jana 6 45
107 K. Bose 3 34
108 A. Pandit 8 65
109 A. Hasan 2 31
110 M. Majhi 5 20
111 J. Das 3 50
112 A. Datta 7 25
113 A. Manna 4 29
114 B. Patro 6 55
115 S. Mahapatro 1 40

15 rows selected.
SQL> SSELECT * FROM BOATS;

B_ID B_NAME COLOUR

200 Xylo RED
201 Steam GREEN
202 Ambay BLUE
203 Arcadia YELLOW
204 Loony RED
103 Nadal YELLOW
250 Wally GREEN
150 Fabe RED
234 Gala BLUE
300 Zoro GREEN
249 Sangalo YELLOW
123 Xonan GREEN

12 rows selected.

SQL> SELECT * FROM RESERVED;

S_ID B_ID DAY

101 203 10
102 200 2
110 201 7
104 300 6
102 201 5
106 250 30
115 249 24
112 250 15
106 203 4
112 204 20
103 202 25
114 200 29
104 234 7
102 202 19
104 123 6
111 150 8
104 249 9
105 202 7
104 103 23
112 200 104
104 250 7
104 150 3
 103 234 9
104 200 7
104 202 5
108 123 56
104 203 7
104 201 30
104 304 45

9 rows selected.
QUERIES:

(1) Find the average age of the sailor (who are at least 25 years old) for
each rating level that has at least two such sailors.

SQL> SELECT AVG(AGE) FROM SAILORS WHERE AGE>=25 AND RATING>=6;

AVG(AGE)
---------------
36.1666667

(2) Find the S_ID’s of all sailors who have reserved RED boats but not
GREEN boats.

SQL> SELECT [Link] FROM (SELECT SAILORS.S_ID AS ID,SAILORS.S_NAME AS NAME

FROM SAILORS,RESERVED,BOATS WHERE SAILORS.S_ID=RESERVED.S_ID AND
RESERVED.B_ID=BOATS.B_ID AND [Link]='RED')A WHERE [Link] NOT
IN(SELECT SAILORS.S_ID FROM SAILORS,RESERVED,BOATS WHERE
SAILORS.S_ID=RESERVED.S_ID AND RESERVED.B_ID=BOATS.B_ID AND
BOATS.B_ID=RESERVED.B_ID AND [Link]='GREEN');

NAME
---------------
B. Patro J. Das

(3) Finds the names of sailors who have reserved boat 203.

SQL> SELECT SAILORS.S_NAME FROM SAILORS, RESERVED WHERE

SAILORS.S_ID=RESERVED.S_ID AND RESERVED.B_ID=203;

S_NAME
--------------
T. Sikdar A. Jana D.
Das
(4) Find the name of the sailors who have reserved a red boat.

SQL> SELECT SAILORS.S_NAME FROM SAILORS, RESERVED, BOATS WHERE

SAILORS.S_ID=RESERVED.S_ID AND RESERVED.B_ID=BOATS.B_ID AND
[Link]='RED';

S_NAME
--------------
A. Paul A. Datta B.
Patro J. Das A. Datta
D. Das D. Das D. Das
8 rows selected.
(5) Find the sailors whose rating is better than some sailor named ADITI.

SQL> SELECT SAILORS.S_NAME FROM SAILORS WHERE RATING>(SELECT RATING

FROM SAILORS WHERE SAILORS.S_NAME='Aditi Manna');

S_NAME
---------------
T. Sikdar A. Paul M.
Deb D. Das M. Pandey
A. Jana M. Majhi A.
Datta B. Patro
9 rows selected.
(6) Find the name of sailors who have reserved both RED and GREEN
boat.

SQL> SELECT [Link] FROM (SELECT SAILORS.S_ID AS ID,SAILORS.S_NAME AS NAME

FROM SAILORS,RESERVED,BOATS WHERE SAILORS.S_ID=RESERVED.S_ID AND
RESERVED.B_ID=BOATS.B_ID AND [Link]='RED')A WHERE [Link] IN(SELECT
SAILORS.S_ID FROM SAILORS,RESERVED,BOATS WHERE SAILORS.S_ID=RESERVED.S_ID
AND RESERVED.B_ID=BOATS.B_ID AND [Link]='GREEN');

NAME
-------------
A. Paul D. Das D. Das
D. Das A. Datta A.
Datta

(7) Find the name of sailors who are older than the oldest sailor with a
rating of 8.
SQL> SELECT S_NAME FROM SAILORS WHERE AGE>(SELECT
MAX(AGE)FROM SAILORS WHERE RATING=8);

S_NAME
------------
A. Jana A. Pandit J.
Das B. Patro
S. Mahapatro

(8) Find the name of sailors who have reserved all boats.

SQL> SELECT SAILORS.S_NAME FROM SAILORS,(SELECT S_ID,COUNT(*)AS C

FROM RESERVED GROUP BY S_ID)A WHERE SAILORS.S_ID=A.S_ID AND
A.C=(SELECT COUNT(*) FROM BOATS);

S_NAME
--------------
D. Das

________________
Teacher Signature