Quiz 2

Table

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

missing data

A NULL value represents _____. the empty string zero false missing data

DEFAULT

A _____ constraint specifies a column value that is used when no value is provided in an INSERT statement. NOT NULL CHECK UNIQUE DEFAULT

Set

A _____ is a collection of values with no inherent order. set sequence row tuple

primary key

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

Supplier

A database designer wants to create three tables: Supplier, Product, and Country. The Supplier table has a CountryID column with values that must appear in the Country table's CountryID column. The Product table has a ProductID column. Which table's CREATE TABLE statement(s) must specify a FOREIGN KEY? Supplier Product Country Supplier and Country

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

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

Operand

A value that is used in a computation is known as a/an _____. operand function operator clause

constraint

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

VARCHAR(50), DECIMAL(8, 2)

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

rows of a table have no inherent order

Data independence means _____. a table may not have duplicate rows rows of a table have no inherent order each cell contains exactly one value a table may not have duplicate column names

6

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) ); 7 5 8 6

NULL, FALSE

In MySQL, if Salary is NULL then Salary > 5000 is _____ and Salary IS NOT NULL is _____. NULL, NULL FALSE, FALSE NULL, FALSE NULL, TRUE

NULL

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

TRUE

In MySQL, what is the result of TRUE OR NULL? TRUE NOT FALSE NULL

ordered, not ordered

In a table, columns are _____ and rows are _____. ordered, ordered not ordered, not ordered ordered, not ordered not ordered, ordered

Product

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

table

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) ); complex column row table

a name and a data type

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

Row

In the relational model, an unnamed tuple of values is a _____. column data type row table

FOREIGN KEY (ShipPartCode) REFERENCES Part (PartCode)

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

Data Query Language

The statement below is an example from which SQL sublanguage? SELECT ProductName FROM Product; Data Transaction Language Data Query Language Data Manipulation Language Data Definition Language

Keyword

UPDATE, SELECT, and ORDER BY are _____ in an SQL statement. keywords literals comments identifiers

BLOB

What data type stores binary values? INT POINT BLOB FLOAT

Structured Query Language

What does SQL stand for? Structured Query Language Simple Query Language Selected Query Language Standard Query Language

ON DELETE CASCADE

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 ON UPDATE CASCADE ON DELETE SET NULL ON UPDATE SET NULL

* + = NOT AND OR

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

CREATE DATABASE reservationDB;

What is the correct statement for creating a database called reservationDB? CREATE reservationDB DATABASE; CREATE DATABASE reservationDB; CREATE DB reservationDB; DATABASE CREATE reservationDB;

DROP DATABASE DatabaseName;

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

VARCHAR(50) NOT 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 ); NOT NULL VARCHAR(50) NOT NULL NOT NULL VARCHAR(50) VARCHAR(50 NOT NULL)

ALTER TABLE Supplier ADD Fax VARCHAR(30);

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

ALTER TABLE Supplier DROP City;

Which SQL statement deletes the City column from the Supplier table? ALTER TABLE Supplier DROP City; DROP COLUMN City; USE Supplier; DROP City; DROP City;

DROP TABLE Supplier;

Which SQL statement deletes the table Supplier? DELETE TABLE Supplier; DROP TABLE Supplier; DROP Supplier; DROP Supplier TABLE;

SupplierID

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

INT

Which data type should a database use to store negative numbers? BLOB INT DATETIME CHAR

Data Definition Language

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

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

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) ); (111, NULL, 20, "12301 270th Pl, Seattle, WA 98126") (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")

ALTER TABLE Employee ADD PRIMARY KEY (ID);

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

Foreign key referring to a foreign key in the same table

Which statement is not a special case of foreign keys? Foreign key referring to a foreign key in the same table Composite foreign key referring to a composite primary key Multiple foreign keys referring to the same primary key Foreign key referring to a primary key in the same table

SELECT ProductName, Size FROM Product;

Which statement selects all rows and just the ProductName and Size columns from the Product table? SELECT ProductName, Size FROM Product; SELECT ProductId, ProductName, Size FROM Product; SELECT * FROM Product; SELECT *, ProductName, Size FROM Product;

Values must be unique and may not be NULL

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

Merge

_____ is not a relational algebra operation. Join Merge Union Project