1. List the name, city, and state from the authors table, and the title, and type...

Transcribed Image Text:

1. List the name, city, and state from the authors table, and the title, and type from the titles table where the state of the author is CA or TN. The name of the author should be formatted as the last name followed by a comma and space followed by the first name. There should be a row produced in the result set for each row in the authors table. Order the result set by the author's last name. The query should produce the result set listed below. (Hint: LEFT OUTER JOIN statement) name Bennet, Abraham Carson, Cheryl Dull, Ann Stringer, Dirk White, Johnson Yokomoto, Akiko (20 row(s) affected) city Berkeley Berkeley Palo Alto title Oakland Menlo Park Walnut Creek state ૪૪૪ ૪૪૪ title The Busy Executive's Database Guide But Is It User Friendly? Secrets of Silicon Valley NULL NULL Prolonged Data Deprivation: Four Case Studies psychology Sushi, Anyone? trad_cook 2. Using the INSERT command, insert a new row into the employee table. The employee has an id of TYS54321M, a first name of John, a last name of Smith, a job id of 12, a job level of 25, a pub id of 0877, and a hire date of March 1, 2012. type 3. Using the UNION command, calculate new prices for the books bases on the current price for each book in the titles table. If the price is greater than or equal to $15.00 and less than or equal to $20.00, add 10% to the price of the book, and if the price is greater than $20.00, add 5% to the price of the book. Order the result set by the title. The query should produce the result set listed below. But Is It User Friendly? Computer Phobic AND Non-Phobic Individuals: Behavior Variations Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Prolonged Data Deprivation: Four Case Studies Secrets of Silicon Valley Silicon Valley Gastronomic Treats Straight Talk About Computers The Busy Executive's Database Guide business popular_comp popular_comp price 22.95 21.59 20.95 19.99 20.00 19.99 19.99 19.99 new_price 24.097500 22.669500 21.997500 21.989000 22.000000 21.989000 21.989000 21.989000 4. List the sum of quantity, and the sum of the year to date sales for each order by store id, store name, quantity, and year to date sales where the sum of the quantity is greater than 10. Display the store id, and quantity from the sales table, the store name from the stores table, and the year to date sales from the titles table. The query should produce the result set listed below. stor_id 7066 7067 7131 7896 8042 stor_name Barnum's News & Brews Doc-U-Mat: Quality Laundry and Books Fricative Bookshop Bookbeat (5 row(s) affected) au_name Green Locksley MadFeather O'Leary (4 row(s) affected) 5. List all the authors in California who have written more than 1 book. au_fname Marjorie Charlene 5 4 3 4 50 62 Stearns Michael stor_id ord_num aty Average by Storid 6380 6871 6380 7066 A2976 7066 QA7442.3 75 62 7067 D4482 10 22 40 22 7067 P2121 7067 P2121 7067 P2121 20 22 20 22 7131 N914008 20 21 7131 N914014 25 21 7131 P3087a 20 21 7131 P3087 25 21 7131 P3057 15 21 25 21 7131 P3007 7896 QQ2299 15 20 7896 TQ456 10 20 7896 X99.9 35 20 423LL9022 15 20 8042 423LL930 10 20 8042 8042 P723 25 20 0042 QA879.1 30 20 quantity 125 90 130 60 6. List the average quantity, and the sum of the quantity from the sales table by store id, and the sum of the quantity of all the sales. Display the store id, order number, and quantity. The query should produce the result set listed below. (Hint: Use OVER) SubTotal by Storld 8 8 125 125 90 90 90 50 130 130 130 130 130 130 60 60 60 80 80 80 80 ytd_sales 6140 21611 32185 8 24849 38997 133 133 223 223 223 223 353 353 353 353 353 353 413 413 413 493 493 493 493 Running Total 8 1. List the name, city, and state from the authors table, and the title, and type from the titles table where the state of the author is CA or TN. The name of the author should be formatted as the last name followed by a comma and space followed by the first name. There should be a row produced in the result set for each row in the authors table. Order the result set by the author's last name. The query should produce the result set listed below. (Hint: LEFT OUTER JOIN statement) name Bennet, Abraham Carson, Cheryl Dull, Ann Stringer, Dirk White, Johnson Yokomoto, Akiko (20 row(s) affected) city Berkeley Berkeley Palo Alto title Oakland Menlo Park Walnut Creek state ૪૪૪ ૪૪૪ title The Busy Executive's Database Guide But Is It User Friendly? Secrets of Silicon Valley NULL NULL Prolonged Data Deprivation: Four Case Studies psychology Sushi, Anyone? trad_cook 2. Using the INSERT command, insert a new row into the employee table. The employee has an id of TYS54321M, a first name of John, a last name of Smith, a job id of 12, a job level of 25, a pub id of 0877, and a hire date of March 1, 2012. type 3. Using the UNION command, calculate new prices for the books bases on the current price for each book in the titles table. If the price is greater than or equal to $15.00 and less than or equal to $20.00, add 10% to the price of the book, and if the price is greater than $20.00, add 5% to the price of the book. Order the result set by the title. The query should produce the result set listed below. But Is It User Friendly? Computer Phobic AND Non-Phobic Individuals: Behavior Variations Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Prolonged Data Deprivation: Four Case Studies Secrets of Silicon Valley Silicon Valley Gastronomic Treats Straight Talk About Computers The Busy Executive's Database Guide business popular_comp popular_comp price 22.95 21.59 20.95 19.99 20.00 19.99 19.99 19.99 new_price 24.097500 22.669500 21.997500 21.989000 22.000000 21.989000 21.989000 21.989000 1. List the name, city, and state from the authors table, and the title, and type from the titles table where the state of the author is CA or TN. The name of the author should be formatted as the last name followed by a comma and space followed by the first name. There should be a row produced in the result set for each row in the authors table. Order the result set by the author's last name. The query should produce the result set listed below. (Hint: LEFT OUTER JOIN statement) name Bennet, Abraham Carson, Cheryl Dull, Ann Stringer, Dirk White, Johnson Yokomoto, Akiko (20 row(s) affected) city Berkeley Berkeley Palo Alto title Oakland Menlo Park Walnut Creek state ૪૪૪ ૪૪૪ title The Busy Executive's Database Guide But Is It User Friendly? Secrets of Silicon Valley NULL NULL Prolonged Data Deprivation: Four Case Studies psychology Sushi, Anyone? trad_cook 2. Using the INSERT command, insert a new row into the employee table. The employee has an id of TYS54321M, a first name of John, a last name of Smith, a job id of 12, a job level of 25, a pub id of 0877, and a hire date of March 1, 2012. type 3. Using the UNION command, calculate new prices for the books bases on the current price for each book in the titles table. If the price is greater than or equal to $15.00 and less than or equal to $20.00, add 10% to the price of the book, and if the price is greater than $20.00, add 5% to the price of the book. Order the result set by the title. The query should produce the result set listed below. But Is It User Friendly? Computer Phobic AND Non-Phobic Individuals: Behavior Variations Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Prolonged Data Deprivation: Four Case Studies Secrets of Silicon Valley Silicon Valley Gastronomic Treats Straight Talk About Computers The Busy Executive's Database Guide business popular_comp popular_comp price 22.95 21.59 20.95 19.99 20.00 19.99 19.99 19.99 new_price 24.097500 22.669500 21.997500 21.989000 22.000000 21.989000 21.989000 21.989000 4. List the sum of quantity, and the sum of the year to date sales for each order by store id, store name, quantity, and year to date sales where the sum of the quantity is greater than 10. Display the store id, and quantity from the sales table, the store name from the stores table, and the year to date sales from the titles table. The query should produce the result set listed below. stor_id 7066 7067 7131 7896 8042 stor_name Barnum's News & Brews Doc-U-Mat: Quality Laundry and Books Fricative Bookshop Bookbeat (5 row(s) affected) au_name Green Locksley MadFeather O'Leary (4 row(s) affected) 5. List all the authors in California who have written more than 1 book. au_fname Marjorie Charlene 5 4 3 4 50 62 Stearns Michael stor_id ord_num aty Average by Storid 6380 6871 6380 7066 A2976 7066 QA7442.3 75 62 7067 D4482 10 22 40 22 7067 P2121 7067 P2121 7067 P2121 20 22 20 22 7131 N914008 20 21 7131 N914014 25 21 7131 P3087a 20 21 7131 P3087 25 21 7131 P3057 15 21 25 21 7131 P3007 7896 QQ2299 15 20 7896 TQ456 10 20 7896 X99.9 35 20 423LL9022 15 20 8042 423LL930 10 20 8042 8042 P723 25 20 0042 QA879.1 30 20 quantity 125 90 130 60 6. List the average quantity, and the sum of the quantity from the sales table by store id, and the sum of the quantity of all the sales. Display the store id, order number, and quantity. The query should produce the result set listed below. (Hint: Use OVER) SubTotal by Storld 8 8 125 125 90 90 90 50 130 130 130 130 130 130 60 60 60 80 80 80 80 ytd_sales 6140 21611 32185 8 24849 38997 133 133 223 223 223 223 353 353 353 353 353 353 413 413 413 493 493 493 493 Running Total 8 4. List the sum of quantity, and the sum of the year to date sales for each order by store id, store name, quantity, and year to date sales where the sum of the quantity is greater than 10. Display the store id, and quantity from the sales table, the store name from the stores table, and the year to date sales from the titles table. The query should produce the result set listed below. stor_id 7066 7067 7131 7896 8042 stor_name Barnum's News & Brews Doc-U-Mat: Quality Laundry and Books Fricative Bookshop Bookbeat (5 row(s) affected) au_name Green Locksley MadFeather O'Leary (4 row(s) affected) 5. List all the authors in California who have written more than 1 book. au_fname Marjorie Charlene 5 4 3 4 50 62 Stearns Michael stor_id ord_num aty Average by Storid 6380 6871 6380 7066 A2976 7066 QA7442.3 75 62 7067 D4482 10 22 40 22 7067 P2121 7067 P2121 7067 P2121 20 22 20 22 7131 N914008 20 21 7131 N914014 25 21 7131 P3087a 20 21 7131 P3087 25 21 7131 P3057 15 21 25 21 7131 P3007 7896 QQ2299 15 20 7896 TQ456 10 20 7896 X99.9 35 20 423LL9022 15 20 8042 423LL930 10 20 8042 8042 P723 25 20 0042 QA879.1 30 20 quantity 125 90 130 60 6. List the average quantity, and the sum of the quantity from the sales table by store id, and the sum of the quantity of all the sales. Display the store id, order number, and quantity. The query should produce the result set listed below. (Hint: Use OVER) SubTotal by Storld 8 8 125 125 90 90 90 50 130 130 130 130 130 130 60 60 60 80 80 80 80 ytd_sales 6140 21611 32185 8 24849 38997 133 133 223 223 223 223 353 353 353 353 353 353 413 413 413 493 493 493 493 Running Total 8