Need help on the following SQL Statements. These questions are from the following book, A guide to SQ ISBN number 978-0-357-361689. On pages 169-170 are questions #4, 5, 6, 9,11, 13, 19 and critical thinking #1. Also, extra statement similar to critical thinking #1 from p. 133 using UNION. Please note that I am using MySQL Workbench, not oracle.

5.4. Use the IN operator to find the ID, first name, and last name of each customer for which an invoice was created on November 15, 2021.

ANSWER:

5.5 Repeat Exercise 4, but this time use the EXISTS operator in your answer. Use the EXIST operator to find the ID, first name, and last name of each customer for which an invoice was created on November 15, 2021.

ANSWER:

5.6 Find the ID, first name, and last name of each customer for which an invoice was not created on November 15, 2021. (Use a subquery, either using IN or EXIST keyword.) NOTE: (There are two possible answers depending on if you include those who dont have any invoices at all.)

ANSWER:

5.9. Use a subquery to find the sales rep ID, first name, and last name of each sales rep who represents at least one customer with a credit limit of $500. List each sales rep only once in the results.

ANSWER:

5.11 Find the ID, first name, and last name of each customer that currently has an invoice on file for Wild Bird Food (25 lb). (Use multi-levels of nested subqueries.)

ANSWER:

5.13 List the invoice number and invoice date for each invoice created for the customer James Gonzalez. (Use a subquery)

ANSWER:

5.18 List the item ID, description, unit price, and category for each item that has a unit price greater than the unit price of every item in category CAT. Use either the ALL or ANY operator in your query. (Hint: Make sure you select the correct operator.) Give a second query statement to do this again using either the MIN or MAX aggregate function.

ANSWER:

Critical Thinking

1. If you used ALL in Exercise 18, repeat the exercise using ANY. If you used ANY, repeat the exercise using ALL, and then run the new command. What question does the new command answer? Give a second query statement to do this again using either the MIN or MAX aggregate function.

ANSWER:

Critical Thinking Page 133,

Critical Thinking #1 with word AND changed to OR: List the item ID and description of all items that are in the DOG or CAT category OR contain the word Small in the description. Use multiple steps to find the solution: ALSO use UNION instead of AND for the Third part.

Step 1. What statement will List the item ID and description of all items that are in the DOG or CAT category?

ANSWER:

Step 2. What statement will List the item ID and description of all items that contain the word Small in the description?

ANSWER:

Step 3. Combine these results using UNION to get your final answer for List the item ID and description of all items that are in the DOG or CAT category and contain the word Small in the description.

ANSWER:

