Assignment 2

Assignment 2
Abhijeet Rana – 200419192
Prof. Maziar Shazari

Question 1: Delete, Restore Database, Retrieve data.
Delete Adventureworks2014 and create it again (Restore).
Show the first and last names of the persons in the Person.Person table. We need the one with the first
name staring with ‘Amb’ without aby middle name.
Select FirstName,LastName /* selecting command */

from Person.Person /* from table name*/
where FirstName like 'Amb%' and MiddleName is null/* where condition */
Question 2 : Update data
1- Update the person title of Amy Alberts to ‘Ms.’ and Modify the date (modifieddate) to the
current date.
 There might be more than one Amy Alberts. Only update the ones without any title.
update Person.Person /* updating table name*/

set Title = 'Ms.', ModifiedDate = GETDATE() /*setting title and modified date */
where FirstName = 'Amy' and LastName = 'Alberts' and Title is null /*where condition
*/
Question 3 : Join tables
We need several items from 3 tables.
First, we need First name, Last name and title from Person.person.
Also, we need businessEntityID from Person.BusinessEntity.
And we need the AddressID from person.BusinessEntityAddress.
With a Join query show the needed results.
select PP.FirstName,PP.LastName,PP.Title, PBE.BusinessEntityID, PBEA.AddressID /*

select command*/
from Person.Person as PP /* from table name*/
join Person.BusinessEntity as PBE /* join command*/
on PP.BusinessEntityID = PBE.BusinessEntityID /* on basis of BusinessEntityId*/
JOIN Person.BusinessEntityAddress as PBEA /* join command*/
ON PP.BusinessEntityID = PBEA.BusinessEntityID /* on basis of BusinessEntityId*/
Question 4 : Database Diagram
A) Create a Database diagram for the step 3.
B) Make a database diagram using three tables.
(PurchaseOrderDetail, Purchasing.Vendor
PurchaseOrderHeader)
Answer A-
Answer B-
Question 5 : Create a view with only 5 columns. Three columns are (OrderQty, UnitPrice,
PurchaseOrderID) from PurchaseOrderDetail table and two columns are (OrderDate, VendorID) from
PurchaseOrderHeader table. We need to see only the first 10 Rows.
We need to see what is stored in your virtual table.
create view PurchaseOrder as /* creating view */

select top 10 PPOD.OrderQty, PPOD.UnitPrice, PPOD.PurchaseOrderID, PPOH.OrderDate,
PPOH.VendorID /* selecting command */
FROM Purchasing.PurchaseOrderDetail as PPOD /* from table name*/
JOIN Purchasing.PurchaseOrderHeader as PPOH /* join command*/
ON PPOD.PurchaseOrderID = PPOH.PurchaseOrderID /* on basis of BusinessEntityId*/
Select * from PurchaseOrder /* selecting command for showing view*/

Question 6 : Trigger
A- Create a trigger to fire when a row is going to be updated. The trigger must show a message
to indicate that an update happened with the update’s date.
Answer A - CREATE TRIGGER update_reminder /* creating trigger alert*/

ON Purchasing.PurchaseOrderDetail /* for table name*/
AFTER UPDATE
AS
Print ('Update happened with update date'); /* update message*/
GO
B) Update all the modified date with the current date and show the results.
Answer B- update Purchasing.PurchaseOrderDetail set ModifiedDate = GETDATE() /* update

command*/
Question 7 : Case & Join command

In the sales person’s table, we need to compare the values of last year and recent sale of the sale
persons.
If they have sold more than last year, write ‘Need more bonus’. Otherwise, write ‘No bonus’. Name
the column as ‘Bonus’
We also need the name of the persons.
Order the result by last name.
The result must be similar to the screen shot below.
SELECT PP.FirstName, PP.LastName, SP.SalesYTD, SP.SalesLastYear, (SP.SalesYTD -

SP.SalesLastYear) AS Compare, (CASE /* select command*/
WHEN SalesYTD > SalesLastYear /* when condition*/
THEN 'Need more bonus' /* then command*/
ELSE 'No Bonus' /* else*/
END) AS Bonus /* end of case*/
FROM Sales.SalesPerson AS SP /* from table name*/
JOIN Person.Person AS PP /* join command*/
ON SP.BusinessEntityID = PP.BusinessEntityID /* on the basis of foreign key*/
ORDER BY PP.LastName /* order by last name */

Assignment 2

Uploaded by

Copyright:

Available Formats

Assignment 2

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assignment 2

Uploaded by

Copyright:

Available Formats

Assignment 2

Abhijeet Rana – 200419192

Prof. Maziar Shazari

Delete Adventureworks2014 and create it again (Restore).

name staring with ‘Amb’ without aby middle name.

Select FirstName,LastName /* selecting command */

Question 2 : Update data

update Person.Person /* updating table name*/

We need several items from 3 tables.

Also, we need businessEntityID from Person.BusinessEntity.

And we need the AddressID from person.BusinessEntityAddress.

With a Join query show the needed results.

select PP.FirstName,PP.LastName,PP.Title, PBE.BusinessEntityID, PBEA.AddressID /*

A) Create a Database diagram for the step 3.

B) Make a database diagram using three tables.

PurchaseOrderHeader table. We need to see only the first 10 Rows.

We need to see what is stored in your virtual table.

create view PurchaseOrder as /* creating view */

Select * from PurchaseOrder /* selecting command for showing view*/

Answer A - CREATE TRIGGER update_reminder /* creating trigger alert*/

Answer B- update Purchasing.PurchaseOrderDetail set ModifiedDate = GETDATE() /* update

Question 7 : Case & Join command

the column as ‘Bonus’

We also need the name of the persons.

Order the result by last name.

The result must be similar to the screen shot below.

SELECT PP.FirstName, PP.LastName, SP.SalesYTD, SP.SalesLastYear, (SP.SalesYTD -

WHEN SalesYTD > SalesLastYear /* when condition*/

THEN 'Need more bonus' /* then command*/

ELSE 'No Bonus' /* else*/

END) AS Bonus /* end of case*/

FROM Sales.SalesPerson AS SP /* from table name*/

JOIN Person.Person AS PP /* join command*/

ON SP.BusinessEntityID = PP.BusinessEntityID /* on the basis of foreign key*/

ORDER BY PP.LastName /* order by last name */

You might also like