In this section, several examples of SQL DML statements are given. Variations on WHERE clause, FROM clause and using SQL functions are all demonstrated. 

Basic Select Statements

      CREATE TABLE students (studentid NUMBER(5,0), name VARCHAR2(25),
      major VARCHAR2(15), gpa NUMBER(6,3), tutorid NUMBER(5,0));
      INSERT INTO students VALUES (101, 'Bill', 'CIS', 3.45,  102);
      INSERT INTO students VALUES (102, 'Mary', 'CIS', 3.10,  NULL);
      INSERT INTO students VALUES (103, 'Sue',  'Marketing', 2.95, 102);
      INSERT INTO students VALUES (104, 'Tom',  'Finance', 3.5, 106);
      INSERT INTO students VALUES (105, 'Alex', 'CIS', 2.75, 106);
      INSERT INTO students VALUES (106, 'Sam',  'Marketing', 3.25, 103);
      INSERT INTO students VALUES (107, 'Jane', 'Finance', 2.90, 102);

      CREATE TABLE courses(studentid  NUMBER(5,0) NOT NULL,
      coursenumber VARCHAR2(15) NOT NULL,
      coursename VARCHAR2(25), semester  VARCHAR2(10),
      year NUMBER(4,0), grade VARCHAR2(2));

      INSERT INTO courses VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+');
      INSERT INTO courses VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-');
      INSERT INTO courses VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
      INSERT INTO courses VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-');
      INSERT INTO courses VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B');
      INSERT INTO courses VALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+');
      INSERT INTO courses VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A');
      INSERT INTO courses VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
      INSERT INTO courses VALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-');

• Average GPA of all students:

          SELECT AVG(gpa)
          FROM   students;

          AVG(GPA)
          ----------
          3.12857143

• Average GPA of Finance and CIS students:

          SELECT AVG(gpa)
          FROM   students
          WHERE  major = 'CIS' OR major = 'Finance';

          AVG(GPA)
          ----------
          3.14

• Give the name of the student with the highest GPA:

This is an example of a subquery

          SELECT name, gpa
          FROM   students
          WHERE  gpa =
          (  SELECT MAX(gpa) FROM students  );

          NAME            GPA
          -------- ----------
          Tom             3.5

Another option is to enclose some text in quotes and concatenate that text with the output of the SQL statement:

          SELECT 'The student with the highest GPA is ' || name
          FROM   students
          WHERE  gpa =
          (  SELECT MAX(gpa) FROM students   );

          NAME
          ------------------------------------------
          The student with the highest grade is Tom

• Show the students with the GPA grades in each major:

          SELECT    name, major, gpa
          FROM      students s1
          WHERE     gpa =
          (
          SELECT max(gpa)
          FROM   students s2
          WHERE  s1.major = s2.major
          );

          NAME     MAJOR             GPA
          -------- ---------- ----------
          Bill     CIS              3.45
          Tom      Finance           3.5
          Sam      Marketing        3.25

Note the two aliases given to the students table: s1 and s2. These allow us to refer to different views of the same table.

      SELECT    name, major, gpa
      FROM      students s1
      WHERE     gpa =
      (
      SELECT max(gpa)
      FROM   students s2
      WHERE  s1.major = s2.major
      )
      ORDER BY gpa DESC;

      NAME     MAJOR             GPA
      -------- ---------- ----------
      Tom      Finance           3.5
      Bill     CIS              3.45
      Sam      Marketing        3.25

Selecting from 2 or More Tables

