CIS 336 Final Exam

$28.00

CIS 336 Final Exam
(TCO 6) When using the MySQL DATE_FORMAT function, which code displays…

SKU: CIS 336 Final Exam Categories: , Tags: , ,

Description

CIS 336 Final Exam

CIS 336 Final Exam

A+

Consider the ERD below. How many tables would be required to create a query to display the customer first and last name along with orderdate of each order they placed?

One

Two

Three

Four

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 group formed by aggregate functions.

You can code views that _____. (Points : 4)

Join tables

Summarize data

Use subqueries and functions

All of the above

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

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

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

To be in the third normal form, _____. (Points : 4)

Each non-key column 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

CIS 336 Final Exam

(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

(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

(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;

(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

(TCO 2) A _____ key is a key that is composed of more than one attribute. (Points : 4)

primary
foreign
       composite
       domain

(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

(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

(TCO 7) The SQL aggregate function that determines the highest value in a given column is _____. (Points : 4)

COUNT
MAX
MAXIMUM
SUM

(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

(TCO 6) When using the MySQL DATE_FORMAT function, which code displays a full month name? (Points : 4)

%M
%m
%b
%mon

(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

(TCO 2) When identifying potential entities for an ERD, look for the _____ in the business narrative. (Points : 4)

adjectives
adverbs
nouns
verbs

(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

(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

(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)
(TCO 4) A(n) _____ provides a graphical description of a data model. (Points : 4)
Entity relationship diagram

Data dictionary

Business rule

Psuedocode

(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

(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

(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 ordered FROM orders WHERE shipdate IS NOT NULL

SELECT orderid FROM orders WHERE shipdate IS NULL;

SELECT orderid FROM orders;

(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

(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

(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

(TCO 7) What type of subquery is executed once for each row processed by the main query? (Points : 4)

Correlated

Uncorrelated

Inner
Outer

(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

(TCO 9) Which of the following is not a benefit provided by using a view? (Points : 4) 

 

(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)

 (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)

(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)

(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)

(TCO 7) Write a query to display the average retail price of all products formatted to display with two decimal places. (Points : 10)

(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)

(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)
(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)

CIS 336 Final Exam

DeVry