FINAL

What is the correct order of operator precedence in SQL (listed from higher to lower precedence)?

* + = NOT AND OR

What does the following statement return? ​​SELECT DATEDIFF('2020-11-04', '2020-11-09');

-5

How many rows are in the table defined by the following relational algebra expression? Employee∩Student

0

How many rows are in the table defined by the following relational algebra expression? σ(Salary > 50000)(ρ(Department,Salary)( DepartmentγSUM(Salary)(Employee)))

2

ow many columns are created by the SQL statement below? CREATE TABLE Supplier (

6

A date dimension table tracks data for two years. How many rows does the table contain?

730

A view table provides which benefit when created in a database with multiple tables?

A consolidated view of specific data without changing the underlying database structure

Which of the following is not true about the INSERT statement?

A single INSERT statement can only add one row.

Which SQL statement adds a new column Fax to the Supplier table?

ALTER TABLE Supplier ADD Fax VARCHAR(30);

Refer to the Product table. Which columns are present in the query's result table?

All columns are present

Refer to the Teacher and Class tables. Which foreign key action updates the TeacherID for the Web Development course to 45672 when Bryan McNeal's TeacherID is updated to 45672?

CASCADE

The following query selects all possible combinations of part names and supplier names. What XXX completes the query?

CROSS JOIN Supplier

Which wide-column database has no single point of failure and is always available?

Cassandra

An organization has a main office and three satellite locations. Data specific to each location is stored locally in which configuration?

Distributed

Which NoSQL database uses tables that consist of a key and a group of items?

DynamoDV

ShipPartCode is a foreign key in the Shipment table. ShipPartCode refers to the primary key PartCode of the Part table. What replaces XXX in the following statement?

FOREIGN KEY (ShipPartCode) REFERENCES Part (PartCode)

Refer to the Product table. Complete the SQL statement to select all products with sizes 0-3M, 3-6M, and 6-9M.

IN

Which information would be acceptable in a key-value database?

Key = car make, value = Ford

What occurs when a distributed database experiences a network error and nodes cannot communicate?

Network partition

In the SQL code below, which of the following is an identifier? UPDATE Product SET UnitPrice = 9.50 WHERE ProductId = 20;

Product

Complete the ORDER BY clause to sort the Products by ProductName alphabetically, then in decreasing Quantity.

ProductName, Quantity DESC

Refer to the Product table. Complete the SQL statement to select all products with sizes 0-3M, 3-6M, and 6-9M.

RESTRICT

Refer to the Teacher and Class tables. To maintain referential integrity, which foreign key action rejects the deletion of the row containing Rosa Lopez?

RESTRICT

Refer to the Teacher and Class tables. When Bryan McNeal is deleted from the Teacher table, the TeacherID for the Web Development course automatically changes to 11234. Which action has been specified for the TeacherID foreign key?

SET DEFAULT

A database administrator wants to correlate pairs of rows from the same table. Which type of join should the database administrator use?

Self-join

What does SQL stand for?

Structured Query Language

A database designer wants to create three tables: Supplier, Product, and Country. The Supplier table has a CountryID column with values that must appear in the Country table's CountryID column. The Product table has a ProductID column. Which table's CREATE TABLE statement(s) must specify a FOREIGN KEY?

Supplier

In MySQL, what is the result of TRUE OR NULL?

TRUE

Refer to the Teacher and Class tables. The action ON UPDATE SET NULL is specified for the TeacherID foreign key. What is the result when Bryan McNeal's TeacherID is changed to 45672?

The TeacherID for Web Development is set to NULL.

What causes the generation of a NULL primary key value when using a view in an INSERT statement?

The base table primary key is not included in the view table.

An Inventory view table is defined with a WHERE clause that specifies ShelfNumber = 25. An INSERT adds a new item to ShelfNumber 20. What happens when the WITH CHECK OPTION is not specified in the CREATE VIEW statement?

The item appears in the base table but not in the view table.

A database system has a database called onlineShop. What is the result of a CREATE statement that tries to create onlineShop a second time?

The statement produces an error that indicates the database already exists.

Refer to the tables. Complete the ORDER BY clause to produce the result table below.

UnitPrice DESC

What should be added so NULL values are not allowed in ProductName?

VARCHAR(50) NOT NULL

Choose the best data types to replace XXX and YYY.

VARCHAR(50), DECIMAL(8, 2)

Which two rules apply to primary keys?

Values must be unique and can be NULL

What is a concern with using an in-memory database?

Volatility

Within each row of a wide column database, all columns of a family are stored ____ to optimize performance of read queries.

contiguously

A/An _____ is a subquery that contains a reference to a table column that also appears in the outer query.

correlated

For reporting purposes, an organization establishes a(n) _____, which is separate from the operational database.

data warehouse

A NoSQL database that represents data using XML or JSON is called a _____ database.

document

Data independence means _____.

each cell contains exactly one value

A retailer is using a cloud-based transaction database and is experiencing sales fluctuations through the year. The ability for cloud resources to adapt to such change in utilization is called _____.

elasticity

The query below is a/an _____? SELECT LastName, FirstName, CourseTitle FROM Teacher INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID;

equijoin

Data warehouses should be refreshed periodically. To start, all data should be _____.

extracted to a temporary database

A primary key of a _____ is the composite of the foreign keys that reference a _____.

fact table, dimension table

A database admin configures replication for a distributed database. Which technique applies updates to any available node?

group replication

Operational data changes _____, while analytic data is updated _____.

in real time, as fixed intervals

A hierarchy in a dimension table contains a series of _____ where each has a _____ relationship to the next.

indexes, one-many

A NoSQL database that represents data as a key with multiple values is called a _____ database.

key-value

In the relational algebra, compatible tables have the same _____.

number of columns and column names

What should be added to the SQL statements to produce the Result table below? USE _____; SHOW _____;

onlineShop, TABLES

A value that is used in a computation is known as a/an _____.

operand

In a table, columns are _____ and rows are _____..

ordered not ordered

A column, or group of columns, that serves as the unique identifier in a relational database table is called a/an _____.

primary key

In a document database, a ________ assigns documents to shards.

range function

An operational database contains tables in third normal form. This design reduces ____

redundancy

Due to infrequent updates, many distributed databases use a _____ catalog.

replicated

What causes the generation of a NULL primary key value when using a view in an INSERT statement?

the base table primary key is not included in the view table

Strict consistency is enforced in some document databases by directing read queries to _____.Strict consistency is enforced in some document databases by directing read queries to _____.

the primary replica

To fulfill data processing requirements in a growing transactional database, _____ scales up by increasing processing power of a limited number of systems.

vertical scaling

A _____ database stores related data as column families

wide column

A _____ database stores related data as column families.

wide column

In a primary/secondary replica update for a distributed database, secondary nodes are updated _____.

with independent local transactions

Which relational algebra expression is equivalent to the following SQL statement? SELECT PassengerName FROM Booking WHERE TicketPrice < 1000;

Π(PassengerName)(σ(TicketPrice<1000)(Booking))