Relational Database Design and SQL Queries in ASP.NET Core Quiz

Assess your understanding of fundamental relational database concepts, normalization, and essential SQL queries in ASP.NET Core backend development. This quiz covers primary keys, data types, relationships, CRUD operations, and key SQL clauses to help reinforce your database design and querying skills.

1. Primary Key Selection

   Which statement best describes the purpose of a primary key in a relational database table used in an ASP.NET Core backend?

   1. It uniquely identifies each record in the table.
   2. It stores large binary objects such as images.
   3. It creates a reference to another table's column.
   4. It groups records with similar values for faster retrieval.

   Explanation: The primary key is used to uniquely identify each record in a table, which is essential for data integrity and efficient queries. Creating references to other tables involves foreign keys, not primary keys. Large binary objects are stored in specific data types like BLOB or varbinary, not as primary keys. Grouping for faster retrieval typically involves indexes or the GROUP BY clause, not primary keys.

2. Data Types in Table Columns

   You need to store a user's email address in a database table. Which SQL data type is most suitable for this column in an ASP.NET Core project?

   1. BOOLEAN
   2. VARCHAR
   3. DATE
   4. INT

   Explanation: VARCHAR is ideal for storing variable-length strings, such as email addresses, making it the correct data type. INT is meant for integer values and unsuitable for text data. DATE stores calendar dates, not text. BOOLEAN is used for true or false values, which do not accommodate text like email addresses.

3. Normalized Database Structure

   Why is normalization important when designing relational databases for an ASP.NET Core backend?

   1. To guarantee faster application startup times.
   2. To reduce data redundancy and improve data integrity.
   3. To ensure all columns are integers.
   4. To increase the number of indexes available.

   Explanation: Normalization structures the database to minimize redundancy and maintain data integrity, preventing anomalies during data operations. Increasing the number of indexes is not the main purpose of normalization. Ensuring all columns are integers is not required and not related to normalization. Faster application startup time is not a typical outcome of normalization.

4. Establishing Relationships

   In a relational database for an ASP.NET Core backend, how do you create a direct relationship between an Orders table and a Customers table?

   1. By making both tables have the same primary key
   2. By using the SELECT DISTINCT statement with both tables
   3. By adding a foreign key column in Orders referencing Customers
   4. By storing all order data inside the Customers table

   Explanation: A foreign key in the Orders table referencing the Customers table establishes a relational link, enforcing referential integrity. Sharing the same primary key across unrelated tables is incorrect and causes data issues. SELECT DISTINCT is for filtering query results, not defining relationships. Storing all order data in Customers causes redundancy and violates normalization.

5. Basic SQL SELECT Query

   Which SQL statement will retrieve all columns from the Products table in an ASP.NET Core backend database?

   1. DELETE FROM Products;
   2. SELECT * FROM Products;
   3. UPDATE Products SET Price = 0;
   4. INSERT INTO Products VALUES ('Sample', 10);

   Explanation: SELECT * FROM Products; retrieves every column and row from the Products table. DELETE removes records, which is not desired here. UPDATE changes data but does not fetch records. INSERT adds records and does not retrieve existing rows.

6. SQL Filtering with WHERE Clause

   You want to find all users older than 30 in the Users table. Which clause should you add to your SQL SELECT statement?

   1. GROUP BY Age
   2. ORDER BY Age DESC
   3. HAVING Age u003E 30
   4. WHERE Age u003E 30

   Explanation: The WHERE clause is used to filter rows based on specified conditions, so WHERE Age u003E 30 returns users older than 30. GROUP BY organizes results for aggregation, not filtering rows. ORDER BY sorts results but doesn't filter them. HAVING is used after GROUP BY for aggregated filtering, not single row conditions.

7. SQL JOINs for Related Data

   If you want to list all orders along with customer names, which SQL operation should you use between the Orders and Customers tables?

   1. CREATE INDEX
   2. UNION ALL
   3. ALTER TABLE
   4. INNER JOIN

   Explanation: INNER JOIN combines rows from both tables based on a related column, allowing you to view orders and corresponding customer names in a single result. UNION ALL merges rows from multiple SELECT queries, not joining tables on relationships. CREATE INDEX is for optimizing search performance, not combining data. ALTER TABLE modifies table structure, not fetching related records.

8. CRUD Operation Identification

   In the context of relational databases and ASP.NET Core, which SQL statement performs an update to existing records?

   1. UPDATE
   2. DROP
   3. SELECT
   4. DELETE

   Explanation: The UPDATE statement is used to modify existing records in a table. DELETE removes records instead of updating them. SELECT retrieves records for reading, leaving them unchanged. DROP removes entire tables or database structures, not individual records or fields.

9. Ensuring Unique Values

   Which SQL keyword ensures that the values in a particular column are always unique in a table?

   1. UNIQUE
   2. INDEX
   3. FOREIGN
   4. DEFAULT

   Explanation: The UNIQUE constraint enforces uniqueness for values in a column, ensuring that no duplicate values exist. DEFAULT sets a predefined value when none is provided, but does not guarantee uniqueness. FOREIGN is related to foreign keys, not value constraints. INDEX improves search speed but doesn't enforce unique values by itself.

10. Default Values for Columns

    How do you specify a default value for a column in an SQL table during its creation in a relational database?

    1. Use the PRIMARY keyword next to the value
    2. List the value under the FOREIGN clause
    3. Add the DEFAULT keyword followed by the value in the column definition
    4. Include the UNIQUE keyword after the value

    Explanation: The DEFAULT keyword assigns a predefined value to a column when a new row is inserted without explicitly providing a value for the column. PRIMARY is used for primary keys, not default values. UNIQUE defines uniqueness constraints, not default assignments. FOREIGN refers to keys relating tables rather than default values.