Introduction to SQL

Let’s discuss about SQL here, the Introduction & datatypes that we use in databases to store data.

SQL: Structured Query Language

• A database computer language designed for the retrieval and management of data in a relational database
• Used to access and manipulate/manage databases
• SQL is the standard language for many commonly used Relational Database Management Systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server.

Some Important notations/points:

• Semicolon (;) is suggested to use after each query, it generally denotes the end of a statement/query. Used necessarily, if multiple statements/queries are there on worksheet and have to be executed at once.
• SQL keywords are not case-sensitive, it means ‘select’ is as same as ‘SELECT’ or ‘Select’.
• NULL – Denotes a field with no value or unknown data, but not a field having values like zero(0) or space( ).
• CONSTRAINTS – Rules that used to limit the type of data into a column, e.g., unique value, restricting null value, etc.
• INDEX- Basically, a Search keyused to retrieve data from the database more quickly, it speeds up searches/queries.

Data Types-

SQL Data types define the type of input/value that can be stored in a column or field.

Data types can be divided broadly into various categories-

1. Numeric Data types
2. Non-Unicode Character String Data types
3. Unicode Character String Data types
4. Date and Time Data types
5. Binary Data types
6. Miscellaneous Data types

NOTE: As we know, there are many database management systems that use SQL as their standard languages, so Not all data types are supported for all.
e.g., Oracle doesn’t support DATETIME & My SQL doesn’t support CLOB data type.

We will mainly discuss the commonly used datatypes of “SQL server” here…

Numeric Data types-

bit – Integer either 0 or 1, storage space is of 1 bit only.

tinyint – Allows whole numbers from 0 to 255, storage space is of 1 byte(8 bits).

smallint – Allows Integers from -32,768 to 32,767 & storage space is of 2 bytes.

int – Allows Integers from -2,147,483,648 to 2,147,483,647 & storage space is of 4 bytes.

bigint –  Allows Integers from -9,223,372,036, 854,775,808 to 9,223,372,036, 854,775,807 & storage space is of 8 bytes.

decimal(p,s) – Allow numbers from -10³⁸ +1 to 10³⁸ -1 & storage of 5-17 bytes.
Here, p represents ‘precision’ i.e., total length of characters/digits in the number(before & after decimal) & s represents ‘scale’ i.e., length of characters/digits after decimal only.

numeric(p,s) – Allow numbers from -10³⁸ +1 to 10³⁸ -1 & storage of 5-17 bytes.

Float(n) – Floating precision number from -1.79E+308 to 1.79E+308
Here, n specifies its size & precision.
For n=1-24, its precision is 7 digits and size is 4 bytes, & For n=25-53, its precision is 15 digits and size is 8 bytes.

Real – Floating precision number from -3.40E+38 to 3.40E+38 & storage of 4 bytes.

Money – Monetary data from -922,337,203,685,477.5808 to +922,337,203,685,477.5807 with storage space of 8 bytes

smallmoney – Monetary data from -214,748.3648 to 214,748.3647 with storage space of 4 bytes

Non-Unicode Character String Data types-

• Used for local languages, like English.

Char(n) – Fixed width with maximum length of 8,000 characters(can contain letters, numbers, and special characters) with defined storage.

Varchar(n) – Variable width with maximum length of 8,000 characters(can contain letters, numbers, and special characters) having storage of 2 bytes + number of characters.

Varchar(max) – Variable width with maximum length of 1,073,741,824 characters(can contain letters, numbers, and special characters) having storage of 2 bytes + number of characters
Not suppoted in MY SQL.

Text – Variable width with maximum length of 2 GB of text data having storage of 4 bytes + number of characters

Unicode Character String Data types- (Not supported in MYSQL)

• Used for multiple international languages like Hindi, English, etc.

nchar(n) – Fixed width with maximum length of 4,000 characters(can contain letters, numbers, and special characters) in Unicode string

nvarchar(n) – Variable width with maximum length of 4,000 characters(can contain letters, numbers, and special characters) in Unicode string

nvarchar(max) – Variable width with maximum length of 536,870,912 characters(can contain letters, numbers, and special characters) in unicode string

ntext – Variable width with maximum length of 1 GB of text data in Unicode string

Date & Time Data types-

datetime – From 1^st January, 1753 to 31^st December, 9999 having accuracy of 3.33 milliseconds with storage space of 8 bytes

datetime2 – From 1^st January, 0001 to 31^st December, 9999 having accuracy of 100 nanoseconds with storage space of 6-8 bytes

smalldatetime – From 1^st January, 1900 to 6^th June, 2079 having accuracy of 1 minute with storage space of 4 bytes

date – Date only, from 1^st January, 0001 to 31^st December 9999 with storage space of 3 bytes

time – Time only, having accuracy of 100 nanoseconds with storage space of 3-5 bytes

Binary Data types-

• Data which cannot easily be stored using character or numeric data types, such as Graphic images, pdf files.

binary – Fixed length binary data having maximum length of 8,000 bytes.

varbinary – Variable length binary data having maximum length of 8,000 bytes.

Varbinary(max) – Variable length binary data having maximum length of 2 GB. It can store image/word/pdf files.

image – Variable length binary data having maximum length of 2 GB. It stores only image files(BMP,TIFF,GIF,JPEG).

Miscellaneous Data types-

sql_variant – Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp

uniqueidentifier – Stores a globally unique identifier GUID, a 16 byte binary SQL Server data type that is globally unique across tables, databases, and servers

xml – stores XML formatted data with maximum storage space of 2 GB

cursor – Reference to a cursor object used for database operations table – stores a result set for later processing

We are done with the basic introduction of SQL, and datatypes in different database management systems.

If you liked this article, or It helped you to understand it better, Please do like it and share your views in the comment section.

Stay tuned with me for such topics & keep learning by Subscribing to my Website.

Thank You😊