Explore fundamental relational data concepts

What problem was the relational database model originally designed to solve? a) Multiple arbitrary data structures that were inefficient, hard to maintain, and hard to optimize b) The absence of any standard query language c) Slow network connections between systems d) The lack of support for unstructured data

a

What is described as one of the key advantages of the relational database model? a) It requires no schema b) Its use of tables — an intuitive, efficient, and flexible way to store and access structured information c) It stores data in JSON documents d) It eliminates the need for primary keys

b

In a relational database, an entity can be anything for which you want to record information. Which best describes a table's rows? a) Each row represents an entity type b) Each row represents a column definition c) Each row represents a single instance of an entity d) Each row represents a datatype

c

In a Customer table, a MiddleName column can be empty for rows representing customers with no middle name or whose middle name is unknown. What is this empty value called? a) Blank b) Empty string c) Zero d) NULL

d

What is the purpose of a column's datatype? a) It defines what kind of data the column stores, such as text, decimal, integer, or date/time b) It determines how many rows the table can hold c) It sets the table's primary key d) It defines the table's display order

a

Complete the datatype descriptions: a Price column would likely store ____ numeric data, while a Quantity column might be constrained to ____ numeric values. a) integer; decimal b) decimal; integer c) text; date/time d) character; binary

b

ANSI stands for: a) American National Security Institute b) American Network Standards Interface c) American National Standards Institute d) Association of National Systems Integrators

c

Why are ANSI datatypes significant to relational databases? a) They are required by every database system b) They replace all vendor-specific datatypes c) They eliminate the need for a schema d) They are standard datatypes supported by most database systems

d

A schema design process that minimizes data duplication and enforces data integrity. a) Normalization b) Denormalization c) Indexing d) Partitioning

a

Complete the simple definition of normalization: "Separate each ____ into its own table." a) column b) entity c) datatype d) key

b

Which of the following is one of the simple normalization rules given in the module? a) Combine related entities into a single table to simplify queries b) Store duplicate values so each row is self-contained c) Separate each discrete attribute into its own column d) Avoid primary keys to reduce overhead

c

A key value that uniquely identifies each entity instance (row) in a table. a) Foreign key b) Composite key c) Candidate key d) Primary key

d

A key stored in one table to reference the primary key of a related entity in another table. a) Foreign key b) Primary key c) Composite key d) Alternate key

a

A relational database management system (RDBMS) can enforce referential integrity to ensure what? a) That all tables have the same number of columns b) That a value entered into a foreign key field has an existing corresponding primary key in the related table c) That every column has a NULL value d) That queries always return sorted results

b

A key defined as a primary or foreign key based on a unique combination of multiple columns. a) Alternate key b) Candidate key c) Composite key d) Surrogate key

c

In the module's example, the LineItem table uses a unique combination of which two columns to identify a line item from an individual order? a) CustomerID and ProductID b) OrderNo and CustomerID c) ProductID and Qty d) OrderNo and ItemNo

d

What does SQL stand for? a) Structured Query Language b) Standard Query Logic c) Sequential Query Language d) Systems Query Layer

a

In what year was SQL originally standardized by ANSI? a) 1987 b) 1986 c) 1990 d) 1979

b

In what year was SQL standardized by the International Organization for Standardization (ISO)? a) 1986 b) 1990 c) 1987 d) 1993

c

Which SQL dialect is used by Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure Virtual Machines? a) PL/SQL b) PL/pgSQL c) MySQL d) Transact-SQL (T-SQL)

d

Which SQL dialect is the procedural language dialect implemented in PostgreSQL? a) PL/pgSQL b) T-SQL c) PL/SQL d) ANSI SQL

a

Which SQL dialect is used by Oracle, with its name standing for Procedural Language/SQL? a) T-SQL b) PL/SQL c) PL/pgSQL d) Transact-SQL

b

Why do database management systems have proprietary SQL extensions? a) Because ANSI forbids standard SQL features b) Because extensions are required by law c) Because extensions provide functionality not covered by the SQL standard, such as security management and programmability d) Because all vendors must add different extensions

c

What does DDL stand for, and what is it used for? a) Data Delivery Language; delivering data to applications b) Data Definition Language; creating, modifying, and removing tables and other database objects c) Data Distribution Layer; distributing data across servers d) Direct Database Link; linking databases together

b

Which DDL statement creates a new object in the database, such as a table or a view? a) ALTER b) DROP c) CREATE d) RENAME

c

Which DDL statement modifies the structure of an object, such as adding a new column to a table? a) CREATE b) DROP c) RENAME d) ALTER

d

Which DDL statement removes an object from the database? a) DROP b) DELETE c) ALTER d) REVOKE

a

Which DDL statement renames an existing object? a) ALTER b) RENAME c) CREATE d) UPDATE

b

Why must you be careful with the DROP statement? a) It only removes the object temporarily b) It requires an index c) It permanently removes an object, and when you drop a table, all its rows are lost d) It changes the datatypes of all columns

c

What does DCL stand for, and who generally uses it? a) Data Connection Language; used by developers b) Data Conversion Layer; used by analysts c) Direct Command Line; used by administrators d) Data Control Language; used by database administrators to manage access to objects

d

Which DCL statement grants permission to perform specific actions? a) GRANT b) DENY c) REVOKE d) ALLOW

a

Which DCL statement denies permission to perform specific actions? a) GRANT b) DENY c) REVOKE d) BLOCK

b

Which DCL statement removes a previously granted permission? a) DENY b) DROP c) REVOKE d) DELETE

c

