SQL homework help Case Study 2

Do you need this SQL homework help Case Study 2?

Create the follow table:

Keep scrolling down.

Simple Queries:

1. List all the employee details. SELECT * FROM Employees;

2. List all the department details. SELECT * FROM Department;

3. List all job details. SELECT * FROM Job;

4. List all the locations. SELECT * FROM Location;

5. List out the First Name, Last Name, Salary, Commission for all Employees. SELECT First_Name, Last_Name, Commission FROM Employee;

6. List out the Employee ID, Last Name, Department ID for all employees and alias Employee ID as “ID of the Employee”, Last Name as “Name of the Employee”, Department ID as “Dep_id”. SELECT Employee_ID as ID, Last_Name AS “Name of the Employee”, Department_ID as Dep_id FROM Employee;

7. List out the annual salary of the employees with their names only. SELECT First_Name, Last_Name, Salary FROM Employee;

WHERE Condition:

1. List the details about “Smith”.  SELECT * FROM Employee WHERE Last_Name = “Smith”;

2. List out the employees who are working in department 20. SELECT * FROM Employee WHERE Department_ID = “20”;

3. List out the employees who are earning salaries between 3000and 4500. SELECT * FROM Employee WHERE salary BETWEEN 3000 AND 4500;  SELECT * FROM Employee WHERE salary >= 3000 AND Salary <= 4500;

4. List out the employees who are working in department 10 or 20. SELECT * FROM Employee WHERE Department_ID = 10 OR Department_ID = 20;

5. Find out the employees who are not working in department 10 or 30. SELECT * FROM Employee WHERE Department_ID NOT 10 AND Department_ID NOT 30;

6. List out the employees whose name starts with ‘S’. SELECT * FROM Employee WHERE First_Name LIKE ‘S%’

7. List out the employees whose name starts with ‘S’ and ends with ‘H’. SELECT * FROM Employee WHERE First_Name LIKE ‘S%’ AND Last_NAME LIKE ‘%H’;

8. List out the employees whose name length is 4 and start with ‘S’. SELECT * FROM Employee WHERE LENGTH(First_Name) = 4;

9. List out employees who are working in department 10 and draw salaries more than 3500. SELECT * FROM Employee WHERE Department_ID = 10 AND salary >= 3500;

10. List out the employees who are not receiving commission. SELECT * FROM Employee WHERE Comm = Null;

ORDER BY Clause:

1. List out the Employee ID and Last Name in ascending order based on the Employee ID. SELECT Employee_id, Last_Name FROM Employee ORDER BY Employee_ID ASC;

2. List out the Employee ID and Name in descending order based on salary. SELECT Employee_ID, First_Name FROM Employee ORDER BY Salary DESC;

3. List out the employee details according to their Last Name in ascending-order. SELECT * FROM Employee ORDER BY Last_Name ASC;

4. List out the employee details according to their Last Name in ascending order and then Department ID in descending order. SELECT * FROM Employee ORDER BY Last_Name ASC, Department_ID DESC;

GROUP BY and HAVING Clause:

1. How many employees are in different departments in the organization? SELECT Department_ID COUNT(Employee_ID) FROM Employee GROUP BY Department_ID;

2. List out the department wise maximum salary, minimum salary and average salary of the employees. SELECT Department_ID, MAX(Salary), MIN(Salary), AVG(Salary) FROM Employee GROUP BY Department_ID;

3. List out the job wise maximum salary, minimum salary and average salary of the employees. SELECT Job_ID, MAX(Salary), MIN(Salary), AVG(Salary) FROM Employee GROUP BY Job_ID;

4. List out the number of employees who joined each month in ascending order. SELECT
EXTRACT(MONTH FROM hire_date) AS month,
COUNT(*) AS number_of_employees
FROM
Employee
GROUP BY
month
ORDER BY
month ASC;

5. List out the number of employees for each month and year in ascending order based on the year and month.

6. List out the Department ID having at least four employees.

7. How many employees joined in the month of January?

8. How many employees joined in the month of January or September?

9. How many employees joined in 1985?

10. How many employees joined each month in 1985?

11. How many employees joined in March 1985?

12. Which is the Department ID having greater than or equal to 3 employees joining in April 1985?

Joins:

1. List out employees with their department names.

2. Display employees with their designations.

3. Display the employees with their department names and regional groups.

4. How many employees are working in different departments? Display with department names.

5. How many employees are working in the sales department?

6. Which is the department having greater than or equal to 5 employees? Display the department names in ascending order.

7. How many jobs are there in the organization? Display with designations.

8. How many employees are working in “New York”?

9. Display the employee details with salary grades. Use conditional statement to create a grade column.

10. List out the number of employees grade wise. Use conditional statement to create a grade column.

11.Display the employee salary grades and the number of employees between 2000 to 5000 range of salary.

12. Display all employees in sales or operation departments.

SET Operators:

1. List out the distinct jobs in sales and accounting departments.

2. List out all the jobs in sales and accounting departments.

3. List out the common jobs in research and accounting departments in ascending order.

Subqueries:

1. Display the employees list who got the maximum salary.

2. Display the employees who are working in the sales department.

3. Display the employees who are working as ‘Clerk’.

4. Display the list of employees who are living in “New York”.

5. Find out the number of employees working in the sales department.

6. Update the salaries of employees who are working as clerks on the basis of 10%.

7. Delete the employees who are working in the accounting department.

8. Display the second highest salary drawing employee details.

9. Display the nth highest salary drawing employee details.

10. List out the employees who earn more than every employee in department 30.

11. List out the employees who earn more than the lowest salary in department. Find out whose department has no employees.

12. Find out which department has no employees.

13. Find out the employees who earn greater than the average salary for their department.

THE END