B.Com.II Year III Sem.

RDBMS-SQL LAB

RELATIONAL DATABASE MANAGEMENT SYSTEM

A. Create a Supplier table as shown below : (for questions from 1 to 10)

Sup_No Sup_Name Item_Supplied Item_Price City

(Primary Key)

S1 Suresh Keyboard 400 Hyderabad

S2 Kiran Processor 8000 Delhi

S3 Mohan Mouse 350 Delhi

S4 Ramesh Processor 9000 Bangalore

S5 Manish Printer 6000 Mumbai

S6 Srikanth Processor 8500 Chennai

Creating a Supplier Table

SQL>create table Supplier(Sup_No varchar2(3) PRIMARY KEY, Sup_Name varchar2(10), Item_Supplied varchar2(10),
Item_Price number(6), City varchar2(12));

Table created.

SQL>desc Supplier;

INSERT VALUES INTO THE SUPPLIER TABLE.

insert into Supplier values('S1','Suresh', 'Keyboard',400, 'Hyderabad');

insert into Supplier values('S2','Kiran', 'Processor',8000,'Delhi');

insert into Supplier values('S3','Mohan', 'Mouse',350, 'Delhi');

insert into Supplier values('S4','Ramesh', 'Processor',9000, 'Bangalore');

insert into Supplier values('S5','Manish', 'Printer',6000, 'Mumbai');

insert into Supplier values('S6','Srikanth', 'Processor',8500, 'Chennai');

SQL>SELECT * from Supplier;

1
B.Com.II Year III Sem. RDBMS-SQL LAB

1. Write sql query to display Suplier numbers and Supplier names whose name starts with ‘R’

selectSup_No, Sup_Name from Supplier where Sup_Name like 'R%';

2. Write sql query to display the name of suppliers who supply Processors and whose city is Delhi.

selectSup_Name from Supplier where Item_Supplied='Processor' and City='Delhi';

3. Write sql query to display the names of suppliers who supply the same items as supplied by Ramesh.

selectSup_Name from Supplier where Item_Supplied in (select Item_Supplied from Supplier where
Sup_Name='Ramesh');

4. Write sql query to increase the price of Keyboard by 200.

update Supplier set Item_Price=Item_Price+200 where Item_Supplied='Keyboard';

select * from Supplier where Item_Supplied='Keyboard';

2
B.Com.II Year III Sem. RDBMS-SQL LAB

5. Write sql query to display supplier numbers, Suplier names and itemprice for suppliers in delhi in the ascending order
of itemprice.

selectSup_No, Sup_Name, Item_Price from Supplier where City='Delhi' Order By Item_Priceasc;

6. Write sql query to add a new column called CONTACTNO.

alter table Supplier add(CONTACTNO number(12));

Table alterd.

desc Supplier;

7. Write sql query to delete the record whose itemprice is the lowest of all the items supplied .

delete from Supplier where(select * from Supplier where Item_Price=min(Item_Price));

1 row(s) deleted.

Select * from Supplier;

3
B.Com.II Year III Sem. RDBMS-SQL LAB

8. Create a view on the table which displays only supplier numbers and supplier names.

create view Sup_View as select Sup_No, Sup_Name from Supplier;

view created.

select * from Sup_View;

9. Write sql query to display the records in the descending order of itemprice for each itemsupplied.
select * from Supplier order by Item_Pricedesc;

10. Write sql query to display the records of suppliers who supply items other than Processor or Keyboard.
select * from Supplier where Item_Supplied not in( select Item_Supplied from Supplier where Item_Supplied='Processor'
or Item_Supplied='Keyboard');

4
B.Com.II Year III Sem. RDBMS-SQL LAB

B. Below are the details of Employees working for a software Company. (For questions from 11 to 20) Create
the table called EmpDetailswith the below mentioned details.

Eid Ename DOB Designation Salary DOJ

(Primary Key)

E101 Suma 29-Dec-89 Designer 20000 01-Apr-10

E102 Amit 10-Jan-95 Programmer 25000 18-Feb-18

E103 Payal 15-Aug-85 Tester 35000 13-Jun-11

