Database in everyday apps (social media, banking) in Intro to Computing - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all records from a database table named 'users'.

Intro to Computing

SELECT [1] FROM users;

Drag options to blanks, or click blank then click option'

Aname

B*

Cuser_id

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using a specific column name when the task asks for all columns.

Leaving the blank empty.

The asterisk (*) means 'all columns' in SQL, so this query selects all data from the 'users' table.

2fill in blank

medium

Complete the code to find users with the username 'alice'.

Intro to Computing

SELECT * FROM users WHERE username [1] 'alice';

Drag options to blanks, or click blank then click option'

A=

BLIKE

C!=

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' which means 'not equal'.

Using 'LIKE' without wildcards for exact match.

The '=' operator checks for exact equality, so this query finds users whose username is exactly 'alice'.

3fill in blank

hard

Fix the error in the SQL query to count the number of transactions.

Intro to Computing

SELECT COUNT([1]) FROM transactions;

Drag options to blanks, or click blank then click option'

Atransaction_id

Bdate

C*

Damount

Attempts:

3 left

💡 Hint

Common Mistakes

Counting a specific column which may have NULLs and cause incorrect counts.

Leaving the parentheses empty.

Using COUNT(*) counts all rows in the table, which is the correct way to count transactions.

4fill in blank

hard

Fill both blanks to select usernames and emails of users who have logged in more than 5 times.

Intro to Computing

SELECT [1], [2] FROM users WHERE login_count > 5;

Drag options to blanks, or click blank then click option'

Ausername

Bemail

Cpassword

Duser_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting password instead of email.

Selecting user_id instead of username.

This query selects the username and email columns for users with more than 5 logins.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps user IDs to their email addresses for users with active status.

Intro to Computing

user_emails = { [3]['[1]']: [3]['[2]'] for [3] in users if [3]['status'] == 'active' }

Drag options to blanks, or click blank then click option'

Auser_id

Bemail

Cuser

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'id' instead of 'user_id' as key.

Using 'users' as loop variable instead of a single user.

Mixing up keys and values in the comprehension.

This comprehension creates a dictionary where each key is a user_id and the value is the user's email, but only for active users.

Practice

1. What is the main purpose of a database in apps like social media or banking?

easy

A. To send emails to users automatically

B. To store and organize information so it can be easily found and used

C. To control the app's colors and fonts

D. To create graphics and animations for the app

Solution

1. Step 1: Understand what apps need

   Apps like social media and banking need to keep lots of information safe and easy to find.

2. Step 2: Role of a database

   A database stores and organizes this information in tables, like a digital filing cabinet.

3. Final Answer:

   To store and organize information so it can be easily found and used -> Option B

4. Quick Check:

   Database = store and organize info [OK]

Hint: Databases hold data, not design or emails [OK]

Common Mistakes:

• Confusing database with app design features
• Thinking database sends emails
• Mixing database with app styling

2. Which of the following is the correct way to describe a table in a database?

easy

A. A program that runs the app on your phone

B. A folder on your computer where files are saved

C. A place where data is stored in rows and columns, like a spreadsheet

D. A list of colors used in the app design

Solution

1. Step 1: Recall what a table is

   A table in a database organizes data in rows and columns, similar to a spreadsheet.

2. Step 2: Compare options

   Only 'A place where data is stored in rows and columns, like a spreadsheet' correctly describes this; others describe unrelated things.

3. Final Answer:

   A place where data is stored in rows and columns, like a spreadsheet -> Option C

4. Quick Check:

   Table = rows and columns [OK]

Hint: Think spreadsheet when you hear 'table' in databases [OK]

Common Mistakes:

• Confusing tables with app programs
• Mixing tables with computer folders
• Thinking tables are design elements

3. Consider a social media app database table named Users with columns UserID, Name, and Age. If the table has these rows:

UserID | Name   | Age
1      | Alice  | 25
2      | Bob    | 30
3      | Carol  | 22

What will be the result of a query that finds all users older than 23?

medium

A. Alice and Bob

B. Bob and Carol

C. Alice, Bob, and Carol

D. Only Carol

Solution

1. Step 1: Identify users older than 23

   Check each user's age: Alice (25) > 23, Bob (30) > 23, Carol (22) ≤ 23.

2. Step 2: List matching users

   Alice and Bob meet the condition; Carol does not.

3. Final Answer:

   Alice and Bob -> Option A

4. Quick Check:

   Age > 23 = Alice, Bob [OK]

Hint: Filter by age > 23 carefully [OK]

Common Mistakes:

• Including Carol who is 22
• Missing Bob who is 30
• Selecting all users without filtering

4. A banking app database has a table Accounts with columns AccountID, Balance. The following SQL query is written:

SELECT AccountID, Balance FROM Accounts WHERE Balance > 1000

But the app returns an error. What is the most likely mistake?

medium

A. The column Balance does not exist or is misspelled

B. The SQL query is missing a semicolon at the end

C. The table name Accounts is misspelled

D. The query should use SELECT * FROM Accounts instead

Solution

1. Step 1: Check query syntax

   The query syntax is correct and semicolon is optional in many systems.

2. Step 2: Verify column names

   If the app errors, likely the column Balance is misspelled or missing in the table.

3. Final Answer:

   The column Balance does not exist or is misspelled -> Option A

4. Quick Check:

   Column name error causes query failure [OK]

Hint: Check column names carefully for typos [OK]

Common Mistakes:

• Assuming missing semicolon causes error
• Thinking table name is wrong without checking
• Believing SELECT * fixes column errors

5. A social media app wants to show a list of friends for a user. The database has two tables:

Users(UserID, Name)
Friends(UserID1, UserID2)

If UserID1 and UserID2 represent friend pairs, which SQL query correctly finds all friends of user with UserID = 5?

hard

A. SELECT Name FROM Users WHERE UserID IN (SELECT UserID2 FROM Friends WHERE UserID1 = 5)

B. SELECT Name FROM Users WHERE UserID = 5

C. SELECT Name FROM Users WHERE UserID IN (SELECT UserID1 FROM Friends WHERE UserID2 = 5)

D. SELECT Name FROM Users WHERE UserID IN (SELECT UserID1 FROM Friends WHERE UserID2 = 5) OR UserID IN (SELECT UserID2 FROM Friends WHERE UserID1 = 5)

Solution

1. Step 1: Understand friend pairs

   Friends table stores pairs (UserID1, UserID2) meaning both are friends.

2. Step 2: Find all friends of UserID 5

   Friends can appear as UserID1 or UserID2, so query must check both sides.

3. Step 3: Analyze options

   SELECT Name FROM Users WHERE UserID IN (SELECT UserID1 FROM Friends WHERE UserID2 = 5) OR UserID IN (SELECT UserID2 FROM Friends WHERE UserID1 = 5) checks both UserID1 and UserID2 for 5, correctly finding all friends.

4. Final Answer:

   SELECT Name FROM Users WHERE UserID IN (SELECT UserID1 FROM Friends WHERE UserID2 = 5) OR UserID IN (SELECT UserID2 FROM Friends WHERE UserID1 = 5) -> Option D

5. Quick Check:

   Check both friend columns for user 5 [OK]

Hint: Friends can be in either column, check both [OK]

Common Mistakes:

• Checking only one side of friend pairs
• Selecting user 5 instead of their friends
• Using wrong columns in subqueries