CIS275 - Chapter 4: Structured Query Language

Question 1

_____ is a high-level computer language for storing, manipulating, and retrieving data in a relational database.

Answer 1

Structured Query Language (SQL)

Question 2

Answer 2

1. A database designer uses SQL to create a database and the database tables.
2. A database user uses SQL to insert, retrieve, update, and delete data from the tables.

Question 3

Answer 3

Question 4

An _____ is a complete command composed of one or more clauses.

Answer 4

SQL statement

A clause groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000. An SQL statement may be written on a single line, but good practice is to write each clause on a separate line.

Question 5

Answer 5

1. The SELECT clause starts the statement. Name is a column name.
2. The FROM clause must follow the SELECT clause. City is a table name.
3. The WHERE clause is optional. When included, the WHERE clause must follow the FROM clause. Population > 100000 is a condition.
4. The three clauses ending in a semicolon is a statement. The statement retrieves the names of all cities that have a population greater than 100,000 people.

Question 6

Answer 6

Question 7

Answer 7

Question 8

The SQL language is divided into five sublanguages:

_____ defines the structure of the database.

Answer 8

Data Definition Language (DDL)

Question 9

The SQL language is divided into five sublanguages:

_____ retrieves data from the database.

Answer 9

Data Query Language (DQL)

Question 10

The SQL language is divided into five sublanguages:

_____ manipulates data stored in a database.

Answer 10

Data Manipulation Language (DML)

Question 11

The SQL language is divided into five sublanguages:

______ controls database user access.

Answer 11

Data Control Language (DCL)

Question 12

The SQL language is divided into five sublanguages:

_____ manages database transactions.

Answer 12

Data Transaction Language (DTL)

Question 13

Answer 13

Question 14

The _____ statement creates a new database.

Answer 14

CREATE DATABASE

Once a database is created, tables can be added to the database.

Question 15

The _____ statement deletes the database, including all tables in the database.

Answer 15

DROP DATABASE

Question 16

Answer 16

Question 17

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

Answer 17

SHOW

Question 18

Commonly used SHOW statements include:

_____ lists databases available in the database system.

Answer 18

SHOW DATABASES

Question 19

Commonly used SHOW statements include:

_____ lists tables available in the currently selected database.

Answer 19

SHOW TABLES

Question 20

Commonly used SHOW statements include:

_____ lists columns available in a specific table named by a FROM clause.

Answer 20

SHOW COLUMNS

Question 21

Commonly used SHOW statements include:

_____ shows the CREATE TABLE statement for a given table.

Answer 21

SHOW CREATE TABLE

Question 22

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

Answer 22

USE

Question 23

Answer 23

Question 24

A _____ is a number that may be negative.

Answer 24

signed number

Question 25

An _____ is a number that cannot be negative.

Answer 25

unsigned number

Question 26

Integer 1 byte Signed range: -128 to 128 Unsigned range: 0 to 255

Answer 26

TINYINT

Question 27

Integer ## Footnote 2 bytes Signed range: -32,768 to 32,767 Unsigned range: 0 to 65,535

Answer 27

SMALLINT

Question 28

Integer ## Footnote 3 bytes Signed range: -8,388,608 to 8,388,607 Unsigned range: 0 to 16,777,215

Answer 28

MEDIUMINT

Question 29

Integer ## Footnote 4 bytes Signed range: -2,147,483,648 to 2,147,483,647 Unsigned range: 0 to 4,294,967,295

Answer 29

INTEGER or INT

Question 30

8 bytes Signed range: -2^63 to 2^63 -1 Unsigned range: 0 to 2^64 -1

Answer 30

BIGINT

Question 31

Decimal ## Footnote Varies depending on M and D Exact decimal number where M = number of significant digits, D = number of digits after decimal point

Answer 31

DECIMAL(M,D)

Question 32

Decimal ## Footnote 4 bytes Approximate decimal numbers with range: -3.4E+38 to 3.4E+38

Answer 32

FLOAT

Question 33

Decimal ## Footnote 8 bytes Approximate decimal numbers with range: -1.8E+308 to 1.8E+308

Answer 33

DOUBLE

Question 34

Date and time ## Footnote 3 bytes Format: YYYY-MM-DD. Range: '1000-01-01' to '9999-12-31'

Answer 34

DATE

Question 35

Date and time ## Footnote 3 bytes Format: hh:mm:ss

Answer 35

TIME

Question 36

Date and time ## Footnote 5 bytes Format: YYYY-MM-DD hh:mm:ss. Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

Answer 36

DATETIME

Question 37

Character ## Footnote N bytes Fixed-length string of length N; 0 ≤ N ≤ 255

Answer 37

CHAR(N)

Question 38

Character ## Footnote Length of characters + 1 bytes Variable-length string with maximum N characters; 0 ≤ N ≤ 65,535

Answer 38

VARCHAR(N)

