Database Management Practical exam Questions

SQL Queries

Q.1 Create table employee with attributes emp_id, emp_name, date_of_joining, emp_salary,
emp_dept and insert 5 rows in it and also write SQL queries for the following :
(i)Display the list of employee excluding job title as ‘Salesman’.
(ii)Display the average salaries for each department.
(iii)Change the name of employee ‘Rahul Gosai’ to ‘Jigar Dave’,
(iv)Display employee details whose name start with ‘A’
(v) Display minimum salary of each department.

Q.2.Create table emp with attributes emp_no, ename, job, mgr, joindate, salary, comm.,
dept_no and insert 5 rows in it and also write SQL queries for the following:
i)Find the employees who belong to deptno 10 or 30.
ii) Find out number of employees who work as manager.
iii) Change the salary of employee “Prashant Sali” to 40000
iv)List all employees with null values for comm.(i.e. commission)
v). Display details of employees in descending order of their salary.

Q.3. Create table for following schema with suitable integrity constraints:
Student (Rollno, Name, Mark, Age, Place, Phoneno, dob) and insert 5
rows in it and also write SQL queries for the following
i) To list name of student who do not have a phone number.
ii) To list students from Nashik and Pune
iii) To change mark of Monika to 88 instead of 80.
iv) To list the students from Amit’s Age group.
v) To display dob in format June 4,2018

Q.4. Create table emp with attributes empno, empname, job, mgr, joindate, salary, comm.,
dept_no and insert 5 rows in it and also write SQL queries for the following :
i)To display annual salary of all employees.
ii) To display name and salary for all employees whose salary is not in rage of Rs. 5000
and Rs. 10000.
(iii) Change the salary to 50000 for only those employees whose job is manager and
dept_no is 10
(iv) Display employee details whose name ends with ‘sh’ and name contains maximum
of 6 characters
(v) display details of employees whose salary is greater than minimum salary of
each department
Q.5. Create table emp with attributes emp_id, emp_name, emp_city, emp_addr, emp_dept,
join_date and insert 5 rows in it and also write SQL queries for the following
i) Display the names of employees in capital letters.
ii) Display the emp_id of employee who live in city Pune and Mumbai
iii) Display the details of employees whose joining date is after ’01-Apr-2017’.
iv) Display the total number of employees whose dept no. is ‘20’.
v) Display department numbers having maximum salary greater than 30000

Q.6 Create table Employee with attributes EMP_ID, FIRST_NAME, LAST_NAME, SALARY,
JOINING_DATE, DEPARTMENT and insert 5 rows in it and also write SQL queries for
the following
i) Get FIRST_NAME, LAST_NAME from employee table.
ii) Get unique DEPARTMENT from employee table.
iii) Get FIRST_NAME form employee table using alias name “Employee Name”
iv) Get FIRST_NAME from employee table after removing white spaces from left side.

Q.7 Create tables for following schema:

depositor (cust_name, acc_no)
borrower (cust_name, loan_no)
and insert 5 rows in it and also write SQL queries for the following
(i) Find customer name having savings account as well as loan account.
(ii) Find customer names having loan account but not the saving account.
(iii) Find customer name having savings account or loan account or both.

Q.8 Create table for following schema with suitable integrity constraints:
Employee (emp_no,emp_name,dept,designation,salary,Dept_location)
and insert 5 rows in it and also write SQL queries for the following
(i) List all Managers in Mumbai location.
(ii) Set salary of all ‘project leaders’ to 70000/-.
(iii) List employees with having alphabet ‘A’ as second letter in their name.
(iv) Display details of those employees who work in Mumbai or Chennai.
(v)Display employees details whose name does not contain pattern ‘ra’

Q.9 Create table for following schema with suitable integrity constraints:
Student (rollno, name, class, DOB,percentage, mobileno)
and insert 5 rows in it and also write SQL queries for the following
i. To add new column percentage to student table
ii. To drop column DOB from student table
iii. To rename column name to student_name.
iv.Display details of students whose percentage is greater than 60
Q.10 Create table for following schema with suitable integrity constraints:
Employee (E_id, E_name, Salary, Department_name)
and insert 5 rows in it and also write SQL queries for the following
i. Find Summation of salary for all employees.
ii. Display Minimum and Maximum Salary.
iii. Display all record in descending order of Employee name.
iv. Display employee name working in department “Quality”

Q.11 Create tables for following schema with suitable integrity constraints:
Product (P_id, Name, Quantity, Price);
Supplier(S_id, S_name, P_id, Contact_no)
Manufacturer(M_id, M_name, Owner, City, P_id,)
and insert 5 rows in it and also write SQL queries for the following
i. Increase Price of all products by 20% having product id P123.
ii. . Change contact_no for S123 supplier with new number as “9876543210”.
iii. Display records of products supplied by supplier S125.
iv. Eliminate Supplier with Supplier id S126.
v. Display all manufacturers whose name starts with ‘A’.

