DATABASE DESIGN & DEVELOPMENT FINAL EXAM
Table, Documents, Flat files - Answer- SQL has been evolving to retrieve data from which of the following? (Multiple answers are possible.)
True - Answer- Relational databases always report how many rows you insert, update, or delete.
TRUE OR FALSE
1986, 1992, 2016 - Answer- The American National Standards Institute (ANSI) established SQL standards in which of the following years? (Multiple answers are possible.)
extensions
False - Answer- A "database system" is nothing more than a set of related information.
TRUE OR FALSE
Generating data, Manipulating data, Retrieving data - Answer- SQL was initially created to be the language for which of the following? (Multiple answers are possible.)
False - Answer- Relational databases always report how many rows you change in the data dictionary when you CREATE a table.
True - Answer- You can access the errata at this page: https://www.oreilly.com/catalog/errata.csp?isbn=0636920274803
True - Answer- A "network database system" can work as a multi-parent hierarchy.
True - Answer- SELECT statements retrieves data.
True - Answer- A "network database system" exposes sets of records and links, where the links define relationships between different records.
False - Answer- "Primary keys" are examples of redundant data in relational database systems.
True - Answer- SQL transaction statements determine where to begin, end, and roll back transactions.
True - Answer- A "primary key" consisting of two or more columns is a compound key.
True - Answer- The roots of SQL go all the way back to the 1970s. JDBC, ADO.NET, Ruby DBI, Python DBI, Package Database/SQL - Answer- Which of the following are SQL integration toolkits? (Multiple answers are possible.)
T - Answer- Foreign keys in a relational database system are like lines that connect entities in a hierarchical or network database management system.
True - Answer- A "surrogate key" is generated by database management systems as a unique set of numbers.
True - Answer- The following WHERE clause filters on the combination of two criteria.
mysql> SELECT title -> FROM film -> WHERE rating = 'G' AND rental_duration >= 7;
having, select, from, where, order by - Answer- Which of the following are valid clauses in an SQL query? (Multiple answers are possible.)
A table created by using the CREATE TABLE statement. - Answer- Which of the following best describes a permanent table?
True - Answer- The following WHERE clause filters on the combination of one or the other of two criteria.
mysql> SELECT title -> FROM film -> WHERE (rating = 'G' OR rental_duration >= 7) -> AND (rating = 'PG' OR rental_duration <= 3);
True - Answer- A "database connection" gets generated when the server verifies your username and password are correct.
True - Answer- The following SELECT clause is the first clause of a SELECT statement but the last clause evaluated by the SQL engine.
Derived table - Answer- The following SELECT statement is using what type of table:
mysql> SELECT CONCAT(cust.last_name, ', ',cust.first_name) AS full_name -> FROM (SELECT first_name -> , last_name -> , email -> FROM customer -> WHERE first_name LIKE 'JE%') cust;
It displays the following result set:
+------------------+ | full_name | +------------------+ | DAVIS, JENNIFER | | HALL, JESSICA | | BELL, JEAN | | GREENE, JEANETTE | | LAWSON, JEANNE | | BANKS, JESSIE | | TERRY, JENNIE | | CASTRO, JENNY | | SPEAR, JEFFREY | | JORDON, JERRY | | HURTADO, JEREMY | | SCHILLING, JESSE | | PINSON, JEFFERY | | EAST, JEFF | | MCCARTNEY, JESUS | | KENYON, JEROME | | MILAM, JESSIE | +------------------+
True - Answer- "Column aliases" let you rename columns in the SELECT clause.
Temporary table - Answer- The following CREATE VIEW statement creates a customer_view of four columns from the customer table:
mysql> CREATE TEMPORARY TABLE actors_j -> ( actor_id smallint(5) -> , first_name varchar(45) -> , last_name varchar(45)); This inserts rows into the temporary table:
mysql> INSERT INTO actors_j -> SELECT actor_id -> , first_name -> , last_name -> FROM actor -> WHERE last_name LIKE 'J%';
The following SELECT statement is using what type of table:
mysql> SELECT * FROM actors_j;
The query displays the following result set:
+----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------
+-----------+ | 119 | WARREN | JACKMAN | | 131 | JANE | JACKMAN | | 8 | MATTHEW | JOHANSSON | | 64 | RAY | JOHANSSON | | 146 | ALBERT | JOHANSSON | | 82 | WOODY | JOLIE | | 43 | KIRK | JOVOVICH | +----------+------------+-----------+
True - Answer- A FROM clause with two tables requires a link between the tables inside
an ON clause.
MySQL Shell, MySQL - Answer- Which of the following can request and hold a MySQL "database connection"? (Multiple answers are possible.)
A view created by using the CREATE VIEW statement. - Answer- Which of the following
best describes a virtual table?
True - Answer- The following WHERE clause filters on the combination of two criteria.
mysql> SELECT title -> FROM film -> WHERE rating = 'G' AND rental_duration >= 7;
True - Answer- The following mysql client command lets you connect to the MySQL studentdb database:
[username@localhost ~] $ mysql -ustudent -p -Dstudentdb
True - Answer- The following GROUP BY clause lists the raw columns returned with the
result of an aggregating function, like the COUNT(), SUM() or others.
mysql> SELECT CONCAT(c.first_name,' ',c.last_name) AS full_name -> , COUNT(*) AS number_of_customer -> FROM customer c INNER JOIN rental r -> ON c.customer_id = r.customer_id -> GROUP BY c.first_name -> , c.last_name;
True - Answer- You define table aliases by appending <table_alias> after the table name in the FROM clause.
True - Answer- The following SELECT statement returns a string literal in a its result set:
mysql> SELECT 222 AS room_number -> , 'Kotter' AS teacher;
+-------------+---------+ | room_number | teacher | +-------------+---------+ | 222 | Kotter | +-------------+---------+
True - Answer- The following WHERE clause filters on the combination of one or the other of two criteria.
mysql> SELECT title -> FROM film -> WHERE (rating = 'G' OR rental_duration >= 7) -> AND (rating = 'PG' OR rental_duration <= 3);
