A.

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

SQL> CREATE TABLE SUPPLIER(SUP_NO VARCHAR(2) PRIMARY KEY, SUP_NAME

VARCHAR2(20), ITEM_SUPPLIED VARCHAR2(20), ITEM_PRICE NUMBER(6,2), CITY
VARCHAR2(20));

Table created.

SQL> DESC SUPPLIER;

Name Null? Type

----------------------------------------- -------- ----------------------------

SUP_NO NOT NULL VARCHAR2(2)

SUP_NAME VARCHAR2(20)

ITEM_SUPPLIED VARCHAR2(20)

ITEM_PRICE NUMBER(6,2)

CITY VARCHAR2(20)

SQL> INSERT INTO SUPPLIER

VALUES('&SUP_NO','&SUP_NAME','&ITEM_SUPPLIED','&ITEM_PRICE','&CITY');

Enter value for sup_no: S1

Enter value for sup_name: Suresh

Enter value for item_supplied: Keyboard

Enter value for item_price: 400

Enter value for city: Hyderabad

old 1: INSERT INTO SUPPLIER

VALUES('&SUP_NO','&SUP_NAME','&ITEM_SUPPLIED','&ITEM_PRICE','&CITY')

new 1: INSERT INTO SUPPLIER VALUES('S1','Suresh','Keyboard','400','Hyderabad')

1 row created.

SQL> /

Enter value for sup_no: S2

Enter value for sup_name: Kiran

Enter value for item_supplied: Processor

Enter value for item_price: 8000

Enter value for city: Delhi

old 1: INSERT INTO SUPPLIER

VALUES('&SUP_NO','&SUP_NAME','&ITEM_SUPPLIED','&ITEM_PRICE','&CITY')

new 1: INSERT INTO SUPPLIER VALUES('S2','Kiran','Processor','8000','Delhi')

1 row created.

SQL> /

Enter value for sup_no: S3

Enter value for sup_name: Mohan

Enter value for item_supplied: Mouse

Enter value for item_price: 350

Enter value for city: Delhi

old 1: INSERT INTO SUPPLIER

VALUES('&SUP_NO','&SUP_NAME','&ITEM_SUPPLIED','&ITEM_PRICE','&CITY')

new 1: INSERT INTO SUPPLIER VALUES('S3','Mohan','Mouse','350','Delhi')

1 row created.

SQL> SELECT * FROM SUPPLIER;

SUP_NO SUP_NAME ITEM_SUPPLIED ITEM_PRICE CITY

-- -------------------- -------------------- ---------- -------------------- ----------

S1 Suresh Keyboard 600 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

6 rows selected.
I. Queries on Supplier Table

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%';

SUP_NO SUP_NAME
-- --------------------------------
S4 Ramesh

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';

SUP_NAME
--------------------
Kiran

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 = (SELECT

ITEM_SUPPLIED FROM SUPPLIER WHERE SUP_NAME='Ramesh');

SUP_NAME
--------------------
Kiran
Ramesh

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

SQL> UPDATE SUPPLIER SET ITEM_PRICE = ITEM_PRICE + 200 WHERE

ITEM_SUPPLIED='Keyboard';
1 row updated.

SQL> SELECT ITEM_SUPPLIED, ITEM_PRICE FROM SUPPLIER WHERE

ITEM_SUPPLIED='Keyboard';

ITEM_SUPPLIED ITEM_PRICE
-------------------- ----------
Keyboard 600
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;

SUP_NO SUP_NAME ITEM_PRICE

-- -------------------- ----------
S3 Mohan 350
S2 Kiran 8000

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

SQL> ALTER TABLE SUPPLIER ADD CONTACTNO NUMBER(10);

Table altered.

SQL> DESC SUPPLIER;

Name Null? Type
----------------------------------------- -------- ----------------------------
SUP_NO NOT NULL VARCHAR2(2)
SUP_NAME VARCHAR2(20)
ITEM_SUPPLIED VARCHAR2(20)
ITEM_PRICE NUMBER(6,2)
CITY VARCHAR2(20)
CONTACTNO NUMBER(10)

