Powered by Blogger.

Saturday, February 15, 2014

Tutorial on SQL DML and DDL statements

1.SQL is divided into two parts:  Data Manipulation Language (DML) and  Data Definition Language (DDL).

2.The query and update commands form the DML part of SQL:

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database

3.The DDL of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables.

The most important DDL statements in SQL are:

CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

4.SQL Select Statements:The SELECT statement is used to select data from a database.
 SELECT column_name(s) FROM table_name --select the content of the columns from the table.
 SELECT * FROM table_name -- select all the columns from the  table.
(asterisk (*) is a quick way of selecting all columns.)

5.SELECT DISTINCT Statement:DISTINCT keyword can be used to return only distinct values.
  SELECT DISTINCT column_name(s)FROM table_name -- select only the distinct column values from the table.

5.SQL WHERE Clause:WHERE clause is used to extract only those records which fulfill a specic criterion.
 SELECT column_name(s)FROM table_nameWHERE column_name operator value

6.Operators used in WHERE Clause:

With the WHERE clause, the following operators can be used:

 Operator Description
  =          Equal
  <>         Not equal
  >        Greater than
  <         Less than
  >=        Greater than or equal
  <=         Less than or equal
 BETWEEN Between an inclusive range
 LIKE         Search for a pattern
  IN      To specify multiple possible values for a column

7.SQL AND & OR Operators:The AND & OR operators are used to filter records based on more than one condition.
     i.The AND operator displays a record if both the first condition and the second condition are true.
             Ex:SELECT * FROM Customers WHERE FirstName='XX'AND LastName='YY'
     ii.The OR operator displays a record if either the first condition or the second condition is true.

             Ex:SELECT * FROM Customers WHERE FirstName='xx'OR FirstName='yy'

   Combining AND & OR:
             EX:SELECT * FROM Persons WHERE LastName='yy' AND (FirstName='zz' OR FirstName='xx')

8.SQL ORDER BY Keyword:ORDER BY keyword is used to sort the result-set by a specified column.

       SELECT column_name(s)FROM table_nameORDER BY column_name(s) ASC|DESC

   i.The ORDER BY keyword sorts the records in ascending order by default.
         Ex:SELECT * FROM Customers ORDER BY LastName

   ii.If we want to sort the records in a descending order,then we need to use the DESC keyword.

         Ex:SELECT * FROM Customers ORDER BY LastName DESC

9.SQL INSERT Statement:The INSERT INTO statement is used to insert a new row in a table.


The Insert Into statement can be written as follows:

   i.The first form doesn't specify the column names where the data will be inserted, only their values:
           Ex:INSERT INTO table_name VALUES (value1, value2, value3,...)
   ii.The second form specifies both the column names and the values to be inserted:

           Ex:INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

10.SQL UPDATE Statement:The UPDATE statement is used to update existing records in a table.


           Ex:UPDATE table_name SET column1=value, column2=value2,...WHERE some_column=some_value

11.SQL DELETE Statement:The DELETE statement is used to delete rows in a table.


           Ex:DELETE FROM table_name WHERE some_column=some_value

12 Deleting All Rows:We can  delete all rows in a table without deleting the table i.e. the table structure, attributes, and indexes .

           Ex:DELETE FROM table_name


              DELETE * FROM table_name