CH3

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'

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

What does the following statement return? SELECT ROUND(12.439, 1);

12.4

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

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 type of join is executed by the following relational algebra expression? Department⋈Department.Manager=Employee.IDEmployee

Inner

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

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

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

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

number of columns and data types

which relational expression is equivalent to the following SQL statement?

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

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

Refer to the Product table. How many rows appear in the query's result table? SELECT DISTINCT Size FROM Product;

4 rows

Evaluate the SQL statement and data below. What is correct result? SELECT SUM(Cost) + SUM(Markup) FROM Profit;

485

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

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

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 Product table. Complete the SQL statement so the result table shows 23.99. SELECT _____ FROM Product;

MAX(UnitPrice)

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

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 Customer table. Which query returns the result table below?

SELECT RewardsMember, COUNT(*) FROM Customer GROUP BY RewardsMember;

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. 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 Customer table. Which query returns the result table below?

SELECT State, COUNT(*) FROM Customer GROUP BY State HAVING COUNT(*) > 1;

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)

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)

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.

Refer to the tables. Complete the ORDER BY clause to produce the result table below. SELECT * FROM Product ORDER BY _____;

UnitPrice DESC

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

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

correlated subquery

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

equijoin

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

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

materialized view base table

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

rename

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