Assignment: From Basic to Advanced Part 2

[Pankaj-Str]

Objective

This assignment is designed to guide students through various SQL topics, ranging from basic to advanced levels. By the end of the assignment, students will be able to create, query, and manipulate relational databases with advanced SQL techniques.

---

Part 1: Basic SQL (DML & DDL)

1. Creating Tables:

• Create a database named EmployeeManagement.
• Inside this database, create the following tables:
  • Employee:
    • EmployeeID (Primary Key, Integer)
    • FirstName (Text, not NULL)
    • LastName (Text, not NULL)
    • DOB (Date)
    • HireDate (Date)
    • DepartmentID (Foreign Key, Integer)
    • Salary (Decimal)
  • Department:
    • DepartmentID (Primary Key, Integer)
    • DepartmentName (Text, not NULL)

Task:

• Write SQL queries to:
  • Create both tables with appropriate constraints.
  • Insert at least 5 records into each table.

2. Basic Data Retrieval:

Task:

• Write SQL queries to:
  • Retrieve all employees' FirstName, LastName, and Salary.
  • Retrieve employees who were hired after January 1, 2020.
  • Retrieve employees with a salary greater than 50000.

---

Part 2: Intermediate SQL (Joins, Aggregations, Subqueries)

1. Joins:

Task:

• Write SQL queries to:
  • Retrieve a list of employees along with their department names using an inner join.
  • List all departments along with the total number of employees in each department (use LEFT JOIN to include departments without employees).

2. Aggregations:

Task:

• Write SQL queries to:
  • Find the average salary of employees in each department.
  • Get the maximum and minimum salary of employees in the entire company.

3. Subqueries:

Task:

• Write SQL queries to:
  • Retrieve employees whose salary is above the company’s average salary.
  • Retrieve the department(s) with the highest total salary.

---

Part 3: Advanced SQL (Complex Queries, Views, Transactions, Indexing)

1. Complex Queries:

Task:

• Write SQL queries to:
  • Retrieve employees who have been with the company for more than 5 years.
  • List employees who were hired in the same year as another employee (use a self-join).

2. Views:

Task:

• Create a view named EmployeeDetails that contains employee names, department names, and salaries.
• Write a query to retrieve data from the EmployeeDetails view for employees with a salary greater than 60000.

3. Transactions:

Scenario: You are adding a new department and transferring an employee from one department to another. Ensure this process happens atomically.

Task:

• Start a transaction.
• Insert a new department named Research.
• Update an employee's DepartmentID to the newly created Research department.
• Commit the transaction.

4. Indexing:

Task:

• Create an index on the Salary column of the Employee table to optimize salary-based queries.
• Test the performance before and after creating the index by running the query:

  SELECT * FROM Employee WHERE Salary > 70000;
  
  

This assignment will help students develop practical skills from basic SQL operations like creating and querying tables to advanced concepts like transactions, indexing, and functions.