Database final
What will be returned when the following SQL query is executed? Select driver_no, count(*) as num_deliveries from deliveries group by driver_no having count(*) > 2;
A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries
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 command do? Insert into Customer_T values (001, 'John Smith','231 West St', 'Boston', 'MA','02115');
Adds a new record to the Customer_T
The SQL command ____ adds one or more new columns to a table
Alter table
The command for creating a database is:
CREATE SCHEMA
What does the following SQL statement do? Update Product_T Set Unit_Price = 775 Where Product_ID = 7
Changes the unit price of Product 7 to 775
In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query.
Correlated
Which of the following counts ONLY rows that contain a value
Count
A ________ is a temporary table used in the FROM clause of an SQL query
Derived Table
To eliminate duplicate rows in a query, the ________ qualifier is used in the SQL Select command
Distinct
Any create a command may be reversed by using a ______ command
Drop
________ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows.
EXISTS
A join in which the joining condition is based on equality between values in the common columns is called a(n):
Equi- Join and Natural Join
The following code is an example of a(n): SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;
Equi-join
An equi-join is a join in which one of the duplicate columns is eliminated in the result table.
False
In databases, null values are equivalent to zero
False
The DELETE TABLE DDL command is used to remove a table from the database
False
The DROP command deletes rows from a table individually or in groups
False
Which of the following can produce scalar and vector aggregates?
Group By
Which of the following finds all groups meeting stated conditions?
Having
Which of the following questions is answered by the SQL statement? Select Count (Product_Description) from Product_T;
How many products have product description in the Product Table
The first in a series of steps to follow when creating a table is to:
Identify each attribute and its characteristics
One major advantage of the outer join is that:
Information is not lost
A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n):
Natural Join
To get all the customers from Hawaii sorted together, which of the following would be used?
ORDER BY
A join in which rows that do not have matching values in common columns are still included in the result table is called a(n):
Outer Join
A single value returned from an SQL query that includes an aggregate function is called a(n):
Scalar aggregate
What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best"
Select all the fields from the Customer table for each row with a customer labeled "Best"
Which of the following will produce the minimum of all standard prices
Select min(standard_price) from Product_V;
An operation to join a table to itself is called a(n)
Self-join
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
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 result set is returned from the following query? Select Customer_Name, telephone from customers where city in ('Boston','New York','Denver');
The customer_Name and telephone of all customers living in either Boston, NewYork, or Denver
What will result from the following SQL Select statement? Select min(Product_Description) from Product_V;
The first product description alphabetically in Product_V will be shown
What result set will the following query return? Select Item_No, description from item where weight > 100 and weight < 200;
The item_No and description for all items weighing between 101 and 199
What result set 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
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
A join in which the joining condition is based on equality between values in the common column is called an equi-join
True
A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated.
True
An SQL query that implements an outer join will return rows that do not have matching values in common columns.
True
An insert command does not need to have the fields listed
True
The ALTER TABLE command is used to change a table definition
True
The SQL command used to populate tables is the INSERT command
True
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
Multiple values returned from an SQL query that includes an aggregate function are called:
Vector aggregate
In an SQL statement, which of the following parts states the conditions for row selection?
Where
Given a table a table named store with 5 fields: store_id, address, city, state, zip code, why would the following insert command not work? insert into store values ('234 Park Street')
You must specify the fields to insert if you are only inserting some fields
The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;
all rows of the Customer_T Table regardless of matches with the Order_T Table
The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T RIGHT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;
all rows of the Order_T Table regardless of matches with the Customer_T Table
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
What does the following SQL statement do? Delete from Customer_T where state = 'HI';
deletes all records from Customer_T where the state is equal to HI
The following code would include: SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;
only rows that match both Customer_T and Order_T Tables
A type of query that is placed within a WHERE or HAVING clause of another query is called a:
subquery