In this assignment, you will be working with a fictional database of a company named "XYZ Corp." The database contains two tables, employees and departments. Your task is to write SQL commands to retrieve and manipulate data from these tables.
Use the employees table to answer the following questions:
- Write a query to select all columns and rows from the employees table.
- Write a query to select only the name and salary columns of all employees with a salary greater than 50000.
- Write a query to calculate the average salary of all employees.
- Write a query to count the number of employees who work in the "Marketing" department.
- Write a query to update the salary column of the employee with an id of 1001 to 60000.
- Write a query to delete all employees whose salary is less than 30000.
Use the departments table to answer the following questions:
- Write a query to select all columns and rows from the departments table.
- Write a query to select only the name and manager columns of the "Finance" department.
- Write a query to calculate the total number of employees in each department.
- Write a query to insert a new department called "Research" with a manager named "John Doe".
- A text file containing all SQL commands written to answer the above questions.
- A brief explanation of each SQL command and its purpose.
Note: You can assume that both tables have the following columns:
employees:
id, name, age, salary, department_id
departments:id, name, manager