Question 39

Answer 39

Question 40

The _____ statement creates a new table by specifying the table name, column names, and column data types.

Answer 40

CREATE TABLE

Question 41

The _____ statement deletes a table, along with all the table's rows, from a database.

Answer 41

DROP TABLE Ex: DROP TABLE Employee; deletes the Employee table.

Question 42

Answer 42

Question 43

The _____ statement adds, deletes, or modifies columns on an existing table.

Answer 43

ALTER TABLE 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.

Question 44

Answer 44

1. The ALTER TABLE statement adds a Salary column to Employee that holds 7 significant digits and 2 decimal places. 2. The ALTER TABLE statement changes the Salary column's name to AnnualSalary. The data type is changed to INT. 3. The ALTER TABLE statement deletes the AnnualSalary column.

Question 45

Answer 45

Question 46

Answer 46

CREATE TABLE Country ( ISOCode3 CHAR(3), Population INTEGER UNSIGNED ); DROP TABLE Country; CREATE TABLE Country creates table Country. Since the code is always three letters long, the best data type is CHAR(3). Population should be stored as a whole number, and since no country's population exceeds 1,500,000,000 and a population can't be negative, INTEGER UNSIGNED or INT UNSIGNED is the best data type. DROP TABLE Country deletes the Country table.

Question 47

Answer 47

Expected: ALTER TABLE Country DROP Population; The ALTER TABLE statement adds, deletes, or modifies columns on an existing table. DROP Population; deletes the column Population.

Question 48

Answer 48

Expected: ALTER TABLE Country ADD IndepDate DATE; The ALTER TABLE statement adds, deletes, or modifies columns on an existing table. ADD IndepDate DATE; adds a column called IndepDate. Since IndepDate will hold a date, the best data type is DATE.

Question 49

Answer 49

Expected: ALTER TABLE Country DROP IndepDate; The ALTER TABLE statement adds, deletes, or modifies columns on an existing table. DROP IndepDate; deletes the column IndepDate.

Question 50

Answer 50

Expected: ALTER TABLE Country CHANGE Population OfficialPopulation INTEGER UNSIGNED; CHANGE Population OfficialPopulation INTEGER UNSIGNED; renames Population to OfficialPopulation and changes OfficialPopulation from accepting numbers up to 8,388,607 to accepting numbers up to 2,147,483,647.

Question 52

A _____ is a rule that applies to table data.

Answer 52

constraint Constraints are specified in a CREATE TABLE statement or may be added to a preexisting table with an ALTER TABLE statement.

Question 53

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

Answer 53

PRIMARY KEY constraint

Question 54

Answer 54

Question 55

An auto-increment column is a column that is assigned an automatically incrementing value.

Answer 55

auto-increment column A primary key on an ID column is commonly implemented as an auto-increment column. Ex: A database system may assign an auto-incrementing column values 1, 2, 3, etc. for each row that is inserted into the table. The AUTO\_INCREMENT keyword defines an auto-increment column in MySQL.

Question 56

Answer 56

Question 57

A _____ is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords.

Answer 57

foreign key constraint 1. The Employee table has primary key ID and several rows. 2. The Department table is created with a FOREIGN KEY constraint that REFERENCES the Employee ID column. 3. When rows are added to Department, the ManagerID value must exist in Employee ID. ManagerID 9999 is rejected because 9999 does not exist in Employee ID.

Question 58

Answer 58

Question 59

\_\_\_\_\_ responds to an invalid primary key deletion. Ex: Deleting a primary key 1234 that is used in a foreign key.

Answer 59

ON DELETE ON DELETE and ON UPDATE must be followed by a response: RESTRICT SET NULL SET DEFAULT CASCADE

Question 60

\_\_\_\_\_ responds to an invalid primary key update. Ex: Updating a primary key 1234 to 5555 when 1234 is used in a foreign key.

Answer 60

ON UPDATE ON DELETE and ON UPDATE must be followed by a response: RESTRICT SET NULL SET DEFAULT CASCADE

Question 65

Answer 65

Question 66

Answer 66

Expected: PRIMARY KEY (ISOCode2) PRIMARY KEY (ISOCode2) sets ISOCode2 as the primary key of the table.

Question 67

Answer 67

Expected: PRIMARY KEY (ISOCode2, ISOCode3) PRIMARY KEY (ISOCode2, ISOCode3) sets ISOCode2 and ISOCode3 as the composite primary key of the table.

Question 68

Answer 68

Expected: PRIMARY KEY (TLD, ISOCode3) PRIMARY KEY (TLD, ISOCode3) sets TLD and ISOCode3 as the composite primary key of the table.

Question 69

Answer 69

Expected: FOREIGN KEY (Code2) REFERENCES Country (ISOCode2) FOREIGN KEY (Code2) REFERENCES Country (ISOCode2) indicates that the foreign key Code2 refers to the primary key in the Country table.

