CIS336 Quiz Week 6

$5.00

CIS336 Quiz Week 6
(TCO 7) Given the two tables described below, which of the following

SKU: CIS336 Quiz Week 6 Categories: , Tags: , , ,

Description

CIS336 Quiz Week 6

CIS336 Quiz Week 6

A+

(TCO 7) Which is an aggregate function?

TRUNCATE

MAX

FLOOR

ROUND

(TCO 7) Which of the following statements is correct?

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

(TCO 7) Given the books table described below, which of the following will display each category along with the average price of books in that category?
books:   bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)

SELECT category, AVG(retail) FROM books;

SELECT AVG(retail) FROM books GROUP BY category;

SELECT category, AVG(retail) FROM books GROUP BY category;

SELECT category, AVG(retail) FROM books GROUP BY category, retail;

(TCO 7) Given the books table described below, which of the following will list the title(s) of books with the earliest publication date?
books:   bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)

SELECT title FROM books WHERE pubdate = MIN(pubdate);

SELECT title , MIN(pubdate) FROM books

SELECT title FROM (SELECT title, MIN(pubdate) FROM books);

SELECT title FROM books WHERE pubdate = (SELECT MIN(pubdate) FROM books);

(TCO 7) Which of the values listed below can be returned by a subquery referenced as follows?
WHERE vendorid = (subquery)

A single value

A column of one or more rows

A table

A subquery can’t be used in this way

(TCO 7) Which operator can be used to test that one or more rows are returned by the subquery?

IS NULL

IS NOT NULL

EXISTS

NOT EXISTS

(TCO 7) Given the two tables described below, which of the following queries will display the names of the customers whose orders have shipped?
customer:  with the fields customerid(PK), lastname, firstname
order:  with the fields orderid(PK), orderdate, shipdate, customerid(FK)

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate = orderdate);

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE orderID IS NULL);

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate IS NULL);

SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate IS NOT NULL);

(TCO 7) Given the two tables described below, which of the following queries will display number of orders not yet shipped?
customer:  with the fields customerid(PK), lastname, firstname
order:  with the fields orderid(PK), orderdate, shipdate, customerid(FK)

SELECT COUNT(shipdate) FROM order;

SELECT COUNT(*) FROM order WHERE shipdate IS NULL;

SELECT COUNT(*) FROM order WHERE shipdate IS NOT NULL;

SELECT COUNT(shipdate) FROM order GROUP BY customerid;

(TCO 7) Given the books table described below, which of the following will display the average retail price of books?
books:   bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)

SELECT COUNT(retail) FROM books;

SELECT SUM(retail) FROM books;

SELECT AVG(retail) FROM books;

SELECT AVG(retail) FROM books GROUP BY category;

(TCO 7) Which of these scenarios would justify the usage of an aggregate function?

When we need to sum up values

When we need to convert string data into date or number values

When we need to select rows from a table with a condition that depends on the data from a different table

No way to tell without an ERD