II-B.

Com (Computer Applications)

III-Semester

Subject:RelationaldatabaseManagementSystem(RDBMS)

Lab(Record)Programs
 Index
[Link]. Programs PageNo.
A Create a Supplier table as shown below:(for questions from1to10)
Write sql query to display Supliernumbers and Suppliernames whose name starts
1
with ‘R’
2 Write sql query to display the name of suppliers who supply Processors and whose
city is Delhi
Write sql query to display the names of suppliers who supply the
3
Same items as supplied by Ramesh
4 WritesqlquerytoincreasethepriceofKeyboardby200
Writesqlquerytodisplaysuppliernumbers,Supliernamesanditemprice for suppliers in
5
delhi in the ascending order of itemprice
6 Writesqlquerytoaddanewcolumncalled CONTACTNO

7 Write sql query to delete the record whose itemprice is the lowest of all the items
supplied
Createaviewonthetablewhichdisplaysonlysuppliernumbersand
8
supplier names
Writesqlquerytodisplaytherecordsinthedescendingorderof itemprice for each
9
itemsupplied
Write sql query to display the records of suppliers who supply items other than
10
Processor or Keyboard
Below are the details of Employees working for a software Company. (For questions
B
from 11 to 20)
Writesqlquerytodisplayalltheemployeeswhosedesignationis
11
Programmer
12 Writesqlquerytodisplayemployeeswhohavejoinedafter2014
Write sql query to display all the employees whose name ends with‘a’
13

Write sql query to display the total salary of all the employees whose designation is
14
programmer
15 Writesqlquerytodisplayalltheemployeenamesinuppercase
Writesqlquerytodisplaythedetailsoftheemployeewithhighest experience
16

Write sql query to display the details of the employees whose namecontains ‘ee’
17

Write sql query to increase the salaries of employees by 5000 whose designation is
18
DBA
Writesqlquerytodisplaytheemployeeswhosesalaryismorethan
19
theaveragesalaryofall theemployees
Writesqlquerytodisplaytherecordinthefollowingformat: xxxxxxxxx is working as
20
xxxxxxxxxxxxxx with a Salary of Rs. Xxxxxxxx
C Create the two tables as shown below with the given constraints: (for questions 21 to
30)
Writesqlquerytodisplayalltheemployeeswhoearnmorethan
21
averagesalaryofalltheemployeesinthe company
22 WritesqlquerytodisplaythefieldsEid, Enameand Dname

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

Write sql query to list all the job designations in the employee table without
24
repetitions
25 WritesqlquerytodisplayalltheemployeedetailsDepartmentwise
andintheascendingorderoftheir salaries
26 WritesqlquerytodisplayalltheclerksinDeptIdD2
Write sql query to display all the employees who joined in the year2011
27

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

Writesqlquerytodisplayalltheemployeeswhosesalaryisbetween
29
30000and45000
Writesqlquerytodisplayalltheemployeedetailsalongwiththeir work experience in the
30
company till current date
Below are the details of Students enrolled in various course of [Link] (For questions
D
from 31 to 40)
31 Write sql query to display the students who are not from Telangana or
AndhraPradesh
CreateaviewtodisplaythecolumnsSid,Snameforstudents belonging to Telangana
32

33 Writesqlquerytocreateanindexoncolumn Sname
Writesqlquerytodisplayallthefemalestudentsenrolledunder Comp course and who
34
belong to OBC
Writesqlquerytodisplaythestudentids,names,andtheirpresent
35
age
Writesqlquerytodisplaythestudentsintheascendingorderoftheir names for each
36
course
Write sql query to delete all the students records who have enrolled for Comp
37
course and who are born after 2002
Writeasqlquerytoaddtwonewcolumns ContactnoandEmailtothe existing fields
38

WritsansqlquerytodisplayalltheStudentnamesprefixedwith Mr/Ms Based on Gender

39
column
Write an sql query to display all the Student names where the length of the name is
40
5 characters
E CreateaTableforLibraryInformation:(forquestionsfrom41to50)
WritesqlquerytodisplaythelistofauthorsfromHimalayapublications
41

Write sql querytodisplaythe total costofbooks purchasedPublisher wise

42

WritesqlquerytocountthetotalnumberofbooksunderKalyani publications
43

