Docstoc

Structured Query Language sql tutorial 1

Document Sample
Structured Query Language sql tutorial 1 Powered By Docstoc
					Structured Query Language
           (SQL)

                       Asif Sohail
                    University of the Punjab
  Punjab University College of Information Technology (PUCIT)




                                                                Slide 1
Introduction

● SQL pronounced as Sequel is a language for RDBMS or
  SQL makes RDBMS possible.
● It was first defined by Chamberlin and others at IBM’s
  research laboratory in San Jose, California (Late 1970’s)
● ANSI and ISO have worked for the standardization of SQL.
● SQL is a non procedural language.
● SQL does much more and is more powerful than just asking
  queries from the database.
● Using SQL, one can:




                                                       Slide 2
Introduction
 ● Create Relations or Tables.
 ● Insert, Delete and Update Records.
 ● Apply Validation Checks.
 ● Create users.
 ● Apply Security Restrictions etc. etc.




                                           Slide 3
Communicating with a RDBMS Using SQL


     SQL statement
     is entered          Statement is sent to
      SQL> SELECT loc         database
      2 FROM dept;


                                                Databas
                                                e


     Data is displayed
      LOC
      -------------
      NEW YORK
      DALLAS
      CHICAGO
      BOSTON




                                                          Slide 4
SQL and SQL Plus Interaction

           SQL                        SQL
        Statements       Buffer    Statements


                                            Serve
                                              r
   SQL*Plus




          SQL*Plus                 Query
         Commands                 Results
                     Formatted
                      Report


                                                    Slide 5
Logging In to SQL Plus
  ● SQL Plus is an environment that enables you to
    enter and execute SQL commands.
  ● The Log on screen is:




                                                     Slide 6
SQL Statements

      SELECT      Data retrieval
      INSERT
      UPDATE      Data manipulation language
      DELETE      (DML)

      CREATE
      ALTER
                  Data definition language (DDL)
      DROP
      RENAME
      TRUNCATE

                  Transaction
      COMMIT
                  control
      ROLLBACK
      SAVEPOINT
                  Data control language
                  (DCL)
      GRANT
                                                   Slide 7
 Tables Used in the Course
       EMP
         EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
         --------- ---------- --------- --------- --------- --------- --------- ---------
         7839 KING PRESIDENT 17-NOV-81 5000 10
         7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
         7782 CLARK MANAGER 7839 09-JUN-81 2450 10
         7566 JONES MANAGER 7839 02-APR-81 2975 20
         7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
         7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
         7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
         7900 JAMES CLERK 7698 03-DEC-81 950 30
         7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
         7902 FORD ANALYST 7566 03-DEC-81 3000 20
DEPT SMITH CLERK 7902 17-DEC-80 800 20
         7369
         7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
DEPTNO DNAME LOC
         7876 ADAMS CLERK 7788 12-JAN-83 1100 20
---------7934 MILLER CLERK 7782 23-JAN-82 1300 10
          -------------- ---------- 10 ACCOUNTING                                           SALGRADE
NEW YORK
20 RESEARCH DALLAS                                                                          GRADE LOSAL HISAL
30 SALES CHICAGO                                                                            --------- --------- ---------
40 OPERATIONS BOSTON                                                                        1 700 1200
                                                                                            2 1201 1400
                                                                                            3 1401 2000
                                                                                            4 2001 3000
                                                                                            5 3001 9999




                                                                                                                            Slide 8
Querying the Tables
  ● The Select statement is the building block for querying the
    tables:
  ● Syntax:
SELECT [DISTINCT] {* | column [alias],...}
FROM table (s)
[WHERE clause]
[ORDER BY clause]
[GROUP BY clause]
[HAVING clause]




                                                             Slide 9
Capabilities of SQL SELECT Statements

      Selection             Projectio
                            n



      Table 1               Table 1
                    Joi
                    n



      Table 1               Table 2

                                        Slide 10
Writing SQL Statements


    ● SQL statements are not case sensitive.
    ● SQL statements can be on one or
      more lines.
    ● Keywords cannot be abbreviated or split across
      lines.
    ● Clauses are usually placed on separate lines.
    ● Tabs and indents are used to enhance
      readability.




                                                       Slide 11
Writing SQL Statements
● Examples (Querying emp table):
   ○ Select * from emp;
   ○ Select empno from emp;
   ○ Select empno,ename,job from emp;
   ○ Select ename,sal+100 from emp;
   ○ Select ename, sal*2/100 “Tax” from emp;
   ○ Select empno,ename,job,sal,sal*12 “Annual Sal” from
     emp;
   ○ Select distinct job from emp;




                                                      Slide 12
