Notes

Q1. From the following table return complete information about the employees.

emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id

Q2. From the following table, write a SQL query to find the salaries of all employees. Return salary.

Q3. From the following table, write a SQL query to find the unique designations of the employees. Return job name.

Q4. From the following table, write a SQL query to list the employees’ name, increased their salary by 15%, and expressed as number of Dollars.

Q5. From the following table, write a SQL query to find those employees with hire date in the format like February 22, 1991. Return employee ID, employee name, salary, hire date.

Q6. From the following table, write a SQL query to calculate the average salary of employees who work as analysts. Return average salary.

Q7. From the following table, write a SQL query to find those employees whose designation is ‘CLERK’. Return complete information about the employees

Q8. From the following table, write a SQL query to count the number of customers. Return number of customers.

Sample table: customer

customer_id | cust_name | city | grade | salesman_id

Q9. From the following tables write a SQL query to find the salesperson and customer who reside in the same city. Return Salesman, cust_name and city.

Q10. From the following tables write a SQL query to find the salesperson and customer who reside in the same city. Return Salesman, cust_name and city.

Sample table: salesman

salesman_id | name | city | commission

Sample table: customer

Customer_id | cust_name | city | grade | salesman_id

Q11. From the following tables write a SQL query to find those orders where the order amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.

Sample table: customer

customer_id | cust_name | city | grade | salesman_id

Sample table: orders

ord_no purch_amt ord_date customer_id salesman_id

Q12. Sample Database: Employees Table
EmpID

Name

Department

Salary

JoiningDate

1

John Smith

IT

60000

2019-01-15

2

Alice Brown

HR

55000

2020-03-10

3

David Lee

IT

75000

2018-07-20

4

Emma Davis

Finance

50000

2021-01-25

5

Mike Taylor

IT

80000

2017-11-30

6

Sara Wilson

HR

62000

2020-06-18



. Basic Aggregate Functions

1. Find the total salary of all employees.
2. Find the average salary of all employees








3 Find the highest salary in the company.
4 Find the lowest salary in the company.
5. Count the total number of employees.
Aggregates with GROUP BY
3. Find the total salary for each department.
4. Find the average salary per department.
5. Find the highest salary in each department.
6. Find the lowest salary in each department.
7. Count the number of employees in each department.
Aggregates with WHERE and HAVING

11. Find the total salary of IT department employees.
12. Find the average salary of employees who joined after 2020.
13. List departments having more than 1 employee.
14. Show the average salary of departments where the average salary is more than 60,000.
15. Find the sum of salaries for HR department employees earning more than 55,000.