Login users

Here is the the admin and security code for SQL Server that we did in class

Create Login managerLogin 
with password='password'

use Vincents
Create user ManagerUser 
For login managerLogin

Create role managementRole
Grant Select, insert, update 
on Album to managementRole
Grant Select, insert, update 
on Inventory to managementRole

Alter role managementRole 
add member ManagerUser

Sql and JSON

--JSON -SQL Server 2017

/*{
    _id : "1",
 lastName : "Jones",
 firstName : "Sara",
 email: "sara.jones@metroalt.com"
   }*/

Select top 10 EmployeeFirstName, EmployeeLastName, EmployeeEmail
From Employee
for JSON auto

Select BusBarnKey, BusbarnPhone, BusBarnAddress as 'address.street',
BusbarnCity as 'address.city', BusBarnZipCode as 'address.zipCode'
From BusBarn
for JSON Path

ALTER DATABASE MetroAlt SET COMPATIBILITY_LEVEL = 130

Declare @JSON nvarchar(max)
Set @JSON=N'{"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13}'
Select * From OPENJSON(@JSON)

Declare @JSON2 nvarchar(max)
Set @JSON2 ='[
    {"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13},
    {"_id" : 2, "product": "Chrome Book", "price" : 245.99, "quantityAvailable": 23},
    {"_id" : 3, "product": "Bose Lap Top Speakers", "price" : 89.50, "quantityAvailable" : 10},
    {"_id" : 4, "product": "Blue Tooth Game Controller", "price" : 149.99, "quantityAvailable" : 3},
    {"_id" : 5, "product": "Star Wars Mouse Pad", "price" : 1.50, "quantityAvailable" : 100},
    {"_id" : 6, "product": "Dell XPS Desk Top Computer", "price" : 945.00, "quantityAvailable" : 7},
    {"_id" : 7, "product": "Microsoft Surface Pro", "price" : 1250.75, "quantityAvailable" : 9},
    {"_id" : 8, "product": "Norton Anti Virus", "price" : 75.50, "quantityAvailable": 2},
    {"_id" : 9, "product": "Mechanical Keyboard", "price" : 125.50, "quantityAvailable" : 3},
    {"_id" : 10, "product": "Android Tablet", "price" : 345.23, "quantityAvailable" : 5}
  ]'

  Select * from OpenJSON(@JSON2)
  with(
  id int '$._id',
  product nvarchar(255) '$.product',
  price decimal(8,2) '$.price',
  quantity int '$.quantityAvailable'
  )

XML

Use MetroAlt
Select top 10 EmployeeLastName, EmployeeFirstName, EmployeeEmail
From Employee 
For XML raw

Select top 10 EmployeeLastName, EmployeeFirstName, EmployeeEmail
From Employee 
For XML raw, root('employee'), elements

Select BusbarnAddress, BusBarnCity, Bus.Buskey
From BusBarn
Inner Join Bus
On Bus.BusBarnKey=BusBarn.BusBarnKey
For XML auto, root('Barn'), elements

Create table Maintenance(
   maintenanceKey int identity primary key,
   maintenanceDate Date default getdate(),
   maintenanceNotes Nvarchar(255)
)

Alter table Maintenance
drop column MaintenanceNotes

Create xml schema collection xmlSchema_notes
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.metroalt.com/maintenancenote" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="maintenancenote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="title" type="xs:string" />
        <xs:element name="note">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="followup" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Alter table Maintenance
Add MaintenanceNotes xml (xmlschema_notes)

insert into Maintenance([MaintenanceNotes])
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <title>Wear and Tear on Hydralic units</title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

Select * from Maintenance

Select MaintenanceKey, MaintenanceDate, 
maintenanceNotes.query
('declare namespace mn="http://www.metroalt.com/maintenancenote";//mn:maintenancenote/mn:title') titles
From Maintenance

JSON --JavaScript Object Notation
Movie Movie Reviews
Collections Movies, Genres, Reviews, Reviewers

db.moviescollection.insert(
{
   _id : 1,
   title : "Princess Bride",
   year : "1987",
   actors :["Carry Elweys","Frank Savage","Mandy Patinkin"],
   studio : "Warner Brothers", 
   rating : "PG",
   genre : ["Fantasy", "Romance", "Comedy"]
}
)

db.reviewerscollection.insert(
{
    _id : 1,
    username : "Joe",
   email : "Joe432@gmail.com",
   dateregistered : "3/5/2018",
   reviews : [{
                movie : "Princess Bride",
                title : "The red wedding",
                reviewdate : "3/2/2018"
               },
               {
                 movie: "Shape of Water",
                 title : "fishman wins oscar",
                 reviewdate : "3/5/2018"
        }
               ]
 
}
)

