emresaridogan/sql-cheat-sheet

MSSQL Cheat Sheet

Data Types

Integers

INT -- Between -2 billion / +2 billion. It uses 4 bytes of Storage.

TINYINT -- Used for data from 0 to 255. It uses 1 bytes of Storage

SMALLINT -- Between -32.768 and 32.768. It uses 2 bytes of Storage

BIT -- It can be used in cases of two different situations. E.g. girl/boy

BIGINT -- Between 2⁶³ and 2⁶³. It uses 8 bytes of Storage

Float

FLOAT(size,d) -- The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. E.g. float(5,2) is 190,39.

Decimal

DECIMAL(size,d)  -- The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. E.g. Decimal(4,2) is 78,65 or Decimal(4,1) is 125,8.

String

CHAR(n) -- It is not support unicode. Used in characters of fixed length. For example, it is used in statements such as identification number and phone number. It takes up as much space as the input size, even if fewer characters are entered than specified (n). n parameter can be from 0 to 255.

NCHAR(n) -- Unlike char, it supports unicode.

VARCHAR(n)  -- Unlike char, it takes up as much space as the size of the data. n parameter can be from 0 to 65535.

NVARCHAR(n)  -- Unlike varchar, it supports unicode.

TEXT(n) -- Up to 65.535 bytes (approximately 64 KB) of data can be stored.

Date

DATE -- Format - (YYYY-MM-DD)

Date Time

DATETIME -- Format - (YYYY-MM-DD HH:MM:SS)

Time

TIME -- Format - (HH:MM:SS)

Comments

/* Multi
line
comment */

-- Single Line Comment

Data Definition Language (DDL)

Create Database

create database db;

Use Database

use db;

Create Table

create table tblStudents
(
    student_id tinyint identity (1,1) primary key,
    first_name varchar(50),
    last_name varchar(50),
    gender varchar(6),
    age tinyint,
	club varchar(50),
	city varchar(25)
);

create table tblLessons
(
	lesson_id tinyint identity (1,1) primary key,
	lesson_name varchar(20)
);

create table tblResults
(
    result_id tinyint identity (1,1) primary key,
    student_id tinyint,
    lesson_id tinyint,
    exam1 smallint,
    exam2 smallint,
	average decimal(5,2),
	result bit
);

Drop Column

alter table tblStudents drop column gender;

Add Column

alter table tblStudents add gender bit;

Modify the Datatype of column

alter table tblStudents alter column average tinyint;

Truncate Table

truncate table tblStudents; --  Delete all rows

Drop Table

drop table tblLessons;

Drop Database

drop database db;

Data Manipulation Language (DML)

Insertion (Complete)

insert into tblStudents (first_name, last_name, gender, age, club, city) values('Ali','Yıldırım','Male',21,'Chess','İzmir');
insert into tblStudents values('Baran','Doğu','Male',20,'Football','Malatya');

Insertion (Partial)

insert into tblStudents (first_name, last_name) values('Emre','Sarı');

Updating all rows

update tblResults set average=(exam1+exam2)/2;

Updating a specified row

update tblResults set average = average + 10 where student_id = 1;

Delete a specified row

delete from tblStudents where student_id = 2;

Delete all rows

delete from tblStudents;

Data Query Language (DQL)

Display Table

select * from tblStudents;

Select only specified columns

select student_id, first_name from tblStudents;

Select only few rows

select student_id, first_name from tblStudents where age > 20;

Where Clause

Greater than(>)

select * from tblResults where average > 49;

Greater than equal to(>=)

select * from tblResults where average >= 50;

Less than(<)

select * from tblResults where average < 50;

Less than equal to(<=)

select * from tblResults where average <= 49;

Range

select * from tblResults where average >= 50 and average < 80;

BETWEEN and AND

select * from tblResults where average between 50 and 80;

OR

select * from tblResults where average = 50 or average = 75;

Null

select * from tblStudents where gender is NULL;

Not null

select * from tblStudents where gender is NOT NULL;

ORDER BY Clause

select * from tblStudents ORDER BY age DESC; --large to small sorting
select * from tblStudents ORDER BY age asc; -- small to large sorting

Like Operator

select * from tblStudents where last_name like '%a%'; -- Similar to *a* in regrex

select * from tblStudents where first_name like 'Al_'; -- Similar to Al. in regrex

Distinct

select count(distinct(city)) from tblStudents -- Unique values

Top & Percent

select top 4* from tblStudents
select top 50 percent * from tblStudents

In

select * from tblStudents where club in ('Chess','Library')

Aggregation

Sum function

select sum(exam1) from tblResults;

Average function

select avg(exam1) from tblResults;

Count function

select count(exam1) from tblResults;

Maximum function

select max(exam1) from tblResults;

Minimum function

select min(exam1) from tblResults;

Group By

select gender, count(*) as 'Count Of Students' from tblStudents group by gender

Having

select age, count(*) as 'Count Of Students' from tblStudents group by age having count(*) < 2 -- Adds that the number of students must be less than 2

Where

select result_id, first_name+' '+last_name as 'Name',lesson_name,exam1,exam2,average,result from tblResults, tblLessons, tblStudents where tblResults.lesson_id=tblLessons.lesson_id and tblStudents.student_id=tblResults.student_id

Inner Join

select result_id, first_name+' '+last_name as 'Name',lesson_name,exam1,exam2,average,result from tblResults 
inner join tblLessons on tblResults.lesson_id=tblLessons.lesson_id 
inner join tblStudents on tblStudents.student_id=tblResults.student_id

Left Join

select * from tblResults as r left join tblStudents as s on r.student_id=s.student_id

Right Join

select * from tblResults as r left join tblStudents as s on r.student_id=s.student_id

Full Join

select * from tblResults as r full join tblStudents as s on r.student_id=s.student_id

Procedure

Create Procedure Without Parameter

create procedure sales
as
select sales_id, product_name, first_name+' '+last_name, employee_name,how_many,sale_price,how_many*sale_price,sale_date 
from tblSales s,tblProducts p,tblCustomer c,tblEmployee e 
where s.product_id=p.product_id and s.customer_id=c.customer_id and s.employee_id=e.employee_id

Create Procedure With Parameter

create procedure getProduct
@value varchar(50)=''
as
select product_name,stock,brand from tblProducts where product_name=@value

Alter Procedure

ALTER procedure sales
as
select sales_id, product_name, left(first_name,1)+'.'+last_name, employee_name,how_many,sale_price,how_many*sale_price,sale_date 
from tblSales s,tblProducts p,tblCustomer c,tblEmployee e 
where s.product_id=p.product_id and s.customer_id=c.customer_id and s.employee_id=e.employee_id

Delete Procedure

drop procedure sales

Trigger

create trigger action
on tblSales
after insert --insert,delete,update
as
update tblActions set action+=1