BCA SQL Practice Kit

Part 1: SQL Exam Questions (20 Questions)

Tables Used
Employee(EmpID, EmpName, DeptID, Salary, JoinDate)

Department(DeptID, DeptName, Location)

Project(ProID, ProName, DeptID, Budget)

Questions and Solutions

Q1. Display all employee names.

SELECT EmpName FROM Employee;

Q2. Display names and salaries of employees earning more than 30,000.

SELECT EmpName, Salary FROM Employee WHERE Salary > 30000;

Q3. List all distinct department IDs from Employee table.

SELECT DISTINCT DeptID FROM Employee;

Q4. Count total employees.

SELECT COUNT(*) AS TotalEmployees FROM Employee;

Q5. Display the average salary of employees.

SELECT AVG(Salary) AS AverageSalary FROM Employee;

Q6. List employees with salary between 20,000 and 50,000.

1
 SELECT EmpName, Salary FROM Employee WHERE Salary BETWEEN 20000 AND 50000;

Q7. Show employees whose name starts with 'A'.

SELECT EmpName FROM Employee WHERE EmpName LIKE 'A%';

Q8. Display employees sorted by salary in descending order.

SELECT * FROM Employee ORDER BY Salary DESC;

Q9. Retrieve the maximum salary from Employee table.

SELECT MAX(Salary) AS MaxSalary FROM Employee;

Q10. Retrieve the minimum salary from Employee table.

SELECT MIN(Salary) AS MinSalary FROM Employee;

Q11. Display total salary of all employees.

SELECT SUM(Salary) AS TotalSalary FROM Employee;

Q12. List the number of employees in each department.

SELECT DeptID, COUNT(*) AS NoOfEmployees FROM Employee GROUP BY DeptID;

Q13. Display average salary of each department.

SELECT DeptID, AVG(Salary) AS AvgSalary FROM Employee GROUP BY DeptID;

Q14. Show employees who joined after 1-Jan-2020.

SELECT EmpName, JoinDate FROM Employee WHERE JoinDate > '2020-01-01';

2
Q15. Retrieve employees whose salary is not 30,000.

SELECT EmpName, Salary FROM Employee WHERE Salary <> 30000;

Q16. Display department names along with employee names (JOIN).

SELECT [Link], [Link] FROM Employee E JOIN Department D ON [Link] =

[Link];

Q17. List all employees and projects with their department.

SELECT [Link], [Link], [Link]

FROM Employee E
JOIN Department D ON [Link] = [Link]
JOIN Project P ON [Link] = [Link];

Q18. Show employees whose name contains 'n'.

SELECT EmpName FROM Employee WHERE EmpName LIKE '%n%';

Q19. Display employees with salary higher than the average salary.

SELECT EmpName, Salary FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM
Employee);

Q20. Display the number of projects in each department.

SELECT DeptID, COUNT(*) AS NoOfProjects FROM Project GROUP BY DeptID;

Part 2: SQL Viva Questions (30 Questions)

Q1. What is SQL? Explain its types. - SQL is Structured Query Language. Types: DDL, DML, DCL, TCL.

Q2. Difference between SQL and PL/SQL. - SQL is a query language, PL/SQL is procedural extension.

3
Q3. What is a table? Difference from database. - Table stores data in rows and columns; database is a
collection of tables.

Q4. Explain SELECT statement with example.

SELECT EmpName, Salary FROM Employee;

Q5. What are NULL values? How to handle them? - NULL represents missing value. Use IS NULL/IS NOT
NULL.

Q6. Purpose of DISTINCT? Example.

SELECT DISTINCT DeptID FROM Employee;

Q7. How does WHERE work? Can it be used with aggregate functions? - Filters rows before aggregation;
use HAVING with aggregates.

Q8. Difference between = and <>. - = checks equality; <> checks inequality.

Q9. How do you sort query results? ASC and DESC?

SELECT * FROM Employee ORDER BY Salary ASC;

SELECT * FROM Employee ORDER BY Salary DESC;

Q10. Filter using BETWEEN and LIKE. - BETWEEN 20000 AND 50000; LIKE 'A%'

Q11. What is an aggregate function? Examples. - Operates on multiple rows, returns single value.
Examples: COUNT, SUM, AVG, MAX, MIN.

Q12. Difference COUNT(*), COUNT(column), COUNT(DISTINCT column). - COUNT(*) all rows,

COUNT(column) non-null, COUNT(DISTINCT column) unique non-null.

Q13. Total salary of all employees.

SELECT SUM(Salary) AS TotalSalary FROM Employee;

Q14. Maximum and minimum salary.

SELECT MAX(Salary), MIN(Salary) FROM Employee;

4
Q15. Average salary per department.

SELECT DeptID, AVG(Salary) FROM Employee GROUP BY DeptID;

Q16. Purpose of GROUP BY. - Groups rows for aggregation.

Q17. WHERE vs HAVING. - WHERE filters before grouping; HAVING filters after grouping.

Q18. Example HAVING.

SELECT DeptID, COUNT(*) FROM Employee GROUP BY DeptID HAVING COUNT(*) > 5;

Q19. What is JOIN? Types. - Combines rows from tables. Types: INNER, LEFT, RIGHT, FULL OUTER.

Q20. INNER JOIN vs LEFT JOIN. - INNER → matching rows only; LEFT → all left table rows + matches.

Q21. Employee names with department names.

SELECT [Link], [Link] FROM Employee E JOIN Department D ON [Link] =

[Link];

Q22. Employees and projects with department.

SELECT [Link], [Link], [Link] FROM Employee E JOIN Department D ON

[Link] = [Link] JOIN Project P ON [Link] = [Link];

Q23. What is a subquery? - A query inside another query.

Q24. Can subquery return multiple rows? Example.

SELECT EmpName FROM Employee WHERE DeptID IN (SELECT DeptID FROM Department
WHERE Location='Delhi');

Q25. Employees earning more than average salary.

SELECT EmpName, Salary FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM
Employee);

5
Q26. Employees whose name contains 'n'.

SELECT EmpName FROM Employee WHERE EmpName LIKE '%n%';

Q27. Number of projects in each department.

SELECT DeptID, COUNT(*) FROM Project GROUP BY DeptID;

Q28. Difference BETWEEN … AND … and >= … AND <= … - BETWEEN is shorthand for >= AND <=.

Q29. Difference CHAR and VARCHAR. - CHAR fixed length; VARCHAR variable length.

Q30. Handling duplicate records. - Use DISTINCT or ROW_NUMBER() with CTE to remove duplicates.