Question 70

Answer 70

Expected: FOREIGN KEY (Domain) REFERENCES Country (TLD) FOREIGN KEY (Domain) REFERENCES Country (TLD) indicates that the foreign key Domain refers to the primary key in the Country table.

Question 71

Answer 71

Expected: Nothing happens to Country table. Domain .ly is deleted from Statistics table. TLD of LY is updated to .xy in Country table. Domain of .ly is updated to .xy in Statistics table. Row containing LY is deleted in Country table. Row containing .ly is deleted in Statistics table. Deleting the foreign key '.ly' in Statistics has no effect on the primary key '.ly' in Country. ON UPDATE CASCADE updates the foreign key Domain in the Statistics table to the same value used to update the primary key TLD in the Country table. ON DELETE CASCADE deletes rows with foreign key Domain in Statistics table when the matching primary key TLD in Country table is deleted.

Question 72

Answer 72

Expected: Nothing happens to Country table. CntyNm Italy is deleted from Statistics table. Row containing .it is deleted in Country table. Row containing Italy is deleted in Statistics table. Name of .it is updated to XYZ in Country table. CntyNm of Italy is updated to XYZ in Statistics table. Deleting the foreign key 'Italy' in Statistics has no effect on the primary key 'Italy' in Country. ON DELETE CASCADE deletes rows with foreign key CntyNm in Statistics table when the matching primary key Name in Country table is deleted. ON UPDATE CASCADE updates the foreign key CntyNm in the Statistics table to the same value used to update the primary key Name in the Country table.

Question 73

Answer 73

Expected: Row containing Uganda is deleted in Country table. Row containing UG is deleted in Statistics table. ISOCode2 of Uganda is updated to XY in Country table. Code2 of UG is updated to XY in Statistics table. Nothing happens to Country table. Code2 UG is deleted from Statistics table. ON DELETE CASCADE deletes rows with foreign key Code2 in Statistics table when the matching primary key ISOCode2 in Country table is deleted. ON UPDATE CASCADE updates the foreign key Code2 in the Statistics table to the same value used to update the primary key ISOCode2 in the Country table. Deleting the foreign key 'UG' in Statistics has no effect on the primary key 'UG' in Country.

Question 79

\_\_\_\_\_ rejects an insert, update, or delete that violates referential integrity. RESTRICT is applied by default when no action is specified.

Answer 79

RESTRICT

Question 80

\_\_\_\_\_ sets an invalid foreign key value to NULL.

Answer 80

SET NULL

Question 81

\_\_\_\_\_ sets invalid foreign keys to a default primary key value.

Answer 81

SET DEFAULT

Question 82

\_\_\_\_\_ 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.

Answer 82

CASCADE

Question 83

All table columns, except primary keys, may contain NULL values by default. The _____ constraint is used in a CREATE TABLE statement to prevent a column from having a NULL value.

Answer 83

NOT NULL

Question 84

Answer 84

Question 85

When a row is inserted into a table, an unspecified value is assigned NULL by default. The _____ is used in a CREATE TABLE statement to specify a column's default value when no value is provided.

Answer 85

DEFAULT constraint ## Footnote 1. The Name column does not have a DEFAULT constraint. When a row is inserted into Employee with no specified name, Name is NULL by default. 2. The BirthDate column has a DEFAULT constraint, so January 1, 2000 is used if a row is inserted with no specified birth date. 3. The Salary column has a DEFAULT constraint, so salary is 0.00 if no salary is specified when inserting a new row.

Question 86

Answer 86

Question 87

A table's primary key always has unique values, but values in other columns may contain duplicates. The _____ ensures that all column values are unique. ## Footnote The UNIQUE constraint may be applied to a single column or to multiple columns. A constraint that is applied to a single column is called a \_\_\_\_\_. A constraint that is applied to multiple columns is called a \_\_\_\_\_. MySQL creates an index for each UNIQUE constraint, which can improve query retrieval performance.

Answer 87

UNIQUE constraint column-level constraint table-level constraint 1. The Username column has a UNIQUE column-level constraint, so each Username must be different. 2. Attempting to insert a second row with username 'mrodriguez' fails because 'mrodriguez' already exists. 3. The UNIQUE table-level constraint requires each combination of Name and Extension be different. 4. Inserting Maria Rodriquez with extension 4888 does not violate the UNIQUE constraint because (Maria Rodriguez, 5050) and (Maria Rodriguez, 4888) are different. 5. Attempting to insert Maria Rodriquez with extension 5050 fails because (Maria Rodriguez, 5050) already exists.

Question 88

Answer 88

Question 89

The _____ specifies an expression that limits the range of a column's values.

Answer 89

