CIS275 - Chapter 4: Structured Query Language Flashcards
_____ is a high-level computer language for storing, manipulating, and retrieving data in a relational database.
Structured Query Language (SQL)
- A database designer uses SQL to create a database and the database tables.
- A database user uses SQL to insert, retrieve, update, and delete data from the tables.
An _____ is a complete command composed of one or more clauses.
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.
- The SELECT clause starts the statement. Name is a column name.
- The FROM clause must follow the SELECT clause. City is a table name.
- The WHERE clause is optional. When included, the WHERE clause must follow the FROM clause. Population > 100000 is a condition.
- 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.
The SQL language is divided into five sublanguages:
_____ defines the structure of the database.
Data Definition Language (DDL)
The SQL language is divided into five sublanguages:
_____ retrieves data from the database.
Data Query Language (DQL)
The SQL language is divided into five sublanguages:
_____ manipulates data stored in a database.
Data Manipulation Language (DML)
The SQL language is divided into five sublanguages:
______ controls database user access.
Data Control Language (DCL)
The SQL language is divided into five sublanguages:
_____ manages database transactions.
Data Transaction Language (DTL)
The _____ statement creates a new database.
CREATE DATABASE
Once a database is created, tables can be added to the database.
The _____ statement deletes the database, including all tables in the database.
DROP DATABASE
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:
_____ lists databases available in the database system.
SHOW DATABASES
Commonly used SHOW statements include:
_____ lists tables available in the currently selected database.
SHOW TABLES
Commonly used SHOW statements include:
_____ lists columns available in a specific table named by a FROM clause.
SHOW COLUMNS
Commonly used SHOW statements include:
_____ shows the CREATE TABLE statement for a given table.
SHOW CREATE TABLE
The ___ statement selects a database and is required to show information about tables within a specific database.
USE
A _____ is a number that may be negative.
signed number
An _____ is a number that cannot be negative.
unsigned number
Integer
1 byte
Signed range: -128 to 128
Unsigned range: 0 to 255
TINYINT
Integer
2 bytes
Signed range: -32,768 to 32,767
Unsigned range: 0 to 65,535
SMALLINT
Integer
3 bytes
Signed range: -8,388,608 to 8,388,607
Unsigned range: 0 to 16,777,215
MEDIUMINT
Integer
4 bytes
Signed range: -2,147,483,648 to 2,147,483,647
Unsigned range: 0 to 4,294,967,295
INTEGER or INT
8 bytes
Signed range: -2^63 to 2^63 -1
Unsigned range: 0 to 2^64 -1
BIGINT
Decimal
Varies depending on M and D
Exact decimal number where M = number of significant digits,
D = number of digits after decimal point
DECIMAL(M,D)
Decimal
4 bytes
Approximate decimal numbers with range: -3.4E+38 to 3.4E+38
FLOAT
Decimal
8 bytes
Approximate decimal numbers with range: -1.8E+308 to 1.8E+308
DOUBLE
Date and time
3 bytes
Format: YYYY-MM-DD. Range: ‘1000-01-01’ to ‘9999-12-31’
DATE
Date and time
3 bytes
Format: hh:mm:ss
TIME
Date and time
5 bytes
Format: YYYY-MM-DD hh:mm:ss.
Range: ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
DATETIME
Character
N bytes
Fixed-length string of length N; 0 ≤ N ≤ 255
CHAR(N)
Character
Length of characters + 1 bytes
Variable-length string with maximum N characters; 0 ≤ N ≤ 65,535
VARCHAR(N)
The _____ statement creates a new table by specifying the table name, column names, and column data types.
CREATE TABLE
The _____ statement deletes a table, along with all the table’s rows, from a database.
DROP TABLE
Ex: DROP TABLE Employee; deletes the Employee table.
The _____ statement adds, deletes, or modifies columns on an existing table.
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.
- The ALTER TABLE statement adds a Salary column to Employee that holds 7 significant digits and 2 decimal places.
- The ALTER TABLE statement changes the Salary column’s name to AnnualSalary. The data type is changed to INT.
- The ALTER TABLE statement deletes the AnnualSalary column.
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.
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.
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.
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.
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.
A _____ is a rule that applies to table data.
constraint
Constraints are specified in a CREATE TABLE statement or may be added to a preexisting table with an ALTER TABLE statement.
The _____ in a CREATE TABLE statement names the table’s primary key, the column(s) that uniquely identify each row.
PRIMARY KEY constraint
An auto-increment column is a column that is assigned an automatically incrementing value.
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.
A _____ is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords.
foreign key constraint
- The Employee table has primary key ID and several rows.
- The Department table is created with a FOREIGN KEY constraint that REFERENCES the Employee ID column.
- 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.
_____ responds to an invalid primary key deletion. Ex: Deleting a primary key 1234 that is used in a foreign key.
ON DELETE
ON DELETE and ON UPDATE must be followed by a response:
RESTRICT
SET NULL
SET DEFAULT
CASCADE
_____ responds to an invalid primary key update. Ex: Updating a primary key 1234 to 5555 when 1234 is used in a foreign key.
ON UPDATE
ON DELETE and ON UPDATE must be followed by a response:
RESTRICT
SET NULL
SET DEFAULT
CASCADE
Expected:
PRIMARY KEY (ISOCode2)
PRIMARY KEY (ISOCode2) sets ISOCode2 as the primary key of the table.
Expected: PRIMARY KEY (ISOCode2, ISOCode3)
PRIMARY KEY (ISOCode2, ISOCode3) sets ISOCode2 and ISOCode3 as the composite primary key of the table.
Expected: PRIMARY KEY (TLD, ISOCode3)
PRIMARY KEY (TLD, ISOCode3) sets TLD and ISOCode3 as the composite primary key of the table.
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.
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.
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.
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.
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.
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.
NOT NULL
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.
DEFAULT constraint
- 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.
- The BirthDate column has a DEFAULT constraint, so January 1, 2000 is used if a row is inserted with no specified birth date.
- The Salary column has a DEFAULT constraint, so salary is 0.00 if no salary is specified when inserting a new row.
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.
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.
UNIQUE constraint
column-level constraint
table-level constraint
- The Username column has a UNIQUE column-level constraint, so each Username must be different.
- Attempting to insert a second row with username ‘mrodriguez’ fails because ‘mrodriguez’ already exists.
- The UNIQUE table-level constraint requires each combination of Name and Extension be different.
- Inserting Maria Rodriquez with extension 4888 does not violate the UNIQUE constraint because (Maria Rodriguez, 5050) and (Maria Rodriguez, 4888) are different.
- Attempting to insert Maria Rodriquez with extension 5050 fails because (Maria Rodriguez, 5050) already exists.
The _____ specifies an expression that limits the range of a column’s values.
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.
- The CHECK column-level constraint ensures all rows have a HireDate between Jan 1, 2000 and Dec 12, 2019.
- 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.
- The CHECK table-level constraint requires BirthDate to come before HireDate.
- Inserting Sam fails because the BirthDate Nov 29, 2003 is after HireDate Nov 1, 2003.
- The NULL BirthDate makes the table-level CHECK constraint evaluate to UNKNOWN, which does not violate the constraint.
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.
CONSTRAINT keyword
Constraint names can help a database administrator or programmer identify which constraint is being violated in a database error message.
- The CONSTRAINT keyword gives the CHECK and UNIQUE constraints user-defined names.
- The database uses constraint names in error messages. Fred’s HireDate is not >= 2000-01-01.
- The ALTER TABLE statement drops the HireCheck constraint. Any HireDate may now be added to Employee.
- The ALTER TABLE statement adds a new constraint that ensures the HireDate is before Feb 14, 2000. Emma’s HireDate violates the new constraint.
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.
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.
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.
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.
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.
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.
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.
The _____ adds rows to a table.
INSERT statement
The INSERT statement includes the INTO and VALUES clauses:
INSERT syntax:
INSERT INTO TableName (Column1, Column2, …, ColumnN)
VALUES (Value1, Value2, …, ValueN);
- The INSERT statement’s INTO clause names the Employee table and Employee’s columns in parentheses.
- The VALUES clause names the column values in parenthesis. The value order must match the column order in the INTO clause.
- 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.
- Any number of rows may be added with a single INSERT statement.
The _____ names the table and columns where data is to be added.
INTO clause
- The INSERT statement’s INTO clause names the Employee table and Employee’s columns in parentheses.
- The VALUES clause names the column values in parenthesis. The value order must match the column order in the INTO clause.
- 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.
- Any number of rows may be added with a single INSERT statement.
The _____ specifies the column values to be added.
VALUES clause
The VALUES clause may list any number of rows in parentheses to insert multiple rows.
- The INSERT statement’s INTO clause names the Employee table and Employee’s columns in parentheses.
- The VALUES clause names the column values in parenthesis. The value order must match the column order in the INTO clause.
- 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.
- Any number of rows may be added with a single INSERT statement.
Database users make some common errors when creating INSERT statements:
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.
- 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.
- If ID is an auto-increment column, the ID should not be listed in the INSERT statement. The database assigns the ID automatically.
- If Salary is a NOT NULL column, then the Salary value must be specified.
The _____ modifies existing rows in a table.
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;
The ____ deletes existing rows in a table.
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;
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.
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.
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.
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.
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.
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.
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.
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.
The _____ selects rows from a table named in the _____.
SELECT statement
FROM clause
The data returned from the SELECT statement, called the _____, is stored in a _____.
result set
result table
The SELECT statement retrieves all rows by default. The _____ is combined with the SELECT statement to filter the result set.
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.
an expression that evaluates to TRUE, FALSE, or NULL.
condition
Only a condition that is TRUE selects the row.
A condition uses a _____ to compare two values.
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.
The WHERE clause supports _____ AND, OR, and NOT.
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.
- The SELECT statement selects only rows WHERE percentage is between 0.0 and 10.0. Two rows are returned.
- The SELECT statement selects only rows WHERE percentage < 5.0 OR percentage > 90.0. Two rows are returned.
The SELECT statement supports _____, which perform calculations on two operands.
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 *.
- The Customer table tracks each customer’s balance and payment information.
- The SELECT and WHERE clauses use subtraction to determine which customers owe more than $1000 after making a payment. Two rows are returned.
- The WHERE clause uses multiplication to determine which customers can pay off their balance after making 10 payments. Two rows are returned.
Expected:
Left: ISOCode2, ISOCode3, PopDensity
Right: PopDensity
SELECT * selects all columns.
SELECT PopDensity selects only the PopDensity column.
Expected: Senegal
WHERE Name = ‘Senegal’ specifies only the countries where Name is equal to ‘Senegal’.
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.
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.
Expected: 20410000
Guinea is the only country for which PopDensity > 105. The Population of Guinea is 12410000, so 12410000 + 8000000 is returned.
Two operators are used to test for NULL in a WHERE clause:
_____ tests if a value is NULL.
IS NULL
Two operators are used to test for NULL in a WHERE clause:
_____tests if a value is not NULL.
IS NOT NULL
The _____ is used with a SELECT statement to return only unique or ‘distinct’ values.
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.
The _____ is used in a WHERE clause to determine if a value matches one of several values.
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.
The _____, when used in a WHERE clause, matches text against a pattern using the two wildcard characters _____.
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”.
The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the _____.
BINARY keyword.
Ex: LIKE BINARY ‘L%t’ matches ‘Left’ but not ‘left’.
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.
ORDER BY clause
DESC keyword
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.
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.
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.
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.
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.
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.
MySQL has functions for processing strings, numbers, dates, and times. An _____ is a function that works on a group of values.
Aggregate functions
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.
COUNT()
MIN()
MAX()
SUM()
AVG()
- The SUM() function sums the Population values in each group.
- The GROUP BY clause forms groups based on the CountryCode column.
- CountryCode contains two unique values: ZMB and ZWE. So two rows are returned with the total population of each CountryCode value.
- The COUNT() function counts how many rows exist in each group.
- The groups are formed by the CountryCode and District columns.
- Each unique CountryCode and District combination is counted.
- The SUM() function sums the Population values in each group.
- The GROUP BY clause forms groups based on the CountryCode column.
- CountryCode contains two unique values: ZMB and ZWE. So two rows are returned with the total population of each CountryCode value.
- The COUNT() function counts how many rows exist in each group.
- The groups are formed by the CountryCode and District columns.
- Each unique CountryCode and District combination is counted.
- The HAVING clause follows the GROUP BY clause.
- 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.
- 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.
SQL _____ perform useful calculations beyond simple addition, subtraction, multiplication, and division.
numeric functions
SQL _____ manipulate strings.
string functions
_____ operate on DATE, TIME, and DATETIME data types.
Date and time functions
- 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.
- The subquery executes first to find the official language Percentage for ABW, which is 5.3.
- The outer query executes using the value 5.3 returned by the subquery. Three languages have Percentage > 5.3
- The SELECT statement uses the IN operator with a subquery to determine which Languages are used in Europe.
- The subquery first finds all Codes from Europe: ALB and AND.
- The outer query then selects the CountryCode and Language for the CountryCodes ALB and AND.
- 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.
- 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.
- 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.
- Then the outer query executes using the average population returned from the subquery. Buenos Aires has a population 2982146 > 2124303.5.
- 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.
- The outer query finds São Paulo also has a population > BRA’s average population.
- Rio de Janeiro is not selected because Rio de Janeiro’s population 5598953 is not > 7783719.
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.
EXISTS operator
NOT EXISTS
- 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.
- 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.
- 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.
- Rio de Janeiro is also selected because at least one BRA row has Percentage > 97.
