Expected duration
8–10 hours
Deadline
1645 on Lesson 20
Points
75 points

Learning Objectives

Help Policy

Authorized Resources
Any, except posted solutions
Notes
Never copy another person’s work and submit it as your own.

Do not jointly work on this homework, but you may ask classmates questions about SQL syntax.

You must document all help received from all sources, including the instructor and instructor-provided course materials (including your textbook). One exception: you do not need to document any resources related to SQL syntax (e.g., PostgreSQL’s Reference for SQL Commands).

DFCS will recommend a course grade of F for any cadet who egregiously violates this Help Policy or contributes to a violation by others.

Instructions

Use the provided link (distributed separately – look in Blackboard under “Assignments” for the invitation link) to access the assignment in GitHub Classroom.

Load the Ch07_ConstructCo.sql and Ch07_LargeCo.sql files to populate the data for this assignment. Both files are in the Git repository that is created in GitHub Classroom.

Modify the file in the src directory that corresponds to the question. You do not need to delete the SQL comment that appears at the beginning of the file – it is included as a convenience when writing the query.

Travis CI will test all the queries when you push your changes to GitHub. If the results from executing your query matches the expected results, then your query is likely to be correct; otherwise, you should review your query and the test results to determine where they differ. You should not modify any files in the test directory; doing so could prevent the automated tests from running correctly. Please also note that the entire class must share the resources provided by Travis so there may be a significant delay before running the tests. You should test each query against a local copy of the database before pushing it to GitHub.

Write SQL queries to answer the following questions from Database Systems: Design, Implementation, & Management (Coronel and Morris, 2017):

ConstructCo

The ConstructCo database (ER diagram) stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project.

  1. Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Note that JOB_CODE is a foreign key to JOB. (Problem 7.1)

    Attribute (Field) Name Data Declaration
    EMP_NUM INTEGER
    EMP_LNAME VARCHAR(15)
    EMP_FNAME VARCHAR(15)
    EMP_INITIAL CHAR(1)
    EMP_HIREDATE DATE
    JOB_CODE INTEGER

  2. Having created the table structure in the prior problem, write the SQL code to enter the first two rows for the following table. (Problem 7.2)

    EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE
    101 News John G 2000-11-08 502
    102 Senior David H 1989-07-12 501
    103 Arbough June E 1995-12-01 500
    104 Ramoras Anne K 1987-11-15 501
    105 Johnson Alice K 1993-02-01 502
    106 Smithfield William   2004-06-22 500
    107 Alonzo Maria D 1993-10-10 500
    108 Washington Ralph B 1991-07-22 501
    109 Smith Larry W 1997-07-18 501
  3. Assuming that the data shown in the EMP_1 table have been entered (see prior table), write the SQL code that will list all attributes for a job code of 502. Sort the results by employee number in ascending order. (Problem 7.3)
  4. Write SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. (Problem 7.4)
  5. Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. Hint: Use logical operators to include all of the information given in this problem. (Problem 7.5)

LargeCo

