Question

Hi, I'm trying to do my homework for my SQL Server class I keep getting stuck on the questions 1-5  I found out like how the format need to be I'm just stuck on implanting the code in.

Auntie B's Antic ERD CUSTOMER OWNER PEOPLE PeoplelD CustomerSince CHARITY Peopleld FirstName LastName Address City State Zip Phone CellPhone EMail PeopleType CharityID Name Address City State WORKERS PeoplelD HireDate TermDate SSN ip Phone ContactID ORDERS OrderlD OrderDate CustomerlD EmployeelD DONATION DonationID DonationDate CharityID EmployeelD ITEM ItemID ItemTypelD Color Size MaxPrice MinPrice OwnerID ORDER ITEM OrderlD ItemID price ITEM DONATION DonationID ItemID value ITEM TYPE ItemTypelD ITEM PICKUP ItemID Date Name Description 1 Create a query that will show who picked up unsold items during the month ot May 2013. Include in your output the first and last name of the owner, along with the name of the item, max price and pick up date. Order your output by Pick up date. My output looked like the following: Name Price Date Bateman Tara LeMay Michael Roswell Furniture DR Clothes Ladies Toys Child 222.80 2013-05-01 2013-05-04 2013-05-12 12.00 2) Using a SUBQUERY, create a query that will show those orders for toys. In your output include the order id, item id and price. My output looked like the following: orderi it price 1010 1012 1012 1013 1015 1034 1036 1040 1040 1042 1042 1042 1042 1013 1024 1025 1022 1023 1041 1046 1062 1064 1074 1075 1077 1079 10.50 10.50 12.00 15.00 3) Using a SUBQUERY find those orders that sold for greater than the average priced sold item. Include in your query the grderid, orderdate and price. Sort your output by price so that the most expensive item is first. My output looked like the following: orderid orderDate price 2001 2013-03-23 1035 2013-04-21 3022 2013-02-28 2002 2013-04-16 1033 2013-03-21 1032 2013-03-20 1031 2013-05-01 1019 2012-03-11 1038 2013-05-02 1016 2011-12-11 1018 2012-02-01 3021 2013-03-30 1004 2010-11-05 1020 2012-03-25 175.50 145.00 145.00 125.00 100.0 85.00 80.00 57.50 17.95 47.50 32.50 30.00 4) Using a SET OPERATOR find out which charities have not received donations. Include in your output the charity name. My output looked like the following: TA Angels Traders Mother Wattles Purple Heart Ron McDonald House Salvation Army St. Francis IIome st. Raja Home Create a query that will show the first and last name of those employees that have not taken an order. your output the first and last name of the employee along with the order id. Suppress those records of employees that have taken orders. Order your output by last name. Your output should look like the following: 5) Display in orderi Debbie David rom Dukes Rakesh Renl NULL NUL.T. NULL Baratheon Michele Bob Mark NULL NULL NULL.

Answer 1

2) SELECT a.OrderID , b.ItemID , a.price FROM ORDER_ITEM a , ITEM b WHERE a.ItemID = b.ItemID AND b.ItemTypeID = (SELECT ItemTypeID FROM ITEM WHERE ItemTypeID = "Toys" );

3) SELECT a.OrderID , a.OrderDate , b.price FROM ORDERS a , ORDER_ITEM b WHERE a.OrderID = b.OrderID AND b.price > (SELECT avg((MaxPrice+MinPrice)/2) FROM ITEM);

4) SELECT Name FROM CHARITY WHERE (SELECT CharityID FROM CHARITY MINUS SELECT CharityID FROM DONATION);

For question 1 and 5 you need to use multiple inner join but in some table there is no column same so I am use join here.