E104 Kiran 20-Apr-90 Programmer 40000 7-Mar-14

E105 Meenal 29-May-83 DBA 50000 9-Dec-11

E106 Sheila 1-May-70 Analyst 60000 25-Sep-18

E107 Swamy 13-Jan-85 Programmer 45000 14-Feb-16

E108 Sushma 22-Dec-76 DBA 45000 31-Jan-12

CREATING A TABLE ON EmpDetails.

create table EmpDetails(Eid varchar2(5) PRIMARY KEY, Ename varchar2(10), DOB date, Designation varchar2(10), Salary
number(8), DOJ date);

Table created.

descEmpDetailsB

INSERTING VALUES INTO EmpDetailsTABLE.

insert into EmpDetailsB values('E101', 'Suma', '29-Dec-89', 'Designer', 20000, '01-Apr-10');

insert into EmpDetailsB values('E102', 'Amit', '10-Jan-95', 'Programmer', 25000, '18-Feb-18');

insert into EmpDetailsB values('E103', 'Payal', '15-Aug-85', 'Tester', 35000, '13-Jun-11');

insert into EmpDetailsB values('E104', 'Kiran', '20-April-90', 'Programmer', 40000, '07-Mar-14');

insert into EmpDetailsB values('E105', 'Meenal', '29-May-83', 'DBA', 50000, '09-Dec-11');

5
B.Com.II Year III Sem. RDBMS-SQL LAB

insert into EmpDetailsB values('E106', 'Sheila', '01-May-70', 'Analyst', 60000, '25-Sep-18');

insert into EmpDetailsB values('E107', 'Swamy', '13-Jan-85', 'Programmer', 45000, '14-Feb-16');

insert into EmpDetailsB values('E108', 'Sushma', '22-Dec-76', 'DBA', 45000, '31-Jan-12');

select * from EmpDetailsB;

11. Write sql query to display all the employees whose designation is Programmer.

select * from EmpDetailsB where Designation='Programmer';

12. Write sql query to display employees who have joined after 2014.

select * from EmpDetailsB where DOJ>'31-Dec-2014';

13. Write sql query to display all the employees whose name ends with ‘a’.
select * from EmpDetailsB where Ename like '%a';

6
B.Com.II Year III Sem. RDBMS-SQL LAB

14. Write sql query to display the total salary of all the employees whose designation is programmer.

select Designation,sum(Salary) as TotalSalary from EmpDetailsB where Designation='Programmer' group by

Designation;

15. Write sql query to display all the employee names in upper case.

select UPPER(Ename) from EmpDetailsB;

16. Write sql query to display the details of the employee with highest experience.

select * from EmpDetailsB where Doj=(select Min(Doj) from EmpDetailsB);

17. Write sql query to display the details of the employees whose name contains ‘ee’.

select * from EmpDetailsB where Ename like '%ee%';

7
B.Com.II Year III Sem. RDBMS-SQL LAB

18. Write sql query to increase the salaries of employees by 5000 whose designation is DBA.

UPDATE EmpDetailsB set Salary=Salary+5000 where Designation='DBA';

2 Row(s) updated.

select * from EmpDetailsB where Designation='DBA';

19. Write sql query to display the employees whose salary is more than the average salary of all the employees.

select * from EmpDetailsB where Salary>(select AVG(Salary) from EmpDetails);

20. Write sql query to display the record in the following format: xxxxxxxxx is working as xxxxxxxxxxxxxx with a Salary of
Rs.xxxxxxxx
eg: Suma is working as Designer with a Salary of Rs. 20000

selectEname, 'is working as ', Designation, 'with Salary of Rs.', Salary from EmpDetailsB;

8
B.Com.II Year III Sem. RDBMS-SQL LAB

C. Create the two tables as shown below with the given constraints: (for questions 21 to 30)
Table name: Employee Tablename: Department
Constraints: Eid is Primary key and DeptId is foreign key Constraints:DeptId Primary key

Salary should not be less than 10000 and Dname is NOT NULL

Eid Ename DeptId Designation Salary DOJ

