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?

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

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

Explanation / Answer

DML SQL Queries:

Data Manipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanent to database, it can be rolled back.

INSERT into table-name values(data1,data2,..)

UPDATE table-name set column-name = value where condition;

DELETE from table-name;

Answers:

2.i) QUERY: select Name, ProductNumber, ReorderPoint from TableName where ProductID = '356'

NOTE: here, in the above question am not understand of you giving details. That's why am giving the TableName. Actually here, in that sql query replace the tablename as what you want the data from that table. Then you can getting the data.

2.ii) QUERY: select Name, ProductNumber, ReorderPoint from TableName

to execute this query with replacement of tablename, then we get the data about Name,ProductNumber,ReorderPoint these columns data.

2.iii) QUERY: Select * from TableName

to execute this query with replacement of tablename, then we get the data of total records in that table.

2) Insert Command is used to add a row into the table, here the query as follows:

QUERY: Insert into YourName_STORES(StoreCode ,Name , Address, City, State , Zip ) values ("IT330","Test_YourName","1234 Somewhere Street","Here","MA",00333);

To execute this query you can add the record into the table YourName_STORES

3) Insert Command is used to add a row into the table, here the query as follows:

QUERY: Insert into SALES (OrderNumber ,StoreCode ,OrderDate,Quantity ,Terms ,TitleID ) values("TESTORDER","IT330",01/01/2014,10,"NET 30",1234567);

To execute this query you can add the record into the table SALES

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

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