Database Final

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' _set%' %s' _set'

%set'

Which of the following values violates the CHECK constraint below? --- CREATE TABLE Customer ( CustomerId INT AUTO_INCREMENT, Name VARCHAR(60), Age INT CHECK (Age BETWEEN 18 AND 50), ShippingAddress VARCHAR(200), PRIMARY KEY (CustomerId) ); (456, "Sarah Mcgraw", 61, "12301 270th Pl, Seattle, WA 98126") (123, "Sarah Mcgraw", 50, NULL) (999, "Sarah Mcgraw", NULL, "12301 270th Pl, Seattle, WA 98126") (111, NULL, 20, "12301 270th Pl, Seattle, WA 98126")

(456, "Sarah Mcgraw", 61, "12301 270th Pl, Seattle, WA 98126")

In the Reservation table below, a room may be reserved several times, so the RoomNumber column is not unique. To ensure that a room can only be reserved by only one guest for the day, the minimal primary key consists of which columns? (RoomNumber, DateOfStay, Guest) (RoomNumber, DateOfStay) (RoomNumber, Guest, ModeOfPayment) (RoomNumber, ModeOfPayment)

(RoomNumber, DateOfStay)

What is the correct order of operator precedence in SQL (listed from higher to lower precedence)? = NOT * + AND OR NOT * + AND OR = NOT * + = AND OR * + = NOT AND OR

* + = NOT AND OR

Evaluate the SQL statement and data below. What is correct result? --- SELECT SUM(Cost) + SUM(Markup) FROM Profit; 450, 35 250, 35 485 125, 200, 160

485

Assume the lastName value is obtained from a submitted form. Which values should replace XXX and YYY in the PHP code? --- $sql = "INSERT INTO Teacher (LastName) " . "VALUES (XXX)"; $stmt = $pdo->prepare($sql); $stmt->bindValue(YYY, $_POST["lastName"]); $stmt->execute(); $_lastName, $_lastName :lastName , "lastName" :lastName, @lastName :lastName, :lastName

:lastName , "lastName"

When using the MySQL Command-Line Client, which character ends a command? ; ! " :

;

Which statement replaces XXX and YYY in the PHP code to call the stored procedure below? --- CREATE PROCEDURE GetTeacherLoad(IN teachID INT(6), OUT teacherLoadCount INT) ... $stmt = $pdo->prepare("CALL GetTeacherLoad(XXX, YYY)"); $stmt->bindValue(1, 32461); $stmt->execute(); $stmt = $pdo->query("SELECT @teacherLoadCount AS count"); $row = $stmt->fetch(); echo "Teacher Load Count for 32461 is $row[count]"; ? , ? ?, @teacherLoadCount "teacherId", "teacherLoadCount" :teacherID, :teacherLoadCount

?, @teacherLoadCount

Which statement creates a primary key constraint on the ID column of the Employee table? ALTER TABLE Employee PRIMARY KEY (ID); ALTER TABLE Employee ADD PRIMARY KEY (ID); ALTER TABLE Employee CHANGE PRIMARY KEY (ID); ADD PRIMARY KEY (ID) TABLE Employee;

ALTER TABLE Employee ADD PRIMARY KEY (ID);

Which SQL statement adds a new column Fax to the Supplier table? ALTER Supplier ADD Fax VARCHAR(30); ALTER TABLE Supplier (Fax) INSERT VARCHAR(30); ALTER TABLE Supplier ADD Fax VARCHAR(30); ALTER TABLE Supplier INSERT Fax VARCHAR(30);

ALTER TABLE Supplier ADD Fax VARCHAR(30);

A database team plans the creation of a new database. During which phase does the team capture data requirements? Analysis Modeling Normalization Implementation

Analysis

In the following ER diagram, what does 'AlbumTitle' represent? Key Attribute Relationship Entity

Attribute

