Question

For this assignment, you will use the provided database in the Unit 5 script file. You will add statements to this file to query the database tables. For your reference, below is a screenshot of the enhanced entity relationship diagram (ERD) as a reference when you write your queries. With the data types listed in the diagram, this will help you identify the types of operators that you can use for particular queries.

Use paiza.io for MySQL to execute and troubleshoot your Structured Query Language (SQL) statements. It is recommended that you use Notepad to write your SQL statements. Then, copy and paste your SQL statements to paiza.io to test your statements to ensure that they execute without errors. Save your work frequently.

The asterisk (*) indicates that a column is not null (i.e., required to have a value).

1. If you would like to see the test SQL statements, click on “Run” to see the statements execute. You should see “Hello” appear in the Output window (see the following screenshot).
2. Because you do not require these test SQL statements for your assignment, delete the sample SQL statements. To do this, select the text, and click on the Delete key on your keyboard. Your MySQL paiza.io screen should now be blank, as you see in the following screenshot. Note that if you ran the test statements, “Hello” will still appear in the Output window.
3. Copy and paste the code from the provided script file into the code editor, and click on “Run” (“Ctrl + Enter”). You should see no error messages displayed in the Output area at the bottom of your paiza.io screen. After you click on “Run,” you should see a green bar with “Success” at the top. You will see this circled in the following screenshot:
4. Next, you will write 1 query to respond to each of the following questions. Note that you can only use the information that is in the question or the enhanced ERD. Write a query to do the following:
   1. Display all of the rows in the publisher table.
   2. Display all of the books in the book table that were written by the author whose author ID is 1504192.
   3. Display all of the purchases in the shopbasket that were ordered on 2020-02-29.
   4. Display the largest quantity ordered in the shopbasket.
   5. Display the authors' first and last names sorted by their last name.
   6. Display a count of the number of orders placed via the shopbasket.
   7. Display the order date and quantity of orders in the shopbasket that have a quantity of more than 25.
   8. Display the first and last names of those authors who have a null Facebook.
   9. Display all of the customers' names, city, and state if they live in GA, FL, or CA.
   10. Display the book titles of those books that were not published by Scholastic Press.
   11. Display the first and last names of the authors and the book titles. Only display those authors who wrote a book with the word database in the title.
   12. Add a multiline comment before the first CREATE TABLE statement that includes your name, the course code (ITCO231), and the date when you are submitting your assignment.
5. Save your script file using your last name, ITCO231, U5, and the file extension .sql, as in “Smith_ITCO231_U5.sql.”
   1. Tip: When saving a Notepad file, you can change the file extension to .sql as required for the assignment by changing the “Save as type” to “All Files” and then typing the .sql extension, as shown in the example.

I didnt understand by what you mean by SQL TEST. If what you are asking me is that an SQL TEST? Yes! It is SQL QUERY TEST!!

Answer 1

a) Display All of the rows in the Publisher Table

SELECT * FROM publisher

b) Display all of the books in the book table that were written by author 1504192

SELECT * FROM book WHERE author_id = 1504192

c) Display all of the purchases in shopbasket that were ordered on 2020-02-29

SELECT * FROM shopbasket WHERE order_date = '2020-02-29'

d) Display Largest Quantity in ShopBasket. Here we will use the MAX aggregation Function.

SELECT MAX(quantity) FROM shopbasket

e) Display author First and Last name, sorted by last name

SELECT first_name, last_name FROM author order by last_name

f) Display number of orders placed via shopbasket. Here we will use the Count Aggregation Function to get the total rows.

SELECT count(1) from shopbasket

g) Display order date and quantity of orders in shoporders where quantity more than 25

SELECT order_date, quantity FROM shopbasket where quantity > 25

h) Display First and last Names of Author who have null facebook

SELECT first_name, last_name FROM auhtor where facebook IS NULL

i) Display Customers Name, City and State if they live in GA,FL or CA

SELECT first_name,last_name, city,state FROM customer where state in ('GA','FL','CA')

j) Display book titles of those books that were not published by Scholastic Press

SELECT b.title FROM book b JOIN publisher p ON b.author_id = p.author_id where p.name <> 'Scholastic Press'

k) Display the first and last names of the authors and the book titles. Only display those authors who wrote a book with the word database in title. Here wie will use the Wild card operator "LIKE"

SELECT a.first_name, b.last_name, b.title FROM author a JOIN book b ON a.id=b.author_id WHERE b.title LIKE '%database%'

HOPE YOU LIKE IT AND PLEASE GIVE UPVOTE.

IF YOU HAVE ANY DOUBT, LET ME KNOW IT IN THE COMMENT SECTION. I WILL REPLY ASAP.

HAVE A NICE DAY.

THANK YOU.