RDBMS

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

A.
Step-1:
SQL> create table Supplier
2 (
3 Sup_no char(2) primary key,
4 Sup_name varchar2(20),5 Item_supplied varchar2(20),
6 Item_price number(4),
7 City varchar2(20));
Table created.
Step-2:
SQL> insert into Supplier values('S1','Suresh','Keyboard',400,'Hyderabad');
1 row created.
SQL> insert into Supplier values('S2','Kiran','Processor',8000,'Delhi');
1 row created.
SQL> insert into Supplier values('S3','Mohan','Mouse',350,'Delhi');
1 row created.
SQL> insert into Supplier values('S4','Ramesh','Processor',9000,'Bangalore');
1 row created.
SQL> insert into Supplier values('S5','Manish','Printer',6000,'Mumbai');
1 row created.
SQL> insert into Supplier values('S6','Srikanth','Processor',8500,'Chennai');
1 row created.
SQL> commit;
Commit complete.
Step-3:
SQL>select * from supplier;
1. Write sql query to display Suplier numbers and Supplier names whose name starts with
‘R’
SQL> select Sup_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.
SQL> select Sup_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.
SQL> select Sup_name from Supplier where Item_supplied='Processor';

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

SQL> select Item_price+200 from Supplier where Item_supplied='Keyboard';

5. Write sql query to display supplier numbers, Suplier names and itemprice for suppliers
in delhi in the ascending order of itemprice.
SQL> select Sup_no,Sup_name,Item_price from Supplier where City='Delhi' order by
Item_price;

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

1.
SQL> alter table Supplier
2 add Contact_no number(10);
Table altered.
2.
SQL> select * from Supplier;

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

1.
SQL> delete from Supplier where Item_price=(select min(Item_price)from Supplier);
1 row deleted.
2.
SQL> select * from Supplier;

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

1.
SQL> create view Supplier_view as select Sup_no,Sup_name from Supplier;
View Created.
2.SQL>select * from Supplier_view;

9. Write sql query to display the records in the descending order of itemprice for each
itemsupplied.
SQL> select * from Supplier order by Item_price desc;
10. Write sql query to display the records of suppliers who supply items other than
Processor or Keyboard.

SQL> select * from Supplier where Item_supplied !='Keyboard' and Item_supplied !

='Processor';

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

