CIS 336 Final Exam mostly tested questions with answers testing papers

Document Content and Description Below

CIS 336 Final Exam mostly tested questions with answers testing papers 1. Consider the ERD below. How many tables would be required to create a query to display the customer first and last na... me along with orderdate of each order they placed? One Two Three Four 2. Which of the following statements is correct? (Points : 4) WHERE operates on groups formed by aggregate functions and HAVING operates on individual rows. WHERE can only be used along with HAVING. HAVING can only be used along with WHERE. WHERE operates on individual rows and HAVING operates on groups formed by aggregate functions. 3. You can code views that _____. (Points : 4) join tables summarize data usesubqueries and functions All of the above 4. The customer table’s primary key is CustomerID. It has no null entries, and all values are unique. This is an example of _____ integrity. (Points : 4) entity referential relational logical 5. In the following command, the primary key constraint is created at the _____ level. CREATE TABLE vendors ( vendorID INT, vendorName VARCHAR(20), CONSTRAINT vendor_pk PRIMARY KEY (vendorID) ); (Points : 4) table column database alter 6. Which join syntax is required if the primary key and foreign key have different names? Natural join JOIN USING JOIN ON Any can be used 7. To be in the third normal form, _____. (Points : 4) each non-key column must depend only on the primary key each non-key column must not depend on the primary key each non-key column must contain repeating values All of the above Question 1. 1. (TCO 2) A _____ relationship must be implemented by creating a new entity that has 1:M relationships with the two original entities. (Points: 4) 1:1 1:M M:1 M:N Question 2. 2. (TCO 2) _____ integrity dictates that the foreign key must contain values that match the primary key in the related table or must contain null. (Points : 4) Entity Referential Relational Logical Question 3. 3. (TCO 8) Which command is used to remove a table named products in MySQL? (Points : 4) DROP TABLE products; REMOVE TABLE products; ALTER TABLE products DROP; DELETE TABLE products; Question 4. 4. (TCO 6) The special operator used to check if a value matches one of a list of specific values is _____. (Points : 4) BETWEEN NULL LIKE IN Question 5. 5. (TCO 2) A _____ key is a key that is composed of more than one attribute. (Points : 4) primary foreign composite domain Question 6. 6. (TCO 3) When looking at a relationship between two tables on an ERD, the child table can be identified by the presence of a _____ constraint. (Points : 4) UNIQUE NOT NULL FOREIGN KEY PRIMARY KEY Question 7. 7. (TCO 3) When a constraint is created at the _____ level in a CREATE TABLE command, the constraint definition is simply included as part of the attribute definition. (Points : 4) table column database alter Question 8. 8. (TCO 7) The SQL aggregate function that determines the highest value in a given column is _____. (Points : 4) COUNT MAX MAXIMUM SUM Question 9. 9. (TCO 7) Which join syntax is required if the primary key and foreign key have different names? (Points : 4) Natural join JOIN USING JOIN ON Any can be used Question 10. 10. (TCO 6) When using the MySQL DATE_FORMAT function, which code displays a full month name? (Points : 4) %M %m %b %mon Question 11. 11. (TCO 3) Which keyword is used to require that a value be entered for the attribute? (Points : 4) FOREIGN KEY DEFAULT NOT NULL AUTO_INCREMENT Question 12. 12. (TCO 2) When identifying potential entities for an ERD, look for the _____ in the business narrative. (Points : 4) adjectives adverbs nouns verbs Question 13. 13. (TCO 2) To apply the second normal form, you move columns that don’t depend on the entire primary key to another table and establish a relationship between the two tables. This _____. (Points : 4) reduces redundancy but makes maintenance more difficult reduces redundancy and makes maintenance easier increases redundancy but makes maintenance easier increases redundancy but makes the data more consistent Question 14. 14. (TCO 2) A _____ means that a nonprime attribute is functionally dependent on only part of a table’s primary key but not the entire primary key. (Points : 4) partial dependency transitive dependency repeating group primary key Question 15. 15. (TCO 4) In a typical online store scenario, which of the following diagrams best represents the relationship between customers and orders placed by customers? (Points : 4) Option #2 Question 16. 16. (TCO 4) A(n) _____ provides a graphical description of a data model. (Points : 4) entity relationship diagram data dictionary business rule psuedocode Question 17. 17. (TCO 3) Which statement is false regarding the creation of a foreign key constraint at table level or with an ALTER statement? (Points : 4) The parent table must already exist. The field must already be defined in the table. The PK and FK fields must have the same name. The PK and FK fields may have different names. Question 18. 18. (TCO 3) You are creating a table called Department with fields for the primary key DeptID (Integer) and department name (VARCHAR). What (if anything) is wrong with the following code? CREATE TABLE Department ( deptID INTEGER Primary Key, deptName VARCHAR(10) NOT NULL ); (Points : 4) You must use curly braces, not parentheses. The field names must be all lowercase. The keywords cannot be in uppercase. Nothing is wrong. Question 19. 19. (TCO 6) Given a table orders with fields for orderid, orderdate, and shipdate, which query will display the orderid for only those orders that have shipped? (Points : 4) SELECT ordered FROM orders WHERE shipdate<>orderdate; SELECT orderid FROM orders WHERE shipdate IS NOT NULL; SELECT orderid FROM orders WHERE shipdate IS NULL; SELECT orderid FROM orders; Question 20. 20. (TCO 6) Which WHERE clause will return data on all employees whose last name starts with ‘Smith’? (Points : 4) WHERE lastname LIKE ‘Smith WHERE lastname LIKE ‘Smith%’ WHERE lastname LIKE ‘%Smith%’ WHERE lastname LIKE ‘%Smith Question 21. 21. (TCO 7) Consider the ERD below. How many tables would be required to create a query to display the description of each product ordered along with the first and last name of the customer that ordered it? (Points : 4) One Two Three Four Question 22. 22. (TCO 7) A full outer join returns _____. (Points : 4) rows in the left table that don’t satisfy the join condition unmatched rows from both the left and right tables rows in the right table that don’t satisfy the join condition the Cartesian product of the two tables Question 23. 23. (TCO 7) What type of subquery is executed once for each row processed by the main query? (Points : 4) Correlated Uncorrelated Inner Outer Question 24. 24. (TCO 7) If your SELECT clause contains both aggregate and non-aggregate functions, _____. (Points : 4) All non-aggregate columns must be included in a WHERE clause. All non-aggregate columns must be included in a GROUP BY clause. All aggregate and non-aggregate columns must be included in a GROUP BY clause. All aggregate columns must be included in a WHERE clause. Question 25. 25. (TCO 9) Which of the following is not a benefit provided by using a view? (Points : 4) Views can be used to update multiple tables with a single statement. You can use views to limit the exposure of the tables in your database to certain rows. Views can be used to hide the complexity of query operations. You can code views that join tables. Page 3 Question 1. 1. (TCO 6) Write a query to list the customer first name, last name as a single field with a heading of Customer along with the balance sorted by balance from lowest to highest. (Points : 10) SELECT CONCAT(FirstName,’ ’ ,LastName) AS ‘Customer’, Balance FROM Customer ORDER BY Balance ASC; 2. (TCO 6) Write a query to display the customer number, last name and first name for every customer represented by sales rep 15 or sales rep 20. (Points : 10) SELECT CustomerID, LastName, FirstName FROM Customer WHERE RepID IN (15,20); 3. (TCO 6) Display all of the information from the part table for parts with a retail price below 100 and zero on hand. (Points : 10) SELECT *FROM Part WHERE Retail < 100 AND UnitsOnHand = 0; 4. (TCO 7) Write a query to display the orderid, order date, customer last name and firstname for all orders that have not shipped. (Points : 10) SELECT OrderID, OrderDate, LastName, FirstName FROM Order JOIN Customer Using(CustomerID) WHERE ShipDate IS NULL; 5. (TCO 7) Write a query to display the average retail price of all products formatted to display with two decimal places. (Points : 10) SELECT ROUND(AVG(Retail),2) FROM Part; 6. (TCO 7) Write a query using JOINS to list the orderid, partid, and part description and units on hand or every order that has not shipped. (Points : 10) SELECT o.OrderID, p.PartID, p.Description, p.UnitsOnHand FROM Order o Left Join OrderLine ol USING (OrderID) Left Join Part p USING (PartID) WHERE o.ShipDate IS NULL; 7.(TCO 7) Write a query to display each sales reps ID and the total number of customers that they have. Only include those reps that have more than 10 customers. (Points : 10) SELECT RepID, COUNT(CustomerID) AS ‘Total Customer’ FROM SalesRep INNER JOIN Customer USING(RepID) GROUP BY RepID HAVING COUNT(CustomerID) > 10; 8.(TCO 7) Using a subquery, list the partid and description of all parts where the number of units on hand is greater than the average of units on hand for all parts. (Points : 10) SELECT p.PartID, p.Description FROM Part p WHERE p.UnitsOnHand> (SELECT AVG(UnitsOnHand) FROM Part); [Show More]

Last updated: 2 years ago

Preview 1 out of 17 pages