ITCS 3160 SQL Practice

What will be returned when the following SQL statement is executed? SELECT driver_no, count(*) as num_deliveries FROM deliveries GROUP BY driver_no;

A listing of each driver as well as the number of deliveries that he or she has made

What will be returned when the following SQL statement is executed? SELECT driver_no, count(*) as num_deliveries FROM deliveries WHERE state = 'MA' GROUP BY driver_no

A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state.

What does the following SQL statement do? Alter Table Customer_T Add (Type Varchar(2));

Alters the Customer_T table, and adds a field called "Type"

A join operation:

Causes two tables with a common domain to be combined into a single table or view

INSERT INTO is an example of _________ code.

DML

The first part of an SQL query to be read is the _________ statement.

FROM

(T/F) A database is maintained and queried using the data mapping language (DML)

False

(T/F) A subquery in which processing in the inner query depends on data from the outer query is called a codependent query

False

(T/F) Count (*) tallies only those rows that contain a value, while Count counts all rows.

False

(T/F) In databases, null values are equivalent to zero

False

(T/F) Indexes generally slow down access speed in most RDBMS

False

(T/F) The HAVING clause and the WHERE clause perform the same operation

False

(T/F) The WHERE clause is always processed before the GROUP BY clause when both occur in a SELECT statement

False

(T/F) The following code is an example of a subquery SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID;

False

One major advantage of the outer join is that:

Information is not lost

The last part of an SQL query to be read is the _________ statement.

ORDER BY

Referential Integrity Constraints are generally established between:

Primary and Foreign keys.

Indexes are created in most RDBMS's to:

Provide a rapid random and sequential access to base-table data.

What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best"

Selects all the fields from the Customer table for each row with a customer labeled "Best"

Relational databases are heavily based on the mathematical concept of:

Set Theory

SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query

Subquery

The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode FROM Customer_T WHERE Customer_T.CustomerID = (SELECT Order_T.CustomerID FROM Order_T WHERE OrderID = 1008);

Subquery

EXISTS will take a value of _________ if the subquery returns an intermediate results table which contains one or more rows

TRUE

Which of the following is true of the order in which SQL statements are evaluated?

The SELECT clause is processed before the ORDER BY clause.

What result will the following SQL statement produce? Select Avg(Standard_Price) as Average From Product_V;

The average Standard_Price of all products in Product_V

What results will be produced by the following SQL query? SELECT sum(standard_price) as Total_Price FROM Product_V WHERE Product_Type = "Wood";

The total price of all products that are of type wood

(T/F) A database table is defined using the data definition language (DDL)

True

(T/F) Adding the DISTINCT keyword to a query eliminates duplicates

True

(T/F) The FROM clause is the first statement processed in an SQL command

True

(T/F) The ORDER BY clause sorts the final results rows in ascending or descending order

True

(T/F) The asterisk (*) wildcard designator can be used to select all fields from a table as well as in WHERE clauses when an exact match is not possible

True

(T/F) The following SQL statement is an example of a correlated subquery SELECT first_name, last_name, total_sales FROM salesman s1 WHERE total_sales > all (SELECT total_sales FROM salesman s2 WHERE s1.salesman_id != s2.salesman_id);

True

(T/F) When a GROUP BY clause is included in an SQL statement, only those columns with a single value for each group can be included

True

(T/F) When creating tables, it's important to decide which columns will allow null values before the table is created.

True

The ________ operator is used to combine the output from multiple queries into a single result table

Union

The command for creating a database is:

Create Schema

To get all the customers from Hawaii sorted together, which of the following would be used?

Order By

What result will the following query return? SELECT Item_No FROM Order_V WHERE Quantity > 10;

The Item_No of all orders that had more than 10 items.

(T/F) An SQL query that implements an outer join will return rows that do not have matching values in common columns

True

(T/F) SQL statements can be included in another language, such as C or Java.

True

Multiple values returned from an SQL query that includes an aggregate function are called:

Vector Aggregates

All of the following are guidelines for better query design EXCEPT: a) understand how indexes are used in query processing b) use a lot of self-joins c) write simple queries d) retrieve only the data that you need

b) use a lot of self joins

The UNION clause is used to:

Combine the output from multiple queries into a single result table

A _________ is a temporary table used in the FROM clause of an SQL query

Derived Table

In which of the following situations would one have to use an outer join in order to obtain the desired results?

A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).

The SQL command ___________ adds one or more new columns to a table.

Alter Table

A join in which the joining condition is based on equality between values in the common columns is called a:

Both equi-join and natural join

In order for two queries to be UNION-compatible, they must:

Both output compatible data types for each column and return the same number of rows

To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL Select command.

Distinct

(T/F) Using an outer join produces this information: Rows that do not have matching values in common columns are not included in the result table

False

(T/F) When the SELECT clause in the create view statement contains the keyword DISTINCT, the view can be used to update the data.

False

DDL is typically used during which phase of the development process?

Physical Design

(T/F) The following queries produce the same results. SELECT DISTINCT customer_name, customer_city FROM customer, salesman WHERE customer.salesman_id = salesman.salesman_id AND salesman.lname = 'SMITH'; SELECT customer_name, customer_city FROM customer WHERE customer.salesman_id = (SELECT salesman_id FROM salesman WHERE lname = 'SMITH');

True

(T/F) The following query totals sales for each salesperson SELECT salesperson_id, sum(sales) FROM salesperson GROUP BY salesperson_id;

True