CH3

Refer to the Product table. How many rows appear in the query's result table?

SELECT DISTINCT Size FROM Product;

4 rows

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

SELECT ProductName, Quantity

FROM Product

WHERE Size _____ ('0-3M', '3-6M', '6-9M');

IN

Refer to the Product table. Complete the SQL statement to select all products sold as a set.

SELECT ProductName, Quantity

FROM Product

WHERE ProductName LIKE _____;

'%set'

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

SELECT *

FROM Product

ORDER BY _____;

UnitPrice DESC

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

SELECT ProductName, Size, Quantity, SupplierId

FROM Product

ORDER BY _____ ;

ProductName, Quantity DESC

What does the following statement return?

SELECT ROUND(12.439, 1);

12.4

What does the following statement return?

SELECT SUBSTRING('Excellent', 3, 4);

'cell'

What does the following statement return?

​​SELECT DATEDIFF('2020-11-04', '2020-11-09');

-5

Refer to the Product table. Complete the SQL statement so the result table shows 63, which is the total quantity of all products.

SELECT _____ FROM Product;

SUM(Quantity)

Refer to the Product table. Complete the SQL statement so the result table shows 23.99.

SELECT _____ FROM Product;

MAX(UnitPrice)

Refer to the Customer table. Which query returns the result table below?

SELECT RewardsMember, COUNT(*)

FROM Customer

GROUP BY RewardsMember;

Refer to the Customer table. Which query returns the result table below?

SELECT State, COUNT(*)

FROM Customer

GROUP BY State

HAVING COUNT(*) > 1;

Evaluate the SQL statement and data below. What is correct result?

SELECT SUM(Cost) + SUM(Markup)

FROM Profit;

485

In the SQL query below, the Teacher table is the _____.

SELECT LastName, FirstName, CourseTitle

FROM Teacher

INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID;

left table

Refer to the tables below. Which join completes the SQL query and produces the result table below?

SELECT CourseTitle, FirstName, LastName FROM Class _____ Teacher ON Class.TeacherID = Teacher.TeacherID;

LEFT JOIN

Refer to the tables below. Which join completes the SQL query and produces the result table below?

SELECT CourseTitle, FirstName, LastName FROM Class _____ Teacher ON Class.TeacherID = Teacher.TeacherID;

RIGHT JOIN

What is wrong in the following query?

SELECT Sales.OrderID, Customer.FirstName, Customer.LastName

FROM Sales

INNER JOIN Customer ON CustomerID = CustomerID;

The CustomerID prefix is missing.

The query below is a/an _____?

SELECT LastName, FirstName, CourseTitle FROM Teacher INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID;

equijoin

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

self-join

The following tables describe parts and companies that supply parts:

CREATE TABLE Part ( PartID SMALLINT, PartName VARCHAR(30), PartSuppierCode CHAR(3), PRIMARY KEY (PartID), FOREIGN KEY (PartSupplierCode) REFERENCES Supplier (SupplierCode) );

CREATE TABLE Supplier ( SupplierCode CHAR(3), SupplierName VARCHAR(30), PostalCode CHAR(5), PRIMARY KEY (SupplierCode) );

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

SELECT PartName, SupplierName FROM Part XXX;

CROSS JOIN Supplier

The following table lists parts. Many parts are contained in another part, called an assembly, and tracked in the AssemblyID column. If a part is not contained in another part, the AssemblyID is NULL.

CREATE TABLE Part ( PartID SMALLINT, PartName VARCHAR(30), AssemblyID SMALLINT, PRIMARY KEY (PartID), FOREIGN KEY (AssemblyID) REFERENCES Part(PartID) );

The following self-join selects one row for each part. Each row contains the names of the part and the assembly, if any, containing the part. What XXX completes the query?

SELECT P.PartName AS Part, A.PartName AS Assembly FROM Part P XXX;

LEFT JOIN Part A ON P.AssemblyID = A.PartID

Refer to the tables. Which products are selected by the query below?

SELECT ProductName FROM Product WHERE SupplierID IN (SELECT SupplierID FROM Supplier WHERE CountryID = 2);

Onesies set, Sunsuit, Romper

Refer to the Product table. Which products are selected by the query below?

SELECT ProductName FROM Product WHERE Quantity > (SELECT MIN(Quantity) FROM Product);

Onesies set, Sunsuit, Pajama set, Shorts set

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

correlated subquery

Refer to the tables. The Product's Quantity column stores the stockroom's product quantity before any products are sold. Which products are selected by the query below?

SELECT ProductName FROM Product P WHERE Quantity > (SELECT SUM(Quantity) FROM Sales WHERE ProductID = P.ProductID);

all products that are in stock

Refer to the tables. Which products are returned by the query below?

SELECT ProductName FROM Product P WHERE NOT EXISTS (SELECT * FROM Sales WHERE ProductID = P.ProductID);

Romper, Pajama set

Refer to the tables and the nested query below. Which flattened query generates the same result as the nested query?

SELECT E.Name FROM Employee E WHERE EXISTS (SELECT * FROM Family F WHERE F.ID = E.ID AND Relationship = 'Daughter');

SELECT DISTINCT E.Name

FROM Employee E

INNER JOIN Family F ON F.ID = E.ID

WHERE Relationship = 'Daughter';

Refer to the tables. Which query produces the result table below?

SELECT S.OrderId, P.ProductName, P.UnitPrice * S.Quantity AS SubTotal FROM Sales S INNER JOIN Product P ON S.ProductId = P.ProductId;

Refer to the tables. Which query produces the result table below?

SELECT S.OrderId, C.CustomerName FROM Sales S INNER JOIN Customer C ON S.CustomerId = C.CustomerId;

Refer to the tables. What should be added to the SELECT caluse to produce the result table below?

SELECT _____ AS TotalSales FROM Sales S INNER JOIN Product P ON S.ProductId = P.ProductId;

SUM(P.UnitPrice * S.Quantity)

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

When a database stores view data, it uses ______ that depends on data in a corresponding ______.

materialized view

base table

which statement is an advantage of using a view table?

FALSE: The creation of a new base table is always up to date.

Complex SELECT statements can be saved in a view table.

Sensitive table data can be hidden from users and programmers.

Optimal SELECT statements can be saved in a view table.

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 (gets rejected)

which relational algebra operation is denoted by the green letter ρ?

rename

which relational expression is equivalent to the following SQL statement?

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

What type of join is executed by the following relational algebra expression?

Department⋈Department.Manager=Employee.IDEmployee

Inner

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

number of columns and data types

Refer to the following tables:

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

Employee∩Student

0

Refer to the Employee table:

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

σ(Salary > 50000)(ρ(Department,Salary)(DepartmentγSUM(Salary)(Employee)))

2