CTS2433 Exam 1

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

Structuring data properly on storage media.

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.

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

Management system

What links a host programming language to a database system?

API

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

ensures consistency and availability

The analysis phase of database design includes which process?

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

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

Error code

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

;

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

Programmer

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

SELECT, UPDATE

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

Which principle defines data independence?

Physical design never affects query results.

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

Root

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 reversed, and the transaction is canceled.

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

primary key

A _____ constraint specifies a column value that is used when no value is provided in an INSERT statement.

DEFAULT

Choose the best data types to replace XXX and YYY.

CREATE TABLE Product ( ProductId INT, ProductName XXX, UnitPrice YYY );

VARCHAR(50), DECIMAL(8, 2)

Which statement creates a primary key constraint on the ID column of the Employee table?

ALTER TABLE Employee ADD PRIMARY KEY (ID);

What foreign key action should be added to ensure that if a supplier is removed from the Supplier table, the products associated with the same supplier are also removed?

CREATE TABLE Product ( ProductId INT NOT NULL AUTO_INCREMENT, ProductName VARCHAR(50), UnitPrice DECIMAL(5,2), SupplierId INT, PRIMARY KEY (ProductId), FOREIGN KEY (SupplierId) REFERENCES Supplier(SupplierId) _____ );

ON DELETE CASCADE

In the following statement, the UNIQUE clause is a _____ constraint.

CREATE TABLE Employee ( ID SMALLINT UNSIGNED, FullName VARCHAR(60), Extension CHAR(4), UNIQUE (ID, Extension), PRIMARY KEY (ID) );

table

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.

Which language defines statements used for creating and dropping tables?

Data Definition Language

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

What is the result of a relational operation?

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.

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

NULL

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

A single INSERT statement can only add one row.

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?

CREATE TABLE Shipment ( ShipNumber INT UNSIGNED, ShipPartCode CHAR(3) NOT NULL, Quantity SMALLINT UNSIGNED, PRIMARY KEY (ShipNumber), XXX );

FOREIGN KEY (ShipPartCode) REFERENCES Part (PartCode)

The statement below is an example from which SQL sublanguage?

SELECT ProductName FROM Product;

Data Query Language

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

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

What does the following statement return?

SELECT ROUND(12.439, 1);

12

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

materialized view, base table

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

number of columns and data types

Which statement is NOT an advantage of using a view table?

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

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

(It's asking to find what isn't currently in the Sales table PRODUCTID.)

Romper, Pajama set

What does the following statement return?

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

-5

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

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 SUBSTRING('Excellent', 3, 4);

cell

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

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

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