SQL

SQL stands for Structured Query Language which is a computer

language for storing, manipulating and retrieving data stored in a
relational database. SQL was developed in the 1970s by IBM
Computer Scientists and became a standard of the American
National Standards Institute (ANSI) in 1986, and the International
Organization for Standardization (ISO) in 1987.

SQL is the standard language to communicate with Relational

Database Systems. All the Relational Database Management
Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix,
Postgres and SQL Server use SQL as their Standard Database
Language.
 1. Data Definition Language(DDL):

DDL changes the structure of the table like creating a table, deleting a
table, altering a table, etc.

All the command of DDL are auto-committed that means it permanently

save all the changes in the database.

Following are the five DDL commands in SQL:

1. CREATE Command

2. DROP Command

3. ALTER Command

4. TRUNCATE Command

5. RENAME Command

• CREATE Command: The database or its objects are created with this command
(like table, index, function, views, store procedure, and triggers). There are two
types of CREATE statements in SQL, one is for the creation of a database and
the other for a table.

A database is a systematic collection of data. To store data in a well-structured manner,

the first step with SQL is to establish a database. To build a new database in SQL, use
the CREATE DATABASE statement.

Syntax: CREATE DATABASE db_name;

db_name : name of the database(any name can be given)

Example: CREATE DATABASE Company;

The above example will create a database named Company.
. Let’s look at how to utilize the CREATE TABLE statement to create tables in SQL in
more detail.

Syntax: CREATE TABLE table_name(

column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
column4 data_type(size),
…..
);

Here,
table_name is the name of a table,
column1 is the name of the first column,
data_type refers to the type of data that will be stored in this column. For instance, int is
used to represent integer data.
size refers to the maximum amount of data that can be stored in a certain column.

Example: CREATE TABLE Employee(Emp_Name VARCHAR2(20), DOB DATE, Mobile

INT(10), Email VARCHAR2(20));

The above command will create the table schema that look like:

Emp_Name DOB DATE Mobile Email

• DROP Command: The DROP command can be used to delete a whole

database or simply a table that means entire data will also be deleted. The
DROP statement deletes existing objects such as databases, tables, indexes,
and views.

Syntax: For dropping table: DROP TABLE table_name;

For dropping database: DROP DATABASE db_name;

Example: DROP TABLE Employee;

DROP DATABASE Company;

The first command in the example will drop the Employee table and the second one will
drop the entire database.
 • ALTER Command: In an existing table, this command is used to add,
delete/drop, or edit columns. It can also be used to create and remove
constraints from a table that already exists.

To add a new column:

Syntax: ALTER TABLE table_name ADD column_name COLUMN-definition;
Example: ALTER TABLE Employee ADD Address VARCHAR2(20);
To modify the existing column:

Syntax: ALTER TABLE MODIFY(COLUMN DEFINITION….);

Example: ALTER TABLE Employee MODIFY(Emp_Name VARCHAR2(25));

The above command will modify the ‘Emp_Name’ column to data type VARCHAR2 with
size 25.

To delete column:

ALTER TABLE table_name

DROP COLUMN column_name;

ALTER TABLE Employee DROP COLUMN Address ;

• TRUNCATE Command: used to indicate the table’s extents for deallocation

(empty for reuse). This procedure removes all data from a table quickly, usually
circumventing a number of integrity checking processes. It was included in the
SQL:2008 standard for the first time. It is somewhat equivalent to the delete
command.

Syntax: TRUNCATE TABLE table_name;

Example: TRUNCATE TABLE Employee;

The above command will delete the data from the ‘Employee’ table but not the table.
RENAME Command
RENAME is a DDL command which is used to change the name of the database
table.

Syntax of RENAME command

1. RENAME TABLE Old_Table_Name TO New_Table_Name;

Example

1. RENAME TABLE Student TO Student_Details ;

This query changes the name of the table from Student to Student_Details.

2. Data Manipulation Language:

DML is an abbreviation of Data Manipulation Language.

The DML commands in Structured Query Language change the data present
in the SQL database. We can easily access, store, modify, update and delete
the existing records from the database using DML commands.

o DML commands are used to modify the database. It is responsible for

all form of changes in the database.

o The command of DML is not auto-committed that means it can't

permanently save all the changes in the database. They can be rollback.

Following are the four main DML commands in SQL:

 1. SELECT Command

2. INSERT Command

3. UPDATE Command

4. DELETE Command

Different DML commands are:

• INSERT Command: It is used to insert data into a table’s row.

Syntax: INSERT INTO TABLE_NAME (col1, col2, col3,…. col N)

VALUES (value1, value2, value3, …. valueN);

Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, …. valueN);

Example: INSERT INTO Employee(Emp_Name, DOB, Mobile, Email)

VALUES(‘Joe’, ‘1995-02-16’, 7812865845, ‘joe@[Link]’);

The above command will insert the mentioned values in the ‘Employee’ table.

• UPDATE Command: In SQL, the UPDATE statement is used to update

data in an existing database table. We can use the Alter statement to
update single or several columns depending on our needs.

Syntax: UPDATE table_name SET column1 = value1, column2 = value2,…

WHERE condition;

Example: UPDATE Employee SET Mobile=9935467903 WHERE

Emp_Name=’Joe’;
Omitting WHERE Clause in UPDATE Statement
If we omit the WHERE clause from the update query then all of the rows will get
updated.
Query:

UPDATE Customer SET CustomerName = 'Shubham';

Output:

The table Customer will now look like this,

Important Points About SQL

• DELETE Command: In SQL, the DELETE statement is used to delete

records from a table. Depending on the condition we set in the WHERE
clause, we can delete a single record or numerous records.

