Scribd
Upload
19 views2 pages

SQL Queries for Employee and Sales Data

The document describes 10 SQL queries involving 6 database tables: Employees, Payment, Salesman, Customer, Orders, and Department. The queries involve finding employees who received bonuses, salespeople in the same city as customers, departments with high-paid employees, employee salaries and averages, salespeople with multiple customer orders, salespeople with a single customer, salespeople alphabetically after customers, customers served by salespeople with commissions in a range, customers with grades served by salespeople from a city, and order amounts by date above a threshold.

Uploaded by

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

SQL Queries for Employee and Sales Data

The document describes 10 SQL queries involving 6 database tables: Employees, Payment, Salesman, Customer, Orders, and Department. The queries involve finding employees who received bonuses, salespeople in the same city as customers, departments with high-paid employees, employee salaries and averages, salespeople with multiple customer orders, salespeople with a single customer, salespeople alphabetically after customers, customers served by salespeople with commissions in a range, customers with grades served by salespeople from a city, and order amounts by date above a threshold.

Uploaded by

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

DBMS QUERIES

Tables :
 Employees (emp_id, emp_name, job_name, hire_date, salary,
commission, dep_id)
 Payment (emp_id, pay_type,amount_paid, pay_date)
 Salesman (s_id, s_name, s_city, s_commission)
 Customer (c_id, c_name, c_city, grade, s_id)
 Orders (o_no, purch_amt, o_date, c_id, s_id)
 Department (dep_id, dep_name, dep_location)

Query 1. List the employees who have received a bonus. Tables : Employees,


payment.

Query 2. Write a SQL query to find the salespeople whose place of living (city)
matches with any of the city where customers live. Return s_id, s_name, s_city and
s_commission. Tables : Salesman, Customer.

Query 3. Finds all departments which have at least one employee with the
salary is greater than 5000. Tables : Department , Employees.

Query 4. Finds the salaries of all employees, their average salary, and the
difference between the salary of each employee and the average salary. Table :
Employees.

Query 5. Write a SQL query to find the salespeople who deal the customers
with more than one order. Return s_id, s_name, s_city and
s_commission.Tables : Salesman, Customer , Orders.

Query 6. From the following tables write a SQL query to find the salespeople
who deal a single customer. Return s_id, s_name, s_city and s_commission.
Tables : Salesman, Customer.

Query 7. From the following tables, write a SQL query to find all those
salespeople whose name exist alphabetically after the customer’s name. Return
s_id, s_name, city, commission. Tables : Salesman, Customer.
Query 8. write a SQL query to find those customers who served by a
salesperson and the salesperson works at the commission in the range 12% to
14% (Begin and end values are included.). Return c_name AS "Customer",
c_city AS "City". Tables : Salesman, Customer.

Query 9. From the following tables, write a SQL query to find those customers
where each customer has a grade and served by at least a salesperson who
belongs to a city. Return c_name as "Customer", grade as "Grade". Tables :
Salesman, Customer,Orders.

Query 10. Write a query to find the sums of the amounts from the orders table,
grouped by date, eliminating all those dates where the sum was not at least
1000.00 above the maximum order amount for that date. Tables : Customer ,
Orders.

You might also like