db.reviewscollection.insert(
[{
   movie : "Princess Bride",
   title : "The red wedding",
   username: "Joe",
   email : "Joe432@gmail.com",
   reviewdate : "3/2/2018",
   rating : 3,
   reviewtext : "A classic romantic comedy, but a little aged"

},

{
   movie: "Shape of Water",
   title : "fishman wins oscar",
   username: "Joe",
   email : "Joe432@gmail.com",
   reviewdate : "3/5/2018",
   rating : 4.5
}]
)

JSON for Mongo

//books reviews
{ 
   "_id" : 1,
   "title" : "The Last Apprentice",
   "author" : "Joseph Delany",
   "ISBN" : "431423894328",
   "year" : "2012",
   "genre" : "Fantasy",
   "publisher" : "Penguin"
     
}



{
   "_id" : 2,
   "title" : "The Triple Package",
   "author" : ["Jeb Rubenfeld","Amy Chua"],
   "year" : "2014",
   "genre" : ["Non Fiction", "Sociology"],
   "publisher" : "Norton"
}

//reviews
{
   "_id" : 1,
   "booktitle" : "The Last Apprentice",
   "reviewsource" : "Good Reads",
   "reviewtitle" : "Luke warm and not skywalker",
   "reviewRating" : 3,
   "Reviewer" : {
                   "username" : "starwarsfan98",
                   "email" : "starwarsfan98@gmail.com"
                },
   "reviewdate" : "3/1/2018",
   "review" : "I liked it but I didn't like a lot."
   
}

//reviewers
{
    "_id" : 1,
    "username" : "starwarsfan98",
    "email" : "starwarsfan98@gmail.com",
    "dateregistered" : "1/2/2018",
    "reviews" :[{
                  "title" : "Luke warm and not skywalker",
                  "reviewDate" : "3/1/2018"

                 },
                 {
                    "title" : "The Darth Vader of Fantasies",
                    "reviewDate" : "3/2/2018"

                 }]
  
}

Triggers

Use Community_Assist
go
Create trigger tr_ChangeEmployeePosition on EmployeePosition
after Update
as
Declare @employeeKey int
Declare @EmployeeAnnualSalary money
Select @EmployeeKey = Employeekey from Deleted
Select @EmployeeAnnualSalary=EmployeeAnnualSalary from Employee
where Employeekey = @EmployeeKey
if(@EmployeeAnnualSalary=0)
Begin
Update Employee
Set EmployeeAnnualSalary =1000
where EmployeeKey = @EmployeeKey
end
Else
Begin
Update Employee
Set EmployeeAnnualSalary=EmployeeAnnualSalary*1.05
Where employeeKey = @employeeKey
End

Select * from EmployeePosition

Update EmployeePosition
Set PositionKey = 3
where employeeKey=4

Go
Alter trigger tr_DeletePerson on Person
instead of delete
As
if not exists 
  (Select name from sys.Tables 
   Where name ='Deletions')
Begin
 Create table Deletions
 (
 PersonKey int, 
 PersonLastName nvarchar(255), 
 PersonFirstName nvarchar(255), 
 PersonEmail nvarchar(255),  
 PersonEntryDate datetime
 )
End
Insert into Deletions(PersonKey, PersonLastName, PersonFirstname,
PersonEmail, PersonEntryDate)
Select PersonKey, PersonLastName,PersonFirstName,
PersonEmail, PersonEntryDate from Deleted

Select * from Person
Delete from Person where Personkey=133
Select * from Deletions

Alter table Person
disable trigger tr_DeletePerson

Alter table Person
enable trigger tr_DeletePerson

Select * from BusinessRule

--Create a trigger that gives the date
--by which a grant request should be reviews
go
Create trigger tr_GrantReviewDateDue
on GrantRequest
after Insert
As
if not exists
 (Select name from sys.tables 
   Where name = 'ToReview')
 begin
  Create table ToReview
  (
     GrantRequestKey int,
     GrantRequestDate DateTime,
     GrantTypeKey int,
     ToReveiwByDate Datetime
  )
 end
 Insert into ToReview(GrantRequestKey, GrantRequestDate,
 GrantTypeKey, ToReveiwByDate)
 Select GrantRequestKey, GrantRequestDate, GrantTypeKey, 
 DateAdd(hour,48,GrantRequestDate)
 From inserted

 Insert into GrantRequest( GrantRequestDate, PersonKey, GrantTypeKey, 
 GrantRequestExplanation, GrantRequestAmount)
 Values(GetDate(),121,1,'didn''t eat a good breakfast',200.00)

 Select * from GrantRequest
 Select * from ToReview