DCL command
Data Control Language(DCL) is used to control privilege in Database. To
perform any operation in the database, such as for creating tables,
sequences or views we need privileges. Privileges are of two types,
System : creating session, table etc are all types of system privilege.
Object : any command or query to work on tables comes under
object privilege.
DCL defines two commands,
Grant : Gives user access privileges to database.
Revoke : Take back permissions from user.
WHERE clause
Where clause is used to specify condition while retriving data from
table. Where clause is used mostly withSelect, Update and Delete query. If
condititon specified by where clause is true then only the result from table
is returned.
SELECT Query
Select query is used to retrieve data from a tables. It is the most used SQL
query. We can retrieve complete tables, or partial by mentioning conditions
using WHERE clause.
Syntax of SELECT Query
SELECT column-name1, column-name2, column-name3, columnnameN from table-name;
Like clause
Like clause is used as condition in SQL query. Like clause compares data
with an expression using wildcard operators. It is used to find similar data
from the table.
Wildcard operators
There are two wildcard operators that are used in like clause.
Percent sign % : represents zero, one or more than one character.
Underscore sign _ : represents only one character.
Order By Clause
Order by clause is used with Select statement for arranging retrieved data
in sorted order. The Order byclause by default sort data in ascending
order. To sort data in descending order DESC keyword is used withOrder
by clause.
Syntax of Order By
SELECT column-list|* from table-name order by asc|desc;
Group By Clause
Group by clause is used to group the results of a SELECT query based on
one or more columns. It is also used with SQL functions to group the result
from one or more tables.
Syntax for using Group by in a statement.
SELECT column_name, function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING Clause
having clause is used with SQL Queries to give more precise condition for
a statement. It is used to mention condition in Group based SQL functions,
just like WHERE clause.
Syntax for having will be,
select column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition
Distinct keyword
The distinct keyword is used with Select statement to retrieve unique
values from the table. Distinctremoves all the duplicate records while
retrieving from database.
Syntax for DISTINCT Keyword
SELECT distinct column-name from table-name;
AND & OR operator
AND and OR operators are used with Where clause to make more precise
conditions for fetching data from database by combining more than one
condition together.
AND operator
AND operator is used to set multiple conditions with Where clause.
OR operator
OR operator is also used to combine multiple conditions
with Where clause. The only difference between AND and OR is their
behaviour. When we use AND to combine two or more than two conditions,
records satisfying all the condition will be in the result. But in case of OR,
atleast one condition from the conditions specified must be satisfied by any
record to be in the result.
DML command
Data Manipulation Language (DML) statements are used for managing
data in database. DML commands are not auto-committed. It means
changes made by DML command are not permanent to database, it can be
rolled back.
1) INSERT command
Insert command is used to insert data into a table. Following is its general
syntax,
INSERT into table-name values(data1,data2,..)
2) UPDATE command
Update command is used to update a row of a table. Following is its
general syntax,
UPDATE table-name set column-name = value where condition;
3) Delete command
Delete command is used to delete data from a table. Delete command can
also be used with condition to delete a particular row. Following is its
general syntax,
DELETE from table-name;
Introduction to SQL
Structure Query Language(SQL) is a programming language used for
storing and managing data in RDBMS. SQL was the first commercial
language introduced for E.F Codd's Relational model. Today almost all
RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) uses SQL as the
standard database language. SQL is used to perform all type of data
operations in RDBMS.
SQL Command
SQL defines following data languages to manipulate data of RDBMS.
DDL : Data Definition Language
All DDL commands are auto-committed. That means it saves all the
changes permanently in the database.
Command
Description
create
to create new table or database
alter
for alteration
truncate
delete data from table
drop
to drop a table
rename
to rename a table
DML : Data Manipulation Language
DML commands are not auto-committed. It means changes are not
permanent to database, they can be rolled back.
Command
Description
insert
to insert a new row
update
to update existing row
delete
to delete a row
merge
merging two rows or two tables
TCL : Transaction Control Language
These commands are to keep a check on other commands and their affect
on the database. These commands can annul changes made by other
commands by rolling back to original state. It can also make changes
permanent.
Command
Description
commit
to permanently save
rollback
to undo change
savepoint
to save temporarily
DCL : Data Control Language
Data control language provides command to grant and take back authority.
Command
Description
grant
grant permission of right
revoke
take back permission.
DQL : Data Query Language
Command
Description
select
retrieve records from one or more table
