-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLQueryCreatingObjects.sql
146 lines (106 loc) · 4.63 KB
/
SQLQueryCreatingObjects.sql
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
-- use database for a session
use SalesDB;
--create tables
CREATE TABLE Products
(ProductID int PRIMARY KEY NOT NULL,
ProductName varchar(50) NOT NULL,
ProductDescription varchar(max) NOT NULL);
--insert data
INSERT Products (ProductID, ProductName, ProductDescription)
VALUES (1, 'The brown fox and the yellow bear', 'A popular book for children.');
--query data from table directly
SELECT ProductName, ProductDescription
FROM Products;
--create a view
/*
Views are saved queries that you can create in your databases. A single view can reference one or more tables.
And, just like a table, a view consists of rows and columns of data.
You can use views as a source for your queries in much the same way as tables
*/
go
CREATE VIEW sales.CustOrders
AS
SELECT
O.custid,
DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0) AS ordermonth,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY custid, DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0);
--query a view
SELECT custid, ordermonth, qty
FROM Sales.CustOrders;
-- create local and global temp tables
--local
CREATE TABLE #Products (
ProductID INT PRIMARY KEY,
ProductName varchar,
...
);
--global
CREATE TABLE ##Products (
ProductID INT PRIMARY KEY,
ProductName varchar,
...
);
INSERT #Products (ProductID, ProductName, ProductDescription)
VALUES (1, 'The temporary time leap', 'A novel about temporary time leaping.');
SELECT *
FROM #Products
ORDER BY ProductName;
------- CTE: Common Table Expression ----------
--Common Table Expressions (CTEs) provide a mechanism for you to define a subquery that may then be
--used elsewhere in a query. Unlike a derived table, a CTE is defined at the beginning of a query and
--may be referenced multiple times in the outer query.
/*
CTEs are named expressions defined in a query. Like subqueries and derived tables, CTEs provide a means
to break down query problems into smaller, more modular units. CTEs are limited in scope to the execution
of the outer query. When the outer query ends, so does the CTE's lifetime.
*/
WITH CTE_year
AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;
-- ------------ Derived Tables --------------
-- helping you to break down complex queries into more manageable parts.
-- Unlike subqueries, you write derived tables using a named expression that is logically equivalent to a
--table and may be referenced as a table elsewhere in the outer query.
--A derived table is created at the time of execution of the outer query and goes out of scope when the outer query ends.
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS derived_year
GROUP BY orderyear;
--pass args
DECLARE @emp_id INT = 9; --declare and assign the variable
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid=@emp_id --use the variable to pass a value to the derived table query
) AS derived_year
GROUP BY orderyear;
GO
--Exercises:
SELECT ProductID, Name, ListPriceFROM SalesLT.ProductWHERE ProductCategoryID = 6;go
CREATE VIEW SalesLT.vProductsRoadBikes ASSELECT ProductID, Name, ListPriceFROM SalesLT.Product
WHERE ProductCategoryID = 6;GO
SELECT ProductID, Name, ListPriceFROM SalesLT.vProductsRoadBikes
WHERE ListPrice < 1000;goSELECT ProductID, Name, ListPrice,
CASE WHEN ListPrice > 1000 THEN N'High' ELSE N'Normal' END AS PriceTypeFROM SalesLT.Product;go
SELECT DerivedTable.ProductID, DerivedTable.Name, DerivedTable.ListPriceFROM (
SELECT ProductID, Name, ListPrice, CASE WHEN ListPrice > 1000 THEN N'High'
ELSE N'Normal' END AS PriceType FROM SalesLT.Product ) AS DerivedTable
WHERE DerivedTable.PriceType = N'High';go
create view citycustomer as select AddressLine1, city, StateProvince, CountryRegion from SalesLT.Address
where CountryRegion = 'Canada';go select * from citycustomer;GO
select ProductID, Name, Weight, ListPrice,case when Weight > 1000 then 'Heavy' else 'Normal' end as
WeightType from SalesLT.Product; GO
select derived.ProductID, derived.Name, derived.Weight, derived.ListPrice from (
select ProductID, Name, Weight, ListPrice,case when Weight > 1000 then 'Heavy' else 'Normal' end as
WeightType from SalesLT.Product) as derivedwhere derived.WeightType='Heavy'