SQL POWERPOINT PRESENTATION ON SQL .pptx

SQL
Structured Query Language
1
E-CONTENT PREPARATION FOR
CLASS XI
CHAPTER- 8

INTRODUCTION
🞆SQL stands for Structured Query Language.
🞆It is a Standard language used for accessing and
manipulating relational databases.
🞆Pronounced as SEQUEL.
🞆It was originally founded and developed in Sweden by
David Axmark, Allan Larsson and Michael Widenius.
🞆The benefit with SQL is that we don’t have to specify
how to get the data from the database. Rather, we
simply specify what is to be retrieved, and SQL does the
rest.
🞆SQL is the most popular query language used by major
relational database management systems such as
MySQL, ORACLE, SQL Server, etc. 2

CHARACTERISTICS OF SQL
🞆Cost: It is released under an open–source license
and hence required no cost or payment for its
usage.
🞆Speed: It has superior speed. It is easy to use
and is reliable.
🞆Ease of Use: It is a simple database system. It
can be easily managed from the command line.
Several graphical interfaces are also available.
🞆Query Language Support: Standard SQL
commands are understandable to MySQL.
3

CHARACTERISTICS OF SQL
🞆Data Types: It supports many data types to
support different types of data. It also supports
fixed–length and variable–length records.
🞆Security: It offers privilege and password
system that is very flexible and secure.
🞆Scalability and Limits: It can handle large
databases.
🞆Connectivity: Clients can connect to MySQL
server easily.
4

FEATURES OF SQL
🞆It performs the task of creation and modification
of database structure.
🞆Changing security settings for system.
🞆Permitting users for working on databases or
tables
🞆Querying a database.
🞆Inserting/modifying/deleting the database
contents.
5

MYSQL
🞆MySQL is an open source RDBMS software which can
be easily downloaded from the official website
https://mysql.com
🞆Create and supported by MySQL AB, a company based
in Sweden. This company is now subsidiary of Sun
Microsystems. In April 2009, Oracle Corp. acquired
Sun Microsystems.
🞆Few rules to follow while writing SQL statements in
MySQL:
▪ SQL is case insensitive. That means name and NAME
are same for SQL.
▪ Always end SQL statements with a semicolon (;).
▪ To enter multiline SQL statements, we don’t write ‘;’
after the first line. We put enter to continue on next
line.
6

MYSQL
▪ NULL denotes missing or undefined value.
▪ It can appear in a column of any data type provided
they are not explicitly restricted by NOT NULL or
Primary Key constraint.
▪ NULL is not equivalent to zero or a blank space.
▪ Comments: It is a text that is not executed.
▪ They are used for documentation purpose only.
▪ Three types of comments in MySQL are-
• /*comment*/ (Multiline)
• --comment (Single Line)
• #comment (Single Line) 7

DATA TYPES IN MYSQL
🞆Data type indicates the type of data value that
an attribute can have.
🞆The data type of an attribute decides the
operations that can be performed on the data of
that attribute. For example, arithmetic
operations can be performed on numeric data but
not on character data.
🞆MySQL supports three categories of data types:
▪ Numeric
▪ Date and Time
▪ String
8

COMMONLY USED DATA TYPES IN
MYSQL
Data Type Description
INT
A normal–sized integer that can be signed or
unsigned. If signed, the allowable range is from –
2147483648 to 2147483647. If unsigned, the
allowable range is from 0 to 4294967295. Each INT
value occupies 4 bytes of storage. For values larger
than that, we have to use BIGINT, which occupies 8
bytes
DECIMAL(M,D)
Represents a floating point number. M is the total
number of digits, and D is the number of digits after
decimal point.
FLOAT
Holds numbers with decimal points. Each float value
occupies 4 bytes
CHAR(M)
A fixed–length string between 0 and 255 characters in
length where M is the length. Providing M is optional
and its default value is 1.
9

