DBMS: Database Languages
Akhilesh Arya
Akhilesh Deep Arya: 9460508551
Database Language
• Database languages are used to read, store and update
the data in the database.
• Following are the different types of database languages:
Database Language
Data Definition Transaction Control
Language language
Data Control Data Manipulation
Language Language
Akhilesh Deep Arya: 9460508551
Data Definition Language
• DDL is used to define database objects.
• It is used to create schema, tables, indexes, constraints,
etc. in the database.
Create: It is used to create objects in the database.
Alter: It is used to alter the structure of the database.
Drop: It is used to delete objects from the database.
Truncate: It is used to remove all records from a table.
Rename: It is used to rename an object.
Akhilesh Deep Arya: 9460508551
Data Control Language
• DCL statements control access to data and the database
using statements such as GRANT and REVOKE.
• A privilege can either be granted to a User with the help
of GRANT statement.
• Privilege can be canceled by using REVOKE command.
Grant: It is used to give user access privileges to a database.
Revoke: It is used to take back permissions from the user.
Akhilesh Deep Arya: 9460508551
Data Manipulation Language
• A DML is a language that enables users to access or
manipulate data stored in the database.
• Data manipulation involves retrieval of data from the
database, insertion of new data into the database and
deletion of data or modification of existing data.
Select: It is used to retrieve data from a database.
Insert: It is used to insert data into a table.
Update: It is used to update existing data within a table.
Delete: It is used to delete all records from a table.
Akhilesh Deep Arya: 9460508551
Transaction Control Language
• TCL is used to run the changes made by the DML
statement. TCL can be grouped into a logical transaction.
Commit: It is used to save the transaction on the database.
Rollback: It is used to restore the database to original since the last Commit.
Akhilesh Deep Arya: 9460508551
SQL Data Types
• A data type specifies size range and nature of data field
in database.
SQL Data Types
Binary Date
Approximate
String
Numeric
Exact Number
Akhilesh Deep Arya: 9460508551
Binary
• There are Three types of binary Data types which are
given below
Data Type Description
binary It has a maximum length of 8000 bytes. It contains fixed-
length binary data.
varbinary It has a maximum length of 8000 bytes. It contains
variable-length binary data.
image It has a maximum length of 2,147,483,647 bytes. It
contains variable-length binary data.
Akhilesh Deep Arya: 9460508551
Approximate Numeric
Data type From To Description
Float -1.79E + 308 1.79E + 308 It is used to specify a floating-point
value e.g. 6.2, 2.9 etc.
Real -3.40e + 38 3.40E + 38 It specifies a single precision
floating point number
Akhilesh Deep Arya: 9460508551
Exact Numeric
Data type To From Description
int -2147483648 2147483647 It is used to specify an integer value.
smallint -32768 32767 It is used to specify small integer value.
bit (size) 1 64 It has the number of bits to store.
decimal (size, d) The maximum number for size is 65. The maximum number for d is 30.
The default value for size is 10. The default value for d is 0.
Akhilesh Deep Arya: 9460508551
String
Data type Description
char It has a maximum length of 8000 characters. It contains Fixed-length
non-unicode characters.
varchar It has a maximum length of 8000 characters. It contains variable-
length non-unicode characters.
text It has a maximum length of 2,147,483,647 characters. It contains
variable-length non-unicode characters.
Akhilesh Deep Arya: 9460508551
Date and Time
Data-type Description
date Format: YYYY-MM-DD. The supported range is from '1000-01-01'
to '9999-12-31'
time It is used to store the hour, minute, and second values.
Format: hh:mm:ss.
timestamp It stores the year, month, day, hour, minute, and the
second value. Format: YYYY-MM-DD hh:mm:ss.
Akhilesh Deep Arya: 9460508551
SQL Operators
Operators
Arithmetic Logical Comparison
Addition (+) All Equal to (=)
Subtraction (-) And Not Equal to (!=, <>)
Multiplication (*) Any >, <, >=, <=
Division (/) Between !<, !>
Modulus (%) Exists
Akhilesh Deep Arya: 9460508551
Install MySQL
Step-1: https://www.mysql.com/downloads/
Step-2: Select MySQL Community (GPL) Downloads
Step-3: Select MySQl installer for windows to download
Step-4: Click the installer select custom select
Step-5: Select MySQL server latest edition
Step-6: Select MySQL workbench latest edition
Step-7: Select MySQL Shell latest edition
Step-8: Select password for your Root user in Account and Roles
Step-9: Click finish and TA-DA
Akhilesh Deep Arya: 9460508551
MySQL Workbench
Step-1: Connect SQL Server
A: Open Command Prompt
B: Select the path of the SQL server bin
C: Type cd path of SQL server and press enter
D: Type the command mysql –u root -p
E: Enter the root password
Step-2: Start MySQL workbench
Step-3: Click on Local instance MySQL80
Step-4: Enter the same password of the root
And we are ready to type run our first SQL query
Akhilesh Deep Arya: 9460508551
SQL STATEMENTS
Akhilesh Deep Arya: 9460508551
Data Definition Language
Create Table
CREATE TABLE EMPLOYEE (
EMPID INT NOT NULL,
EMP_NAME VARCHAR (25) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (30),
SALARY INT NOT NULL,
PRIMARY KEY (EMPID)
);
Akhilesh Deep Arya: 9460508551
Cont..
DESC EMPLOYEE;
Field Type Null Key Default
EMPID int(11) NO PRI NULL
EMP_NAME varchar(25) NO NULL
AGE int(11) NULL
ADDRESS Char (30) NULL
SALARY INT(11) NO NULL
Akhilesh Deep Arya: 9460508551
Cont..
• Alter Table
ALTER TABLE EMPLOYEE
ADD EMAIL CHAR(255) NOT NULL;
ALTER TABLE EMPLOYEE
DROP COLUMN ADDRESS;
Akhilesh Deep Arya: 9460508551
Cont..
DESC EMPLOYEE;
Field Type Null Key Default
EMPID int(11) NO PRI NULL
EMP_NAME varchar(25) NO NULL
AGE int(11) NULL
SALARY INT(11) NO NULL
EMAIL char(30) NO NULL
Akhilesh Deep Arya: 9460508551
Cont..
DROP EMPLOYEE;
• The whole structure of the table including the data
gets deleted when we use drop table.
Akhilesh Deep Arya: 9460508551
Cont..
Create Table
CREATE TABLE EMPLOYEE (
EMPID INT NOT NULL,
EMP_NAME VARCHAR (25) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (30),
SALARY INT NOT NULL,
PRIMARY KEY (ID)
);
Akhilesh Deep Arya: 9460508551
Cont..
Suppose EMPLOYEE table have following entries in it.
EMPID EMP_NAME AGE ADDRESS SALARY
101 JOHN 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
103 SANKET 25 BANGLORE 45000
104 KHUSHI 29 GANDHINAGAR 40000
105 SHUBH 30 AJMER 30000
106 JONATHAN 25 PUNE 34000
Akhilesh Deep Arya: 9460508551
Cont..
RENAME EMPLOYEE TO EMP;
SELECT *FROM EMP;
EMPID EMP_NAME AGE ADDRESS SALARY
101 JOHN 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
103 SANKET 25 BANGLORE 45000
104 KHUSHI 29 GANDHINAGAR 40000
105 SHUBH 30 AJMER 30000
106 JONATHAN 25 PUNE 34000
Akhilesh Deep Arya: 9460508551
Cont..
DELETE FROM EMP WHERE EMPID=104;
SELECT * FROM EMP;
EMPID EMP_NAME AGE ADDRESS SALARY
101 JOHN 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
103 SANKET 25 BANGLORE 45000
105 SHUBH 30 AJMER 30000
106 JONATHAN 25 PUNE 34000
Akhilesh Deep Arya: 9460508551
Data Manipulation Language
• Delete Command
DELETE EMPLOYEE;
In this case all the entries from the table employee
gets deleted but structure remains unchanged
You can enter new values in it using the insert
command
Akhilesh Deep Arya: 9460508551
Cont..
• Insert Command
INSERT INTO EMPLOYEE VALUES (101, “JOHN”, 31,
“JAIPUR”, 25000);
INSERT INTO EMPLOYEE VALUES (102, “ANDREW”,
33, “UDAIPUR”, 31000);
Make 4 more entries in the similar way
Akhilesh Deep Arya: 9460508551
Cont..
SELECT * FROM EMPLOYEE;
EMPID EMP_NAME AGE ADDRESS SALARY
101 JOHN 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
103 SANKET 25 BANGLORE 45000
104 KHUSHI 29 GANDHINAGAR 40000
105 SHUBH 30 AJMER 30000
106 JONATHAN 25 PUNE 34000
Akhilesh Deep Arya: 9460508551
Cont..
UPDATE EMPLOYEE SET EMP_NAME = 'KSHITIZ'
WHERE EMPID = 101;
SELECT * FROM EMPLOYEE;
EMPID EMP_NAME AGE ADDRESS SALARY
101 KSHITIZ 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
103 SANKET 25 BANGLORE 45000
104 KHUSHI 29 GANDHINAGAR 40000
105 SHUBH 30 AJMER 30000
106 JONATHAN PUNE
25 Deep Arya: 9460508551
Akhilesh 34000
Commit vs. Rollback (TCL)
SELECT * FROM EMPLOYEE;
EMPID EMP_NAME AGE ADDRESS SALARY
101 KSHITIZ 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
103 SANKET 25 BANGLORE 45000
104 KHUSHI 29 GANDHINAGAR 40000
105 SHUBH 30 AJMER 30000
106 JONATHAN 25 PUNE 34000
Employee
Akhilesh DeepRelation
Arya: 9460508551
Commit
DELETE FROM EMPLOYEE WHERE AGE <= 25;
COMMIT;
SELECT * FROM EMPLOYEE;
EMPID EMP_NAME AGE ADDRESS SALARY
101 KSHITIZ 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
104 KHUSHI 29 GANDHINAGAR 40000
105 SHUBH 30 AJMER 30000
Akhilesh Deep Arya: 9460508551
Rollback
DELETE FROM EMPLOYEE Where AGE = 30;
SELECT * FROM EMPLOYEE;
EMPID EMP_NAME AGE ADDRESS SALARY
101 KSHITIZ 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
104 KHUSHI 29 GANDHINAGAR 40000
Akhilesh Deep Arya: 9460508551
Commit
ROLLBACK;
SELECT * FROM EMPLOYEE;
EMPID EMP_NAME AGE ADDRESS SALARY
101 KSHITIZ 31 JAIPUR 25000
102 ANDREW 33 UDAIPUR 31000
104 KHUSHI 29 GANDHINAGAR 40000
105 SHUBH 30 AJMER 30000
Akhilesh Deep Arya: 9460508551
