2. Write DML SQL queries and answer the questions: Get the AdventureWorks Data D
ID: 666606 • Letter: 2
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.
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.
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
Answer: are written in bold
2. Use the SELECT and the WHERE clause to find the, Name, ProductNumber, and ReorderPoint, where the ProductID is 356
sql>select Name,ProductNumber,RecordPoint from Product where ProductID=356;
//output of this is based on number of records, possibility it master table of Product, we get only ONE record
3.Create two tables called STORES and SALES. Use the data dictionary below to create these tables.
sql>Create table Stores(StoreCode char(5) primary key, 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);
SQL>create table Sales(Orderno varachar(20) Primary key, StoreCode char(5) Not Null References(Stores),OrderDate date not null, Quantity int, Terms varchar(12) not null, TitleId int);
4. Question:
SQL> insert into Stores values('IT330', 'RAVI','1234 Dwarakanagar', 'Vizag',' AP', '530001')
5.Add a record to the SALES table with the following information:
SQL> insert into Sales values('1001A','IT330',' 01/01/2014',10,'NET30',1234567);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.