Step-1:
SQL> create table EmpDetails
2 (
3 Eid char(4) primary key,
4 Ename varchar2(20),
5 DOB date,
6 Designation varchar2(20),
7 Salary number(5),
8 DOJ date);
Table created.
Step-2:
SQL> insert into EmpDetails values('E101','Suma','29-Dec-89','Designer',20000,'01-Apr-10');
1 row created.
SQL>insert into EmpDetails values('E102','Amit','10-Jan-95','Programmer',25000,'18-Feb-18');
1 row created.
SQL> insert into EmpDetails values('E103','Payal','15-Aug-85','Tester',35000,'13-Jun-11');
1 row created.
SQL> insert into EmpDetails values('E104','Kiran','20-Aug-90','Programmer',40000,'7-Mar-14');
1 row created.
SQL> insert into EmpDetails values('E105','Meenal','29-May-83','DBA',50000,'9-Dec-11');
1 row created.
SQL> insert into EmpDetails values('E106','Shelia','1-May-70','Analyst',60000,'25-Sep-18');
1 row created.
SQL> insert into EmpDetails values('E107','Swamy','13-Jan-85','Programmer',45000,'14-Feb-
16');
1 row created.
SQL>insert into EmpDetails values('E108','Sushma','22-Dec-76','DBA',45000,'31-Jan-12');
1 row created.
Step-3:
select * from EmpDetails;

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

SQL> select * from EmpDetails where Designation='Programmer';

12. Write sql query to display employees who have joined after 2014.
SQL> select * from EmpDetails where DOJ>'7-Mar-14';
13. Write sql query to display all the employees whose name ends with ‘a’.
SQL> select * from EmpDetails where Ename like '%a';

14. Write sql query to display the total salary of all the employees whose designation is
programmer.
SQL> select sum(salary) from EmpDetails where Designation='Programmer';

15. Write sql query to display all the employee names in upper case.
SQL> select Upper (Ename) from EmpDetails;

16. Write sql query to display the details of the employee with highest experience.
SQL> select * from EmpDetails where DOJ=(select min (DOJ) from EmpDetails);

17. Write sql query to display the details of the employees whose name contains‘ee’.
SQL> select * from EmpDetails where Ename like '%ee%';

18. Write sql query to increase the salaries of employees by 5000 whose designation is DBA.
SQL> select salary+5000 from EmpDetails where Designation='DBA';
19. Write sql query to display the employees whose salary is more than the average salary
of all the employees.
SQL> select * from EmpDetails 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 ofRs.xxxxxxxx eg: Suma is working as Designer with a
Salary of Rs. 20000

SQL> select Ename|| 'is working as' || Designation || 'with a' || 'Salary of Rs.'|| Salary from
EmpDetails;

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 isforeign key Constraints:DeptId
Primary key
Salary should not be less than 10000 and Dname is NOT NULL
Tablename:Department
Step-1:
SQL> create table Department
2 (
3 Deptid char(2) constraint Deptid_pk primary key,
4 Dname varchar2(20)
5 );
Table created.
Step-2:
SQL> insert into Department values('D1','sales');
1 row created.
SQL> insert into Department values('D2','Marketing');
1 row created.
SQL> insert into Department values('D3','Finance');
1 row created.
Step-3:
SQL> select * from Department;

Table name: Employee

Step-1:
SQL> create table Employee
2 (
3 Eid number(3) constraint Eid_pk primary key,
4 Ename varchar2(20),
5 Deptid char(2) references Department(Deptid),
6 Designation varchar2(20),
7 Salary number(5),
8 DOJ date);

Table created.

SQL>insert into Employee values('101','Sudha','D2','Clerk',20000,'01-Apr-10');

1 row created.

SQL>insert into Employee values('102','David','D1','Manager',50000,'18-Feb-18');

1 row created.

SQL>insert into Employee values('103','Preethi','D3','Clerk',35000,'13-Jun-11');

1 row created.

SQL>insert into Employee values('104','Kiran','D1','Salesman',20000,'7-Mar-14');

1 row created.

SQL>insert into Employee values('105','Meenal','D2','Clerk',50000,'9-Dec-11');

1 row created.

SQL>insert into Employee values('106','Sunitha','D3','Manager',60000,'25-Sep-18');

1 row created.

SQL>insert into Employee values('107','Akhil','D3','Clerk',25000,'14-Feb-16');

1 row created.

SQL> insert into Employee values('108','Sushma','D2','Manager',45000,'31-Jan-12');

1 row created.
Step-3:
SQL>select * from Employee;

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

SQL> select * from EmpDetails where Salary>(select avg(salary) from Employee);

22. Write sql query to display the fields Eid, Ename and Dname.
SQL> select Eid,Ename,Department.Dname from Employee,Department;

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

SQL> select * from Employee order by Salary desc;

24. Write sql query to list all the job designations in the employee table without repetitions.
SQL> 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.
SQL> select Deptid,sum(Salary) from employee group by Deptid order by Deptid;

26. Write sql query to display all the clerks in DeptId D2.
SQL>select * from Employee Department where Designation='Clerk' and
Department.Deptid='D2';

27. Write sql query to display all the employees who joined in the year 2011.
SQL>select * from Employee where DOJ like '%11';

28. Write sql query to display all the employees who joined in the month of February.
SQL>select * from Employee where DOJ like '%FEB%';

29. Write sql query to display all the employees whose salary is between 30000 and 45000.
SQL>select * form 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.
SQL>select sysdate-DOJ "experience" from Employee;
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.

Step-1:
SQL>Create table Student
2(
3 Sid number(4) primary key,
4 Sname varchar2(20),
5 DOB date,
6 State varchar2(15),
7 Gender char(1),
8 Category varchar(15),
9 Course varchar2(10)
10 );
step-2:
SQL> insert into Student values(1001,'Neha','29-Dec-02','Telangana','F','Gen','Comp');

1 row created.

SQL> insert into Student values(1002,'Arun','10-Jan-02','Telangana','M','OBC','Honors');

1 row created.
SQL> insert into Student values(1003,'Payal','15-Aug-01','Maharastra','F','Gen','Appl');

1 row created.

SQL> insert into Student values(1004,'Amrita','20-Apr-02','Karnataka','F','OBC','Honors');

1 row created.

SQL> insert into Student values(1005,'Pavan','29-May-03','Andhra

Pradesh','M','ExServicemen','Comp');

1 row created.

SQL> insert into Student values(1006,'Anchal','01-May-03','Gujarat','F','OBC','Comp');

1 row created.

SQL> insert into Student values(1007,'Ramya','13-Jan-02','Telangana','F','Gen','Appl');

1 row created.

SQL> insert into Student values(1008,'Rakesh','22-Dec-01','Andhra Pradesh','M','Sports','Comp');

1 row created.
Step-3:
SQL>select * from Student;

31. Write sql query to display the students who are not from Telangana or AndhraPradesh.
SQL>select * from Student where State!='Telangana' and State!='Andhra Pradesh';
32. Create a view to display the columns Sid, Sname for students belonging to Telangana.

SQL> create view Sid_view AS

2 select Sid,Sname from Student where State='Telangana';
View created.
SQL>select * from Sid_view;

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

SQL>create index Std_index on Student(Sname);
Index created.

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

35. Write sql query to display the student ids, names, and their present age.
SQL>select Sid,Sname,sysdate-DOB from Student;

36. Write sql query to display the students in the ascending order of their names for each
course.
SQL>select Sname,Course from Student order by Course,Sname;

37. Write sql query to delete all the students records who have enrolled for Comp course
and who are born after 2002.
SQL>delete from Student where Course='Comp' and DOB>='10-Jan-03;

38. Write a sql query to add two new columns Contactno and Email to the existing fields.
SQL> alter table Student
2 add contact_no number(10),
3 add Emaid_Id varchar2(20);
Table altered.
SQL>select * from Student;

39. Writs an sql query to display all the Student names prefixed with Mr./Ms. Based on
Gender column.
SQL>update Student set Sname='Mr'||Sname where Gender='M';
SQL>update Student set Sname='Ms'||Sname where Gender='F';
40. Write an sql query to display all the Student names where the length of the name is 5
characters.

SQL> select * from Student where length(Sname)=5;

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
Step-1:
SQL> create table Library
2 (
3 BookId varchar(4)primary key,
4 BookName varchar2(20) NOT NULL,
5 Author varchar2(20),
6 DatePurchased date,
7 Publisher varchar2(20),
8 Price number(3)
9 );

Table created.

Step-2:

SQL> insert into Library values('B101','Cost Accounting','Jain Narang','11-Feb-

13','Kalyani',800);

1 row created.

SQL> insert into Library values('B102','Business Statistics','OP Aggrawal','12-Dec-

11','Himalaya',750);

1 row created.

SQL> insert into Library values('B103','RDBMS','C J Date','2-Mar-15','TMH',900);

1 row created.

SQL> insert into Library values('B104','Mgmt Accounting','RK Sharma','19-Apr-

16','Kalyani',450);

1 row created.
SQL> insert into Library values('B105','Opertaing Systems','Galvin','25-Nov-13','PHI',750);

1 row created.

SQL> insert into Library values('B106','Advanced Accounting','SC Gupta','16-Apr-

18','Himalaya',600);

1 row created.

Step-3:
SQL>select * from Library;

41. Write sql query to display the list of authors from Himalaya publications.
SQL>select Author from Library where Publisher='Himalaya';

42. Write sql query to display the total cost of books purchased Publisher wise.
SQL>select sum(price) from Library group by Publisher;
43. Write sql query to count the total number of books under Kalyani publications.

SQL>select Count(BookName) from Library where Publisher='Kalyani';

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

SQL> alter table Library RENAME COLUMN Publisher to Publications;
Table altered.
SQL> select * from Library;

45. Write a sql query to display the books in the ascending order of DatePurchased.
SQL>select * from Library order by DatePurchased;

46. Write sql query to create an index on the fields BookName and Author.
SQL>create index Library_index on Library(BookName,Author);

Index created.

47. Write sql query to display the books whose price is between 500 and 700
SQL>select * from Library where Price between 500 and 700;

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

SQL> select BookId,BookName,price+200 from Library where Publications not in

('Himalaya','Kalyani');

SQL>
49. Write sql query to display the book details where author name contains the name
Sharma.
SQL>select * from Library where Author like '%Sharma%';

50. Create a view to display the fields BookId and BookName where the Publisher is
Himalaya.
1.
SQL> create view Library_view as select BookId,BookName from Library where Publi
cations='Himalaya';
View created.
2.
SQL>select * from Library_view;