Introduction to DBMS and SQL

1
Introduction to DBMS and SQL

• GUIDED BY
• MR. YOGESH SAROJ (PGT-CS)
• Presented By
• JAYA XII COM
• SEEMA XII-COM.

2

• 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

3

• 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.

4

• We have so many softwares for managing Database
• DB2
• MS Access
• Fox Pro
• SQL Server
• Oracle
• My SQL

5
SQL

• SQL (Structure Query Language)
• It is relational database language that enables
  you to create and operate on relational database.

6
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.

7
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

8

• 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

9

• 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 gt 2000)

10

• 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),
• .
• )

11
Classification of SQL Commands

• DDL Commands
• DML Commands
• DCL Commands
• TCL Commands
• Query Language

12
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.

13
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 lttable_namegt
• (
• ltcol1gt ltdatatypegt ltsizegt ltconstraintgt,
• ltcol2gt ltdatatypegt ltsizegt ltconstraintgt,
• ..
• ltcolngt ltdatatypegt ltsizegt ltconstraintgt
• )
• Eg. create table emp1
• (
• empid char(4) primary key,
• ename varchar2(20) not null,
• sal number(5) check(salgt2000)
• )

14
Empid Ename Sal
E001 Smith 5000
E002 John 10000
E003 James 2500
15
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 lttable_namegt
• add (ltcol1gt ltdatatypegt ltsizegt ltconstraintgt,
• ltcol2gt ltdatatypegt ltsizegt ltconstraintgt
  .)
• Eg. alter table emp
• add (tel_number number(11) )

16
Alter table

• Modifying Column Definitions To change datatype,
  size, default value and NOT NULL column
  constraint of a column definition.
• Syntaxalter table lttable_namegt
• modify (ltcol_namegt ltnew_datatypegt ltnew_sizegt )
• Eg. alter table emp
• modify (tel_number number(13) )

17
Drop table

• Drop Table Command It removes a table from the
  database .
• Syntax
• drop table lttable_namegt
• Eg. Drop table emp

18
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.

19
Insert table

• Inserting Data into Table
• The data can be inserted in a table using Insert
  Into command.
• Syntax insert into lttable_namegt ltcolumn_listsgt
• values (ltvalue1gt, ltvalue2gt, .)
• 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.
• Or 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.

20
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 lttable_namegt
• 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.

21
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 lttable_namegt
• set col1 ltnew_valuegt,
• col2 lt new_valuegt,
• ..coln ltnew_valuegt
• where ltconditiongt
• Eg. update emp
• set sal 400 updates all rows
• Eg. update emp
• set sal sal2, ename JONES
• where empno 7844 update only one
  row

22
Delete Command

• This is also a DML statement used to remove
  row(s) of a table.
• Syntax delete from lttable_namegt
• where ltconditiongt
• Eg. delete from emp
• where sal lt 5000

23
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 ltpermissionsgt select, insert,
  delete, update
• on ltobject_namegt
• to ltusernamegt
• Eg. grant insert on emp to user1 only user1
  can insert
• grant all on emp to public assign all
  permissions to all users.
• Revoking Privileges It get back permissions from
  the users.
• Syntax revoke ltpermissiongt on ltobject_namegt from
  ltusernamegt
• Eg. revoke all on emp from user1 get back all
  permissions from user1.
• revoke select on emp from public get back
  select permission from all users.

24
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.

25
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

26
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 ltcolumn_listgt
• From lttable_namegt
• Where ltconditiongt
• Group By ltlist_of_column(s)gt
• Having ltsearch_conditiongt Having is dependent
  upon Group By
• Order By ltcolumn_namegt

27
Select Command

• Select from emp
• Select 23 from dual
• Select empid, ename from emp
• Select from emp where enamesmith
• Select distinct sal from emp
• Select job from emp where deptno (select deptno
  from emp where enameSMITH)

28
Select Command

• Eg. select sum(sal), deptno
• from emp
• group by deptno
• Output SUM(SAL) DEPTNO
• --------- -------------------
• 8750 10
• 10875 20
• 9400 30

29
Select Command

• Eg. select sum(sal), deptno
• from emp
• group by deptno
• having deptno 30
• Output SUM(SAL) DEPTNO
• --------
  --------------------
• 9400
  30

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

31
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?

32
Questions

• Ques4. What is the output of following query?
• select 23 from emp
• Ques5. Give three application areas of Database.
• Ques 6 What is the difference b/w unique and
  primary key constraint?

33

• Queries?

34

• Thanks