SQL Server - Introduction to SQL

 Introduction to SQL 

SQL is a standard language for accessing and manipulating databases. SQL stands for Structured Query Language. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.

It is used for storing and managing data in relational database management system (RDMS). It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables

Using the SQL statements, you can perform most of the actions in a database.

Structured Query Language (SQL) is a standard query language that is used to work with relational databases.

We use SQL to

·         Create databases

·         Create tables

·         Read data

·         Insert data

·         Update data

·         Delete data

·         Delete database tables

·         Delete databases

SQL code is divided into four main Categories

1.   SELECT Statement – SELECT Query used to extract data from multiple table. Clauses such as WHERE, FROM, ORDER BY etc used to extract specific/conditional data from the tables

2.      DML – Data manipulation Language is used to INSET, DELETE, UPDATE data to table.

3.    DDL – Data Definition Language is used for managing tables and index structures. Examples of DDL statements include CREATE, ALTER, TRUNCATE and DROP.

4.    DCL – Data Control Language is used to assign database rights and permissions. Statements used are GRANT and REVOKE.

Types of DBMS

There are two types of databases:

Relational

In RDBMS, data is stored in tabular format.  Examples for RDBMS MySQL, PostgreSQL, MSSQL, Oracle etc

Non – Relational

Data Is Stored in Key-Value Pairs. Examples for Non – RDBMS MongoDB, Amazon DynamoDB, Redis, etc

SQL Server - SQL Data Types

 

SQL Data Types

SQL Stores data into rows and columns. Each column must be assigned with a Name and data Type while creating a Table in database. Data Types such as String, Numeric, Date/Time.

MY SQL String Data Types

CHAR(Size)	It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1.
VARCHAR(Size)	It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters.
BINARY(Size)	It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1.
VARBINARY(Size)	It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes.
TEXT(Size)	It holds a string that can contain a maximum length of 255 characters.
TINYTEXT	It holds a string with a maximum length of 255 characters.
MEDIUMTEXT	It holds a string with a maximum length of 16,777,215.
LONGTEXT	It holds a string with a maximum length of 4,294,967,295 characters.
ENUM(val1, val2, val3,...)	It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted.
SET( val1,val2,val3,....)	It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list.
BLOB(size)	It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes.

 

My SQL Numeric Data Types

BIT(Size)	It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1.
INT(size)	It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255.
INTEGER(size)	It is equal to INT(size).
FLOAT(size, d)	It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter.
FLOAT(p)	It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, d)	It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter.
DECIMAL(size, d)	It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0.
DEC(size, d)	It is equal to DECIMAL(size, d).
BOOL	It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.

 

My SQL DATE/TIME Data Types

DATE	It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-01-01' to '9999-12-31'.
DATETIME()	It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-12-31 23:59:59'.
TIMESTAMP()	It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.
TIME()	It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from '-838:59:59' to '838:59:59'
YEAR	It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000.

 

SQL SERVER String Data Types

char(n)	It is a fixed width character string data type. Its size can be up to 8000 characters.
varchar(n)	It is a variable width character string data type. Its size can be up to 8000 characters.
varchar(max)	It is a variable width character string data types. Its size can be up to 1,073,741,824 characters.
text	It is a variable width character string data type. Its size can be up to 2GB of text data.
nchar	It is a fixed width Unicode string data type. Its size can be up to 4000 characters.
nvarchar	It is a variable width Unicode string data type. Its size can be up to 4000 characters.
ntext	It is a variable width Unicode string data type. Its size can be up to 2GB of text data.
binary(n)	It is a fixed width Binary string data type. Its size can be up to 8000 bytes.
varbinary	It is a variable width Binary string data type. Its size can be up to 8000 bytes.
image	It is also a variable width Binary string data type. Its size can be up to 2GB.

 

SQL SERVER Numeric Data Types

bit	It is an integer that can be 0, 1 or null.
tinyint	It allows whole numbers from 0 to 255.
Smallint	It allows whole numbers between -32,768 and 32,767.
Int	It allows whole numbers between -2,147,483,648 and 2,147,483,647.
bigint	It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
float(n)	It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53.
real	It is a floating precision number data from -3.40E+38 to 3.40E+38.
money	It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.

 

