Scribd
Upload
4 views2 pages

Database Systems Lab Exam Tasks

The document outlines the final lab exam for the Database Systems Lab course scheduled for June 12, 2025, with a total of 15 marks. It includes instructions for creating a .sql file and performing various tasks such as implementing a database from an ERD, writing queries with CASE statements, creating views, and developing stored procedures. Additionally, it lists other queries to be executed related to employee salaries and department information.

Uploaded by

ammarraheel19
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views2 pages

Database Systems Lab Exam Tasks

The document outlines the final lab exam for the Database Systems Lab course scheduled for June 12, 2025, with a total of 15 marks. It includes instructions for creating a .sql file and performing various tasks such as implementing a database from an ERD, writing queries with CASE statements, creating views, and developing stored procedures. Additionally, it lists other queries to be executed related to employee salaries and department information.

Uploaded by

ammarraheel19
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Final Lab Exam

Spring 2025
Course: Database Systems Lab Class: BS(SE) – 4B
Date: 12th June, 2025 Time: 1.5 hours
Instructor: Prem Sagar Marks: 15 Marks

Instructions:
1. Create a .sql file with your name and cmsid like this name_cmsid.sql
2. Use cmd/workbench to perform these tasks. Try to do all tasks one by one on same
cmd window if you are using cmd and same .sql file if you are using workbench.
3. Any case of cheating will be given zero marks.

Tasks

1. Implement the Database with the help of given ERD. [5 Marks]


2. Write the queries using CASE statement. [2 Marks]

a. Write a query to display the employee_id, first_name, department_id, job_id, and a n


new column salary_Grade using a CASE statement with the following logic:
- If department_id is 90 and salary > 20000 THEN 'Executive Grade'
- If job_id starts with 'IT' and salary between 6000 and 10000 THEN 'Tech
Grade'
- If salary < 3000 THEN 'Entry Grade'
- Else THEN 'Standard Grade'

b. Bonus Eligibility based on Department and Years of Service:


Write a query to display employee_id, hire_date, department_id, and a new column
Bonus_Eligibility based on this logic:
- If department_id in (50, 80) and the employee has worked more than 10
years THEN 'Gold'
- If hired in the last 5 years and salary > 7000 THEN 'Silver'
- Else THEN 'Not Eligible'

3. Create the following Views [2 Marks]


a. Create a view that shows employees whose salary is greater than the average salary
of their department.
Display: employee_id, employee_name, salary, department_name.
b. Create a view that shows job_title where the average employee tenure (in years) is
more than 7 years.
Display: job_title, average_tenure, total_employees

4. Create the following Stored Procedures [3 Marks]

a. Create a stored procedure named get_department_total_salary that:


- Takes a department ID as input
- Returns the total salary of all employees in that department
b. Create a stored procedure named get_department_salary_report that:
- Takes a department_id and a min_avg_salary as input.
- Shows the job titles in that department where the average salary is higher than
the given minimum.
- Also displays the total salary, number of employees, and average salary for each
job title.

5. Other Queries [3 Marks]


- Find first_name, salary and department_name of employees who earn more
than the average salary of their department.
- Find the number of employees hired per year.
- Find dept_id and department_name from departments where all employees
earn less than 5000.
- Find the most recently hired employee in each department.
- For each job title, find the average salary and list job titles with an average
salary above the overall average salary.
- Find the first_name, last_name, salary, deparment_name from employees who
work in the same department as the employee named 'Steven'

You might also like