Refer to the stored procedure. What is the correct call syntax to get the number of classes taught by Maggie Wilson with ID - 45631 through a stored procedure call from the command line? --- CREATE PROCEDURE TeacherClassLoad(IN teachID INT, OUT numberOfClasses INT) SELECT COUNT(*) INTO numberOfClasses FROM Class WHERE TeacherID = teachID; CALL TeacherClassLoad("45631", "numberofClasses") CALL TeacherClassLoad(45631, @numberofClasses) CALL TeacherClassLoad(45631, numberofClasses) CALL TeacherClassLoad("45631", @numberofClasses)

CALL TeacherClassLoad(45631, @numberofClasses)

Which of the following is an example of database programming? Creating tables and columns using SQL in a database administration tool Creating a web application using Java and SQL Creating a database using a database server administration tool Creating a static web page

Creating a web application using Java and SQL

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

DECIMAL

What is the correct statement for deleting a database? DELETE DATABASE DatabaseName; DROP DatabaseName; DROP DATABASE DatabaseName; DATABASE DROP DatabaseName;

DROP DATABASE DatabaseName;

What is the correct statement for deleting a database? DROP DATABASE DatabaseName; DATABASE DROP DatabaseName; DROP DatabaseName; DELETE DATABASE DatabaseName;

DROP DATABASE DatabaseName;

Which language defines statements used for creating and dropping tables? Data Manipulation Language Data Control Language Data Definition Language Data Query Language

Data Definition Language

In a PHP application using PDO, a _____ consists of the host, port, and name of the database that will be used to establish a connection to the database. Data Source Name Data Connection PDO Exception PDO Object

Data Source Name

Which database role focuses on database storage, response times, and optimization? Administrator Designer Programmer User

Designer

Which database role focuses on database storage, response times, and optimization? Designer Administrator User Programmer

Designer

The EmployeeWorkspace table has a composite key of (EmployeeID, WorkspaceID). WorkHours depends on (EmployeeID, WorkspaceID), and EmployeeLastName depends on EmployeeID. Which column must be removed so EmployeeWorkspace is in second normal form? EmployeeID WorkHours EmployeeLastName WorkspaceID

EmployeeLastName

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) ShipPartCode REFERENCES Part FOREIGN KEY (ShipPartCode) REFERENCES Part (PartCode) FOREIGN KEY (ShipPartCode) REFERENCES Part

FOREIGN KEY (ShipPartCode) REFERENCES Part (PartCode)

SQL database system is optimized for big data. True False

False

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

Four 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'); = BETWEEN IN NOT IN

IN

Which is NOT true? NULL value represent missing data or unavailable data INT data type can store positive numbers olny. In table, columns are ordered and rows are unordered BLOB data type can store binary data such as image

INT data type can store positive numbers olny.

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 You Answered FULL JOIN INNER JOIN RIGHT JOIN

LEFT JOIN

Which is NOT true? INT data type can store negative numbers NULL value represents zero or empty string BLOB data type can store binary data such as image In table, columns are ordered and rows are unordered

NULL value represents zero or empty string

Refer to the Product table. Which products are selected by the query below? --- SELECT * FROM Product WHERE Quantity > 5 AND UnitPrice <= 15.00; Onesies set, Pajama set, Shorts set All products except the Romper No products Onesies set, Pajama set

Onesies set, Pajama set, Shorts set

Which principle defines data independence? Tuning query performance requires application modifications. Modification of indexes generates different results. Logical design maintains schema integrity. Physical design never affects query results.

Physical design never affects query results.

Which principle defines data independence? Tuning query performance requires application modifications. Physical design never affects query results. Modification of indexes generates different results. Logical design maintains schema integrity.

Physical design never affects query results.

How does a database system protect data when concurrent transactions occur? By ensuring authorized users only access permissible data. Documenting any lost transactions by always recording results. Preventing multiple transactions with the same data at the same time. Through the reversal of a whole or partial transaction due to a failure.

Preventing multiple transactions with the same data at the same time.

How does a database system protect data when concurrent transactions occur? Preventing multiple transactions with the same data at the same time. Documenting any lost transactions by always recording results. By ensuring authorized users only access permissible data. Through the reversal of a whole or partial transaction due to a failure.

Preventing multiple transactions with the same data at the same time.

Which of the following database programming techniques is an extension of the SQL language that is typically used for database applications? Functional Programming API Procedural SQL Embedded SQL