• In the FROM portion, list all tables separated by commas. Called a Join.
• The WHERE part becomes the Join Condition

      Example table EMPLOYEE:
      FNAME    MI LNAME         SSN BDATE     ADDRESS                   S SALARY  SUPERSSN DNO
      -------- -- ------- --------- --------- ------------------------- - ------ --------- ---
      JOHN     B  SMITH   123456789 09-JAN-55 731 FONDREN, HOUSTON, TX  M  30000 333445555 5
      FRANKLIN T  WONG    333445555 08-DEC-45 638 VOSS,HOUSTON TX       M  40000 888665555 5
      ALICIA   J  ZELAYA  999887777 19-JUL-58 3321 CASTLE, SPRING, TX   F  25000 987654321 4
      JENNIFER S  WALLACE 987654321 20-JUN-31 291 BERRY, BELLAIRE, TX   F  43000 888665555 4
      RAMESH   K  NARAYAN 666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX  M  38000 333445555 5
      JOYCE    A  ENGLISH 453453453 31-JUL-62 5631 RICE, HOUSTON, TX    F  25000 333445555 5
      AHMAD    V  JABBAR  987987987 29-MAR-59 980 DALLAS, HOUSTON, TX   M  25000 987654321 4
      JAMES    E  BORG    888665555 10-NOV-27 450 STONE, HOUSTON, TX    M  55000           1

      Example table DEPARTMENT:
      DNAME             DNUMBER    MGRSSN MGRSTARTD
      --------------- --------- --------- ---------
      RESEARCH                5 333445555 22-MAY-78
      ADMINISTRATION          4 987654321 01-JAN-85
      HEADQUARTERS            1 888665555 19-JUN-71

      Example Table DEPT_LOCATIONS:
      DNUMBER DLOCATION
      ------- ---------------
      1 HOUSTON
      4 STAFFORD
      5 BELLAIRE
      5 SUGARLAND
      5 HOUSTON

      Example table DEPENDENT:
      ESSN DEPENDENT_NAME  SEX BDATE     RELATIONSHIP
      --------- --------------- --- --------- ------------
      333445555 ALICE           F   05-APR-76 DAUGHTER
      333445555 THEODORE        M   25-OCT-73 SON
      333445555 JOY             F   03-MAY-48 SPOUSE
      123456789 MICHAEL         M   01-JAN-78 SON
      123456789 ALICE           F   31-DEC-78 DAUGHTER
      123456789 ELIZABETH       F   05-MAY-57 SPOUSE
      987654321 ABNER           M   26-FEB-32 SPOUSE

• List all of the employees working in Houston:

          SELECT  employee.fname, employee.lname
          FROM    employee, dept_locations
          WHERE   employee.dno = dept_locations.dnumber
          AND     dept_locations.dlocation = 'HOUSTON' ;

          FNAME    LNAME
          -------- --------
          JOHN     SMITH
          FRANKLIN WONG
          RAMESH   NARAYAN
          JOYCE    ENGLISH
          JAMES    BORG

• List each employee name and the location they work in. List them in order of location and name:

          SELECT    dept_locations.dlocation, department.dname,
                    employee.fname, employee.lname
          FROM      employee, department, dept_locations
          WHERE     employee.dno = department.dnumber
            AND     department.dnumber = dept_locations.dnumber
            AND     employee.dno = dept_locations.dnumber
          ORDER BY  dept_locations.dlocation, employee.lname;

          Results:
          DLOCATION       DNAME           FNAME    LNAME
          --------------- --------------- -------- --------
          BELLAIRE        RESEARCH        JOYCE    ENGLISH
          BELLAIRE        RESEARCH        RAMESH   NARAYAN
          BELLAIRE        RESEARCH        JOHN     SMITH
          BELLAIRE        RESEARCH        FRANKLIN WONG
          HOUSTON         HEADQUARTERS    JAMES    BORG
          HOUSTON         RESEARCH        JOYCE    ENGLISH
          HOUSTON         RESEARCH        RAMESH   NARAYAN
          HOUSTON         RESEARCH        JOHN     SMITH
          HOUSTON         RESEARCH        FRANKLIN WONG
          STAFFORD        ADMINISTRATION  AHMAD    JABBAR
          STAFFORD        ADMINISTRATION  JENNIFER WALLACE
          STAFFORD        ADMINISTRATION  ALICIA   ZELAYA
          SUGARLAND       RESEARCH        JOYCE    ENGLISH
          SUGARLAND       RESEARCH        RAMESH   NARAYAN
          SUGARLAND       RESEARCH        JOHN     SMITH
          SUGARLAND       RESEARCH        FRANKLIN WONG

          16 rows selected.

• What is the highest paid salary in Houston ?

          SELECT MAX(employee.salary)
          FROM   employee, dept_locations
          WHERE  employee.dno = dept_locations.dnumber
            AND  dept_locations.dlocation = 'HOUSTON';

          MAX(EMPLOYEE.SALARY)
          --------------------
          55000

