P.S: This is just a study guide. The questions may not appear exactly like this.
(TCO 7) The _____ condition is generally composed of an equality comparison between the primary key and foreign key of related tables.
1. select
2. view
3. join
4. from
Question 2
(TCO 7) The number of conditions required to join X tables would be _____.
1. X
2. X + 1
3. X – 1
4. X * X
Question 3
(TCO 7) Given the two tables described below, which of the following JOIN queries (if any) is not correct?
customer: with the fields customerid(PK), lastname, firstname
order: with the fields orderid(PK), orderdate, customerid(FK)
1. SELECT lastname, orderdate from customer JOIN order ON customer.customerid = order.customerid;
2. SELECT lastname, orderdate from customer JOIN order USING (customerid);
3. SELECT lastname, orderdate from customer, orders;
4. All of the above
Question 4
(TCO 7) In a join, column names need to be qualified only
1. in inner joins.
2. in outer joins.
3. when the code is confusing.
4. when the same column names exist in both tables.
Question 5
(TCO 7) A full outer join returns
1. rows in the left table that don’t satisfy the join condition.
2. unmatched rows from both the left and right tables.
3. rows in the right table that don’t satisfy the join condition.
4. the Cartesian product of the two tables.
Question 6
(TCO 7) Which of the following can be used to join two tables that do not have a commonly named column?
1. NATURAL JOIN
2. JOIN … USING
3. JOIN … ON
4. OUTER JOIN
Question 7
(TCO 7) Consider the ERD below. Which query would return the customer first and last name along with orderdate of each order they placed?
1. SELECT firstname, lastname, orderdate FROM customer, order;
2. SELECT firstname, lastname, orderdate FROM customer JOIN order WHERE customer.customerid = order.customerid;
3. SELECT firstname, lastname, orderdate FROM customer JOIN order ON customer.customerid = order.orderid;
4. SELECT firstname, lastname, orderdate FROM customer JOIN order ON customer.customerid = order.customerid;
Question 8
(TCO 7) Consider the ERD below. What tables will be needed to create a report showing the firstname and lastname of customers who have placed an order for any product with keyboard in the description?
1. customer, product only
2. customer, order, product only
3. customer, orderline only
4. customer, order, orderline, product
Question 9
(TCO 7) Consider the ERD below. What query will return the orderid and date of any orders with products having a price more than $50?
1. SELECT orderID, orderdate from order JOIN OrderLine JOIN Product WHERE price > 50;
2. SELECT orderID, orderdate from order, OrderLine, Product WHERE price > 50;
3. SELECT orderID, orderdate from order JOIN OrderLine ON order.orderID = OrderLine.orderID JOIN Product ON OrderLine.productID = Product.productID WHERE price > 50;
4. SELECT orderID, orderdate from order JOIN OrderLine ON order.orderID = OrderLine.orderID WHERE price > 50;
Question 10
(TCO 7) Consider the ERD below. Note that there are some products produced in-house that do not require a vendor, thus their vendorID is NULL. Also note that not all vendors currently supply products. Which query would be used to generate a report showing vendorname and productname, and will include ALL vendors even if they do not currently supply any products?
1. SELECT vendorname, productname FROM vendor LEFT JOIN product;
2. SELECT vendorname, productname FROM vendor RIGHT JOIN product;
3. SELECT vendorname, productname FROM vendor LEFT JOIN product ON vendor.vendorID = product.vendorID;
4. SELECT vendorname, productname FROM vendor RIGHT JOIN product ON vendor.vendorID = product.vendorID;