What does DML stand for, and what is it used for? a) Data Modeling Language; designing schemas b) Data Mapping Layer; mapping tables together c) Database Metadata Log; recording changes d) Data Manipulation Language; manipulating the rows in tables

d

Which DML statement reads rows from a table? a) SELECT b) INSERT c) UPDATE d) DELETE

a

Which DML statement inserts new rows into a table? a) UPDATE b) INSERT c) SELECT d) CREATE

b

Which DML statement modifies data in existing rows? a) SELECT b) DELETE c) UPDATE d) ALTER

c

Which DML statement deletes existing rows from a table? a) DROP b) REVOKE c) ALTER d) DELETE

d

By default, the SELECT, UPDATE, and DELETE statements are applied to: a) Every row in a table b) Only the first row c) Only the primary key row d) Only rows with NULL values

a

Which clause do you use to specify criteria so only matching rows are selected, updated, or deleted? a) ORDER BY b) WHERE c) JOIN d) INTO

b

What is the risk of using DELETE or UPDATE without a WHERE clause? a) The statements will fail with an error b) The statements will only affect the first row c) You can lose or modify a lot of data, because the statements affect every row in the table d) The statements will be blocked by the RDBMS

c

Which clause sorts the results of a SELECT statement by a specified column? a) WHERE b) JOIN c) GROUP BY d) ORDER BY

d

Which clause retrieves data from multiple tables by matching a foreign key in one table with its associated primary key in the other? a) JOIN b) WHERE c) INTO d) AS

a

Complete the INSERT syntax: INSERT \_\_\_\_ Product(ID, Name, Price) VALUES (99, 'Drill', 4.99); a) FROM b) INTO c) ON d) TO

b

In Transact-SQL, why is the table name Order enclosed in square brackets [Order] in some queries? a) Because tables must always be bracketed b) Because it references a foreign key c) Because ORDER is a reserved keyword in Transact-SQL d) Because the table is stored in a different schema

c

What does the NOT NULL clause on a column enforce? a) The column is indexed automatically b) The column is the primary key c) The column must have a default value d) The column must always contain a value — it is a mandatory column

d

What is VARCHAR an abbreviation for? a) Variable length character data b) Verified character data c) Virtual character data d) Variable row character data

a

What does the INT datatype store? a) Decimal numbers b) An integer, or whole number c) Text strings d) Date/time values

b

A virtual table based on the results of a SELECT query — a window on specified rows in one or more underlying tables. a) Index b) Stored procedure c) View d) Primary key

c

Complete the code to create a view: CREATE \_\_\_\_ Deliveries AS SELECT ... a) PROCEDURE b) INDEX c) TABLE d) VIEW

d

What does a stored procedure do? a) Defines SQL statements that can be run on command, encapsulating programmatic logic in a database b) Creates a virtual table c) Sorts rows in a table d) Enforces referential integrity

a

Complete the statement that executes a stored procedure: \_\_\_\_ RenameProduct 201, 'Spanner'; a) RUN b) EXEC c) CALL d) INVOKE

b

What is the purpose of an index? a) It encrypts column data b) It defines the primary key c) It helps you search for data in a table d) It enforces referential integrity

c

In the module's analogy, an index over a table is like: a) A map of the database b) A table of contents c) A library card catalog d) An index at the back of a book, containing a sorted set of references with page numbers

d

When you create an index on a column, what does the index contain? a) A copy of the column's data in sorted order, with pointers to the corresponding rows in the table b) A backup of the entire table c) The values of the primary key column only d) A compressed copy of every row

a

For a table containing few rows, what typically happens when a query uses an indexed column? a) The index always speeds up the query b) The query optimizer often ignores the index because reading the entire table is just as efficient c) The query fails because the index is too small d) The index must be rebuilt first

b

What are the costs of creating an index? a) It slows down SELECT queries b) It removes the primary key c) It consumes storage space, and it must be maintained on every insert, update, and delete d) It prevents the table from being joined

c

What must you strike a balance between when deciding whether to create an index? a) The number of columns in the table and the number of rows b) The size of the database and the number of users c) The number of tables and the number of keys d) Having indexes that speed up your queries versus the cost of maintaining them during insert, update, and delete operations

d

In an INSERT statement, what does the INTO clause specify? a) The table and the columns to insert into b) The foreign keys to check c) The type of join to use d) The ordering of the results

a

Besides Microsoft SQL Server, which other database systems are mentioned in the module as common RDBMSs that use SQL? a) MongoDB, Cassandra, and Redis b) MySQL, PostgreSQL, and Oracle c) Cosmos DB, Neo4j, and Redis d) Fabric, Databricks, and Synapse

b

Microsoft Copilot in Azure includes capabilities for Azure SQL Database that let you do what? a) Replace all SQL queries with pre-built templates b) Automatically create indexes without any input c) Ask questions about your database and generate T-SQL queries using natural language d) Delete entire databases with a single click

c

The following are all Data Definition Language (DDL) statements, EXCEPT: a) CREATE b) ALTER c) DROP d) INSERT

d

In a relational table, every row must have a value in every column.

False

Normalization separates each entity into its own table and each discrete attribute into its own column.

True

Referential integrity prevents an order from being created for a customer that does not exist in the Customer table.

True

SQL was originally standardized by ISO in 1986 and by ANSI in 1987.

False

DCL statements are typically used by database administrators to grant, deny, or revoke permissions for specific users or groups.

True

The DROP statement can be reversed by using an UNDO command.

False

By default, an UPDATE statement without a WHERE clause modifies every row in the table.

True

An index always improves query performance, no matter how small the table.

False

A composite key is a primary or foreign key based on a unique combination of multiple columns.

True

Views store their own copy of the data separately from the underlying tables.

False