IST 210 Final Multiple Choice

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

Attribute

Which suppliers remain in the Supplier table after the DELETE statement executes? DELETE FROM Supplier WHERE CountryId <= 2;

Ling Ming Co.

Refer to the Product table. Complete the SQL statement so the result table shows 23.99. (table 1) SELECT _____ FROM Product;

MAX(UnitPrice)

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

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 analysis phase of database design includes which process?

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

Which column is best to replace XXX in the SQL statement below? CREATE TABLE Supplier (SupplierId INT NOT NULL AUTO_INCREMENT, CompanyName VARCHAR(40), ContactName VARCHAR(50), Phone VARCHAR(30), PRIMARY KEY (XXX));

SupplierId

What is the result of a relational operation?

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 characters allowed for the data type.The analysis phase of database design includes which process?

Refer to the Supplier table. Which statement correctly changes Adan Stevens to Maria Stevens? (table 6)

UPDATE SupplierSET ContactName = 'Maria Stevens'WHERE SupplierId = 5;

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

UnitPrice DESC

What should be added so NULL values are not allowed in ProductName? CREATE TABLE Product (ProductId INT AUTO_INCREMENT, ProductName _____,UnitPrice DECIMAL(5,2), SupplierId INT, PRIMARY KEY (ProductId));

VARCHAR(50) NOT NULL

Which two rules apply to primary keys?

Values must be unique and may not be NULL

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? (table 13) SELECT ProductName FROM Product P WHERE Quantity > (SELECT SUM(Quantity) FROM Sales WHERE ProductId = P.ProductId);

all products that are in stock

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

analysis

A _____ is a collection of values that are of the same type.

column

A/An _____ is a rule enforced on a table's data.

constraint

During database design, an ER _____ and _____ are usually developed in parallel.

diagram, glossary

UPDATE, SELECT, and ORDER BY are _____ in an SQL statement.

keywords

Refer to the tables below. Which join completes the SQL query and produces the result table below? (table 9) SELECT CourseTitle, FirstName, LastName FROM Class_____ Teacher ON Class.TeacherID = Teacher.TeacherID;

left join

In the SQL query below, the Teacher table is the _____. SELECT LastName, FirstName, CourseTitleFROM Teacher INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID;

left table

A database designer interviews a sales team that will be using a new database. During the interview, the designer documents entities as _____, and relationships as _____.

nouns, verbs

SELECT ProductName FROM Product WHERE Quantity >= 10; (table 1)

onesies set, Sunsuit, Pajama set

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

primary key

A relational database uses _____ to structure all data.

tables

When an entity-relationship model diagram is implemented within SQL, entities typically become _____, and relationships typically become _____.

tables, foreign keys

What values for CountryId do the suppliers Sugarplum and Periwinkle have after the UPDATE statement executes? (table 7) UPDATE Supplier SET CountryId = 2 WHERE CountryId IS NULL;

2,2

How many columns are created by the SQL statement below? CREATE TABLE Supplier (SupplierId INT NOT NULL AUTO_INCREMENT, CompanyName VARCHAR(40), ContactName VARCHAR(50), City VARCHAR(40), Country VARCHAR(40), Phone VARCHAR(30)

6

What is the correct statement for deleting a database

<code>DROP DATABASE <databaseName>;</code>

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.

Which of the following is not true about the INSERT statement? A single INSERT statement can only add one row. The INSERT statement is used to add new values into the table. VALUES order must match the column order in the INTO clause. The column names can be omitted in an INSERT statement.

A single INSERT statement can only add one row.

Refer to the Product table. Which columns are present in the query's result table? (table 1) SELECT * FROM Product;

All Columns are present

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)); A. (111, NULL, 20, "12301 270th Pl, Seattle, WA 98126") B. (123, "Sarah Mcgraw", 50, NULL) C. (456, "Sarah Mcgraw", 61, "12301 270th Pl, Seattle, WA 98126") D. (999, "Sarah Mcgraw", NULL, "12301 270th Pl, Seattle, WA 98126")

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

Which language defines statements used for creating and dropping tables?

Data Definition Language

Which type of join does the SQL query below? SELECT LastName, FirstName, CourseTitleFROM Teacher INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID;

Equijoin

Refer to the column information produced by SHOW COLUMNS FROM Supplier; statement. CountryId is a foreign key that references the CountryId column in the Country table. Which statement correctly inserts Oshkosh Bgosh? (table 5)

INSERT INTO Supplier (CompanyName, ContactName, CountryId) VALUES ('Oshkosh Bgosh', 'Martina Perry', 2);

Refer to the Product table. Which products are selected by the query below? (table 12) 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? (tbale 11) SELECT ProductName FROM Product WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE CountryId = 2);

Onesies set, Sunsuit, Romper

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

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

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

Product

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

Refer to the tables below. Which join completes the SQL query and produces the result table below?(table 10) 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? (table 14) SELECT ProductName FROM Product P WHERE NOT EXISTS (SELECT * FROM Sales WHERE ProductId = P.ProductId);

Romper, Pajama set

The _____ SQL statement does not alter any database data.

SELECT

Refer to the Customer table. Which query returns the result table below? (Table 3)

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

Refer to the Customer table. Which query returns the result table below? (table 4)

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

Refer to the Product table. Complete the SQL statement so the result table shows 63, which is the total quantity of all products. (table 1) SELECT _____ FROM Product;

SUM(Quantity)

Which statement selects all rows and just the ProductName and Size columns from the Product table? a. SELECT *, ProductName, SizeFROM Product; b. SELECT ProductName, SizeFROM Product; c. SELECT *FROM Product; d. SELECT ProductId, ProductName, SizeFROM Product;

b. SELECT ProductName, SizeFROM Product;

What is converted into 0, 1, and M specifications during database design?

business rules