ℹ

SQL
Status In progress

❔ Interview Questions
Data
Data is a rawfact (real/pure information) which describes the
attribute (properties) of an entity (object).

What is data in SQL?

A data type is an attribute that specifies the type of data that the object
can hold: integer data, character data, monetary data, date and time data,
binary strings, and so on. SQL Server supplies a set of system data types
that define all the types of data that can be used with SQL Server.

Data needs to be pure.

Database
It is a place/container used to store data in a systematic and organized
manner.

A database is a container to save the data.

To store the data it needs to be organized.

Database Operations (CRUD)

SQL 1
 It is an operation used to create, read, update & delete the database.

It is also known as CRUD operation.

We need the CRUD operation to create a database.

Database management system (DBMS)

It is software which is used to maintain and manage the database.

How to communicate with DBMS?

A query language is used to communicate with the database

management system. For example, SQL is used for RDBMS.

It provides security and authentication.

Authentication example - OTP

Security example - LOGIN and Pass

Types of DBMS

SQL 2
 Relational DBMS (RDBMS)
A DBMS which communicates through query language called SQL and
stores the data in table format is known as RDBMS.

It is used in Banks and e-commerce apps since it is more secure than

any other DBMS.

It can handle large data and is secure in most cases since it is just the
data written in table format.

Characteristics of DBMS

If a DBMS follows E. F. CODD (Edger F CODD) rule then it is known

as RDBMS

If a DBMS follows a relational (table format) then it is known as

RDBMS.

To communicate with RDBMS we need a query language called SQL.

SQL

SQL is a query language which is used to communicate with

RDBMS.

Examples-

Oracle - MySQL

Microsoft - MS SQL

Amazon - MongoDB

SQL 3
 What is the difference between excel and SQL?

Relational Model

The relational Model is given by E. F. CODD

In a relational model, data can be stored in the form of a table.

In a relational model, the table is stored in the form of metadata.

Metadata is the data about the data.

E. F. CODD RULE
Rule no. 1: Data entered into the cell should be single-valued or atomic
data.

Rule no. 2: We can store the data in multiple tables & also establish
connections through key attributes.

Rule no. 3: To validate the data entered into the cell use datatype &
constraints (validation for unique value).

Rule no. 4 : Datatype is mandatory and constraints are optional

(requiremental).

Datatypes
Datatype is a type of data or a kind of data used to store in memory.

Different programming languages have different syntax (formula/command)

for datatypes.

Types of datatypes in SQL

SQL 4
 Character datatype

Syntax : Char( );

Not case sensitive

length of the entry is defined between the brackets.

Unutilized/ unused space is wasted

Max size is 2000 bytes.

Every record in the memory has the same length.

Provides faster data processing since it has a fixed length.

It is slower for data transfer since it occupies a large space in

memory.

Use case - Can be used when the length of the entry is defined.

example- DOB, PAN card, Aadhar card, student ID.

Also known as FIXED LENGTH MEMORY ALLOCATION.

Variable datatype

Syntax : Varchar( );

Not case sensitive

length of the entry is defined between the brackets.

unused space is sent back to the memory.

Max size is 2000 bytes.

Every record in the memory has a different length.

SQL 5
 Shows slower data processing due to its dynamic length.

Faster data transfer since it occupies low memory.

Use case - Can be used when the length of the entry is not defined.

EXAMPLE - Name, Address, Location.

Also known as VARIABLE LENGTH MEMORY ALLOCATION.

Object datatype

char and varchar datatypes can be interchanged even after when

they are allocated, but this is not the same case for object datatypes.

Character large object

Syntax: CLOB;

Length is not defined using brackets when it is being written since

it has fixed length in different databases.

example:

ORACLE: 4 GB

MySQL: 12-14 GB

Binary large object

Syntax: BLOB;

max size- 4 GB

It is a datatype which is used to store images, audio, video and

files into the database after converting them into binary values
using some java functions.

Date datatype

Syntax: Date;

It can be written in two formats:

‘DD-MON-YYYY’ (for past and future dates)

‘DD-MON-YY’ (for present dates)

example- date of birth, date of manufacturing, date of expiry, etc.

SQL 6
 Number datatype

Syntax: Number(precision, scale)

precision stands for integer.

range: 1- 38

scale stands for a number of decimals.

range: 0- 38

Number datatypes can store both positive and negative values.

Constraints
Constraints are the set of conditions or the set of rules which
are used to validate the data entered into the cell.

Types of constraints

Unique constraint

It is a constraint used to avoid duplicate data entered into

the cell.

Syntax- Unique( );

SQL 7
 Not Null
Restricts NULL value from being inserted into a column.

Syntax:- NOT NULL ;

Not null is a constraint which represent something in the cell.

Use case - Can be used for mandatory entries such as Name, E-mail,
address and contact which can’t be kept null.

Null is a keyword which represents nothing / empty/ void.

5 + 5 = 10

5+0=5
5 + NULL = NULL

NULL + NULL = NULL

Note:- not null is not needed for optional entries
(non- mandatory). For example, alternate contact
number, alternate email id or roll number.

Check constraint
Verifies that all values in a field satisfy a condition.

It is an extra condition to validate the data entered into the cell.

Syntax- Check (condition);

For example :

Salary condition

50,000 check(salary≥ 50,000 and salary≤7000)

60,000 check(salary≥ 50,000 and salary≤7000)

70,000 check(salary≥ 50,000 and salary≤7000)

Primary Key
Primary key is a key which uniquely identifies row or a column in a
table.

SQL 8
 Characteristics :

1. It should be unique.

2. It should be not null.

3. It should be the combination of unique and not null.

In a table, only one primary key should be there.

Primary key is representing the table.

Primary key is present in parent table.

Foreign Key
Ensures referential integrity for a record in another table.

Also known as referential integrity constraint.

It is a key used to make connection form one table to another table.

characteristics of foreign key-

It can be unique.

It can be not null

It is not a combination of unique and not null.

Primary key of one table should be the foreign key of another table.

Foreign key is present in child table.

SQL 9