This is a copy of my assignment it may be hard to answer considering they\'re al

Question

This is a copy of my assignment it may be hard to answer considering they're all connected, but basically looking for any help possible. We use SQL server for the assignments. If i am leaving out any information that is needed to answer this please comment.
In chapter 6 and 7 you learned about writing SQL code. This lesson will have you write SQL code to perform five different actions (questions)

A key component of writing code is understanding the data and the database/tables that you will be using. You need to review the tables and the related data that you will be using in each of the five questions. Just because you have records or results returned by your code doesn’t mean that the code is correct. You must understand what the question is asking you to do and the expected results based on an understanding/review of the data in the tables. An example of this is Question #1. You are asked to return all the pledge records for donor 59034. Check the Pledge table in the Donors database to see how many donations were made by this donor. Your code should return this number along with the other requested information. You may need to include more than one table in your code to obtain the correct results.

To review the data in a table you expand the databases in your SQL Server. Then expand the tables in the database. You can view the data by right clicking on the table name and clicking on “Select Top 1000 Rows”.

You may have questions since the instructions will not tell you the exact code you need to write. Like many tasks you have flexibility in completing (writing the SQL code) it. Please post questions in the related discussion topic for HO #5.

Include the table on the left (or at least one table where multiple are used), the code window, and the results window (don’t need every result just a good sample).

You can display the window to write your code by selecting “New Query” (see arrow on the included figure). Since there are many databases in your SQLServer you should tell SQLServer which database to use so I coded “use Donors”. Once you write the code you can execute it by selecting “Execute”. SQLServer will display the results in the bottom part of the window like my example. If there are errors, they will be displayed in the bottom part of the window where the results display. Test this process with some very basic code to get comfortable using the features. The questions are not related so you can complete them in any order.
This may take some time if you have not written much code so please start ASAP. #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 RVT_ERD). The SQL code to create a basic database is “create database RVT_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.)

I need screenshot of your ERD expanded with tables and relationships. Also a separate screenshot of each query you will write for questions 1 – 4. I need to see the query written, the data displayed in the results window and total number of rows returned at the bottom right corner of the results window. In total 5 or more screenshots for this Hands-On Lesson.

Explanation / Answer

HI, here are my suggestion about how you can go about doing the required steps in SQL Server:

#1. Use SELECT for the required fields on the two tables, Donor and Pledge and use the WHERE clause to match the Donor ID in both tables.

#2. For this, first change / select the database using "USE AdventureWorks". Then, "SELECT * FROM PurchaseOrderHeader WHERE Employee = 261" (change Employee for the actual tuple name)

#3. SELECT salesorderid, orderdate, totaldue, territory_name FROM SalesOrderHeader WHERE totaldue > 20000 AND territory_name = "SOUTHWEST" ORDER BY ASC;

#4. SELECT a.Salesorderid, a.Order date, a.Due date, a.Order Quantity, b.Line Total, b.ProductId, Name, b.Color FROM SalesOrderHeader a, Product c CROSS JOIN SalesOrderDetail b ON a.ProductID = c.ProductID AND WHERE a.ProductID = 771.
{This is not the exact statement that would work. But, from the question, it was very unclear to devise the actual SQL Query. You would need to take this as a suggestion and make according changes based on what you see as the actual database, it's tables and the records in the tables.}

#5. CREATE DATABASE AB_ERD

CREATE TABLE SalesOrderHeader (Salesorderid INT, Order date DATE, Due date DATE, Order Quantity INT, Line Total INT, ProductId VARCHAR(40), Name CHAR(30), Color CHAR(10))

CREATE TABLE SalesOrderDetail (Salesorderid INT, Order date DATE, Due date DATE, Order Quantity INT, Line Total INT, ProductId VARCHAR(40), Name CHAR(30), Color CHAR(10))

CREATE TABLE Product (Salesorderid INT, Order date DATE, Due date DATE, Order Quantity INT, Line Total INT, ProductId VARCHAR(40), Name CHAR(30), Color CHAR(10))

The above are for creating the 3 tables we used in HO #4 with the variety of columns. Since, again, the question was unclear as to what all columns every table would contain, I've given you a generic form. You would need to modify it yourself.

As per the question, after you modify all of the above according to what you see on your end, you would need to take screenshots that show the "View (which is the result after you run a SELECT query - where you get to see the returned tables and column data), the Queries, and the Total Number of Rows returned by your queries.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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