Pay attention to the minimum cardinalities. Ignore the word ‘Indexes’ right belo

Question

Pay attention to the minimum cardinalities. Ignore the word ‘Indexes’ right below each table.

Use the CLASSICMODELS database. Write SQL statements for the following questions:

A. Display PAYMENTS and PRODUCTS tables.

B.Select and display customer names with contact Last Names. Also, display customers’ payments that are in the range from $80,000 to $105,000, payment dates, order date, shipped date, status and comments. Sort the results in descending order.

C. Select and display the order Number, order Date, shipped Date, status, quantity Ordered, product Name, MSRP, text Description, and buy Price for the order number that has MSRP at $136 and was shipped on Nov 26, 2004.

D. Select and display customer names with contact Last Names, phone, city, state and country. Only display those who live in London (UK) or San Francisco (US).

orderdetails productlines. orderNumber INT(uj productcode vARCHAR(15) productLine VARCHAR(50) productName VARCHAR(70) productCode VARCHAR(15) on ARCHAR(4000) OntmlDesaiption MEDIUNITEKT productLine VARCHAR(50) productScale VARCHAR(10) OpriceEach DOUBLE Dimage MEDIUMBLOB -HE productvendor VARCHAR(50) HH OorderuneNumbersMALLINT(6) productDescription TEXT quantity Instodk SMALLINT(6) buy Price DOUBLE employeeNumber INT(11) MSRP DOUBLE lastName VARCHAR(50) l-1 Indexes firstName VARCHAR(50) extension VARCHAR(10) email VARCHAR(100) customers. officeCode VARCHAR(10) customerNumber INT(11) orders. Qoustomer Name VARCHAR(50) reportsTo INT(11) TorderNumber INT(11) jobTitle VARCHAR(50) I contact astName VARCHAR(50) orderDate DATETIME ARCHAR(50) me requiredDate DATETIME I phone VARCHAR(50) LIE shippedDate DATETIME address Linel ARCHAR(50) status VARCHAR(15) address Line2 ARCHAR(50) offices comments TEXT city VARCHAR(50) officeCode vARCHAR(10) oustomerNumber INT(11) state VARCHAR(50) city VARCHAR(50) postalCode VARCHAR(15) ++1 phone VARCHAR(50) country VARCHAR(500 QaddressLinel vARCHAR(50) payments K salesRepEmployeeNumber INT(11) addressLine2 VARCHAR(50) customerNumber INT(11) Oaeditumit DOUBLE state VARCHAR(50) rcheckNumber VARCHAR(50) Qcountry VARCHAR(50) paymentDate DATETIME postalCode VARCHAR(15) amount DOUBLE Qterritory VARCHAR(10)

Explanation / Answer

A. desc PAYMENTS;

desc PRODUCTS;

B. select cust.customerName, cust.contactLastName, pay.amount, pay.paymentDate,
ord.orderDate, ord.shippedDate, ord.status, ord.comments from customers cust
join payments pay on cust.customerNumber = pay.customerNumber
join orders ord on ord.customerNumber = cust.customerNumber
where pay.amount between 80000 and 105000
order desc

C. select ord.orderNumber, ord.orderDate, ord.shippedDate, ord.status,
ordd.quantityOrdered, prod.productName, prod.MSRP, prodl.textDescription,
prod.buyPrice from productlines prodl join products prod on prodl.productLine = prod.productLine
join orderdetails ordl on ordl.productCode = prod.productCode join orders ord on ord.orderNumber=ordl.orderNumber
where prod.MSRP = 136 and ord.shippedDate = (26/11/2004)

D. select cust.customerName, cust.contactLastName, cust.phone, cust.city, cust.state,
cust.country where cust.city in ('LONDON','San Francisco')

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.