Syntax: DELETE FROM table_name [WHERE condition];

Example: DELETE FROM Employee WHERE Emp_Name=’Joe’;

The above command will delete the record for employee with name ‘Joe’ from
‘Employee’ table.

Delete All of the Records

To remove all the entries from the table, you can use the following query:
Query

DELETE FROM Emp;

Or
DELETE * FROM Emp;

SELECT Command:

The SELECT statement is used to select data from a database.

Select ALL columns

SELECT * FROM Employee;

Syntax: SELECT column1, column2, ...

FROM table_name;

Example: SELECT Emp_Name FROM Employee WHERE

Mobile=9935467903;

The above command will select the record from the ‘Employee’ table where the
mobile number is ‘9935467903’.

3. Data Control Language(DCL):

DCL commands are used to grant and take back authority from any database
user.
 o DCL commands are primarily used to implement access control on the
data stored in the database. It is implemented along the DML (Data
Manipulation Language) and DDL (Data Definition Language)
commands.

o It has a simple syntax and is easiest to implement in a database.

o The administrator can implement DCL commands to add or remove

database permissions on a specific user that uses the database when
required.

The two types of DCL commands are as follows:

o GRANT

o REVOKE

• GRANT Command: User access privileges to a database are given by this

command. It can be used to grant SELECT, INSERT, UPDATE, and DELETE
privileges to a user on a single table or several tables.

Syntax: GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER,

ANOTHER_USER;

Example: GRANT INSERT, SELECT on accounts to Alex

Using this command, Alex has been granted permissions on accounts database objects
like he can query or insert into accounts.

• REVOKE Command: To take back permissions from the user REVOKE

command is used. It is used to revoke a privilege (by default) or a specific
command, such as UPDATE or DELETE, depending on the situation.
Syntax: REVOKE privilege_name ON object_name FROM {user_name |PUBLIC
|role_name}

Example: REVOKE INSERT, SELECT on accounts from John

Using this command, the permissions of John like query or insert on accounts database
objects has been removed.

4. Transaction Control Language(TCL):

Transaction Control Language (TCL) instructions are used in the database to manage
transactions. This command is used to handle the DML statements’ modifications. TCL
allows you to combine your statements into logical transactions.

TCL commands can only use with DML commands like INSERT, DELETE and
UPDATE only.

These operations are automatically committed in the database that's why they
cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

o COMMIT

o ROLLBACK

o SAVEPOINT

Below mentioned are some of the TCL commands:

• COMMIT Command: To save all the transactions in the database Commit is

used.

Syntax: COMMIT;
Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;
COMMIT;
This example will insert the dob in the table which have name = Joe and then COMMIT
the changes in the database.

• ROLLBACK Command: All modifications must be canceled if any of the SQL

grouped statements produce an error. The term “rollback” refers to the process of
undoing changes. This command can only be used to reverse transactions that
have occurred since the last COMMIT or ROLLBACK command.

Syntax: ROLLBACK;

Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;

ROLLBACK;

This example would insert the dob in the table which have name = Joe and then
ROLLBACK the changes in the database. Thus, this operation would not impact the
table.

• SAVEPOINT Command: It’s used to roll back a transaction to a specific point

rather than the complete transaction.

Syntax: SAVEPOINT SavepointName;

Among all transactions, this command is exclusively used to create SAVEPOINT.

ROLLBACK is a command that is used to undo a set of transactions.

The syntax for rollback to savepoint command:

ROLLBACK TO SavepointName;

Example: SAVEPOINT S1; //savepoint created

DELETE FROM Employee WHERE Emp_Name = ‘Joe’; //deleted
SAVEPOINT S2; //Savepoint created.
SQL VIEW
Views in SQL are a kind of virtual table. A view also has rows and columns
like tables, but a view doesn't store data on the disk like a table.

o To create the view, we can select the fields from one or more tables
present in the database.

A VIEW in SQL is a logical subset of data from one or more tables. View is used to
restrict data access.

Syntax for creating a View,

CREATE or REPLACE VIEW view_name

 AS

SELECT column_name(s)

FROM table_name

WHERE condition

Creating a VIEW

Consider following Sale table,

oid order_name previous_balance customer

11 ord1 2000 Alex

12 ord2 1000 Adam

13 ord3 2000 Abhi

14 ord4 1000 Adam

15 ord5 2000 Alex

SQL Query to Create a View from the above table will be,

CREATE or REPLACE VIEW sale_view

AS
SELECT * FROM Sale WHERE customer = 'Alex';

The data fetched from SELECT statement will be stored in another object
called sale_view. We can use CREATE and REPLACE seperately too, but using both
together works better, as if any view with the specified name exists, this query will
replace it with fresh data.

Displaying a VIEW

The syntax for displaying the data in a view is similar to fetching data from a table using
a SELECT statement.

SELECT * FROM sale_view;

Force VIEW Creation

FORCE keyword is used while creating a view, forcefully. This keyword is used to create a
View even if the table does not exist. After creating a force View if we create the base
table and enter values in it, the view will be automatically updated.

Syntax for forced View is,

CREATE or REPLACE FORCE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition;
Update a VIEW

UPDATE command for view is same as for tables.

Syntax to Update a View is,

UPDATE view-name SET VALUE

WHERE condition;

NOTE: If we update a view it also updates base table data automatically.

Read-Only VIEW

We can create a view with read-only option to restrict access to the view.

Syntax to create a view with Read-Only Access

CREATE or REPLACE FORCE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition WITH read-only;

The above syntax will create view for read-only purpose, we cannot Update or Insert
data into read-only view. It will throw an error.