BYU-I CIT225 - W01

Database Design and Development - Week 1 - Notes from Textbook "Learning SQL" and Lecture Presentations

TRUE or FALSE: SQL schema statements define data structures.

TRUE

TRUE or FALSE: SQL transaction statements determine where to begin, end, and roll back transactions.

TRUE

TRUE or FALSE: The results of SQL schema statements are stored in a special set of tables called the data dictionary.

TRUE

TRUE or FALSE: You can access the errata at this page: https://www.oreilly.com/catalog/errata.csp?isbn=0636920274803Links to an external site..

TRUE

SQL has been evolving to retrieve data from which of the following? (Multiple answers are possible.)

* Table
* Webpages
* Comma-separated value files (CSV)
* Documents
* Flat files

* Table
* Documents
* Flat files

A "database system" does which of the following? (Multiple answers are possible.)

* Provide tools for locating a tree
* Store data in a computerized system
* Retrieve data from a computerized system
* Store data in cloud services
* Manage network packets

 

* Store data in a computerized system
* Retrieve data from a computerized system
* Store data in cloud services

TRUE or FALSE: A "database system" is nothing more than a set of related information.

FALSE

TRUE or FALSE: A "network database system" can work as a multi-parent hierarchy.

TRUE

TRUE or FALSE: A "database" is nothing more than a set of related information.

TRUE

TRUE or FALSE: A modern "database system" can manage more than gigabytes of data.

TRUE

What are SQL previous names?

SQUARE , SEQUEL

When was ANSI SQL first standard published?

1986

SQL was initially created to be the language for which of the following? (Multiple answers are possible.)

* Querying JSON
* Generating data
* Manipulating data
* Retrieving data
* Storing data

 

* Generating data
* Manipulating data
* Retrieving data

TRUE or FALSE: A "hierarchical database" system configures data in a single-parent hierarchy.

FALSE

TRUE or FALSE: The SEQUEL language was shortened to SQL.

TRUE

TRUE or FALSE: Relational databases always report how many rows you insert, update, or delete.

TRUE

TRUE or FALSE: A "surrogate key" is generated by database management systems as a unique set of numbers.

TRUE

The American National Standards Institute (ANSI) established SQL standards in which of the following years? (Multiple answers are possible.)

* 1986
* 1989
* 1992
* 1999
* 2003
* 2006
* 2008
* 2011
* 2016

*hierarchical database system*

data is represented as one or more tree structures (examples include Microsoft’s Active Directory, Apache Directory Server)

*single-parent hierarchy*

each node in the tree may have either zero or one parent and zero, one, or many children

*network database system*

exposes sets of records and sets of links that define relationships between different records

*multiparent hierarchy*

each node in the tree may have multiple parent and zero, one, or many children

*tables*

A set of rows, held either in memory (nonpersistent) or on permanent storage (persistent).

*rows*

A set of columns that together completely describe an entity or some action on an entity. Also called a record.

*columns*

An individual piece of data stored in a table.

*primary key*

information that uniquely identifies a row in that table 

One or more columns that can be used as a unique identifier for each row in a table

uniquely identify rows

*compound key*

is a primary key consisting of two or more columns

One or more columns that can be used together to identify a single row in another table.

*foreign keys*

One or more columns that can be used together to identify a single row in another table

are copies of primary keys

(redundant data) that let you join rows

the only redundant piece(s) of data in a database

*natural key*

A descriptive column or set of columns can be a primary key |

A set of descriptive columns is a compound key

*surrogate key*

A stand-in for the natural key

A sequence-driven number

A surrogate key must exist for each unique natural key

*normalization*

the process of refining a database design to ensure that each independent piece of information is in only one place, aside from foreign keys

*entity*

Something of interest to the database user community. Examples include customers, parts, geographic locations, etc.

*result set*

Another name for a nonpersistent table, generally the result of an SQL query.

*SQL schema statements*

used to define the data structures stored in the database

*SQL data statements*

used to manipulate the data structures previously defined using SQL schema statements

*SQL transaction statements*

used to begin, end, and roll back transactions

*procedural* *languages*

defines both the desired results and the mechanism, or process, by which the results are generated

*nonprocedural languages*

define the desired results, but the process by which the results are generated is left to an external agent.

*optimizer*

The optimizer’s job is to look at your SQL statements and, taking into account how your tables are configured and what indexes are available, decide the most efficient execution path (well, not always the *most* efficient).

*optimizer hints*

ways to influence the optimizer’s decisions

*Relational Model*

evolved from Hierarchical databases and Network databases and replaced networked database pointers with redundant data

TRUE or FALSE: SQL data statements manipulate data.

TRUE

TRUE or FALSE: "Foreign keys" are examples of redundant data in relational database systems.

TRUE

TRUE or FALSE: SQL transaction statements determine where to begin, end, and roll back transactions.

TRUE

TRUE or FALSE: A "database" is much more than a set of related information.

FALSE

TRUE or FALSE: A "surrogate key" is generated by database management systems as a unique set of numbers.

TRUE

TRUE or FALSE: The roots of SQL go all the way back to the 1970s.

TRUE

TRUE or FALSE: A primary key made up of descriptive columns, like fname and lname are known as surrogate keys.

FALSE

TRUE or FALSE: "Primary keys" are examples of redundant data in relational database systems.

FALSE

