SQL Exam 2

Ch. 3-6. Rename the EMPLOYEES table as JL_EMPS.

RENAME employees TO jl_emps;

Ch. 11-6. Determine the average profit generated by orders in the ORDERS table. Note: The total profit by order must be calculated before finding the average profit.

SELECT AVG(SUM((retail-cost)*quantity)) FROM orders JOIN orderitems USING(order#) JOIN books USING(isbn) GROUP BY order#;

Ch. 11-1. Determine how many books are in the Cooking category.

SELECT COUNT(*) FROM books WHERE category = 'COOKING';

Ch. 11-2. Display the number of books with a retail price of more than $30.00.

SELECT COUNT(*) FROM books WHERE retail > 30;

Ch. 12-9. Determine the number of different customers who have placed an order for books written or co-written by James Austin.

SELECT COUNT(DISTINCT customers#) FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN bookauthor USING(isbn) JOIN author USING(authorid) WHERE lname= 'AUSTIN' AND fname = 'JAMES');

Ch. 10-7. Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each different length value is displayed only once (not once for each book).

SELECT DISTINCT LENGTH(isbn) FROM books;

Ch. 11-9. List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.

SELECT DISTINCT firstname, lastname FROM customers JOIN orders USING(customer#) JOIN orderitems USING(order#) JOIN books USING(isbn) WHERE (state = 'FL' or state = 'GA') GROUP BY order#, firstname, lastname HAVING SUM(retail*quantity)>80;

Ch. 10- 1. Produce a list of all customers names in which the first letter of the first and last names is in uppercase and the rest are in lowercase.

SELECT INITCAP (firstname), INITCAP(lastname) FROM customers;

Ch. 11-3. Display the most recent publication date of all books sold by JustLee Books.

SELECT MAX(pubdate) FROM books;

Ch. 11-10. What's the retail price of the most expensive book written by Lisa White?

SELECT MAX(retail) FROM books JOIN bookauthor USING(isbn) JOIN author USING(authorid) WHERE lname = 'WHITE' AND fname = 'LISA';

Ch. 11-5. List the retail price of the least expensive book in the Computer category.

SELECT MIN(retail) FROM books WHERE category = 'COMPUTER';

Ch. 10-9. Determine the calendar date of the next occurrence of Wednesday, based on today's date.

SELECT NEXT_DAY(SYSDATE, 'WEDNESDAY') FROM dual;

Ch. 11-4. Determine the total profit generated by sales to customer 1017. Note: Quantity should be reflected in the total profit calculation.

SELECT SUM((retail-cost)*quantity) FROM orders JOIN orderitems USING(order#) JOIN books USING(isbn) WHERE customer# = 1017;

Ch. 10-5. Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you're using.

SELECT TO_CHAR(CURRENT_DATE, 'DAY, HH:MI:SS') FROM dual;

Ch. 12-2. Determine which books cost less than the average cost of other books in the same category.

SELECT a.title, b.category, a.cost FROM books a, (SELECT category, AVG(cost) averagecost FROM books GROUP BY category) b WHERE a.category = b.category AND a.cost < b.averagecost;

Ch. 3-5. Delete the EndDate column from the EMPLOYEES table.

ALTER TABLE employees DROP column enddate;

Ch. 3-4. Modify the Job_class column of the EMPLOYEES table so that it allows storing a maximum width of two characters.

ALTER TABLE employees MODIFY job_class VARCHAR2(2);

Ch. 3-8. Mark the Category column of the BOOK_PRICING table as unused. Verify that the column is no longer available.

ALTER TABLE book_pricing SET UNUSED(category); SELECT * FROM book_pricing;

Ch. 3-3. Add two columns to the EMPLOYEES table. One column, named EmpDate, contains the date of employment for each employee, and its default value should be the systemdate, The second column, named EndDate, contains employees' date of termination.

ALTER TABLE employees ADD (empdate DATE DEFAULT SYSDATE, enddate DATE);

Ch. 3-7. Create a new table containing these four columns from the existing BOOKS table: ISBN, Cost, Retail, and Category. The name of the ISBN column should be ID, and the other columns should keep their original names. Name the new table BOOK_PRICING.

CREATE TABLE book_pricing (id, cost, retail, category) AS (SELECT isbn,cost, retail, category FROM books);

Ch. 3-1. Create a new table containing the category code and description for the categories of books sold by JustLee Books. The table should be called CATEGORY, and the columns should be CatCode and CatDesc. The CatCode column should store a maximum of 2 characters, and the CatDesc column should store a maximum of 10 characters.

CREATE TABLE category (catcode VARCHAR2(2), catdesc VARCHAR2(10));

Ch. 3-2. Create a new table containing these four columns: Emp#, Lastname, Firstname, and Job_class. The table name should be EMPLOYEES. THE Job_class column should be able to store character strings up to a maximum length of four, but the column values shouldn't be padded if the value has less than four characters. The Emp# column contains a numeric ID and should allow five-digit number. Use column sizes you consider suitable for the Firstname and Lastname columns.

CREATE TABLE employees (emp# NUMBER(5), lastname VARCHAR2(15), fistname VARCHAR2(10), job_class VARCHAR2(4));

Ch. 3-10. Delete the BOOK_PRICING tale permanently so that it isn't moved to the recycle bin. Delete the JL_EMPS table so that it can be restored. Restore the JL_EMPS table and verify that it's available again.

DROP TABLE book_pricing PURGE; DROP TABLE jl_emps; FLASHBACK TABLE jl_emps TO BEFORE DROP; SELECT * FROM jl_emps;

Ch. 12-8. Determine which customers placed orders for the least expensive book (in terms of regular retail price) carried by JustLee Books.

SELECT customer# FROM customers JOIN orders USING(customer#) JOIN orderitems USING(order#) JOIN books USING(isbn) WHERE retail = (SELECT MIN(retail) FROM books);

Ch. 11-7. Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn't recently placed an order with JustLee Books.

SELECT customer#, COUNT(*) FROM orders GROUP BY customer#;

Ch. 10-10. Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number, if it exists.

SELECT customer#, SUBSTR(zip, 3, 2), INSTR(customer#, 3) FROM customers;

Ch. 10-2. Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text "NOT REFERRED" if the customer wasn't referred to JustLee Books by another customer or "REFERRED" if the customer was referred.

SELECT firstname, lastname, NVL2(referrred, 'REFERRED', 'NOT REFERRED') FROM customers;

Ch. 12-5. Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books.

SELECT lname, fname FROM bookauthor JOIN author USING(authorid) WHERE isbn IN (SELECT isbn FROM orderitems GROUP BY isbn HAVING SUM(quantity) = (SELECT MAX(COUNT(*)) FROM orderitems GROUP BY isbn));

Ch. 11-8. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the group with an average retail price greater than $50.

SELECT name, category, AVG(retail) FROM books JOIN publisher USING(pubid) WHERE category IN('COMPUTER', 'CHILDREN') GROUP BY name, category;

Ch. 12-3. Determine which orders were shipped to the same state as order 1014.

SELECT order# FROM orders WHERE shipstate = (SELECT shipstate FROM orders WHERE order# = 1014);

Ch. 12-4. Determine which orders has a higher total amount due than order 1008.

SELECT order#, SUM(quantity * paideach) FROM orderitems GROUP BY order# HAVING SUM(quantity * paideach) > (SELECT SUM(quantity * paideach) FROM orderitems WHERE order# = 1008);

Ch. 12-7. List the shipping city and state for the order that had the longest shipping delay.

SELECT shipcity, shipstate FROM orders WHERE shipdate-orderdate = (SELECT MAX(shipdate-orderdate) FROM orders);

Ch. 12-6. List the title of all books in the same category as books previously purchased by customer 1007. Don't include books this customer has already purchased.

SELECT title FROM books WHERE category IN (SELECT DISTINCT category FROM books JOIN orderitems USING(isbn) JOIN orders USING(order#) WHERE customers# = 1007) AND isbn NOT IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# = 1007);

Ch. 12-10. Determine which books were published by the publisher of The Wok Way to Cook.

SELECT title FROM books WHERE pubid = (SELECT pubid FROM books WHERE title = 'THE WOK WAY TO COOK');

Ch. 10-6. Create a list of all book titles and costs. Precede each book's cost with asterisks so that the width of the displayed Cost field is 12.

SELECT title, LPAD(cost, 12, '*') FROM books;

Ch. 10-4. Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplies by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the difference between the retail and cost amount as a percent of the cost.)

SELECT title, ROUND((retail-cost)/cost*100,0)||'%' FROM books;

Ch. 10- 3. Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.

SELECT title, TO_CHAR(quantity*(paideach-cost), '$999.99') FROM books JOIN orderitems USING(isbn) WHERE order# = 1002;

Ch. 10-8. Using today's date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age.

SELECT title, pubdate, SYSDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, pubdate),0)Age FROM books;

Ch. 12-1. List the book title and retail price for all books with a retail price lower than the average retail price of all books sold by JustLee Books.

SELECT title, retail FROM books WHERE retail > (SELECT AVG(retail) FROM books);

Ch. 3-9. Truncate the BOOK_PRICING table, and then verify that the table still exists but no loner contains any data.

TRUNCATE TABLE book_pricing; SELECT * FROM books_pricing;