SQL Server Version installation

Running head: MS SQL SERVER
MS SQL Server
Name of the Student:
Name of the University:
Author Note

1
MS SQL SERVER
Week 1 - Getting started
SQL Server dev Version installation
The screenshots for the step by step installation of the SQL Server dev version has been
described below:

2
MS SQL SERVER

3
MS SQL SERVER

4
MS SQL SERVER

5
MS SQL SERVER

6
MS SQL SERVER
Introduction to Server configuration manager
The tools associated with the SQL Severs are being managed with the help of the SQL
Server configurations manager. The network protocols associated with the server and the server
itself is configured with the help of this tool.

7
MS SQL SERVER
All the instances of the servers are available for the users to view and manage with the
help of this tool.
Architecture of SQL Server versus Access
The MS access database utilizes the file servers design where the details of the database
can be adjusted within a single file. On the other hand, the SQL Server provides the option of a
client server architecture, which provide the option of a centralized database.

8
MS SQL SERVER
Kware Script explanation
The Kware Script runs without any errors and the Kware database is created. However, it
is seen that Kware database only has 4 table. The customer, Products, SalesOrder,
SalesOrderProduct tables are the ones which has been created.

9
MS SQL SERVER
Create table statements, Data types
Supplier Table is created
USE [Kware]
GO
/****** Object: Table [dbo].[Supplier] Script Date: 2/15/2020 1:07:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Supplier](
[SID] [int] NOT NULL,
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED
(

10
MS SQL SERVER
[SID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Purchase Order table is created
USE [Kware]
GO
/****** Object: Table [dbo].[PurchaseOrder] Script Date: 2/15/2020 1:08:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PurchaseOrder](
[POID] [int] NOT NULL,

11
MS SQL SERVER
[SID] [int] NOT NULL,
[Numbr] [numeric](18, 0) NOT NULL,
[Date] [datetime] NOT NULL,
[FullPrice] [money] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProductOrder] PRIMARY KEY CLUSTERED
(
[POID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PurchaseOrder] WITH CHECK ADD CONSTRAINT
[FK_ProductOrder_Supplier] FOREIGN KEY([SID])
REFERENCES [dbo].[Supplier] ([SID])
GO

12
MS SQL SERVER
ALTER TABLE [dbo].[PurchaseOrder] CHECK CONSTRAINT [FK_ProductOrder_Supplier]
GO
USE [Kware]
GO
/****** Object: Table [dbo].[PurchaseOrderProduct] Script Date: 2/15/2020 1:09:11 PM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PurchaseOrderProduct](
[POPID] [int] NOT NULL,
[POID] [int] NOT NULL,
[PID] [int] NOT NULL,
[ItemPrice] [money] NOT NULL,
[NbrItemsRequested] [numeric](18, 0) NOT NULL,

13
MS SQL SERVER
[ExtendedPrice] [money] NOT NULL,
[NbrItemsReceived] [numeric](18, 0) NOT NULL,
CONSTRAINT [PK_PurchaseOrderProduct] PRIMARY KEY CLUSTERED
(
[POPID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PurchaseOrderProduct] WITH CHECK ADD CONSTRAINT
[FK_PurchaseOrderProduct_Product] FOREIGN KEY([PID])
REFERENCES [dbo].[Product] ([PID])
GO
ALTER TABLE [dbo].[PurchaseOrderProduct] CHECK CONSTRAINT
[FK_PurchaseOrderProduct_Product]
GO

14
MS SQL SERVER
ALTER TABLE [dbo].[PurchaseOrderProduct] WITH CHECK ADD CONSTRAINT
[FK_PurchaseOrderProduct_PurchaseOrder] FOREIGN KEY([POID])
REFERENCES [dbo].[PurchaseOrder] ([POID])
GO
ALTER TABLE [dbo].[PurchaseOrderProduct] CHECK CONSTRAINT
[FK_PurchaseOrderProduct_PurchaseOrder]
GO
Primary/foreign/check constraints
Supplier

15
MS SQL SERVER
Purchase Order

16
MS SQL SERVER
Purchase Order Product

17
MS SQL SERVER
Homework
Customer
Product

18
MS SQL SERVER
Purchase Order
Purchase Order Product
Sales Order
Sales Order Product

19
MS SQL SERVER
Suppliers
Week 2 - Creating db structure
Introduce alter table statements
ALTER TABLE Customer
ADD DOB datetime;
UPDATE Customer
SET Customer.DOB = 2010-01-01
Where Customer.CID = 2 OR Customer.CID = 4 OR Customer.CID = 6;
UPDATE Customer
SET Customer.DOB = 2009-01-01

20
MS SQL SERVER
Where Customer.CID = 1 OR Customer.CID = 3 OR Customer.CID = 5;
UPDATE Customer
SET Email = '[e-mail]'
;
Homework
Select * from Customers;
The single table SQL has been obtained here. It is not possible to back up a single table
from a database, however, the table can be copied into a new database created.
Week 3 - Introducing TSQL and advanced SQL
Print Statements
The Print statement returns a user-defined message to the client.
SELECT Statements
This command is used for retrieving an number of rows from a database and in addition
to this, provides the options of selecting a number of tables from the other columns.
String Concatenations
The concatenation function is used for adding up of two strings in the single data form.

21
MS SQL SERVER
Date Calculations
The date calculations are helping for fetching data such as the current date and the
difference in between two dates.
Advanced SQL
The advanced sql commands involves the complex groups involving the where command
and additionally including the explicit and implicit joins.
Homework
1
a.
Select Customer.Name, SalesOrder.* From Customer Inner Join SalesOrder On Customer.CID =
SalesOrder.CID;
b.
Select Customer.Name, SalesOrder.* From Customer, SalesOrder
Where Customer.CID = SalesOrder.CID;

22
MS SQL SERVER
c.
CREATE OR ALTER FUNCTION new_func (@name nvarchar(50), @id int)
RETURNS TABLE As
RETURN (Select Customer.Name, SalesOrder.* From Customer Inner Join SalesOrder
On Customer.CID = SalesOrder.CID
Where Customer.Name = @name)
2
Select * From
Product
Where Description LIKE '%saucepan%' AND Price > 70.00 OR Description LIKE '%goblet%'
AND Price > 70.00;
3
a.

23
MS SQL SERVER
Select * From Customer, SalesOrder, Product, SalesOrderProduct
Where Customer.CID = SalesOrder.CID and SalesOrder.SOID = SalesOrderProduct.SOID and
SalesOrderProduct.PID = Product.PID and Product.Description LIKE '%saucepan%';
b.
Select * From Customer
Inner Join SalesOrder On Customer.CID = SalesOrder.CID Inner join SalesOrderProduct On
SalesOrder.SOID = SalesOrderProduct.SOID
Inner Join Product On SalesOrderProduct.PID = Product.PID
Where Product.Description LIKE '%saucepan%';
c.
Select * From Customer
Inner Join SalesOrder On Customer.CID = SalesOrder.CID Inner join SalesOrderProduct On
SalesOrder.SOID = SalesOrderProduct.SOID

24
MS SQL SERVER
Where SalesOrderProduct.PID IN (Select PID From Product Where Description LIKE
'%saucepan%');
d.
Select * From Customer
Inner Join SalesOrder On Customer.CID = SalesOrder.CID Inner join SalesOrderProduct On
SalesOrder.SOID = SalesOrderProduct.SOID
AND EXISTS (Select PID From Product Where Description LIKE '%saucepan%');
4
The preceding question is difficult to answer over time as the results would provide a
huge set of data and hence, it can be recommended that explicit join can be used for obtaining
the result.
5
a.

25
MS SQL SERVER
Select Customer.Name, COUNT(SalesOrder.SOID) From Customer Inner Join SalesOrder On
Customer.CID = SalesOrder.CID
Group By Customer.Name;
b.
CREATE OR ALTER FUNCTION new_count(@names nvarchar(50))
RETURNS TABLE As
RETURN (Select Customer.Name, COUNT(SalesOrder.SOID) As Count From
Customer Inner Join SalesOrder On Customer.CID = SalesOrder.CID
Where Customer.Name = @names
Group By Customer.Name)
6
Select * From Customer
Inner Join SalesOrder On Customer.CID = SalesOrder.CID Inner join SalesOrderProduct On
SalesOrder.SOID = SalesOrderProduct.SOID
Inner Join Product On SalesOrderProduct.PID = Product.PID
Where SalesOrderProduct.NbrItemsRequested > SalesOrderProduct.NbrItemsDispatched AND
Product.NbrItemsInStock > 0;

26
MS SQL SERVER
7 It is difficult to answer this question 100% accurately, because is the calendar is not uniform
throughout the database system. Hence, the exact answer to clients who are over 18 can not be
found.
Week 4 - database mail, cursors, stored procedures
Mail Profiles

27
MS SQL SERVER

28
MS SQL SERVER

29
MS SQL SERVER
db_send_mail statement
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Customer Report</H1>' +
N'<table border="1">' +
N'<tr><th>CID</th><th>Name</th>' +

30
MS SQL SERVER
N'<th>Street</th><th>Suburb</th><th>State</th>' +
N'<th>Postcode</th></tr>' +
CAST ( ( SELECT td = CID, '',
td = Name, '',
td = Street, '',
td = Suburb, '',
td = State, '',
td = Postcode
FROM Customer
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'student',
@recipients='[e-mail]',
@subject = 'Work Order List',
@body = @tableHTML,

31
MS SQL SERVER
@body_format = 'HTML' ;
Cursors
For the execution of the SQL statements the database engines a work area is used and the
cursors allows the users for naming the area of work and accesses the information which are
stored in it. It is used in database for the retrieving the data, which is single row at a single time.
Create procedure statement
The stored procedures are used for creating an automated function which would be
triggered when the conditions arise.
USE [Kware]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE usp_SendEmail

32
MS SQL SERVER
AS
Declare @email nvarchar(128)
Declare @name nvarchar(128)
Declare @Date date
SELECT GETDATE(), Month(GetDate()), Day(GetDate()), Year(GetDate())
DECLARE rcpt_cursor CURSOR FOR
select
Name,
Email
from dbo.Customer a
where Month(DOB) = Month(GetDate())
and Day(DOB) = Day(GetDate())

33
MS SQL SERVER
OPEN rcpt_cursor
FETCH NEXT FROM rcpt_cursor
INTO @name,@email
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @email
Declare @subject nvarchar(255)
Declare @Bodytext nvarchar(512)
Set @BodyText = @Name + '' + ' has a birthday today.'
Set @Subject = 'Happy Birthday'
exec msdb.dbo.sp_send_dbmail

34
MS SQL SERVER
@Profile_Name = 'HappyBirthday',
@Recipients = @email,
@Body = @BodyText,
@Subject = @Subject
FETCH NEXT FROM rcpt_cursor
INTO @name,@email
END
CLOSE rcpt_cursor
DEALLOCATE rcpt_cursor
Go
Exec usp_SendEmail

35
MS SQL SERVER
SQL jobs
In a SQL Server Agent job the integration service packages are schedules and the type of
integrations are required to be stored for the jobs.
Homework
Week 5 – triggers
Triggers
use kware
go
drop table if exists [Audit]
go

36
MS SQL SERVER
create table [Audit]
(
Action varchar(10)
, [User] varchar(50)
, Timestamp datetime
, [Table] varchar(50)
, NameBefore nvarchar(255)
, NameAfter nvarchar(255)
)
-- exec resetsampledata
drop trigger if exists tr_Customer_audit_insert
go
create trigger tr_Customer_audit_insert
on customer
after insert

37
MS SQL SERVER
as
begin
declare @Name as varchar(255)
select @Name = Name from inserted
insert into [Audit] (Action, [user], Timestamp, [Table], NameAfter)
values ( 'Insert', SYSTEM_USER, getdate(), 'Customer', @Name)
end
GO
DROP TRIGGER IF exists tr_Customer_audit_delete
go
CREATE TRIGGER tr_Customer_audit_delete
ON Customer

38
MS SQL SERVER
AFTER DELETE
AS
BEGIN
DECLARE @Name AS varchar(255)
SELECT @Name = Name FROM deleted
INSERT INTO [Audit] (Action, [User], Timestamp, [Table], NameAfter)
VALUES ('Delete', SYSTEM_USER, GETDATE(), 'Customer', @Name)
END

39
MS SQL SERVER
On update of Product, if discount changed, then recalculate FinalPrice
CREATE TRIGGER pro_dis
ON SalesOrder
AFTER INSERT, UPDATE
AS
UPDATE SalesOrder SET FinalPrice = FullPrice - Discount
GO
Homework
CREATE TRIGGER pro_price
ON SalesOrderProduct
AFTER INSERT, UPDATE
AS
UPDATE SalesOrderProduct SET ExtendedPrice = ItemPrice*NbrItemsRequested
GO
Week 6 - design choice
The designing of the database has been done as per the normal forms. Hence, the tables
contain atomic value. Therefor the second address cannot be added. Hence, as a solution an
additional column is required to added into the system.
The updated Supplier table is described below:

40
MS SQL SERVER
The updated product table is providing below:
The following error was received.
Homework
The ER Model has been described below:

41
MS SQL SERVER
Week 7 - Why subtypes are a bad idea
A single table is a better option to have than that subtypes as one-to-one data as there
might be thousand number of rows and columns therefore it is very difficult to reduce the data
which are duplicate.
Week 8 - Transactions and try/catch
Try catch helps in handling the errors in an effective way. Transaction is used to maintain
the data integrity.

42
MS SQL SERVER
Week 9 - Users, logons and security
Logons are used for the purpose of giving better security. this ensures that authentication
is being performed properly at every instance level
Week 10 - Application front end stuff

43
MS SQL SERVER

44
MS SQL SERVER

45
MS SQL SERVER

46
MS SQL SERVER
Week 11 - Backup and recovery SQL-server style
A set of one or more than one backup media is consisting of a single media set. An ordered
collection of backup media is known as a collection of the backup media. A number of such
media is known as the backup media family.