Module 1: Data structures
Knowledge check: Introduction to databases
Practice Assignment
1. True or False: A primary key can have NULL values.
- False
- True
Explanation: A primary key must have unique and non-NULL values to uniquely identify each record in a table.
2. What data type should you use to store an email address?
- DECIMAL
- VARCHAR
- BOOLEAN
- INTEGER
Explanation: Email addresses are alphanumeric strings and are best stored using the VARCHAR data type, which can hold variable-length character strings.
3. What type of relationship is formed between two tables when a single record in one table can be associated with multiple records in another table through a foreign key?
- One-to-one
- Many-to-many
- One-to-many
- Many-to-one
Explanation: A one-to-many relationship occurs when one record in a table is associated with multiple records in another table via a foreign key.
4. You’re designing a database for a school's student management system. The Students table needs to store each student's information, including their unique student ID, name, date of birth, and class. Which column would be most appropriate to set as the primary key in the Students table?
- Class Name
- Student ID
- Date of Birth
- Name
Explanation: A student ID is unique for each student and is the most appropriate column for a primary key.
5. Which of the following statements accurately describes the characteristics of relational databases? Select all that apply.
- They support complex queries and data manipulation with SQL.
- They allow for the establishment of relationships between different tables of data.
- They primarily use a flat-file format to store data for easy accessibility.
- They store data in structured formats using tables, columns, and rows.
Explanation: Relational databases use SQL for querying and manipulating data, support relationships between tables, and store data in structured table formats. They do not use a flat-file format.
Knowledge check: SQL queries
Practice Assignment
6. You have a table named Books in your library database, and you want to find all books published after 2000 and sort them by their YearPublished in descending order. Which SQL query would you use?
Explanation: This query retrieves all columns for books published after 2000 and sorts them in descending order of YearPublished.
7. You want to update the availability of all books by the author "George Orwell" to 'No' in the Books table. Complete the SQL query:
- Author = ‘George Orwell’
- Title = ‘1984’
- Available = ‘Yes’
- YearPublished > 1980
Explanation: The WHERE clause specifies that only rows where the Author is “George Orwell” will be updated.
8. True or False: The GROUP BY clause in SQL is used to aggregate data and must always be followed by an aggregate function such as SUM, COUNT, or AVG.
- True
- False
Explanation: While the GROUP BY clause is commonly used with aggregate functions, it is not mandatory. You can group by columns without using aggregate functions if you need to organize data into subsets.
9. You are analyzing the Books table and want to determine how many books are available by genre. You run the following SQL query:
Based on this query, which of the following statements is true? Select all that apply.
- The query will produce an error because you cannot group by more than one column.
- The COUNT(*) function can only be used with the GROUP BY clause.
- The query returns the number of books for each combination of genre and availability status.
- The query groups the books by both Genre and Available status.
Explanation: The GROUP BY clause groups the data by both Genre and Available. The COUNT(*) function calculates the number of books for each combination of these grouped columns.
10. Complete the statement: To sort the results of a query by the Title column in ascending order and then by YearPublished in descending order, the SQL query should use the following clause:
- ASC, DESC
- DESC, DESC
- DESC, ASC
- ASC, ASC
Explanation: Sorting by Title in ascending order comes first, followed by YearPublished in descending order.
Knowledge check: Joining tables
Practice Assignment
11. You have two tables in your database: Books and Authors. You want to find all books and their corresponding authors, even if some books do not have a matching author entry. Which SQL query would you use?
Explanation: The LEFT JOIN ensures that all records from the Books table are included in the result, even if there is no matching record in the Authors table.
12. True or False: When using a FULL OUTER JOIN, all records from both tables will be included in the result set, regardless of whether they have a matching record in the other table.
- False
- True
Explanation: A FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN, ensuring all records from both tables are included.
13. You want to retrieve a list of all books and magazines with their titles and publication years but avoid duplicate entries. Complete the SQL query by selecting the appropriate operator.
- UNION ALL
- INNER JOIN
- UNION
- FULL JOIN
Explanation: The UNION operator merges the results of two queries and removes duplicate rows. UNION ALL would include duplicates, which is not desired here.
14. You have two tables, Books and Magazines, and want to create a list showing all genres and the number of books or magazines in each genre, including those genres that might not have any entries in one of the tables. Based on the following SQL query, select all statements that apply.
- The query returns a count of items per genre, including genres with zero items in Magazines.
- The query groups the results by Genre across both tables.
- The query uses LEFT JOIN to ensure all genres in Magazines are included.
- The query could miss some genres that are present only in magazines.
Explanation: The LEFT JOIN ensures all genres in Books are included, but it might miss genres that only appear in Magazines. The GROUP BY aggregates data by genre across the joined tables.
15. Fill in the blank: To display a message indicating "Out of Stock" when a book is not available in the Books table, the SQL query should use the following CASE statement:
Which clause correctly fills in the blank in the above query?
- IF
- HAVING
- WHERE
- CASE
Explanation: The CASE statement allows conditional logic in SQL queries, enabling the query to return “Out of Stock” when Available = 'No' and “In Stock” otherwise.
Knowledge check: Joining tables
Practice Assignment
16. You are tasked with generating a list of all artists in the Chinook database, including those who haven’t released any albums. The query should return artist names from the Artists table and their corresponding album titles from the Albums table. If an artist has no associated album, the album title should display as NULL. Which JOIN type should be used here?
- RIGHT JOIN
- LEFT JOIN
- INNER JOIN
- Emulating FULL OUTER JOIN
Explanation: A LEFT JOIN retrieves all records from the Artists table and matches those records with entries from the Albums table. If no match is found, the album title will be NULL.
17. You are tasked with generating a report that lists all tracks and their corresponding album titles. Some tracks may not belong to any album, and those should display NULL in the album title field. Which SQL JOIN would you use to accomplish this?
- RIGHT JOIN
- LEFT JOIN
- INNER JOIN
- Emulating FULL OUTER JOIN
Explanation: A LEFT JOIN ensures all tracks are included in the result set, even if they don’t belong to any album. The NULL will indicate missing album titles.
18. You are tasked with generating a combined list of genres from the Chinook database, separating them into two categories: Major genres (with more than 50 tracks) and Minor genres (with 50 tracks or fewer). Which SQL operation should be used to combine the results of the below queries into a single list?
- UNION
- LEFT JOIN
- INNER JOIN
- CASE
Explanation: The UNION operator combines results from two separate queries into a single list and removes duplicates.
19. You’re responsible for generating reports that categorize customers into spending tiers based on their total purchases recorded in the Invoices table. To do this effectively, you use the SQL CASE statement, which allows you to apply conditional logic within your queries.
Which of the following queries correctly uses the CASE statement to categorize customers based on their spending? Select all that apply.
Explanation: The CASE statement requires the keyword CASE to begin and END to terminate. Each condition follows the pattern WHEN <condition> THEN <result>.
20. You want to create a list that includes all artists and their corresponding albums, even if some artists have no albums or some albums have no associated artists. Since MySQL does not directly support FULL OUTER JOIN, you need to emulate it. Select all that apply to emulate the FULL OUTER JOIN.
- RIGHT JOIN
- LEFT JOIN
- INNER JOIN
- UNION
Explanation: MySQL does not directly support FULL OUTER JOIN. By combining LEFT JOIN and RIGHT JOIN with a UNION, you emulate its functionality.
Module quiz: Introduction to databases
Graded Assignment
21. You are designing a database for a library system to manage information about books. You decide to use a relational database to store this information. To achieve this, you need to create the Books table in the database.
Which of the following statements correctly describes how you should structure the Books table?
Select all that apply.
Explanation: These principles ensure that the table is well-structured, normalized, and adheres to relational database design best practices.
22. You are a database administrator tasked with creating a table called Books to store information about a collection of books. The table should include a BookID column for unique identifiers and a Title column that stores book titles as variable-length text data.
Complete the SQL statement to correctly define the Title column:
- CHAR
- VARCHAR
- TEXT
- STRING
Explanation: The VARCHAR data type is used for variable-length text. The (255) specifies the maximum number of characters.
23. Which statement about primary keys in SQL is true?
- A table can have multiple primary keys.
- A primary key column cannot contain NULL values.
- A primary key uniquely identifies each row in a table.
- Primary keys are always integers.
Explanation: A primary key is a unique identifier for table records and cannot contain NULL values. A table can have only one primary key, but it can consist of multiple columns (composite primary key).
24. True or False: A foreign key in a table is a field that uniquely identifies a row in another table.
- False
- True
Explanation: A foreign key is a column or set of columns in one table that refers to a primary key in another table, establishing a relationship between the two tables.
25. Which SQL statement correctly retrieves all books published after the year 2000?
- SELECT * FROM Books WHERE YearPublished < 2000;
- SELECT * FROM Books WHERE YearPublished > 2000;
- SELECT * FROM Books WHERE YearPublished != 2000;
- SELECT * FROM Books WHERE YearPublished = 2000;
Explanation: The condition YearPublished > 2000 filters the rows where the publication year is greater than 2000.
26. You are working with a Books table in a database that contains information about various books, including their titles, authors, prices, and publication years. You want to generate a report that lists all the books sorted by their publication year from the earliest to the latest and then by price from lowest to highest for books published in the same year.
Which SQL clauses should you use to sort the records in this manner? Select all that apply.
- ORDER BY
- ASC
- WHERE
- GROUP BY
Explanation: The ORDER BY clause sorts rows, and ASC specifies ascending order for both the publication year and price.
27. Which SQL function is used to calculate the minimum value of a numeric column?
- MIN
- MAX
- SUM
- AVG
Explanation: The MIN function retrieves the smallest value from a numeric column.
28. Which SQL clause is used to group rows that have the same values into summary rows, like totals?
- ORDER BY
- GROUP BY
- HAVING
- JOIN
Explanation: The GROUP BY clause organizes rows with the same values in specified columns into summary rows, often used with aggregate functions.
29. You are working with a database for an online store that has two tables: Customers and Orders. The Customers table contains details about each customer, while the Orders table contains information about the orders placed by customers.
You want to generate a report that includes a list of all customers, regardless of whether they have placed any orders. The report should also include order details of those who have placed orders. If a customer has not placed any orders, they should still appear in the result set. Which SQL statement would correctly retrieve this information?
- RIGHT JOIN
- FULL JOIN
- INNER JOIN
- LEFT JOIN
Explanation: A LEFT JOIN retrieves all records from the Customers table and matches them with Orders records. Customers without orders will still appear with NULL in the order details.
30. Which SQL keyword is used to combine the result sets of two SELECT statements and include all duplicates?
- UNION
- INTERSECT
- UNION ALL
- EXCEPT
Explanation: The UNION ALL keyword combines the results of two SELECT queries without removing duplicates. The UNION keyword excludes duplicates.