CH2

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

what should be added so null values are not allowed in productname?

CREATE TABLE Product ( ProductId INT, ProductName _____, UnitPrice DECIMAL(5,2), SupplierId INT );

VARCHAR(50) NOT NULL

In mySQL, what is the result of TRUE OR NULL

TRUE

a null value represents ____.

missing data

In MySQL, if salary is null then salary > 5000 is ___ and Salary IS NOT NULL is ____.

NULL

False

Which of the following is true about the insert statement?

FALSE: A single INSERT statement can only add one row.

The column names can be omitted in an INSERT statement.

The INSERT statement is used to add new values to a table.

The VALUES order must match the column order in the INTO clause.

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

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

What values for CountryID do the suppliers Sugarplum and Periwinkle have after the UPDATE statement executes?

UPDATE Supplier SET CountryID = 2 WHERE CountryID IS NULL;

2, 2

Which suppliers remain in the Supplier table after the DELETE statement executes?

DELETE FROM Supplier WHERE CountryID <= 2;

Lin Ming Co.

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

primary key

Which two rules apply to primary keys?

values must be unique and may not be NULL

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

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)

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:

Department CodeDepartmentName

1 Sales

2 Marketing

3 Development

What is the result of the following statement?

INSERT INTO Department (DepartmentName) VALUES ('Shipping');

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

Refer to the Teacher and Class tables. The TeacherID in the Class table references the TeacherID in the Teacher table. The TeacherID in the Class table is a/an _____.

Foreign key

what statement is a special case of foreign keys?

FALSE: Foreign key referring to a foreign key in the same table

Foreign key referring to a primary key in the same table

Multiple foreign keys referring to the same primary key

Composite foreign key referring to a composite primary key

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

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)

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

RESTRICT

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

Refer to the Teacher and Class tables. Which foreign key action updates the TeacherID for the Web Development course to 45672 when Bryan McNeal's TeacherID is updated to 45672?

CASCADE

Refer to the Teacher and Class tables. When Bryan McNeal is deleted from the Teacher table, the TeacherID for the Web Development course automatically changes to 11234. Which action has been specified for the TeacherID foreign key?

SET DEFAULT

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/an ______ us a rule enforced on a table's data.

constraint

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

DEFAULT

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

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

which statement creates a primary key constraint on the ID column of the employee table?

ALTER TABLE Employee ADD PRIMARY KEY (ID);