Use the Small Company ERD to complete the steps listed below. A. Inserting and Modifying Data...

 

Transcribed Image Text:

Use the Small Company ERD to complete the steps listed below. A. Inserting and Modifying Data 1. Download and run the M03_HOL_Script.sql script to populate the BOOK_CATEGORY and BOOK tables. 2. Open a text editor to begin writing your SQL script that will contain your SQL statements. (You may use any program that allow you to create .sql files (e.g., Notepad, Wordpad, Notes, TextEdit, Notepad++, etc.) 3. Remember, the INSERT statements in your script should be in the order in which the tables should be created (based on the foreign key constraints). 4. Run your script to ensure it runs without errors. (If your code produces errors, work to resolve them before submitting.) 5. Save the script and upload it as part of your submission. Points will be deducted if a .sql file is not submitted. Your script should contain 5 UPDATE statements, 2 DELETE statements, and 3 single-line comments. B.INSERT statements (15 points) 1. Write INSERT statements to insert the data listed in the ORDERS, ORDER_ITEMS, and CUSTOMERS tables on the next 2 pages. ORDERS TABLE ORDER_NBR CUSTOMER_ID ORDER_DATE 649512688683 185 324151439065 185 185 185 430152209691 588632209701 933411302657 965076542239 499373860704 957050047999 65868172527 714604403275 904222078731 922814204519 900853212124 593967187118 288 288 312 355 426 508 508 869 869 869 2019-03-09 2017-06-26 2020-10-01 2015-03-25 2015-01-21 2019-02-09 2017-06-17 2019-04-26 2016-04-18 2021-03-30 2018-05-14 2018-10-13 2018-03-18 2015-12-26 ORDER_ITEMS Table ORDER_ITEM_ID 28637904056633600000 25663800028063600000 68464155235711300000 71997307208349100000 57136305957312800000 45111184361031100000 79351459472267400000 19659894383050200000 16327448132059600000 16345293331300000000 70745685628809400000 35746858636498500000 27447198525788300000 99251548914643900000 85373557451769700000 90728380725731900000 57468876032291100000 4937231338855340000 15584903349880300000 7221195775361660000 98921575467765400000 74733510108021200000 80658895683305100000 90350160475703600000 78997408073354000000 43252559470829600000 26206850182085300000 ORDER_NBR 957050047999 499373860704 649512688683 922814204519 65868172527 714604403275 714604403275 900853212124 588632209701 324151439065 324151439065 933411302657 65868172527 904222078731 499373860704 649512688683 900853212124 430152209691 499373860704 593967187118 499373860704 65868172527 324151439065 499373860704 922814204519 965076542239 957050047999 BOOK_ID QUANTITY 50322 10 53434 1 53434 1 70003 1 89096 1 117356 1 124600 1 125306 1 1 1 188113 251164 286413 339453 372897 384093 386905 403678 403678 650799 650799 693371 755002 875164 875164 875164 875164 959505 966705 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 10 CUSTOMER TABLE CUSTOMER_ID FIRST_NAME LAST_NAME STREET_ADDRESS 6 Buell Pass 619 Burty Copestake Kasparski 408 Duky 508 Alix Stedall 863 Benbow 111 495 Heugh Swoffer Goldhawk Hamblin Talman Boulder 16 548 288 355 185 788 312 940 853 385 426 869 43 Sigismondo Ebeneser Aluino Otes Giustino Conn Keith Broderick Huck Thorstein Jepensen Fredrick Tewkesbury Tabor Wittier Giffy Besque Sigismondo O'Dunniom Oates Allen Rudyard Duham Haskew Jendrassik 1 Village Green Plaza Tampa 97 West Pass 72 Brown Place 831 Coolidge Crossing 0866 Oneill Street 69 Waxwing Drive 98 Sycamore Circle 10156 Beilfuss Circle 846 Sauthoff Crossing 33 Sutherland Lane 8437 Katie Hill 6632 Blackbird Street 57 Sloan Circle 06 Southridge Terrace 0 North Crossing CITY Springfield 486 Grasskamp Place 31 Saint Paul Place 23787 Lighthouse Bay Crossing Jacksonville Silver Spring Newark Sacramento Sacramento Las Vegas Reston Lafayette San Diego Raleigh Pensacola Van Nuys Camden Beaverton Oklahoma City Austin Lakeland STATE Virginia Florida Florida Maryland Delaware California California Nevada Virginia Louisiana California North Carolina Florida California New Jersey Oregon Oklahoma Texas Florida ZIP CODE COUNTRY PHONE NUMBER EMAIL 22156 33680 32204 20910 19725 95852 94291 89105 20195 70505 92176 27690 32590 91499 8104 97075 73119 78726 33811 US 571-394-6051 bcopestake0@kickstarter.com 813-684-4314 NULL US US 904-828-1995 astedall2@smh.com.au US 202-734-7820 sbenbow3@ihg.com US 302-335-9220 eheugh4@bbc.co.uk US 916-461-2868 aswoffer5@mlb.com US 916-671-1350 ogoldhawk6@pbs.org US 702-662-0739 ghamblin7@unblog.fr US 703-426-3331 US 337-909-4629 kboulder9@nydailynews.com 619-818-9125 bhucka@latimes.com US US 919-150-8515 tjepensenb@joomla.org US 850-175-1836 ftewkesburyc@vinaora.com US US US US US US NULL 213-238-8569 twittiere@cnn.com 856-572-7171 gbesquef@4shared.com NULL sodunniomg@shutterfly.com 405-967-3821 oduhamh@bloomberg.com 512-689-2030 ahaskewi@domainmarket.com 863-833-1300 NULL DATE OF BIRTH GENDER NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL C. UPDATE Statements (5 points) 1. Write UPDATE statements to modify the records listed below. a. Fredrick Tewkesbury (CUSTOMER_ID 312) provided his birthdate. It is June 12, 1996. Write the UPDATE statement update his record in the CUSTOMER table. b. The values in the COUNTRY column in the CUSTOMER table should have been entered as 'US' instead of 'United States'. Write the UPDATE statement to update all of the records that have 'United States' and change the value to 'US'. c. Ellen Abel (EMPLOYEE_ID 174) was promoted to a SA_MAN in her department. She now makes $15,099. Her new manager is Steven King (EMPLOYEE_ID 100). Write the UPDATE statement to update her record in the EMPLOYEE table. d. A few employees received raises. Write UPDATE statements to update their records in the EMPLOYEE table. Their employee IDs and new salary amounts are listed below. 143 - New salary = 2753 144 - New salary = 2653 D. DELETE statements (5 points) 1. Write DELETE statements to remove the records listed below. a. The company no longer carries the books listed below. Write the DELETE statements to remove them from the BOOKS table. (The BOOK_ID and BOOK TITLE are listed for each book.) 981311 Sookie Stackhouse 373086 The Guernsey Literary and Potato Peel Pie Society b. A customer named Sigismondo ODunniom (CUSTMER 385) would like their information removed from the database. Write the DELETE statement to perform this action. E. Comments (5 points) 1. At the beginning of your script, write single-line comments for your name, the name of the assignment, and the date. F. Writing Simple Queries Read each of the questions listed in the left column of the table below. Write the answer to your question and/or the required SQL statement in the corresponding cell in the right column that answers the question. (Test your statement to confirm the results before submitting.) Save this document once you have added your answers and uploaded it as part of your submission. Basic SELECT Statements, Concatenation, Column Aliases, and Literals (4 points) Basic SELECT Statements, Concatenation, Column Aliases, and Literals a. Describe what the asterisk (*) means in the query SELECT * FROM CUSTOMERS;. b. Write a query to display customers' first names, last names, and email addresses. Use the Small Company ERD to complete the steps listed below. A. Inserting and Modifying Data 1. Download and run the M03_HOL_Script.sql script to populate the BOOK_CATEGORY and BOOK tables. 2. Open a text editor to begin writing your SQL script that will contain your SQL statements. (You may use any program that allow you to create .sql files (e.g., Notepad, Wordpad, Notes, TextEdit, Notepad++, etc.) 3. Remember, the INSERT statements in your script should be in the order in which the tables should be created (based on the foreign key constraints). 4. Run your script to ensure it runs without errors. (If your code produces errors, work to resolve them before submitting.) 5. Save the script and upload it as part of your submission. Points will be deducted if a .sql file is not submitted. Your script should contain 5 UPDATE statements, 2 DELETE statements, and 3 single-line comments. B.INSERT statements (15 points) 1. Write INSERT statements to insert the data listed in the ORDERS, ORDER_ITEMS, and CUSTOMERS tables on the next 2 pages. ORDERS TABLE ORDER_NBR CUSTOMER_ID ORDER_DATE 649512688683 185 324151439065 185 185 185 430152209691 588632209701 933411302657 965076542239 499373860704 957050047999 65868172527 714604403275 904222078731 922814204519 900853212124 593967187118 288 288 312 355 426 508 508 869 869 869 2019-03-09 2017-06-26 2020-10-01 2015-03-25 2015-01-21 2019-02-09 2017-06-17 2019-04-26 2016-04-18 2021-03-30 2018-05-14 2018-10-13 2018-03-18 2015-12-26 ORDER_ITEMS Table ORDER_ITEM_ID 28637904056633600000 25663800028063600000 68464155235711300000 71997307208349100000 57136305957312800000 45111184361031100000 79351459472267400000 19659894383050200000 16327448132059600000 16345293331300000000 70745685628809400000 35746858636498500000 27447198525788300000 99251548914643900000 85373557451769700000 90728380725731900000 57468876032291100000 4937231338855340000 15584903349880300000 7221195775361660000 98921575467765400000 74733510108021200000 80658895683305100000 90350160475703600000 78997408073354000000 43252559470829600000 26206850182085300000 ORDER_NBR 957050047999 499373860704 649512688683 922814204519 65868172527 714604403275 714604403275 900853212124 588632209701 324151439065 324151439065 933411302657 65868172527 904222078731 499373860704 649512688683 900853212124 430152209691 499373860704 593967187118 499373860704 65868172527 324151439065 499373860704 922814204519 965076542239 957050047999 BOOK_ID QUANTITY 50322 10 53434 1 53434 1 70003 1 89096 1 117356 1 124600 1 125306 1 1 1 188113 251164 286413 339453 372897 384093 386905 403678 403678 650799 650799 693371 755002 875164 875164 875164 875164 959505 966705 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 10 CUSTOMER TABLE CUSTOMER_ID FIRST_NAME LAST_NAME STREET_ADDRESS 6 Buell Pass 619 Burty Copestake Kasparski 408 Duky 508 Alix Stedall 863 Benbow 111 495 Heugh Swoffer Goldhawk Hamblin Talman Boulder 16 548 288 355 185 788 312 940 853 385 426 869 43 Sigismondo Ebeneser Aluino Otes Giustino Conn Keith Broderick Huck Thorstein Jepensen Fredrick Tewkesbury Tabor Wittier Giffy Besque Sigismondo O'Dunniom Oates Allen Rudyard Duham Haskew Jendrassik 1 Village Green Plaza Tampa 97 West Pass 72 Brown Place 831 Coolidge Crossing 0866 Oneill Street 69 Waxwing Drive 98 Sycamore Circle 10156 Beilfuss Circle 846 Sauthoff Crossing 33 Sutherland Lane 8437 Katie Hill 6632 Blackbird Street 57 Sloan Circle 06 Southridge Terrace 0 North Crossing CITY Springfield 486 Grasskamp Place 31 Saint Paul Place 23787 Lighthouse Bay Crossing Jacksonville Silver Spring Newark Sacramento Sacramento Las Vegas Reston Lafayette San Diego Raleigh Pensacola Van Nuys Camden Beaverton Oklahoma City Austin Lakeland STATE Virginia Florida Florida Maryland Delaware California California Nevada Virginia Louisiana California North Carolina Florida California New Jersey Oregon Oklahoma Texas Florida ZIP CODE COUNTRY PHONE NUMBER EMAIL 22156 33680 32204 20910 19725 95852 94291 89105 20195 70505 92176 27690 32590 91499 8104 97075 73119 78726 33811 US 571-394-6051 bcopestake0@kickstarter.com 813-684-4314 NULL US US 904-828-1995 astedall2@smh.com.au US 202-734-7820 sbenbow3@ihg.com US 302-335-9220 eheugh4@bbc.co.uk US 916-461-2868 aswoffer5@mlb.com US 916-671-1350 ogoldhawk6@pbs.org US 702-662-0739 ghamblin7@unblog.fr US 703-426-3331 US 337-909-4629 kboulder9@nydailynews.com 619-818-9125 bhucka@latimes.com US US 919-150-8515 tjepensenb@joomla.org US 850-175-1836 ftewkesburyc@vinaora.com US US US US US US NULL 213-238-8569 twittiere@cnn.com 856-572-7171 gbesquef@4shared.com NULL sodunniomg@shutterfly.com 405-967-3821 oduhamh@bloomberg.com 512-689-2030 ahaskewi@domainmarket.com 863-833-1300 NULL DATE OF BIRTH GENDER NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL C. UPDATE Statements (5 points) 1. Write UPDATE statements to modify the records listed below. a. Fredrick Tewkesbury (CUSTOMER_ID 312) provided his birthdate. It is June 12, 1996. Write the UPDATE statement update his record in the CUSTOMER table. b. The values in the COUNTRY column in the CUSTOMER table should have been entered as 'US' instead of 'United States'. Write the UPDATE statement to update all of the records that have 'United States' and change the value to 'US'. c. Ellen Abel (EMPLOYEE_ID 174) was promoted to a SA_MAN in her department. She now makes $15,099. Her new manager is Steven King (EMPLOYEE_ID 100). Write the UPDATE statement to update her record in the EMPLOYEE table. d. A few employees received raises. Write UPDATE statements to update their records in the EMPLOYEE table. Their employee IDs and new salary amounts are listed below. 143 - New salary = 2753 144 - New salary = 2653 D. DELETE statements (5 points) 1. Write DELETE statements to remove the records listed below. a. The company no longer carries the books listed below. Write the DELETE statements to remove them from the BOOKS table. (The BOOK_ID and BOOK TITLE are listed for each book.) 981311 Sookie Stackhouse 373086 The Guernsey Literary and Potato Peel Pie Society b. A customer named Sigismondo ODunniom (CUSTMER 385) would like their information removed from the database. Write the DELETE statement to perform this action. E. Comments (5 points) 1. At the beginning of your script, write single-line comments for your name, the name of the assignment, and the date. F. Writing Simple Queries Read each of the questions listed in the left column of the table below. Write the answer to your question and/or the required SQL statement in the corresponding cell in the right column that answers the question. (Test your statement to confirm the results before submitting.) Save this document once you have added your answers and uploaded it as part of your submission. Basic SELECT Statements, Concatenation, Column Aliases, and Literals (4 points) Basic SELECT Statements, Concatenation, Column Aliases, and Literals a. Describe what the asterisk (*) means in the query SELECT * FROM CUSTOMERS;. b. Write a query to display customers' first names, last names, and email addresses.