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))