sql practice Flashcards
- What is the difference between an INNER JOIN and a LEFT JOIN?
iNNER JOIN returns only matching rows between both tables.
LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match is found, NULLs are returned for the right table’s columns.
- What is the difference between WHERE and HAVING?
WHERE filters rows before aggregation (used with raw columns).
HAVING filters rows after aggregation (used with GROUP BY).
- What does the COUNT function do?
COUNT() returns the number of rows matching a condition.
- What is the difference between UNION and UNION ALL?
UNION removes duplicates from the result set.
UNION ALL includes all records, including duplicates.
- What is the difference between a PRIMARY KEY and a UNIQUE constraint?
PRIMARY KEY uniquely identifies a record and cannot be NULL.
UNIQUE ensures all values in a column are unique but can contain NULLs.
What is a foreign key in SQL?
A foreign key is a column or a set of columns that links one table to another. It ensures referential integrity by only allowing values that exist in the referenced table’s primary key or unique column.
- What is the purpose of the GROUP BY clause?
The GROUP BY clause is used to group rows that share a common property and aggregate them (using functions like COUNT, SUM, AVG, etc.).
- What is the difference between a self join and a regular join?
self join is a type of join where a table is joined with itself. This is useful when you need to compare rows within the same table, like matching employees with their managers or comparing related data in a hierarchical structure.
A regular join typically involves joining two different tables to combine rows based on a related column between them. This is more common when you have separate tables representing different entities that need to be combined, like joining a customers table with an orders table.
