Transcript My Presentation - Kendriya Vidyalaya Nasirabad

Introduction to DBMS and SQL
GUIDED BY :
MR. YOGESH SAROJ (PGT-CS)
Presented By :
JAYA XII –COM
 SEEMA XII-COM.

Data Base Management System (DBMS)

Data:- Data means raw fact.

Database :- A collection of meaningful information.
Eg. Telephone directory.

Data Base Management System:-The collection of interrelated data
containing information about one particular field & set of programs to
access those data.






Manage the whole data in perfect manner for following reasons:
User can search information easily
User can insert data easily in order
Updating easily
Deleting information easily
Save data permanently
Advantage of DBMS





1. It reduces data redundancy.
2. It controls data inconsistency to a large
extent.
3. It restricted unauthorized access.
4. It facilitates sharing of data.
5. It provides to describe backup &
recovery.







We have so many softwares for managing
Database:
DB2
MS Access
Fox Pro
SQL Server
Oracle
My SQL
SQL

SQL (Structure Query Language)
It is relational database language that enables
you to create and operate on relational
database.
Feature of SQL



 It is a non procedural language.
 It is a 4GL programming language.
(i.e only What to do? not How to do?).
 It is a case insensitive language.
Constraints of SQL

A constraint is a condition or check that is applied to a
column or set of columns in a table.

Null Constraint: It means a Unknown Value.
Eg. mobile number(10) null

Not Null Constraint: It means always a Known
Value.
Eg. name varchar2(20) not null

Unique Constraint: It ensures that no two rows have
the same value in the specified column(s). i.e Known
Value (Distinct) or Unknown Value.
Eg. ecode number(5) unique

Primary Key Constraint: It is similar to Unique
constraint except that the Primary Key can not allow
Null values so that this constraint must be applied to
columns declared as Not Null. i.e Always Known
Value (Distinct).
Eg. empid char(5) primary key

Default Constraint: A default value can be
specified for a column using default clause
when a user does not enter a value for that
column.
Eg. grade char(2) default= ‘E1’

Check Constraint: It limits values that can be
inserted into a column.
Eg. sal number(10) check(sal > 2000)














Foreign Key Constraint: Whenever two tables are related by a common
column then Foreign Key is present in the Child table (Related Table or
Detail Table) and it is derived from primary key of Parent Table (Primary
Table or Master Table).
Eg. Two Tables:
Items (Itemno, Description, Price)
Orders (Orderno, Orderdate, Itemno, Qty)
where Itemno & Orderno are Primary Key and Itemno is Foreign Key. i.e
both the tables are related through common column Itemno.
Note: It may be possible that Primary Key and Foreign Key are same.
Eg.
create table Items
( Itemno char(5) Primary Key,
…………….);
create table Orders
( Orderno number(5) Primary Key,
Itemno char(5) references Items(Itemno),
…………….
);
Classification of SQL Commands





DDL Commands
DML Commands
DCL Commands
TCL Commands
Query Language
DDL Commands


DDL (Data Definition Language): It provides
commands for defining various database
objects (i.e defining relation schemas, deleting
relations, creating indexes, and modifying
relation schemas etc.)
Eg. Create, Alter, Drop etc.
Create Command














 The tables are created by using Create Table command and also its columns are
named, data types and sizes are supplied for each column.
Syntax:
create table <table_name>
(
<col1> <datatype> [<size>] [<constraint>],
<col2> <datatype> [<size>] [<constraint>],
………..
<coln> <datatype> [<size>] [<constraint>]
);
Eg.
create table emp1
(
empid char(4)
primary key,
ename varchar2(20) not null,
sal number(5) check(sal>2000)
);
Empid
Ename
Sal
E001
Smith
5000
E002
John
10000
E003
James
2500
Alter Command







 Altering Table: The alter table command is used to modify
the structure of existing table. (i.e adding a column, add an
integrity constraint etc.).
Adding Columns: The new column will be added with NULL
values for all rows currently in table.
Syntax:
alter table <table_name>
add (<col1> <datatype> <size> [<constraint>],
<col2> <datatype> <size> <constraint>]
…….);
Eg.
alter table emp
add (tel_number number(11) );
Alter table





Modifying Column Definitions: To change
datatype, size, default value and NOT NULL
column constraint of a column definition.
Syntax:alter table <table_name>
modify (<col_name> <new_datatype>
<new_size> );
Eg.
alter table emp
modify (tel_number number(13) );
Drop table

Drop Table Command: It removes a table
from the database .
Syntax:
drop table <table_name>;

Eg. Drop table emp;


DML


(Data Manipulation Language): It enables
users to manipulate data (i.e commands to
insert, delete, and modify tuples in the
database).
Eg. Insert, Update, Delete etc.
Insert table

