Databases store organized information so programs can search, update, and analyze data efficiently. This cheat sheet covers relational database structure, SQL commands, keys, joins, and basic database design. Students need these ideas to build apps, interpret data, and understand how websites and information systems manage records.
It is designed as a quick reference for reading and writing common SQL statements.
The core idea is that related data is stored in tables made of rows and columns. SQL lets you create tables, insert records, filter results, combine tables, group data, and change stored values. Primary keys identify rows, foreign keys connect tables, and normalization helps reduce repeated data.
Good queries use clear conditions, correct join logic, and safe update or delete filters.
Key Facts
- A relational table stores data in rows and columns, where each row is a record and each column is a field or attribute.
- A primary key uniquely identifies each row in a table, such as student_id in a Students table.
- A foreign key is a column that references a primary key in another table, such as course_id in an Enrollments table referencing Courses(course_id).
- The basic query pattern is SELECT columns FROM table WHERE condition ORDER BY column;
- CRUD means CREATE, READ, UPDATE, and DELETE, which match INSERT, SELECT, UPDATE, and DELETE operations in SQL.
- An INNER JOIN returns only rows where the join condition matches in both tables, using syntax such as FROM A INNER JOIN B ON A.id = B.a_id.
- Aggregate functions summarize groups of rows, including COUNT(*), SUM(column), AVG(column), MIN(column), and MAX(column).
- A safe UPDATE or DELETE statement should usually include WHERE condition, because without WHERE it affects every row in the table.
Vocabulary
- Database
- A database is an organized collection of data that can be stored, searched, updated, and managed.
- Table
- A table is a database structure made of rows and columns that stores one type of related data.
- Primary Key
- A primary key is a column or set of columns that uniquely identifies each row in a table.
- Foreign Key
- A foreign key is a column that links one table to the primary key of another table.
- Query
- A query is an instruction written to retrieve, add, change, or delete data in a database.
- Normalization
- Normalization is the process of organizing tables to reduce repeated data and improve data consistency.
Common Mistakes to Avoid
- Forgetting the WHERE clause in UPDATE or DELETE is dangerous because it changes or removes every row in the table.
- Joining tables on the wrong columns gives incorrect matches because the relationship must use matching key columns such as Student.student_id = Enrollment.student_id.
- Using SELECT * in final queries can return unnecessary data because it fetches every column instead of only the columns needed.
- Confusing WHERE and HAVING causes errors because WHERE filters individual rows before grouping, while HAVING filters grouped results after GROUP BY.
- Storing repeated data in one table creates update problems because the same fact may need to be changed in many places.
Practice Questions
- 1 A Students table has 120 rows and a Courses table has 8 rows. How many rows would SELECT * FROM Students CROSS JOIN Courses; return?
- 2 Write a SQL query to select first_name, last_name, and grade from Students where grade is greater than or equal to 90, sorted from highest grade to lowest grade.
- 3 A table Orders has columns order_id, customer_id, order_total, and order_date. Write a query that returns the total money spent by each customer using GROUP BY.
- 4 Explain why a school database should store student information and course information in separate tables connected by an Enrollments table instead of storing every course name directly inside the Students table.
Understanding Databases & SQL
A database does more than hold facts. It enforces rules about those facts. A school system might require every attendance record to belong to a real student and a real class.
These rules are called constraints. A unique constraint can stop two users from registering the same email address. A required field can prevent a record with no date.
A check constraint can reject an impossible score, such as a negative number of books borrowed. Constraints matter because an error caught when data enters the system is much easier to fix than an error found months later in a report.
The hardest part of many SQL tasks is deciding what one result row should represent. Suppose a music app stores artists, albums, songs, and playlists. One artist can make many albums.
One playlist can contain many songs, while one song can appear on many playlists. That last relationship needs a linking table, often called a junction table. It might store playlist ID, song ID, and the position of the song.
When joining tables, follow the IDs carefully. A wrong join can multiply rows and produce an inflated count or total. Before using COUNT or SUM, inspect a few joined rows to check that each item appears the expected number of times.
Normalization is mainly about storing each fact in one sensible place. If a customer changes their phone number, the new number should need one update, not updates across every order they made. A separate Customers table avoids that repeated detail in an Orders table.
However, splitting data into too many tables can make queries harder to read and slower to run. Database design is a tradeoff. Start with the real facts the system must track.
Identify which facts describe one thing, which facts describe an event, and which facts can change over time. An order is an event.
A product name is a description that might change. Order records often keep the price paid at the time, since a later price change should not rewrite history.
Real databases often serve many people at once. Transactions protect groups of related changes. When a bank transfer moves money, the withdrawal and deposit must both succeed or both be cancelled.
Otherwise the records could show money disappearing. Indexes help databases find rows faster, much like an index in a textbook helps a reader find a topic. An index is useful for columns often used in searches, sorting, or joins, but too many indexes can slow inserts and updates.
Students should practice reading a query in stages. First identify the source tables. Then check the filtering condition.
Then check how rows are grouped or joined. Finally predict the output before running it. This habit exposes mistakes early, especially misplaced conditions, duplicate results, and changes that affect more records than intended.