CTS 2477

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

When data is produced and stored as numeric, textual, or visual information, it varies in _____.

Format

What does a user that interacts with a database use to read and write data?

Query Language

Which role focuses on creating software that interacts with a database?

Programmer

Which database role focuses on database storage, response times, and optimization?

Designer

Which concept relates to software that reads and writes data in a database?

Management System

A database management system reads and writes data in a database, and ________.

ensures consistency and availability

What design consideration would apply to a database that has special performance requirements?

Structuring data properly on storage media

If a database system is processing three queries as part of a transaction and the third query fails, what happens to the transaction?

The successful query results are reverse and the transaction is canceled

How does a database system protect data when concurrent transactions occur?

preventing multiple transactions with the same data at the same time

In terms of database architecture, which component translates the query processor instructions into low-level file-system commands and is responsible for indexing the data?

Storage manager

Which type of database system is optimized for big data?

NoSQL

When a user interacts with a database, they can use a _____ to modify data with commands.

Query Language

The _______ SQL statement does not alter any database data.

SELECT

When using a SQL statement to create a table, which data type is used to store a fractional value?

DECIMAL

A database administrator uses which two SQL statements to view and modify existing customer balance with a late fee?

SELECT, UPDATE

A user creates a table by using a SQL statement. The data type VARCHAR(11) is part of the statement. What does the value of (11) represent?

The number of characters allowed for the data type

The analysis phase of database design includes which process?

Specifying requirements that are not dependent on a specific database system.

A database ______ is the implementation of database requirements in SQL with CREATE TABLE statements.

schema

Which principle defines data independence?

Physical design never affects query results.

What links a host programming language to a database system?

API

In the following ER diagram, what does 'AlbumTitle' represent?

Attribute

A database designer installs MySQL Communicty Edition to create a database. Which account does the designer use to gain full control of MySQL?

Root

When using the MySQL Workbench GUI, which icon will execute an SQL statement?

Lightning Bolt

When using the MySQL Command-Line Client, which character ends a command line?

;

Which option is found on the Schemas tab in the MySQL Workbench?

A list of available datbaases

A database administrator creates a MySQL statement with incorrect syntax. What does MySQL Workbench display when the statement is executed?

Error Code

In the relational model, an unnamed tuple of values is a ______.

row

What is the result of a relational operation?

Table

In a relational model, a column is _______.

A name and a data type

A ______ is a collection of values with no inherent order.

set

_____ is not a relational algebra operation

Merge

What does SQL stand for?

Structured Query Language

UPDATE, SELECT, and ORDER BY are ____ in an SQL Statement

keyword

In the SQL below, which of the following is an identifier?

UPDATE Product

SET UnitPrice = 9.50

WHERE ProductId = 20;

Product

What language defines statements used for creating and dropping tables?

Data definition language

The statement below is an example from which SQL sublanguage?

SELECT ProductName

FROM Product;

Data Query Language

What is the correct statement for creating a database called reservationDB?

CREATE DATABASE reservationDB;

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.

What is the correct statement for deleting a database?

DROP DATABASE DatabaseName;

What should be added to the SQL statements to produce the Result table below?

USE ______;

SHOW _________;

Result:

Tables_in_onlineShop

blah

blah

blah

blah

onlineShop

TABLES

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

ordered

not ordered

Data independence means

rows of a table have no inherent order

How many columns are created by the SQL statement below?

CREATE TABLE Supplier (

SupplierId INT,

CompanyName VARCHAR(40),

ContactName VARCHAR(50),

City VARCHAR(40),

Country VARCHAR(40),

Phone VARCHAR(30)

);

6

Which SQL statement deletes the table supplier?

DROP TABLE Supplier;

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

ALTER TABLE Supplier

ADD Fax VARCHAR(30);

Which SQL Statement deletes the City column from the Supplier table?

ALTER TABLE Supplier

DROP City;

What data type stores binary values?

BLOB

Which data type should a database use to store negative numbers?

INT

Choose the best data types to replace XXX and YYY.

CREATE TABLE Product (

ProductId INT,

ProductName XXX,

UnitPrice YYY

);

VARCHAR(50)

DECIMAL(8,2)

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

operand

What is the correct order of operator precedence in SQL (high to low)?

* + = NOT AND OR

Which statement selects all rows and just the ProductName and Size columns from the Product table?

SELECT ProductName, Size

FROM Product;

Refer to the product table. Which columns are preset in the query's result table?

SELECT * FROM Product;

all columns are present

Refer to the product table. Which columns are preset in the query's result table?

SELECT ProductName FROM Product WHERE Quantity >= 10;

onesies set, sunsuit, pj set

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

SELECT * FROM Product WHERE Quantity > 5 AND UnitPrice <= 15.00;

onsies set, pj set, shorts set

In mySQL what is the result of TRUE AND NULL?

NULL

what should be added so null values are not allowed in productname?

CREATE TABLE Product ( ProductId INT, ProductName _____, UnitPrice DECIMAL(5,2), SupplierId INT );

VARCHAR(50) NOT NULL

In mySQL, what is the result of TRUE OR NULL

TRUE

a null value represents ____.

missing data