Relational database basics in Intro to Computing - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a relational database?

A relational database is like a digital filing cabinet where data is stored in tables. Each table has rows (records) and columns (fields), and tables can be connected by common information.

beginner

What is a table in a relational database?

A table is like a spreadsheet sheet with rows and columns. Each row is a record (one item), and each column is a field (a type of information about the item).

beginner

What is a primary key?

A primary key is a unique ID for each row in a table. It helps find and connect data quickly, like a student ID number in a school.

beginner

What does 'relational' mean in relational databases?

Relational means tables are linked by common fields. For example, a 'Students' table and a 'Classes' table can connect using a student ID to show which classes a student takes.

beginner

How does a foreign key work?

A foreign key is a field in one table that points to the primary key in another table. It’s like a reference or a link between two tables.

What is stored in the columns of a database table?

A Types of information (fields)

B Individual records

C Links between tables

D Database software

Columns hold the types of information, called fields, like 'Name' or 'Age'.

What is the purpose of a primary key?

A To sort data alphabetically

B To uniquely identify each row

C To connect to the internet

D To store multiple tables

The primary key uniquely identifies each row so you can find data easily.

How do tables in a relational database connect?

A By having the same number of rows

B By sharing the same software

C By storing images

D Using foreign keys

Tables connect using foreign keys that reference primary keys in other tables.

Which of these is an example of a relational database?

A A spreadsheet with multiple sheets linked by IDs

B A single text document

C A photo album

D A video player

A spreadsheet with linked sheets is similar to tables connected in a relational database.

What does a foreign key do?

A Creates a new table

B Deletes data

C Links one table to another

D Encrypts data

A foreign key links one table to another by referring to a primary key.

Explain how tables in a relational database are connected and why this is useful.

Think about how a student ID can connect student info to their classes.

Describe what a primary key is and give a real-life example.

Consider something that uniquely identifies a person or item.

Practice

1. What is the main purpose of a relational database?

easy

A. To run computer programs

B. To store data as plain text files

C. To organize data into tables with rows and columns

D. To create graphics and charts

Solution

1. Step 1: Understand the structure of relational databases

   Relational databases store data in tables made of rows and columns, similar to a spreadsheet.

2. Step 2: Compare options with this structure

   Only 'To organize data into tables with rows and columns' matches the core purpose of relational databases.

3. Final Answer:

   To organize data into tables with rows and columns -> Option C

4. Quick Check:

   Relational database = tables with rows and columns [OK]

Hint: Think of data like a spreadsheet with rows and columns [OK]

Common Mistakes:

• Confusing databases with file storage
• Thinking databases create graphics
• Believing databases run programs

2. Which SQL command is used to add new data into a table?

easy

A. SELECT

B. INSERT

C. CREATE

D. DELETE

Solution

1. Step 1: Recall SQL commands and their purposes

   SELECT retrieves data, CREATE makes tables, DELETE removes data, and INSERT adds new data.

2. Step 2: Match the command to adding data

   INSERT is the command used to add new rows of data into a table.

3. Final Answer:

   INSERT -> Option B

4. Quick Check:

   Adding data = INSERT [OK]

Hint: INSERT means putting new data inside [OK]

Common Mistakes:

• Using SELECT to add data
• Confusing CREATE with INSERT
• Thinking DELETE adds data

3. Given the table Students with columns ID, Name, and Age, what will this SQL query return?

SELECT Name FROM Students WHERE Age > 20;

medium

A. All student names where age is greater than 20

B. All student IDs where age is greater than 20

C. All student ages where age is greater than 20

D. All data from the Students table

Solution

1. Step 1: Analyze the SELECT clause

   The query selects the Name column only, so the output will be student names.

2. Step 2: Analyze the WHERE condition

   The condition Age > 20 filters rows to only those students older than 20.

3. Final Answer:

   All student names where age is greater than 20 -> Option A

4. Quick Check:

   SELECT Name with Age > 20 = student names over 20 [OK]

Hint: SELECT column filters output; WHERE filters rows [OK]

Common Mistakes:

• Thinking SELECT returns all columns
• Ignoring the WHERE condition
• Confusing column names in SELECT

4. Identify the error in this SQL statement:

INSERT INTO Students (ID, Name Age) VALUES (1, 'Alice', 22);

medium

A. Missing comma between column names

B. Incorrect table name

C. VALUES keyword is misspelled

D. Extra parentheses around values

Solution

1. Step 1: Check the column list syntax

   The columns are listed as ID, Name Age without a comma between Name and Age.

2. Step 2: Confirm correct syntax for INSERT

   Column names must be separated by commas. Missing comma causes syntax error.

3. Final Answer:

   Missing comma between column names -> Option A

4. Quick Check:

   Columns need commas between names [OK]

Hint: Check commas between column names carefully [OK]

Common Mistakes:

• Overlooking missing commas
• Assuming VALUES is misspelled
• Thinking parentheses are extra

5. You have two tables: Orders(OrderID, CustomerID, Amount) and Customers(CustomerID, Name). Which SQL query correctly lists all orders with the customer names?

hard

A. SELECT OrderID, Name FROM Orders INNER JOIN Customers ON Orders.OrderID = Customers.CustomerID;

B. SELECT OrderID, Name FROM Orders, Customers WHERE Orders.OrderID = Customers.CustomerID;

C. SELECT OrderID, Name FROM Orders LEFT JOIN Customers ON Orders.Amount = Customers.CustomerID;

D. SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Solution

1. Step 1: Understand the relationship between tables

   Orders and Customers are linked by CustomerID, so join must use this key.

2. Step 2: Check each JOIN condition

   SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID; joins on Orders.CustomerID = Customers.CustomerID, which is correct. Others join on wrong columns.

3. Final Answer:

   SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID; -> Option D

4. Quick Check:

   Join tables on matching CustomerID [OK]

Hint: Join tables using matching keys (CustomerID) [OK]

Common Mistakes:

• Joining on wrong columns
• Mixing OrderID with CustomerID
• Using incorrect JOIN types