PL/SQL is part of Oracle Database (not a separate database), whereas MySQL is a
complete database system on its own. Both use SQL, but PL/SQL is Oracle’s
extension, while MySQL uses standard SQL with some custom features.
SQL:
It stands for structured query language.
It is used to communicate with the database.
SQL is initially known as sequel. It stands for Structured English Query
Language.
It was introduced by Raymon F Boyce and Donald D Chamberlin in the
year 1970. They are IBM Employees.
Later it is taken by ANSI – American National Standard Institute.
ANSI changed the name SEQUEL to SQL in the year 1986.
So, we need to learn SQL in order to communicate with the database (to
modify, manipulate, update, insert the data).
Why we store data in soft form.
We are storing the data in a soft form because it used to analyze for the
further process or in the future.
By storing the data in soft form, we can access it whenever or wherever
we want, so that we can analyze it anytime.
Data: Data is a raw fact which describes the attributes of an entity.
Ex: (A person will have some properties like name, age, gender etc... which are
raw fact.
Raw fact means the unchanged data which describes the properties of
an object/entity where we can also modify the data.
Database: Database is a place or medium used to store the data in a systematic
or organised manner.
Why do database look like cylindrical shape?
The data we store in the database will be stored in a form of disk (like
CD’s and DVD) so that it looks like a container.
Why we store the data in systematic or organised manner?
We store data in systematic and organised manner because it is easy to
access data if we place or store data such an organised manner.
If we store data in unorganised manner, it will be difficult and takes
more time to access the data.
We perform some operations on database in order to fetch the data
(i.e., CRUD operations).
C create/insert
R read/retrieve
U update/modify
D delete/drop
DBMS: It stands for database management system. It is a software used to
maintain and manage the database.
It provides two main features to the database, they are 1) Security 2)
Authorization
In order to communicate with the database through DBMS, we use
query language.
By using DBMS software data is stored in a file format in the database.
Performing CRUD operations are difficult and compared to RDBMS.
RDBMS: It stands for Relational database management system. It is a software
used to maintain and manage the database.
It provides two main features to the database, they are 1) Security 2)
Authorization
In order to communicate with the database through DBMS, we use
query language.
By using DBMS software data is stored in a file format in the database.
Performing CRUD operations are easy.
Relational Model: It is a theory proposed by the data scientist named as E. F.
CODD. Relational Model is nothing but it organizes data using tables (relations)
consisting of rows and columns.
The relational model represents how data is stored and managed in Relational
Databases.
If any DBMS is following the relational model. It is going to become RDBMS.
Table: The logical organisation of rows and columns is known as table.
a) Row: It can determine all the properties of single entity. (or) The
horizontal portion of a table is known as row. (or) Each row of a table
represents an entity or record.
b) Column: It can determine the single property of all the entities. (OR) The
vertical portion of a table is known as column. (or) Each column
represents a particular attribute of that entity.
c) Cell: It can determine single property of single entity. (OR) The space
generated by the intersection of rows and columns is known as cell. (OR)
A smallest unit of a table is known as cell.
Rules of E. F. CODD: We have 0-12 rules but no one is discussing about the
zeroth rule because that zeroth rule is saying to follow the all 12 rules.
But here we study only 4 rules which are more important
Rule 1: Data entered into a cell must be single valued data.
If we enter double values in a table i.e., we are not following E.F. CODD rules.
In order to achieve we can add more columns to enter alternative data or else
we can add can in another row.
It will not affect with redundancy because it is mandatory.
Rule 2: We can add everything in the form of table including meta data.
For example img_name, img_size, date, time etc… are known as meta data,
this data will be stored in meta table and that is auto generated.
Note: The data about data is known as meta data.
Meta data is going to be stored inside the meta table.
Meta data and meta table both are auto generated.
Rule 3: We can add data in multiple tables if required we can establish
connection between those tables by using key attributes.
The child table is taking reference from the parent table and maintain
integrity with the values if we are trying to take other values it will not
accept instead we can write null.
Rule 4: we can validate the data in two steps
1) By assigning Data types
2) By assigning constrains
Datatypes: It is used to specify which type of data is to be stored inside
particular column.
Types of datatypes:
1) Char
2) Varchar/Varchar2
3) INT, BIGINT
4) Date
5) Number
6) Large objects
a) CLOB (Character large objects)
b) BLOB (Binary large objects)
CHAR: It is used to store characters like ‘A-Z’;’a-z’;’0-9’; special characters
and alpha numeric characters.
Whenever we are using char datatype characters must enclosed with
‘single-quotes’.
Whenever we are using char datatype we have to mention size to it.
Syntax: CHAR(size)
Default value: ‘1’
Max limit: 2000 characters
Char follows fixed length memory allocation. i.e., once we give the size it
is fixed for char whatever data we insert in it.
We can use char data type for (pan number, student_id, employee_id
etc…)
Varchar: It is used to store characters like ‘A-Z’;’a-z’;’0-9’; special characters
and alpha numeric characters.
Whenever we are using char datatype characters must enclosed with
‘single-quotes’.
Whenever we are using char datatype we have to mention size to it.
There is no default size for this datatype.
This is used whenever the data length variation is happened like our
name.
The max limit of this datatype is 2000 characters. This is standard max
limit.
It follows variable length memory allocation.
It will occupy only the required memory and remaining will sent back to
the memory unit for further usability.
The execution speed of char is faster than varchar.
Varchar2: It is an updated version of varchar datatype. The max limit of this
datatype is 4000 characters.
Syntax: varchar2(size)
Note: By default, the compiler will convert varchar datatype to varchar2
datatype.
Number: It is used to store the numeric values.
Syntax: NUMBER(PRECISION, [SCALE])
PRECISION: It is used to store integers values. The range of precision is 1 to 38.
SCALE: It is used to store decimal values. The range of scale is -84 to 127.
Precision Number(7): 9999999
Precision > Scale Number(7,2): 99999.99
Precision = Scale Number(7,7): .9999999
Precision < Scale Number(2,7): .0000099
If scale is negative Number(7, -2): 9999999
10000000
Date: It is used to store the dates in a particular format. It stores the date in
the oracle specified format.
Syntax: Date
Format: “dd-mm-yy” and “dd-mon-yyyy”
Large objects: It is used to store huge amount of data.
There are two types of large objects:
1) Character large object (CLOB): It is used to store the characters up to
4gb.
Syntax: CLOB
2) Binary large objects (BLOB): It is used to store the multimedia files like
pdf, audio, mp4, photos in the form of binary digits up to 4gb.
Syntax: BLOB
Constrains: These are the rules used to validate the data.
Types of constraints:
1) UNIQUE
2) NOT NULL
3) CHECK
4) PRIMARY KEY
5) FOREIGN KEY
6) DEFAULT
1) UNIQUE: It is used to avoid the duplicate values inside the column.
Syntax: UNIQUE
Example: phone number
2) NOT NULL: It is used to avoid null value inside the columns.
Syntax: NOT NULL
Example: name
3) CHECK: It is a constraint which are extra validation with specified
condition if the data is satisfied condition, it will accept otherwise it will
reject data.
Syntax: CHECK[CONDITION];
Example: phone number for example check length(mobile=10)
4) Primary key: It is used to identify records uniquely from the table.
Syntax: primary key
Characteristics of primary key:
i) It cannot accept duplicated or repeated values.
ii) It cannot accept the null values.
iii) It is a combination of unique and not null constrain.
iv) We can have only one primary key in a table.
v) Primary key is optional but it is highly recommended.
5) Foreign key: It is a constraint which is used to establish the connection
between two tables.
Syntax: Foreign key
Characteristics of Foreign key:
i) It can accept duplicated or repeated values.
ii) It can accept the null values also.
iii) It is not a combination of unique and not null constraints.
iv) We can have multiple foreign keys in a table.
v) The foreign key belongs to the parent table but it is present in child
table.
vi) In order to become a foreign key attribute, it must be the primary key
in its own table.
vii) Foreign key is also known as referential integrity constraint. That
means child table taking reference from the parent table and
maintaining integrity with the values. If we are trying to take the other
values it’ll not accept instead, we can write null.
Diagram
6) Default: It is used to assign the default value to a column.
Syntax: Default(value)