(Primary Key) (Foreign Key) ( > 10000)

101 Sudha D2 Clerk 20000 01-Apr-10

DeptId Dname
102 David D1 Manager 50000 18-Feb-18
(Primary Key)
103 Preethi D3 Clerk 35000 13-Jun-11
D1 Sales
104 Kiran D1 Salesman 20000 7-Mar-14
D2 Marketing
105 Meenal D2 Clerk 50000 9-Dec-11
D3 Finance
106 Sunitha D3 Manager 60000 25-Sep-18

107 Akhil D3 Clerk 25000 14-Feb-16

108 Sushma D2 Manager 45000 31-Jan-12

IN ORDER TO ESTABLISH FOREIGN KEY, WE HAVE TO CREATE DEPARTMENT TABLE FIRST AND THEN USE THE DeptId
COLUMN IN THE Employee TABLE.

CREATING Department TABLE.

create table Department(DeptId varchar2(10) primary key,Dname varchar2(20) not null);

Table created.

desc Department;

INSERTING VALUES INTO DEPARTMENT TABLE.

insert into Department values('D1','Sales');

insert into Department values('D2','Marketing');

insert into Department values('D3','Finance');

select * from Department;

9
B.Com.II Year III Sem. RDBMS-SQL LAB

CREATING Employee TABLE WITH FOREIGN KEY.

create table Employee(Eid varchar2(5) PRIMARY KEY, Ename varchar2(20),DeptId varchar2(10) NOT NULL,Designation
varchar2(20),Salary number(8) check(salary>10000), DOJ date, FOREIGN KEY(DeptId)REFERENCES Department(DeptId));

desc Employee;

INSERT INTO Employeevalues('101', 'Sudha','D2','Clerk',20000 , '01-Apr-10');

INSERT INTO Employeevalues('102', 'David','D1','Manager',50000 , '18-Feb-18');
INSERT INTO Employee values('103', 'Preethi','D3','Clerk',35000 , '13-Jun-11');
INSERT INTO Employeevalues('104', 'Kiran','D1','Salesman',20000 , '07-Mar-14');
INSERT INTO Employee values('105', 'Meenal','D2 ','Clerk ',50000 , '9-Dec-11');
INSERT INTO Employee values('106', 'Sunitha','D3','Manager',60000 , '25-Sep-11');
INSERT INTO Employee values('107', 'Akhil','D3','Clerk',25000 , '14-Feb-16');
INSERT INTO Employee values('108', 'Sushma','D2','Manager',45000 , '31-Jan-12');
select * from Employee;

10
B.Com.II Year III Sem. RDBMS-SQL LAB

21. Write sql query to display all the employees who earn more than average salary of all the employees in the
company.

select * from Employee where Salary>(select AVG(salary) from Employee);

22. Write sql query to display the fields Eid, Ename and Dname.

selectEid, Ename, Dname from Employee E, Department D where E.DeptId=D.DeptId;

23. Write sql query to sort the employee table in the descending order of salaries.

select * from Employee order by Salary desc;

11
B.Com.II Year III Sem. RDBMS-SQL LAB

24. Write sql query to list all the job designations in the employee table without repetitions.

select distinct(Designation) from Employee;

25. Write sql query to display all the employee details Department wise and in the ascending order of their salaries.

selectd.DeptId,d.Dname, e.Eid, e.Ename, e.Salary from Employee e, Department d where d.DeptId=e.DeptId order by
DeptId, Salary;

26. Write sql query to display all the clerks in DeptId D2.

select * from Employee where DeptId='D2' and Designation='Clerk';

12
B.Com.II Year III Sem. RDBMS-SQL LAB

27. Write sql query to display all the employees who joined in the year 2011.
select * from Employee where DOJ>='01-Jan-2011' and DOJ<='31-Dec-2011';

28. Write sql query to display all the employees who joined in the month of February.

select * from Employee where to_char(DOJ,'MON')='FEB' ;

29. Write sql query to display all the employees whose salary is between 30000 and 45000.
select * from Employee where Salary between 30000 and 45000;

