Introduction to DBMS and SQL - PowerPoint PPT Presentation

Loading...

PPT – Introduction to DBMS and SQL PowerPoint presentation | free to download - id: 5a3775-MGZlN



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Introduction to DBMS and SQL

Description:

We have so many softwares for managing ... Null Constraint: It means a Unknown Value. Eg. mobile ... The new column will be added with NULL values for all rows ... – PowerPoint PPT presentation

Number of Views:21
Avg rating:3.0/5.0
Slides: 35
Provided by: HCL60
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
About PowerShow.com