Relational Data Concepts, Normalization & SQL

In a relational database, what does a row in a table represent?

• AA datatype definition that every column in the table is required to share
• BA named collection of related tables
• CA single instance of the entity that the table models✓
• DA foreign key relationship that links two entirely separate tables together

Answer: Each row in a relational table represents one instance of the entity the table models, such as one customer in a Customer table. A datatype definition and a foreign key relationship describe structure rather than a specific instance, and a collection of tables is a database, not a row.

A Customer table includes a MiddleName column that is empty for some customers. What does this represent?

• AA DROP statement that permanently removed the MiddleName column from the table definition
• BA NULL value, meaning no data was recorded for that column in that row✓
• CA composite key formed from two related columns
• DA foreign key pointing to a missing Customer row

Answer: An empty column value in a row is described as NULL, which simply means no data has been recorded there. A DROP statement removes an entire object rather than leaving a blank value, and neither a composite key nor a foreign key relates to a single missing value in an existing row.

What is the main purpose of normalization when designing a relational database schema?

• ATo increase the number of columns stored in a single table
• BTo combine multiple entities into one large table
• CTo remove the need for primary keys in a database
• DTo minimise data duplication and enforce data integrity✓

Answer: Normalization is a schema design process that minimises data duplication and enforces data integrity. It does the opposite of merging entities into one large table, and primary keys remain essential to uniquely identify rows after normalization.

According to the basic normalization rules, what should be done with each discrete attribute of an entity?

• AIt should be stored in a separate database entirely
• BIt should be removed unless it is used as a primary key
• CIt should be separated into its own column✓
• DIt should be combined with related attributes in a single cell

Answer: The basic rules of normalization state that each discrete attribute should be separated into its own column, rather than combined with other attributes in the same cell. Attributes don't need a whole separate database, and non-key attributes are still kept, just organised into columns.

What is a primary key used for in a relational table?

• AUniquely identifying each instance of an entity, or row, in the table✓
• BDefining the specific datatype that must be used by every column in the table
• CGranting permission for a user to modify the table
• DSorting the rows returned by a SELECT query into a specific column order

Answer: A primary key is an ID or other value used to uniquely identify each row, or instance of an entity, in a table. Sorting results is the job of an ORDER BY clause, granting permissions is handled by DCL statements, and a primary key does not define the datatype of every column.

A LineItem table uniquely identifies each line item using a combination of the OrderNo and ItemNo columns together. What is this type of key called?

• AA foreign key
• BAn index
• CA composite key✓
• DAn elastic pool

Answer: A key based on a unique combination of multiple columns, such as OrderNo and ItemNo together, is known as a composite key. A foreign key instead links a row to a related table, an elastic pool shares database resources, and an index speeds up searches rather than identifying rows.

What does SQL stand for, and what is it used for?

• AStandard Quality Layer, a compliance framework for database auditing
• BSequential Query Logic, a proprietary scripting language used to automate Azure virtual machines
• CStructured Query Language, the standard language for communicating with a relational database✓
• DServer Query Link, a network protocol used specifically for connecting to Azure storage accounts

Answer: SQL stands for Structured Query Language and is the standard language used to communicate with a relational database management system, including retrieving and updating data. It is not a scripting language for virtual machines, a compliance framework, or a network protocol for storage.

Which SQL dialect is used by Microsoft SQL Server, Azure SQL Database and Azure SQL Managed Instance?

• APL/SQL
• BDDL
• CpgSQL
• DTransact-SQL (T-SQL)✓

Answer: Transact-SQL, or T-SQL, is the dialect used by Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance and SQL Server on Azure Virtual Machines. pgSQL is the dialect implemented by PostgreSQL, PL/SQL is Oracle's Procedural Language/SQL, and DDL is a category of statements rather than a dialect.

A team is querying an Oracle database and needs to write a stored procedure using Oracle's own SQL dialect. Which dialect should they use?

• ADML
• BpgSQL
• CT-SQL
• DPL/SQL✓

Answer: PL/SQL, which stands for Procedural Language/SQL, is the dialect used by Oracle. T-SQL is used by Microsoft SQL Server and Azure SQL services, pgSQL is the dialect used by PostgreSQL, and DML is a category of statements rather than a vendor dialect.

Which group of SQL statements is used to create, modify and remove objects such as tables in a database?

• AProcedural Language/SQL (PL/SQL) statements
• BData Manipulation Language (DML) statements
• CData Definition Language (DDL) statements✓
• DData Control Language (DCL) statements

Answer: Data Definition Language, or DDL, statements such as CREATE, ALTER, DROP and RENAME are used to create, modify and remove tables and other database objects. DCL statements manage permissions, DML statements manipulate the rows within existing tables, and PL/SQL is Oracle's own SQL dialect rather than a statement group.

A database administrator wants to grant a user permission to read and insert data into a table, without giving them the ability to delete data. Which type of SQL statement should they use?

• AA Data Control Language (DCL) statement, such as GRANT✓
• BA Data Manipulation Language (DML) statement, such as SELECT
• CA view definition, such as CREATE VIEW
• DA Data Definition Language (DDL) statement, such as CREATE

Answer: Data Control Language, or DCL, statements such as GRANT, DENY and REVOKE are used to manage permissions to perform specific actions on database objects. DDL statements instead create or remove objects, DML statements manipulate rows in tables a user already has permission to access, and a view simply presents query results rather than controlling access.

Which SQL statement is used to remove existing rows from a table?

• AREVOKE
• BDROP
• CUPDATE
• DDELETE✓

Answer: The DELETE statement removes existing rows from a table, typically limited to matching rows through a WHERE clause. DROP instead removes an entire object such as a table, UPDATE modifies the values in existing rows rather than removing them, and REVOKE removes a previously granted permission.

Two tables, Order and Customer, need to be combined in a single query so that each order row also shows its customer's address. Which SQL clause achieves this?

• AA JOIN clause that matches a foreign key to its related primary key✓
• BA WHERE clause that filters rows using criteria based on the OrderDate column value
• CAn ORDER BY clause that sorts rows by CustomerName
• DA GRANT statement that gives access to both tables

Answer: A JOIN clause retrieves data from multiple tables by matching a foreign key in one table, such as a Customer reference on an Order, with its associated primary key in the other table. A WHERE clause filters rows rather than combining tables, ORDER BY only sorts results, and a GRANT statement manages permissions rather than combining data.

What is a view in a relational database?

• AA stored procedure that automatically updates rows according to a fixed schedule
• BA virtual table based on the results of a SELECT query✓
• CAn index that speeds up searches on a single column
• DA physical copy of a table that is duplicated and stored on a separate server

Answer: A view is a virtual table based on the results of a SELECT query, letting several underlying tables be treated as a single queryable object. It is not a physical copy of data on another server, a scheduled stored procedure, or an index used to speed up lookups.

A business wants to encapsulate the SQL statements needed to rename a product, so that applications can run this logic on command with parameters. Which database object should they create?

• AA composite key
• BA view
• CA stored procedure✓
• DAn index

Answer: A stored procedure defines SQL statements that can be run on command, often with parameters, encapsulating programmatic logic such as renaming a product based on its ID. A view instead presents the results of a SELECT query, an index speeds up searching a table, and a composite key uniquely identifies a row using multiple columns.

Why might a database administrator create an index on the Name column of a large Product table?

• ATo grant other users permission to query and modify the Product table without restriction
• BTo let the database engine find rows matching a Name value more quickly than scanning the whole table✓
• CTo permanently remove duplicate rows from the Product table
• DTo automatically translate every query written against the table into a completely different SQL dialect

Answer: An index stores a sorted copy of a column's data with pointers to the corresponding rows, so the database engine can find matching rows faster than scanning the entire table, which matters most on large tables. An index doesn't translate SQL dialects, grant permissions, or remove duplicate rows, since those are handled by other mechanisms entirely.