Chapter 8 - Using SQL to Remove Data

SELECT * FROM Minutes, Seconds; Explanation This will work, but it can be confusing. Using the CROSS JOIN specifically in the statement ensures that you (and others looking at your statements) know what is intended.

2. Which SQL statement is a valid way to complete a cross join?

SELECT * FROM tblAccount CROSS JOIN tblBalance; Explanation The other correct syntax is SELECT * FROM tblAccount, tblBalance. However, we recommend the CROSS JOIN keyword as it clearly states the intention of the SQL statement (e.g., to create a Cartesian product join).

3. Which SQL statement will correctly perform a cross join on the tables tblAccount and tblBalance?

Cartesian product Explanation It is a Cartesian product: the key operation being multiplication. Therefore, given Table A with 3 rows and Table B with 6 rows, the cross join will return 18 rows.

A CROSS JOIN is also known as a _____ 1. Sum 2. Union 3. Cartesian sum 4. Cartesian product

75 Explanation Since a cross join is a product, all of the results will be returned; therefore, Table A's record count * Table B's record count is 15 * 5 = 75.

A CROSS JOIN is performed on two tables. Table A has 15 rows; Table B has 5. How many rows will the SQL query return? 1. 15 2. 0 3. 225 4. 75

Virtual table or tables Explanation A View is a virtual table or tables. It keeps the original table(s) safe from harm, or allows only certain fields to be visible to end users. Dropping a view is far less dangerous than dropping the original table.

A View is a _____. 1. Virtual table or tables 2. Copy of the entire database 3. Copy of a query 4. Copy of the database table

Union Explanation The union join displays data in new rows; a full outer join displays it as new columns.

A _____ displays the merged data into new rows instead of columns. 1. Left outer join 2. Inner join 3. Full outer join 4. Union

Running totals Explanation Running totals or running counts can be completed via the self-join.

A self-join can be used for what purpose? 1. Calculating averages 2. Deleting data 3. Running totals 4. Updating foreign keys

The same table Explanation A self-join is a way to create a query that basically joins a table to itself; it's not a true join, but a way of comparing data in one table to data in the same table.

A self-join query compares data from one table to _____ 1. The same table 2. Another query 3. A stored procedure 4. Another joined table

The query will not run and display an error Explanation If the view does not exist, and you try to reference it, thee will be errors. Use the IF EXISTS statement to ensure smooth processing.

A view, Sales_View, has been dropped. A query still refers to this view. What will happen when the query is run? 1. The query will not run and display an error 2. The database application will re-create the view 3. No records will be returned 4. The query will run but with bad output

Simple join Explanation It's only called simple because it grabs all rows of data that match on a given key. Example: All employee ID's from both the employee table and the benefit table where the employee ID's are the same

An INNER JOIN is also called a(n) _____ 1. Complex join 2. Abstract join 3. Outer join 4. Simple join

Intersects Explanation A way to think of the join is that it is an intersection of two tables; the data that matches the condition for both tables is returned by the query.

An INNER JOIN returns rows of tables where the data _____ 1. Is different 2. Is read-only 3. Is not NULL 4. Intersects

Full outer Explanation Run by itself, a full outer returns all records. You can modify it to return unmatched rows (e.g., we entered a sale without a customer)

An advanced SQL join that combines results from all tables is a(n) _____ join. 1. Right outer 2. Left outer 3. Full outer 4. Inner

The first UPDATE statement is attached to the WITH clause. Once this statement finishes execution, the item_CTE table will be removed and no longer available. The second UPDATE statement is thus attempting to update the item_CTE table, which no longer exists. Explanation The correct answer is the third suggested answer. The WITH clause creates a temporary table like a view. However, unlike the view this table is not stored in the database schema. Once the outer SQL statement linked to the WITH clause completes execution, the table ceases to exist. Also, please note that the first UPDATE statement will actually perform the necessary updates on the base table. In this situation, the UOM field will be set to the value 'PCs' for all records where the itemID has a value less than 'I000000005'.

