DATABASE

MANAGEMENT SYSTEM
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

Unit 3 Basics of SQL

Content:
1. Introduction to SQL
1.1. Basics of SQL
1.2. SQL Rules
1.3. SQL Process
1.4. SQL characteristic
1.5. SQL Advantages
1.6. SQL Datatype
1.7. SQL Command
2. Data Definition Language (DDL)
2.1 Create Command
2.2 Alter Command
2.3 Truncate Command
2.4 Drop Command
3. Data Manipulation Language (DML)
3.1 Insert Command
3.2 Update Command
3.3 Delete Command
4. Data Control Language (DCL)
4.1 Grant Command
4.2 Revoke Command

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 1
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

1. Introduction of SQL
1.1 Basics of SQL:
• SQL stands for Structured Query Language. It is used for storing and
managing data in relational database management system (RDMS).
• It is a standard language for Relational Database System. It enables a user to
create, read, update and delete relational databases and tables.
• All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server
use SQL as their standard database language.
• SQL allows users to query the database in a number of ways, using English-like
statements.

1.2 SQL Rules:

SQL follows the following rules:
• Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
• Statements of SQL are dependent on text lines. We can use a single SQL
statement on one or multiple text line.
• Using the SQL statements, you can perform most of the actions in a database.
• SQL depends on tuple relational calculus and relational algebra.
1.3. SQL Process:
• When an SQL command is executing for any RDBMS, then the system figure
out the best way to carry out the request and the SQL engine determines that
how to interpret the task.
• In the process, various components are included. These components can be
optimization Engine, Query engine, Query dispatcher, classic, etc.
• All the non-SQL queries are handled by the classic query engine, but SQL query
engine won't handle logical files.

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 2
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

1.4. Characteristics of SQL:

• SQL is easy to learn.
• SQL is used to access data from relational database management systems.
• SQL can execute queries against the database.
• SQL is used to describe the data.
• SQL is used to define the data in the database and manipulate it when needed.
• SQL is used to create and drop the database and table.
• SQL is used to create a view, stored procedure, function in a database.
• SQL allows users to set permissions on tables, procedures, and views.

1.5. Advantages of SQL:

There are the following advantages of SQL:
1. High speed
Using the SQL queries, the user can quickly and efficiently retrieve a large
number of records from a database.
2. No coding needed
In the standard SQL, it is very easy to manage the database system. It doesn't
require a substantial amount of code to manage the database system.
3. Well defined standards
Long established are used by the SQL databases that are being used by ISO and
ANSI.
4. Portability
SQL can be used in laptop, PCs, server and even some mobile phones.
5. Interactive language
SQL is a domain language used to communicate with the database. It is also
used to receive answers to the complex questions in seconds.
6. Multiple data view
Using the SQL language, the users can make different views of the database
structure.

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 3
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

1.6 SQL Datatype:

✓ SQL Datatype is used to define the values that a column can contain.
✓ Every column is required to have a name and data type in the database table.

1. Number:
Define by numeric
2. Varchar:
Define by varchar(size)
3. Char:
Define by char(size)
4. Integer
Define by integer
5. Date:
Size not given because it is fixed.
YYYY-MM-DD
6. Time:
HH:MM:SS
7. Timestamp
YYYY-MM-DD HH:MM:SS

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 4
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

1.7 SQL Commands:

• SQL commands are instructions. It is used to communicate with the database. It is
also used to perform specific tasks, functions, and queries of data.
• SQL can perform various tasks like create a table, add data to tables, drop the
table, modify the table, set permission for users.

✓ There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

SQL
command

DDL DML DCL TCL DQL

Create Commit select
Insert Grant
Alter Update Revoke Rollback
Truncate Delete Save point
Drop

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 5
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

2. 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 is auto-committed that means it permanently save all the
changes in the database.

2.1 Create:
✓ This command will create database or table.

1. To create database:
Syntax: CREATE DATABASE DATABASE_NAME;
e.g. CREATE DATABASE COMPANY;
➢ where company is the name of database.

2. To create new table:

Syntax: CREATE TABLE TABLE_NAME (
COLUMN1 DATATYPE,
COLUMN2 DATATYPE,
COLUMN3 DATATYPE,
....
);

e.g. CREATE TABLE Employee(

EmpId integer,
FirstName varchar(20),
LastName varchar(20),
Email varchar(25),
PhoneNo varchar(25),
Salary integer
);

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 6
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

Select * from employee; // for print employee table

3. To create table from already existing table:

I. When limited column required from existing table:

Syntax: CREATE TABLE NEW_TABLE_NAME (COLUMN1, COLUMN2) AS
SELECT COLUMN1, COLUMN2 FROM OLD_TABLE_NAME;
e.g. CREATE TABLE E1(empid, firstname) AS SELECT empid, firstname FROM
Employee;
Select * from e1; // for print e1 table

II. When all column required from existing table:

Syntax: CREATE TABLE NEW_TABLE_NAME AS SELECT * FROM
OLD_TABLE_NAME;

e.g. CREATE TABLE Employee_Backup AS SELECT * FROM Employee;

Select * from employee_backup; // for print employee_backup table

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 7
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

2.2 Alter:

✓ The ALTER command is a DDL command to modify the structure of existing

tables in the database by adding, modifying, renaming, or dropping columns and
constraints.

1. To add Columns in the existing Table:

SYNTAX: ALTER TABLE TABLE_NAME ADD COLUMN_NAME1 DATA_TYPE,
ADD COLUMN_NAME2 DATA_TYPE
….. ;
e.g., ALTER TABLE Employee ADD Address VARCHAR (100);