• To obtain the Cartesian Product of two tables, use a SELECT statement with no WHERE clause:

          SELECT *
          FROM department, dept_locations;

          DNAME           DNUMBER    MGRSSN MGRSTARTD DNUMBER DLOCATION
          --------------- ------- --------- --------- ------- ----------
          RESEARCH              5 333445555 22-MAY-78       1 HOUSTON
          ADMINISTRATION        4 987654321 01-JAN-85       1 HOUSTON
          HEADQUARTERS          1 888665555 19-JUN-71       1 HOUSTON
          RESEARCH              5 333445555 22-MAY-78       4 STAFFORD
          ADMINISTRATION        4 987654321 01-JAN-85       4 STAFFORD
          HEADQUARTERS          1 888665555 19-JUN-71       4 STAFFORD
          RESEARCH              5 333445555 22-MAY-78       5 BELLAIRE
          ADMINISTRATION        4 987654321 01-JAN-85       5 BELLAIRE
          HEADQUARTERS          1 888665555 19-JUN-71       5 BELLAIRE
          RESEARCH              5 333445555 22-MAY-78       5 SUGARLAND
          ADMINISTRATION        4 987654321 01-JAN-85       5 SUGARLAND
          HEADQUARTERS          1 888665555 19-JUN-71       5 SUGARLAND
          RESEARCH              5 333445555 22-MAY-78       5 HOUSTON
          ADMINISTRATION        4 987654321 01-JAN-85       5 HOUSTON
          HEADQUARTERS          1 888665555 19-JUN-71       5 HOUSTON
          15 rows selected.

• In which states do our employees work ?

          SELECT  DISTINCT dlocation
          FROM    dept_locations;

          DLOCATION
          ---------------
          BELLAIRE
          HOUSTON
          STAFFORD
          SUGARLAND

• List the Department name and the total salaries for each department:

          SELECT   department.dname, SUM( employee.salary )
          FROM     employee, department
          WHERE    employee.dno = department.dnumber
          GROUP BY department.dname

          Results:

          DNAME           SUM(EMPLOYEE.SALARY)
          --------------- --------------------
          ADMINISTRATION                 93000
          HEADQUARTERS                   55000
          RESEARCH                      133000

• We can also use a Column Alias to change the title of the columns

          SELECT   department.dname, SUM( employee.salary ) AS TotalSalaries
          FROM     employee, department
          WHERE    employee.dno = department.dnumber
          GROUP BY department.dname

          Results:

          DNAME           TOTALSALARIES
          --------------- -------------
          ADMINISTRATION          93000
          HEADQUARTERS            55000
          RESEARCH               133000

• Here is a combination of a function and a column alias:

          SELECT   fname, lname, salary  AS CurrentSalary,
                   (salary * 1.03)  AS ProposedRaise
          FROM     employee;

          FNAME    LNAME    CURRENTSALARY PROPOSEDRAISE
          -------- -------- ------------- -------------
          JOHN     SMITH            30000         30900
          FRANKLIN WONG             40000         41200
          ALICIA   ZELAYA           25000         25750
          JENNIFER WALLACE          43000         44290
          RAMESH   NARAYAN          38000         39140
          JOYCE    ENGLISH          25000         25750
          AHMAD    JABBAR           25000         25750
          JAMES    BORG             55000         56650

          8 rows selected.

Recursive Queries and Table Aliases

• Recall some of the E-R diagrams and relations we dealt with had a recursive relationship.
• For example: A student can tutor one or more other students. A student has only one tutor.

STUDENTS (studentid, name, major, grade, student_tutorid)
• Provide a listing of each student and the name of their tutor:

          SELECT   s1.name AS Student,  tutors.name AS Tutor
          FROM     students s1,  students tutors
          WHERE    s1.tutorid = tutors.studentid;

          STUDENT                   TUTOR
          ------------------------- -----------
          Bill                      Mary
          Sue                       Mary
          Jane                      Mary
          Sam                       Sue
          Tom                       Sam
          Alex                      Sam

• The above is called a "recursive" query because it access the same table two times.
• We give the table two aliases called s1 and tutors so that we can compare different aspects of the same table.
• However, as is, the table is missing something: We don't see who is tutoring Mary. Use a left outer join to see the rest of the information. In MS Access, we use the LEFT JOIN command. In Oracle, we place a (+) after the join condition to indicate an outer join:

 

 
 
 
 
 

In MS Access:

            SELECT   s1.name AS Student,  tutors.name AS Tutor
            FROM     students s1   LEFT JOIN   students tutors
            ON       s1.tutorid = tutors.studentid;

In Oracle:

            SELECT   s1.name AS Student,  tutors.name AS Tutor
            FROM     students s1, students tutors
            WHERE    s1.tutorid = tutors.studentid (+);

            STUDENT                   TUTOR
            ------------------------- -------------
            Bill                      Mary
            Sue                       Mary
            Jane                      Mary
            Sam                       Sue
            Tom                       Sam
            Alex                      Sam
            Mary

• Here is one more twist: Suppose we were interested in those students who do not tutor anyone? Use a right outer join (RIGHT JOIN in MS Access).
• How many students does each tutor work with ?

          SELECT   s1.name AS TutorName,
          COUNT(tutors.tutorid) AS NumberTutored
          FROM     students s1, students tutors
          WHERE    s1.studentid = tutors.tutorid
          GROUP BY s1.name;

          TUTORNAME                 NUMBERTUTORED
          ------------------------- -------------
          Mary                                  3
          Sam                                   2
          Sue                                   1