Consider the SQL statement shown in the diagram below. The first SQL update statement UPDATE item_CTE SET UOM = 'PCs' succeeds but the second update statement UPDATE item_CTE set UOM = 'PC' fails. What is the reason for this failure ? 1. The SQL server configuration is not correctly done and is not setup to allow for multiple SQL statements to be linked to the WITH clause. 2. The WITH clause utilizes resources heavily and there is insufficient resources left in the server to execute the second update statement. 3. The statement will fail because the WITH clause is not designed to work with the SQL update statement. 4. The first UPDATE statement is attached to the WITH clause. Once this statement finishes execution, the item_CTE table will be removed and no longer available. The second UPDATE statement is thus attempting to update the item_CTE table, which no longer exists.

Left outer Explanation A left outer join will return not only purchases at all locations but locations that did not have a purchase.

Consider the following tables of data: Location and Purchases. We want to retrieve all purchases AND all locations, even if there were no purchases made at those locations. What type of SQL join is appropriate? 1. Inner join 2. Union 3. Left outer 4. Cross join

No, we can select the exact information that we require Explanation Remember that we can always select which information we actually require from multiple tables when joining them.

Do we always need all the information that is combined with a Join? 1. No, we can select the exact information that we require 2. No, we only need the keys of the tables 3. Yes, more information can help solve better business questions 4. Yes, that's the only way the Join works

Orders Explanation The subquery in this case is inside the WHERE clause. Within this subquery, we are querying the orders table to only get those with a quantity under 5.

Examine the following SQL statement. What table is the subquery querying? 1. none 2. orders 3. customer 4. inventory

Aliases Explanation The system won't know what you are trying to compare. There should be aliases; e.g.: SELECT DISTINCT c1.customerName, c1.customerName FROM c1.Contacts, c2.Contacts... This tells the system that you are creating a self-join on the Contacts table.

Examine the following code. What is missing from the self-join statement? 1. Table name 2. GROUP BY clause 3. Aliases 4. Primary key

Global temporary Explanation In SQL Server, global temp tables are prefixed with ##. They can be used across databases.

Examine the following code. What type of table is orders_temp? 1. Local Temporary 2. Void 3. Empty 4. Global temporary

FROM Orders AS o1, Orders AS o2 Explanation When self-joining, you need to make aliases, but it's the same table. That way the AS statement tells the database engine what's being queried.

Examine the following code: What would be the correct way to complete the FROM statement? SELECT o1.orderID, o1.productName, o2.productName, o2.orderID 1. FROM Orders, Orders 2. FROM Orders o1, o2 3. FROM Orders AS o1, Orders AS o2 4. FROM o1.Orders, o2.Orders

An alias for the subquery Explanation Remember that the alias is important for the subquery: If it isn't specified after the statement (in this case the GROUP BY statement), the database engine won't know what you mean when you reference it in the main SELECT statement. The first SELECT statement references subquery1, but it is never defined in the actual subquery.

Examine the following subquery: What is missing from this SQL statement? 1. An alias for the subquery 2. It only queries one table: Need to query another 3. Nothing 4. Semicolons after the WHERE clause

Add productID to the tblOrders table Explanation A workable solution would be to ensure that the tables are correctly related by the productID... that way you have a product to go with every order. When the INNER JOIN returns the intersecting results, you will get data. As it is, the tables are not related at all and really can't be queried together.

Examine the following tables, tblProducts and tblOrders. What is needed so that an INNER JOIN can be completed? 1. Remove orderID form tblOrders 2. Add a customer name to tblOrders 3. Add productID to the tblOrders table 4. Combine both tables into one

A table indicating the chemistry majors who have registered for courses. Explanation The WHERE clause indicates that a specific parameter is returned from the query, in this case it is when the student's major is chemistry.

Given the following SQL statement, please describe what results will be given. 1. Only the students who are chemistry majors taking chemistry courses. 2. A table listing all students taking a chemistry course. 3. A table indicating the students who are not taking a chemistry course or registered as a chemistry major. 4. A table indicating the chemistry majors who have registered for courses.

Joins can be on primary-key-to-primary-key or primary-key-to-foreign-key Explanation When joining tables, you can only join on keys, but you can join a primary key to either another primary key or a foreign key.

How can you match tables to perform a Join? 1. Joins are only on the primary-key-to-primary-key in the tables involved in the joins 2. Joins are only on the foreign-key-to-foreign-key in the tables involved in the joins 3. Joins are only on the primary-key-to-foreign-key of the tables involved in the joins 4. Joins can be on primary-key-to-primary-key or primary-key-to-foreign-key