CHECK constraint Ex: CHECK (Salary \> 20000) ensures the Salary is greater than 20,000. If the CHECK expression does not evaluate to TRUE or UNKNOWN (for NULL values), the constraint is violated. A CHECK constraint can be a column-level or table-level constraint. 1. The CHECK column-level constraint ensures all rows have a HireDate between Jan 1, 2000 and Dec 12, 2019. 2. Maria's HireDate is between Jan 1, 2000 and Dec 31, 2019. But inserting Lisa fails because Mar 15, 2020 is after Dec 31, 2019. 3. The CHECK table-level constraint requires BirthDate to come before HireDate. 4. Inserting Sam fails because the BirthDate Nov 29, 2003 is after HireDate Nov 1, 2003. 5. The NULL BirthDate makes the table-level CHECK constraint evaluate to UNKNOWN, which does not violate the constraint.

Question 90

Determining if a value is between two other values, like the animation above does with HireDate, is a common SQL operation. The _____ provides an alternative way to determine if a value is between two other values.

Answer 90

BETWEEN operator ## Footnote Ex: -- Same as: HireDate \>= '2000-01-01' AND HireDate \<= '2020-01-01' HireDate DATE CHECK (HireDate BETWEEN '2000-01-01' AND '2020-01-01'),

Question 91

Answer 91

Question 92

MySQL gives constraints a default name if no name is specified. A constraint can be given a name using the \_\_\_\_\_, followed by the constraint name and declaration.

Answer 92

CONSTRAINT keyword Constraint names can help a database administrator or programmer identify which constraint is being violated in a database error message. 1. The CONSTRAINT keyword gives the CHECK and UNIQUE constraints user-defined names. 2. The database uses constraint names in error messages. Fred's HireDate is not \>= 2000-01-01. 3. The ALTER TABLE statement drops the HireCheck constraint. Any HireDate may now be added to Employee. 4. The ALTER TABLE statement adds a new constraint that ensures the HireDate is before Feb 14, 2000. Emma's HireDate violates the new constraint.

Question 93

Answer 93

Question 94

Answer 94

Expected: ISOCode2, ISOCode3, Name The NOT NULL constraint prevents the Area and Population columns from having a NULL value. A primary key cannot contain a NULL value, so Code values cannot be NULL. All other table columns can contain NULL values.

Question 95

Answer 95

Expected: 1.0, NULL, 0 The Area column has a DEFAULT constraint, so 1.0 is used if a row is inserted with no specified Area. The ISOCode2 column does not have a DEFAULT constraint, so when a row is inserted into Country with no specified ISOCode2, ISOCode2 is NULL by default. The Code column has a DEFAULT constraint, so 0 is used if a row is inserted with no specified Code.

Question 96

Answer 96

Expected: Violates, Violates The first pair of rows have Code '92' and Name 'Skyrim', so the UNIQUE constraint on (Code, Name) is violated. In the second pair of rows, the ISOCode3 column has a UNIQUE constraint but the two rows have the same ISOCode3 'YSK', so the Country's table's UNIQUE constraints are violated.

Question 97

Answer 97

Expected: Violates, Violates A column-level CHECK constraint limits the Area column to be between 1.0 and 5000.0. In the first row, the Area of 219746.18 violates the column-level constraint. In the second row, the Area of 252070.66 violates the column-level constraint.

Question 98

Answer 98

Expected: CONSTRAINT CheckPopulation CHECK (Area \< Population) DROP CHECK CheckPopulation CONSTRAINT CheckPopulation CHECK (Area \< Population) adds a constraint called CheckPopulation that ensures that Area is less than Population. DROP CHECK UniqueCode drops the UniqueCode constraint.

Question 99

Answer 99

Expected: CONSTRAINT UniqueCode UNIQUE (ContinentCode, ISOCode3) DROP INDEX UniqueCode CONSTRAINT UniqueCode UNIQUE (ContinentCode, ISOCode3) adds a constraint called UniqueCode that ensures the ContinentCode and ISOCode3 combination is unique. DROP INDEX UniqueCode drops the UniqueCode constraint.

Question 100

Answer 100

Expected: CONSTRAINT CheckPopulation CHECK (PopDensity \< Population) DROP CHECK CheckPopulation CONSTRAINT CheckPopulation CHECK (PopDensity \< Population) adds a constraint called CheckPopulation that ensures that PopDensity is less than Population. DROP CHECK UniqueCode drops the UniqueCode constraint.

Question 101

The _____ adds rows to a table.

Answer 101

INSERT statement The INSERT statement includes the INTO and VALUES clauses: INSERT syntax: INSERT INTO TableName (Column1, Column2, ..., ColumnN) VALUES (Value1, Value2, ..., ValueN); 1. The INSERT statement's INTO clause names the Employee table and Employee's columns in parentheses. 2. The VALUES clause names the column values in parenthesis. The value order must match the column order in the INTO clause. 3. The column names may be omitted as long as the VALUES clause lists all column values in the same order as the table's columns. 4. Any number of rows may be added with a single INSERT statement.

Question 102