SQL> SELECT * FROM SUPPLIER;

SUP_NO SUP_NAME ITEM_SUPPLIED ITEM_PRICE CITY CONTACTNO

-- -------------------- -------------------- ---------- -------------------- ----------
S1 Suresh Keyboard 600 Hyderabad
S2 Kiran Processor 8000 Delhi
S4 Ramesh Processor 9000 Bangalore
S5 Manish Printer 6000 Mumbai
S6 Srikanth Processor 8500 Chennai

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

SQL> SELECT MIN(ITEM_PRICE) FROM SUPPLIER;

MIN(ITEM_PRICE)
---------------
350
 SQL> DELETE FROM SUPPLIER WHERE ITEM_PRICE = (SELECT MIN(ITEM_PRICE) FROM
SUPPLIER);

1 row deleted.

SQL> SELECT * FROM SUPPLIER;

SUP_NO SUP_NAME ITEM_SUPPLIED ITEM_PRICE CITY CONTACTNO

-- -------------------- -------------------- ---------- -------------------- ----------
S1 Suresh Keyboard 600 Hyderabad
S2 Kiran Processor 8000 Delhi
S4 Ramesh Processor 9000 Bangalore
S5 Manish Printer 6000 Mumbai
S6 Srikanth Processor 8500 Chennai

5 rows selected.

8. Create a view on the table which displays only supplier numbers and supplier names.
SQL> CREATE VIEW SUP_NO_NAME AS SELECT SUP_NO, SUP_NAME FROM SUPPLIER;

View created.

SQL> SELECT * FROM SUP_NO_NAME;

SUP_ NO SUP_NAME
-- --------------------
S1 Suresh
S2 Kiran
S3 Mohan
S4 Ramesh
S5 Manish
S6 Srikanth

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

SQL> SELECT ITEM_SUPPLIED, ITEM_PRICE FROM SUPPLIER ORDER BY ITEM_PRICE DESC;

ITEM_SUPPLIED ITEM_PRICE
-------------------- --------------------------------------------------
Processor 9000
Processor 8500
Processor 8000
Printer 6000
Keyboard 600
Mouse 350

6 rows selected.

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 NOT IN ('Processor', 'Keyboard');

SUP_NO SUP_NAME ITEM_SUPPLIED ITEM_PRICE CITY

-- -------------------- -------------------- ---------- ------------------------------------------------------------
S3 Mohan Mouse 350 Delhi
S5 Manish Printer 6000 Mumbai
B. Create the table called EmpDetails with the below mentioned details. (For questions from
11 to20)

SQL> CREATE TABLE EMPDETAILS(EID VARCHAR2(4) PRIMARY KEY, ENAME VARCHAR2(20),

DOB DATE, DESIGNATION VARCHAR2(20), SALARY NUMBER(7,2), DOJ DATE);

Table created.

SQL> DESC EMPDETAILS;