A x B Explanation The keyword is product; remember that a product is the result of multiplication. This would mean that all values in A and B will be returned.

How is the Cartesian product of A and B represented? 1. A x B 2. A + B 3. A - B 4. A / B

Full outer Explanation A true full outer join returns all records from the tables. A union displays the data in new rows.

If you need to display all records from both Purchase_Orders and Customers table and display them in columns, which type of SQL join is most appropriate? 1. Union 2. Full outer 3. Right outer 4. Left outer

Errors/program crash Explanation If running ad-hoc, you'll get an error. If the code is in a stored procedure, it will probably crash.

If you try to reference a table after the DROP TABLE command, what will happen? 1. Errors/program crash 2. Nothing 3. Warnings will display 4. The table will be recreated

SELECT Explanation The general use of a subquery within the SELECT clause is to return a single value: Sum, count, minimum/maximum values. Since a subquery within the SELECT can only return one value, this is a great place for an aggregate function like this.

If you want to view a maximum value from a table, in which part of the SQL statement should the subquery be placed? 1. ORDER BY 2. SELECT 3. GROUP BY 4. FROM

INNER SELECT Explanation The OUTER SELECT is actually the main query: Since a subquery is a query-within-a-query, the term INNER SELECT captures the properties and purpose of the subquery.

In SQL Server, a subquery can also be called a(n) _____ 1. INNER SELECT 2. OUTER SELECT 3. Reverse query 4. Update query

WHERE clause Explanation The condition that actually sets the join happens at the WHERE.

In which part of a query is a Join actually happening? 1. IF clause 2. * clause 3. WHERE clause 4. SELECT clause

I, II and III Explanation The EXISTS and NOT EXISTS operator can be used with any SELECT, UPDATE or DELETE SQL statement.

The EXISTS and NOT EXISTS operators are always used as a sub-query which is linked to another main SQL statement. The main SQL statement can perform which of the following tasks ? I. SELECT II. UPDATE III. DELETE 1. I and II 2. I and III 3. I, II and III 4. II and III

SELECT itemID FROM item WHERE EXISTS ( SELECT DISTINCT itemID FROM itemSupplier WHERE itemSupplier.itemID = item.itemID ) Explanation The first answer is the correct answer, though there are also other possible SQL statements which can accomplish the same. Note that SELECT DISTINCT is used instead of just SELECT. There is a possibility that the same itemID is supplied by multiple suppliers and occurs multiple times in the itemSupplier table. SELECT DISTINCT removes repeated rows containing the same itemID. The second answer has a syntax error. The inner sub-query ( SELECT itemID FROM itemSupplier ) may return zero, one or more rows with the itemID field and this result cannot be compared to the single valued itemID field in the item table. The last two answers are both non-valid SQL statements.

The diagram below is to be used for this question. The SQL statement below is executed. SELECT itemID FROM item WHERE NOT EXISTS ( SELECT itemID FROM itemSupplier WHERE itemSupplier.itemID = item.itemID ) Which is the correct result returned by this SQL statement ? 1. OUTPUT A 2. OUTPUT B 3. OUTPUT C 4. OUTPUT D

I, II and III Explanation All answers are correct. All three SQL statements will accomplish the same objective to list out itemIDs from the item table which exists in the itemSupplier table.

The diagram below is to be used together with this question. The first table is named itemSupplier. and the second table is named item. Which of the following SQL statements below will list out all itemIDs from the item table which exist in the itemSupplier table ? I. SELECT item.itemID FROM item WHERE item.itemID = ANY ( SELECT DISTINCT itemID FROM itemsupplier ) II. SELECT item.itemID FROM item WHERE item.itemID IN ( SELECT DISTINCT itemID FROM itemsupplier ) III. SELECT itemID FROM item WHERE EXISTS ( SELECT itemID FROM itemSupplier WHERE itemSupplier.itemID = item.itemID ) 1. II and III 2. I, II and III 3. I and II 4. I and III