COMMONLY USED DATA TYPES IN
MYSQL
Data Type Description
VARCHAR(M)
A variable–length string between 0 and 65535
characters in length. Here, providing M is
mandatory.
DATE
A date in YYYY–MM–DD format, between
1000–01–01 and 9999–12–31
TIME To store time in the format HH:MM:SS
10

DIFFERENCE BETWEEN CHAR AND
VARCHAR
CHAR VARCHAR
FIXED LENGTH.
When a column is given data type
as CHAR(n), then all values stored
in that column have this length,
i.e. n bytes. If a value is shorter
than this length n then blanks are
added, but the size of the value
remains n bytes.
VARIABLE LENGTH.
When a column is given data type as
VARCHAR(n), then the maximum
size of a value is n bytes. Each value
that is stored in this column stores
exactly as given, no blanks are
added if the length is shorter than
maximum length. However, if the
maximum length is exceeded than
an error message will be displayed.
It is used when our string has fixed
length. Ex. Aadhar No,Phone No.
etc.
It is used when our string does not
have fixed length. Ex. Names,Cities
etc.
Holds maximum of 255 characters. Holds maximum of 65535 characters.
Uses Static memory allocation. Uses Dynamic memory allocation. 11

CONSTRAINTS
🞆Constraints are certain types of restrictions on the
data values that an attribute can have.
🞆They are used to ensure the accuracy and reliability
of data.
🞆However, it is not mandatory to define constraint for
each attribute of a table
12

CONSTRAINTS
Constraint Description
NOT NULL
Ensures that a column cannot
have NULL values where NULL
means missing/ unknown/not
applicable value.
UNIQUE
Ensures that all the values in a
column are distinct / unique.
DEFAULT
A default value specified for the
column if no value is provided.
PRIMARY KEY
The column which can uniquely
identify each row or record in a
table.
FOREIGN KEY
The column which refers to
value of an attribute defined as
primary key in another table.
13

CONSTRAINTS
🞆NOT NULL Constraint: When this constraint is
set to a column, it ensures that the value ‘NULL’
cannot be entered in the specified column in any
row.
Ex: CREATE TABLE Student (RollNo INT(5) NOT
NULL, Name VARCHAR(25));
🞆UNIQUE Constraint: When this constraint is set
to a column, it ensures that the duplicate values
cannot be entered in the specified column.
Ex: CREATE TABLE Student (RollNo INT(5)
UNIQUE, Name VARCHAR(25)); 14

CONSTRAINTS
🞆PRIMARY KEY Constraint: This constraint sets a
column or a group of columns as the Primary Key of
a table. Therefore, NULLs and duplicate values in
this column are not accepted.
Ex: CREATE TABLE Student (RollNo INT(5)
PRIMARY KEY, Name VARCHAR(25));
🞆FOREIGN KEY Constraint: Data will be accepted
in this column, if same data value exists in a column
in another related table. This other related table
name and column name are specified while creating
the foreign key constraint
Ex: CREATE TABLE Result (RollNo INT(5),
Marks INT(3), FOREIGN KEY(RollNo)
REFERENCES Student(RollNo)); 15

CONSTRAINTS
🞆ENUM Constraint: This constraint defines a set of
string values as the column domain. So any value in this
column will be from the specified values only.
Ex: CREATE TABLE Person (Name VARCHAR(40),
Gender ENUM(‘Male’, ‘Female’));
🞆 Now, while inserting rows into the table Person, for the
field Gender the value either ‘Male’ or ‘Female’ only is to
be entered. Attempting entering value other than this
will results in error.
🞆DEFAULT Constraint: This constraint is used to
assign a default value when no value is provided to
attribute.
Ex: CREATE TABLE Person (Name VARCHAR(40),
Gender VARCHAR(2) DEFAULT ‘Male’);
🞆 Now, while inserting data into the table if value for the
gender is not provided then the value ‘Male’ will be
assigned.
16

