SQL and Database Design Fundamentals Quiz Quiz

Explore essential SQL concepts and database design best practices with this quiz, covering key topics such as relational tables, normalization, query syntax, and data integrity. Ideal for learners seeking a solid foundation in working with databases and understanding basic SQL operations.

1. Identifying the Primary Key

   In a table storing employee information, which column is the best candidate for a primary key?

   1. FirstName
   2. EmployeeID
   3. Department
   4. Salary

   Explanation: EmployeeID is the best candidate for a primary key because it provides a unique identifier for each record. FirstName may not be unique as multiple employees can share the same first name. Department groups employees and is not unique across individuals. Salary can repeat for different employees, making it unsuitable as a primary key.

2. Choosing SQL Statements

   Which SQL statement is used to retrieve data from a table named 'Customers'?

   1. PULL DATA FROM Customers;
   2. REMOVE FROM Customers;
   3. SELECT * FROM Customers;
   4. GET ALL Customers;

   Explanation: The 'SELECT * FROM Customers;' statement is the standard SQL command for retrieving data from the Customers table. 'REMOVE FROM Customers;' is not a valid SQL statement. 'GET ALL Customers;' and 'PULL DATA FROM Customers;' are not recognized SQL commands, even though they sound descriptive.

3. Understanding Foreign Keys

   What is the primary purpose of a foreign key in relational database design?

   1. To establish a link between two tables
   2. To increase query speed
   3. To encrypt column data
   4. To make table names unique

   Explanation: A foreign key is used to create a relationship between two tables by referencing the primary key in another table. Table names are made unique through naming conventions, not foreign keys. While foreign keys can influence performance, their main purpose is not to increase speed. They do not provide encryption for column data.

4. Recognizing SQL Syntax

   Which keyword is used to sort the result set in SQL queries?

   1. GROUP ON
   2. FILTER BY
   3. SORT BY
   4. ORDER BY

   Explanation: ORDER BY is the correct keyword used in SQL to sort records by one or more columns. GROUP ON is not an SQL keyword, and GROUP BY is used for grouping, not sorting. SORT BY and FILTER BY are common phrases but are not valid SQL commands.

5. Avoiding Data Redundancy

   Which database design principle reduces duplicate data by dividing information into multiple related tables?

   1. Normalization
   2. Aggregation
   3. Denormalization
   4. Minimization

   Explanation: Normalization reduces data redundancy by structuring data into related tables. Denormalization does the opposite, potentially increasing redundancy for performance. Aggregation refers to summarizing data but is not primarily about redundancy. Minimization is not a standard database design principle.

6. Ensuring Data Integrity

   If a column is defined as NOT NULL in a table, what does this constraint enforce?

   1. The values must be numeric
   2. Every row must have a value in this column
   3. The values must reference another table
   4. The values must be unique

   Explanation: The NOT NULL constraint ensures that each record has a value in the specified column. Uniqueness is enforced with UNIQUE or PRIMARY KEY, not NOT NULL. Numeric type is set by the data type, not the NOT NULL constraint. Referencing another table is handled by foreign keys.

7. Composing an INSERT Statement

   Which of the following is the correct syntax to insert a new record with values into a table named 'Products'?

   1. NEW ROW Products Name 'Apple' Price 2.50;
   2. ADD TO Products ('Apple', 2.50);
   3. APPEND Products ('Apple', 2.50);
   4. INSERT INTO Products (Name, Price) VALUES ('Apple', 2.50);

   Explanation: INSERT INTO is the correct SQL syntax for adding a new record with specified column values. ADD TO, NEW ROW, and APPEND are incorrect phrases for data insertion, despite sounding plausible.

8. Selecting Unique Values

   Which SQL keyword is used to return only distinct (non-duplicate) values from a column?

   1. DISTINCT
   2. SINGLE
   3. DIFFERENT
   4. UNIQUE

   Explanation: DISTINCT is the correct SQL keyword for retrieving unique values from a column and eliminating duplicates. UNIQUE is a constraint for column values, not a keyword for selection. DIFFERENT and SINGLE are not recognized in SQL for this purpose.

9. Identifying a Many-to-Many Relationship

   Which table structure is commonly used to represent a many-to-many relationship between Books and Authors?

   1. Two unrelated tables for Books and Authors
   2. A single table containing all Books and their Authors as columns
   3. A view that only lists author names
   4. A junction table containing BookIDs and AuthorIDs

   Explanation: A junction (or linking) table with BookIDs and AuthorIDs allows mapping multiple authors to multiple books. Combining all books and authors in one table as columns is inefficient and unscalable. Unrelated tables do not establish a many-to-many relationship. A view listing only author names does not represent the relationship.

10. Identifying the SQL WHERE Clause

    What is the main function of the WHERE clause in a SQL SELECT statement?

    1. To define the output format
    2. To update existing rows
    3. To filter rows based on given conditions
    4. To join multiple tables

    Explanation: The WHERE clause is used to filter rows in a result set based on specific conditions. It does not determine the output format or join tables; joining is done with JOIN statements. Updating rows is performed using the UPDATE statement, not the WHERE clause itself.