2. Write DML SQL queries and answer the questions: Get the AdventureWorks Data D

Question

2.Write DML SQL queries and answer the questions:

Get the AdventureWorks Data Dictionary from
technet.microsoft.com/en-us/library/ms124438(v=sql.100).aspx

Use the SELECT and the WHERE clause to find the, Name, ProductNumber, and ReorderPoint, where the ProductID is 356

i.Submit the SQL statement used to accomplish this task

ii.Submit the value for the following fields:

Name

ProductNumber

ReorderPoint

iii.How many record(s) were listed?

3.Create two tables called STORES and SALES. Use the data dictionary below to create these tables.

TABLE

attribute

data type

Null?

Key

STORES

StoreCode

char(5)

Not Null

PK

Name

varchar(40)

Not Null

Address

varchar(40)

Not Null

City

varchar(20)

Not Null

State

char(2)

Not Null

Zip

char(5)

Not Null

SALES

OrderNumber

varchar(20)

Not Null

PK

StoreCode

char(5)

Not Null

FK (ref Table: STORES)

OrderDate

date

Not Null

Quantity

int

Not Null

Terms

varchar(12)

Not Null

TitleID

int

Not Null

Submit the SQL statements used to create these tables.

4.Add a record to the STORES table with the following information:

StoreCode = IT330, Name = Test_YourName, Address = 1234 Somewhere Street, City = Here, State = MA, Zip = 00333.

5. Submit the SQL statement used to accomplish this task.

6. Write an SQL statement to validate the record added in the previous step. Submit the SQL statement used to accomplish this task.

7 .Add a record to the SALES table with the following information:

OrderNumber = TESTORDER, StoreCode = IT330, OrderDate = 01/01/2014, Quantity = 10, Terms = NET 30, TitleID = 1234567

8. What is the SQL statement you used to do this task?

9. Write a SQL statement to validate the record added in the previous step. Submit the SQL statement used to accomplish this task.

I actually have question 2 figured out but I am terrible with SQL Queries and cannot figure out the last couple of questions :(

TABLE

attribute

data type

Null?

Key

STORES

StoreCode

char(5)

Not Null

PK

Name

varchar(40)

Not Null

Address

varchar(40)

Not Null

City

varchar(20)

Not Null

State

char(2)

Not Null

Zip

char(5)

Not Null

SALES

OrderNumber

varchar(20)

Not Null

PK

StoreCode

char(5)

Not Null

FK (ref Table: STORES)

OrderDate

date

Not Null

Quantity

int

Not Null

Terms

varchar(12)

Not Null

TitleID

int

Not Null

Explanation / Answer

2.
SELECT Name, ProductNumber, and ReorderPoint
FROM Product
WHERE ProductID = 356;

3.
CREATE TABLE STORES(
StoreCode char(5) NOT NULL,
Name varchar(40) NOT NULL,
Address varchar(40) NOT NULL,
city varchar(20) NOT NULL,
State char(2) NOT NULL,
Zip char(5) NOT NULL,
PRIMARY KEY (StoreCode)
);

CREATE TABLE SALES(
OrderNumber varchar(20) NOT NULL,
StoreCode char(5) NOT NULL,
OrderDate date NOT NULL,
Quantity int NOT NULL,
Terms varchar(12) NOT NULL,
TitleID int NOT NULL,
PRIMARY KEY (OrderNumber),
FOREIGN KEY (StoreCode) REFERENCES STORES(StoreCode)
);

4 & 5.
INSERT INTO STORE
VALUES('IT330', 'Test_YourName', '1234 Somewhere Street', 'Here', 'MA', '00333');

6.
SELECT *
FROM STORE
WHERE StoreCode = 'IT330'

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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