SQL COMMAND SYNTAX
🞆SQ L has a set of predefined commands.
🞆SQL command syntax has following elements-
🞆SQL commands are case insensitive.
17
Terminology Description
Keyword A keyword refers to a special word that has a
special meaning to SQL. For example, SELECT
and FROM are keywords
Clause A clause is a portion of an SQL statement. Each
clause is identified by a keyword.
Statement A statement is a combination of two or more
clauses.

DATA DEFINITION LANGUAGE (DDL)
🞆The DDL part of SQL permits database tables to
be created or deleted. It also defines indices
(keys), specifies links between tables, and
imposes constraints on tables.
🞆Examples of DDL commands in SQL are:
▪ CREATE DATABASE - creates a new database.
▪ CREATE TABLE - creates a new table.
▪ ALTER TABLE - modifies a table.
▪ DROP TABLE - deletes a table.
🞆These commands will work with table structure
not on table data directly (indirectly may act on
table data). 18

DATA MANIPULATION LANGUAGE (DML)
🞆The Query and Update commands on tables are
referred as DML commands. A few examples for
DML commands in SQL are:
⚫SELECT - Extracts data from a table
⚫UPDATE - Updates data in a table
⚫DELETE - Deletes data from a table
⚫INSERT INTO - Inserts new data into a table
19

DATA CONTROL LANGUAGE (DCL)
🞆DCL commands deals with the rights, permissions
and other controls of the database system.
🞆Examples of DCL commands:
▪ GRANT- gives user access privileges to database.
▪ REVOKE- withdraws user access privileges given
by using the GRANT command.
20

TRANSACTION CONTROL LANGUAGE (TCL)
🞆TCL deals with the transactions within the
database.
🞆Examples of TCL commands:
▪ COMMIT– commits a transaction.
▪ ROLLBACK– rollbacks a transaction.
▪ SAVEPOINT– defines a marker in a transaction.
These markers are useful in rolling back a
transaction till the marker.
▪ SET TRANSACTION–specify characteristics for
the transaction.
21

CREATING AND USING DATABASE
🞆To create a database the CREATE DATABASE
command is used.
🞆Syntax:
CREATE DATABASE [IF NOT EXISTS]
<Database_Name>;
🞆The IF NOT EXISTS is an optional clause of the
statement. The IF NOT EXISTS clause prevents us
from an error of creating a new database that already
exists in the database server.
🞆No two database in MySQL server can have the same
database name.
Ex : CREATE DATABASE School;
The above statement creates a database with the name
School. To work with this database this database
should be opened using USE statement, as follows
🞆Syntax : USE <Database_Name>;
Ex : USE School;
22

LISTING DATABASES
🞆To list all databases on a MySQL server host, we
use the SHOW DATABASES command as follows:
🞆SHOW DATABASES;
23

REMOVING DATABASE
🞆Removing database means deleting all the tables
contained in the database and the database itself
permanently.
🞆To delete a database, we use the statement as
follows:
DROP DATABASE [IF EXISTS] database_name;
E.g. DROP DATABASE school;
🞆The IF EXISTS is an optional part of the
statement to prevent you from removing a
database that does not exist in the database
server.
24

