Using Pubs (or Pubs2) sample tables to create SQL statements (1 per question) to accomplish the following
Question:
Using Pubs (or Pubs2) sample tables to create SQL statements (1 per question) to accomplish the following (command and output required):
- List the first name, last name and author ID of the authors in the city of OAKLAND order by last name.
- List all book titles that have the word "computer" anywhere in their titles.
- List any titles, and the price of any titles that do not have a price assigned.
- 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).
- List each title with its current cost and with the projected cost increase of 10%.
- 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)
- What is the minimum price of all the books?
- What is the count of the books for each type?
- What is the count of books between $10 and $20
- What is the name of the author who wrote the book with the title id MC2222?
- For each book (title), list the publisher (the name of the publisher), order by book (title).
- 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)
- What is the average salary of the employees from MD?
- What is the sum of the salaries for each state?
- What is our employee total count for each department?
- For each employee, list the employee last name with their department name.
- 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.
- 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.
- pid eid phone
- 2 4 410-111-2222
- 3 2 301-222-1111
- 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.
- 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.
- 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.
- Display the first name, last name, title, publisher name of book that are of type business.
- Modify the last name of Anne Ringer to Anne Ringer-Shasho
- Delete all books with total sale less than 2500 or that have no price.