Assignment: SQL Operations Practice

Objective:

The objective of this assignment is to help students understand and apply SQL concepts such as table creation, UNION, INTERSECT, WHERE, AND, OR$,$ NOT, and other set operations without using JOIN.

Dataset:

Students need to create two tables: Employees and Departments.

Step $1$: Create Tables

Create the following two tables:

$1.$ Table: Employees

CREATE TABLE Employees $($

$$ EmployeeID INT PRIMARY KEY,

$$ FirstName VARCHAR$(50),$

$$ LastName VARCHAR$(50),$

$$ DepartmentID INT,

$$ Salary DECIMAL$(10,2),$

$$ HireDate DATE,

$$ JobTitle VARCHAR$(50)$

$)$;

$2.$ Table: Departments

CREATE TABLE Departments $($

$$ DepartmentID INT PRIMARY KEY,

$$ DepartmentName VARCHAR$(50),$

$$ Location VARCHAR$(50)$

$)$;

Step $2$: Insert Sample Data

Insert sample data into these tables. You can use your own sample data or refer to the following:

Employees: Insert $10-15$ records.

Departments: Insert $5-7$ records.

Step $3$: Solve the Problems

Problems:

$1.$ List all employees who have a salary greater than $$60,000.$

o$$ Hint: Use the WHERE clause to filter employees with a salary greater than $$60,000.$

$2.$ Display employees who work in the $\text{'}$IT$\text{'}$ department and earn less than $$50,000.$

o$$ Hint: Use the AND operator to apply multiple conditions.

$3.$ Find all employees who are either 'Managers' or 'Analysts'.

o$$ Hint: Use the OR operator to include multiple job titles.

$4.$ List all employees who do NOT work in the $\text{'}$HR$\text{'}$ department.

o$$ Hint: Use the NOT operator along with the WHERE clause.

$5.$ Create a query using UNION to combine results of employees in the 'Finance' department and those who are 'Analysts'.

o$$ Hint: Use two separate queries combined with UNION to merge results.

$6.$ Use INTERSECT to find employees who have the job title 'Developer' and are also in the $\text{'}$IT$\text{'}$ department.

o$$ Hint: Use two queries and the INTERSECT operator to identify common records.

$7.$ Create a query using EXCEPT $($or MINUS for Oracle$)$ to find employees in the 'Finance' department who are not 'Managers'.

o$$ Hint: Use EXCEPT or MINUS to subtract 'Manager' employees from 'Finance' department employees.

$8.$ Find all employees hired after January $1,2020,$ and earning a salary between $$40,000$ and $$70,000.$

o$$ Hint: Use the WHERE clause with the BETWEEN operator and a date condition.

$9.$ List employees who have a department ID of $1,2,$ or $3.$

o$$ Hint: Use the IN operator to filter by multiple department IDs.

$10.$ Find employees whose last names do not start with the letter $\text{'}$A$\text{'}.$

o$$ Hint: Use the NOT LIKE operator with a wildcard to exclude specific names.

Submission Guidelines:

For each problem, write the SQL code and provide screenshots or outputs of the results.

Ensure your code is well$-$documented with comments to explain the logic of each query.