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 YourName_STORES and SALES. Use the data dictionary below to create these tables.

TABLE

attribute

data type

Null?

Key

YourName_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: YourName_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 YourName_STORES table with the following information:

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

Submit the SQL statement used to accomplish this task.

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

5.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

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

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

TABLE

attribute

data type

Null?

Key

YourName_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: YourName_STORES)

OrderDate

date

Not Null

Quantity

int

Not Null

Terms

varchar(12)

Not Null

TitleID

int

Not Null

Explanation / Answer

Hi i am writing all the queries in detail please run them you get answers.

2)your not mentilned the table name for 2nd question.and i did find so many sample tables in given link.its difficult find which table's columns are.i just take table name as product.

Select Name,ProductNumber,Reorder point from table where ProductNumber= 356;

3)creating the first table:

Create table Srinivas_Stores (

StoreCode char (5) NOTNULL ,

Name varchar (40) NOTNULL ,

Address varchar (40) NOTNULL ,

City varchar (20) NOTNULL ,

State char (5) NOTNULL ,

Zil char (5) NOTNULL ,

PRIMARYKEY (StoreCode));

The above syntax is creation of table with primary key StoreCode .

Second table creation syntax:

Create Table SALES (

OrderNumber Varchar (20) NOTNULL ,

StoreCode char(5) NOTNULL ,

OrderDate date NOTNULL ,

Quantity int NOTNULL ,

Terms varchar (12) NOTNULL ,

TitleID int NOTNULL ,

FOREIGNKEY (StoreCode) REFERENCES Srinivas _ STORES (StoreCode));

Here we mentioned storecode is the foreign key.

4)

INSERT INTO Srinivas_Stores values (IT330,TEST_SRINIVAS,1234SOMEWHERE,HERE,MA,00330);

This command executed automatically when press enter.we can see the udated table by using SELECT Statement.

5) Inserting items into another table given below.

INSERT INTO SALES VALUES (TESTORDER,IT330,01/01/2014,10,NET 30,123456);

This is also executes automatically.we can checkout by using SELECE CLAUSE as mentioned below.also we can get a message called "one row inserted".

SELECT * from SALES;

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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