SQL Queries
SQL command to retrieve all rows from the Employee table.
SELECT * FROM Employee;
SQL query to combine rows from two tables where they have matching values.
INNER JOIN
SQL command to count number of employees.
SELECT COUNT(*) FROM Employee;
Purpose of GROUP BY.
Groups rows based on column values.
Operator used for filtering rows.
Selection (σ)
SQL command to update salary of employee with ID=101 to 50000.
UPDATE Employee SET Salary = 50000 WHERE EmpID = 101;
Which join returns all rows from left table and matched rows from right?
LEFT JOIN
Function to find the highest salary.
MAX(Salary)
SQL to count employees in each department.
SELECT DNO, COUNT(*)
FROM Employee
GROUP BY DNO;
Operator used for choosing columns.
Projection (π)
What does the DISTINCT keyword do in SQL?
What does the DISTINCT keyword do in SQL?
Write a query to list all employees with their department names.
Find average salary of employees in department 10.
SELECT AVG(Salary)
FROM Employee
WHERE DNO = 10;
Difference between WHERE and HAVING.
WHERE filters rows before grouping; HAVING filters groups after grouping.
Difference between Union and Cartesian Product.
Union = combines tuples (same schema); Cartesian Product = all combinations.
SQL command to delete all records but keep the table structure.
TRUNCATE TABLE TableName;
Which join includes unmatched rows from both tables?
FULL OUTER JOIN
What will SUM(NULL,10,20) return?
30 (NULL ignored)
Find departments with average salary > 40000.
SELECT DNO, AVG(Salary)
FROM Employee
GROUP BY DNO
HAVING AVG(Salary) > 40000;
Expression for employees with salary > 50000.
σ Salary > 50000 (Employee)
Difference between DELETE and DROP.
DELETE removes rows, keeps structure; DROP removes table completely.
Difference between CROSS JOIN and INNER JOIN.
Difference between CROSS JOIN and INNER JOIN.
Which aggregate function returns number of unique values?
COUNT(DISTINCT column)
Can we use aggregate functions in WHERE clause?
No, only in HAVING
Expression for names of employees working in Dept 10.
Expression for names of employees working in Dept 10.