30. Write sql query to display all the employee details along with their work experience in the company till current date.
select Eid, Ename, DeptId,Designation, Salary, DOJ,round((sysdate-DOJ )/365) as Experience from Employee;

13
B.Com.II Year III Sem. RDBMS-SQL LAB

D. Below are the details of Students enrolled in various course of B.Com (For questions from 31 to 40)
Create the table called Student with the below mentioned details.

Sid Sname DOB State Gender Category Course

(Primary Key)

1001 Neha 29-Dec-02 Telangana F Gen Comp

1002 Arun 10-Jan-02 Telangana M OBC Honors

1003 Payal 15-Aug-01 Maharashtra F Gen Appl

1004 Amrita 20-Apr-02 Karnataka F OBC Honors

1005 Pavan 29-May-03 AndhraPradesh M ExServicemen Comp

1006 Anchal 1-May-03 Gujarat F OBC Comp

1007 Ramya 13-Jan-02 Telangana F Gen Appl

1008 Rakesh 22-Dec-01 AndhraPradesh M Sports Comp

CREATING STUDENT TABLE

create table Student (Sid varchar(10)Primary Key,Snamevarchar2(20) NOT NULL, DOB date NOT NULL, State char(20)
NOT NULL, Gender char(1) NOT NULL, Category char(20) NOT NULL, Course char(20) NOT NULL);

Table created.

desc student;

INSERTING VALUES INTO STUDENT TABLE.

INSERT INTO Student values(1001, 'Neha', '29-Dec-02', 'Telangana', 'F', 'Gen', 'Comp');

14
B.Com.II Year III Sem. RDBMS-SQL LAB

INSERT INTO Student values(1002, 'Arun', '10-Jan-02', 'Telangana', 'M', 'OBC', 'Honors');

INSERT INTO Student values(1003, 'Payal', '15-Aug-01', 'Maharashtra', 'F', 'Gen', 'Appl');

INSERT INTO Student values(1004, 'Amrita', '20-Apr-02', 'Karnataka', 'F', 'OBC', 'Honors');

INSERT INTO Student values(1005, 'Pavan', '29-May-03', 'AndhraPradesh', 'M', 'ExServicemen', 'Comp');

INSERT INTO Student values(1006, 'Anchal', '1-May-03', 'Gujarat', 'F', 'OBC', 'Comp');

INSERT INTO Student values(1007, 'Ramya', '13-Jan-02', 'Telangana', 'F', 'Gen', 'Appl');

INSERT INTO Student values(1008, 'Rakesh', '22-Dec-01', 'AndhraPradesh', 'M', 'Sports', 'Comp');

select * from student;

31. Write sql query to display the students who are not from Telangana or AndhraPradesh.

select * from Student where State not in('Telangana', 'AndhraPradesh');

32. Create a view to display the columns Sid, Sname for students belonging to Telangana.

create view SIdName as select Sid, Sname from Student where state='Telangana';

View created

select * from SIdName;

15
B.Com.II Year III Sem. RDBMS-SQL LAB

33. Write sql query to create an index on column Sname.

create index iSname on Student(Sname);

desciSname;

Object type INDEX object ISNAME

34. Write sql query to display all the female students enrolled under Comp course and who belong to OBC.
select * from Student where Course='Comp' and Gender='F' and Category='OBC';

35. Write sql query to display the student ids, names, and their present age.

select Sid ,Sname, round((sysdate-DOB )/365) as PresentAge from student;

36. Write sql query to display the students in the ascending order of their names for each course.

select Sname, Course from student order by Course,Snameasc;

16
B.Com.II Year III Sem. RDBMS-SQL LAB

37. Write sql query to delete all the students records who have enrolled for Comp course and who are born after 2002.

delete from Student where Course='Comp' and DOB>'31-Dec-2002';

2 Row(s) deleted.

Student Table after deleting 2 rows.

Select * from Student;

38. Write a sql query to add two new columns Contactno and Email to the existing fields.

alter table Student add(ContactNo number(12), Email varchar(20));

Table altered.

Desc Student;

17
B.Com.II Year III Sem. RDBMS-SQL LAB

