Answered: Write a query to display the first…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

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 2017-7-15, and 2017-7-31. 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.

My code just ends in "query completed" instead of what is correct. This is what I tried.

SELECT DISTINCT CUST_FNAME, CUST_LNAME, CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP

FROM LGCUSTOMER AS C, LGINVOICE AS I, LGLINE AS L, LGPRODUCT AS P, LGBRAND AS B

WHERE C.CUST_CODE = I.CUST_CODE

AND I.INV_NUM = L.INV_NUM

AND L.PROD_SKU = P.PROD_SKU

AND P.BRAND_ID = B.BRAND_ID

AND BRAND_NAME = 'FORESTERS BEST'

AND PROD_CATEGORY = 'Top Coat'

AND INV_DATE BETWEEN '15-JUL-2011' AND '31-JUL-2011'

ORDER BY CUST_STATE, CUST_LNAME, CUST_FNAME;

Transcribed Image Text:FIGURE P7.27 THE CH07_LARGECO ERD LGCUSTOMER PK Cust Code Cust_Fname Cust_Lname Cust_Street Cust_City Cust_State Cust_ZIP Cust_Balance places LGINVOICE PK Inv Num FK1 Cust_Code Inv_Date Inv_Total FK2 Employee_ID completes LGEMPLOYEE PK Emp Num Emp_Fname Emp_Lname Emp_Email Emp_Phone Emp_Hiredate Emp_Title Emp_Comm FK1 Dept_Num LGDEPARTMENT employs manages 1 PK Dept_Num H+ FK1 Emp_Num Dept Name Dept_Mail Box Dept_Phone contains +H has +PK LGLINE PK,FK1 Inv_Num PK Line Num FK2 Prod_SKU Line_Qty Line Price LGSALARY HISTORY PK,FK1 Emp_Num Sal_From Sal End Sal_Amount appears on 41- LGBRAND PK Brand ID PK Brand Name Brand Type includes I LGPRODUCT Prod_SKU Prod_Descript Prod Type Prod Base Prod_Category Prod_Price Prod_QOH Prod_Min FK1 Brand_ID provided by LGSUPPLIES PK,FK1 Vend_ID PK,FK2 Prod_SKU provides LGVENDOR PK Vend ID Vend_Name Vend_Street Vend_City Vend_State Vend_ZIP

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

Select distinct a.CUST_FNAME, a.CUST_LNAME, a.CUST_STREET, a.CUST_CITY, a.CUST_STATE, a.CUST_ZIP from LGCUSTOMER a, LGBRAND b, LGPRODUCT c, LGINVOICE d, LGLINE e

Where a.CUST_CODE = d.CUST_CODE And b.BRAND_ID = c.BRAND_ID And d.INV_NUM = e.INV_NUM And c.PROD_SKU = e.PROD_SKU

And b.BRAND_NAME = 'Foresters Best' And PROD_CATEGORY = 'Top Coat' And d.INV_DATE > '2017/07/15' And d.INV_DATE < '2017/07/31'

Order By a.CUST_STATE, a.CUST_LNAME, a.CUST_FNAME;

However it is not ordered correctly?

Solution

by Bartleby Expert

SEE SOLUTION

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions