IS 410 EXAM 2 (Ch.4-6)
Match the SELECT statement clause to its definition. A. Indicate the conditions under which a category (group) will be included B. Indicate the conditions under which a row will be included in the result C. Lists the columns (and expressions) to be returned from the query D. Indicate categorization of results E. Indicate the table(s) or view(s) from which data will be obtained
(A) HAVING (B) WHERE (C) SELECT (D) GROUPED BY (E) FROM
Which of the following is not a data integrity restriction? A.Updates to primary records B.Inserts of primary records C.Inserts of dependent records D.Deletion of primary records
(B) Inserts of primary records
Place the steps for developing a relational database in the proper order. - Understand Business Need for the database -Create an EER diagram -create an ER diagram - Create a relational & SQL schema -Create the database Using SQL Programming
1. Understand Business Need for the database 2. Create an ER diagram 3. Create an EER diagram 4. Create a relational & SQL schema 5. Create the database Using SQL Programming
A subquery that is executed once for the entire query is called [A] and a subquery that is executed once for each row returned by the outer query is called [B].
A subquery that is executed once for the entire query is called NONCORRELATED and a subquery that is executed once for each row returned by the outer query is called CORRELATED.
Match the relational model component with its definition. A. Tables (relations), rows, columns B. Power SQL operations for retrieving and modifying data C. Mechanisms for implementing business rules that maintain integrity of manipulated data D. A named, two-dimensional table of data
A. Data structure B. Data manipulation C. Data integrity D. Relation
Match the definition with the correct term. A. A relational operation that causes two or more tables with a common domain to be combined into a single table or view B. A join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table C. An equi-join in which one of the duplicate columns is eliminated in the result table D. A join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table) E. Includes all data from each table that was joined
A. Join B. Equi-Join C. Natural (Inner) join D. Outer Join E. Union Join
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
A primary key that consists of more than one attribute is called a: -foreign key -composite key -multivalued key -cardinal key
Composite key
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
T/F: An OUTER join will only return rows from each table that have matching rows in the other.
FALSE
T/F: There can be multivalued attributes in a relation.
FALSE
Which of the following are properties of relations? -Each attribute has the same name -No two rows in a relation are identical -there are multivalued attributes in a relation -all columns are numeric
No two rows in a relation are identical
In the figure below, what type of relationship do the relations depict? -identifying entity/weak entity -one to many -ternary -many to many
One-to-many
A rule that states that each foreign key value must match a primary value in the other relation is called the: -referential integrity constraint -key match rule -entity key group rule -foreign/primary match rule
Referential integrity constraint
What does the following SQL statement do? SELECT * From Customer WHERE Cust_Type = "Best" -Selects fields with a "*" in them from the Customer table -Selects all the fields from the Customer table for each row with a customer labeled "*" -Selects the "*" field from the Customer table for each row with a customer labeled "Best" -Selects all the fields from the Customer table for each row with a customer labeled "Best"
Selects all the fields from the Customer table for each row with a customer labeled "Best"
What kind of join does the following code represent: SELECT E.EmployeeID, E.EmployeeName, M.EmployeeName AS Manager FROM Employee_T E, Employee_T M WHERE E.EmployeeSupervisor = M.EmployeeID; Result: EMPLOYEEID 122-44-347 EMPLOYEENAME Jim Jason MANGER Robert Lewis
Self Join
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 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
(T/F) Data structures include data organized in the form of tables with rows and columns.
True
T/F: A LEFT OUTER JOIN clause causes rows from the first mentioned table (customer) to appear even if there is no corresponding order data.
True
T/F: A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation
True
T/F: A cascading delete removes all records in other tables associated with the record to be deleted.
True
T/F: A primary key is an attribute that uniquely identifies each row in a relation
True
T/F: A referential constraint is a rule that maintains consistency among the rows of the two relations
True
T/F: A subquery is when you place an inner query (SELECT statement) inside an outer query.
True
T/F: A view is based on a SELECT statement and has a name.
True
T/F: Referential integrity are constraints that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships.
True
T/F: SQL stands for Structured Query Language.
True
T/F: The CHECK statement allows you to constrain attributes to specific values.
True
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
When creating a database, you define the database in the [A], you load the database in the [B] and you control the database in the [C].
When creating a database, you define the database in the DDL, you load the database in the DML and you control the database in the DCL.
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.
A join operation: -brings together data from two different fields. -is used to combine indexing operations. -causes two tables with a common domain to be combined into a single table or view. -causes two disparate tables to be combined into a single table or view
causes two tables with a common domain to be combined into a single table or view.
In the figure below, the primary key for the "Order Line" is which type of key? -composite -foreign -standard -grouped
composite
When a regular entity type contains a multivalued attribute, one must: -create a single relation with multiple lines for each instance of the multivalued attribute -create two new relations, one containing the multivalued attribute -create two new relations, both containing the multivalued attribute -delete the relation and start over
create two new relations, one containing the multivalued attribute
The _____ states that no primary key attribute may be null -referential integrity rule -entity integrity rule -partial specialization rule -range domain rule
entity integrity rule
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 attribute in a relation of a database that serves as the primary key of another relation in the same database is called a: -link attribute -link key -foreign key -foreign attribute
foreign key
The entity integrity rule states that: -no primary key attribute can be null -referential integrity must be maintained across all entities -each entity must have a primary key - a primary key must have only one attribute
no primary key attribute can be null
____use the result of the inner query to determine the processing of the outer query. -Subqueries -Inner subqueries -Outer subqueries -Correlated subqueries
subqueries
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
Data is represented in the form of: -data trees -tables -data notes -chairs
tables
All of the following are guidelines for better query design EXCEPT: -use a lot of self-joins. -retrieve only the data that you need. -understand how indexes are used in query processing. -write simple queries.
use a lot of self-joins