The _____ names the table and columns where data is to be added.

Answer 102

INTO clause 1. The INSERT statement's INTO clause names the Employee table and Employee's columns in parentheses. 2. The VALUES clause names the column values in parenthesis. The value order must match the column order in the INTO clause. 3. The column names may be omitted as long as the VALUES clause lists all column values in the same order as the table's columns. 4. Any number of rows may be added with a single INSERT statement.

Question 103

The _____ specifies the column values to be added.

Answer 103

VALUES clause The VALUES clause may list any number of rows in parentheses to insert multiple rows. 1. The INSERT statement's INTO clause names the Employee table and Employee's columns in parentheses. 2. The VALUES clause names the column values in parenthesis. The value order must match the column order in the INTO clause. 3. The column names may be omitted as long as the VALUES clause lists all column values in the same order as the table's columns. 4. Any number of rows may be added with a single INSERT statement.

Question 104

Answer 104

Question 105

Database users make some common errors when creating INSERT statements:

Answer 105

Inserting duplicate primary key values or foreign key values that do not match an existing primary key. Inserting primary key values for auto-increment columns. Inserting NULL values for columns that are NOT NULL. 1. The INSERT statement uses an ID that already exists in Employee. Duplicate primary key values cannot be added, so a unique ID must be chosen. 2. If ID is an auto-increment column, the ID should not be listed in the INSERT statement. The database assigns the ID automatically. 3. If Salary is a NOT NULL column, then the Salary value must be specified.

Question 106

The _____ modifies existing rows in a table.

Answer 106

UPDATE statement The UPDATE statement uses the SET clause to specify the new column values. The UPDATE statement uses a WHERE clause to determine which rows are updated. The WHERE clause is used with UPDATE, DELETE, and SELECT statements to specify a condition that must be true for a row to be chosen. Omitting the WHERE clause results in all rows being updated. UPDATE syntax. UPDATE TableName SET Column1 = Value1, Column2 = Value2, ..., ColumnN = ValueN WHERE condition;

Question 107

Answer 107

Question 108

The ____ deletes existing rows in a table.

Answer 108

DELETE statement The FROM keyword is followed by the table name whose rows are to be deleted. The WHERE clause specifies which rows should be deleted. Omitting the WHERE clause results in all rows in the table being deleted. DELETE syntax. DELETE FROM TableName WHERE condition;

Question 109

Answer 109

Question 110

Answer 110

Question 111

The _____ deletes all rows from a table.

Answer 111

TRUNCATE statement TRUNCATE is nearly identical to a DELETE statement with no WHERE clause except for some small differences that depend on the database system. Ex: In MySQL, A TRUNCATE statement resets the table's auto-increment values back to 1, but a DELETE statement does not. TRUNCATE TABLE TableName;

Question 112

Answer 112

Expected: INSERT INTO Country (ISOCode2, Population, Name) VALUES ('MR', 4403319, 'Mauritania'); INSERT INTO Country (ISOCode2, Population, Name) names the table and columns where data is to be added. VALUES ('MR', 4403319, 'Mauritania') names the values to be inserted into each column. The columns' values must match the order of the column names in the INSERT INTO clause.

Question 113

Answer 113

Expected: Allowed Not allowed Not allowed Inserting values ('Algeria', 'DZ') into columns (Name, ISOCode2) does not contain any errors and is allowed. The name column is a NOT NULL column, and a Name value 'Algeria' is specified. Value 'DZ' is allowed because the primary key 'DZ' does not exist in the 'ISOCode2' column. Rank is auto-increment, so the database chooses a unique integer for Rank. Inserting values (5, 'Guinea', 'GN') into columns (Rank, Name, ISOCode2) is not allowed because Rank is an auto-increment column. Inserting values ('Mali', 'ML') into columns (Name, ISOCode2) is not allowed because the primary key 'ML' already exists.

Question 114

Answer 114

Expected: Not allowed Not allowed Allowed Inserting values (3, 'Comoros', 'KM') into columns (Rank, Name, ISOCode2) is not allowed because Rank is an auto-increment column. Inserting values ('Bahamas', 'BS') into columns (Name, ISOCode2) is not allowed because the primary key 'BS' already exists. Inserting values ('Suriname', 'SR') into columns (Name, ISOCode2) does not contain any errors and is allowed. The name column is a NOT NULL column, and a Name value 'Suriname' is specified. Value 'SR' is allowed because the primary key 'SR' does not exist in the 'ISOCode2' column. Rank is auto-increment, so the database chooses a unique integer for Rank.

Question 115

Answer 115

Expected: UPDATE Country SET Code = 705 WHERE ISOCode2 = 'CA'; UPDATE Country indicates that the Country table will be changed. SET Code = 705 names the new Code value. WHERE ISOCode2 = 'CA' indicates that only the row with ISOCode2 'CA' will be changed.

Question 116

