SQL Querying and Database Design Basics Quiz Quiz

Challenge your understanding of essential SQL querying concepts and foundational database design principles. This beginner-friendly quiz covers common SQL commands, table structure, data types, keys, and relational database basics to help you solidify your skills in database management.

1. Basic SELECT Statement

   Which SQL statement would retrieve all columns from a table named 'employees'?

   1. FETCH employees ALL;
   2. GET ALL employees;
   3. SELECT columns FROM employees;
   4. SELECT * FROM employees;

   Explanation: The correct way to select all columns from the 'employees' table is 'SELECT * FROM employees;'. The asterisk (*) acts as a wildcard for all columns. 'GET ALL employees;' is not valid SQL syntax. 'SELECT columns FROM employees;' is incorrect because 'columns' is not a keyword. 'FETCH employees ALL;' is also not valid SQL; the FETCH command works differently.

2. Primary Key Purpose

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

   1. To uniquely identify each row
   2. To count the number of rows
   3. To encrypt table data
   4. To speed up data retrieval

   Explanation: A primary key uniquely identifies each row in a table, ensuring no duplicate records exist. While primary keys may help with retrieval speed via indexing, their main function is enforcing uniqueness. They do not encrypt data or count rows; those are different database functionalities.

3. Data Types

   Which data type would you use to store dates in a SQL table?

   1. INTEGER
   2. DATE
   3. DECIMAL
   4. VARCHAR

   Explanation: The 'DATE' data type is designed for storing calendar dates in SQL. 'VARCHAR' is meant for variable-length character strings, 'INTEGER' for whole numbers, and 'DECIMAL' for fixed-point numeric values, making them unsuitable for date storage.

4. Inserting Rows

   Which SQL command is used to add a new record to the 'products' table?

   1. UPDATE products VALUES (...);
   2. INSERT INTO products VALUES (...);
   3. CREATE ROW products (...);
   4. ADD ROW products VALUES (...);

   Explanation: 'INSERT INTO products VALUES (...);' is the correct syntax for adding a new record. 'ADD ROW' and 'CREATE ROW' are not SQL commands. 'UPDATE' is used to modify existing rows, not to add new ones.

5. Foreign Key Function

   In a student-enrollment database, what is the purpose of a foreign key in the 'enrollments' table referencing the 'students' table?

   1. To automatically delete rows in both tables
   2. To store encrypted passwords
   3. To establish a relationship with the 'students' table
   4. To speed up the database server

   Explanation: A foreign key in 'enrollments' points to the 'students' table, creating a relational link and ensuring referential integrity. It does not directly improve server speed, delete data automatically (unless specified with cascade rules), or store sensitive information like passwords.

6. Using WHERE Clause

   Which part of a SQL query filters rows based on a specified condition?

   1. GROUP BY
   2. ALTER
   3. ORDER BY
   4. WHERE

   Explanation: The 'WHERE' clause filters results to include only rows that meet a specific condition. 'GROUP BY' groups rows for aggregation, 'ORDER BY' sorts the results, and 'ALTER' is used for modifying table structures, not for filtering data.

7. Table Normalization

   What is the main goal of table normalization in database design?

   1. To speed up network connections
   2. To increase font size in tables
   3. To remove all indexes
   4. To reduce data redundancy

   Explanation: Normalization is a design process used to reduce redundant data and ensure logical data dependencies. It does not affect network connection speed or visual aspects such as font size. Removing all indexes is not related and could even harm performance.

8. NULL Values

   In SQL, what does the value NULL represent in a table cell?

   1. The string 'NULL'
   2. The default value for the column
   3. Absence of any value
   4. A zero value

   Explanation: NULL indicates that a value is missing or not applicable, representing an absence of any value. It is not a zero (numerical value) nor the text 'NULL'. The default value for the column may be different and is set by the column's schema.

9. Aggregate Function Usage

   Which SQL function would you use to calculate the total number of rows in a table?

   1. AVG()
   2. SUM()
   3. MAX()
   4. COUNT()

   Explanation: 'COUNT()' returns the total number of rows that match a query. 'SUM()' adds up numerical values in a column, 'AVG()' computes averages, and 'MAX()' returns the highest value, none of which are meant for simply counting rows.

10. Unique Constraint Purpose

    What does the UNIQUE constraint ensure when applied to a column in a table?

    1. No two rows can have the same value in that column
    2. The column must always have a default value
    3. Rows are ordered alphabetically by that column
    4. The column supports only numerical data

    Explanation: A UNIQUE constraint prevents duplicate values in a column, ensuring all entries are distinct in that field. It does not enforce default values, restrict data types to numbers only, or control row ordering, which are separate concerns.