Procedural SQL

In the SQL code below, which of the following is an identifier? --- UPDATE Product SET UnitPrice = 9.50 WHERE ProductId = 20; Product WHERE SET 9.5

Product

Which statement should replace XXX? --- CREATE FUNCTION GetTeacherID(teacherLastName VARCHAR(20)) RETURNS INT XXX BEGIN DECLARE t_id INT; SELECT TeacherID INTO t_id FROM Teacher WHERE LastName = teacherLastName; END; NO SQL READS SQL DATA SQL DETERMINISTIC

READS SQL DATA

Refer to the Teacher and Class tables. To maintain referential integrity, which foreign key action rejects the deletion of the row containing Rosa Lopez? SET NULL SET DEFAULT RESTRICT CASCADE

RESTRICT

Refer to the Customer table. Which query returns the result table below? SELECT RewardsMember, COUNT(*) ORDER BY RewardsMember FROM Customer; SELECT RewardsMember, COUNT(*) FROM Customer GROUP BY RewardsMember; SELECT RewardsMember, COUNT(*) GROUP BY RewardsMember FROM Customer; SELECT RewardsMember, COUNT(*) FROM Customer ORDER BY RewardsMember;

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; SELECT State, COUNT(*) FROM Customer GROUP BY State HAVING MAX(CustomerId) > 1; SELECT State, COUNT(*) FROM Customer GROUP BY State WHERE SUM(CustomerId) > 1; SELECT State, COUNT(*) FROM Customer GROUP BY State WHERE COUNT(*) > 1;

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

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

SELECT, UPDATE

_____ is an ISO standard that extends procedural SQL for use in stored procedures. SQL/DS SQL/PSM SQL/CI SQL/SAS

SQL/PSM

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; Quantity * 5 MIN(Quantity) AVG(Quantity) SUM(Quantity)

SUM(Quantity)

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? Data dictionary Data indexes Transaction manager Storage manager

Storage manager

The table below tracks grades received by students in courses. Scores of 90 and above receive an A grade, 80 to 89 a B, and so on. --- StudentNumber CourseCode StudentName ScoreNumber LetterGrade 8034 Math 100 Maria Rodriguez 95 A 2111 Math 100 Dana Gebhardt 88 B 9930 Spanish 22 A Ji-Ho Kim 72 C 8034 History 11 Maria Rodriguez 88 B 5091 Biology 200B Bem Kuminga 41 F --- What column(s), if any, must be removed to achieve third normal form? StudentName only StudentName and LetterGrade only StudentName and ScoreNumber only None, the table is in third normal form

StudentName and LetterGrade only

What is the result of a relational operation? Table Column Row Key

Table

What is wrong in the following query? --- SELECT Sales.OrderID, Customer.FirstName, Customer.LastName FROM Sales INNER JOIN Customer ON CustomerID = CustomerID; Nothing is wrong in the query. The columns in the SELECT clause are in the wrong order. The CustomerID prefix is missing. The Customer table does not appear in the FROM clause.

The CustomerID prefix is missing

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 Web Development course is deleted. The TeacherID for Web Development is set to NULL. The TeacherID for Bryan McNeal is set to NULL. The change is rejected.

The TeacherID for Web Development is set to NULL.

Which statement is NOT an advantage of using a view table? Sensitive table data can be hidden from users and programmers. The creation of a new base table is always up to date. Optimal SELECT statements can be saved in a view table. Complex SELECT statements can be saved in a view table.

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

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 INSERT rejects the data with no error. The INSERT accepts the data and displays an error. The item appears in the base table and the view table. The item appears in the base table but not in the view table.

The item appears in the base table but not in the view table.

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 significant digits allowed for the data type. The initial default value for the data type. The number of characters allowed for the data type. The numeric integer value for the data type. The ID for the data type.

The number of characters allowed for the data type.

