SQL Basics
This query would retrieve all columns and rows from the Employees table.
What is SELECT * FROM Employees;
This uniquely identifies each record in a table and by default is the clustered index on a table.
What is the primary key?
This constraint automatically assigns a preset value when none is provided.
What is the DEFAULT constraint?
This JOIN returns only rows that have matching values in both tables.
This is the practice of organizing data to eliminate redundancy and improve consistency.
What is normalization?
This clause can be used to filter records returned by an SQL query and is commonly used in SELECT, UPDATE, and DELETE commands.
What is the WHERE clause?
These are the possible ways to relate tables to one another.
What are one to one, one to many, and many to many relationships?
This constraint generates a unique value, increasing for each new row.
What is an IDENTITY (or AUTO_INCREMENT) constraint?
This JOIN could return all customers, even if they had no orders placed.
What is a LEFT JOIN?
This could occur when a query/table lacks an appropriate index while filtering rows, resulting in a very slow response time.
What is a table scan?
This clause combines identical column values into summary rows. For example, getting the count of ski models for each ski brand.
What is the GROUP BY clause?
This type of key combines multiple columns to unique identify a record.
What is a composite key?
This data type would be great to derive a user's age from.
What is a DATE or DATETIME?
This virtual table is great for simplifying complex queries and can help us to secure sensitive data.
What is a view?
This design principle ensures each column stores only one piece of information. Bonus: It is the first step in _____.
What is atomicity and First Normal Form?
What is the HAVING clause?
This will automatically delete dependent rows when a parent is deleted.
This would be great to add to a table if we need to retrieve data quicker. However, you should consider the downsides, which are: ____.
What are an index and slower data modifications?
The CEO of your company decided to learn SQL and angrily demands to know why their database is broken because some customers are not coming back in their query:
SELECT c.CustomerName, o.OrderID
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2025-01-01';
What is using the wrong JOIN type - INNER JOIN wrong, LEFT JOIN correct.
This is the form that removes transitive dependencies (non-key columns depend only on the primary key of the table) and is what most databases can strive for.
What is Third Normal Form?
This type of query utilizes values from its parent query and will run for each row returned from the parent query.
What is a correlated subquery?
This type of relationship must be defined using this type of table.
What are many to many relationships and an associative table?
_____ determines the physical row order for a table, and _____ is a separate ordered structure pointing to our data, generally stored in a ________.
What is a clustered index, a non clustered index, and a B (Binary) Tree?
This query will show us these departments:
SELECT d.DepartmentName
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
WHERE e.EmployeeID IS NULL;
What are departments with no employees?
A database design that supports historical tracking of data changes. (Product A was priced at $10 between these dates, at $12 between other dates, etc.)
What is a temporal database/What are temporal attributes?