Explore fundamental relational data concepts

An organization has multiple applications, each storing data in its own unique structure, making the data inefficient, hard to maintain, and hard to optimize. Which data model was designed to solve this problem? a) Relational database model b) Document database model c) Key-value database model

a

Which feature of the relational database model is described as an intuitive, efficient, and flexible way to store and access structured information? a) JSON documents b) Tables c) Graph structures

b

A retail company wants to track its customers, products, orders, and line items. In a relational database, what does each row in the customer table represent? a) A collection of customer attributes b) A relationship between customers c) A single instance of a customer entity

c

A Customer table includes a MiddleName column that can be empty for customers with no middle name or whose middle name is unknown. What term describes this empty value? a) NULL b) Blank c) Zero

a

A Product table has a Price column that stores decimal numeric data rather than text. What determines the kind of data a column stores? a) The number of rows in the table b) The column's datatype c) The table's primary key

b

A database designer wants to use standard datatypes that are supported by most database systems. Which organization defines these standard datatypes? a) IEEE b) ISO c) American National Standards Institute (ANSI)

c

A company's sales spreadsheet duplicates customer and product details for each line item sold. What schema design process should be applied to minimize this data duplication? a) Normalization b) Denormalization c) Indexing

a

Which of the following is a simple normalization rule? a) Combine multiple entities into a single table b) Separate each discrete attribute into its own column c) Store duplicate values to make each row self-contained

b

A database designer needs to uniquely identify each row in a Customer table. What should be used? a) A foreign key b) A composite key c) A primary key

c

An Order table needs to reference the customer who placed the order. What should be stored in the Order table? a) The customer's primary key as a foreign key b) A copy of the customer's address c) The customer's name as a text value

a

A relational database management system prevents an order from being created for a customer that does not exist in the Customer table. What is this enforcement called? a) Normalization b) Referential integrity c) Indexing

b

A LineItem table uses a unique combination of OrderNo and ItemNo to identify each line item. What type of key is this? a) Primary key b) Foreign key c) Composite key

c

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

a

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

b

Which SQL dialect is used by Microsoft SQL Server and Azure SQL Database? a) PL/SQL b) PL/pgSQL c) Transact-SQL (T-SQL)

c

Which SQL dialect is used by Oracle and stands for Procedural Language/SQL? a) PL/SQL b) T-SQL c) PL/pgSQL

a

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

b

A database administrator needs to create a new table in the database. Which category of SQL statement should be used? a) DML b) DCL c) DDL

c

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

a

A database administrator wants to permanently remove a table and all its rows from the database. Which statement should be used? a) DELETE b) DROP c) RENAME

b

Which DCL statement grants a user permission to read and modify data in a table? a) DENY b) REVOKE c) GRANT

c

Which DML statement is used to retrieve rows from a table? a) SELECT b) INSERT c) UPDATE

a

By default, an UPDATE statement without a WHERE clause will: a) Modify only the first row b) Modify every row in the table c) Fail with an error

b

Which clause is used to specify criteria so that only matching rows are selected, updated, or deleted? a) ORDER BY b) JOIN c) WHERE

c

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

a

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

b

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

c

A column defined with the NOT NULL clause: a) Must always contain a value b) Becomes the primary key automatically c) Has a default value assigned

a

What does VARCHAR stand for? a) Verified character data b) Variable length character data c) Virtual character data

b

What does the INT datatype store? a) Decimal numbers b) Text strings c) An integer or whole number

c

A view in a relational database is best described as: a) A virtual table based on a SELECT query b) A physical copy of table data c) A sorted index of table rows

a

Which SQL statement is used to execute a stored procedure? a) RUN b) EXEC c) CALL

b

An index over a table is most similar to: a) A table of contents b) A library card catalog c) An index at the back of a book

c

What does a database index contain? a) A copy of the indexed column's data in sorted order with pointers to the corresponding rows b) A backup of the entire table c) Only the primary key values

a

For a table with few rows, what typically happens when a query uses an indexed column? a) The index always speeds up the query b) The query optimizer may ignore the index because reading the entire table is just as efficient c) The query fails because the index is too small

b

What is a cost of creating an index on a table? a) It slows down SELECT queries b) It prevents the table from being joined c) It consumes storage and must be maintained on every insert, update, and delete

c

Which of the following is NOT a DDL statement? a) INSERT b) CREATE c) ALTER

a

A stored procedure is used to: a) Create a virtual table b) Encapsulate programmatic logic in a database c) Speed up search operations on a column

b

Which Azure capability can generate T-SQL queries using natural language? a) Azure Stream Analytics b) Azure Data Explorer c) Microsoft Copilot in Azure

c