Selecting All Columns


   SQL> SELECT *
   2 FROM dept;


   DEPTNO DNAME LOC
   --------- -------------- -------------
   10 ACCOUNTING NEW YORK
   20 RESEARCH DALLAS
   30 SALES CHICAGO
   40 OPERATIONS BOSTON




                                            Slide 13
Selecting Specific Columns


   SQL> SELECT deptno, loc
   2 FROM dept;


   DEPTNO LOC
   --------- -------------
   10 NEW YORK
   20 DALLAS
   30 CHICAGO
   40 BOSTON




                             Slide 14
Editing SQL Statements
● To edit the last SQL statement and then execute it, types
  ed at SQL prompt and press Enter Key.
● Notepad will be opened with the last SQL statement
  written in it.
● Edit and statement, save it and exit from the notepad.
● At SQL prompt, type / and press enter to execute the
  statement.




                                                         Slide 15
Arithmetic Expressions

   ● Create expressions on NUMBER and DATE
     data by using arithmetic operators.

         Operator   Description
            +       Add
             -      Subtract
             *      Multiply
             /      Divide




                                         Slide 16
Using Arithmetic Operators

   SQL> SELECT ename, sal, sal+300
   2 FROM emp;


   ENAME SAL SAL+300
   ---------- --------- ---------
   KING 5000 5300
   BLAKE 2850 3150
   CLARK 2450 2750
   JONES 2975 3275
   MARTIN 1250 1550
   ALLEN 1600 1900
   ...
   14 rows selected.




                                     Slide 17
Operator Precedence
                                  _
                   * / +
    ● Multiplication and division take priority over
      addition and subtraction.
    ● Operators of the same priority are evaluated from
      left to right.
    ● Parentheses are used to force prioritized
      evaluation and to clarify statements.




                                                          Slide 18
Defining a Column Alias


    ● Renames a column heading
    ● Is useful with calculations
    ● Immediately follows column name; optional AS
      keyword between column name and alias
    ● Requires double quotation marks if it contains
      spaces or special characters or is case sensitive




                                                          Slide 19
Using Column Aliases
   SQL> SELECT ename AS name, sal salary
   2 FROM emp;


   NAME SALARY
   ------------- ---------
   ...



   SQL> SELECT ename "Name",
   2 sal*12 "Annual Salary"
   3 FROM emp;


   Name Annual Salary
   ------------- -------------
   ...



                                           Slide 20
Defining a Null Value
  ● A null is a value that is unavailable, unassigned,
    unknown, or inapplicable.
  ● A null is not the same as zero or a blank space.


    SQL> SELECT ename, job, comm
    2 FROM emp;

   ENAME JOB COMM
   ---------- --------- ---------
   KING PRESIDENT
   BLAKE MANAGER
   ...
   TURNER SALESMAN 0
   ...
   14 rows selected.


                                                         Slide 21
Null Values in Arithmetic Expressions

● Arithmetic expressions containing a null value evaluate to
  null.
   SQL> select ename, 12*sal+comm
   2 from emp;



   ENAME 12*SAL+COMM
   ---------- -----------
   KING

To avoid the above, use nvl function.
  ENAME 12*SAL+ NVL(COMM,0)
  ---------- -------------------
  KING 5000



                                                          Slide 22
Concatenation Operator
  ● Concatenates columns or character strings to other
    columns
  ● Is represented by two vertical bars (||)
  ● Creates a resultant column that is a character expression


   SQL> SELECT ename||job AS "Employees"
   2 FROM emp;




                                                          Slide 23
Literal Character Strings
 ● A literal is a character, expression, or number included in
   the SELECT list.
 ● Date and character literal values must be enclosed within
   single quotation marks.
 ● Each character string is output once for each row
   returned.

   SQL> SELECT ename ||' '||'is a'||' '||job
   2 AS "Employee Details"
   3 FROM emp;




                                                           Slide 24
Duplicate Rows
 ● The default display of queries is all rows,
   including duplicate rows.
    SQL> SELECT deptno
    2 FROM emp;


   DEPTNO
   ---------
   10
   30
   10
   20
   ...
   14 rows selected.




                                                 Slide 25
Eliminating Duplicate Rows
Eliminate duplicate rows by using the
DISTINCT keyword in the SELECT clause.
   SQL> SELECT DISTINCT deptno
   2 FROM emp;


   DEPTNO
   ---------
   10
   20
   30




                                         Slide 26
              Thank you for your attention.


Asif Sohail
Assistant Professor
University of the Punjab
Punjab University College of Information Technology (PUCIT)
Allama Iqbal (Old) Campus, Anarkali
Lahore, Pakistan
Tel: +92-(0)42-111-923-923 Ext. 154
E-mail: asif@pucit.edu.pk




                                                              Slide 27

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:19
posted:6/15/2011
language:English
pages:27
Description: Systax of Insert delete and update queries