For each information request below, formulate a single SQL query to produce the

Question

For each information request below, formulate a single SQL query to produce the required information.

4) List the names and list prices of products that sold only in quatity of 2. (in other words, in quantity of 2 but not in any other quantities). Use subqueries in the where clause.

5) List the name and list price of products that were sold on a sale for which the shipping cost was over $60. (use two levels of subqueries in where clauses.)

6) List Employees (first and last names) and their hire dates of employees who were hired before their managers. (whose hire date is earlier than their managers hire date). Use a subquery in the where clause.

7) Count the number of unique values of the set of values of PurchaseDate and EmployeeID from the Purchase table. (Use a subquery in FROM) (name column NumbOfEmpPurch)

*** looking for help on these few problems on SQL. Looking for how to do the query and an explanation of why you did what to help me with the other problems on the homework. ****

Customer customerID First Name LastName Street Address City State PostalCode Count Phone Saleltem Product ID tem Size SaleID Quanti SalePrice Sale SaleID SaleDate Customer ID Ta Shipping Salary Employee EmployeeID Salary Wage Employee EmployeeID age MaxHours Product ProductID ProductName anufacturerID Composition List Price Gender Catego Colo Description Inventoryltem Product ID tem Size Qty OnHand ItemSize tem Size Employee EmployeeID First Name LastName ddress City State ZIP Phone ManagerID SSN Ema ddress HireDate Manufacturer ManufacturerID ManufacturerName Address1 ddress2 City State PostalCode Phone Fax Contact URL Purchaseltem Product ID tem Size PurchaseID Quanti PurchasePrice Purchase PurchaseID PurchaseDate Employee ID ExpectedDeliveryDate ManufacturerID Shipping

Explanation / Answer

For these four questions you will need fourTables:

Product,PurchaseItem,Purchase and Employee...

Use this Sql to create table (I have added columns which were needed for this question youu can alter the table)

CREATE TABLE `product` (
`ProductId` int(11) NOT NULL,
`ProductName` int(11) NOT NULL,
`ListPrice` double NOT NULL
) ;

CREATE TABLE `purchase` (
`PurchaseId` int(11) NOT NULL,
`PurchaseDate` date NOT NULL,
`EmployeeID` int(11) NOT NULL,
`Shipping` int(11) NOT NULL
);

CREATE TABLE `purchaseitem` (
`ProductId` int(11) NOT NULL,
`ItemSize` int(11) NOT NULL,
`PurchaseId` int(11) NOT NULL,
`Quantity` int(11) NOT NULL,
`PurchasePrice` double NOT NULL
) ;

CREATE TABLE `employee` (
`EmployeeId` int(11) NOT NULL,
`FirstName` varchar(20) NOT NULL,
`LastName` varchar(20) NOT NULL,
`ManagerId` int(11) NOT NULL,
`HireDate` int(11) NOT NULL
) ;

Insert the dummy data and then try the queries (i have explained you how it works but see how they works in real)

4) You need to select the Productname and list price given the condition that Quantity which is stored in PurchaseItem Table , for that inner join bth the table on product Id and put the where condition

Query: - " SELECT A.ProductName,A.ListPrice FROM `product` as A Join `purchaseitem` as B ON A.ProductId =B.ProductId where B.Quantity='2'; "

5) In this you will need to join three table like this:

Query: "SELECT A.ProductName, A.ListPrice FROM `product` as A JOIN `purchaseitem` as B ON A.ProductId = B.ProductId JOIN `Purchase` as C on B.PurchaseId = C.PurchaseId WHERE C.Shipping>60;"

6) Concept of self Join:

Here you need to self join the employee table as the magnager would also be an employee, So to get the hire date of manager self join the table when magagerId of first would be employeeId of second:

Query:- SELECT A.FirstName,B.LastName,A.HireDate as Emp_HireDate, B.HireDate as Manager_HireDate FROM `employee` as A JOIN `employee` as B ON A.ManagerID=B.EmployeeId WHERE A.HireDate<B.HireDate

7) Group the rows on Employee ID and purchaseDate in purchase table to get the Purchase count of an employee on a date:

Query :-

SELECT EmployeeID,COUNT(EmployeeID) as NumOfEmpPurch, PurchaseDate FROM purchase GROUP BY EmployeeID,PurchaseDate;

Can also use Count Distinct....

I hopre you will find it useful. )

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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