Answer 116

Expected: UPDATE Country SET Name = 'Zambia' WHERE TLD = '.bz'; UPDATE Country indicates that the Country table will be changed. SET Name = 'Zambia' names the new Name value. WHERE TLD = '.bz' indicates that only the row with TLD '.bz' will be changed.

Question 117

Answer 117

UPDATE Country SET Name = 'Kuwait' WHERE TLD = '.er'; UPDATE Country indicates that the Country table will be changed. SET Name = 'Kuwait' names the new Name value. WHERE TLD = '.er' indicates that only the row with TLD '.er' will be changed.

Question 118

Answer 118

Expected: DELETE FROM Country WHERE ISOCode2 = 'IE'; DELETE FROM Country indicates that the Country table will have rows deleted. WHERE ISOCode2 = 'IE' indicates that only the row with ISOCode2 'IE' will be deleted.

Question 119

Answer 119

Expected: DELETE FROM Country WHERE ISOCode2 = 'UY'; DELETE FROM Country indicates that the Country table will have rows deleted. WHERE ISOCode2 = 'UY' indicates that only the row with ISOCode2 'UY' will be deleted.

Question 120

The _____ selects rows from a table named in the \_\_\_\_\_.

Answer 120

SELECT statement FROM clause

Question 121

The data returned from the SELECT statement, called the \_\_\_\_\_, is stored in a \_\_\_\_\_.

Answer 121

result set result table

Question 122

Answer 122

Question 123

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

Answer 123

LIMIT clause Ex: The SELECT statement below returns only the first 100 rows from the City table. SELECT \* FROM City LIMIT 100;

Question 124

The SELECT statement retrieves all rows by default. The _____ is combined with the SELECT statement to filter the result set.

Answer 124

WHERE clause The WHERE clause must come after the FROM clause. The WHERE clause works like an if statement in a programming language, specifying conditions that must be true for a row to be selected.

Question 125

an expression that evaluates to TRUE, FALSE, or NULL.

Answer 125

condition Only a condition that is TRUE selects the row.

Question 126

A condition uses a _____ to compare two values.

Answer 126

comparison operator A comparison operator evaluates to NULL if one or both operands are NULL. Ex: NULL \> 1000 is NULL. More information about NULL values is covered elsewhere in this material.

Question 127

Answer 127

Question 128

The WHERE clause supports _____ AND, OR, and NOT.

Answer 128

Logical operators A logical operator evaluates to TRUE, FALSE, or NULL. Ex: 1 \< 2 AND 2 \< 3 is TRUE because the condition on the left is TRUE, and the condition on the right is TRUE.

Question 129

Answer 129

1. The SELECT statement selects only rows WHERE percentage is between 0.0 and 10.0. Two rows are returned. 2. The SELECT statement selects only rows WHERE percentage \< 5.0 OR percentage \> 90.0. Two rows are returned.

Question 130

Answer 130

Question 131

The SELECT statement supports \_\_\_\_\_, which perform calculations on two operands.

Answer 131

Arithmetic operators Arithmetic operators can be used in the SELECT clause or the WHERE clause. Expressions with arithmetic operators are computed using the same rules as basic arithmetic. Items in parentheses () have highest precedence. Multiplication (\*), division (/), and modulus (%) have precedence over addition (+) and subtraction (-). Ex: The expression 7 + 3 \* 2 = 7 + 6 = 13 because \* has precedence over +, but (7 + 3) \* 2 = 10 \* 2 = 20 because () has precedence over \*.

Question 132

Answer 132

1. The Customer table tracks each customer's balance and payment information. 2. The SELECT and WHERE clauses use subtraction to determine which customers owe more than $1000 after making a payment. Two rows are returned. 3. The WHERE clause uses multiplication to determine which customers can pay off their balance after making 10 payments. Two rows are returned.

Question 133

Answer 133

Question 134

Answer 134

Expected: Left: ISOCode2, ISOCode3, PopDensity Right: PopDensity SELECT \* selects all columns. SELECT PopDensity selects only the PopDensity column.

Question 135

Answer 135

Expected: Senegal WHERE Name = 'Senegal' specifies only the countries where Name is equal to 'Senegal'.

Question 136

Answer 136

Expected: Kenya, Netherlands For the country to be selected, one or the other (or both) of the conditions must be true. Malawi does not have Name 'Kenya' or ISOCode2 'NL', so is not selected. Kenya has Name 'Kenya', so is selected. Netherlands has ISOCode2 'NL', so is selected.

Question 137

Answer 137

Expected: Senegal, Zimbabwe WHERE Population + 5000000 \< 25799999 selects only those countries with Population + 5000000 \< 25799999. Senegal Population + 5000000 is 20850000 \< 25799999, which is \< 25799999, so is selected. Ivory Coast Population + 5000000 is 30070000 \< 25799999, which is not \< 25799999, so is not selected. Zimbabwe Population + 5000000 is 19440000 \< 25799999, which is \< 25799999, so is selected.