Which of the following is a common RDBMS that uses SQL? a) PostgreSQL b) MongoDB c) Redis

a

Ensuring that product prices are stored as decimal values and line item quantities as integers is a benefit of: a) Removing data duplication b) Constraining values to appropriate data types c) Creating foreign key relationships

b

In a relational database, an entity can be: a) Only a physical object b) Only a business transaction c) Anything for which you want to record information

c

A database administrator needs to rename an existing table. Which DDL statement should be used? a) RENAME b) ALTER c) CREATE

a

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

b

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

c

A view provides a way to: a) Query data as a virtual table based on a SELECT query b) Permanently store data separately from the underlying tables c) Speed up insert operations

a

Which SQL statement type is used to manipulate the rows in tables, including retrieving, inserting, modifying, and deleting data? a) DDL b) DML c) DCL

b

A company wants to improve query performance on a large Product table when searching by product name. What should be created? a) A view on the Product table b) A stored procedure c) An index on the Name column

c

In the normalization example, changing a customer's address requires modifying: a) A single row in the Customer table b) Every row that references that customer c) The Order table only

a

Decomposition of attributes into individual columns during normalization provides: a) Removal of primary keys b) Granularity for querying, such as filtering customers by city c) Elimination of foreign keys

b

The relational model is used by organizations of all types and sizes for: a) Only large enterprises b) Only transactional systems c) A broad variety of information management needs

c

Proprietary SQL extensions provide functionality NOT covered by the SQL standard, including: a) Security management and programmability b) Data definition and data manipulation c) Standard query operations

a

Which statement is true about the basic form of an INSERT statement? a) It inserts multiple rows at once b) It inserts one row at a time c) It cannot specify column names

b

The SELECT * statement retrieves: a) Only the primary key column b) Only the first row c) All columns from the table

c

In the CREATE TABLE example from the module, the Price column is defined as: a) DECIMAL NULL b) INT NOT NULL c) VARCHAR(20)

a

A join condition typically matches: a) Two primary keys from the same table b) A foreign key from one table and its associated primary key in another table c) Two foreign keys from different tables

b

The GRANT statement in the module permits user1 to: a) Create new tables b) Delete the Product table c) Read, insert, and modify data in the Product table

c

When a query returns many rows, they: a) Don't necessarily appear in any specific sequence b) Always appear in primary key order c) Always appear in alphabetical order

a

The UPDATE example in the module changes the Address column for rows that have: a) Any ID value b) The value 1 in the ID column c) NULL in the ID column

b

The DELETE example in the module removes rows from the Product table where: a) ID = 1 b) ID = 99 c) ID = 162

c

The stored procedure example in the module is named: a) RenameProduct b) UpdateProduct c) ChangeProduct

a

The index example in the module creates an index named: a) idx_ProductPrice b) idx_ProductName c) idx_ProductID

b

The index creates what type of structure that the query optimizer can use? a) A hash table b) A linked list c) A tree-based structure

c

How many indexes can you create on a table? a) Many b) Only one c) A maximum of two

a

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

b

The SQL code examples in the module are based on which dialect? a) PL/SQL b) PL/pgSQL c) Transact-SQL

c

Which of the following is mentioned as a common RDBMS that uses SQL? a) MySQL b) MongoDB c) Cassandra

a

Azure SQL Managed Instance uses which SQL dialect? a) PL/SQL b) Transact-SQL c) PL/pgSQL

b

T-SQL includes proprietary extensions for: a) Only basic queries b) Only data definition c) Writing stored procedures and triggers, and managing user accounts

c

A trigger is described in the module as: a) Application code that can be stored in the database b) A type of index c) A virtual table

a

The relational database model was designed to solve the problem of multiple arbitrary data structures.

True

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

False

ANSI standard datatypes are supported by most database systems.

True

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

True

Referential integrity prevents orders from being created for customers that do not exist in the Customer table.

True

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

False

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

False

By default, a DELETE statement without a WHERE clause removes every row from the table.

True

An index always improves query performance, regardless of table size.

False

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

True

A view stores its own copy of the data separately from the underlying tables.

False

A stored procedure can accept parameters to create a flexible solution for common actions.

True

Indexes consume storage space and must be maintained on insert, update, and delete operations.

True

The ORDER BY clause is used to sort the results of a SELECT statement.

True

T-SQL is the SQL dialect used by PostgreSQL.

False

Microsoft Copilot in Azure can generate T-SQL queries using natural language.

True

A primary key uniquely identifies each entity instance in a table.

True

A foreign key is stored in one table to reference the primary key of a related entity in another table.

True

The basic form of an INSERT statement can insert multiple rows at a time.

False

The SELECT statement without a WHERE clause returns all rows from the table.

True