Section 2

The ____ TABLE statement adds, deletes, or modifies columns on an existing table

ALTER

The _____ statement deletes existing rows in a table. The _____ keyword is followed by the table name whose rows are to be deleted. The _______ clause specifies which rows should be deleted. Omitting this clause results in all rows in the table being deleted.

DELETE, FROM, WHERE DELETE FROM TableName WHERE condition;

ON ______ responds to an invalid primary key deletion. Ex: Deleting a primary key 1234 that is used in a foreign key. ON ______ responds to an invalid primary key update. Ex: Updating a primary key 1234 to 5555 when 1234 is used in a foreign key.

DELETE, UPPDATE ON DELETE and ON UPDATE must be followed by a response: RESTRICT rejects an insert, update, or delete that violates referential integrity. RESTRICT is applied by default when no action is specified. SET NULL sets an invalid foreign key value to NULL. SET DEFAULT sets invalid foreign keys to a default primary key value. CASCADE propagates primary key changes to foreign keys. If a primary key is deleted, rows containing matching foreign keys are deleted. If a primary key is updated, matching foreign keys are updated to the same value.

T or F? The statement DROP Employee; deletes the Employee table.

False The keyword TABLE must appear after DROP: DROP TABLE Employee;

The ____ operator is used in a WHERE clause to determine if a value matches one of several values.

IN SELECT * FROM CountryLanguage WHERE Language IN ('Dutch', 'Kongo', 'Albaniana');

When a row is inserted into a table, an unspecified value is assigned ____ by default.

NULL

Which alteration to the CREATE TABLE statement sets the default Name to Accounting? Name VARCHAR(20) DEFAULT Accounting, Name VARCHAR(20) DEFAULT 'Accounting', Name VARCHAR(20) 'Accounting',

Name VARCHAR(20) DEFAULT 'Accounting',

T or F? An UPDATE statement with no WHERE clause changes all the rows.

T see salary UPDATE Employee SET Salary = 42000;

The ______ statement deletes all rows from a table. It is nearly identical to a DELETE statement with no WHERE clause except for some small differences that depend on the database system.

TRUNCATE TRUNCATE TABLE TableName;

A constraint that is applied to a single column is called a ________ _____ ______. A constraint that is applied to multiple columns is called a _____ _____ ______.

column-level constraint, table-level constraint

A _____ is a rule that applies to table data. They are specified in a CREATE TABLE statement or may be added to a preexisting table with an ALTER TABLE statement.

constraint

The ALTER TABLE statement specifies the table name followed by a clause that indicates what should be altered. The table below summarizes the three ALTER TABLE clauses which are...

ALTER TABLE TableName ADD ColumnName DataType; ALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType; ALTER TABLE TableName DROP ColumnName;

The WHERE clause supports logical operators which are....

AND, OR, and NOT.

The ________ keyword defines an auto-increment column in MySQL

AUTO_INCREMENT ID SMALLINT UNSIGNED AUTO_INCREMENT

The _______ operator provides an alternative way to determine if a value is between two other values

BETWEEN HireDate DATE CHECK (HireDate BETWEEN '2000-01-01' AND '2020-01-01'),

The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the ______ keyword

BINARY

The _____ constraint specifies an expression that limits the range of a column's values. Ex: _____ (Salary > 20000) ensures the Salary is greater than 20,000.

CHECK If the CHECK expression does not evaluate to TRUE or UNKNOWN (for NULL values), the constraint is violated.

A constraint can be given a name using the ________ keyword, followed by the constraint name and declaration. Constraint names can help a database administrator or programmer identify which constraint is being violated in a database error message

CONSTRAINT

Constraints can be used two different ways

CREATE TABLE Department ( Name VARCHAR(20) UNIQUE, - column level ManagerID SMALLINT, Appointment DATE, UNIQUE (ManagerID, Appointment) - table level );

The ______ DATABASE statement creates a new database. Once a database is created, tables can be added to the database. The ____ DATABASE statement deletes the database, including all tables in the database.

CREATE, DROP

The ____ keyword with the ORDER BY clause orders rows in descending order.

DESC SELECT * FROM CountryLanguage ORDER BY Language DESC;

The ______ clause is used with a SELECT statement to return only unique or 'distinct' values

DISTINCT SELECT DISTINCT Language FROM CountryLanguage WHERE IsOfficial = 'F';

The SQL language is divided into five sublanguages:

Data Definition Language (DDL) defines the structure of the database. Data Query Language (DQL) retrieves data from the database. Data Manipulation Language (DML) manipulates data stored in a database. Data Control Language (DCL) controls database user access. Data Transaction Language (DTL) manages database transactions.

T or F? Only one column may be listed in a PRIMARY KEY constraint.

F. Multiple columns may be listed in the PRIMARY KEY constraint. In the animation above, two columns are listed to create the composite primary key ID and Number for the Family table.

A foreign key constraint is added to a CREATE TABLE statement with the __________ and ________ keywords

FOREIGN KEY, REFERENCES FOREIGN KEY (ManagerID) REFERENCES Employee(ID)

MySQL requires DROP _____ ConstraintName for a UNIQUE constraint and DROP _____ ConstraintName for a CHECK constraint.

INDEX, CHECK

Which statement correctly inserts Technical support? INSERT INTO Department (Code, Name) VALUES (99); INSERT INTO Department (Code, Name) VALUES (99, 'Technical support'); INSERT INTO Department (Code, Name) VALUES (99, 'Technical support', NULL);

INSERT INTO Department (Code, Name) VALUES (99, 'Technical support', NULL); The INSERT INTO clause lists Code and Name, and the VALUES clause lists the two matching values. The SHOW COLUMNS result table shows NULL is the default value for ManagerID, so the unspecified ManagerID is NULL.

The INSERT statement adds rows to a table. The INSERT statement includes the INTO and VALUES clauses: The INTO clause names the table and columns where data is to be added. The VALUES clause specifies the column values to be added.

INSERT INTO TableName (Column1, Column2, ..., ColumnN) VALUES (Value1, Value2, ..., ValueN);

Two operators are used to test for NULL in a WHERE clause:

IS NULL tests if a value is NULL. IS NOT NULL tests if a value is not NULL.

The ____ operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

LIKE % matches any number of characters. Ex: LIKE 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat". _ matches exactly one character. Ex: LIKE 'L_t' matches "Lot" and "Lit" but not "Lt" and "Loot". SELECT * FROM CountryLanguage WHERE Language LIKE 'A%a';

Some tables may contain thousands or millions of rows, and selecting all rows can take a long time. MySQL has a _____ clause that limits the number of rows returned by a SELECT statement

LIMIT SELECT Column1, Column2, ... ColumnN FROM TableName WHERE condition;

Which alteration to the CREATE TABLE statement prevents ManagerID from being NULL? ManagerID NOT NULL SMALLINT, ManagerID NOT NULL, ManagerID SMALLINT NOT NULL,

ManagerID SMALLINT NOT NULL, Correct The NOT NULL constraint must be listed after the column name and data type.

The _____ ___ clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order.

ORDER BY SELECT * FROM CountryLanguage ORDER BY Language;

The _____ _____ constraint in a CREATE TABLE statement names the table's primary key, the column(s) that uniquely identify each row.

PRIMARY KEY CREATE TABLE Employee ( ID SMALLINT UNSIGNED, Name VARCHAR(60), Salary DECIMAL(7,2), PRIMARY KEY (ID) );

The _____ statement selects rows from a table named in the _____ clause.

SELECT, FROM SELECT Column1, Column2, ... ColumnN FROM TableName;

The _____ statement provides database users and administrators with information about databases, the database contents (tables, columns, etc.), and server status information.

SHOW Commonly used SHOW statements include: SHOW DATABASES lists databases available in the database system. SHOW TABLES lists tables available in the currently selected database. SHOW COLUMNS lists columns available in a specific table named by a FROM clause. SHOW CREATE TABLE shows the CREATE TABLE statement for a given table.

T or F? Foreign keys must reference primary keys?

T

The _______ statement modifies existing rows in a table. The UPDATE statement uses the _____ clause to specify the new column values. The _____ clause is used with UPDATE, DELETE, and SELECT statements to specify a condition that must be true for a row to be chosen.

UPDATE, SET, WHERE UPDATE TableName SET Column1 = Value1, Column2 = Value2, ..., ColumnN = ValueN WHERE condition;

The ____ statement selects a database and is required to show information about tables within a specific database.

USE

When creating a table, every table column must be assigned a data type. Common data types include: integer, decimal, date and time, and character. Most databases allow integer and decimal numbers to be signed or unsigned. A ____ number is a number that may be negative. An _______ number is a number that cannot be negative.

signed, unsigned

An SQL ______ is a complete command composed of one or more clauses. A ______ groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000

statement, clause