Question 138

Answer 138

Expected: 20410000 Guinea is the only country for which PopDensity \> 105. The Population of Guinea is 12410000, so 12410000 + 8000000 is returned.

Question 139

Two operators are used to test for NULL in a WHERE clause: \_\_\_\_\_ tests if a value is NULL.

Answer 139

IS NULL

Question 140

Two operators are used to test for NULL in a WHERE clause: \_\_\_\_\_tests if a value is not NULL.

Answer 140

IS NOT NULL

Question 142

Answer 142

Question 143

The _____ is used with a SELECT statement to return only unique or 'distinct' values.

Answer 143

DISTINCT clause Ex: The first SELECT statement in the figure below results in two 'Spanish' rows, but the second SELECT statement returns only unique languages, resulting in only one 'Spanish' row.

Question 144

Answer 144

Question 145

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

Answer 145

IN operator The SELECT statement in the figure below uses the IN operator to select only rows where the Language column has a Dutch, Kongo, or Albaniana value.

Question 146

Answer 146

Question 147

The \_\_\_\_\_, when used in a WHERE clause, matches text against a pattern using the two wildcard characters \_\_\_\_\_.

Answer 147

LIKE operator % 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".

Question 148

The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the \_\_\_\_\_.

Answer 148

BINARY keyword. Ex: LIKE BINARY 'L%t' matches 'Left' but not 'left'.

Question 149

Answer 149

Question 150

A SELECT statement selects rows from a table with no guarantee the data will come back in a certain order. The \_\_\_\_\_orders selected rows by one or more columns in ascending (alphabetic or increasing) order. The _____ with the _____ orders rows in descending order.

Answer 150

ORDER BY clause DESC keyword

Question 151

Answer 151

Question 152

Answer 152

Expected: SELECT \* FROM Country WHERE Area IS NULL; WHERE Area IS NULL returns only rows where Area is NULL. NULL represents unknown or not applicable.

Question 153

Answer 153

Expected: SELECT DISTINCT ISOCode2 FROM Country WHERE Area \< 40000; SELECT DISTINCT ISOCode2 returns only rows with unique ISOCode2 values. WHERE Area \< 40000 returns only rows where Area is less than 40000.

Question 154

Answer 154

Expected: SELECT DISTINCT ISOCode2 FROM Country WHERE Area \< 20000; SELECT DISTINCT ISOCode2 returns only rows with unique ISOCode2 values. WHERE Area \< 20000 returns only rows where Area is less than 20000.

Question 155

Answer 155

Expected: Liechtenstein Slovakia Dominica WHERE Continent IN ('SAmerica', 'NAmerica', 'Europe') returns rows where the Continent is 'SAmerica', 'NAmerica', or 'Europe'. Liechtenstein's Continent is Europe, so the row containing Liechtenstein is returned. Slovakia's Continent is Europe, so the row containing Slovakia is returned. Dominica's Continent is NAmerica, so the row containing Dominica is returned.

Question 156

Answer 156

Expected: Botswana WHERE Name LIKE 'B%' matches names starting with B followed by any number of characters. Thailand does not start with B, so the row containing Thailand is not returned. Eritrea does not start with B, so the row containing Eritrea is not returned. Botswana starts with B, so the row containing Botswana is returned.

Question 157

Answer 157

Expected: SELECT \* FROM Country ORDER BY Population DESC, Area; The clause ORDER BY Population DESC, Area orders the rows by Population in descending order, then by Area in ascending order.

Question 158

MySQL has functions for processing strings, numbers, dates, and times. An _____ is a function that works on a group of values.

Answer 158

Aggregate functions

Question 159

Some common aggregate functions include: \_\_\_\_\_ - Counts the number of rows retrieved by a SELECT statement. \_\_\_\_\_ - Finds the minimum value in a group. \_\_\_\_\_ - Finds the maximum value in a group. \_\_\_\_\_ - Sums all the values in a group. \_\_\_\_\_ - Finds the arithmetic mean (average) of all the values in a group.

Answer 159

COUNT() MIN() MAX() SUM() AVG()

Question 160

Answer 160

Question 161

The _____ clause groups rows with identical values into a set of summary rows

Answer 161

GROUP BY clause ## Footnote Some important points about the GROUP BY clause: One or more columns are listed after GROUP BY, separated by commas. GROUP BY clause returns one row for each group. Each group may be ordered with the ORDER BY clause. GROUP BY clause must appear before the ORDER BY clause and after the WHERE clause (if present).

Question 162

1. The SUM() function sums the Population values in each group. 2. The GROUP BY clause forms groups based on the CountryCode column. 3. CountryCode contains two unique values: ZMB and ZWE. So two rows are returned with the total population of each CountryCode value. 4. The COUNT() function counts how many rows exist in each group. 5. The groups are formed by the CountryCode and District columns. 6. Each unique CountryCode and District combination is counted.