39. Write an sql query to display all the Student names prefixed with Mr./Ms. Based on Gender column.

Select

Sname,case

when Gender='M' then concat('Mr.',sname)

when Gender='F' then concat('Ms.',sname)

end as NamewithTitlePrefix

from student;

40. Write ansql query to display all the Student names where the length of the name is 5 characters.

selectSname from Student where Length(Sname)=5;

18
B.Com.II Year III Sem. RDBMS-SQL LAB

E. Create a Table for Library Information : (for questions from 41 to 50)

Table name: Library

Constraints: BookId is primary key and BookName is NOT NULL

BookId BookName Author DatePurchased Publisher Price

(Primary Key)

B101 Cost Accounting Jain Narang 11-Feb-13 Kalyani 800

B102 Business Statistics OP Aggarwal 22-Dec-11 Himalaya 750

B103 Rdbms C J Date 2-Mar-15 TMH 900

B104 Mgmt Accounting RK Sharma 19-Apr-16 Kalyani 450

B105 Operating Systems Galvin 25-Nov-13 PHI 750

B106 Advanced Accounting SC Gupta 16-Apr-18 Himalaya 600

CREATING LIBRARY TABLE

create table Library(BookId varchar2(10) primary key,BookName varchar2(20) not null,Author varchar2(20)
,DatePurchaseddate,Publisher varchar2(20),price number(7));

Table created.

desc Library;

INSERTING VALUES INTO LIBRARY TABLE

insert into Library values('B101','Cost Accounting','Jain Narang','11-Feb-13','Kalyani',800);

insert into Library values('B102','Business Statistics','OP Aggarwal','22-Dec-11','Himalaya',750);

insert into Library values('B103','Rdbms','C J Date','02-Mar-15','TMH',900);

insert into Library values('B104','Mgmt Accounting','RK Sharma','19-Apr-16','Kalyani',450);

insert into Library values('B105','Operating Systems','Galvin','25-Nov-13','PHI',750);

19
B.Com.II Year III Sem. RDBMS-SQL LAB

insert into Library values('B106','Advanced Accounting','SC Gupta','16-Apr-18','Himalaya',600);

select * from Library;

41. Write sql query to display the list of authors from Himalaya publications.

select Author from Library where Publisher='Himalaya';

42. Write sql query to display the total cost of books purchased Publisher wise.

select Publisher, sum(Price) as TotalPrice from Library group by Publisher;

43. Write sql query to count the total number of books under Kalyani publications.

select publisher, count(*) as NoOfBooks from Library where Publisher='Kalyani' group by publisher;

20
B.Com.II Year III Sem. RDBMS-SQL LAB

44. Write sql query to rename the column Publisher as Publications.

alter table Library rename column Publisher to Publications;

Table Altered.

desc Library;

45. Write a sql query to display the books in the ascending order of DatePurchased.

Select BookName, DatePurchased from Library order by DatePurchased;

46. Write sql query to create an index on the fields BookName and Author.

create index IBookNameAuthor on Library(BookName, Author);

Object Type INDEX Object IBOOKNAMEAUTHOR

47. Write sql query to display the books whose price is between 500 and 700
selectBookName, Price from Library where Price BETWEEN 500 and 700;

21
B.Com.II Year III Sem. RDBMS-SQL LAB

48. Write sql query to increase the price of all the books by 200 for publishers other than Himalaya or Kalyani.

ALTER THE PUBLICATION COLUMN TO PUBLISHER AND THEN UPDATE THE TABLE.

alter table Library rename column Publications to Publisher;

Table Altered.

desc Library1;

update Library set Price=Price+200 where Publisher not in('Himalaya', 'Kalyani');

2 row(s) updated.

select Publisher, Price from Library;

49. Write sql query to display the book details where author name contains the name Sharma.

select * from Library where Author like '%Sharma%';

1 row returned

50. Create a view to display the fields BookId and BookName where the Publisher is Himalaya.

create view BookIdNameView as select BookId, BookName from Library where publisher='Himalaya';

View created

select * from BookIdNameView;

22