### Table Creation
```sql
CREATE TABLE Department (
Deptno INT PRIMARY KEY,
Deptname VARCHAR(50),
Location VARCHAR(50)
);
CREATE TABLE Employee (
EmpNo INT PRIMARY KEY,
EmpName VARCHAR(50),
Job VARCHAR(50),
Manager INT,
HireDate DATE,
Salary DECIMAL(10, 2),
Commission DECIMAL(10, 2),
Deptno INT,
FOREIGN KEY (Deptno) REFERENCES Department(Deptno)
);
CREATE TABLE SalaryGrade (
Grade INT PRIMARY KEY,
LowSalary DECIMAL(10, 2),
HighSalary DECIMAL(10, 2)
);
```
### Inserting Sample Data
```sql
INSERT INTO Department VALUES (1, ‘HR’, ‘New York’);
INSERT INTO Department VALUES (2, ‘Finance’, ‘London’);
INSERT INTO Department VALUES (3, ‘IT’, ‘San Francisco’);
◼ Add more departments as needed
INSERT INTO Employee VALUES (101, ‘John Doe’, ‘Manager’, NULL, ‘1980-06-15’, 5000,
NULL, 1);
INSERT INTO Employee VALUES (102, ‘Jane Smith’, ‘Clerk’, 101, ‘1981-07-20’, 3000,
NULL, 2);
INSERT INTO Employee VALUES (103, ‘Alice Johnson’, ‘Analyst’, 101, ‘1981-11-15’, 4000,
NULL, 3);
◼ Add more employees as needed
INSERT INTO SalaryGrade VALUES (1, 2000, 3000);
INSERT INTO SalaryGrade VALUES (2, 3001, 4000);
INSERT INTO SalaryGrade VALUES (3, 4001, 5000);
◼ Add more salary grades as needed
```
### SQL Queries
1. **List employee names who have joined between the months July to December
of the year 1981.**
```sql
SELECT EmpName
FROM Employee
WHERE HireDate BETWEEN ‘1981-07-01’ AND ‘1981-12-31’;
```
2. **List employee details including department and their grade based on the
salary of all the employees except clerks.**
```sql
SELECT E.EmpName, E.Job, E.Salary, D.Deptname, S.Grade
FROM Employee E
JOIN Department D ON E.Deptno = D.Deptno
JOIN SalaryGrade S ON E.Salary BETWEEN S.LowSalary AND S.HighSalary
WHERE E.Job <> ‘Clerk’;
```
3. **List the employees whose name should not start with a letter ‘A’ and should
not end with a letter ‘A’ but it should be there in the name.**
```sql
SELECT EmpName
FROM Employee
WHERE EmpName LIKE ‘%A%’ AND EmpName NOT LIKE ‘A%’ AND EmpName NOT LIKE
‘%A’;
```
4. **Find all the employees who have joined the company before their managers.**
```sql
SELECT E1.EmpName
FROM Employee E1
JOIN Employee E2 ON E1.Manager = E2.EmpNo
WHERE E1.HireDate < E2.HireDate;
```
5. **List the name of employees who have finished their 25 years of experience in
the company.**
```sql
SELECT EmpName
FROM Employee
WHERE HireDate <= DATEADD(YEAR, -25, GETDATE());
```
6. **List the employee name, salary, PF, HRA, DA and gross; order the results in the
ascending order of gross.**
```sql
SELECT EmpName, Salary,
Salary * 0.10 AS PF,
Salary * 0.50 AS HRA,
Salary * 0.30 AS DA,
Salary + (Salary * 0.10) + (Salary * 0.50) + (Salary * 0.30) AS Gross
FROM Employee
ORDER BY Gross ASC;
```
7. **List the departments for which no employee is working.**
```sql
SELECT Deptname
FROM Department D
LEFT JOIN Employee E ON D.Deptno = E.Deptno
WHERE E.EmpNo IS NULL;
```
8. **List the department name, number of employees working, total salary, average
salary, maximum salary and minimum salary in each of the department.**
```sql
SELECT D.Deptname,
COUNT(E.EmpNo) AS NumEmployees,
SUM(E.Salary) AS TotalSalary,
AVG(E.Salary) AS AvgSalary,
MAX(E.Salary) AS MaxSalary,
MIN(E.Salary) AS MinSalary
FROM Department D
JOIN Employee E ON D.Deptno = E.Deptno
GROUP BY D.Deptname;
```
9. **List year in which most of the employees have joined the organization (Display
the year and no of employees).**
```sql
SELECT YEAR(HireDate) AS Year, COUNT(EmpNo) AS NumEmployees
FROM Employee
GROUP BY YEAR(HireDate)
ORDER BY NumEmployees DESC
LIMIT 1;
```
10. **List the department in which maximum number of employees working.**
```sql
SELECT D.Deptname
FROM Department D
JOIN Employee E ON D.Deptno = E.Deptno
GROUP BY D.Deptname
ORDER BY COUNT(E.EmpNo) DESC
LIMIT 1;
```
