types and details of SQL Statements

SQL provides set of commands for a variety of tasks including the following:

• Querying data
• Updating, inserting and deleting data
• Creating, modifying and deleting database objects
• Controlling access to the database
• Providing for data integrity and consistency.

SQL statements are divided into the following categories

•  
• Data Definition Language(DDL)
• Data Manipulation Language(DML)
• Data Query Language (DQL)
• Data Control Language (DCL)

1) Data Definition Language(DDL)

  This is used to create, alter and delete database objects

  The commands used are create, alter and drop

•   Create table, create view, drop index 
•   Alter table
•   Drop table, drop view, drop index

2) Data Manipulation Language(DML)

  Data manipulation language commands let users insert, modify and delete the data in the database. SQL provides three data manipulation statements insert, update and delete

3) Data Query Language (DQL)

  This is one of the most commonly used SQL statements. This SQL statement enables the users to query one or more tables to get the information they want. SQL has only one data query statement 'select.'

4) Data Control Language (DCL)

  The data control language consists of commands that control the user access to the database objects. Various DCL commands are : Commit, Rollback, Save point, Grant, Revoke.

spP � b p � �� time stamp '2005-04-25 08:25:30.45'

  Dates must be specified in the format-year followed by month followed by day, as shown. The seconds field of time or timestamp can have a fractional part, as in the timestamp above

SQL datatypes

Data Types:-

  Data types are a classification of a particular type of information. It is easy for humans to distinguish between different types of data. SQL supports following data types:

1)Character(n)

     This data type represents a fixed length string of exactly 'n' characters where 'n'is greater than zero and should be an integer.

Example:-

    Name character(10)

            or

    Name char(10)

2) Varchar(n) or character varying(n)

    This data type represents a varying length string whose maximum length is 'n' characters.

  Example:-

      Name varchar(n)

3) Numeric (p,q)

     This data type represent a decimal number 'p' digits and sign with assumed decimal point 'q' digits from the sign. Both 'p' and 'q' are integers.

  Example:-

    Price numeric(6,2)

4)Integer

    An integer represents a signed integer decimal or binary.

    Example:-

       Roll_No integer(3)

5) Small int

    A small integer is a machine independent subset of the integer domain type

  Example:-

   Roll_No small int(3)

6) Real, double precision

  Floating point and double-precision floating point numbers with machine dependent precision.

7) Float(n)

   A floating point number, with precision of at least n digits.

   Example:-

     Rate float(5,2)

8) Date

   A calendar date containing a (four-digit) year, month and day of the month

 Example:-

     Date_of_birth date

9) Time

   The time of day, in hours, minutes and seconds. A variant, time(p) can be used to specify the number of fractional digits for seconds (the default being 0)

 Example:-

  Arrival_time time

10) Time stamp

    A combination of date and time. A variant, timestamp (P), can be used to specify the number of fractional digits for seconds.

   Date and time values can be specified like this:

      date '2005-04-25'

      time '09:10:25'

      time stamp '2005-04-25 08:25:30.45'

  Dates must be specified in the format-year followed by month followed by day, as shown. The seconds field of time or timestamp can have a fractional part, as in the timestamp above

Structured Query Language-definition of SQL

Structured Query Language (SQL):-

     Structured Query Language (SQL) is the standard command set used to communicate with the relational database management systems. All tasks related to relational data management creating tables, querying the database for information, modifying the data in the database, deleting them, granting access to users and so on can be done using SQL.

Characteristics of SQL:-

    SQL usage by its very nature is extremely flexible. It uses a free form syntax that gives the user the ability to structure SQL statements in a way best suited to him. Each SQL statements in a particular column or be finished in a single line. The same SQL request can be written in a variety of ways.

Advantages of SQL:-

     !) SQL is a high level language that provides a greater degree of abstraction than procedural languages.

    !!) SQL enables the end-users and systems personnel to deal with a number of database management systems where it is available. Increased acceptance and availability of SQL are also in its favor.

    !!!)Applications written in SQL can be easily ported across systems. Such porting could be required when the underlying DBMS needs to be upgraded or changed.

    !V) SQL specifies what is required and not how it should be done.