Joins
What does an INNER JOIN return?
Rows with matching values in both tables.
What does the UNION operator do?
Combines results from two queries, removing duplicates.
What does the % wildcard represent in SQL?
Zero, one, or multiple characters.
What does COUNT() do?
Counts all rows in a table.
What is a subquery?
A query nested within another SQL query.
Describe a LEFT JOIN.
Returns all records from the left table and matched records from the right table.
Explain the INTERSECT operator.
Returns only the rows common to both queries.
What does the _ wildcard represent?
A single character.
How do you calculate the average of the Salary column in the Employees table?
SELECT AVG(salary) FROM employees;
Define referential integrity.
Ensures foreign keys correctly reference primary keys in related tables.
What is a RIGHT JOIN?
Returns all records from the right table and matched records from the left table.
What is the purpose of the EXCEPT operator?
Returns rows from the first query that aren't in the second.
Write a query to find names starting with 'A' from the employees table.
SELECT * FROM employees WHERE name LIKE 'A%';
What is the purpose of GROUP BY?
Groups rows sharing a property so aggregate functions can be applied to each group.
What is a primary key constraint?
Uniquely identifies each record in a table.
Explain a FULL OUTER JOIN.
Returns all records when there is a match in either left or right table.
How does UNION ALL differ from UNION?
UNION ALL includes duplicates; UNION removes them.
Write a query to find names with the second letter being 'e' from the employees table
SELECT * FROM employees WHERE name LIKE '_e%';
When would you use HAVING instead of WHERE?
To filter groups after aggregation.
How does a foreign key constraint work?
Ensures the value in a column matches a value in another table's primary key.
Provide an example where a FULL OUTER JOIN is useful.
Combining customer and order data to show all customers and all orders, regardless of matches.
When would you use INTERSECT over INNER JOIN?
When comparing results from two queries without needing to combine columns.
Explain the difference between LIKE and = in WHERE clauses.
LIKE allows pattern matching; = requires exact matches.
Write a query to find departments with more than 10 employees. Employees table has an employee_id column & department name column.
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
Write a subquery to find employees earning above the average salary. Employees tables contains a column called salary.
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);