Inserting Data into Table
The data can be inserted in a table using Insert Into command.
Syntax:
insert into <table_name> [<column_lists>]
values (<value1>, <value2>, …………….);

Eg.





insert into emp1
values(‘E001’,’Vipin’,5000);
Note:
Here the order of values matches the order of
columns in the create table command of the table.
insert into emp1 (empid, ename, sal)

values(‘E001’,’Vipin’,5000);
Note: The columns not listed in the insert into command will
have their default values or null values.

Or
Insert Table









Mass Level Data Insertion
If you want to insert data from user online then you can use insertion
through substitution of parameters.(i.e & as substitution operator or place
holder).
Syntax:
insert into <table_name>
values(‘&col1’,’&col2’,………);
Note: The parameters for character values are enclosed in single quote.
Eg. insert into emp1
values(‘&empid’,’&ename’,&sal);
It will asked from you to Enter value for empid, Enter value for ename and
Enter value for sal.
Then a message display that 1 row created.
Update table












 Modifying Data with Update Command
This is a DML statement used to modify or change some or all of the values
in an existing row of a table.
Syntax:
update <table_name>
set col1 = <new_value>,
col2 = < new_value>,
…..coln = <new_value>
[where <condition>];
Eg. update emp
set sal= 400;
‘updates all rows
Eg. update emp
set sal= sal*2, ename= ‘JONES’
where empno = 7844;
‘update only one row
Delete Command

This is also a DML statement used to remove
row(s) of a table.
Syntax: delete from <table_name>
[where <condition>];

Eg.



delete from emp
where sal < 5000;
DCL (Data Control Language)
Commands

The rights or permissions assigned to user(s) to use some or all of Oracle objects are
known as privileges.


Granting Privileges: It is used to assigning permissions to users. (Only DBA can
assign)
Syntax:
grant <permissions>
‘select, insert, delete, update
on <object_name>
to <username>;

Eg.



grant insert on emp to user1;
grant all on emp to public;
‘only user1 can insert
‘assign all permissions to all users.

Revoking Privileges: It get back permissions from the users.
Syntax:
revoke <permission> on <object_name> from <username>;

Eg.


revoke all on emp from user1;
revoke select on emp from public;
‘get back all permissions from user1.
‘get back select permission from all users.
TCL


(Transaction Control Language): It controls
over transaction processing by specifying the
beginning and ending of transactions.
Eg. Commit, Rollback, Rollback to, Save point
etc.
TCL Commands

Oracle treat a transaction as a single entity & incase of successful
termination of transaction the changes are made permanent. The
commands used with transactions are:

COMMIT: It ends the current transaction by saving database changes &
starts a new transaction.
Eg. commit;
‘i.e end or start a transaction






ROLLBACK: It ends the current transaction by discarding database
changes & starts a new transaction.
Eg. rollback;
‘i.e undo upto commit
SAVEPOINT: It defines breakpoints or bookmarks for the transaction to
allow partial rollbacks.
Eg. savepoint P1;
ROLLBACK TO: Its undo up to given bookmark or breakpoint.
Eg. rollback to P1;
Query Language









Query language : The Select command of SQL make queries
on the database i.e it is given to produce certain specified
information from the database table(s).
There are various ways and combinations to use a select
statement.
The complex syntax of SQL Select command is as given below:
Select <column_list>
From <table_name>
Where <condition>
Group By <list_of_column(s)>
Having <search_condition> ‘Having is dependent upon Group By
Order By <column_name>;
Select Command






Select * from emp;
Select 2+3 from dual;
Select empid, ename from emp;
Select * from emp where ename=‘smith’;
Select distinct sal from emp;
Select job from emp where deptno= (select
deptno from emp where ename=‘SMITH’);
Select Command







Eg. select sum(sal), deptno
from emp
group by deptno;
Output: SUM(SAL) DEPTNO
--------- ------------------8750
10
10875
20
9400
30
Select Command







Eg.
select sum(sal), deptno
from emp
group by deptno
having deptno= 30;
Output:
SUM(SAL) DEPTNO
-------- -------------------9400
30
Select Command









Eg. select sum(sal), deptno
from emp
group by deptno
order by deptno desc;
Output:
SUM(SAL) DEPTNO
--------- -------------------9400
30
10875
20
8750
10
Questions

Ques1. What is difference b/w having clause and
where clause in select query?

Ques2. What is difference b/w delete and drop
command?

Ques3. Write a query to select second largest salary
from emp table?
Questions

Ques4. What is the output of following query?
select 2+3 from emp;

Ques5. Give three application areas of
Database.

Ques 6: What is the difference b/w unique and
primary key constraint?
Queries?
Thanks