44 WritesqlquerytorenamethecolumnPublisherasPublications
Writeasqlquerytodisplaythebooksintheascendingorderof DatePurchased
45

WritesqlquerytocreateanindexonthefieldsBookNameandAuthor
46

Write sql query to display the books whose price is between 500 and 700
47

Writesqlquerytoincreasethepriceofallthebooksby200for
48
publishersotherthanHimalayaorKalyani
Writesqlquerytodisplaythebookdetailswhereauthorname contains the name Sharma
49

Create a view to display the fields BookId and BookName where the Publisher is
50
Himalaya
Creating Supplier Table:
SQL>create table Supplier(
Sup_No varchar2(3) primary key,
Sup_Name varchar2(10),
Item_Supplied varchar2(10),
Item_Price number(4),
City varchar2(10)
);
Output:
Table created.
SQL>desc supplier;
Output:

Inserting the Data into Supplier Table:

SQL>insert into supplier values('S1','Suresh','Keyboard',400,'Hyderabad');
Output: 1 row created.
SQL> insert into supplier values ('S2','Kiran','Processor',8000,'Delhi');
Output: 1 row created.
SQL> insert into supplier values ('S3','Mohan','Mouse',350,'Delhi');
Output: 1 row created.
SQL> insert into supplier values ('S4','Ramesh','Processor',9000,'Bangalore');
Output: 1 row created.
SQL> insert into supplier values ('S5','Manish','Printer',6000,'Mumbai');
Output: 1 row created.
SQL> insert into supplier values ('S6','Srikanth','Processor',8500,'Chennai');
Output: 1 row created
 Data saving permanently:

SQL> Commit;

Commit complete.
Now I want to see the data whether the data is saved or not in my table.
Query: SQL>select * from Supplier;

Output:

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

Query : Select Sup_No, Sup_Name from Supplier where Sup_Name like 'R%';

Output :

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

Query : Select * from Supplier WHERE Item_Supplied='Processor' AND

City='Delhi';
Output :

3. Write sql query to display the names of suppliers who supply the same items as supplied by Ramesh.
Query : select sup_name,Item_supplied from supplier
where item_supplied = (select Item_Supplied from Supplier where
Sup_Name='Ramesh');
Output :
4. Write sql query to increase the price of Keyboard by 200.

Query : update Supplier set Item_Price=Item_Price+200 Where

item_Supplied like 'Keyboard';
Or
update Supplier set Item_Price=Item_Price+200 Where
item_Supplied='Keyboard';
Output : 1 row updated.

Checking the Item_Price updated or not

SQL>select * from Supplier;

Output :

5. Write sql query to display Supplier number , Supplier names and item pricefor suppliers in delhi
in the ascending order of item price.

Query : Select Sup_No, Sup_Name, Item_Supplied, Item_Price,city from Supplier

where city = 'Delhi' order by item_price ASC;

Output :

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

Query : SQL>desc supplier;

Output:
 Query : SQL>alter table supplier add(Contact_No number(10));

Output : Table altered.

Checking the Contact_No attribute (Column) added or not.

Query : SQL>desc Supplier;

Output :

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

Query : SQL>select * from Supplier;

Output :

Query : SQL>delete from Supplier

Where Item_Price=(Select min(Item_Price) from Supplier);

Output : 1 rows deleted.

Query : SQL>select * from Supplier;

Output :
8. Create a view on the table which displays only supplier numbers and suppliernames.
Query : SQL>Create view Supplierview As select sup_no,Sup_Name from
Supplier;
Output : View created.

Checking View Created or Not:

Query : SQL>select * from Supplierview;

Output :

9. Write sql query to display the records in the descending order of itemprice for each
itemsupplied.

Query : SQL> select * from supplier order by item_supplied, item_price desc;

Output :

10. Write sql query to display the records of suppliers who supply items other
than Processor or Keyboard.

Query : SQL>select *from Supplier Where item_supplied NOT IN('Processor','Keyboard');

Output :
B. Below are the details of Employees working for a software Company.
(Forquestionsfrom11to20)
CreatethetablecalledEmpDetailswiththebelowmentioneddetails.

CreatingEmpDetailsTable:
SQL>create table EmpDetails(Eid varchar2(5) primary key, EName char(8),
DOB date, Designation char(20),Salary number(6), DOJ date);

Output:
Table created.

Inserting theData intoEmpDetails Table:

