Recent Post

SQL

Introduction
SQL stands for structure query language.SQL lets you access and manipulate database and an ANSI (American National Standards Institute) standard.

Functions of SQL
SQL can execute queries against a database.
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records in a database
SQL can create new database
SQL can create new tables in a database
SQL can create stored procedure in a database
SQL can create views in a database
SQL can set  permissions on tables,procedures and views

RDBMS

RDBMS stands for Relational Database Management System.It is the bases for SQL, and for all modern database system such as MS SQL server, IBM DB2, Oracle, MySQL and Microsoft Access.The data in RDBMS is stored in database objects callled tables.A table is a collection of related data entries and it consists of columns and rows.





Type of  SQL

Two type of SQL
DML: Data Manipulation Language (SELECT)
DDL: Data Definition Language (CREATE TABLE)

SQL COMMANDS

SELECT Statement
   (i) SELECT statement defines WHAT is to be returned (separated by commas)
   (ii) "*" mean all columns from all tables in the FROM statement.

FROM Statement:
   (i) Defines the table(s) or view(s) used by the SELECT or WHERE statements.
   (ii) Multiple Tables/Views are separated by commas.

WHERE Clause: 
   (i) Defines what records are to be included  in the query . it is optional.
   (ii) Uses conditional operates.
         (a) = , > , >= , <= , !=(< >)
         (b) BETWEEN x AND y
         (c) IN (list)
         (d) LIKE ' % string' (" % " is a wild-card)
         (e) IS NULL
         (f) NOT ( BETWEEN / LIKE / NULL)

SELECT DISTINCT Statement
  (i) The SELECT DISTINCT statement is used to return only distinct ( different ) values.
  (ii) In a table, a column may contains many duplicate values; and sometimes you only want to list the different ( distinct ) values.
  (iii) Syntax: SELECT DISTINCT column_name , column_name FROM table_name;

AND & OR;
   (i) "AND" means all conditions are TRUE for the records
   (ii) " OR" means at least one of the conditions is TRUE.
   (iii) Multiple WHERE conditions are linked by AND/OR statement

ORDER statement:
    (i) Defines how the records are to be sorted
    (ii) Must be in the SELECT statement to be ORDER BY
    (iii) Default is to order in ASC (Ascending ) order. To sort the records in a descending order, use the DESC keyword.
    (iv) Syntax: SELECT column_name , column_name FROM table_name ORDER BY column_name , column ASC/DESC;

Group Function:
    (i) Performs common mathematical operations on a group of records.
    (ii) Must define what constitutes a group by using the GROUP BY clause.
    (iii) All non-group element in the SELECT statement must be in the GROUP BY clause

INSERT INTO statement:
     (i) Used to insert new records in a table.
     (ii) Syntax: INSERT INTO table_name VALUES (value 1 , value 2 ,.....);
                                                      or
        INSERT INTO table_name(column 1 , column 2 ,.....) VALUES ( value 1 , value 2 ,.....);

UPDATE statement: 
      (i) Used to update records in a table
      (ii) Syntax: UPDATE table_name SET column1 = value 1 , column 2 = value 2,....WHERE some_column = some_value

DELETE statement
   (i) Used to delete rows in table
   (ii) Syntax : DELETE FROM table_name WHERE some_column = some_value;

LIKE operator: 
    (i) The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
    (ii) Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
  
IN operator:
    (i) The IN operator allows you to specify multiple values in a WHERE clause.
    (ii) Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1 , value2,.....)

BETWEEN operator:
     (i) It selects value within a range. The values can be numbers , text or dates.
     (ii) Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value 1 and values 2;

Aliases:
    (i) SQL aliases are used to temporarily rename a table or a column heading.
    (ii) SQL aliases are used to give a database table , or a column in a table , a temporary name.
    (iii) Basically aliases are created to make column names more readable.
     (iv) Syntax: SELECT column_name AS alias_name FROM table_name AS alias_name.

No comments