Session -01

SQL - Structured Query Language

Data

Collection of meaningful information

Database

It is collection of data in file format.

For ex: excel, word, text, note pad etc.

Computer – Word , note pad , Excel for storing data

Disadvantage:

1. It stores less amount of data.

2. Data accessibility is very slow
3. No relationship between two or more files

RDBMS – Relational Database Management System – (Data Base)

1. It is collection of table related information or structured data information.

2. It stores huge amount of data and to extract data we have simple language called SQL
(Structured query language)
3. There is relation between two or more tables.

Table

It is collection of rows and columns.

Different flavors of RDBMS

1. SQL server Management Studio – SSMS – Microsoft

2. SQL developer – Oracle
3. Toad – Oracle
4. Teradata – Teradata
5. DB2 – IBM
6. Postgrey SQL
7. MySQL etc.

How to install SSMS

URL : https://www.microsoft.com/en-in/download/details.aspx?id=42299

You tube Video link: https://youtu.be/VKWIiJUl70A (Windows 11) /

https://youtu.be/QsXWszvjMBM (Windows 10) / you can search any video in you tube with below
keywords use :

How to download and install Microsoft SQL server 2008/2012/2014/2018/2019 for windows7/
windows10/ windows11

Computer Configuration : RAM - 4GB/ 8GB Har disk :256SSD/ 256GB/ 512GB ,
OS - WINDOWS 10 /11

SSMS Version: SQL Server 2014/2018/2019

OS : Windows 2007/08-

SQL server 2008/2012

For MacBook refer the below link

https://youtu.be/3BFxALltQaM

There are two types of databases (DB)

1. System Defined databases / System Database

2. User defined databases / Database Snapshots

1.System Defined databases

 Master - By default DB of Any RDBMS or SQL server

 Model
 Msdb
 Tempdb

SQL is not case sensitive language.

Ex: Meaning of CDS is as same as cds

Colors

Blue – System defined keywords

For ex: SELECT, CREATE , TABLE etc.

Pink – System defined functions

For ex: max, min, sum avg etc.

Green – User written Comments

2.User Defined database

In this user can create database by using a simple syntax i.e., CREATE DATABASE Name

How to create database?

Syntax: create database User_Defined_Database_Name

Every SQL statement ends with semicolon (;)

How to execute SQL statements?

1. Select SQL statement and click on Execute button on top of SQL server window.
2. Select SQL statement and press F5 key from keyboard

How to navigate user created DB or SQLCLASS?

Syntax: USE User_Defined_Database_Name or SQLCLASS

There are two types of comments in SQL

1. Single line comments (--)

2. Multiple line comments (/* multiple line statement */)
We want to create a simple employee table which hold complete employee details
of an organization.

EMP_ID, EMP_NAME, EMP_CITY, EMP_SAL,EMP_DOJ etc.

SQL DATA TYPES

Type of data / Value of an object can hold is known as data type

Different types of data type supported by SQL

1.Numeric Data type
2.String or character data type
3.Approximate Numeric data type
4.Data and time data type

1.Numeric Data type

This kind of data types supports only numeric values which is in
range from 0-9.

1.TINYINT
It will store value ranging from 0 to 255.

How data will store at the back-end memory

128 64 32 16 8 4 2 1

Suppose we want to store number 129 then it will store in below format
128 64 32 16 8 4 2 1 = 255
1 0 0 0 0 0 0 1

How to Declare Variable called number in SQL?

Syntax:
Declare @Variable_Name <Data Type>

How to assign value to a variable?

syntax:
SET @Variable_Name = Value

How to display variable value?

syntax:
Print @Variable_Name

Example:
1.

2.

Declare @num tinyint

SET @num = 256
print @num
Error: Arithmetic overflow error for data type tinyint, value = 256.

Session -02

2.SMALLINT
It stores value ranging from -32768 to 32767.

1.
Declare @val smallint
SET @val = 256
print @val

O/P - 256
2.

3.

4.

3.INT or INTEGER
It stores an integer value i.e., ranging from -2147483648 to
2147483647.
1.

2.

3.

4.BIGINT
It stores an integer value beyond the range of integer data type,
it stores 8 bytes of data.

1.

2.
3.

5.Decimal
It will display exact fixed-point number
We can use Precision as well as scale along with Decimal

Syntax: Decimal(Precision, Scale)

Precision: total number of digits, both to the the left and right of
the decimal point.
Scale : the number of digits to the right side of the decimal point.
1.

2.

3.
2.String or character data types
If we want to store string or character data in RDBMS then it should
always be enclosed within single quotes ('').

1.char
It will allow to store values like A-Z, a-z, 0-9, special
characters.
Static Memory allocation and its maximum size is 8000 characters.

For example:
if we want to store values like mobile number with country code or
email id then it contains alphanumeric value combination then we can
use string or character data types in SQL.

Functions

len() - This function is used to calculate length of string

datalength() - This function is used to calculate data type size.

1.

2.
3.

4.

5.
6.

2.varchar
It will allow to store values like A-Z, a-z, 0-9, special
characters.
Dynamic Memory allocation and its maximum size is 8000
characters.

1.

2.
3.

4.

5.
Session-03

