Scribd
Upload
7 views4 pages

SQL Queries for Employee and Sales Data

The document outlines Lab #5 for a Database Management Systems course, focusing on SQL queries involving sorting commands and set operators. It includes tasks to retrieve employee and salesperson data based on specific criteria, such as name characters, salary, and order amounts. Each task requires writing SQL queries and returning specified fields in a sorted manner.

Uploaded by

Abdul Haseeb
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
7 views4 pages

SQL Queries for Employee and Sales Data

The document outlines Lab #5 for a Database Management Systems course, focusing on SQL queries involving sorting commands and set operators. It includes tasks to retrieve employee and salesperson data based on specific criteria, such as name characters, salary, and order amounts. Each task requires writing SQL queries and returning specified fields in a sorted manner.

Uploaded by

Abdul Haseeb
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

CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095

“Lab # 5”
Object: Apply Sorting commands and Set Operators to retrieve data from tables
in SQL
Lab Tasks
Employees (employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
1. From the following schema, write a SQL query to find those employees whose
first name contains the letters R, A, or N. Sort the result-set in ascending order
by salary. Return all fields.

Query:

Result:

1
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095

2. From the following schema, write a SQL query to find those employees who
earn above 21000 or the fifth character in their phone number is 8.. Sort the
result-set in ascending order by last name. Return full name (first name and
last name), hire date, commission percentage, email, and telephone separated
by '-', and salary.
Query:

Result:

2
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095

Orders(ord_no, purch_amt, ord_date, customer_id, salesman_id) Salesman


(salesman_id, name, city, commission), Customer (customer_id, cust_name, city,
grade, salesman_id)

3. From the following table, write a SQL query to find those salespersons
generated the largest and smallest orders on each date. Return salesperson ID,
name, order no., highest on/ lowest on, order date.
Query:

Result:

3
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095

4. From the following tables, write a SQL query to find those salespersons who
have same cities where customer lives as well as do not have customers in
their cities and indicate it by ‘NO MATCH’. Sort the result set on 2nd column
(i.e. name) in descending order. Return salesperson ID, name, customer name,
commission.
Query:

Result:

You might also like