SQL>insert into EmpDetails values('&Eid','&EName','&DOB','&Designation',&Salary, ‘&DOJ');
Enter value for eid:E101
Enter value for ename:Suma
Enter value for dob: 29-Dec-89
Enter value for designation: Designer
Enter value for salary:20000
Enter value for doj:01-Apr-10

Output:
1 row created.

SQL>/
Enter value for eid:E102
Enter value for ename:Amit
Enter value for dob: 10-Jan-95
Enter value for designation:Programmer
Enter value for salary:25000
Enter value for doj: 18-Feb-18
Output:
1 row created.
SQL>/
Enter value for eid:E103
Enter value for ename: Payal
Enter value for dob:15-Aug-85
Enter value for designation:Tester
Enter value for salary:35000
Enter value for doj:12-Jun-11
Output:
1 row created.

SQL>/
Enter value for eid:E104
Enter value for ename:Kiran
Enter value for dob: 20-Apr-90
Enter value for designation: Programmer
Enter value for salary:40000
Enter value for doj: 07-Mar-14
Output:
1 row created.
SQL>/
Enter value for eid:E105
Enter value for ename: Meenal
Enter value for dob:29-May-83
Enter value for designation: DBA
Enter value for salary:50000
Enter value for doj:09-Dec-11
Output:
1 row created.
SQL>/
Enter value for eid:E106
Enter value for ename: Sheila
Enter value for dob:01-May-70
Enter value for designation: Analysist
Enter value for salary:60000
Enter value for doj:25-Sep-18

Output:
1 row created.

SQL>/
Enter value for eid:E107
Enter value for ename: Swamy
Enter value for dob:13-Jan-85
Enter value for designation: Programmer
Enter value for salary:45000
Enter value for doj: 14-Feb-16
Output:
1 row created.
SQL>/
Enter value for eid:E108
Enter value for ename:Sushma
Enter value for dob:22-Dec-76
Enter value for designation: DBA
Enter value for salary:45000
Enter value for doj:31-Jan-12

Output:
1 row created.
SQL> Commit;
Commit Complete.

Now Iwant to see the data whether the data is saved or not in my table.
SQL>select * from EmpDetails;
Output :

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

Query: SQL> select * from EmpDetails Where designation='Programmer';

Output :

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

Query : SQL>select * from EmpDetails where doj>'31-Dec-14';

Output :

13. Write sql query to display all the employees whose name ends with ‘A’.

Query : SQL>select * from EmpDetails where Ename like '%a';

Or
select * from empdetails where trim(ename) like '%a';
Output :

14. Write sql query to display the total salary of all the employees whose designation is
programmer.
Query : SQL>select sum(Salary) from EmpDetails
Where Designation='Programmer';
Output :
 Or
Query : select Designation, sum(Salary) from EmpDetails group by designation
having designation= 'Programmer';

Output :

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

Query: SQL>select upper(EName) as UPENAME from EmpDetails;

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

Query : SQL> select * from empdetails where doj in (select min(doj) from
empdetails);
Output :

or

Query : SQL> select * from empdetails where months_between(SYSDATE, DOJ)=(SELECT

MAX(MONTHS_BETWEEN(SYSDATE,DOJ))FROM EMPDETAILS);

Output :

17. Write sql query to display the details of the employees whose name contains ‘ee’.
Query : SQL>select * from EmpDetails where EName LIKE '%ee%';
Output :

18. Write sql query to increase the salaries of employees by 5000 whose designation is DBA.
Testing : Before Updation
Query : SQL>select * from EmpDetails;
Output :
Testing : After Updation
Query : SQL>update EmpDetails set salary=Salary+5000
Where Designation='DBA';
Output : 2 rows updated.

Query : SQL> select * from EmpDetails;

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

Query : SQL> select * from EmpDetails

Where salary>(Select avg(salary) from EmpDetails);

Output :

20. Write sql query to display the record in the following format: xxxxxxxxx is working as
xxxxxxxxxxxxxx with aSalary [Link]
eg: Suma is working as Designer with a Salary of Rs. 20000
Query : SQL> SELECT EName ||'is workingas'||
Designation ||'with a Salary of Rs.'||Salary FROM EmpDetails;
Output :
C. Create the two tables as shown below with the given constraints:
(for questions 21 to 30)

CreatingDepartmentTable:

SQL>createtableDepartment(
DeptIdvarchar2(10) primary key,
Dnamevarchar2(15)
);

Output:
Tablecreated.
InsertingtheDataintoDepartmentTable:
SQL> insert into Department values('&DeptId', '&Dname');
EntervalueforDeptId:D1
EntervalueforDname:Sales

Output:
1rowcreated.
SQL>/
EntervalueforDeptId:D2
EntervalueforDname:Marketing

Output:
1rowcreated.

EntervalueforDeptId:D3 Enter
value for Dname: Finance

Output:
1rowcreated.

SQL>Commit;

Commitcomplete.

NowIwanttoseethedatawhetherthedataissavedornotinmytable.

SQL>select * from Department;

Output:

CreatingEmployeeTable:

SQL>createtableEmployee(
Eid varchar2(10) primary key,
Enamevarchar2(10),
Did varchar2(10) REFERENCES department(deptid),
Designation varchar2(10),
salarynumber(10)CHECK(salary>10000),
Doj date
);
Output:
Tablecreated.

InsertingtheDataintoEmployeeTable:
SQL>insertintoEmployeevalues
('&Eid','&Ename','&Did','&Designation',&Salary,'&Doj');
Entervalueforeid:101
Enter value for ename: Sudha
Entervaluefordeptid:D2
Enter value for designation: Clerk
Entervalueforsalary:20000
Entervaluefordoj:01-Apr-10
Output:
1rowcreated.S
QL> /
Entervalueforeid:102 Enter
value for ename: David
Entervaluefordeptid:D1
Enter value for designation: Manager
Entervalueforsalary:50000
Enter value for doj: 18-Feb-18
Output:
1rowcreated.

SQL>/
Entervalueforeid:103
Enter value for ename: Preethi
Entervaluefordeptid:D3
Enter value for designation: Clerk
Entervalueforsalary:35000
Entervaluefordoj:13-Jun-11
Output:
1rowcreated.

SQL>/
Entervalueforeid:104 Enter
value for ename: Kiran
Entervaluefordeptid:D1
Entervaluefordesignation:Salesman
Entervalueforsalary:20000
Enter value for doj: 17-Mar-14
Output:
1rowcreated.

SQL>/
Entervalueforeid:105
Enter value for ename: Meenal
Entervaluefordeptid:D2
Enter value for designation: Clerk
Entervalueforsalary:50000
Entervaluefordoj:09-Dec-11
Output:
1rowcreated.

SQL>/
Entervalueforeid:106
Enter value for ename: Sunitha
Entervaluefordeptid:D3
Enter value for designation: Manager
Entervalueforsalary:60000
Enter value for doj: 25-Sep-18
Output:
1rowcreated.

SQL>/
Entervalueforeid:107 Enter
value for ename: Akhil
Entervaluefordeptid:D3
Enter value for designation: Clerk
Entervalueforsalary:25000
Entervaluefordoj:14-Feb-16
Output:
1rowcreated.

SQL>/
Entervalueforeid:108
Enter value for ename: Sushma
Entervaluefordeptid:D2
Enter value for designation: Manager
Entervalueforsalary:45000
Enter value for doj: 31-Jan-12
Output:
1 row created.

SQL> commit;

Commitcomplete.

NowIwanttoseethedatawhetherthedataissavedornotinmytable.

SQL>select * from Employee;

Output:

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

Query : SQL>select * from employee where salary>(select

avg(salary) from employee);

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

Query : SQL>select Eid, Ename, Dname from Employee, department

Where [Link]=[Link];
Output :

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

Query : SQL>select * from employee;

Output :

Query : SQL>select * from Employee order by Salary Desc;

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

Query : SQL> Select DISTINCT designation from employee;

Output :

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

Query : SQL> select eid,ename,designation, doj,did,dname,salary from

employee20 inner join department on
[Link]=[Link] order by dname,salary asc;
Output :

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

Query : SQL>Select * from Employee

Where Designation='Clerk' AND DID='D2';
Output :
27. Write sql query to display all the employees who joined in the year 2011.

Query : SQL> select * from employee20 where doj between '01-JAN-11'

And '31-DEC-11';
Output :

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

Query : SQL>select * from employee where

to_char(doj,'MON')='FEB';
Output :

29. Write sql query to display all the employees whose salary is between 30000
and 45000.

Query : SQL> select * from employee

Where salary BETWEEN 30000 AND45000;

Output :
30. Write sql query to display all the employee details along with
their work experience in the company till current date.
Query : SQL> select eid,ename,round((sysdate-doj)/30/12,0)
experience
from employee20;
Output :
D. Below are the details of Students enrolled in various course of
[Link]
(For questions from 31 to 40)
Create the table called Student with the below mentioned details.

Creating Student Table:

SQL> Create table Student (Sid number(5) primary key,

Sname char(8),
DOB date,
State char(15),
Gender char(2),
Category char(15),
Course char(10));
Output:
Table created.

Inserting the Data into Student Table:

SQL> insert into Student values
(&Sid,'&Sname','&DOB','&State','&Gender','&Category','&Course');
Enter value for sid: 1001
Enter value for sname: Neha
Enter value for dob:29-Dec-02
Enter value for state: Telangana
Enter value for gender: F
Enter value for category: Gen
Enter value for course: Comp

Output:
1 row created.

SQL>/
Enter value for sid: 1002
Enter value for sname: Arun
Enter value for dob: 10-Jan-02
Enter value for state: Telangana
Enter value for gender: M
Enter value for category: OBC
Enter value for course: Honors
Output:
1 row created.

SQL>/
Enter value for sid: 1003
Enter value for sname: Payal
Enter value for dob:15-Aug-01
Enter value for state: Maharashtra
Enter value for gender: F
Enter value for category: Gen
Enter value for course: Appl
Output:
1 row created.

SQL>/
Enter value for sid: 1004
Enter value for sname: Amrita
Enter value for dob: 20-Apr-02
Enter value for state: Karnataka
Enter value for gender: F
Enter value for category: OBC
Enter value for course: Honors
Output:
1 row created.

SQL>/
Enter value for sid: 1005
Enter value for sname: Pavan
Enter value for dob: 29-May-03
Enter value for state: Andhra
Pradesh Enter valuefor gender: M
Enter value for category: ExServicemen
Enter value for course: Comp

Output:
1 row created.

SQL>/
Enter value for sid:1006
Enter value for sname: Anchal
Enter value for dob: 1-May-03
Enter value for state: Gujarat
Enter value for gender: F
Enter value for category: OBC
Enter value for course: Comp

Output:
1 row created.

SQL>/
Enter value for sid: 1007
Enter value for sname: Ramya
Enter value for dob: 13-Jan-02
Enter value for state: Telangana
Enter value for gender: F
Enter value for category: Gen
Enter value for course: Appl

Output:
1 row created.
SQL>/
Enter value for sid: 1008
Enter value for sname: Rakesh
Enter value for dob: 22-Dec-01
Enter value for state: Andhra
Pradesh Enter value for gender: M
Enter value for category: Sports
Enter value for course: Comp

Output:
1 row created.
SQL> commit;
Commit complete.
Now I want to see the data whether the data is saved or not in my table.
SQL>select * from Student;

Output:

31. Write sql query to display the students who are not from
Telangana or Andhra Pradesh.
Query : SQL>select * from Student where state
NOT IN('Telangana','Andhra Pradesh');
Output :
 32. Create a view to display the columns Sid, Sname for students
belonging to Telangana.
Query : SQL>Create view Student_View as select Sid,
Sname from Student where state='Telangana';

Output : View created.

Query : SQL>Select * from Student_View;

Output :

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

Query : SQL>Create index Sname_Index on Student(Sname);

Output : Index created.

34. Write sql query to display all the female students enrolled under
Comp course and who belong to OBC.

Query : SQL>Select * from Student

Where Gender='F' AND Course='Comp' AND
Category='OBC';
Output :

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

Query : SQL>Select Sid, Sname,

round(to_number(months_between(sysdate,
DOB)/12),0) as AGE from Student;
Output :
36. Write sql query to display the students in the ascending order of
their names for each course.
Query : Select * from Student order by Sname, course asc;

Output :

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

Query : select * from Student;

Output :
Query : SQL>delete from Student
Where course='Comp' AND EXTRACT(YEAR
FROM TO_DATE(dob,'dd-mon-yy'))>2002;
Output :

38. Write a sql query to add two new columns Contactno and
Email to the existing fields.
Query : SQL> desc Student;

Output :

Query : SQL>alter table Student ADD(

Contactno number(10),Email varchar2(25));
Output : Table altered.
Query : SQL> desc Student;
Output :
39. Write an sql query to display all the Student names prefixed with
Mr./Mrs. Based on Gender column.

Query : SQL>Select SID,Case when Gender='M' then

'Mr.' when Gender='F' then 'Mrs.' end, Sname from Student;
Output :

40. Write an sql query to display all the Student names where the
length of the name is 5 characters.
Query : SQL>select sname from student where
length(trim(sname))=5;
Output :
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

Creating Student Table:

SQL>Create table Library(
BookId varchar2(5) primary key,
BookName char(25)Not Null,
Author char(20),
DatePurchased date,
Publisher char(10),
Price number(5)
);

Output:
Table created.
Inserting the Data into Student Table:
SQL>insert into Library values
('&BookId','&BookName','&Author','&DatePurchased','&Publisher',
&Price);
Enter value for bookid: B101
Enter value for bookname: CostAccounting
Enter value for author: JainNarang
Enter value for datepurchased: 11-Feb-13
Enter value for publisher: Kalyani
Enter value for price: 800
Output:
1 row created.
SQL>/
Enter value for bookid: B102
Enter value for bookname: Business Statistics
Enter value for author: OPAggrawal
Enter value for datepurchased: 22-Dec-11
Enter value for publisher: Himalaya
Enter value for price:750
Output:
1 row created.
SQL>/
Enter value for bookid: B103
Enter value for bookname: RDBMS
Enter value for author: CJDate
Enter value for datepurchased: 02-Mar-15
Enter value for publisher: THM
Enter value for price: 900
Output:
1 row created.
SQL>/
Enter value for bookid: B104
Enter value for bookname: MGMT Accounting
Enter value for author:RKSharma
Enter value for datepurchased: 19-Apr-16
Enter value for publisher:Kalyani
Enter value for price: 450
Output:
1 row created.

SQL>/
Enter value for bookid: B105
Enter value for bookname: Operating System
Enter value for author: Galvin
Enter value for datepurchased: 25-Nov-13
Enter value for publisher: PHI
Enter value for price: 750
Output:
1 row created.
SQL>/
Enter value for bookid: B106
Enter value for bookname: Adv. Accounting
Enter value for author: ScGupta
Enter value for datepurchased: 16-Apr-18
Enter value for publisher: Himalaya
Enter value for price:600
Output:
1 row

created.

SQL>

Commit;

Commit

complete.

SQL>Select *from Library;

[Link] sql query to display the list of authors from Himalaya publications.

Query : SQL> select author,publisher from

library where publisher='Himalaya';
Output :

[Link] sql query to display the total cost of books purchased Publisherwise.

Query : SQL> Select sum(Price), Publisher from Library

group by Publisher;

Output :
[Link] sql query to count the total number of books under
Kalyani publications.

Query : SQL> Select count(BookName), Publisher from

Library where Publisher='Kalyani' group by
Publisher;
Output :

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

Query :
SQL>desc
library; Output :

Query : SQL> alter table library rename column

 publisher to Publications;

Output : Table

altered. Query :

SQL>desc

library; Output :

[Link] a sql query to display the books in the

ascending order of DatePurchased.

Query : SQL>Select * from Library ORDER BY

datepurchased ASC; Output :

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

Query : SQL> create index LibraryIndex ON Library

(BookName, Author);
Output : Index Created.
[Link] sql query to display the books whose price is between 500

and 700

Query : SQL>Select * from Library where Price BETWEEN

500 AND
700;

Output :

[Link] sql query to increase the price of all the books by 200 for
publishers other than Himalaya or Kalyani.

Query : SQL>Select * from Library;

Query : SQL>update library set price=price+200

where publications NOT
IN('Himalaya','Kalyani');

Output : 2 rows updated.

Query : SQL>Select * from Library;

Output :
[Link] sql query to display the book details where author name
contains the name Sharma.
Query : SQL> select * from Library where Author like '%Sharma%';
Output :

[Link] a view to display the fields BookId and BookName

where the Publisher is Himalaya.

Query : SQL>Create view LibraryView as select BookId, BookName

from Library where Publications='Himalaya';

Output : View created.

Query : SQL>Select * from LibraryView;
Output :