Employee_t Employee_ID EmployeeFirstName EmployeeLastName EmployeeJobTitle Manag

Question

Employee_t

Employee_ID

EmployeeFirstName

EmployeeLastName

EmployeeJobTitle

ManagerID

SALES_REPRESENTATIVE_t

EmployeeID(Fk)

ComissionRate

COURSE_t

CourseID(PK)

CourseDescription

EMPLOYEE_COURSE_t

EmployeeID(FK)

CourseID(FK)

CompletionDate

PRODUCT_t

ProductID(PK)

ProductDescription

StockQuantity

PRICE_HISTORY_t

ProductID(FK)

StartDate(PK)

DueDate

UnitPrice

FABRITCATED_T

PartID(PK)

ProductID(FK)

PartQuantity

Product_Supplier_t

ProductID(FK)

SupplierID(FK)

VendorPartID

ProductCost

PurchasedQuantity

Supplier_t

SupplierID(PK)

SUpplierName

CUSTOMER_t

CustomerID (PK)

CustomerName

CustomerStreet

CUstomerState

SalesRepID

ORDER_t

OrderID(PK)

CustomerPONumber

OrderDate

DueDate

ShipDate

CustomerID(PK)

ORDER_LINE_t

OrderID(FK)

ProductID(FK)

OrderQuantity

1A)List the ProductIDs, product descriptions, and unit prices that are valid on July 14, 2015 for all

products sorted by ProductID.

1B)List the suppliers that provide hack saws, and sort the list with the lowest prices first. Hint: Your SQL
statement must look for a “Hack Saw”, and not some ProductID or PartID. Include the Supplier IDs,
Supplier Names, Vendor Part #s, and cost.

1C). Provide an inventory report that lists the most costly items first. Hint: The most costly item is the one
in which the product (i.e., multiplication) of cost and quantity yields the largest value, in other words, the
item that we have spent the most money on. Include Product ID, Product Description, Unit Price, Cost,
Quantity Purchased, Amount Spent (the product of Quantity Purchased and Cost), Supplier ID, and
Supplier Name.

Explanation / Answer

1.select ProductID,ProductDescription,StockQuantity from product_t p,PRICE_HISTORY_t ph where p.productID=PH.ProductID AND ph.due_date between('14-july-2015',sysdate)

1b.Ans

select * from Supplier_t s,Product_Supplier_t p where s.supplierID=(+)P.SupplierID order by price desc

+ MEANS outer join operator

1c.select max(productcost) from Product_Supplier_t p , PRODUCT_t where productcost>=(select productcost*quality from Product_Supplier_t p , PRODUCT_t)

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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