3.Approximate Numeric Data Type

REAL and FLOAT are both used to store approximate numeric

values(floating-point numbers), but they have some differences in
precision.

1.Real
 Uses 4 bytes of storage.
 Provides less precision than FLOAT.
 Approximate data type, meaning small rounding errors can
occur.

2.float
 Uses 8 bytes (by default) but can be specified with different
precision.
 More precise than REAL.
 Also, an approximate data type.

declare @nd real = 12345.67891234

select @nd as Real;

declare @nd1 float = 12345.67891234

select @nd1 as Float;
Example:
CREATE TABLE FR_TEST (realColumn REAL, floatColumn FLOAT);

INSERT INTO FR_TEST (realColumn, floatColumn ) VALUES (1234.567890123456,

1234.567890123456);

Difference between float and real data type in SQL

Features Real Float

Precision The real data type is a floating-point number The float data type is a floating-point number
with a smaller precision compared to float. with a larger precision compared to the real
data type.
Storage The real data type typically requires 4 bytes of The float data type generally requires 8 bytes of
Size storage. storage.
Range The real data type has a more limited range of The float data type can store a wider range of
values it can represent compared to float. values compared to real.
Accuracy The real data type sacrifices some precision for Due to its larger precision, the float data type
the sake of storage size, making it suitable for provides higher accuracy for decimal
cases where the precision requirement is not as calculations and is often used when a higher
strict. level of precision is required.

4.Date and Time Data Type

If we want to store date, time and datetime then we have data types

To display the present date and time of SQL server database we use
GETDATE() function.
GETDATE() - function display present date and time
we need use GETDATE() Function along with SELECT key word
Types
1. DATE
Stores only the date (year, month, day).
Format: YYYY-MM-DD
Storage: 3 bytes
Range: 0001-01-01 to 9999-12-31

Example:
DECLARE @d DATE = '2025-03-11';
SELECT @d;

2. TIME [(fractional seconds precision)]

Stores only the time (hour, minute, second, and fractional seconds).
Format: hh:mm:ss[.fractional_seconds]
Storage: 3 to 5 bytes (depends on precision)
Range: 00:00:00.0000000 to 23:59:59.9999999
Default precision: 7 (up to 100 nanoseconds)

Example:

DECLARE @t TIME = '14:30:15.123';

SELECT @t;

3. DATETIME
Stores both date and time (without time zone).
Format: YYYY-MM-DD hh:mm:ss[.fff]
Storage: 8 bytes
Range: 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997
Precision up to 3 milliseconds.
Example:
DECLARE @dt DATETIME = '2025-03-11 14:30:15';
SELECT @dt;

4. DATETIME2 [(fractional seconds precision)]

Stores both date and time (more precise than DATETIME).
Format: YYYY-MM-DD hh:mm:ss[.fractional_seconds]
Storage: 6 to 8 bytes (depends on precision)
Range: 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999
Default precision: 7 (100 nanoseconds)

Example:
DECLARE @dt2 DATETIME2 = '2025-03-11 14:30:15.1234567';
SELECT @dt2;

5. SMALLDATETIME
Stores both date and time but with lower precision.
Format: YYYY-MM-DD hh:mm:ss
Storage: 4 bytes
Range: 1900-01-01 00:00:00 to 2079-06-06 23:59:59
Precision up to 1 minute (values are rounded to the nearest minute).

Example:
DECLARE @sdt SMALLDATETIME = '2025-03-11 14:30:45';
SELECT @sdt;

6. DATETIMEOFFSET [(fractional seconds precision)]

Similar to DATETIME2, but includes a time zone offset.
Format: YYYY-MM-DD hh:mm:ss[.fractional_seconds] [+|-]hh:mm
Storage: 8 to 10 bytes
Range: 0001-01-01 00:00:00.0000000 -14:00 to 9999-12-31
23:59:59.9999999 +14:00
Useful for handling time zones.

Example:
DECLARE @dto DATETIMEOFFSET = '2025-03-11 14:30:15.1234567 -05:00';
SELECT @dto;

Which Data Type to Use?

Use DATE if you only need to store the date.
Use TIME if you only need to store the time.
Use DATETIME for general use but prefer DATETIME2 for better precision.
Use DATETIME2 if you need precise date-time values.
Use SMALLDATETIME if you need to save space and do not need seconds
precision.
Use DATETIMEOFFSET if working with multiple time zones.

How to create table in Database?

syntax:
CREATE TABLE Table_Name (
Column_Name1 <Data Type>,
Column_Name2 <Data Type>,
Column_Name3 <Data Type>,
.
.
Column_Namen <Data Type>
)

For Example:
We want to create table name of EMPLOYEE which is having columns
like
EMP_ID, EMP_NAME, EMP_CITY, EMP_SAL, EMP_DOJ

CREATE TABLE EMPLOYEE (

EMP_ID smallint,
EMP_NAME varchar(100),
EMP_CITY varchar(50),
EMP_SAL decimal,
EMP_DOJ datetime
)

How to select table from database?

syntax:
Select * from Table_Name

Select * from EMPLOYEE

Select EMP_ID, EMP_SAL from EMPLOYEE

Session-04