Answer the three questions in the Multiple Tables Homework Document NOTE: Yellow

Question

Answer the three questions in the Multiple Tables Homework Document

NOTE: Yellow bar indicates how many records should be returned

Create the SQL statements to generate the following report (Partial lists displayed).

A)

Employee

CompanyName

ContactName

Customer Phone

OrderID

Orderdate

Steven Buchanan

Vins et alcools Chevalier

Paul Henriot

26.47.15.10

10248

1996-07-04

Michael Suyama

Toms Spezialitaten

Karin Josephs

0251-031259

10249

1996-07-05

Margaret Peacock

Hanari Carnes

Mario Pontes

(21) 555-0091

10250

1996-07-08

Janet Leverling

Victuailles en stock

Mary Saveley

78.32.54.86

10251

1996-07-08

B)

Which Customers used “United Package” as shippers during the month of March 1998?

CompanyName

OrderID

OrderDate

CompanyName

Hanari Carnes

11022

1998-04-14

United Package

Folk och fa HB

11050

1998-04-27

United Package

HILARIoN-Abastos

11055

1998-04-28

United Package

Eastern Connection

11056

1998-04-28

United Package

Franchi S.p.A.

11060

1998-04-30

United Package

Hungry Owl All-Night Grocers

11063

1998-04-30

United Package

White Clover Markets

11066

1998-05-01

United Package

Drachenblut Delikatessen

11067

1998-05-04

United Package

Tortuga Restaurante

11069

1998-05-04

United Package

C)

Which Orders ID’s contain products where the Category Name is ‘Beverages’?

(Partial list displayed).

OrderID

ProductID

ProductName

10253

39

Chartreuse verte

10254

24

Guarana Fantastica

10255

2

Chang

10257

39

Chartreuse verte

10258

2

Chang

10260

70

Outback Lager

10261

35

Steeleye Stout

10263

24

Guarana Fantastica

Show transcribed image text

Employee

CompanyName

ContactName

Customer Phone

OrderID

Orderdate

Steven Buchanan

Vins et alcools Chevalier

Paul Henriot

26.47.15.10

10248

1996-07-04

Michael Suyama

Toms Spezialitaten

Karin Josephs

0251-031259

10249

1996-07-05

Margaret Peacock

Hanari Carnes

Mario Pontes

(21) 555-0091

10250

1996-07-08

Janet Leverling

Victuailles en stock

Mary Saveley

78.32.54.86

10251

1996-07-08

Show query box Showing rows 0 29 (830 total, Query took 0.0008 sec)

Explanation / Answer

As you haven't provided the structures of the tables, i am assuming below tables(Don't worry, i have searched on internet for the schema.. so i hope it will match yours):

CREATE TABLE `Customers` (
`CustomerID` VARCHAR(5) NOT NULL,
`CompanyName` VARCHAR(40) NOT NULL,
`ContactName` VARCHAR(30),
`ContactTitle` VARCHAR(30),
`Address` VARCHAR(60),
`City` VARCHAR(15),
`Region` VARCHAR(15),
`PostalCode` VARCHAR(10),
`Country` VARCHAR(15),
`Phone` VARCHAR(24),
`Fax` VARCHAR(24),
CONSTRAINT `PK_Customers` PRIMARY KEY (`CustomerID`)
);

CREATE TABLE `Employees` (
`EmployeeID` INTEGER NOT NULL AUTO_INCREMENT,
`LastName` VARCHAR(20) NOT NULL,
`FirstName` VARCHAR(10) NOT NULL,
`Title` VARCHAR(30),
`TitleOfCourtesy` VARCHAR(25),
`BirthDate` DATETIME,
`HireDate` DATETIME,
`Address` VARCHAR(60),
`City` VARCHAR(15),
`Region` VARCHAR(15),
`PostalCode` VARCHAR(10),
`Country` VARCHAR(15),
`HomePhone` VARCHAR(24),
`Extension` VARCHAR(4),
`Photo` LONGBLOB,
`Notes` MEDIUMTEXT NOT NULL,
`ReportsTo` INTEGER,
`PhotoPath` VARCHAR(255),
`Salary` FLOAT,
CONSTRAINT `PK_Employees` PRIMARY KEY (`EmployeeID`)
);

CREATE TABLE `Orders` (
`OrderID` INTEGER NOT NULL AUTO_INCREMENT,
`CustomerID` VARCHAR(5),
`EmployeeID` INTEGER,
`OrderDate` DATETIME,
`RequiredDate` DATETIME,
`ShippedDate` DATETIME,
`ShipVia` INTEGER,
`Freight` DECIMAL(10,4) DEFAULT 0,
`ShipName` VARCHAR(40),
`ShipAddress` VARCHAR(60),
`ShipCity` VARCHAR(15),
`ShipRegion` VARCHAR(15),
`ShipPostalCode` VARCHAR(10),
`ShipCountry` VARCHAR(15),
CONSTRAINT `PK_Orders` PRIMARY KEY (`OrderID`)
);

CREATE TABLE `Shippers` (
`ShipperID` INTEGER NOT NULL AUTO_INCREMENT,
`CompanyName` VARCHAR(40) NOT NULL,
`Phone` VARCHAR(24),
CONSTRAINT `PK_Shippers` PRIMARY KEY (`ShipperID`)
);

CREATE TABLE `Order Details` (
`OrderID` INTEGER NOT NULL,
`ProductID` INTEGER NOT NULL,
`UnitPrice` DECIMAL(10,4) NOT NULL DEFAULT 0,
`Quantity` SMALLINT(2) NOT NULL DEFAULT 1,
`Discount` REAL(8,0) NOT NULL DEFAULT 0,
CONSTRAINT `PK_Order Details` PRIMARY KEY (`OrderID`, `ProductID`)
);

CREATE TABLE `Categories` (
`CategoryID` INTEGER NOT NULL AUTO_INCREMENT,
`CategoryName` VARCHAR(15) NOT NULL,
`Description` MEDIUMTEXT,
`Picture` LONGBLOB,
CONSTRAINT `PK_Categories` PRIMARY KEY (`CategoryID`)
);

CREATE TABLE `Products` (
`ProductID` INTEGER NOT NULL AUTO_INCREMENT,
`ProductName` VARCHAR(40) NOT NULL,
`SupplierID` INTEGER,
`CategoryID` INTEGER,
`QuantityPerUnit` VARCHAR(20),
`UnitPrice` DECIMAL(10,4) DEFAULT 0,
`UnitsInStock` SMALLINT(2) DEFAULT 0,
`UnitsOnOrder` SMALLINT(2) DEFAULT 0,
`ReorderLevel` SMALLINT(2) DEFAULT 0,
`Discontinued` BIT NOT NULL DEFAULT 0,
CONSTRAINT `PK_Products` PRIMARY KEY (`ProductID`)
);

Queries:

1.
select e.Title as Employee, c.CompanyName, c.ContactName, c.Phone as 'Customer Phone', o.OrderID, o.OrderDate
from Orders o, Employees e, Customers c
where o.EmployeeID = e.EmployeeID and o.CustomerID = c.CustomerID

2.
select c.CompanyName, o.OrderID, o.OrderDate, s.CompanyName
from Orders o, Shippers s, Customers c
where o.CustomerID = c.CustomerID and o.ShipVia = s.ShipperID and s.CompanyName='United Package'

3.
select od.orderId, od.ProductID, p.ProductName
from 'Order Details' od, Products p, Categories c
where od.ProductID = p.ProductID
and p.CategoryID = c.CategoryID
and c.CategoryName = 'Beverages'

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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