Name Null? Type
----------------------------------------- -------- ----------------------------
EID NOT NULL VARCHAR2(4)
ENAME VARCHAR2(20)
DOB DATE
DESIGNATION VARCHAR2(20)
SALARY NUMBER(7,2)
DOJ DATE

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
old 1: INSERT INTO EMPDETAILS
VALUES('&EID','&ENAME','&DOB','&DESIGNATION','&SALARY','&DOJ')
new 1: INSERT INTO EMPDETAILS VALUES('E101','Suma','29-Dec-
89','Designer','20000','01-Apr-10')
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
old 1: INSERT INTO EMPDETAILS
VALUES('&EID','&ENAME','&DOB','&DESIGNATION','&SALARY','&DOJ')
new 1: INSERT INTO EMPDETAILS VALUES('E102','Amit','10-Jan-
95','Programmer','25000','18-Feb-18')

1 row created.
Below are the details of Employees working for a software Company

SQL> SELECT * FROM EMPDETAILS;

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- --------------------------------------------------
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 07-MAR-14
E105 Meenal 29-MAY-83 DBA 55000 09-DEC-11
E106 Sheila 01-MAY-70 Analyst 60000 25-SEP-18
E107 Swamy 13-JAN-85 Programmer 45000 14-FEB-16
E108 Sushma 22-DEC-76 DBA 50000 31-JAN-12

7 rows selected.

II. Queries on Employees working for a software Company

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

SQL> SELECT * FROM EMPDETAILS WHERE DESIGNATION='Programmer';

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- -------------------------------------------
E102 Amit 10-JAN-95 Programmer 25000 18-FEB-18
E104 Kiran 20-APR-90 Programmer 40000 07-MAR-14
E107 Swamy 13-JAN-85 Programmer 45000 14-FEB-16

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

SQL> SELECT * FROM EMPDETAILS WHERE DOJ>'01-Jan-15';

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- ---------------------------------------------------
E102 Amit 10-JAN-95 Programmer 25000 18-FEB-18
E106 Sheila 01-MAY-70 Analyst 60000 25-SEP-18
E107 Swamy 13-JAN-85 Programmer 45000 14-FEB-16
13. Write sql query to display all the employees whose name ends with ‘a’.

SQL> SELECT * FROM EMPDETAILS WHERE ENAME LIKE '%a';

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- ------------------------------------------------
E101 Suma 29-DEC-89 Designer 20000 01-APR-10
E106 Sheila 01-MAY-70 Analyst 60000 25-SEP-18
E108 Sushma 22-DEC-76 DBA 45000 31-JAN-12

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

SQL> SELECT SUM(SALARY) AS TOTAL_SALARY FROM EMPDETAILS WHERE

DESIGNATION='Programmer';

TOTAL_SALARY
------------
110000

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

SQL> SELECT UPPER(ENAME) FROM EMPDETAILS;

UPPER(ENAME)
--------------------
SUMA
AMIT
PAYAL
KIRAN
MEENAL
SHEILA
SWAMY
SUSHMA

8 rows selected.
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);

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- ------------------------------------------

E101 Suma 29-DEC-89 Designer 20000 01-APR-10

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

SQL> SELECT * FROM EMPDETAILS WHERE ENAME LIKE '%ee%';

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- ---------

E105 Meenal 29-MAY-83 DBA 50000 09-DEC-11

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

SQL> UPDATE EMPDETAILS SET SALARY = SALARY + 5000 WHERE DESIGNATION = 'DBA';

2 rows updated.

SQL> SELECT * FROM EMPDETAILS;

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- ---------

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 07-MAR-14

E105 Meenal 29-MAY-83 DBA 55000 09-DEC-11

E106 Sheila 01-MAY-70 Analyst 60000 25-SEP-18

E107 Swamy 13-JAN-85 Programmer 45000 14-FEB-16

E108 Sushma 22-DEC-76 DBA 50000 31-JAN-12

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

SQL> SELECT AVG(SALARY) FROM EMPDETAILS;

AVG(SALARY)

-----------

41250

SQL> SELECT * FROM EMPDETAILS WHERE SALARY> (SELECT AVG(SALARY) FROM

EMPDETAILS);

EID ENAME DOB DESIGNATION SALARY DOJ

---- -------------------- --------- -------------------- ---------- ---------

E105 Meenal 29-MAY-83 DBA 55000 09-DEC-11

E106 Sheila 01-MAY-70 Analyst 60000 25-SEP-18

E107 Swamy 13-JAN-85 Programmer 45000 14-FEB-16

E108 Sushma 22-DEC-76 DBA 50000 31-JAN-12

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 and Dname is NOT NULL

DEPRTMENT TABLE

CREATE TABLE DEPARTMENT(DEPTID CHAR(2) PRIMARY KEY, DNAME VARCHAR2(20) NOT

NULL);

SQL> DESC DEPARTMENT;

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTID NOT NULL CHAR(2)

DNAME NOT NULL VARCHAR2(20)

EMPLOYEE TABLE
CREATE TABLE EMPLOYEE(EID NUBMER(3) PRIMARY KEY, ENAME VARCHAR2(20), DEPTID
CHAR(2) REFERENCES DEPARTMENT, DESIGNATION VARCHAR2(20), SALARY NUMBER(7,2),
DOJ DATE);

Table created.

SQL> DESC EMPLOYEE;

Name Null? Type

----------------------------------------- -------- ----------------------------

EID NOT NULL NUMBER(3)

ENAME VARCHAR2(20)

DEPTID CHAR(2)

DESIGNATION VARCHAR2(20)

SALARY NUMBER(7,2)

DOJ DATE

INSERT INTO DEPARTMENT VALUES(‘&DEPTID’, ‘&DNAME’);

SQL> SELECT * FROM DEPARTMENT;

DE

PTID DNAME

-- --------------------

D1 Sales

D2 Marketing

D3 Finance

INSERT INTO EMPLOYEE VALUES

(&EID,’&ENAME’,’&DEPTID’,’&DESIGNATION’,’&SALARY’,’&DOJ’);
SQL> SELECT * FROM EMPLOYEE;

EID ENAME DEPTID DESIGNATION SALARY DOJ

---------- -------------------- -- -------------------- ---------- ---------

101 Sudha D2 Clerk 20000 01-APR-10

102 David D1 Manager 50000 18-FEB-18

103 Preethi D3 Clerk 35000 13-JUN-11

104 Kiran D1 Salesman 20000 07-MAR-14

105 Meenal D2 Clerk 50000 09-DEC-11

106 Sunitha D3 Manager 60000 25-SEP-18

107 Akhil D3 Clerk 25000 14-FEB-16

108 Sushma D2 Manager 45000 31-JAN-12

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

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

EID ENAME DEPTID DESIGNATION SALARY DOJ

---------- -------------------- -- -------------------- ---------- ---------

102 David D1 Manager 50000 18-FEB-18

105 Meenal D2 Clerk 50000 09-DEC-11

106 Sunitha D3 Manager 60000 25-SEP-18

108 shma D2 Manager 45000 31-JAN-12

21. Write sql query to display the fields Eid, Ename and Dname
 SQL> SELECT EID, ENAME, DNAME FROM EMPLOYEE E, DEPARTMENT D WHERE E.DEPTID =
D.DEPTID;

EID ENAME DNAME

---------- -------------------- --------------------

101 Sudha Marketing

102 David Sales

103 Preethi Finance

104 Kiran Sales

105 Meenal Marketing

106 Sunitha Finance

107 Akhil Finance

108 Sushma Marketing

8 rows selected.

22. Write sql query to sort the employee table in the descending order ofsalaries.

SQL> SELECT * FROM EMPLOYEE ORDER BY SALARY DESC;

EID ENAME DEPTID DESIGNATION SALARY DOJ

---------- -------------------- -- -------------------- ---------- ------------------------------------------------

106 Sunitha D3 Manager 60000 25-SEP-18

102 David D1 Manager 50000 18-FEB-18

105 Meenal D2 Clerk 50000 09-DEC-11

108 Sushma D2 Manager 45000 31-JAN-12

103 Preethi D3 Clerk 35000 13-JUN-11

107 Akhil D3 Clerk 25000 14-FEB-16

101 Sudha D2 Clerk 20000 01-APR-10

104 Kiran D1 Salesman 20000 07-MAR-14

8 rows selected.

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

SQL> SELECT DISTINCT(DESIGNATION) FROM EMPLOYEE ;

 DESIGNATION

--------------------

Clerk

Manager

Salesman

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

SQL> SELECT EID, ENAME, DESIGNATION, SALARY, DNAME FROM EMPLOYEE E,

DEPARTMENT D WHERE E.DEPTID = D.DEPTID GROUP BY EID, ENAME, DESIGNATION,
DNAME, SALARY ORDER BY SALARY;

EID ENAME DESIGNATION SALARY DNAME

---------- -------------------- -------------------- ---------- --------------------

101 Sudha Clerk 20000 Marketing

104 Kiran Salesman 20000 Sales

107 Akhil Clerk 25000 Finance

103 Preethi Clerk 35000 Finance

108 Sushma Manager 45000 Marketing

105 Meenal Clerk 50000 Marketing

102 David Manager 50000 Sales

106 Sunitha Manager 60000 Finance

8 rows selected.

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

SQL> SELECT * FROM EMPLOYEE E, DEPARTMENT D WHERE D.DEPTID='D2' AND

DESIGNATION ='Clerk';

EID ENAME DEPTNO DESIGNATION SALARY DOJ DEPTID DNAME

---------- -------------------- -- -------------------- ---------- --------- -- ---------------------------------------------

101 Sudha D2 Clerk 20000 01-APR-10 D2 Marketing

105 Meenal D2 Clerk 50000 09-DEC-11 D2 Marketing

26. Write sql query to display all the employees who joined in the year 2011

SQL> SELECT * FROM EMPLOYEE WHERE EXTRACT (YEAR FROM DOJ)=2011;

EID ENAME DEPTNO DESIGNATION SALARY DOJ

---------- -------------------- -- -------------------- ---------- ---------

103 Preethi D3 Clerk 35000 13-JUN-11

105 Meenal D2 Clerk 50000 09-DEC-11

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

SQL> SELECT * FROM EMPLOYEE WHERE EXTRACT(MONTH FROM DOJ) = 2;

EID ENAME DEPTNO DESIGNATION SALARY DOJ

---------- -------------------- -- -------------------- ---------- -------------------------------------------------

102 David D1 Manager 50000 18-FEB-18

107 Akhil D3 Clerk 25000 14-FEB-16

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

SQL> SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 30000 AND 45000;

EID ENAME DEPTNO DESIGNATION SALARY DOJ

---------- -------------------- -- -------------------- ---------- ------------------------------------------------

103 Preethi D3 Clerk 35000 13-JUN-11

108 Sushma D2 Manager 45000 31-JAN-12

29. Write sql query to display all the employee details along with their work experience in the
company till current date

SQL> SELECT EID, ENAME, DESIGNATION, SALARY, EXTRACT(YEAR FROM SYSDATE) -

EXTRACT(YEAR FROM DOJ) AS EXPERIENCE FROM EMPLOYEE;

EID ENAME DESIGNATION SALARY EXPERIENCE

---------- -------------------- -------------------- ---------- ---------------------------------------------------

101 Sudha Clerk 20000 12

102 David Manager 50000 4

103 Preethi Clerk 35000 11

104 Kiran Salesman 20000 8

105 Meenal Clerk 50000 11

106 Sunitha Manager 60000 4

107 Akhil Clerk 25000 6

108 Sushma Manager 45000 10

8 rows selected.
D. Create the table called Student with the below mentioned details. (For questions from 31
to 40)

CREATE TABLE STUDENT(SID NUMBER(4) PRIMARY KEY, SNAME VARCHAR2(20), DOB

DATE, STATE VARCHAR2(20), GENDER CHAR(1), CATEGORY VARCHAR2(20), COURSE
VARCHAR2(10));

Table created.

INSERT INTO STUDENT VALUES

(&SID,’&SNAME’,’&DOB’,’&STATE’,’&GENDER’,’&CATEGORY’,’&COURSE’);

Below are the details of Students enrolled in various course of B.Com

SQL> SELECT * FROM STUDENT;

SID SNAME DOB STATE GENDER CATEGORY COURSE

---------- -------------------- --------- -------------------- - -------------------- ---------- ---------- -----------------

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

1007 Ramya 13-JAN-02 Telangana F Gen Appl

1008 Rakesh 22-DEC-01 AndhraPradesh M Sports Comp

6 rows selected.

III. QUERIES ON STUDENT TABLE

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

SQL> SELECT * FROM STUDENT WHERE STATE NOT IN ('Telangana', 'AndhraPradesh');

SID SNAME DOB STATE GENDER CATEGORY COURSE

---------- -------------------- --------- -------------------- - -------------------- ---------------------------------

 1003 Payal 15-AUG-01 Maharashtra F Gen Appl

1004 Amrita 20-APR-02 Karnataka F OBC Honors

1006 Anchal 01-MAY-03 Gujarat F OBC Comp

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

SQL> CREATE VIEW STUDENT_TELANGANA AS SELECT SID,SNAME FROM STUDENT WHERE

STATE='Telangana';

View created.

SQL> SELECT * FROM STUDENT_TELANGANA;

SID SNAME

---------- -----------

1001 Neha

1002 Arun

1007 Ramya

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

SQL> CREATE INDEX IDX_SNAME ON STUDENT(SNAME);

Index created.

33. 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';

SID SNAME DOB STATE GENDER CATEGORY COURSE

---------- -------------------- --------- -------------------- - -------------------- -------------------------------------

1006 Anchal 01-MAY-03 Gujarat F OBC Comp

34. Write sql query to display the student ids, names, and their present age
 SQL> SELECT SID, SNAME, EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM DOB) AS
