Database final

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

Any create a command may be reversed by using a ______ command

Drop

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

One major advantage of the outer join is that:

Information is not lost

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

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

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

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

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

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

________ 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

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

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;

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

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

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

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