Q.12 Create tables for following schema with suitable integrity constraints:
Emp(E_id, E_Name, D_id, Manager)
Department (D_id, D_name, Location)
and insert 5 rows in it and also write SQL queries for the following
i. Perform Inner join on above table.
ii. Perform Left Join on Department and Emp Table.
iii. Perform Cross Join on Department and Emp Table.
iv. Perform Natural Join on Department and Emp Table.
v.Perform Non equi Join on Department and Emp Table.

Q.13. Create table for following schema with suitable integrity constraints:
Candidate (C_id, Seat_no, Name, Score, City)
and insert 5 rows in it and also write SQL queries for the following
i. Display Candidate who scored between 60 and 70 Marks.
ii. Display Candidate whose city is either Pune or Mumbai.
iii. Produce record of candidate who belongs to Nashik and scored more than 60 marks.
iv. Change the city of candidate C_10 from Pune to Mumbai.
v. Display Candidate whose score is greater than score of Amit
Advanced SQL Queries

Q.14 . Create table for following schema with suitable integrity constraints:
Employee (emp_id, emp_name, job, hiredate, salary, comm, deptno)
Department(deptno,dname,location)
Write SQL commands for following statements:
i. Create view emp_vu20 that contains details of employees in deptno 20
ii. Display employee details from view emp_vu20 whose salary is greater than 50,000
iii. Delete view named as emp_vu20
iv. Create view emp_dept that contains employee name,job,salary and their department name for only
those employee whose job is manager

Q.15 Write SQL Commands to perform following operations on Sequence.

i. Create a sequence with following specifications.
• Name : DB_SEQ
• Starting Value : 1
• Maximum Value : 10
• Increment By: 2
ii.Insert records into any table using sequence DB_SEQ
iii. Modify a sequence named DB_SEQ with following specifications.
• Increment By: 3
• Maximum Value : 20
v. Remove a sequence DB_SEQ from database

Q16. Write SQL Statement to perform following operation.

i. Create Synonym for Hotel table to HM.
ii. Create an Index on Hotel Table, use H_id as index attribute.

Q17 Create table for following schema with suitable integrity constraints:
Student (enrollment_no ,roll_no, name, class, DOB,percentage, mobileno)
Write SQL Commands to perform following operations on View.
i. Create a view to fetch enrollment number, name, and percentage from student table and
name it as S_View.
ii. Display record from S_View where percentage is greater than 60.
iii. Delete view named as S_view.
PL/SQL PROGRAMMING

1. Write PL/SQL program to display square of any number.

2. Write PL/SQL program to perform addition of two numbers.
3. Write PL/SQL program to find area of circle
4. Write PL/SQL code to check whether entered number is even or odd.
5. Write a PL/SQL program to accept 3 numbers and display largest number.
6. Display numbers 1 to 10 in reverse order using for loop
7. Write PL/SQL program to find factorial of user defined number.
8. Write PL/SQL program which handles divide by zero exception.
9. Write PL/SQL program which handles case_not_found exception.
10. Write PL/SQL program which handles no_data_found exception.
11. Declare the cursor,that selects all records from emp table, after that inserts these records into
emp2 table (create new exmp2 table).
12. With the help of cursor display department number and names from dept table.
13. Create a trigger to store backup entry in backup_emp table, of every employee when the record of
his/her is deleted from the emp table.
14. Create trigger trg1 that convert enames from lowercase to uppercase before inserting records into
emp table.
15. Write PL/SQL code for following
i. Write a function circle_area to find area of circle, use radius as input parameter
ii. Write a procedure to count number of employee in department, use dept_no as input parameter

16. Write PL/SQL procedure / Function for following statements.

i. Create a procedure to greet a user with his name.
ii. Write Function to perform square of user defined number

17. Write SQL Commands for following statements.

i. Create user Jack having password as ‘Jack123’.

ii. Assign insert and update privileges to user Jack on student table.
iii. Remove update privileges from ‘Jack’.
iv. Assign the SELECT privilege on BOOK table to all users
v. Revoke the SELECT privileges on BOOK table from all users.
vi. Assign ALL privilege on BOOK table to all users
vii. Remove ALL privilege on BOOK table from all users
viii. Create user Rajan having password as ‘Rajan1234’.
ix. Give the system privileges for creating tables and views to ‘Rajan’
x. Revoke the system privilege for creating table from ' Rajan '.