Question

#1) Display Last Name, First Name, DonorId, Fundname, TotalPledged from the donor’s database (you may need to use donor and pledge tables to get the information) for Donor ID 59034. (Your results window should have 3 rows) #2) Display all records from the PurchaseOrderHeader from the AdventureWorks Database that were sold by Employee 261 (Your results window should have 401 rows) #3) Display salesorderid, orderdate, totaldue, and territory name from salesorderheader and salesterritory for all totaldue that are greater than $20,000 and from the Southwest territory. List the total ascending. You will be using AdventureWorks Database. (Your results window should have 351 rows) #4) Display Salesorderid, Order date, Due date, Order Quantity, Line Total, ProductId, Name, and Color from SalesOrderHeader, SalesOrderDetail and Product tables for ProductID 771. (Your results window should have 241 rows) In any of the above queries, if you are returning more rows then specified means that you have not joined the tables correctly. In the Where clause make sure you are specifying the join condition between the tables. e.g. Where SalesOrderDetail.ProductId=Product.ProductId when you are trying to retrieve the data that is common from these two tables. #5) Create a new database called Your initials_ERD (mine would be ED_ERD). The SQL code to create a basic database is “create database ED_ERD”. Using SQL (reference examples in chapters 6 and 7) create tables and relationships that match your ERD submitted in HO #4. Include primary and foreign key constraints plus data types. (15 pts.)

Answer 1

Solution:

The first four subparts have been answered as HOMEWORKLIB guidelines, please repost others.

The schema of the database is not given so I am taking the info which is provided and based on the writing the queries, please adjust them accordingly before executing.

1)

SELECT Last Name, First Name, DonorId, Fundname, TotalPledged FROM donor, pledge WHERE donorId= '59034'

2)

SELECT * FROM PurchaseOrderHeader WHERE EmployeeiD= '261'

3)

SELECT salesorderid, orderdate, totaldue, and territory name from salesorderheader and salesterritory FROM sales WHERE totaldue >20000 AND teritory= 'Southwest'

4)

SELECT  Salesorderid, Order date, Due date, Order Quantity, Line Total, ProductId, Name, and Color from SalesOrderHeader, SalesOrderDetail FROM Product WEHRE ProductID''771'

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)