SELECT itemID FROM item WHERE EXISTS ( SELECT DISTINCT itemID FROM itemSupplier WHERE itemSupplier.itemID = item.itemID ) Explanation The first answer is the correct answer, though there are also other possible SQL statements which can accomplish the same. Note that SELECT DISTINCT is used instead of just SELECT. There is a possibility that the same itemID is supplied by multiple suppliers and occurs multiple times in the itemSupplier table. SELECT DISTINCT removes repeated rows containing the same itemID. The second answer has a syntax error. The inner sub-query ( SELECT itemID FROM itemSupplier ) may return zero, one or more rows with the itemID field and this result cannot be compared to the single valued itemID field in the item table. The last two answers are both non-valid SQL statements.

The diagram shown below is to be used for this question. The table showing the item master is named item and the table with the supplier-item relationship is named itemSupplier. The itemID field for all items which are supplied by at least one supplier is to be listed out. Which of the following SQL statements provided below can accomplish this ? 1. SELECT itemID FROM item WHERE EXISTiNG ( SELECT DISTINCT itemID FROM itemSupplier ) 2. IF EXISTS ( SELECT itemID FROM itemSupplier ) SELECT * FROM item 3. SELECT itemID FROM item WHERE EXISTS ( SELECT DISTINCT itemID FROM itemSupplier WHERE itemSupplier.itemID = item.itemID ) 4. SELECT itemID FROM item WHERE itemID = ( SELECT itemID FROM itemSupplier )

SELECT * FROM tblEmployee, tblHealthPlan INNER JOIN tblEmployee ON tblEmployee.planCode = tblHealthPlan.planCode; Explanation In order for the INNER JOIN to work you need to have fields in both tables that are related so that you actually get data that intersects. In this query, we're going to get a ton of data (the asterisk tells us to select ALL records from BOTH tables that meet the criteria of the plan code matching).

The following is only the first part of an INNER JOIN query. Which of the following is the correctly-written full query?

An ORDER_BY clause is particularly useful when you expect to have a large data set returned and you want to view student names in alphabetical order. Explanation A full outer join will return all rows from all tables, an ORDER_BY clause returns results in a particular order for organizational purposes.

The results of a full outer join can be organized using an ORDER_BY clause; how would this be useful considering the examples given in the lesson? 1. An ORDER_BY clause is particularly useful when you expect to have a large data set returned and you want to view student names in alphabetical order. 2. An ORDER_BY clause could be used to return a list of students who are chemistry majors. 3. You might use an ORDER_BY clause to eliminate rows will null values. 4. An ORDER_BY clause could be used to return a list of all students whose last names start with the letter 'W'

When you need all the rows from the tables you are joining. Explanation A full outer join is useful when you are looking for gaps in data or want all the rows from both the tables returned.

Under what circumstance would you select a full outer join SQL statement? 1. When looking for specific data that will result in some rows being left out of the query. 2. When you only want returned rows that contain data. 3. When you need all the rows from the tables you are joining. 4. When you need all the rows from the left table returned and only matching rows from the right.

It is deleted Explanation Everything is deleted! The table and data are removed from the database application.

What happens to the data in a temporary table after you use the DROP TABLE command? 1. It is deleted 2. It is saved to another system table 3. It is saved for 30 days. 4. It is copied to a temporary file and deleted

The keyword TABLE Explanation When dropping a table, you need the full command, DROP TABLE

