Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    DBMS R19 UNIT 2

    Uploaded by

    Krupasagar Reddy Challa

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    DBMS R19 UNIT 2

    Uploaded by

    Krupasagar Reddy Challa
    1 views9 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    1 views9 pages

    DBMS R19 UNIT 2

    Uploaded by

    Krupasagar Reddy Challa

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 9

    UNIT-II Introduction to SQL

    Overview of the SQL Query Language


    The SQL language has several parts:
    • Data-definition language (DDL). The SQL DDL
    provides commands for defining relation schemas,
    deleting relations, and modifying relation schemas.
    • Data-manipulation language (DML). The SQL DML
    provides the ability to query information from the
    database and to insert tuples into, delete tuples
    from, and modify tuples in the database.
    Integrity. The SQL DDL includes commands for
    specifying integrity constraints that the data stored in
    the database must satisfy. Updates that violate integrity
    constraints are disallowed.
    Overview of the SQL Query Language

    • View definition. The SQL DDL includes commands


    for defining views.
    • Transaction control. SQL includes commands for
    specifying the beginning and ending of transactions.
    • Embedded SQL and dynamic SQL. Embedded and
    dynamic SQL define how SQL statements can be
    embedded within general-purpose programming
    languages, such as C, C++, and Java.
    • Authorization. The SQL DDL includes commands for
    specifying access rights to relations and views.
    SQL Data definition
    • The schema for each relation.
    • The types of values associated with each attribute.
    • The integrity constraints.
    • The set of indices to be maintained for each relation.
    • The security and authorization information for each
    relation.
    • The physical storage structure of each relation on
    disk.
    • Basic Types
    char(n): A fixed-length character string with user-specified length n.
    The full form, character, can be used instead.
    • varchar(n): A variable-length character string with user-specified
    maximum
    • length n. The full form, character varying, is equivalent.
    • int: An integer (a finite subset of the integers that ismachine
    dependent). The full form, integer, is equivalent.
    • smallint: A small integer (a machine-dependent subset of the integer
    type).
    • numeric(p, d):Afixed-point numberwith user-specified precision. The
    number consists of p digits (plus a sign), and d of the p digits are to the
    right of the decimal point. Thus, numeric(3,1) allows 44.5 to be stored
    exactly, but neither 444.5 or 0.32 can be stored exactly in a field of this
    type.
    • real, double precision: Floating-point and double-precision floating-
    point numbers with machine-dependent precision.
    • float(n): A floating-point number, with precision of at least n digits.
    • Basic Schema Definition:
    • create table department (dept name varchar (20), building
    varchar (15), budget numeric (12,2), primary key (dept
    name));
    • The general form of the create table command is:
    create table r
    (A1 D1,
    A2 D2,
    ...,
    An Dn,
    integrity-constraint1
    • ,
    • ...,
    integrity-constraintk
    );
    • primary key (Aj1 , Aj2, . . . , Ajm ):The primary-key
    specification says that attributes Aj1 , Aj2, . . . , Ajm
    form the primary key for the relation. The primarykey
    attributes are required to be nonnull and unique; that
    is, no tuple can have a null value for a primary-key
    attribute, and no two tuples in the relation can be
    equal on all the primary-key attributes.
    • foreign key (Ak1 , Ak2, . . . , Akn ) references s:
    Theforeign key specification says that the values of
    attributes (Ak1 , Ak2, . . . , Akn ) for any tuple in the
    relation must correspond to values of the primary
    key attributes of some tuple in relation s
    • Not null: The not null constraint on an attribute
    specifies that the null value is not allowed for
    that attribute;
    • insert into instructor values (10211, ’Smith’,
    ’Biology’, 66000); The values are specified in the
    order in which the corresponding attributes are
    listed in the relation schema.
    • The command
    drop table r;
    is a more drastic action than
    delete from r;
    • alter table r add AD;
    • where r is the name of an existing relation, A is
    the name of the attribute to be added, and D is
    the type of the added attribute. We can drop
    attributes from a relation by the command
    • alter table r drop A;
    • where r is the name of an existing relation, and
    A is the name of an attribute of the relation.
    Many database systems do not support
    dropping of attributes, although they will allow
    an entire table to be dropped.
    Basic Structure of SQL Queries
    • The basic structure of an SQL query consists of three clauses:
    select, from, and where. The query takes as its input the
    relations listed in the from clause, operates on them as
    specified in the where and select clauses, and then produces a
    relation as the result.
    • Queries on a Single Relation
    “select name from instructor”.

    You might also like