SQL Queries

Simple Queries:

INSERT INTO location (Location_ID, Regional_Group) VALUES

(1, 'North America'),

(2, 'Europe'),

(3, 'Asia'),

(4, 'South America'),

(5, 'Africa'),

(6, 'Australia'),

(7, 'Antarctica');

INSERT INTO LOCATION (Location_ID, Regional_Group) VALUES

(122, 'NEW YORK'),

(123, 'DALLAS'),

(124, 'CHICAGO'),

(167, 'BOSTON');

INSERT INTO DEPARTMENT (Department_ID, Name, Location_ID) VALUES

(10, 'ACCOUNTING', 122),

(20, 'RESEARCH', 124),

(30, 'SALES', 123),

(40, 'OPERATIONS', 167);

INSERT INTO DEPARTMENT (DEPARTMENT_id, NAME, location_id) VALUES

(1, 'Human Resources', 1),

(2, 'Finance', 2),

(3, 'Marketing', 3),

(4, 'Operations', 4),

(5, 'IT', 5),

(6, 'Legal', 6),

(7, 'Sales', 3),

(8, 'Customer Support', 2),

(9, 'Research & Development', 4),

(10, 'Procurement', 1);

INSERT INTO JOB (Job_ID, Function) VALUES

(667, 'CLERK'),

(668, 'STAFF'),

(669, 'ANALYST'),

(670, 'SALESPERSON'),

(671, 'MANAGER'),

(672, 'PRESIDENT');

INSERT INTO JOB (Job_ID, Function) VALUES

(667, 'CLERK'),

(668, 'STAFF'),

(669, 'ANALYST'),

(670, 'SALESPERSON'),

(671, 'MANAGER'),

(672, 'PRESIDENT');

INSERT INTO JOB (job_id, FUNCTIONS) VALUES

(1, 'Software Developer'),

(2, 'Data Analyst'),

(3, 'Product Manager'),

(4, 'Human Resources Manager'),

(5, 'Financial Analyst'),

(6, 'Marketing Specialist'),

(7, 'Customer Support Representative'),

(8, 'Quality Assurance Engineer'),

(9, 'Sales Executive'),

(10, 'Network Administrator');

CREATE TABLE Employee (

    EMPLOYEE_ID INT PRIMARY KEY,

    LAST_NAME VARCHAR(50),

    FIRST_NAME VARCHAR(50),

    MIDDLE_NAME CHAR(1),

    JOB_ID INT,

    MANAGER_ID INT,

    HIRE_DATE DATE,

    SALARY DECIMAL(10, 2),

    COMM DECIMAL(10, 2),

    DEPARTMENT_ID INT

);

INSERT INTO Employee (EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMM, DEPARTMENT_ID)

VALUES

(7369, 'SMITH', 'JOHN', 'Q', 667, 7902, '1984-12-17', 800.00, NULL, 20),

(7499, 'ALLEN', 'KEVIN', 'J', 670, 7698, '1985-02-20', 1600.00, 300.00, 30),

(7505, 'DOYLE', 'JEAN', 'K', 671, 7839, '1985-04-04', 2850.00, NULL, 30),

(7506, 'DENNIS', 'LYNN', 'S', 671, 7839, '1985-05-15', 2750.00, NULL, 30);

1. List all the employee details.

   SELECT * FROM employees;
   

2. List all the department details.

   SELECT * FROM departments;
   

3. List all job details.

   SELECT * FROM jobs;
   

4. List all the locations.

   SELECT * FROM locations;
   

5. List out the first name, last name, salary, and commission for all employees.

   SELECT first_name, last_name, salary, commission FROM employees;
   

6. List out employee_id, last_name, and department_id for all employees, and rename:

   • employee_id as "ID of the employee"
   • last_name as "Name of the employee"
   • department_id as "Department ID"

   SELECT 
       employee_id AS "ID of the employee", 
       last_name AS "Name of the employee", 
       department_id AS "Department ID" 
   FROM employees;
   

7. List out the employees' annual salary with their names only.

   SELECT first_name, last_name, (salary * 12) AS annual_salary FROM employees;
   

---

Where Conditions:

8. List the details about "SMITH".

   SELECT * FROM employees WHERE last_name = 'SMITH';
   

9. List out the employees who are working in department 20.

   SELECT * FROM employees WHERE department_id = 20;
   

10. Find out the employees who are not working in department 10 or 30.

    SELECT * FROM employees WHERE department_id NOT IN (10, 30);
    

11. List out the employees whose name starts with "S".

    SELECT * FROM employees WHERE first_name LIKE 'S%';
    

12. List out the employees whose names start with "S" and end with "H".

    SELECT * FROM employees WHERE first_name LIKE 'S%H';
    

