Scribd
Upload
2 views2 pages

Lab02A - SQL Aggregation

The document outlines the objectives and requirements for Lab 02A in a Database Systems course, focusing on SQL aggregations. Students are expected to learn and apply SQL commands such as GROUP BY, ORDER BY, and HAVING, along with aggregate functions like SUM, AVG, and COUNT. A series of SQL queries are provided for students to execute, covering various employee and department statistics within a given HR database schema.

Uploaded by

liloxi3969
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
2 views2 pages

Lab02A - SQL Aggregation

The document outlines the objectives and requirements for Lab 02A in a Database Systems course, focusing on SQL aggregations. Students are expected to learn and apply SQL commands such as GROUP BY, ORDER BY, and HAVING, along with aggregate functions like SUM, AVG, and COUNT. A series of SQL queries are provided for students to execute, covering various employee and department statistics within a given HR database schema.

Uploaded by

liloxi3969
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

Database Systems Lab

Lab 02A: SQL Aggregations

Objective
The students should be able to:
1. Understand the use of GROUP BY and ORDER BY clause
2. Understand the use of HAVING clause
3. Use Aggregate functions like SUM, AVG, MIN, MAX, COUNT, DISTINCT

Submission Requirements
Save your script file and upload it to LMS.

HR Database Schema

Fall 2023 page. 1 Ms. Abeera Tariq


Database Systems Lab

SQL Queries

Write and Execute SQL queries for the following information needs. Make use of suitable
column aliases for the new aggregated columns.
1. How many employees are working in department with ID = 50 ?
2. What is the total number of employees belonging to department 80 and have salary greater
than 24000
3. How many employees are receiving a commission?
4. What is average, minimum and maximum salary of employees having JOB_ID ending with
CLERK? Use suitable alias for each new column.
5. How many distinct departments are there in employees table?
6. How many distinct job_ids are there in employees table?
7. Compute average COMMISSION_PCT rounded up to two decimal places
8. Compute average COMMISSION_PCT ignoring NULL values rounded up to two decimal places
9. Compute average COMISSON_PCT considering NULL values truncated upto two decimal
places.
10. Compute AVG_SALARY for each department in employees table rounded up to two decimal
places
11. Compute AVG_SALARY for each JOB_ID in employees table
12. Compute department-wise total salary of all employees sorted by department_id
grouped by job_id
13. What is the minimum average salary paid to a department?
14. Show the top 5 job_titles based on max_salary. Hint: fetch
15. Make corrections in the query given below:

16. Make corrections in the query given below:

17. Display DEPARTMENT_ID, JOB_ID and department-wise job-wise SALARY_TOTAL of all


employees HAVING SALARY_TOTAL > 10,000 sorted by DEPARTMENT_ID
18. What is average job duration in months for each DEPARTMENT_ID (hint: end_date and
start_date is given)
19. What is the maximum job duration in months for each JOB_ID
20. Display the number of locations for each COUNTRY_ID

Fall 2023 page. 2 Ms. Abeera Tariq

You might also like