CTS 2433 Exam 1

Chapter's 1, 2 and 3

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)

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

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