Queries
https://breeze.careeredonline.com/pwaaxmlmp812player/?session=breezbreezfpptyp7cw9nc54vx
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 table names, column names, and data types listed in the diagram, this will help you identify the tables and columns required to answer the query questions as well as 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).
Example statements to CREATE TABLE, REPLACE INTO the table, and SELECT all data FROM the table are shown when you first access the paiza.io webpage. 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).
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.
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:
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:
Display all of the rows in the publisher table.
Display all of the books in the book table that were written by the author whose author ID is 1504192.
Display all of the purchases in the shopbasket that were ordered on 2020-02-29.
Display the largest quantity ordered in the shopbasket.
Display the authors’ first and last names sorted by their last name.
Display a count of the number of orders placed via the shopbasket.
Display the order date and quantity of orders in the shopbasket that have a quantity of more than 25.
Display the first and last names of those authors who have a null Facebook.
Display all of the customers’ names, city, and state if they live in GA, FL, or CA.
Display the book titles of those books that were not published by Scholastic Press.
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.
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.
Save your script file using your last name, ITCO231, U5, and the file extension .sql, as in “Simon_ITCO231_U5.sql.” <–this is my last name, Simon.
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.
Note that you must include the provided script file statements in paiza.io and then paste your query after the final insert statement. You can paste multiple queries to test at the same time, but note that the output will not be separated in the Output area. It is recommended to test one query at a time and then save that query to your script file once it executes correctly. Be sure to end each query appropriately.
Reference
paiza.io. (n.d.). Retrieved from https://paiza.io/en/projects/new?language=mysql
