Module-7-Assignment.sql

-- Use the employees table to answer the following questions:

a. SELECT * FROM employees;
-- This command selects all columns and rows from the employees table.

b. SELECT name, salary FROM employees WHERE salary > 50000;
-- This command selects the name and salary columns of all employees with a salary greater than 50000.

c. SELECT AVG(salary) FROM employees;
-- This command calculates the average salary of all employees.

d. SELECT COUNT(*) FROM employees JOIN departments ON employees.department_id = departments.id WHERE departments.name = 'Marketing';
-- This command joins the employees table with the departments table on the department_id and id columns, respectively, and then selects all employees with department name 'Marketing' and counts them.

e. UPDATE employees SET salary = 60000 WHERE id = 1001;
-- This command updates the salary column of the employee with an id of 1001 to 60000.

f. DELETE FROM employees WHERE salary < 30000;
-- This command deletes all employees whose salary is less than 30000.


-- Use the departments table to answer the following questions:

a. SELECT * FROM departments;
-- This command selects all columns and rows from the departments table.

b. SELECT name, manager FROM departments WHERE name = 'Finance';
-- This command selects the name and manager columns of the "Finance" department.

c. SELECT departments.name, COUNT(*) FROM employees JOIN departments ON employees.department_id = departments.id GROUP BY departments.name;
-- This command calculates the total number of employees in each department by joining the employees table with the departments table on the department_id and id columns, respectively, and then grouping the result by department name.

d. INSERT INTO departments (name, manager) VALUES ('Research', 'John Doe');
-- This command inserts a new department called "Research" with a manager named "John Doe".