The LargeCo database (ER diagram) stores data for a company that sells paint products. The company tracks the sale of products to customers. The database keeps data on customers (LGCUSTOMER), sales (LGINVOICE), products (LGPRODUCT), which products are on which invoices (LGLINE), employees (LGEMPLOYEE), the salary history of each employee (LGSALARY_HISTORY), departments (LGDEPARTMENT), product brands (LGBRAND), vendors (LGVENDOR), and which vendors supply each product (LGSUPPLIES). Some of the ctables contain only a few rows of data, while other tables are quite large; for examples, there are only eight departments, but more than 3300 invoices containing over 11000 invoice lines.

  1. Write a query to display the SKU (stock keeping unit), description, type, base, category, and price for all products that have a PROD_BASE of water and a PROD_CATEGORY of sealer. (Problem 7.45)

    PROD_SKU PROD_DESCRIPT PROD_TYPE PROD_BASE PROD_CATEGORY PROD_PRICE
    1403-TUY Sealer, Water Based, for Concrete Floors Interior Water Sealer 42.99

  2. Write a query to display the first name, last name, and email address of employees hired from January 1, 2003, to December 31, 2012. Sort the output by last name and then by first name. (Problem 7.46)

    EMP_FNAME EMP_LNAME EMP_EMAIL
    Sue Ash s.ash98@lgcompany.com
    Alida Blackwell a.blackwell99@lgcompany.com
    Phoebe Bledsoe p.bledso99@lgcompany.com
    Valarie Bledsoe v.bledso99@lgcompany.com
    Wilford Burgos w.burgos6@lgcompany.com
    Kasey Cash k.cash0@lgcompany.com
    Danica Castle c.danica99@lgcompany.com
    Doug Caudill c.doug0@lgcompany.com
    Lucio Caudill l.caudil4@lgcompany.com
    Hannah Coleman h.colema7@lgcompany.com
    Phillis Conklin p.conkli4@lgcompany.com
    Lee Connor l.connor99@lgcompany.com

  3. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 300 or have the title “CLERK I.” Sort the output by last name and then by first name. (Problem 7.47)

    EMP_FNAME EMP_LNAME EMP_PHONE EMP_TITLE DEPT_NUM
    Lavina Acevedo 862-6787 Associate 300
    Lauren Avery 550-2270 Senior Associate 300
    Rosalba Baker 632-8197 Associate 300
    Fern Carpenter 735-4820 Purchasing Specialist 300
    LeeAnn Clinton 616-9615 Clerk I 600
    Tanika Crane 449-6336 Purchasing Specialist 300
    Sammy Diggs 525-2101 Senior Associate 300
    Lana Dowdy 471-8795 Senior Associate 300
    Stephanie Dunlap 618-8203 Buyer - Raw Materials 300
    Hal Fisher 676-3662 Senior Associate 300
    Lindsay Good 337-9570 Clerk I 600
    LeeAnn Horn 828-4361 Senior Associate 300

  4. Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 83731, 83745, and 84039. Sort the output by employee number and salary “from” date. (Problem 7.48)

    EMP_NUM EMP_LNAME EMP_FNAME SAL_FROM SAL_END SAL_AMOUNT
    83731 Vargas Sheron 7/15/2012 7/14/2013 43740
    83731 Vargas Sheron 7/15/2013 7/13/2014 48110
    83731 Vargas Sheron 7/14/2014 7/14/2015 49550
    83731 Vargas Sheron 7/15/2015   51040
    83745 Spicer Dwain 8/2/2009 8/1/2010 56020
    83745 Spicer Dwain 8/2/2010 8/2/2011 57700
    83745 Spicer Dwain 8/3/2011 8/1/2012 63470
    83745 Spicer Dwain 8/2/2012 8/1/2013 68550
    83745 Spicer Dwain 8/1/2013 7/31/2014 71980
    83745 Spicer Dwain 8/1/2014 8/1/2015 74140
    83745 Spicer Dwain 8/2/2015   76360
    84039 Coleman Hannah 6/28/2012 6/27/2013 47380
    84039 Coleman Hannah 6/27/2013 6/26/2014 51170
    84039 Coleman Hannah 6/27/2014 6/27/2015 52700
    84039 Coleman Hannah 6/28/2015   54280

  5. Write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2015, and July 31, 2015. If a customer purchased more than one such product, display the customer’s information only once in the output. Sort the output by state, last name, and then first name. (Problem 7.49)

    CUST_FNAME CUST_LNAME CUST_STREET CUST_CITY CUST_STATE CUST_ZIP
    Lupe Sanitana 1292 West 70th Place Phenix City AL 36867
    Hollis Stiles 1493 Dolly Madison Circle Snow Hill AL 36778
    Lisette Whittaker 339 Horthpark Drive Montgomery AL 36197
    Deadndre Jamison 1571 Hanes Street Miami FL 33169
    Cathleen Whitman 1712 Northfield Drive Marshallville GA 31057
    Sherie Stover 640 Mountain View Drive Parksville KY 40464
    Bryce Hogan 1860 Imlach Drive Newbury MA 01951
    Shelby Salas 486 Susitna View Court North Tisbury MA 02568
    Jermaine Hancock 1627 Sunders Road Ellicott City MD 21041
    Whitney Whitfield 1259 Rhone Street Phippsburg ME 04567
    Monroe Allison 272 Schodde Street Kalamazoo MI 49002
    Darleen Parra 561 Collie Hill Way Madison MS 39130
    Clinton Aguirre 1651 Vanguard Drive Franklinville NC 27248
    Tommie Palmer 933 Elcadore Circle Arapahoe NC 28510
    Jefferey McBride 1043 Rockridge Drive Glenwood NJ 07418
    Sidney Garza 772 Sheppard Drive Fair Harbor NY 11706
    Tamela Guidry 1873 Baxter Road Brooklyn NY 11252
    Karen Levine 1534 Palmer Court Cincinatti OH 45218
    Stephenie McKenzie 1039 Delaware Place Wilkes Barre PA 18763
    Lan Nichols 367 Lakeview Drive Pittsburgh PA 15262
    Kasey Sosa 975 West 96th Avenue Kinzer PA 17535
    Shelby Thayer 1634 Ruane Road Bordeaux SC 29835
    Wilson Bell 1127 Cunningham Street Louisville TN 37777
    Renate Ladd 652 Lewis Street Crystal City VA 22202
    Melonie Jimenez 848 Downey Finch Lane East Monkton VT 05443

  6. Write a query to display the employee number, last name, email address, title, and department name of each employee who job title is “ASSOCIATE.” Sort the output by department name, employee title, and employee number. (Problem 7.50)

    EMP_NUM EMP_LNAME EMP_EMAIL EMP_TITLE DEPT_NAME
    84526 Lassiter f.lassit8@lgcompany.com Associate Accounting
    83517 Albright so.albri96@lgcompany.com Associate Accounting
    84386 Rivera d.rivera76@lgcompany.com Associate Accounting
    83378 Dunham f.dunham5@hgcompany.com Associate Accounting
    83538 Rollins m.rollin99@lgcompany.com Associate Accounting
    83661 Finn d.finn87@lgcompany.com Associate Accounting
    84383 Washington l.washin98@lgcompany.com Associate Customer Service
    84206 Healy n.healy82@lgcompany.com Associate Customer Service
    83451 Ellis r.ellis81@lgcompany.com Associate Customer Service
    84442 Gregory a.gregor95@lgcompany.com Associate Customer Service
    84459 Gilliam e.gillia10@lgcompany.com Associate Customer Service
    84300 Seay a.seay75@lgcompany.com Associate Customer Service

  7. Write a query to display a brand name and the number of products of that brand that are in the database. Sort the output by the brand name. (Problem 7.51)

    BRAND_NAME NUMPRODUCTS
    Binder Prime 27
    Busters 25
    Foresters Best 15
    Home Comfort 36
    Le Mode 36
    Long Haul 41
    Olde Tyme Quality 27
    Stuttenfurst 27
    Valu-Matte 18

  8. Write a query to display the number of products in each category that have a water base. Sort the results by product category. (Problem 7.52)

    PROD_CATEGORY NUMPRODUCTS
    Cleaner 2
    Filler 2
    Primer 16
    Sealer 1
    Top Coat 81

  9. Write a query to display the number of products within each base and type combination. Sort the results by product base and type. (Problem 7.53)

    PROD_BASE PROD_TYPE NUMPRODUCTS
    Solvent Exterior 67
    Solvent Interior 83
    Water Exterior 39
    Water Interior 63

  10. Write a query to display the total inventory—that is, the sum of all products on hand for each brand ID. Sort the output by brand ID in descending order. (Problem 7.54)

    BRAND_ID TOTALINVENTORY
    35 2431
    33 2158
    31 1117
    30 3012
    29 1735
    28 2200
    27 2596
    25 1829
    23 1293

  11. Write a query to display the employee number, first name, last name, and largest salary amount for each employee in department 200. Sort the output by largest salary in descending order and employee number in ascending order. (Problem 7.57)

    EMP_NUM EMP_FNAME EMP_LNAME LARGESTSALARY
    83509 Franklyn Stover 210000
    83705 Jose Barr 147000
    83537 Cleo English 136000
    83565 Lourdes Abernathy 133000
    83593 Rosannie Nash 129000
    83621 Fonda Gonzalez 126000
    83649 Delma Jacob 123000
    83677 Herb Manning 120000
    83936 Bradford Bray 117000
    83734 Inez Rocha 112000
    84049 Lane Brandon 110000
    83763 Jamie Felton 107000

  12. Write a query to display the customer code, first name, last name, and sum of all invoice totals for customers with cumulative invoice totals greater than $1,500. Sort the output by the sum of invoice totals in descending order. (Problem 7.58)

    CUST_CODE CUST_FNAME CUST_LNAME TOTALINVOICE
    215 Charmaine Bryan 3134.15
    98 Valentin Marino 3052.46
    152 Lisette Whittaker 3042.78
    117 Karon Mta 3009.63
    97 Erwin Anderson 2895.49
    112 Lan Nichols 2867.14
    118 Jessie Hicks 2786.55
    220 Abraham Platt 2187.26
    103 Corrina Gifford 2122.07
    302 Shirlene Fitch 2046.31
    173 Ingrid Hardy 2040.31
    132 Janis Dubois 2014.62

  13. Write a query to display the employee number, last name, first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by first name. (Problem 7.61)

    EMP_NUM EMP_LNAME EMP_FNAME TOTALINVOICES
    83565 Abernathy Lourdes 19158.54
    83792 Andersen Wally 20627.47
    83705 Barr Jose 22098.88
    84049 Brandon Lane 20683.06
    83936 Bray Bradford 21139.94
    84248 Castle Danica 17700.42
    84420 Caudill Doug 11308.21
    83393 Cortes Sang 17436.88
    84021 Dickinson Jarod 20437.35
    84163 Easley Gwen 24813.26
    83537 English Cleo 18883.13
    84078 Erwin Diego 23839.85

  14. Write a query to display the brand ID, brand name, brand type, and average price of products for the brand that has the largest average product price. (Problem 7.63)

    BRAND_ID BRAND_NAME BRAND_TYPE AVGPRICE
    29 Busters VALUE 22.59

  15. Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and brand ID for sales of sealer and top coat products of the same brand on the same invoice with the sealer information appearing before top coat. Sort the results by invoice number, sealer line number, and top coat line number in ascending order. (Problem 8.22)

    INV_NUM LINE_NUM PROD_SKU LINE_NUM PROD_SKU BRAND_ID
    115 2 5140-RTG 1 1203-AIS 35
    118 2 5140-RTG 5 5046-TTC 35
    135 5 3036-PCT 2 1074-VVJ 25
    153 2 3701-YAW 1 3955-NWD 30
    222 1 1336-FVM 3 8199-YRF 33
    234 4 5728-ZPO 3 9272-LTP 27
    234 4 5728-JPO 2 9126-PWF 27
    243 1 4072-SWV 3 5653-RTU 23
    287 1 8894-LUR 5 9838-FUF 27
    333 1 3701-YAW 6 2584-CIJ 30
    333 1 3701-YAW 5 4784-SLU 30
    369 2 1403-TUY 1 8726-ZNM 29

    (Note: This table omits the product descriptions for brevity, but they should be included in the actual query result.)

  16. The Binder Prime Company wants to recognize the employee who sold the most of its products during a specified period. Write a query to display the employee number, employee first name, employee last name, email address, and total units sold for the employee who sold the most Binder Prime products between Novemeber 1, 2015, and December 5, 2015. If there is a a tie for most units sold, sort the output by employee last name. (Problem 8.23)

    EMP_NUM EMP_FNAME EMP_LNAME EMP_EMAIL TOTAL
    84134 Rosalie Garlan g.rosali98@lgcompany.com 23
    83850 Rusty Miles m.rusty95@lgcompany.com 23

  17. Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then first name. (Problem 8.24)

    CUST_CODE CUST_FNAME CUST_LNAME
    684 Wendi Bean
    340 Marcia Burris
    211 Gerald Caudill
    292 Valarie Dillard
    293 Cair Erickson
    416 Tatiana Howe
    996 Ezra Lyon
    98 Valentin Marino
    121 Peter Small
    1157 Lucio Staley
    617 Cesar Talley
    457 Shauna Werner
    131 Sal Whaley

  18. LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest purchases made by customers in that state. Write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer in Alabama. Be certain to include any customers in Alabama who have never made a purchase; their invoice dates should be NULL and the invoice totals should display as NULL (i.e., an empty string in the output). (Problem 8.25)

    CUST_CODE CUST_FNAME CUST_LNAME CUST_STREET CUST_CITY CUST_STATE CUST_ZIP INV_DATE LARGEST INVOICE
    903 Robin Addison 323 Loretta Place Mobile AL 36693 8/26/2015 230.63
    643 Nina Allen 680 Red Talon Drive Robertsdale AL 36574 6/21/2015 11.99
    295 Dorothy Austin 829 Big Bend Loop Diamond Shamrock AL 36614 4/24/2015 589.75
    393 Foster Bernal 1299 East 3rd Avenue Birmingham AL 35280    
    853 Gaylord Bolton 1069 Lugene Lane Montgomery AL 36131 11/25/2015 372.68
    925 Alana Booker 1874 I Street Mccullough AL 36502 12/12/2015 208.85
    1248 Lisa Brady 491 Lowland Avenue Daphne AL 36577 12/5/2015 414.47
    538 Chiquita Caldwell 1501 Briggs Court Normal AL 35762 5/26/2015 143.90
    89 Monica Cantrell 697 Adak Circle Loachapoka AL 36865 3/31/2015 516.58
    1233 Nathalie Church 1802 Snowy Owl Circle Napiar Field AL 36303 11/24/2015 160.96
    304 Gertrude Connors 1042 Pleasant Drive Georgiana AL 36033 12/29/2015 376.32
    1131 Carma Cornett 767 Chisana Way Killen AL 35645 10/25/2015 265.12
    1407 Felicia Cruz 643 Turnagain Parkway Coalburg AL 35068 1/6/2016 387.94

  19. One of the purchasing managers is interested in the impact of product prices on the sale of products of each brand. Write a query to display the brand name, brand type, average price of products of each brand, and total units sold of products of each brand. Even if a product has been sold more than once, its price should only be included once in the calculation of the average price. However, you must be careful because multiple products of the same brand can have the same price, and each of those products must be included in the calculation of the brand’s average price. (Problem 8.26)

    BRAND_NAME BRAND_TYPE AVERAGE PRICE UNITS SOLD
    Binder Prime PREMIUM 16.12 3753
    Busters VALUE 22.59 3727
    Foresters Best VALUE 20.94 2086
    Home Comfort CONTRACTOR 21.80 4842
    Le Mode PREMIUM 19.22 5284
    Long Haul CONTRACTOR 20.12 5728
    Olde Tyme Quality CONTRACTOR 18.33 3614
    Stuttenfurst CONTRACTOR 16.47 3671
    Valu-Matte VALUE 16.84 2485

  20. The purchasing manager is still concerned about the impact of price on sales. Write a query to display the brand name, brand type, product SKU, product description, and price of any products that are not a premium brand, but that cost more than the most expensive premium brand products. (Problem 8.27)

    BRAND_NAME BRAND_TYPE PROD_SKU PROD_DESCRIPT PROD_PRICE
    Long Haul CONTRACTOR 1964-OUT Fire Reistant Top Coat, for Interior Wood 78.49