CREATING TABLE
🞆We can create a table/relation in our database with
the CREATE TABLE command.
🞆Syntax:
CREATE TABLE <table-name> (< column name>
<data type> [ <size>],(< column name><data
type> [ <size>], …);
E.g. CREATE TABLE STUDENT(
Roll_No int(2) primary key,
Name varchar(25),
Class int(2),
Marks decimal(3,1)
); 25

VIEWING TABLE STRUCTURE
🞆The DESCRIBE statement can be used to see the
structure of a table.
🞆It displays the Column names, their data types,
constraints etc.
🞆Syntax:
DESCRIBE <table name>;
OR
DESC <table name>;
E.g. DESCRIBE student;
26

INSERTING RECORDS IN TABLE
🞆INSERT INTO command can be used to insert
rows of data into a table. Its usage is as follows:
🞆Syntax:
INSERT INTO STUDENT (Roll,Name,Class,DOB)
VALUES (18,‘Rajeev,11,’2000-10-18’);
INSERT INTO STUDENT (Roll,Name,Class)
VALUES (07,‘Siri,11);
( In this case the value NULL will be assigned to
the field DOB )
28

🞆However while providing data for all the column
elements the column names need not be provided.
🞆Syntax :
INSERT INTO <TableName> VALUES (<Value1>,
<Value2>, <Value3>, ………<ValueN>);
Ex : INSERT INTO STUDENT VALUES(03,
‘Pritish’, ‘11’, ‘1996–04–05’);
🞆We can also insert multiple records at a time.
Ex : INSERT INTO STUDENT VALUES
(03, ‘Pritish’, ‘11’, ‘1996–04–05’),
(01, ‘Arup’, ‘11’, ‘1989–08–05’);
29

🞆It is important to observe the following points
while inserting records in table :
▪ Values must be passed in the order of their
column.
▪ Char, Varchar data types must be enclosed in
single quotes.
▪ Date data type must also be enclosed in single
quotes.
▪ Date values are entered in the format (yyyy-mm-
dd)
30

RETRIEVING (DISPLAYING) DATA OF A
TABLE:
🞆The SELECT command is used to display data of a
table. It is also possible to display the filtered data
from the table.
🞆To Display all the rows and columns of Table:
Syntax :
SELECT * FROM <TableName>;
Ex: SELECT * FROM Student;
🞆To Display selected Columns and all Rows:
Syntax:
SELECT<ColumnName1>,<ColumnName2>, ….
>,<ColumnNameN> FROM <TableName>;
Ex: SELECT Roll, Name FROM Student;
31

RETRIEVING (DISPLAYING) DATA OF A
TABLE:
🞆To Display selected Columns and selected Rows:
Syntax:
SELECT <ColumnName1>, <ColumnName2>, …. FROM
<TableName> WHERE Condition;
Ex: SELECT Roll, Name FROM Student WHERE Class =
11;
🞆Eliminating Redundant Data with DISTINCT
Keyword:
Syntax :
SELECT DISTINCT <ColumnName1>,
<ColumnName2>,… FROM <TableName>;
Ex. SELECT DISTINCT Class FROM Student;
🞆DISPLAYING CURRENT DATABASE:
To display the name of the present working database, the
following statement can be used:
SELECT DATABASE( );
32

NULL
🞆NULL means a value that is unavailable,
unassigned, unknown or inapplicable. NULL is not
the same as zero or a space or any other character.
In a table NULL is searched for using IS NULL
keywords.
Ex: SELECT * FROM Student WHERE Name IS
NULL;
Ex: SELECT * FROM Employee WHERE
Commission IS NULL;
🞆 NOT NULL values in a table can be searched using
IS NOT NULL.
Ex: SELECT * FROM Employee WHERE
Commission IS NOT NULL;
33

SIMPLE CALCULATION IN MYSQL
🞆MySQL doesn’t require every SELECT statement
to refer to a table, so we can select the results of
arbitrary expressions without providing WHERE
clause.
Ex: SELECT 10+8; result will be 18
SELECT 10/2; result will be 5
Note: Any operation with NULL will result in
NULL.
Ex: SELECT 10+NULL; result will be NULL
34

ALTER COMMAND
🞆The ALTER TABLE command is used to change an
existing table’s structure .
🞆 Adding an attribute to a Table:
Syntax : ALTER TABLE <TableName> ADD
<ColumnName> DataType;
Ex : ALTER TABLE Student ADD Grade CHAR(1);
🞆Removing an attribute of a Table:
Syntax : ALTER TABLE <TableName> DROP
<ColumnName>;
Ex : ALTER TABLE Student DROP LAST_NAME;
🞆Modifying datatype of an attribute of a Table:
Syntax: ALTER TABLE <TableName> MODIFY
<ColumnName> <New_Definition>;
Ex : ALTER TABLE Student MODIFY Class
VARCHAR(4); 35

ALTER COMMAND
🞆 Adding Primary Key constraint to a Table:
Syntax: ALTER TABLE <TableName> ADD
PRIMARY KEY(ColumnName);
Ex : ALTER TABLE Student ADD PRIMARY
KEY(RollNo);
🞆Deleting Primary Key constraint to a Table:
Syntax :ALTER TABLE <TableName> DROP
PRIMARY KEY;
Ex: ALTER TABLE Student DROP PRIMARY KEY;
36

ALTER COMMAND
🞆 Adding Foreign Key constraint to a Table:
Syntax: ALTER TABLE <TableName> ADD
FOREIGN KEY(ColumnName) REFERENCES
<Referenced_TableName>(ColumnName);
Ex :ALTER TABLE Student ADD FOREIGN
KEY(RollNo) REFERENCES Marks(RollNo);
🞆 Deleting Foreign Key constraint to a Table:
Syntax: ALTER TABLE <TableName> DROP
FOREIGN KEY Constraint_Name;
Ex: ALTER TABLE STUDENT DROP FOREIGN
KEY fk_category;
37

ALTER COMMAND
🞆 Adding UNIQUE constraint:
Syntax: ALTER TABLE <table_name> ADD
CONSTRAINT UNIQUE (column/column_list);
Ex: ALTER TABLE Department ADD
CONSTRAINT UNIQUE (DEPCODE);
🞆 Deleting UNIQUE constraint:
Syntax: ALTER TABLE <table_name> DROP
INDEX <index_name>;
Ex: : ALTER TABLE Department DROP INDEX
DEPCODE;
38

ALTER COMMAND
🞆 Adding DEFAULT value to an attribute:
Syntax: ALTER TABLE <table_name>ALTER
<column_name> SET DEFAULT <default_value>;
Ex: ALTER TABLE Department
ALTER Location SET DEFAULT ‘Guwahati';
🞆Deleting DEFAULT value to an attribute:
Syntax: ALTER TABLE <table_name>
ALTER <column_name> DROP DEFAULT;
Ex: ALTER TABLE Department
ALTER City DROP DEFAULT;
39

ALTER COMMAND
🞆 Adding NOT NULL constraint to an attribute:
MODIFY <column_name> <data_type> NOT NULL;
Ex: ALTER TABLE Persons MODIFY Age INT
NOT NULL;
🞆Deleting NOT NULL constarint to an attribute:
MODIFY <column_name> <data_type> NULL;
Ex: ALTER TABLE Persons MODIFY Age INT
NULL;
40

DROP STATEMENT
🞆 Sometimes a table in a database or the database
itself needs to be removed. We can use DROP
statement to remove a database or a table
permanently from the system. However, one should
be very cautious while using this statement as it
cannot be undone.
▪ Syntax to drop a table:
DROP TABLE table_name;
Ex: DROP TABLE Admission;
▪ Syntax to drop a database:
DROP DATABASE database_name;
Ex: DROP DATA BASE Student; 41

SELECT STATEMENT
🞆 The SQL statement SELECT is used to retrieve data
from the tables in a database and is also called query
statement.
🞆Syntax:
SELECT attribute1, attribute2, ...
FROM table_name
WHERE condition;
Ex: SELECT Name, Marks from STUDENT where
Marks>=100;
Note: SELECT * FROM <table_name> is used to
retrieve all the records from a table;
42

SELECT STATEMENT
🞆 The FROM clause is always written with SELECT
clause as it specifies the name of the table from
which data is to be retrieved.
🞆The WHERE clause is optional and is used to
retrieve data that meet specified condition(s).
43

USING ARITHMETIC OPERATORS
WITH SELECT:
🞆 Arithmetic operators perform mathematical
calculations. In SQL the following arithmetic
operators are used:
44
Operator Meaning
+ Addition
– Subtraction
* Multiplication
/ Division
%
Modulus Division (Remainder
Division)

COLUMN ALIAS
🞆 When arithmetic expressions are used in SELECT
statement the Column name will be the expression.
For example, for the query:
SELECT Age+3 FROM Student;
Here, the column name of the output will be Age+3.
🞆This column name can be changed (aliased) as
follows:
SELECT Age+3 AS “Modified Age” FROM Student;
🞆Here the keyword AS is optional
45

RELATIONAL OPERATORS:
🞆The relational operators used in MySQL are as
follows:
46
Operator Meaning
< Less Than
<= Less Than or Equal to
> Greater Than
>= Greater Than or Equal to
= Equal to
!= (Or) <> Not Equal to

LOGICAL OPERATORS:
🞆The relational operators used in MySQL are as
follows:
47
Operator Symbol Meaning
AND &&
If both conditions are true
then output will also result to
true.
OR ||
If either condition is true
output results to true.
NOT !
The NOT operator reverses
the meaning. Makes the input
True as False and vice–versa.

CONDITIONS BASED ON A LIST (USING IN)
🞆The IN operator select values that match any value
in the given list of values. To display data of
Students whose marks are 68 or 76 or 78, we can
use the IN operator like this:
Ex: SELECT RollNo, Name, Marks FROM
Student WHERE Marks IN (68, 76, 78);
Ex: SELECT * FROM Employee WHERE State
IN(‘ASSAM', 'MIZORAM', ‘WEST BENGAL');
🞆The NOT IN operator can be used to negate the
output of IN operator.
Ex: SELECT RollNo, Name, Marks FROM
Student WHERE Marks NOT IN (68, 76, 78);
Ex: SELECT * FROM Employee WHERE State
IN(‘ASSAM', 'MIZORAM', ‘WEST BENGAL'); 48

CONDITIONS BASED ON RANGE (USING
BETWEEN)
🞆The BETWEEN operator is used for range search. It is
used along with WHERE clause.
Ex: SELECT RollNo, Name, Marks FROM Student
WHERE Marks1 BETWEEN 70 AND 80;
🞆The above query will display the RollNo, Name, Marks
from the Student table whose value of Marks will be
between 70 and 80, both inclusive.
🞆The NOT BETWEEN can be used to negate the output
of BETWEEN.
Ex: SELECT RollNo, Name, Marks FROM Student
WHERE Marks NOT BETWEEN 70 AND 80;
🞆The above query will displays the RollNo, Name, Marks
from the Student table whose value of Marks are not be
between 70 and 80, both exclusive.
49

PATTERN MATCHING (USING LIKE
CLAUSE)
🞆SQL provides LIKE operator that can be used with
WHERE clause to search for a specified pattern in
a column.
🞆The LIKE clause tells the DBMS that we won't be
doing a strict comparison like = or < or > but we
will be using wildcards in our comparison.
🞆Syntax: SELECT <column name>, [<column
name>…] WHERE <column name> LIKE Pattern;
🞆The LIKE operator makes use of the following two
wild card characters:
▪ % (percent)— used to represent zero, one, or
multiple characters.
▪ _ (underscore)— used to represent a single
character.
50

CLAUSE)
🞆Ex: SELECT * FROM Student WHERE Name LIKE
'%tha';
'G%';
'G%b';
'%Sen%';
'A_ _ _ _ Sharma';
🞆The keyword NOT LIKE is used to select the rows that
do not match the specified pattern. To display rows
from the table Student that have names not starting
with 'G', we can enter:
Ex: SELECT * FROM Student WHERE Name NOT
LIKE 'G%';
51