AGE FROM STUDENT;

SID SNAME AGE

---------- -------------------- ----------

1001 Neha 20

1002 Arun 20

1003 Payal 21

1004 Amrita 20

1005 Pavan 19

1006 Anchal 19

1007 Ramya 20

1008 Rakesh 21

8 rows selected.

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

SQL> SELECT SID, SNAME, DOB, STATE,GENDER, COURSE, CATEGORY FROM STUDENT
GROUP BY SID, SNAME, DOB, STATE, GENDER, COURSE, CATEGORY ORDER BY SNAME;

SID SNAME DOB STATE GENDER COURSE CATEGORY

---------- -------------------- --------- -------------------- - ---------- ----------------------------------------

1004 Amrita 20-APR-02 Karnataka F Honors OBC

1006 Anchal 01-MAY-03 Gujarat F Comp OBC

1002 Arun 10-JAN-02 Telangana M Honors OBC

1001 Neha 29-DEC-02 Telangana F Comp Gen

1005 Pavan 29-MAY-03 AndhraPradesh M Comp ExServicemen

1003 Payal 15-AUG-01 Maharashtra F Appl Gen

1008 Rakesh 22-DEC-01 AndhraPrades M Comp Sports

 1007 Ramya 13-JAN-02 Telangana F Appl Gen

