Using Pubs (or Pubs2) sample tables to create SQL statements (1 per question) to accomplish the following (command and output required): List the first

Question

63 users unlocked this solution today!

 

Using Pubs (or Pubs2) sample tables to create SQL statements (1 per question) to accomplish the following (command and output required): 

 

1. List the first name, last name and author ID of the authors in the city of OAKLAND order by last name.
2. List all book titles that have the word "computer" anywhere in their titles.
3. List any titles, and the price of any titles that do not have a price assigned.
4. Repeat #3 but in the output, list a price of zero for all those that do not have a price assigned (do not modify the data, only the output changes).
5. List each title with its current cost and with the projected cost increase of 10%.
6. A sentence for each author that looks like this:

 

First name = ________  Last Name = _________  Initials = 

 

Example: 

First name = Harry  Last Name = Shasho  Initials = HS

 

Part II - (Aggregate Functions / Joins / Subqueries) - Using your PUBs sample tables, create SQL statements (1 per question) to accomplish the following (command and output required) 

 

1. What is the minimum price of all the books?
2. What is the count of the books for each type?
3. What is the count of books between $10 and $20
4. What is the name of the author who wrote the book with the title id MC2222?
5. For each book (title), list the publisher (the name of the publisher), order by book (title).
6. What books are priced above the current average?

 

Part III - (Aggregate Functions / Joins / Subqueries / Views) Using the database you created (your employee database), create SQL statements (1 per question) to accomplish the following (command and output required)

 

1. What is the average salary of the employees from MD?
2. What is the sum of the salaries for each state?
3. What is our employee total count for each department?
4. For each employee, list the employee last name with their department name.
5. need to make up a view of your employee table of only the employees from NY and without the salary column.  Call the view emp_NY.  need to make a select * from this view.
6. A. need to make up anew table named emp_co_phone with the following columns:  phone id int, emp_id int (or whatever data type you used in your employee table), phone_num varchar(25).  Put in 3 rows of sample data (give 3 employees company phones).   Sample below.

 

1. pid        eid        phone
2.  
3. 2          4          410-111-2222
4. 3          2          301-222-1111

 

1. NOTE:  Make sure the emp_id column is the same data type, as your employee table from previous labs and that you use emp_id's from employees in your employee table.       
2. B.  List employee name, salary, state, phone_num for all 8 employees.  For employees without phones, show 'N/A'

Part IV - (Extra Credit - Using the PUBs / PUBS2 sample tables) 1 SQL statement per question.   Show command and output. 

 

 

1. Show output of the average price of books per type (from the titles table) with the type only showing rows that have an average over 10.
2. Display the first name, last name, title, publisher name of book that are of type business.
3. Modify the last name of Anne Ringer to Anne Ringer-Shasho
4. Delete all books with total sale less than 2500 or that have no price.

This diagram shows the tables in the pubs2 database and some of the relationships among them. Figure A-1: Diagram of the pubs2 database TITLE AUTHOR au_ord royaltyper au_id AUTHORS au_id au_Iname au_fname phone address city state country postalcode 1 au_id 1 au_id AU PIX pic format_type bytesize title_id tide_id au_id 1 BLURBS piawidth_hor pixwidth_vert copy au_id TITLES sto title type pub_id price advance total_sales notes pubdate contract 1 N SALESDETAIL ord_num the aty discount title_id title_id N stor_id 1 stor_id DISCOUNTS discounttype stor lowaty highqty discount title_id title_id N pub_id pub_id N1 stor_id ord_num N stor_id stor_id ROYSCHED stor_id ord_num 1 lorange hirange royalty PUBLISHERS 1 pub_name city state SALES stor id and rum date N stor_id stor_id STORES stor_name stor_address city state country postalcode payterms