Computer Lab - Practical Questlon Bank

FACULTY OF COMMERCE, OSMANIA UNIVERSITY

B.Com Computer Applications - Semester (New)} w.e.f. 2019-20

RELATIONAL DATABASE MANAGEMENT SYSTEM PracticalQuestion Bank
Paper No.DSC 303
Time: 60 Minutes Record : 10
Viva-Voce: 10
Skill Test: 15

Total Marks:35

A.Create a Supplier table as shown below : (for questions from 1to 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

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

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

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

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

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

6. Write sqi query toadd a new column called CONTACTNO.

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

8. Create a view on the table which displays ornly supplier numbers and
supplier names.
9. Write sqlquery to display the records in the
descending order of itemprice for each itemsupolied.
10. Write sai query to display the records of suppliers who
supply items other than Processor or
Keyboard.
Company. (For questions from 11toz0)
B. Beiow are the details of Employees working for asoftware
Create the table called EmpDetails with the belowmentioned 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

Tester 35000 13-Jun-11

E103 Payal 15-Aug-85
E104 Kiran 20-Apr-90 Programmer 40000 7-Mar- 14
Meenal 29-May-83 DBA 50000 9-Dec-11
E105
Sheila 1-May-70 Analyst 60000 25-Sep-18
E106
Programmer 45000 14-Feb-16
E107 Swamy 13-Jan-85

Sushma 22-DeC-76 DBA 45000 31-Jan-12

E108

is Programmer.
11. Write sqiquery to display all the employees whose designation
joined after 2014.
12. Write sql query todispiay employees who have
name ends with 'a'.
13. Write sql query to display allthe employees whose
employees whose designation is
14. Write sql query to display the total salary of all the
programmer.

case.
15. Write sql query to displayall the employee names in upper
experience.
16. Write sql query todisplay the details of the employee with highest
name contains 'ee'.
17. Write sql query to display the details of the empioyees whose
whose designation is DBA.
18. Write sql query toincrease the salaries of empioyees by 5000
average salary of all the
19. Write sql query to display the empioyees whose salary is more than the
employees.

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

C Create the two tables as shown below with the given constrants: (for questions 21 to 30)
Table name: Employee Tablename:Department

Constraints: Eid is Primary key and Deptid is foreign key Constraints:Deptld Primary key

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

Eid Designation Salary DOJ Deptld Dname

Ename Deptld
(Primary Key) (Foreign Key) (> 10000) (Primary Key)
101 Sudha D2 Clerk 20000 01-Apr-10 D1 Sales

102 David D1 Manager 50000 18-Feb-18 D2 Marketing

Clerk 35000 13-Jun-11 D3 Finance

103 Preethi D3
20000 7-Mar-14
104 Kiran D1 Salesman

Meenal D2 Clerk S0000 9-Dec-11

105

Sunitha D3 Manager 60000 25-Sep-18

106
25000 14-Feb-16
107 Akhil D3 Clerk

D2 Manager 45000. 31-Jan-12

108 Sushma

earn more than average salary of allthe

21. Writesql query todisplay all the employees who
employees in the company.
Ename and Dname.
22.Write sqi query to display the fields Eid,
table in the descending order of salaries.
23. Write sqlquery to sort the employee
the employee table without repetitions.
24. Write sqlquery to list all the job designations in
Write sql query to display all the employee details Department wise and in the ascending order
25.
of their salaries.

clerks in Deptid D2.

26. Write sql query to display all the
employees who joined in the year 2011.
27. Writesql query to display allthe
who joined in the month of February.
28. Write sqlquery to display all the employees
Write sql query to display allthe employees whose salary is between 30000 and 45000.
29.
details along with their work experience in the
30. Write sqlquery to display all the employee
company till current date.
D. Below are the details of Students enrolled in various course of B.Com (For questions Trom 32
to 40)
Create the table called Student with the below mentioned detals.
Sid SnameDOB State GenderCategory Course

(Primary Key)
1001 Neha 29-Dec-02 Telangana Gen Comp
1002 Arun 10-Jan-02 Telangana M OBC Honors

1003 Payal 15-Aug-01 Maharashtra Gen Appl

Karnataka OBC Honors
1004 Amrita 20-Apr-02
1005 Pavan 29-May-03 AndhraPradesh M ExServicemen Comp
1006 Anchal 1-May-03 Gujarat OBC Comp
1007 Ramya 13-Jan-02 Telangana Gen Appl
1008 Rakesh22-Dec-01 AndhraPradesh M Sports Comp

31. Write sqlquery todisplay the students who are not from Telangana or AndhraPradesh.

32.Create aview to display the columns Sid, Sname for students belonging to Telangana.
33. Write sql query to create an index on column Sname.
34. Write sql query to display all the female students enrolled under Comp course and who belong
to OBC.

35. Write sqlquery to display the student ids, names, and their present age.
36.Write sqiquery to display the students in the ascending order of their names for each course.
37. Write sql query todelete all the students records who have enrolled for Comp course and who
are born after 2002.

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

39. Writs an sqlquery to display allthe Student names prefixed with Mr./Ms. Based on Gender
column.

40. Write an sqlquery to display all the Student names where the length of the name is 5 characters.
E. Create a Table for Library Information: (for questions from 41 to 50)

Table name:Library

Constraints: Bookld is primary key and BookName is NOT NULL

Author DatePurchased Publisher Price
Booktd BookName

(Primary Key)
11-Feb-13 Kalyani 800
B101 Cost Accounting Jain Narang
OP Aggarwal 22-Dec-11 Himalaya 750
8102 Business Statistics
2-Mar-15 TMH 900
Rdbms C Date
B103
Kalyani 450
Mgmt Accounting RK Sharma 19-Apr-16
B104
25-Nov-13 PHi 750
Operating Systems Galvin
B105
16-Apr-18 Himalaya 600
B106 Advanced Accounting SC Gupta

Himalaya publications.
41. Write sqiquery to display the list of authors from
of books purchased Publisher wise.
42. Write sql query todisplay the total cost
number of books under Kalyani publications.
43. Write sqiquery to count the total

column Publisher as Publications.

44. Write sql query to rename the

in the ascending order of DatePurchased.

45. Write a sql query to display the books
on the fields BookName and Author.
46. Write sqlquery to create an index

whose price is between 500 and 700

47. Write sqlquery todisplay the books

query to increase the price of all the books by 200 for publishers other than Himalaya or
48. Write sql
Kalyani.
author name contains the name Sharma.
49. Write sqlquery to display the book details where
where the Publisher is Himalaya.
50. Create a view to display the fields Bookid and BookName