Need help on the following SQL Statements. These questions are from the following book, "A guide to SQ" ISBN number 978-0-357-361689. On pages 169-170 are questions #4, 5, 6, 9, 11, 13, 19 and critical thinking #1. Also, extra statement similar to critical thinking #1 from p. 133 using UNION. Please note that I am using MySQL Workbench, not oracle. 5.4. Use the IN operator to find the ID, first name, and last name of each customer for which an invoice was created on November 15, 2021. ANSWER: 5.5 Repeat Exercise 4, but this time use the EXISTS operator in your answer. Use the EXIST operator to find the ID, first name, and last name of each customer for which an invoice was created on November 15, 2021. ANSWER: 5.6 Find the ID, first name, and last name of each customer for which an invoice was not created on November 15, 2021. (Use a subquery, either using IN or EXIST keyword.) NOTE: (There are two possible answers depending on if you include those who don't have any invoices at all.) ANSWER: 5.9. Use a subquery to find the sales rep ID, first name, and last name of each sales rep who represents at least one customer with a credit limit of $500. List each sales rep only once in the results. ANSWER: 5.11 Find the ID, first name, and last name of each customer that currently has an invoice on file for Wild Bird Food (25 lb). (Use multi-levels of nested subqueries.) ANSWER: 5.13 List the invoice number and invoice date for each invoice created for the customer James Gonzalez. (Use a subquery) ANSWER: 5.18 List the item ID, description, unit price, and category for each item that has a unit price greater than the unit price of every item in category CAT. Use either the ALL or ANY operator in your query. (Hint: Make sure you select the correct operator.) Give a second query statement to do this again using either the MIN or MAX aggregate function. ANSWER: Critical Thinking 1. If you used ALL in Exercise 18, repeat the exercise using ANY. If you used ANY, repeat the exercise using ALL, and then run the new command. What question does the new command answer? Give a second query statement to do this again using either the MIN or MAX aggregate function. ANSWER: Critical Thinking Page 133, Critical Thinking #1 with word AND changed to OR: List the item ID and description of all items that are in the DOG or CAT category OR contain the word "Small" in the description. Use multiple steps to find the solution: ALSO use UNION instead of AND for the Third part. Step 1. What statement will List the item ID and description of all items that are in the DOG or CAT category? ANSWER: Step 2. What statement will List the item ID and description of all items that contain the word "Small" in the description? ANSWER: Step 3. Combine these results using UNION to get your final answer for List the item ID and description of all items that are in the DOG or CAT category and contain the word "Small" in the description. ANSWER: A BETRO 93 Dont Limit 267 WHERE (invoice_line.item_id = item.item_id) 268 AND (Invoice_num= '24233'); 269 . use kimtay; 270. select item_id, first_name, last_name from customer where item_id in (select invoice_num from invoice_line where ite 271 select from customer; 270. 271 . use kimtay; select item_id, first_name, la select * from customer; select * from invoice_line; select * from invoices; 272 273 CUST_ID 125 SCHEMAS a Filter objects Triggers invoice_line Columns INVOICE_NUM ITEM_ID QUANTITY QUOTED_PRICE Indexes Foreign Keys Triggers invoices Columns INVOICE_NUM INVOICE_DATE CUST_ID Indexes Foreign Keys Triggers item Columns ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION PRICE Indexes Foreign Keys Triggers sales_rep Columns REP_ID Administration Schemas Result Grid U Filter Rows: INVOICE_NUM INVOICE_DATE 14216 2021-11-15 14219 2021-11-15 14222 2021-11-16 14224 2021-11-16 14228 2021-11-18 14231 2021-11-18 14233 2021-11-18 14237 2021-11-19 NULL NULL 227 294 182 435 125 435 616 NULL SCHEMAS a Filter objects 270 271 . Dont Limit select item_id, first_name, last_name from customer where item_i select * from customer; select * from invoice_line; select * from invoices; select * from item;| 272. 273 . 274. Edit: Export/Import: ON HAND CATEGORY LOCATION PRICE 12 BC33 B B 10 15 c 27 B 41 12 Foreign Keys Triggers item 03 Columns ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION PRICE Indexes Foreign Keys Triggers sales_rep Columns REP_ID FIRST_NAME LAST_NAME ADDRESS CITY STATE POSTAL CELL_PHONE COMMISSION RATE Indexes Foreign Keys Triggers Views Stored Procedures Functions sys Administration Schemas Result Grid I Filter Rows: ITEM_ID DESCRIPTION > AD72 Dog Feeding Station Feathers Bird Cage (12x24x 18) CA75 Endosed Cat Litter Station DT12 Dog Toy Gift Set FM23 Fly Mask with Ears FS39 Folding Saddle Stand FS42 Aquarium (55 Gallon) KH81 Wild Bird Food (25 lb) LD 14 Locking Small Dog Door LP73 Large Pet Carrier PF 19 Pump & Filter Kit QB92 Quilted Stable Blanket SP91 Small Pet Carrier UF39 Underground Fence System Insulated Water Bucket NULL NULL A 5 24 DOG BRD CAT DOG HOR HOR FSH BRD DOG DOG FSH HOR CAT DOG HOR c 79.99 79.99 39.99 39.99 24.95 39.99 124.99 19.99 49.99 59.99 74.99 119.99 39.99 199.99 79.99 NULL 14 23 A B A 5 32 18 B A WB49 7 34 NULL NULL NULL Dont Limit SCHEMAS a Filter objects Foreign Keys Triggers item Columns ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION . PRICE Indexes Foreign Keys Triggers 271. select * from customer; 272. select * from invoice_line; 273 . select * from invoices; 274. select * from item; 275 select * from sales_reps| 270. 271 . use kimtay; select item_id, first_name, la select * from customer; select * from invoice_line; select * from invoices; 272 273 CUST_ID 125 SCHEMAS a Filter objects Triggers invoice_line Columns INVOICE_NUM ITEM_ID QUANTITY QUOTED_PRICE Indexes Foreign Keys Triggers invoices Columns INVOICE_NUM INVOICE_DATE CUST_ID Indexes Foreign Keys Triggers item Columns ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION PRICE Indexes Foreign Keys Triggers sales_rep Columns REP_ID Administration Schemas Result Grid U Filter Rows: INVOICE_NUM INVOICE_DATE 14216 2021-11-15 14219 2021-11-15 14222 2021-11-16 14224 2021-11-16 14228 2021-11-18 14231 2021-11-18 14233 2021-11-18 14237 2021-11-19 NULL NULL 227 294 182 435 125 435 616 NULL SCHEMAS a Filter objects 270 271 . Dont Limit select item_id, first_name, last_name from customer where item_i select * from customer; select * from invoice_line; select * from invoices; select * from item;| 272. 273 . 274. Edit: Export/Import: ON HAND CATEGORY LOCATION PRICE 12 BC33 B B 10 15 c 27 B 41 12 Foreign Keys Triggers item 03 Columns ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION PRICE Indexes Foreign Keys Triggers sales_rep Columns REP_ID FIRST_NAME LAST_NAME ADDRESS CITY STATE POSTAL CELL_PHONE COMMISSION RATE Indexes Foreign Keys Triggers Views Stored Procedures Functions sys Administration Schemas Result Grid I Filter Rows: ITEM_ID DESCRIPTION > AD72 Dog Feeding Station Feathers Bird Cage (12x24x 18) CA75 Endosed Cat Litter Station DT12 Dog Toy Gift Set FM23 Fly Mask with Ears FS39 Folding Saddle Stand FS42 Aquarium (55 Gallon) KH81 Wild Bird Food (25 lb) LD 14 Locking Small Dog Door LP73 Large Pet Carrier PF 19 Pump & Filter Kit QB92 Quilted Stable Blanket SP91 Small Pet Carrier UF39 Underground Fence System Insulated Water Bucket NULL NULL A 5 24 DOG BRD CAT DOG HOR HOR FSH BRD DOG DOG FSH HOR CAT DOG HOR c 79.99 79.99 39.99 39.99 24.95 39.99 124.99 19.99 49.99 59.99 74.99 119.99 39.99 199.99 79.99 NULL 14 23 A B A 5 32 18 B A WB49 7 34 NULL NULL NULL Dont Limit SCHEMAS a Filter objects Foreign Keys Triggers item Columns ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION . PRICE Indexes Foreign Keys Triggers 271. select * from customer; 272. select * from invoice_line; 273 . select * from invoices; 274. select * from item; 275 select * from sales_reps|