FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
sql-scripts-remember/scripts/20_Views.sql at main · DishaniD/sql-scripts-remember · GitHub
DishaniD
/
sql-scripts-remember
Public
forked from
DataWithBaraa/sql-ultimate-course
Notifications
You must be signed in to change notification settings
Fork
0
Star
0
Code
Pull requests
0
Actions
Projects
Security and quality
0
Insights
Additional navigation options
Code
Pull requests
Actions
Projects
Security and quality
Insights
Expand file tree
Breadcrumbs
sql-scripts-remember
/
scripts
/
20_Views.sql
Copy path
More file actions
More file actions
Latest commit
History
History
History
111 lines (100 loc) · 3.7 KB
Breadcrumbs
sql-scripts-remember
/
scripts
/
20_Views.sql
Copy path
File metadata and controls
111 lines (100 loc) · 3.7 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
/*
==============================================================================
SQL Views
-------------------------------------------------------------------------------
This script demonstrates various view use cases in SQL Server.
It includes examples for creating, dropping, and modifying views, hiding
query complexity, and implementing data security by controlling data access.
Table of Contents:
1. Create, Drop, Modify View
2. USE CASE - HIDE COMPLEXITY
3. USE CASE - DATA SECURITY
===============================================================================
*/
/*
==============================================================================
CREATE, DROP, MODIFY VIEW
===============================================================================
*/
/*
TASK:
Create a view that summarizes monthly sales by aggregating:
- OrderMonth (truncated to month)
- TotalSales, TotalOrders, and TotalQuantities.
*/
--
Create View
CREATE
VIEW
Sales
.
V_Monthly_Summary
AS
(
SELECT
DATETRUNC(
month
, OrderDate)
AS
OrderMonth,
SUM
(Sales)
AS
TotalSales,
COUNT
(OrderID)
AS
TotalOrders,
SUM
(Quantity)
AS
TotalQuantities
FROM
Sales
.
Orders
GROUP BY
DATETRUNC(
month
, OrderDate)
);
GO
--
Query the View
SELECT
*
FROM
Sales
.
V_Monthly_Summary
;
--
Drop View if it exists
IF
OBJECT_ID
(
'
Sales.V_Monthly_Summary'
,
'
V'
)
IS
NOT
NULL
DROP
VIEW
Sales
.
V_Monthly_Summary
;
GO
--
Re-create the view with modified logic
CREATE
VIEW
Sales
.
V_Monthly_Summary
AS
SELECT
DATETRUNC(
month
, OrderDate)
AS
OrderMonth,
SUM
(Sales)
AS
TotalSales,
COUNT
(OrderID)
AS
TotalOrders
FROM
Sales
.
Orders
GROUP BY
DATETRUNC(
month
, OrderDate);
GO
/*
==============================================================================
VIEW USE CASE | HIDE COMPLEXITY
===============================================================================
*/
/*
TASK:
Create a view that combines details from Orders, Products, Customers, and Employees.
This view abstracts the complexity of multiple table joins.
*/
CREATE
VIEW
Sales
.
V_Order_Details
AS
(
SELECT
o
.
OrderID
,
o
.
OrderDate
,
p
.
Product
,
p
.
Category
,
COALESCE
(
c
.
FirstName
,
'
'
)
+
'
'
+
COALESCE
(
c
.
LastName
,
'
'
)
AS
CustomerName,
c
.
Country
AS
CustomerCountry,
COALESCE
(
e
.
FirstName
,
'
'
)
+
'
'
+
COALESCE
(
e
.
LastName
,
'
'
)
AS
SalesName,
e
.
Department
,
o
.
Sales
,
o
.
Quantity
FROM
Sales
.
Orders
AS
o
LEFT JOIN
Sales
.
Products
AS
p
ON
p
.
ProductID
=
o
.
ProductID
LEFT JOIN
Sales
.
Customers
AS
c
ON
c
.
CustomerID
=
o
.
CustomerID
LEFT JOIN
Sales
.
Employees
AS
e
ON
e
.
EmployeeID
=
o
.
SalesPersonID
);
GO
/*
==============================================================================
VIEW USE CASE | DATA SECURITY
===============================================================================
*/
/*
TASK:
Create a view for the EU Sales Team that combines details from all tables,
but excludes data related to the USA.
*/
CREATE
VIEW
Sales
.
V_Order_Details_EU
AS
(
SELECT
o
.
OrderID
,
o
.
OrderDate
,
p
.
Product
,
p
.
Category
,
COALESCE
(
c
.
FirstName
,
'
'
)
+
'
'
+
COALESCE
(
c
.
LastName
,
'
'
)
AS
CustomerName,
c
.
Country
AS
CustomerCountry,
COALESCE
(
e
.
FirstName
,
'
'
)
+
'
'
+
COALESCE
(
e
.
LastName
,
'
'
)
AS
SalesName,
e
.
Department
,
o
.
Sales
,
o
.
Quantity
FROM
Sales
.
Orders
AS
o
LEFT JOIN
Sales
.
Products
AS
p
ON
p
.
ProductID
=
o
.
ProductID
LEFT JOIN
Sales
.
Customers
AS
c
ON
c
.
CustomerID
=
o
.
CustomerID
LEFT JOIN
Sales
.
Employees
AS
e
ON
e
.
EmployeeID
=
o
.
SalesPersonID
WHERE
c
.
Country
!=
'
USA'
);
GO
Back
|
FazBrowse Home
|
New Git URL