Scribd
Upload
17 views3 pages

SQL Practice Questions and Answers

Uploaded by

bsah74439
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
17 views3 pages

SQL Practice Questions and Answers

Uploaded by

bsah74439
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL PRACTICE QUESTIONS

SELECT QUERY

Given a table Employee(EID, EName, DeptNo, Salary, City) and Department(DeptNo, DName) write
the queries for the following situations:-

1. Display all the details of the Employees.

2. Display the Department Names from the Department Table

3. Display the Details of the Employees who are working in Kolkata or Chennai

4. Display the Name and Salary of the Employees whose name has 3rd last Letter as o. (Eg. Ashutosh,
Santosh, Anoop, Swaroop etc.)

5. Display the EID, EName and DName from Employee and Department tables.

6. Display the EName and DName from Employee and Department for Employees who earn more than
50000 and are posted in 'Mumbai'

7. Display the Total Number of Employees in the Employee Table who are working in Delhi

8. Display the Average Salary of Each Department along with the DeptNo.

9. Arrange the Employees working in DeptNo 101 in Descending Order of their Salaries.

10. Display the Maximum Salary of Each Department for Departments which have more than 5
employees working in them.

11. Count the number of Employees working in each department whose names start with "A"

12. Find the Total Salary of Employees in Each Department which has less than 10 employees from
either "Mumbai" or "Chennai"

13. Find the No of Employees working in each city whose name contains "SH" and minimum salary in the
City is more than 25000. Arrange them in ascending order of the City.

14. Find the City wise Total Salary of employees for Employees working in "Physics" or "Chemistry"
Department, and earns more than 5000 and no of employees in the City is not less than 6 and arrange
them in Descending order of the No of Employees in each city.

ANSWERS

1. SELECT * FROM Employee;

2. SELECT DName FROM Department;


3. SELECT * FROM Employee

WHERE City IN ("Kolkata", "Chennai");

4. SELECT EName, Salary FROM Employee

WHERE EName LIKE "%o__";

5. SELECT EID, EName, DName

FROM Employee E,Department D

WHERE [Link] = [Link];

6. SELECT EName, DName

FROM Employee E JOIN Department D

ON [Link] = [Link]

WHERE Salary> 50000 and City = 'Mumbai';

7. SELECT COUNT(*) FROM Employee

WHERE City = "Delhi";

8. SELECT DeptNo, AVG(Salary) FROM Employee

GROUP BY DeptNo;

9. SELECT * FROM Employee

WHERE DeptNo = 101

ORDER BY Salary DESC;

10. SELECT MAX(Salary) FROM Employee

GROUP BY DeptNo

HAVING COUNT(*) > 5;


11. SELECT DeptNo, COUNT(*) FROM Employee

WHERE EName LIKE "A%"

GROUP BY DeptNo;

12. SELECT SUM(Salary) FROM Employee

WHERE City IN ("Mumbai", "Chennai")

GROUP BY DeptNo

HAVING COUNT(*) < 10;

13. SELECT City, COUNT(*) FROM Employee

WHERE EName LIKE "%SH%"

GROUP BY City

HAVING MIN(Salary)> 25000

ORDER BY City;

14. SELECT City, SUM(Salary)

FROM Employee E JOIN Department D

ON [Link] = [Link]

WHERE DName IN ("Physics", "Chemistry") AND Salary > 5000

GROUP BY City

HAVING COUNT(*) >= 6

ORDER BY COUNT(*) DESC;

Common questions

Powered by AI

SELECT DeptNo, AVG(Salary) FROM Employee GROUP BY DeptNo; . This implies that the salary data is being summarized to show a central tendency within each department, potentially indicating disparities or uniformities in salary distribution.

SELECT COUNT(*) FROM Employee WHERE City = 'Delhi'; . Knowing the number of employees in a specific location can be relevant for resource allocation, planning, and identifying growth or reduction areas within the organization.

SELECT DName FROM Department; . This query is useful for understanding the different departments available in the database, which could be critical for joins or filtering operations in more complex queries.

SELECT City, COUNT(*) FROM Employee WHERE EName LIKE '%SH%' GROUP BY City HAVING MIN(Salary) > 25000 ORDER BY City;

SELECT MAX(Salary) FROM Employee GROUP BY DeptNo HAVING COUNT(*) > 5; . The query groups employees by department and selects the maximum salary, filtering only departments with more than 5 employees to ensure a significant minimum staff size before aggregation.

SELECT DeptNo, COUNT(*) FROM Employee WHERE EName LIKE 'A%' GROUP BY DeptNo;

SELECT EName FROM Employee E JOIN Department D ON E.DeptNo = D.DeptNo WHERE DName IN ('Physics', 'Chemistry') AND Salary > 5000 GROUP BY City HAVING COUNT(*) >= 6;

SELECT DeptNo, SUM(Salary) FROM Employee WHERE City IN ('Mumbai', 'Chennai') GROUP BY DeptNo HAVING COUNT(*) < 10;

SELECT * FROM Employee WHERE DeptNo = 101 ORDER BY Salary DESC;

SELECT EName, DName FROM Employee E JOIN Department D ON E.DeptNo = D.DeptNo WHERE Salary > 50000 AND City = 'Mumbai';

You might also like