SQL SERVER DATE/TIME Data Types

datetime	It is used to specify date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds.
datetime2	It is used to specify date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds
date	It is used to store date only. It supports range from January 1, 0001 to December 31, 9999
time	It stores time only to an accuracy of 100 nanoseconds
timestamp	It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable.

 

 

SQL Server - SELECT Statement, Working with TABLES

 

SQL SELECT Statement

The SELECT statement is used to fetch/extract data from a database.

Syntax :

SELECT Field1, Field2, ...
FROM table_name;

Example :

SELECT EmpID, Name, ...
FROM Employee;

 

SQL SELECT DISTINCT Statement

SELECT DISTINCT statement is used to fetch/return only different field values.

Inside a table, a column often contains many duplicate values. DISTINCT allows you list the different values records only.

 

Syntax :

SELECT DISTINCT Field1, Field2, ...
FROM table_name;

Example :

SELECT DISTINCT EmpId, Name, ...
FROM Employee;

 

SQL SELECT WHERE Clause

SELECT WHERE clause is used to fetch/return selective or filtered records.

Syntax :

SELECT Field1, Field2, ...
FROM table_name

WHERE Field1 >=Value;

Example :

SELECT EmpId, Name,
FROM Employee

WHERE EmpId >= 100;

WHERE clause can be combined with AND, OR, and NOT operators

SELECT EmpId, Name,
FROM Employee

WHERE EmpId >= 100 and city = ‘Delhi’;

SELECT EmpId, Name,
FROM Employee

WHERE EmpId >= 100 OR city = ‘Delhi’;

ORDER BY keyword

SELECT EmpId, Name,
FROM Employee

WHERE EmpId >= 100 OR city = ‘Delhi’

ORDER BY EmplD ASC, City DESC;

 

DELETE STATEMENT

DELETE statement is used to delete records from table

DELETE FROM Employee where EmpId= 101;

 

SELECT TOP Statement

SELECT TOP clause is used to fetch/return number of records from the top of the table.

Syntax :

SELECT TOP 10 Field1, Field2, ...
FROM table_name

WHERE Field1 >=Value;

Example :

SELECT TOP 10 EmpId, Name,
FROM Employee

WHERE EmpId >= 100;

 

SELECT LIKE Statement

The LIKE operator is used in a WHERE clause to search for a specific records in a column.

There are two wildcards used with the LIKE operator

·         The percent sign (%) represents zero, one, or multiple characters

·         The underscore sign (_) represents one, single character

 

Below statement fetch all records with name starts with character ‘d’

SELECT *
FROM Employee

WHERE Name Like ‘d%’;

Below statement fetch all records with name ends with character ‘d’

SELECT *
FROM Employee

WHERE Name Like ‘%d’;

Below statement fetch all records with name having character ‘d’ in 2^nd position

SELECT *
FROM Employee

WHERE Name Like ‘_d%’;

SQL Server - CREATE, INSERT, ALTER, DROP, UPDATE Tables

 

CREATE TABLE 

The CREATE TABLE statement is used to create a new table in a database.

Syntax :

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
);

Example :

CREATE TABLE Employee (
    EmpId int,
    Name varchar(255),
    Address varchar(255),
    City varchar(255)
); 

 

INSERT TABLE 

The INSERT TABLE statement is used to insert data into table in a database.

Syntax :

INSERT INTO table_name (column1,  column2, column3)

Values (value1, Value2, Value3)

Example :

INSERT INTO Employee (EmpId,  Name, City)

Values (101, ‘Rajendra’, ‘Mumbai’)

ALTER TABLE 

The ALTER TABLE statement is used to alter table structure.

Syntax :

ALTER TABLE table_name

ADD New Field DataType

Example :

ALTER TABLE Employee

ADD DOB DATETIME NULL

DROP TABLE 

The DROP TABLE statement is used DELETE table permanently from database

Syntax :

DROP TABLE table_name

Example :

DROP TABLE Employee

UPDATE TABLE

UPDATE TABLE statement used modify/update existing table data

Syntax ;

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example:

UPDATE Employee
SET Name = ‘Ishanth’, City = ‘Delhi’
WHERE EmpId= 101;