Final Exam CIT225 Flashcards
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 |
+——————+
17 rows in set (0.50 sec)
Yes, the subquery table in the FROM clause of the outer query can also be referred to as a derived table or an inline view.
A derived table is a virtual table that is created as a result of a subquery, and it can be used in the same way as a physical table. In the given example, the subquery is retrieving data from the “customer” table and filtering it to include only records where the first name starts with ‘JE’. The result of the subquery is then used as a derived table that can be selected from in the outer query.
An inline view is another term used to describe a derived table, and refers to a subquery that is used in the FROM clause of another query. In the given example, the subquery is being used as an inline view in the FROM clause of the outer query.
So, the subquery table in the example can be called a derived table, an inline view, or a subquery table, all of which refer to the same concept of using a subquery to create a virtual table that can be used in another query.
A derived table and a virtual table are both table-like structures that are created as a result of a query, but they have some differences in how they are created and used.
A derived table is created as a result of a subquery that is included in the FROM clause of another query. The subquery is executed first, and its result set is used to create a temporary table, also known as a derived table or an inline view, that can be referenced in the outer query. A derived table is essentially a virtual table that is created on-the-fly from a subquery.
A virtual table, on the other hand, can be created in several ways. It can be created from a view, which is a named query that defines a specific set of columns and rows from one or more tables in the database. It can also be created from a common table expression (CTE), which is a named temporary result set that can be referenced in subsequent queries. In addition, some databases support table-valued functions (TVFs), which are user-defined functions that return a result set that can be used as a virtual table.
In summary, a derived table is a type of virtual table that is created as a result of a subquery in the FROM clause of another query. A virtual table, on the other hand, is a more general term that refers to any table-like structure that is created from a query, including views, CTEs, and TVFs.
True or False: A “surrogate key” is generated by database management systems as a unique set of numbers.
True
A surrogate key is a unique identifier that is added to a database table to serve as its primary key. It is typically generated by the database management system and is not derived from the data in the table. Surrogate keys are often used when there is no natural or obvious primary key in the data itself.
Surrogate keys can be generated using a variety of techniques, such as using an auto-incrementing integer or a UUID (Universally Unique Identifier). The key is then assigned to each row in the table, providing a unique identifier for that row.
The use of surrogate keys can simplify database design and management, as it eliminates the need to use composite keys or other complex key structures. It also ensures that each row in the table has a unique identifier, making it easier to reference and manipulate data.
True or False: A “network database system” can work as a multi-parent hierarchy
True
Yes, a network database system can work as a multi-parent hierarchy. In a network database model, data is organized as a collection of records, each containing one or more fields, and each record can have multiple parent and child records. This type of database model is often used for complex data structures that cannot be easily represented in a relational database model.
In a multi-parent hierarchy, a record can have multiple parent records, which allows for more complex relationships between data. This is different from a traditional hierarchy where each record has only one parent record. In a network database model, the relationships between records are represented by pointers or links that connect the child record to one or more parent records.
The ability to represent multi-parent hierarchies in a network database system can be useful in applications where data has complex relationships, such as in project management, supply chain management, or genealogy. However, it is important to note that network database systems can be more complex and difficult to manage than relational database systems, and may require specialized skills and expertise to design and maintain.
True or False: SELECT statements retrieve data
True
Yes, SELECT statements are used to retrieve data from a database. In SQL (Structured Query Language), SELECT statements are used to query a database table or view and return a result set that contains the data that matches the specified criteria.
A typical SELECT statement consists of the SELECT keyword, a list of columns to retrieve data from (or an asterisk to select all columns), the FROM keyword followed by the name of the table or view, and optional clauses such as WHERE, ORDER BY, GROUP BY, and HAVING.
For example, the following SELECT statement retrieves all the columns from a table named “employees” where the employee’s job title is “Manager”:
SELECT *
FROM employees
WHERE job_title = ‘Manager’;
This statement would return a result set that contains all the columns (e.g., employee ID, name, job title, hire date, etc.) for all employees in the “employees” table whose job title is “Manager.”
SELECT statements are essential for retrieving and analyzing data from a database and are used extensively in data analytics, business intelligence, and other data-related applications.
Which of the following are SQL integration toolkits?
The term “SQL integration toolkits” can refer to various software tools or frameworks that are designed to help integrate SQL-based systems with other applications or technologies. Some examples of SQL integration toolkits include:
ODBC (Open Database Connectivity) - A standard API for accessing and integrating data from various database management systems using SQL.
JDBC (Java Database Connectivity) - A Java-based API that provides access to SQL databases and allows Java programs to interact with them.
ADO.NET (ActiveX Data Objects .NET) - A data access technology in the .NET Framework that provides a consistent interface for accessing data from different databases using SQL.
SQLAlchemy - A Python-based toolkit that provides a set of high-level APIs for working with SQL-based databases and integrating them with other Python applications.
Django ORM (Object-Relational Mapping) - A Python-based ORM that allows developers to interact with SQL databases using Python objects and provides a high-level abstraction for working with data.
Ruby DBI is a database interface library for Ruby programming language that provides a uniform API for various relational database systems. It supports many databases including MySQL, Oracle, PostgreSQL, SQLite, and more. It is not a SQL integration toolkit per se, but it does allow Ruby programs to interact with SQL-based databases.
Python DB is not a specific tool or framework but rather a term that can refer to any of the various Python-based database APIs, such as Python DB-API, SQLAlchemy, Django ORM, and others. These APIs provide different levels of abstraction and functionality for working with SQL-based databases from within Python.
These are just a few examples of SQL integration toolkits. There are many other tools and frameworks available that can help developers integrate SQL-based systems with other technologies and applications.
What was SQL originally created for?
SQL (Structured Query Language) was originally created in the early 1970s by IBM researcher Edgar F. Codd as a way to manage and manipulate data stored in relational database systems. It was designed to be a declarative language that allowed users to express complex queries in a concise and easily understandable way.
SQL was originally developed as part of the System R project at IBM, which aimed to create a prototype of a relational database management system. The language was later standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), leading to the development of various SQL-based relational database management systems such as Oracle, Microsoft SQL Server, MySQL, and PostgreSQL.
Today, SQL is the standard language for managing and querying data in relational database systems, and is widely used in various industries and applications, including finance, healthcare, e-commerce, and more. The language has evolved over time and has become more feature-rich, with new functionality added to support more complex data structures, analytical processing, and other advanced data-related tasks.
The following query returns all columns from the language table.
mysql> SELECT name
-> FROM language;
It returns a result like the following:
+———-+
| name |
+———-+
| English |
| Italian |
| Japanese |
| Mandarin |
| French |
| German |
+———-+
6 rows in set (0.04 sec)
False
Each “database connection” to the MySQL server is assigned an identifier, which is shown to you when you first log in to your account.
No, this statement is incorrect. When you first log in to a MySQL account, you are not assigned a database connection identifier. Instead, a new connection is established to the MySQL server for each client that connects to it.
Each connection to the MySQL server is identified by a unique connection ID, which is automatically assigned by the server. You can obtain the connection ID for a particular connection by running the CONNECTION_ID() function in MySQL. This function returns the ID of the current connection, which is a positive integer value.
It’s important to note that connections to the MySQL server are not the same as user accounts. User accounts are used to authenticate and authorize users to access databases and perform various operations, while connections are used to establish a communication channel between the client and the server for executing SQL statements and retrieving data.
In summary, each client that connects to a MySQL server is assigned a unique connection ID, which identifies the connection during its lifetime. User accounts, on the other hand, are used to authenticate and authorize users to perform various operations within the databases they have access to.
A FROM clause with two tables requires a link between the tables inside an ON clause.
Yes, in most cases, when you use a FROM clause with two or more tables in a SQL query, you need to specify a join condition in an ON clause to link the tables together. The join condition specifies how the rows from the different tables should be matched based on a common column or set of columns.
For example, consider the following query that joins two tables, orders and customers, using an inner join:
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
In this query, the two tables are linked together using the customer_id column, which is present in both tables. The ON clause specifies the join condition, which is orders.customer_id = customers.customer_id. This condition ensures that only the rows from the two tables that have matching customer_id values are included in the result set.
It’s worth noting that there are different types of join operations, such as inner join, left join, right join, and full outer join, each with their own syntax and rules for specifying the join condition. In general, however, when joining two or more tables in a SQL query, you need to specify a join condition in an ON clause to link the tables together.
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 exclude a NULL value.
mysql> SELECT first_name
-> , last_name
-> FROM customer
-> WHERE last_name BETWEEN ‘FA’ AND ‘G’;
Yes, a range condition in the WHERE clause of a SQL query can compare whether a literal, column, or expression value is between a set of literal, column, or expression values, and exclude a NULL value.
In the example query you provided, the BETWEEN operator is used to compare the values in the last_name column of the customer table to a range of values, specifically from ‘FA’ to ‘G’. The BETWEEN operator includes both the starting and ending values of the range, so in this case, it would include any last names that start with the letters ‘FA’, ‘FB’, …, ‘G’.
It’s worth noting that when using the BETWEEN operator, the comparison is performed in ascending order. So in the example query, all last names that come between ‘FA’ and ‘G’ in alphabetical order would be included in the result set.
Additionally, when using the BETWEEN operator, it’s important to be aware of how NULL values are handled. By default, NULL values are excluded from the range comparison, so any rows with a NULL value in the last_name column would not be included in the result set. If you want to include NULL values in the range comparison, you can use the IS NULL or IS NOT NULL operators to explicitly test for NULL values.
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’;
No, a “range condition” in the WHERE clause cannot compare a NULL value to a set of literal, column, or expression values that exclude NULL.
In the example query you provided, the middle_name column is being compared to a range of values using the BETWEEN operator. If any row in the customer table has a NULL value in the middle_name column, it would not be included in the result set because NULL values are excluded by default when using the BETWEEN operator.
To include rows with NULL values in the comparison, you can use the IS NULL or IS NOT NULL operators. For example, you could modify the query to include rows with NULL middle names by using a WHERE clause like this:
SELECT first_name, last_name
FROM customer
WHERE middle_name BETWEEN ‘FA’ AND ‘G’ OR middle_name IS NULL;
This would include any rows where the middle_name column value is between ‘FA’ and ‘G’, or where the middle_name column is NULL.
A “condition” in the WHERE clause may compare inequality, like IN, between literal, column, or expression values and a lists of values.
mysql> SELECT ‘True’ AS answer
-> WHERE ‘a’ IN (SELECT ‘a’ UNION SELECT ‘b’ UNION SELECT ‘c’);
Yes, a “condition” in the WHERE clause can compare inequality using operators like IN, which tests if a value matches any value in a list of values.
In the example query you provided, the WHERE clause is using the IN operator to check if the literal value ‘a’ is present in the list of values returned by the subquery. If ‘a’ is present in the subquery, the query will return a single row with the value ‘True’ in the answer column.
Here’s how the query works:
The subquery SELECT ‘a’ UNION SELECT ‘b’ UNION SELECT ‘c’ creates a list of three values: ‘a’, ‘b’, and ‘c’.
The WHERE clause checks if the literal value ‘a’ is present in the list of values from the subquery. Since ‘a’ is present, the condition is true.
The SELECT clause returns a single row with the value ‘True’ in the answer column.
So the output of the query would be:
+——–+
| answer |
+——–+
| True |
+——–+
1 row in set (0.00 sec)
A “condition” in the WHERE clause that may compare equality between literal, column, or expression values and use which of the following wildcards?
A “condition” in the WHERE clause that may compare equality between literal, column, or expression values can use the wildcard character % for pattern matching.
The % wildcard matches any sequence of zero or more characters, and it can be used in conjunction with the LIKE operator to perform pattern matching. For example, the following query retrieves all rows from the customer table where the last_name column starts with the letter ‘S’:
SELECT *
FROM customer
WHERE last_name LIKE ‘S%’;
This query will match any last name that starts with the letter ‘S’, followed by any sequence of zero or more characters. So it will match ‘Smith’, ‘Simpson’, ‘Santana’, and so on.
Yes, the asterisk symbol (*) is a wildcard character that is used in SQL to select all columns or all rows from a table.
When used in a SELECT statement, * selects all columns from the specified table. For example, the following query selects all columns from the customer table:
SELECT * FROM customer;
When used in a DELETE or UPDATE statement, * selects all rows from the specified table. For example, the following query deletes all rows from the customer table:
DELETE FROM customer WHERE *;
However, it’s generally not recommended to use * in production code because it can have unintended consequences, such as selecting too much data or creating performance issues. It’s better to explicitly list the columns or rows that you need to select or modify in your SQL statements.
Yes, the underscore symbol (_) is a wildcard character that is used in SQL to match any single character in a string.
It is typically used in conjunction with the LIKE operator to perform pattern matching on character strings. For example, the following query selects all rows from the customer table where the last name starts with the letter “S” and the second letter is any character:
SELECT * FROM customer WHERE last_name LIKE ‘S_’;
This query would match last names such as “Smith” and “Stewart”, but not “Scott” or “Stone”.
Note that the underscore character matches exactly one character, whereas the percent sign (%) matches any number of characters (including zero).
A “condition” in the WHERE clause may compare inequality, like > SOME, >= SOME, < SOME, or <= SOME, between literal, column, or expression values and a lists of values returned by a subquery.
Yes, a “condition” in the WHERE clause may use inequality operators (> SOME, >= SOME, < SOME, or <= SOME) to compare a literal, column, or expression value with a list of values returned by a subquery. The subquery must return a single column with one or more rows, and the inequality operator compares the value on the left side with the set of values returned by the subquery.
Here’s an example that returns all the customers who have spent more than the average amount spent by all customers:
SELECT first_name, last_name, amount
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
WHERE amount > (SELECT AVG(amount) FROM payment);
In this example, the WHERE clause uses the > operator to compare the amount column of the payment table with the average amount returned by the subquery (SELECT AVG(amount) FROM payment). The JOIN clause is used to link the customer and payment tables using the customer_id column. The query returns the first name, last name, and amount spent by each customer who has spent more than the average.
With a tree table join, there are three tables and two join types in the FROM clause, and two on subclauses.
A tree table join involves multiple tables where there is a parent-child relationship between the tables. In a tree table join, there can be more than two tables involved. Each child table may have its own parent table, creating a hierarchical relationship.
A tree table join typically involves a single join type (i.e. INNER JOIN) and multiple ON subclauses. The JOIN type is used to specify the relationship between the parent and child tables, while the ON subclauses are used to specify the join conditions between the tables.
Therefore, the statement “With a tree table join, there are three tables and two join types in the FROM clause, and two on subclauses” is not accurate.
In relational databases, facts are broken into separate pieces (or tables) to avoid redundancy, and queries let you gather data from two or more tables by using join operations.
True
