SQL / Database Flashcards
What is the having clause in SQL?
It’s like a where clause except it works against a query that contains a group by. Example: if you wanted to create a query to list all customers and their order count for all customers that have an order count greater than three (i.e., using an aggregate function).
What is normalization and denormalization?
Normalization is where we try to eliminate duplication of data and dependency. Example: taking the mailing address out of a table and replacing them with a reference to an address table. So when a customer has the same physical address, it can refer to one row in the address table.
Denormalization is the opposite— we’re trying to increase performance by duplicating some data or storing it together. Example: taking the customer name and shipping address and putting it into the orders table. So we don’t have to do any joins when processing the data.
What are the different types of joins in SQL?
Inner is just a join where we take two tables and get the result of rows that match the join predicate (the clause used — usually a column from one matching a column from another) from both tables.
An outer join is where one or both of the tables will include all rows even if the join predicate is not satisfied.
Left outer join, the table on the left will always have a row in the result set for each row it contains, even if there is no value in the right (null).
Right outer join, is the opposite. Full outer join means both sides contain all rows.
A cross join is the Cartesian product of rows from tables from the join — the result set contains a row for each combination of every row from the first and every row from the second.
What is the difference between a primary key and a foreign key?
A primary key is a column or a set of columns that uniquely identify a row in a table. A primary key should be short, stable and simple.
A foreign key is a field (or collection of fields) in a table whose value is required to match the value of the primary key for a second table.
How could I return the count of records returned from a query?
SQL (use an aggregate function and HAVING clause):
SELECT first_name, COUNT(first_name) FROM actor WHERE actor_id BETWEEN 100 AND 200 GROUP BY first_name HAVING COUNT(first_name) > 1;
Mongo:
db.entitites.find({“_