SQL NOTES- SESSION 1, 2 & 3
WHAT IS SQL?
➢ SQL stands for Structured Query Language.
➢ It is used for communication with the database.
DATA
Data is a raw-fact which describes the attributes of an Entity.
Examples:
1) Person - entity
Attributes and data(in red) of a person
First name: Rohan
Surname: Singh
Phone number: 9876543210
Dob: 14-MAY-199X
Gender: MALE
2) Laptop - entity
Attributes and data (in red) of a laptop
Brand: Dell
RAM: 8gb
Touch: no
Storage: 1 TB
3) Water Bottle - entity
Attributes and data (in red) of a water bottle
Height: 20 cms
Color: blue
Capacity: 500 ml
DATABASE :
Database is a place or a medium in which we store the data in a systematic and organized manner.
➢ The basic operations that can be performed on a database are
▪ CREATE / INSERT
▪ READ / RETRIEVE
▪ UPDATE / MODIFY
▪ DELETE / DROP
➢ These operations are referred as CRUD Operations.
COMPUTER DATABASE
HARD DISK
You have saved
a PPT in your
computer
PEN DRIVE
DATABASE MANAGEMENT SYSTEM ( DBMS )
It is a software which is used to maintain and manage the database
➢ DBMS provides 2 important features i.e Security and Authorization.
Software
DATA SECURITY & AUTHORISATION
Database QUERY LANGUAGE
DBMS SOFTWARE
➢ We use query language to communicate or interact with DBMS
➢ DBMS stores the data in the form of files.
Example:
FILE A FILE B FILE C
RELATIONAL DATABASE MANAGEMENT SYSTEM( RDBMS )
➢ It is a type of DBMS software in which we store the data in the form of rows and
columns(tables).
➢ It also provides 2 important features i.e security and authorization.
➢ We use SQL to communicate or interact with RDBMS
➢ RDBMS stores the data in the form of Tables.
Software
SECURITY &
AUTHORISATION
Data
STRUCTURED
QUERY Database
LANGUAGE
RDBMS
EXAMPLE:
EMPNO Names Sal
1 A 5000
2 B 6000
3 C 2000
4 D 1000
5 E 2000
RELATIONAL MODEL
➢ Relational Model was designed by E.F CODD.
➢ In Relational Model we can store the data in the form of tables.
➢ Any DBMS which follows Relational Model becomes RDBMS .
➢ Any DBMS which follows rules of EF CODD becomes RDBMS .
Relational RDBMS
DBMS
Model
RULES OF EF CODD
TABLE
➢ It is a logical organization of data which consists of rows and columns.
Columns / Attribute / Fields/properties
Rows/ Cell
Records/
Tuples
➢ The cell is formed by intersection of rows and columns in a table.
➢ The cell is the smallest unit in a table which stores data.
Example: EMP (TABLE)
Employee(entity)
Attributes of
EID ENAME SALARY employee:
RECORDS 1 SMITH 1000 EID, Ename,
OF DIFFERENT Salary
2 ALLEN 1500
EMPLOYEE
3 CLARK 2000
RULES OF E.F CODD :
1.The data entered into a cell must always be a single valued data.
EID ENAME PHONE_NO
1 SMITH 101
2 ALLEN 102, 202
3 CLARK 103
EID ENAME PHONE_NO ALTERNATE NO
1 SMITH 101
2 ALLEN 102 202
3 CLARK 103
2.According to E.F CODD we can store the data in Multiple Tables. If
needed we can establish a connection between the tables with the help of
Key Attribute.
3.In RDBMS we store everything in the form of tables including Metadata.
Metadata: The details about a data is knows as Metadata.
EID ENAME PHOTO
1 SMITH
2 ALLEN Photo
3 CLARK Metadata
Image Name: Mypic
size: 127kb
DATA
resolution: 400 x 600
format: jpeg
META TABLE
Image name size Format Resolution
Mypic 127 jpeg 400 x 600
4.The data entered into the table can be validated in 2 steps.
➢ By assigning Datatypes.
➢ By assigning Constraints.
Datatypes are mandatory, whereas Constraints are Optional.
Datatypes in SQL :
It is used to specify or determine the type of data that will be stored in a particular memory
location.
DATATYPES:
1.CHAR
2.VARCHAR / VARCHAR2
3.DATE
4.NUMBER
5.LARGE OBJECTS
➢ Character Large Object
➢ Binary Large Object
NOTE : SQL is not a Case Sensitive Language.
1. CHAR : In character datatype we can store 'A-Z' , 'a-z' , '0-9'
and Special Characters (!, $, &, @, etc)
➢ Characters must always be enclosed within single quotes ' '.
➢ Whenever we use char datatype, we must mention size
➢ Size: it is used to specify number of characters it can store.
➢ The maximum number of characters it can store is 2000 characters.
➢ Char follows fixed length memory allocation.
Syntax: CHAR ( SIZE )
Example: CHAR (8)
Q S P
Used Memory Unused Memory (memory wastage)
