Business DA Midterm

What steps should be followed when preparing to create a table? 1. Identify the appropriate data type and length for each attribute 2. Identify the columns that should accept null values 3. Identify that columns that need to be unique 4. Identify all primary-foreign key mates 5. Determine default values 6. Identify any columns for which domain constraints, such as check, need to be stated 7. Create the table and any indexes using create table and create index statements

(Already correctly ordered)

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 table

What does the following SQL statement do? ALTER TABLE Customer_T ADD (Type Varchar (2));

Alters the Customer_T table by adding a field called "Type"

The following statement is an example of: CREATE TABLE Customer_t ( CustNmbr number(11,0), CreditLimit number(6,2), CustStart date, CustEnd date, PERIOD for Custperiod(CustStart,CustEnd));

An application time period table

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

DISTINCT

The number of entity types that participate in a relationship is called the:

Degree

When a user creates a virtual table it is called a(n):

Dynamic view

Implementation of a standard can never stifle creativity and innovation

False

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

The following code is an example of a Subquery. SELECT Customer_T.CustomerID, Order_T.CustomerID,CustomerName, OrderIDFROM Customer_T, Order_TWHERE Customer_T.CustomerID = Order_T. CustomerID;

False

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

False

The following code is an example of a correlated subquery. SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState,CustomerPostalCodeFROM Customer_TWHERE Customer_T.CustomerID =(SELECT Order_T.CustomerIDFROM Order_TWHERE OrderID = 1008);

False

The following two SQL statements will produce different results. SELECT last_name, first_name FROM customerWHERE state = 'MA' OR state = 'NY' OR state = 'NJ' OR state = 'NH' OR state = 'CT'; SELECT last_name, first_nameFROM customerWHERE state in ('MA','NY','NJ','NH','CT');

False

When the total specialization rule is set for a supertype/subtype relationship, one could roughly compare the supertype to an abstract class in object-oriented programming.

False

A centralized knowledge base of all data definitions, data relationships, screen and report formats, and other system components is called a(n):

Repository

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"

A type of query that is placed within a WHERE or HAVING clause of another query is called a:

Subquery

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, New York or Denver

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 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 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.

A person is an example of an entity.

True

If an identifier is not assigned, the default primary key for an associative relation consists of the two primary key attributes from the other two relations.

True

The following queries produce the same results. SELECT DISTINCT Customer_Name, Customer_CityFROM Customer, SalesmanWHERE Customer.Salesman_ID = Salesman.Salesman_ID and Salesman.Lname = 'SMITH'; SELECT Customer_Name, Customer_CityFROM CustomerWHERE Customer.Salesman_ID =(SELECT Salesman_ID FROM SalesmanWHERE Lname = 'SMITH');

True

The most important challenge of customizing a purchased data model is determining the business rules that will be established through the data model.

True

In an E-R diagram, there are/is __________________ business rule(s) for every relationship.

Two

SQL-invoked routines can be:

functions or procedures

The following code would include: SELECT Customer_T.CustomerID, Order_T.CustomerID,CustomerName, OrderIDFROM Customer_T, Order_TWHERE Customer_T.CustomerID = Order_T. CustomerID;

only rows that match both Customer_T and Order_T Tables.