CIT 225 Final Exam Study Guide
The American National Standards Institute (ANSI) established SQL standards in which years?
1986, 1992, 2016
What is a subquery?
A query contained within another SQL statement.
What syntax is used to create a temporary table?
CREATE TEMPORARY TABLE table_name
What syntax is used to create a virtual table (a view)?
CREATE VIEW view_name
A "range condition" in the WHERE clause may compare whether a NULL value, like an optional middle_name column value, is between a set of values that excludes a NULL value. In other words, can you compare a NULL to a range of values?
FALSE
A Cartesian product has a one-to-one result of combining two tables and does not multiply the number of rows.
FALSE
A LEFT JOIN can return values in the right table that do not have a corresponding value in the left table.
FALSE
A WHERE clause can only contain one condition
FALSE
A single query that excludes the result of a subquery returns the same result as two queries joined by the except operator when the second query matches the excluded subquery in the current release of MySQL (8.0.21).
FALSE
Does the simple CASE expression support the following operators? >= IN LIKE
FALSE
Is a FULL JOIN supported by ANSI SQL92?
FALSE
Is it mandatory to use AS when declaring an alias?
FALSE
Microsoft's "Active Directory" is a network database system.
FALSE
MySQL uses the MINUS set operator.
FALSE
MySQL's CONCAT() function is available in Oracle and performs exactly the same way as it does in MySQL.
FALSE
MySQL's conditional logic IF() function is part of the SQL standard (SQL92).
FALSE
The AVG() function returns the mode of a set of values
FALSE
The CAST function lets you convert a hexadecimal value to an extended character defined in the character set.
FALSE
The CAST() function in MySQL lets you convert a string that does not comply with the default date format.
FALSE
The MySQL proprietary (exclusive to MySQL) conditional logic function is the COALESCE() function.
FALSE
The MySQL proprietary (exclusive to MySQL) conditional logic function is the DECODE() function.
FALSE
The char function by itself lets you display accented characters, like å in the latin1 character set.
FALSE
The describe command only works for tables and not views.
FALSE
The following query returns all columns from the language table. SELECT name FROM language;
FALSE
The type of CASE statement shown below is searched, not simple. CASE (SELECT COUNT(*) FROM inventory i -> WHERE i.film_id = f.film_id) WHEN 0 THEN 'Out of Stock' WHEN 1 THEN 'Scarce' -> WHEN 2 THEN 'Scarce' WHEN 3 THEN 'Available' WHEN 4 THEN 'Available' ELSE 'Common' END AS availability
FALSE
Views can store aggregated data columns only when the constructing query uses column aliases.
FALSE
Views introduce design complexity by exposing complicated queries.
FALSE
When you execute the CREATE VIEW statement, the database server stores the definition and immediately executes it.
FALSE
The following query returns only Harry Potter films with prequels. SELECT DISTINCT f.title AS film, CASE WHEN NOT f.film_id = fp.film_id AND f.prequel_id = fp.film_id THEN fp.title END AS prequel_id FROM film f RIGHT JOIN film fp ON f.prequel_id = fp.film_id OR f.prequel_id IS NULL WHERE f.series_name = 'Harry Potter' AND fp.series_name = 'Harry Potter' ORDER BY f.series_number;
FALSE, it seems to also return Harry Potter films with no prequel because of the OR clause
SQL was initially created to be the language for which tasks involving data?
Generating, Manipulating, and Retrieving Data
Which JOINS require an ON clause?
INNER JOIN, LEFT JOIN, and RIGHT JOIN
The ____ JOIN returns all rows from the table on the left side of the join and only the rows that meet the join condition on the right side of the join.
LEFT
Which situations require working with every row in a table?
Retrieving all rows, modifying all rows, or deleting all the data in a table.
"Apache Directory Server" is a hierarchical database system.
TRUE
A "condition" in the WHERE clause may compare inequality, like IN, between literal, column, or expression values and a lists of values.
TRUE
A "range condition" in the WHERE clause may compare whether a NULL value, like an optional middle_name column value, is NOT between a set of values that excludes a NULL value.
TRUE
A CROSS JOIN returns the cartesian product of two tables
TRUE
A FROM clause with two tables generally requires a link between the tables inside an ON clause.
TRUE
A SELECT statement retrieves data.
TRUE
A modern "database system" can manage many gigabytes of data.
TRUE
A self-join between two copies of the same table is accomplished through a primary key and foreign key in the same table
TRUE
A view is simply a mechanism for querying data.
TRUE
COUNT and COUNT DISTINCT can return different things, all other factors being kept the same?
TRUE
Conditional logic is simply the ability to take one of several paths during program execution.
TRUE
Data is generally stored at the lowest level of granularity.
TRUE
Does the following query return all the customers with last names that start with D, but exclude those whose last names start with Di? SELECT first_name, last_name FROM customer WHERE last_name LIKE 'D%' AND (first_name,last_name) NOT IN (SELECT first_name, last_name FROM customer WHERE last_name LIKE 'DI%');
TRUE
Does the following query return one column with three rows? mysql> SELECT * -> FROM (SELECT 'Yes' AS reply -> UNION ALL -> SELECT 'No' AS reply -> UNION ALL -> SELECT 'Maybe' AS reply) r;
TRUE
GROUP BY creates an explicit group in a query
TRUE
In set theory, A except B leaves you with the rows in set A not found in set B, which is known as the complement of B.
TRUE
In set theory, B except A leaves you with the rows in set B not found in set A, which is known as the complement of A.
TRUE
In the following query, should = be replaced with IN? SELECT country_id FROM country WHERE country = ('Canada','Mexico'); (A single value can't be equal to a list, but it can be IN a list.)
TRUE
In the following query, should ANY be used instead of ALL? SELECT first_name , last_name FROM customer WHERE customer_id = ALL (SELECT customer_id FROM payment WHERE amount = 0);
TRUE
MySQL supports joins between a table and the result from query because the query's result becomes a temporary table.
TRUE
MySQL's conditional logic CASE expression is part of the SQL standard (SQL92).
TRUE
Noncorrelated subqueries can be successfully run on their own as a separate query.
TRUE
Queries using set operators can only have one ORDER BY clause.
TRUE
Relational databases always report how many rows you insert, update, or delete.
TRUE
Subqueries can be used as expression generators in SQL (they can be used in comparisons).
TRUE
The COUNT function returns the number of column values
TRUE
The DATE_ADD() function in MySQL lets you add a specified number of days to a date. SELECT DATE_ADD('2020-12-20', INTERVAL 5 DAY) AS christmas_day;
TRUE
The DATE_DIFF() function in MySQL lets you find the number of days between two dates. SELECT DATE_DIFF('2021-02-14','2020-12-25') AS days_between;
TRUE
The HAVING clause filters an aggregated result set like a WHERE clause filters a raw result set.
TRUE
The MAX function creates an implicit group when it is the only thing returned in the SELECT-list.
TRUE
The MySQL date function EXTRACT can return the hour, day, week, month, quarter, and even the year from a timestamp.
TRUE
The SUM function adds a set of values
TRUE
The UNION ALL set operator doesn't remove duplicate rows.
TRUE
The UNION set operator removes duplicate rows.
TRUE
The first table in the FROM clause is the primary table in a JOIN.
TRUE
The following SELECT statement returns a string literal in a its result set: SELECT 222 AS room_number, 'Kotter' AS teacher;
TRUE
The following command creates a table named "test": CREATE TABLE test (test_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,test_name VARCHAR(20));
TRUE
The following creates a view with an obfuscated column. mysql> CREATE VIEW customer_vw -> ( customer_id -> , first_name -> , last_name -> , email ) AS -> ( SELECT customer_id -> , first_name -> , last_name -> , CONCAT(SUBSTR(email,1,2),'*****',SUBSTR(email,-4)) email -> FROM customer;
TRUE
The following query returns all columns from the language table. SELECT * FROM language;
TRUE
The following query returns only Harry Potter films with prequels. SELECT f.title AS film, fp.title AS prequel FROM film f LEFT JOIN film fp ON f.prequel_id = fp.film_id WHERE f.series_name = 'Harry Potter' AND fp.series_name = 'Harry Potter' ORDER BY f.series_number;
TRUE
The following query uses the CASE expression to return 'ACTIVE' or 'INACTIVE'. SELECT CONCAT(c.last_name,', ',c.first_name) AS actor_name, CASE WHEN c.active = 1 THEN 'ACTIVE' ELSE 'INACTIVE' END AS activity_type
TRUE
The following query uses valid syntax pertaining to subqueries: SELECT city_id, city FROM city WHERE country_id IN (SELECT country_id FROM country WHERE country IN ('Canada','Mexico'));
TRUE
The function IS NULL is the correct syntax to locate and return NULL values from a dataset
TRUE
The query demonstrates result set transformations with existence checking. mysql> SELECT a.first_name -> , a.last_name -> , CASE -> WHEN EXISTS (SELECT NULL -> FROM film_actor fa INNER JOIN film f -> ON fa.film_id = f.film_id -> WHERE fa.actor_id = a.actor_id -> AND f.rating = 'G') THEN 'Y' -> ELSE 'N' -> END AS g_actor -> FROM actor a -> WHERE a.last_name LIKE 'ki%';
TRUE
The roots of SQL go all the way back to the 1970s.
TRUE
The syntax STRAIGHT_JOIN makes it so that the tables are joined in the order they appear in the query.
TRUE
This subquery returns rows in the film_actor table that no longer support valid relationships between the actor and film tables. SELECT fa.actor_id, fa.film_id FROM film_actor fa WHERE NOT EXISTS (SELECT NULL FROM actor a INNER JOIN film f WHERE a.actor_id = fa.actor_id AND f.film_id = fa.film_id);
TRUE
Views are not useful when you want to join partitioned tables with inner joins. (You should not join partitioned tables using an INNER JOIN since they do not share any data.)
TRUE
Views are useful when you want to join partitioned tables with set operators.
TRUE
Views can store data that appears preaggregated because the view definition that is stored in the database aggregates the data behind the scenes.
TRUE
You can combine the result set of a LEFT JOIN with other LEFT JOINs in the same query.
TRUE
You can use subqueries as data sources because their result sets become temporary tables.
TRUE
You create a view by assigning a name to a CREATE VIEW statement containing a SELECT statement, and then storing the query for others to use.
TRUE
You do not have to specify the join condition in a NATURAL JOIN
TRUE
You perform a set operation by placing a set operator (UNION, INTERSECT, or EXCEPT) between two select statements.
TRUE
You see the following when connecting to the mysqlsh Shell: MySQL Shell 8.0.21 Copyright (c) 2016... Type '\help' or '\?' for help; '\quit' to exit. MySQL JS >
TRUE
SQL has been evolving to retrieve data from which sources?
Table, documents, flat file
What are the valid set operators in MySQL?
UNION, INTERSECT, and EXCEPT
MySQL standard date format
YYYY-MM-DD HH:MI:SS