-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path5. SQL Basics -> SELECT
125 lines (106 loc) · 9.37 KB
/
5. SQL Basics -> SELECT
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
Used to retrieve data
Accessibility-: The ability to execute SELECT statement is based on permissions. Membership in the following roles provide SELECT permission -:
-> If you are a member of db_datareader role OR db_owner role OR sysadmin role
Structure-:
~SELECT-: Specifies the columns to be returned by the query
~FROM-: Specifies the tables,views,derived tables and joined tables to be used in the query
~WHERE-: Specifies the search condition for the requested rows (Operators-: =,<>,>,<,>=,<=,BETWEEN,LIKE)
~GROUP BY-: Groups a selected set of rows into a set of summary rows by the values of one or more coloumns or expressions. One row is returned for each group.
~HAVING-: Specifies a search condtition for a group
~ORDER BY-: Determines the order of the result set.
Note-: WHERE clause should always be included (except in class).
TOP clause-: is used to limit rows to a specified number. A percentage of rows can also be returned using TOP PERCENT. Two or more rows that tie can also be returned using
WITH TIES. Note-: ORDER BY should always be used with the TOP clause.
e.g. SELECT TOP 100 * from person.person;
SELECT TOP 10 PERCENT * from person.person;
ORDER BY clause-: Sorts the data that is being returned by a query.
By default it sorts the data in ascending order.
NULLs are treated as the lowest possible value.
OFFSET and FETCH can be used to control the no. of rows returned.
e.g. select LastName, FirstName from Person.Person order by LastName DESC;
select LastName, FirstName from Person.Person order by LastName, FirstName;
select LastName, FirstName from Person.Person order by LastName ASC, FirstName DESC;
Working with NULLs-: IS NULL
IS NOT NULL
e.g. select Title, FirstName from Person.Person where Title IS NOT NULL order by FirstName;
Concatenation-: Merging two or more columns together in the output.
e.g. select FirstName + LastName from Person.Person ;
select FirstName + ' ' + LastName from Person.Person;
Column aliasing-: Technique of renaming a column just for that one particular query result.
e.g. select firstname as first, lastname as last from person.person;
OR
select firstname first, lastname last from person.person;
DISTINCT keyword-: Prevents the retrieval of duplicate items.
Returns the first instance of a value and ignores other occurances.
Can be used with AVG and SUM functions.
e.g. select distinct countryregioncode from sales.sealesterritory;
select distinct countryregioncode, zipid from sales.salesterritory;
Aggregate functions -: SUM -> select SUM(uniprice) 'SUM TOTAL' from sales.salesorderdetail; select SUM(uniprice) 'SUM TOTAL' from sales.salesorderdetail where specialid=4;
AVG -> select AVG(uniprice) 'AVERAGE' from sales.salesorderdetail;
COUNT -> select COUNT(uniprice) 'Total Entries' from sales.salesorderdetail where specialid=4;
GROUP BY-: Groups a selected set of rows into a set of summary rows.
Note-: Every column that is not part of the GROUP BY clause must be using an aggregate function. (So it returns a single value for the group)
e.g. select CountryRegionCode, sum(salesytd) from sales.SalesTerritory where salesytd>4000000 GROUP BY CountryRegionCode;
HAVING-: The WHERE clause for the GROUP BY clause.
e.g. select CountryRegionCode, SUM(SalesYTD) from Sales.SalesTerritory where SalesYTD>4000000 GROUP BY CountryRegionCode HAVING SUM(SalesYTD)>6000000;
SELECT INTO-: creates a new table in the default filegroup and inserts the resulting rows from the query into it.
Note-: It creates a new table based on the columns included in the SELECT column.
e.g. select Name, [group], region INTO SalesNewTable from sales.salesTerritory;
T-SQLs Wildcard characters-: %-: Any string of zero or more characters.
_-: Any single character
[]-: Any single character within the specified range or set.
[^]-: Any single character not within the specified range or set.
LIKE & IN-: 1. Using LIKE in a WHERE clause, causes the WHERE condition to match a specified string pattern.
2. Using IN in a WHERE clause, determines whether a specified value matches any value in a subquery or list.
e.g. select Firstname, lastname from person.person where lastname LIKE 'W%'; -> Watson,Watters,Watts,Waxman etc
select Firstname, lastname from person.person where lastname LIKE 'W_x%'; -> Waxman
select Firstname, lastname from person.person where lastname LIKE '[adr]%'; -> All starting with a or d or r.
select Firstname, lastname from person.person where lastname LIKE '[a-f]%'; -> All starting with a,b,c,d,e,f.
select Firstname, lastname from person.person where lastname LIKE '[^a-f]%'; -> All starting with characters not within the range a-f.
select Firstname, lastname from person.person where lastname IN ('Russell','Ryan','Diaz'); -> All lastnames same as Russell, Ryan and Diaz.
UNION-: Combines the result of two or more queries into a single result set.
Note-: By default, duplicate rows will be removed. (Use ALL keyword to include them).
The number and order of columns must be the same in all queries.
e.g. select lastname,firstname into test1 from person.person where lastname like 'Del%'
select lastname,firstname into test2 from person.person where lastname like 'Abe%'
select lastname,firstname from test1 UNION ALL select lastname,firstname from test2 order by firstname;
INTERSECT & EXCEPT-: Common to both tables and Not common to both tables, respectively.
Intersect-: Returns distinct values that are returned from both the lest and right side.
Except-: Returns distinct values that are found in the left query that are not found on the right query.
e.g. select entityid from person.person INTERSECT (or EXCEPT) select entityid from humanresources.employee;
Synonyms-: Provide alternate names for database objects.
Note-: Used to provide a layer of abstraction
e.g. select * into test100 from humanresources.employee CREATE SYNONYM mltable for dbo.test100;
Opps!!! The name of the original table was changed to test200
Soln-: DROP SYNONYM MLTable CREATE SYNONYM MLTable For dbo.test200;
OFFSET-FETCH-: OFFSET-: Skip n rows.
FETCH-: FETCH NEXT n rows.
A result set filtering option similar to TOP.
Used after ORDER BY.
Limits the no. of rows returned.
Has the ability to skip rows in contrast to TOP.
Useful for creating results-paging functionality.
e.g. select distinct firstname,lastname from person.person order by lastname asc, firstname asc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
Ranking-: Data Analysis function in T-SQL.
Applied to a set of rows and returns a single value.
Returns a ranking value for each row.
Result can be divided into partitions.
Each partition gets its own ranking value.
Types-: RANK-: Returns the rank of each row within the partition of a result set.
Does not always returns consecutive integers (In case of a Tie).
DENSE-RANK-: Same functionality as RANK, but without any gaps in the integers.
NTILE-: Distributes the returned rows into a specified number of groups.
ROW_NUMBER-: Returns the sequencial no. of a row within a partition of a result set starting at 1 for the first row in each partition.
e.g. select entityid, rate, RANK() OVER(ORDER BY Rate Desc) as RankByRate from humanresources.employeepayhistory order by BusinessEntityID
SELECT TOP(10) BusinessEntityID, Rate, DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary FROM HumanResources.EmployeePayHistory;
SELECT BusinessEntityID, Rate, NTILE(315) OVER (ORDER BY Rate DESC) AS Quartile FROM HumanResources.EmployeePayHistory;
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD" FROM Sales.vSalesPersonWHERE TerritoryName IS NOT
NULL AND SalesYTD <> 0;
!!!ALL TOGETHER!!!
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,
NTILE(4) OVER (ORDER BY SalesYTD DESC) AS Quartile,
RANK() OVER (ORDER BY SalesYTD DESC ) AS 'Rank',
DENSE_RANK() OVER (ORDER BY SalesYTD DESC) AS DenseRank,
FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;