Answer 162

1. The SUM() function sums the Population values in each group. 2. The GROUP BY clause forms groups based on the CountryCode column. 3. CountryCode contains two unique values: ZMB and ZWE. So two rows are returned with the total population of each CountryCode value. 4. The COUNT() function counts how many rows exist in each group. 5. The groups are formed by the CountryCode and District columns. 6. Each unique CountryCode and District combination is counted.

Question 163

The _____ is used with the GROUP BY clause to filter group results.

Answer 163

HAVING clause The HAVING clause must appear after the GROUP BY clause but before the optional ORDER BY clause.

Question 164

Answer 164

1. The HAVING clause follows the GROUP BY clause. 2. Although the GROUP BY clause creates two groups based on CountryCode, the HAVING clause selects only the group with a population sum \> 2,300,000. 3. The HAVING clause selects only groups that have a row count \>= 2. Only the ZMB, 2 and ZWE, 1 groups have at least 2 rows.

Question 165

SQL _____ perform useful calculations beyond simple addition, subtraction, multiplication, and division.

Answer 165

numeric functions

Question 166

SQL _____ manipulate strings.

Answer 166

string functions

Question 167

Answer 167

Question 168

\_\_\_\_\_ operate on DATE, TIME, and DATETIME data types.

Answer 168

Date and time functions

Question 169

Answer 169

Question 170

A \_\_\_\_\_, sometimes called a nested query or inner query, is a query within another SQL query.

Answer 170

subquery The subquery is typically used in a SELECT statement's WHERE clause to return data to the outer query and restrict the selected results. The subquery is placed inside parentheses ().

Question 171

Answer 171

1. The outer SELECT statement uses a subquery to determine which languages are used by a larger percentage of a country's population than Aruba's official language. 2. The subquery executes first to find the official language Percentage for ABW, which is 5.3. 3. The outer query executes using the value 5.3 returned by the subquery. Three languages have Percentage \> 5.3 4. The SELECT statement uses the IN operator with a subquery to determine which Languages are used in Europe. 5. The subquery first finds all Codes from Europe: ALB and AND. 6. The outer query then selects the CountryCode and Language for the CountryCodes ALB and AND.

Question 172

Answer 172

Question 173

A subquery is _____ when the subquery's WHERE clause references a column from the outer query.

Answer 173

correlated In a correlated subquery, the rows selected depend on what row is currently being examined by the outer query.

Question 174

a temporary name assigned to a column or table.

Answer 174

alias

Question 175

1. The outer query and correlated subquery both select from the City table. The outer query uses an alias C for the City table, so C.CountryCode refers to the outer query's CountryCode column.

Answer 175

1. The outer query and correlated subquery both select from the City table. The outer query uses an alias C for the City table, so C.CountryCode refers to the outer query's CountryCode column. 2. The outer query executes first to process rows in the City table. As each City row is processed, the subquery finds the average population for the city's country. 3. Then the outer query executes using the average population returned from the subquery. Buenos Aires has a population 2982146 \> 2124303.5. 4. The outer query processes the next row, and the average population for ARG is calculated again. La Matanza is not selected because La Matanza's population is not \> 2124303.5. 5. The outer query finds São Paulo also has a population \> BRA's average population. 6. Rio de Janeiro is not selected because Rio de Janeiro's population 5598953 is not \> 7783719.

Question 176

Answer 176

Question 177

Correlated subqueries commonly use the \_\_\_\_\_, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected. The _____ operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected.

Answer 177

EXISTS operator NOT EXISTS 1. The outer query uses EXISTS with a correlated subquery to select only cities in countries where at least one language is spoken by more than 97% of the population. 2. The correlated subquery selects no rows for ARG because no Percentage value is \> 97. Since no rows are selected, EXISTS returns FALSE and no ARG cities are selected. 3. The correlated subquery selects one row for BRA because Portuguese's percentage 97.5 \> 97. EXISTS returns TRUE when at least one row is selected, so all BRA cities are selected. 4. Rio de Janeiro is also selected because at least one BRA row has Percentage \> 97.

Question 178

Answer 178

Question 179

Database users frequently create complex SQL queries that join data from multiple tables to answer business questions. Ex: A bookstore might ask, "Which books are selling best in summer?" and "What types of books do customers from the West Coast purchase?" To create a complex query, a database user can employ the following strategy:

Answer 179

1. Examine a table diagram or other database summary to understand the tables and relationships. 2. Identify the tables containing the necessary data to answer the question. 3. Determine which columns should appear in the result table. 4. Write a query that joins the tables using the table's primary and foreign keys. 5. Break the problem into simple queries, writing one part of the query at a time.

Question 180

Answer 180

Question 181

Answer 181

Question 182

Answer 182

Question 183

Answer 183