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