FXH2zrCX

1# Basic Queries
2
3Brief overview of basic SQL queries.
4
5MSSQL unless noted below.
6
7# Tables
8### Table Create
9```sql
10CREATE TABLE(ColumnName type, ColumnName type, ...);
11
12CREATE TABLE Persons (
13    PersonID int,
14    LastName varchar(255),
15    FirstName varchar(255),
16    Address varchar(255),
17    City varchar(255) 
18);
19```
20### Table Alter
21```sql
22ALTER TABLE ['TABLE_NAME'] ADD ['ColumnName'] type;
23```
24```sql
25ALTER TABLE ['TABLE_NAME'] DROP COLUMN ['ColumnName'];
26```
27### Table Drop
28
29```sql
30DROP TABLE ['TABLE_NAME'];
31```
32### Table Exists
33
34Best practice to check if table exists, then drop or alter as needed - e.g.:
35```sql
36IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ['TABLE SCHEMA'] AND TABLE_NAME = ['TABLE NAME']))
37
38BEGIN
39	//...
40END
41GO
42```
43
44### Primary Key
45Main identifier - must be unique.
46```sql
47CREATE TABLE Persons (
48    ID int NOT NULL,
49    LastName varchar(255) NOT NULL,
50    FirstName varchar(255),
51    Age int,
52    PRIMARY KEY (ID)
53);
54```
55```sql
56ALTER TABLE Persons ADD PRIMARY KEY (ID);
57```
58### FK Constraint
59```sql
60ALTER TABLE [DATABASE].[INFORMATION SCHEMA].[TABLE] AS a 
61ADD CONSTRAINT [FK_NAME]
62ADD FOREIGN KEY a.[COLUMN_A] REFERENCES [TABLE.COLUMN_B];
63```
64```sql
65CREATE TABLE Orders (
66    OrderID int NOT NULL,
67    OrderNumber int NOT NULL,
68    PersonID int,
69    PRIMARY KEY (OrderID),
70    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
71    REFERENCES Persons(PersonID)
72);
73```
74
75### Temporary Tables
76Created a table created at the beginning, used for the duration, and destroyed at the end of a code block/
77```sql
78DECLARE [TEMP_TABLE] TABLE (['ColumnName'] type, ['ColumnName'] type, ...);
79Select * INTO [TEMP_TABLE] FROM [DATABASE].[INFORMATION SCHEMA].[TABLE];
80```
81
82### Table Index
83Create an index on often used or important columns within a table to help speed up searches and queries.
84```sql
85CREATE INDEX [INDEX_NAME] ON [DATABASE].[INFORMATION SCHEMA].[TABLE](COLUMN_A, COLUMN_B, ...);
86```
87
88# Modifying Clauses
89### First Row
90```sql
91SELECT TOP 1 * from [DATABASE].[INFORMATION SCHEMA].[TABLE] WHERE [COLUMN NAME] = ['VALUE'];
92```
93Sorted:
94```sql
95SELECT TOP 1 [COLUMN_B] from [DATABASE].[INFORMATION SCHEMA].[TABLE] WHERE [COLUMN_A] = ['VALUE'] ORDER BY [COLUMN_B] ASC;
96```
97
98### In
99```sql
100SELECT * FROM [DATABASE].[INFORMATION SCHEMA].[TABLE] WHERE [COLUMN] IN (value1, value2, ...);
101SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
102```
103Not the most elegant way to grab what's needed (using a join strategically can elimante the need for two SELECT statements) but a way :
104```sql
105SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
106```
107
108### Like
109Regex pattern matcher.
110Remember there's no asterisks in SQL Regex!  
111'%' is 0 or more chars.
112'_' is 1 char.  
113```sql
114SELECT * from [DATABASE].[INFORMATION SCHEMA].[TABLE] WHERE [COLUMN NAME] = ['VALUE'] LIKE ['REGEX'];
115```
116### Distinct
117Return unique results only.
118```sql
119SELECT DISTINCT [COLUMN] from [DATABASE].[INFORMATION SCHEMA].[TABLE] WHERE [COLUMN NAME] = ['VALUE'];
120```
121
122### Existence Check
123```sql
124//example - checks for a non-null or non-empty result
125EXISTS(SELECT * FROM [DATABASE].[INFORMATION SCHEMA].[TABLE] WHERE [COLUMN NAME] = ['VALUE']);
126//Use freely in WHERE clauses
127```
128
129### Identity Insert
130Override and enable inserting into a table.
131```SQL
132SET IDENTITY_INSERT [DATABASE].[INFORMATION SCHEMA].[TABLE] ON;
133```
134
135### Return Count
136```SQL
137SELECT * FROM table AS t HAVING COUNT(t.ID) > 1 GROUP BY t.ID
138```
139
140# CRUD
141### Create
142```SQL
143INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
144```
145### Read
146```SQL
147SELECT * from [DATABASE].[INFORMATION SCHEMA].[TABLE] WHERE [COLUMN NAME] = ['VALUE'];
148SELECT * from [yef].[dbo].[p_system_cache] WHERE NAME = 'my URL';
149```
150### Update
151```SQL
152UPDATE [DATABASE].[INFORMATION SCHEMA].[TABLE] set [COLUMN NAME] = ['VALUE'] WHERE [COLUMN NAME] = ['VALUE'];
153UPDATE ram] set VALUE = '-DS' WHERE SYSTEM_PARAM_CACHE_ID = '3880';
154```
155### Delete
156```SQL
157DELETE FROM [DATABASE].[INFORMATION SCHEMA].[TABLE]  WHERE [COLUMN NAME] = ['VALUE'];
158```