9 rows selected.

36. 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 EXTRACT (YEAR FROM
DOB)>2002

2 rows deleted.

SQL> SELECT * FROM STUDENT;

SID SNAME DOB STATE GENDER CATEGORY COURSE

---------- -------------------- --------- -------------------- - -------------------- ----------

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

1007 Ramya 13-JAN-02 Telangana F Gen Appl

1008 Rakesh 22-DEC-01 AndhraPradesh M Sports Comp

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

SQL> ALTER TABLE STUDENT ADD CONTACTNO NUMBER(10);

Table altered.

SQL> ALTER TABLE STUDENT ADD EMAILID VARCHAR2(25);

Table altered.

38. Write an sql query to display all the Student names where the length of the name is 5
characters.

SQL> SELECT SNAME FROM STUDENT WHERE SNAME LIKE '_____';

SNAME
 --------------------

Payal

Ramya

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

CREATE TABLE LIBRARY(BOOKID CHAR(4) PRIMARY KEY, BOOKNAME VARCHAR2(50) NOT

NULL, AUTHOR VARCHAR2(20), DATEPURCHASED DATE, PUBLISHER VARCHAR2(20), PRICE
NUMBER(5,2));

Table created.

INSERT INTO LIBRARY VALUES

(‘&BOOKID’,’&BOOKNAME’,’&AUTHOR’,’&DATEPURCHASED’,’&PUBLISHER’,’&PRICE’);