TRUE or FALSE: A "database" is nothing more than a set of related information.

TRUE

Which of the following are SQL integration toolkits? (Multiple answers are possible.)

* JDBC (Java Database Connectivity)
* ADO.NET (Microsoft)
* Ruby DBI
* Python DB
* Package database/sql

Table, Documents, Flat files

SQL has been evolving to retrieve data from which of the following? (Multiple answers are possible.)

Relational databases always report how many rows you insert, update, or delete.

TRUE OR FALSE

True

The American National Standards Institute (ANSI) established SQL standards in which of the following years? (Multiple answers are possible.)

1986, 1992, 2016

A "database system" is nothing more than a set of related information.

TRUE OR FALSE

extensions

False

SQL was initially created to be the language for which of the following? (Multiple answers are possible.)

Generating data, Manipulating data, Retrieving data

Relational databases always report how many rows you change in the data dictionary when you CREATE a table.

False

You can access the errata at this page:
https://www.oreilly.com/catalog/errata.csp?isbn\=0636920274803

True

A "network database system" can work as a multi-parent hierarchy.

True

SELECT statements retrieves data.

True

A "network database system" exposes sets of records and links, where the links define relationships between different records.

True

"Primary keys" are examples of redundant data in relational database systems.

False

SQL transaction statements determine where to begin, end, and roll back transactions.

True

A "primary key" consisting of two or more columns is a compound key.

True

The roots of SQL go all the way back to the 1970s.

True

Which of the following are SQL integration toolkits? (Multiple answers are possible.)

JDBC, ADO.NET, Ruby DBI, Python DBI, Package Database/SQL

Foreign keys in a relational database system are like lines that connect entities in a hierarchical or network database management system.

T

A "surrogate key" is generated by database management systems as a unique set of numbers.

True

The following WHERE clause filters on the combination of two criteria.
mysql\> SELECT title -\> FROM film -\> WHERE rating \= 'G' AND rental_duration \>\= 7;

True

Which of the following are valid clauses in an SQL query? (Multiple answers are possible.)

having, select, from, where, order by

Which of the following best describes a permanent table?

A table created by using the CREATE TABLE statement.

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

True

A "database connection" gets generated when the server verifies your username and password are correct.

True

The following SELECT clause is the first clause of a SELECT statement but the last clause evaluated by the SQL engine.

True

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 | +\------------------+

Derived table

"Column aliases" let you rename columns in the SELECT clause.

True

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 | +\----------+\------------+\-----------+

Temporary table

A FROM clause with two tables requires a link between the tables inside an ON clause.

True

Which of the following can request and hold a MySQL "database connection"? (Multiple answers are possible.)

MySQL Shell, MySQL

Which of the following best describes a virtual table?

A view created by using the CREATE VIEW statement.

The following WHERE clause filters on the combination of two criteria.
mysql\> SELECT title -\> FROM film -\> WHERE rating \= 'G' AND rental_duration \>\= 7;

True

The following mysql client command lets you connect to the MySQL studentdb database:
\[username@localhost ~] $ mysql -ustudent -p -Dstudentdb

True

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

You define table aliases by appending

True

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

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

True

The following mysql client command lets you connect to the MySQL studentdb database:
\[username@localhost ~] $ mysql -ustudent -p

False

A "condition" in the WHERE clause may compare equality between literal, column, or expression values.

True

A WHERE clause can only filter in some data and filter out other data.

True

A "condition" in the WHERE clause may compare inequality between literal, column, or expression values.

True

A "condition" in the WHERE clause may compare equality between literal, column, or expression values with the REGEXP regular expression operator.

True

A "range condition" in the WHERE clause may compare whether a literal, column, or expression values of a NULL value, like an optional middle_name column value, is between a set of literal, column, or expression values that exclude a NULL value.
mysql\> SELECT first_name -\> , last_name -\> FROM customer -\> WHERE middle_name BETWEEN 'FA' AND 'G';

False

A "condition" in the WHERE clause may compare a literal, column, or expression values to see if it is a null value by using the IS NULL operator. True or False

True

A "condition" in the WHERE clause may compare inequality, like \>, \>\=,

False

A WHERE clause can only filter in data.

False

A WHERE clause supports one or more "conditions."

True

Sometimes you will want to work with every row in a table to accomplish which of the following? (Multiple answers are possible.)

Purging all data from a table used to stage new data warehouse feeds.
Modifying all rows in a table after a new column has been added.
Retrieving all rows from a message queue table.

Which of the following "membership conditions" may replace a set of comparisons made against string literal values with the OR operator in the WHERE clause? (Multiple answers are possible.)
mysql\> SELECT title -\> , rating -\> FROM film -\> WHERE rating \= 'G' OR rating \= 'PG';

IN, \=ANY

A "range condition" in the WHERE clause may compare whether a literal, column, or expression values of a NULL value, like an optional middle_name column value, is not between a set of literal, column, or expression values that exclude a NULL value.
mysql\> SELECT first_name -\> , last_name -\> FROM customer -\> WHERE NOT middle_name BETWEEN 'FA' AND 'G';

True

A "range condition" in the WHERE clause may compare whether a literal, column, or expression values is between a set of literal, column, or expression values that include a NULL value.
mysql\> SELECT first_name -\> , last_name -\> FROM customer -\> WHERE last_name BETWEEN 'FA' AND NULL;

False