SQL Fundamentals

WHERE

SELECT * FROM Table WHERE ColumnName <> 'Some value'.

This Query will not return Null values.

To return Null values we need to check if Column is NULL

SELECT * FROM Table WHERE ColumnName IS NULL

LIKE

If a column has type varchar(50), and in one row its value doesn’t have length 50. Spaces will be added to the end.

So WHERE ColumnName LIKE '%SomeValue' will return nothing. You can write the query like this. WHERE ColumnName LIKE '%SomeValue%'

Functions

• LEFT(): return the left most char from string
• RIGHT(): return the right most char from string
• LTRIM(): remove the spaces on the left of string
• RTRIM(): remove the spaces on the right of string

GROUP BY and HAVING

GROUP BY will group values into different groups. HAVING can filter out some values after GROUP BY.

JOIN

INNER JOIN

The default JOIN, rows will be returned if they appear in both tables

CROSS JOIN

Will return the combination of rows from Table A and Table B. If Table A has 4 rows and Table B has 10 row, it will return 40 rows.

OUTER JOIN

• LEFT OUTER JOIN (LEFT JOIN)

The OUTER keyword is optional. LEFT OUTER JOIN is the same as LEFT JOIN. Values will be returned if it appear in the LEFT table. It doesn’t need to be in the RIGHT table.

• RIGHT OUTER JOIN (RIGHT JOIN)

RIGHT OUTER JOIN is the same as RIGHT JOIN. Values will be returned if it appear in the RIGHT table. It doesn’t need to be in the LEFT table.

• FULL OUTER JOIN (FULL JOIN)

Values will be returned if they appear in either LEFT table or RIGHT table.

UNION

UNION can be placed between SELECT queries.

Rows will be appended for each SELECT.

Each SELECT must have same number of Columns and DataType needs to match.

UNION ALL

If SELECT queries return same rows, they will all be returned.

INSERT

INSERT SELECT

The values returned from SELECT will be inserted immediately. Works will multiple rows.

INSERT INTO TableA(ColumnA, ColumnB)
SELECT ColumnA, ColumnB
FROM TableB

UPDATE and DELETE

If we want to delete some value in a row, we could SET it to NULL

UPDATE Table SET ColumnName = NULL WHERE RowId = 1

CREATE TABLE

CREATE TABLE TableName 
(
ProductId int NOT NULL,
Quantity int NOT NULL DEFAULT 1,
ProductName varchar(10) NULL
);

CREATE VIEW

CREATE VIEW ViewName AS 
SELECT *
FROM Table
WHERE ...

VIEW is a temp Table, it can save complex SELECT queries and can be reused later.

TRANSACTION

If there is error in the middle of a TRANSACTION, it will not COMMIT

BEGIN TRANSACTION
-- Multiple UPDATE/INSERT/DELETE queries
COMMIT TRANSACTION

SAVEPOINT and ROLLBACK

If error happens in the middle of a TRANSACTION and we don’t want to ROLLBACK to the start. We can create SAVEPOINT and let it ROLLBACK to that point.

BEGIN TRANSACTION

INSERT INTO Table(ColumnA, ColumnB) VALUES (1, 2);

SAVE TRANSACTION PointOne;

INSERT INTO Table(ColumnA, ColumnB) VALUE (1, 2);

If @@ERROR <> 0 ROLLBACK TRANSACTION PointOne;

COMMIT TRANSACTION

In the above code, if the second INSERT failed, @@ERROR will return a non-zero value, it will ROLLBACK to SAVEPOINT PointOne.

Constraint

• Primary key

CREATE TABLE Table 
(
    RowId int NOT NULL PRIMARY KEY
);

• Foreign key

CREATE TABLE Table 
(
    RowId int NOT NULL PRIMARY KEY,
    ForeignId int NOT NULL REFERENCES TableB(Id)
);

Foreign Key values must come from Primary key in the other table.

Primary key records cannot be deleted unless all Foreign key records were deleted first.

Some DBMS support CASCADE DELETE, which will delete the Primary key record and related Foreign key record in other tables.

• UNIQUE

One table could have multiple UNIQUE Constraint.

• CHECK

Further restrict values in this Column

CREATE TABLE Table 
(
    quantity int NOT NULL CHECK (quantity > 0),
    gender varchar(1) NOT NULL CHECK (gender LIKE '[MF]')
);

INDEX

If you create an index on a Column, DB will sort this Column and store it. Next time you SELECT by this Column, DB will search faster (binary search) because it is sorted.

But add index to a Column will decrease the efficiency of doing UPDATE/INSERT/DELETE on those Columns. Because DB needs to update INDEX on those Columns.

CREATE INDEX Table_Column_Index
ON Table (Column);

TRIGGER

TRIGGER will be execute when certain changes happen to a table

CREATE TRIGGER Table_trigger
ON Table
FOR INSERT, UPDATE
AS 
UPDATE Table
SET ColumnName = Upper(ColumnName)
WHERE Table.Id = inserted.Id