CLAUSE)
🞆A few more general examples are:
▪ 'As%' matches any string starting with As.
▪ '%Deka%' matches any string containing ‘Deka’.
▪ '%a' matches any string ending with 'a’.
▪ '_ _ _' matches any string that is exactly 3
characters long.
▪ '_ _ %' matches any string that has at least 2
characters.
▪ '_ _ _ j' matches any string that is 4 characters long
with any 3 characters in the beginning but ‘j' as the
4th character.
52

PRECEDENCE OF OPEARATORS
🞆All the operators have precedence. Precedence is
the order in which different operators are
evaluated in the same expression. When evaluating
an expression containing multiple operators,
operators with higher precedence are evaluated
before evaluating those with lower precedence.
🞆Operators with equal precedence are evaluated
from left to right within the expression.
🞆Parenthesis can be used to change the preference of
an operator.
53

PRECEDENCE OF OPEARATORS
🞆Various operators in descending order of precedence
(top to bottom) are listed below:
!
– (unary minus)
^
*, /, DIV, %, MOD
-, +
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, IN
BETWEEN,
NOT
&&, AND
||, OR
54

SORTING OF RESULTS(USING ORDER BY
CLAUSE)
🞆The result obtained using SELECT statement is
displayed in the order in which the rows were
entered in the table using the INSERT INTO
statement. The results of the SELECT statement
can be displayed in the ascending or descending
values of a single column or multiple columns using
ORDER BY clause.
🞆Syntax: SELECT <column name>, [<column
name>…] [WHERE <Condition list>] ORDER BY
<column name>;
🞆Ex: SELECT * FROM Student ORDER BY
Marks;
🞆Ex: SELECT * FROM Student ORDER BY
Name;
55

SORTING OF RESULTS(USING ORDER BY
CLAUSE)
🞆To display data in descending order, DESC keyword
is used in ORDER BY clause. However it is not
necessary to specify ASC for ascending order as it is
the default order.
Ex: SELECT * FROM Student ORDER BY
Marks DESC;
56

SORTING ON COLUMN ALIAS
🞆If a Column alias is defined on a column, we can
use it for displaying rows in an ascending or
descending order using ORDER BY clause:
Ex: SELECT Name, Marks1 AS Total FROM
Student ORDER BY Total;
mysql> SELECT Name, Marks AS Total
FROM Student
ORDER BY Total;
🞆Here, Total is the Column Alias.
57

UPDATE STATEMENT
🞆The UPDATE statement is used to update the data
of the table.
🞆WHERE clause is also applicable to this statement.
🞆Syntax: UPDATE <table_name> SET <column
name> = <value>, [ <column name> = <value>, …]
[WHERE <condition>];
Ex: UPDATE Student SET Marks = 94;
The above statement sets the Column Marks value
of all rows to 94 of the table Student.
To apply this to specific rows, WHERE clause can
be applied along with UPDATE statement.
Ex: UPDATE Student SET Marks = 94 WHERE
Name = ‘Ruskin'; 58

DELETE STATEMENT
🞆The DELETE statement is used to delete rows from
a table.
🞆DELETE removes the entire row, not the
individual column values.
🞆Syntax: DELETE FROM < tablename> [ Where
< condn>];
Ex: DELETE FROM Student WHERE Rollno =
14;
🞆DELETE statement can be used to delete all rows
of the table also. The following statement can be
used to delete all the rows from Student table.
Ex: DELETE from Student; 59

DIFFERENCE BETWEN DELETE AND
DROP
DELETE DROP
DELETE command deletes rows
from a table.
DROP command deletes all rows from a
table as well as the table structure along
with its associated metadata.
DROP is a DML command. DROP is a DDL command.
DELETE command can be roll
backed.
DROP command cannot be roll backed.
WHERE clause can be used
along with the DELETE
command.
No clause is used along with DROP
command.
With the DELETE command,
even if we delete all the rows,
space occupied by the table in
the memory is not freed.
DROP command frees the space
occupied by the table in the memory .
60

DIFFERENCE BETWEN DELETE AND
DROP
DROP TRUNCATE
DROP command deletes all rows
from a table as well as the table
structure along with its associated
metadata.
TRUNCATE command deletes all
the records of a table.
Integrity constraints will be
dropped.
Integrity constraints will not be
dropped.
61
There exist a table STUDENT. What will be the out put of the
following statement?
SELECT * FROM STUDENT;
i) After truncation.
ii) After dropping.

SQL POWERPOINT PRESENTATION ON SQL .pptx

More Related Content

Similar to SQL POWERPOINT PRESENTATION ON SQL .pptx

Recently uploaded

SQL POWERPOINT PRESENTATION ON SQL .pptx