13. List out the employees whose name length is 4 and starts with "S".

    SELECT * FROM employees WHERE LENGTH(first_name) = 4 AND first_name LIKE 'S%';
    

14. List out the employees who are working in department 10 and draw salaries more than 3500.

    SELECT * FROM employees WHERE department_id = 10 AND salary > 3500;
    

15. List out the employees who are not receiving commissions.

    SELECT * FROM employees WHERE commission IS NULL;
    

---

Order By Clause:

16. List out the employee ID and last name in ascending order based on the employee ID.

    SELECT employee_id, last_name FROM employees ORDER BY employee_id ASC;
    

17. List out the employee ID and name in descending order based on the salary column.

    SELECT employee_id, first_name, last_name FROM employees ORDER BY salary DESC;
    

18. List out the employee details according to their last name in ascending order and salaries in descending order.

    SELECT * FROM employees ORDER BY last_name ASC, salary DESC;
    

19. List out the employee details according to their last name in ascending order and then on department ID in descending order.

    SELECT * FROM employees ORDER BY last_name ASC, department_id DESC;
    

---

Group By & Having Clause:

20. How many employees are working in different departments in the organization?

    SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;
    

Department-Wise Queries:

1. List out the department-wise maximum salary, minimum salary, and average salary of the employees.

   SELECT 
       department_id, 
       MAX(salary) AS max_salary, 
       MIN(salary) AS min_salary, 
       AVG(salary) AS avg_salary 
   FROM employees 
   GROUP BY department_id;
   

2. List out the job-wise maximum salary, minimum salary, and average salary of the employees.

   SELECT 
       job_id, 
       MAX(salary) AS max_salary, 
       MIN(salary) AS min_salary, 
       AVG(salary) AS avg_salary 
   FROM employees 
   GROUP BY job_id;
   

3. List out the number of employees joined in every month in ascending order.

   SELECT 
       MONTH(hire_date) AS month, 
       COUNT(*) AS employee_count 
   FROM employees 
   GROUP BY MONTH(hire_date) 
   ORDER BY month ASC;
   

4. List out the number of employees joined each month and year in ascending order based on year and month.

   SELECT 
       YEAR(hire_date) AS year, 
       MONTH(hire_date) AS month, 
       COUNT(*) AS employee_count 
   FROM employees 
   GROUP BY YEAR(hire_date), MONTH(hire_date) 
   ORDER BY year ASC, month ASC;
   

5. List out the department IDs having at least four employees.

   SELECT 
       department_id 
   FROM employees 
   GROUP BY department_id 
   HAVING COUNT(*) >= 4;
   

6. How many employees joined in January?

   SELECT COUNT(*) AS employee_count 
   FROM employees 
   WHERE MONTH(hire_date) = 1;
   

7. How many employees joined in January or September?

   SELECT COUNT(*) AS employee_count 
   FROM employees 
   WHERE MONTH(hire_date) IN (1, 9);
   

8. How many employees joined in 1985?

   SELECT COUNT(*) AS employee_count 
   FROM employees 
   WHERE YEAR(hire_date) = 1985;
   

9. How many employees joined in March 1985?

   SELECT COUNT(*) AS employee_count 
   FROM employees 
   WHERE YEAR(hire_date) = 1985 AND MONTH(hire_date) = 3;
   

10. Which is the department ID having three or more employees who joined in April 1985?

    SELECT department_id 
    FROM employees 
    WHERE YEAR(hire_date) = 1985 AND MONTH(hire_date) = 4 
    GROUP BY department_id 
    HAVING COUNT(*) >= 3;
    

---

Subqueries:

11. List out the employees who earn more than every employee in a specific department.

    SELECT * 
    FROM employees 
    WHERE salary > ALL (
        SELECT salary 
        FROM employees 
        WHERE department_id = <specific_department_id>
    );
    

12. List out the employees who earn more than the lowest salary in a specific department.

    SELECT * 
    FROM employees 
    WHERE salary > (
        SELECT MIN(salary) 
        FROM employees 
        WHERE department_id = <specific_department_id>
    );
    

13. Find out whose department has no employees.

    SELECT department_id 
    FROM departments 
    WHERE department_id NOT IN (
        SELECT DISTINCT department_id 
        FROM employees
    );
    

14. Find out which department does not have any employees.

    SELECT * 
    FROM departments 
    WHERE department_id NOT IN (
        SELECT DISTINCT department_id 
        FROM employees
    );
    

---

Joins:

15. List employees with their department names.

    SELECT e.employee_id, e.first_name, d.department_name 
    FROM employees e 
    JOIN departments d 
    ON e.department_id = d.department_id;
    

16. Display employees with their designations (jobs).

    SELECT e.employee_id, e.first_name, j.job_title 
    FROM employees e 
    JOIN jobs j 
    ON e.job_id = j.job_id;
    

---