Tree Queries

• Bill tutors Alex, Mary and Sue.
• Mary tutors Liz and Ed
• Sue tutors Petra

• START WITH - indicates which row the tree should start with.
• CONNECT BY - indicates how successive related rows are to be identified and included in the result.
• LEVEL - a pseudo-column that indicates which level of the tree the current row is assigned to.

      SELECT            LPAD(' ',2*(LEVEL-1)) || students.name
      As TutorTree
      FROM              students
      START WITH        studentid = '102'
      CONNECT BY PRIOR  studentid = tutorid;

      TUTORTREE
      --------------------------------------------------------------------------------
      Mary
      Bill
      Sue
      Sam
      Tom
      Alex
      Jane

      7 rows selected.

WHERE Clause Expressions

• There are a number of expressions one can use in a WHERE clause.
• Subqueries using = (equals):

          SELECT name, grade
          FROM   students
          WHERE  grade =
          (  SELECT MAX(grade) FROM students );

This assumes the subquery returns only one tuple as a result.
Typically used when aggregate functions are in the subquery.
 
• Subqueries using the IN operator are used whenever the value of a column should be found in a set of values. The set of values can be explicitly listed (as in the first example) or they can be created on the fly using a subquery.

          SELECT    employee.fname, department.dname
          FROM      employee, department
          WHERE     employee.dno = department.dnumber
          AND     department.dname IN ('HEADQUARTERS', 'RESEARCH');

          FNAME    DNAME
          -------- ---------------
          JAMES    HEADQUARTERS
          JOHN     RESEARCH
          JOYCE    RESEARCH
          RAMESH   RESEARCH
          FRANKLIN RESEARCH


          SELECT    employee.fname
          FROM      employee
          WHERE     employee.dno IN
          (SELECT dept_locations.dnumber
          FROM dept_locations
          WHERE dept_locations.dlocation = 'STAFFORD');

          FNAME
          -------
          ALICIA
          JENNIFER
          AHMAD

In the above case, the subquery returns a set of tuples. The IN clause returns true when a tuple matches a member of the set.
• Subqueries using EXISTS. EXISTS will return TRUE if there is at least one row resulting from the subquery.

          SELECT   fname, lname, salary
          FROM     employee
          WHERE    EXISTS
          (SELECT  fname
          FROM    EMPLOYEE e2
          WHERE   e2.salary > employee.salary)
          AND EXISTS
          (SELECT  fname
          FROM    EMPLOYEE e3
          WHERE   e3.salary < EMPLOYEE.SALARY);

          FNAME    LNAME       SALARY
          -------- -------- ---------
          JOHN     SMITH        30000
          FRANKLIN WONG         40000
          JENNIFER WALLACE      43000
          RAMESH   NARAYAN      38000

The above query shows all employees names and salaries where there is at least one person who makes more money (the first exists) and at least one person who makes less money (second exists).
• Subqueries with NOT EXISTS. NOT EXISTS will return TRUE if there are no rows returned by the subquery.

          SELECT   fname, lname, salary
          FROM     employee
          WHERE    NOT EXISTS
          (SELECT  fname
          FROM    EMPLOYEE e2
          WHERE   e2.salary > employee.salary);

          FNAME    LNAME       SALARY
          -------- -------- ---------
          JAMES    BORG         55000

The above query shows all employees for whom there does not exist an employee who is paid less. In other words, the highest paid employee.
• The HAVING clause is similar to the WHERE clause. The difference is that WHERE is used to filter individual rows while HAVING is used to filter groups according to the GROUP BY clause.

 

 
 
 
 
 

Show the departments with average salary greater than 33000.

            SELECT    department.dname, AVG(salary)
            FROM      employee, department
            WHERE     employee.dno = department.dnumber
            GROUP BY  department.dname
            HAVING    AVG(salary) > 33000 ;

            DNAME           AVG(SALARY)
            --------------- -----------
            HEADQUARTERS          55000
            RESEARCH              33250

Show departments with 3 or more employees:
            SELECT   department.dname, COUNT(employee.dno)
            FROM   department, employee
            WHERE   department.dnumber = employee.dno
            GROUP BY department.dname
            HAVING   COUNT(employee.dno) >= 3;

            DNAME           COUNT(EMPLOYEE.DNO)
            --------------- -------------------
            ADMINISTRATION                    3
            RESEARCH                          4

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips