Introduction:-
SQL stand for structured query language i.e. a language that is used to
query from database and is supported by every database. This tutorial will give
complete reference about SQL and complex queries.
It is used to store, manipulate and retrieve the data in RDBMS. It is
standard language for all Relational databases.
SQL Command:-
These are the codes which defines type of action on database like insert,
update, delete etc.
SQL command can be classified into five groups:-
DDL (Data Definition language):-Creates or modifies any object like tables,
views, synonyms etc. in database e.g. create, alter, truncate, drop.
DML (Data Manipulation Language):- Make changes in Data in RDBMS e.g.
insert, update, delete.
DCL (Data Control Language):- It is used to give privileges or take it back
from users e.g. grant, revoke.
DQL (Data Query language):- It is used to retrieve data from database e.g.
select.
TCL (Transaction Control Language):- COMMIT and ROLLBACK commands comes in
this section as former is used to make the changes permanent and later erases
all unsaved data before commit.
Note: - Commit and Rollback can only affect the changes done by DMLs.
RDBMS:-
It is Relational Database Management System. It is Database Management
System which follows relational model given by E.F. Codd.
For example:- Oracle, MySQL, MS SQL Server, IBM DB2 etc.
Some Important definition:-
Table:- It is collection of related data stored in rows and columns. It is
the base of RDBMS.
Field:- It is the smallest unit of table. It contains particular
information of the record e.g. name varchar2
(50) in the table employee.
Column:-It is vertical part of the table which contains all the values of
particular field.
Row:-It shows one record of the table and also the horizontal part.
Create table is a command that is used to create table, view, triggers,
procedures, functions and other database objects in the database. We will
discuss every this one by one.
Create Table:-
Code:-
Create table table_name (column_name
datatype(size));
Example:- create table students (name varchar2(50),code number(4), address
varchar2(100), class varchar2(10));
The above code will create a table named students having four columns as name, code, address, class.
Insert Command:-
Insert command is used to insert a new record in the table.
Code:- insert into students(name, address, code, class) values
(‘Sandeep’,1,’Sec 65 Noida’, ’Tenth’).
Insert into students (name,
address, code) values (‘Hari’,2,’NSP New Delhi’);
In the above code the previous line will insert a record in all the field
and the later one will insert in the three field given before values.
The sequence of field name and values should follow the same order so that
it can match the data.
#Note:- commit is necessary after all the DML commands otherwise the data
will remain unchanged.
Select Command:-
Select command is used to retrieve the data from the table. The syntax of
the select command is as follows:-
Select * from students;
It will display all the data of the table as for now it has only one row so
it will display:-
Sandeep
|
1
|
Sec 65 Noida
|
Tenth
|
Hari
|
2
|
NSP New Delhi
|
|
To select particular column we can use it like:-
Select name from students; Result:-
Where Command:-
Where command is used to filter the result of select command based on
condition provided, it makes our result precise and narrow. The syntax is as
follows:-
Select name from students where code=1;
Result:-Sandeep.
Select name from students where code=2;
Result:-Hari.
Select address from students where code=5;
Result:- null. (Reason there is no record the table with the code=5.)
#Note:- null is unknown value to database.
AND Command:-
And command is used to with where command when two or more condition is used.
The syntax is as follows:-
Select name from students where code=1 and class=’Tenth’;
Result:-Sandeep.
You can see above we have two conditions first code=1 and second
class=’Tenth’. Both of the condition must be true to display the result. If we
take an example as:-
Select name from student where code=2 and class=’Tenth’;
Result:-null; (Because both conditions are not satisfied.)