SQL> SELECT * FROM LIBRARY;

BOOKID BOOKNAME AUTHOR DATEPURCH PUBLICATION PRICE

---- -------------------------------------------------- -------------------- --------- -------------------- ----------
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 02-MAR-15 TMH 720
B104 Mgmt Accounting RK Sharma 19-APR-16 Kalyani 450
B105 Operating Systems Galvin 25-NOV-13 PHI 570
B106 Advanced Accounting SC Gupta 16-APR-18 Himalaya 600

6 rows selected.
IV. QUERIES ON LIBRARY TABLE

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

SQL> SELECT AUTHOR FROM LIBRARY WHERE PUBLISHER='Himalaya';

AUTHOR

--------------------

OP Aggarwal

SC Gupta
40. Write sql query to display the total cost of books purchased Publisher wise

SQL> SELECT PUBLISHER, SUM(PRICE) AS TOTAL_PRICE FROM LIBRARY GROUP BY

PUBLISHER;

PUBLISHER TOTAL_PRICE

-------------------- -----------

Kalyani 1250

Himalaya 1350

TMH 900

PHI 750

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

SQL> SELECT COUNT(BOOKID) AS KALYANI_PUB_BOOKS FROM LIBRARY WHERE

PUBLISHER='Kalyani';

KALYANI_PUB_BOOKS