The Department table was created as follows: --- CREATE TABLE Department ( DepartmentCode SMALLINT UNSIGNED AUTO_INCREMENT, DepartmentName VARCHAR(20) NOT NULL, PRIMARY KEY (DepartmentCode) ); The Department table now contains the following rows: --- DepartmentCode DepartmentName 1 Sales 2 Marketing 3 Development --- What is the result of the following statement? --- INSERT INTO Department (DepartmentName) VALUES ('Shipping'); The row (0, 'Shipping') is inserted. The row (NULL, 'Shipping') is inserted. The row (4, 'Shipping') is inserted. The INSERT statement fails.

The row (4, 'Shipping') is inserted.

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 replaces onlineShop with a new onlineShop database. The statement creates a copy of the database onlineShop as onlineShop_1. The statement produces an error that indicates the database already exists. The statement is ignored.

The statement produces an error that indicates the database already exists.

A database management system reads and writes data in a database, and _____. Only the transaction queries that did not conflict are saved. An error is recorded, and the successful query results are saved. The two successful queries are recorded to storage, and the third query is executed until successful. The successful query results are reversed, and the transaction is canceled.

The successful query results are reversed, and the transaction is canceled.

The SELECT SQL statement does not alter any database data. True False

True

Refer to the Supplier table. Which statement correctly changes Adan Stevens to Maria Stevens? Suppose SupplierID is integer type. --- UPDATE Supplier SET ContactName = 'Maria Stevens' WHERE SupplierID = '5'; UPDATE Supplier SET ContactName = 'Maria Stevens' WHERE SupplierID = 5; UPDATE Supplier (ContactName = 'Maria Stevens' ) WHERE SupplierID = 5; UPDATE Supplier SET 'Alan Stevens' = 'Maria Stevens' WHERE SupplierID = '5';

UPDATE Supplier SET ContactName = 'Maria Stevens' WHERE SupplierID = 5;

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

UnitPrice DESC

Choose the best data types to replace XXX and YYY. ---- CREATE TABLE Product ( ProductId INT, ProductName XXX, UnitPrice YYY ); CHAR(50), INT CHAR(50), FLOAT VARCHAR(50), DECIMAL(8, 2) VARCHAR(50), DOUBLE

VARCHAR(50), DECIMAL(8, 2)

Which two rules apply to primary keys? Values can have duplicates and may not be NULL Values can have duplicates and can be NULL Values must be unique and can be NULL Values must be unique and may not be NULL

Values must be unique and may not be NULL

How are attributes documented in an entity-relationship diagram? With a relationship line. As a branching line from an entity. As a shape connected to an entity. Within an entity rectangle.

Within an entity rectangle.

In the relational model, a column is _____. an unnamed set of values a name and a data type an unnamed tuple of values the result of an SQL statement

a name and a data type

A/An _____ is a rule enforced on a table's data. integrity constraint query cursor

constraint

A database management system reads and writes data in a database, and _____. provides general-purpose languages for accessing data enables user interaction ensures consistency and availability offers users direct data access

ensures consistency and availability

When using the fetch() in a SELECT statement, each call to fetch() returns the next row from the result table. If it reaches the last row, the next returned value is _____. NULL false true LAST_ROW_VALUE

false

Programming languages fall into either _____ or _____ paradigms. imperative, declarative declarative, object-oriented declarative, functional imperative, object-oriented

imperative, declarative

Denormalization eliminates _____ queries, and therefore, query performance is improved. create join select delete

join

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

materialized view, base table

A database administrator uses _____ to eliminate redundancy. This is done by decomposing a table into two or more tables in higher normal form. partitioning referential integrity normalization denormalization

normalization

What should be added to the SQL statements to produce the Result table below? --- USE _____; SHOW _____; DATABASE, CREATE TABLE onlineShop, TABLES onlineshop, TABLE DATABASE, COLUMNS

onlineShop, TABLES

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

primary key

Each course must be offered by at least one department. In the Department-Offers-Course relationship, Department is a(n) _____ entity. optional required plural singular

required

When an entity-relationship model is implemented in SQL, an entity typically becomes a _____. table column primary key foreign key

table

When an entity-relationship model diagram is implemented within SQL, entities typically become _____, and relationships typically become _____. tables, primary keys rows, tables foreign keys, rows tables, foreign keys

tables, foreign keys

Relationship minima are usually specified as _____ or _____. minima, one one, two zero, one one, many

zero, one