What is missing from the following statement? 1. Nothing 2. A SELECT statement 3. The pound sign (#) 4. The keyword TABLE

To dig into data to actually make sense of information and answer to business questions Explanation The main reason to combine tables is to dig for further information in your data, that can actually help solve business questions.

What is the business purpose of performing an SQL Join? 1. To make sense of data by adding more information to your current tables 2. To increase the costs of operation by increasing your storage demand 3. To dig into data to actually make sense of information and answer to business questions 4. To find out connections and relationships between tables

The view only Explanation Dropping a view only removes the view and preserves the original data.

What item(s) does the DROP VIEW command delete? 1. The view only 2. The original table and the view 3. The field definitions from the table and the view 4. The database and the view

When you need to combine information from multiple tables Explanation An SQL join is used to combine information between two or more tables.

When do you perform a Join operation? 1. When you want to add rows of information to your data 2. When you need to find relationships between tables 3. When you need to combine information from multiple tables 4. To match columns and gather further information for business questions

A blending of data Explanation JOIN combines different data points from tables to create a more cohesive whole.

Which is the best analogy for JOIN in SQL? 1. A blending of data 2. Copy and pasting two lists 3. It depends on the JOIN in question. 4. Tacking data to the bottom of a list

Tacking extra names at the bottom of a list Explanation UNION adds any fields not present at the bottom.

Which is the best analogy for UNION in SQL? 1. None of the answers are correct; there isn't a good analogy for UNION. 2. Blending data 3. Creating two tables 4. Tacking extra names at the bottom of a list

DROP TABLE #orders_temp; Explanation(This is SQL Server: Temporary tables are named with the pound sign, #)

Which is the correct syntax for dropping a temporary table? a. DROP TABLE #; b. DROP orders_temp; c. DROP TABLE #orders_temp; d. #DROP TABLE orders_temp;

INNER JOIN tblProducts ON tblOrders.productID = tblProducts.productID; Explanation In order to complete the join, we need to specify all tables we are joining together, and on what field.

Which of the following correctly completes the INNER JOIN statement?

SELECT table.column-names FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column; Explanation The order of the full outer join syntax is important for proper commands.

Which of the following is the correct full outer join syntax?

DROP VIEW Customer_View; Explanation Use the DROP VIEW command, followed by the view name. Optionally you can use the IF EXISTS statement to make sure the View is in the database

Which of the following is the correct syntax for dropping/deleting the View Customer_View? 1. Customer_View DROP; 2. DELETE VIEW Customer_View; 3. DROP VIEW Customer_View; 4. DROP Customer_View;

If you ran a grocery store with multiple locations, and you want to find out which locations are out of a specific inventory. Explanation A full outer join returns results from all rows, including those with null data.

Which of the following scenarios describes a reason why you would select a full outer join? 1. If you ran a grocery store with multiple locations, and you want to find out which locations are out of a specific inventory. 2. If you ran a grocery store and wanted a list returned of stores that are stocked with skim milk. 3. If you ran a grocery store and wanted a list returned that excludes employees who work at particular location. 4. If you ran a grocery store with multiple locations, and you want a list that leaves out stores that are out of a specific inventory.

SET Explanation A subquery can also be part of the UPDATE statement. Subqueries can be thought of as inner queries, or filters for the main query.

Which of the following statements does NOT allow for subqueries? 1. FROM 2. SET 3. WHERE 4. SELECT

Enter Explanation ENTER would not appear, but SELECT specifies a field, UNION combines the values, and WHERE helps to limit only to non-duplicates.

Which of the following words WOULD NOT appear in a UNION command? 1. UNION 2. WHERE 3. SELECT 4. ENTER

The command 'JOIN' Explanation JOIN doesn't have to be explicitly stated when using JOIN in SQL to join tables.

Which of the following would NOT be needed when using combining data from data tables? 1. A column title 2. The command 'JOIN' 3. The command 'SELECT' 4. At least one table

To display employees and work units that may or may not have employees Explanation The Work_Unit table is the right-side table; the right outer join will return employees in a work unit and also work units that may or may not have employees.

Which of the following would be the best reason to use a right outer join between an Employee table and a Work_Unit table? 1. To display employees and work units that may or may not have employees 2. To display work units and employees where each employee has a work unit 3. To get only employees that belong to a work unit 4. To retrieve missing work units and employees

IF EXISTS Explanation DROP VIEW IF EXISTS Customer_View; This code works in SQL Server

Which optional statement tells the DROP VIEW command to work only if the view is present in the database? 1. IS ACTIVE 2. IS NOT NULL 3. IS VALID 4. IF EXISTS

DISTINCT Explanation SELECT DISTINCT a1.accountID, a1.accountName... This ensures only one account ID and Name returned for a successful query result.

Which part of a self-join statement ensures that one record per criteria is returned? 1. ORDER BY 2. DISTINCT 3. GROUP BY 4. An alias

Lots of duplicate data in the tables Explanation UNION eliminates duplicates and is better to use if you don't want to write all the extra code to do it.

Which would be the most appropriate reason to use a UNION vs. a JOIN? 1. Lots of duplicate data in the tables 2. If the code is going to be hard to write 3. When there are more than 10 tables 4. If there are single-column tables