-----------------

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

SQL> ALTER TABLE LIBRARY RENAME COLUMN PUBLISHER TO PUBLICATION;

Table altered.

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

SQL> SELECT * FROM LIBRARY ORDER BY DATEPURCHASED;

BOOKID BOOKNAME AUTHOR DATEPURCH PUBLICATION PRICE

---- -------------------------------------------------- -------------------- --------- -------------------- ----------

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

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

 B105 Operating Systems Galvin 25-NOV-13 PHI 750

B103 Rdbms C J Date 02-MAR-15 TM 900

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

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

6 rows selected.

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

SQL> CREATE INDEX IDX_BOOK_AUTHOR ON LIBRARY(BOOKNAME, AUTHOR);

Index created.

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

SQL> SELECT * FROM LIBRARY WHERE PRICE BETWEEN 500 AND 700;

BOOKID BOOKNAME AUTHOR DATEPURCH PUBLICATION PRICE

---- -------------------------------------------------- -------------------- --------- -------------------- ----------

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

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

SQL> UPDATE LIBRARY SET PRICE = PRICE + 200 WHERE PUBLICATION NOT IN
('Kalyani','Himalaya');

2 rows updated.

BOOKID BOOKNAME AUTHOR DATEPURCH PUBLICATION PRICE

---- -------------------------------------------------- -------------------- --------- -------------------- ----------

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 02-MAR-15 TMH 920

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

B105 Operating Systems Galvin 25-NOV-13 PHI 770

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

6 rows selected.
47. Write sql query to display the book details where author name contains the name Sharma

SQL> SELECT * FROM LIBRARY WHERE AUTHOR LIKE '%Sharma%';

BOOKID BOOKNAME AUTHOR DATEPURCH PUBLICATION PRICE

---- -------------------------------------------------- -------------------- --------- -------------------- ----------

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

48. Create a view to display the fields BookId and BookName where the Publisher isHimalaya.

SQL> CREATE VIEW LIB_BOOK AS SELECT BOOKID, BOOKNAME FROM LIBRARY WHERE
PUBLICATION='Himalaya';

View created.

SQL> SELECT * FROM LIB_BOOK;

BOOKID BOOKNAME

---- --------------------------------------------------

B102 Business Statistics

B106 Advanced Accounting