➢ Output of above syntax will be:

2. To modify size of datatype of existing column:

SYNTAX: ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME TYPE
DATA_TYPE (NEW_VALUE);

e.g., In above table of employee if we want to modify size of Firstname to 50 then

ALTER TABLE EMPLOYEE ALTER COLUMN FIRSTNAME TYPE VARCHAR (50);

➢ Output of above syntax will be:

3. To modify datatype of existing column:

SYNTAX: ALTER TABLE Table_name ALTER COLUMN Column_name
TYPE new_datatype USING column_name::new_datatype;

e.g., In above table of employee if we want to modify data type of salary then,

ALTER TABLE EMPLOYEE ALTER COLUMN SALARY TYPE VARCHAR(20) USING

SALARY::VARCHAR(20);
➢ Output of above syntax will be:

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 8
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

4. Use the DROP keyword to delete one or more columns from a table:

SYNTAX: ALTER TABLE TABLE_NAME DROP COLUMN_NAME;

e.g., If we want to delete address column from employee table then,

ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS;

➢ Output of above syntax will be

5. Use the RENAME Keyword to rename table name or column name:

5.1. To rename Table name:

SYNTAX: ALTER TABLE TABLE_NAME RENAME TO NEWTABLE_NAME;

e.g., If we want to rename Employee table name to Emp then;

ALTER TABLE EMPLOYEE RENAME TO EMP;

5.2. To rename column name:

SYNTAX: ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME

TO NEWCOLUMN_NAME;

e.g., If we want to rename in Employee table ,column name Salary to Perk then,

ALTER TABLE EMP RENAME COLUMN SALARY TO PERK;

➢ Output of above syntax will be

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 9
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

2.3 Truncate:
✓ The TRUNCATE statement is used to delete all rows from a table in the
database.
SYNTAX: TRUNCATE TABLE TABLE_NAME;

e.g., If we want to remove all data from the Emp table then,
TRUNCATE TABLE Emp;

2.4 Drop:
✓ Use the DROP TABLE statement to delete a table with data from the database.

SYNTAX: DROP TABLE TABLE_NAME;

e.g., If we want to delete the Emp table then,

DROP TABLE Emp;

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 10
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

3. Data Manipulation Language (DML)

✓ DML commands are used to modify the database. It is responsible for all form of
changes in the database.
✓ 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.

3.1 Insert:
✓ The INSERT statement is used to insert single or multiple records into a table.
SYNTAX:
INSERT INTO table_name (column_name1, column_name2, ..., column_nameN)
VALUES (column1_value, column2_value, ..., columnN_value);
e.g.,
1. If data entry into specific column, then:
INSERT INTO Emp (empid, firstname, phoneno) VALUES (1, 'John ', 33000);

Select * from emp; // for print emp table

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 11
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

2. If data entry into all column, then:

INSERT INTO Emp
VALUES (2,'Mark','King', 'Mark.king@abc.com', '123.123.1834', 33000);

Select * from emp; // for print emp table

3. If multiple data entry into all column, then:

INSERT INTO Emp
VALUES (3, 'Neena', 'Kochhar', '[email protected]', '123.456.4568', 17000),
(4, 'Lex', 'De Haan', '[email protected]', '123.456.4569', 15000);

Select * from emp; // for print emp table

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 12
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

3.2 Update:
✓ The UPDATE command is used to update records of the table in the database.
SYNTAX:
UPDATE table_name SET column_name1 = new_value, column_name2 =
new_value WHERE Condition;

e.g.,
1. If we want to change email of mark, then:
UPDATE Emp SET email = 'jking@test.com' WHERE EmpId = 2;
Select * from emp; // for print emp table

2. If we want to change perk and firstname of Neena, then:

UPDATE Emp SET perk = 50000, firstname= 'Naina' WHERE EmpId =3;
Select * from emp; // for print emp table

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 13
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

3.3 Delete:
✓ Use the DELETE statement to delete records from the existing table in the current
schema or tables of the schema on which you have the DELETE privilege.
SYNTAX:
DELETE FROM table_name WHERE Condition;

e.g.,
1. If we want to delete empid number 4, then:
DELETE FROM Emp WHERE EmpId = 4;
Select * from emp; // for print emp table

2. If we want to delete data of phoneno having 33000, then:

DELETE FROM Emp WHERE phoneno = '33000';
(Here phoneno’s datatype is varchar)
Select * from emp; // for print emp table

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 14
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

4. Data Control Language (DCL)

✓ DCL commands are used to grant and take back authority from any database user.
✓ Here are some commands that come under DCL:
1. Grant
2. Revoke

4.1 Grant:
✓ You can grant users various privileges to tables. These permissions can be any
combination of SELECT, INSERT, UPDATE, DELETE

➢ First create User with following commands:

Syntax:
1. Create a user with a password:
CREATE USER Mark WITH PASSWORD 'jw8s0F4';

➢ Grant provides to user:

Syntax:
Grant Privileges ON object TO user;
e.g.,
GRANT select ON emp TO Mark;
➢ Here Privileges can be select, insert, update, delete, etc.…

4.2 Revoke:
✓ With this command, it can revoke or take grant from users for various privileges to
tables.

Syntax:

Revoke Privileges ON object from user;

e.g.,
Revoke select ON emp From Mark;

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 15
 L.J Institutes of Engineering and Technology
Semester: II Subject: Database Management System
Unit-3 Basics of SQL

Prepared By: Mr. Milan Trivedi & Mr. Divyang Patel Page| 16