COMP 231 Database Management Systems Lab 3 Data Functions by coronanlime

VIEWS: 6 PAGES: 17

									COMP 231 Database Management Systems

Lab 3 Data Functions and Joins




         Fall, 2008




                         COMP 231 Database Management   1
                            System Fall, 2008 Lab 3
Login to SQL*Plus
• Login to SQL*Plus as we taught in
  Lab 1 last week.

• If you have forgotten how to do it,
  you may reference here.

• Download the lab3.sql and
  execute it to create the tables and
  entries for this lab.
• We have 3 tables: students,
  courses and departments

               COMP 231 Database Management   2
                  System Fall, 2008 Lab 3
Objective
• After this lab, you will learn
  the following:
   –   Characters Functions
   –   Number Functions
   –   Date Functions
   –   Joins




               COMP 231 Database Management   3
                  System Fall, 2008 Lab 3
More Character Functions
• LOWER(‘SQL Lab’) => sql lab
• UPPER(‘SQL Lab’) => SQL LAB
• INITCAP(‘SQL Lab’) => Sql Lab

SELECT LOWER(last_name)
FROM students
WHERE UPPER(last_name)
 = ‘DA VINCI’;

           COMP 231 Database Management   4
              System Fall, 2008 Lab 3
More Character Functions
• CONCAT(‘Data’,’Base’)
• SUBSTR(‘Database’, 1, 4)
• LENGTH(‘Database’)
• INSTR(‘Database’, ‘b’)
• LPAD(cga, 8, ‘*’)
• RPAD(cga, 8, ‘*’)
• TRIM(‘D’ FROM ‘Database’)
SELECT ???
FROM dual;
Try the others by yourself and see the
  meaning of each function.

              COMP 231 Database Management   5
                 System Fall, 2008 Lab 3
Number Functions
• ROUND(123.4567, 2)
• TRUNC(123.4567,2)
• MOD(2031, 300)

Try again with
SELECT ???
FROM dual;
using other numbers, e.g.
(123.4567, 0), (123.4567, -1)

            COMP 231 Database Management   6
               System Fall, 2008 Lab 3
Date Functions
• MONTHS_BETWEEN(SYSDATE, ’01-
  SEP-04)
• ADD_MONTHS (SYSDATE, 5)
• NEXT_DAY(SYSDATE, ‘SUNDAY’)
• LAST_DAY(SYSDATE)
  – This one is difficult to guess
• ROUND(SYSDATE, ‘MONTH’)
• ROUND(SYSDATE, ‘YEAR’)
• TRUNC(SYSDATE, ‘MONTH’)
• TRUNC(SYSDATE, ‘YEAR’)
Try again with
SELECT ???
FROM dual;


                  COMP 231 Database Management   7
                     System Fall, 2008 Lab 3
Types of JOINS
• SQL: 1999
  –   Cross Joins
  –   Natural Joins
  –   Using, On clause
  –   Full or two sided outer joins




               COMP 231 Database Management   8
                  System Fall, 2008 Lab 3
Cross Join (Cartesian Product)
• Generated if a join condition is
  missing
SELECT first_name, name
FROM students
CROSS JOIN departments;

How many rows are selected?
Try also:
SELECT first_name, name
FROM students, departments;

               COMP 231 Database Management   9
                  System Fall, 2008 Lab 3
Natural Join
• NATURAL JOIN based on all
  columns in the two tables that
  have the same name
• Selects rows from the two tables
  that have equal values in all
  matched columns
SELECT first_name, name,
  room_number
FROM students
NATURAL JOIN departments;


              COMP 231 Database Management   10
                 System Fall, 2008 Lab 3
Joins with USING clause
• NATURAL JOIN use ALL common
  columns to join
• USING Clause can match one
  column for matching
SELECT s.first_name, d.name
FROM students s JOIN departments
  d
USING (department_id);
Remember to add ( )



            COMP 231 Database Management   11
               System Fall, 2008 Lab 3
Joins with ON clause
• USING clause allow to join 2
  tables who share a common
  column name
• Can we specify 2 columns to join
  by ourselves?
SELECT s.first_name, s.course_id, c.name
FROM students s JOIN courses c
ON (s.course_id = c.course_code)

Can you do this by USING clause?


                COMP 231 Database Management   12
                   System Fall, 2008 Lab 3
3-Way Join
SELECT s.first_name, d.name as
  "Department", c.name as "Course"
FROM students s
JOIN courses c
ON (s.course_id = c.course_code)
JOIN departments d
ON (d.department_id = s.department_id)




                COMP 231 Database Management   13
                   System Fall, 2008 Lab 3
INNER and OUTER Joins
• Inner Joins
  – Return only matched rows
• Outer Joins
  – Return matched rows
  – Also return unmatched rows
• 3 types:
  – Left Outer Joins
  – Right Outer Joins
  – Full Outer Joins


             COMP 231 Database Management   14
                System Fall, 2008 Lab 3
LEFT, RIGHT and FULL Outer Joins
SELECT s.first_name, c.course_code
FROM students s
LEFT OUTER JOIN courses c
ON (s.course_id = c.course_code);

SELECT s.first_name, c.course_code
FROM students s
RIGHT OUTER JOIN courses c
ON (s.course_id = c.course_code);

SELECT s.first_name, c.course_code
FROM students s
FULL OUTER JOIN courses c
ON (s.course_id = c.course_code);


                 COMP 231 Database Management   15
                    System Fall, 2008 Lab 3
Exercises
• Display all students who
  studied more than 2 years
• Display the course code and
  course name that no student
  is taking it.
• Display the instructors whose
  office is at the 3rd floor
  (assume that the instructors’ office
  is located at the same floor as the
  department)


              COMP 231 Database Management   16
                 System Fall, 2008 Lab 3
Exercises
• Display ALL courses (including
  those have no students) and the
  students who take the course with
  the following schema:
  {course code, student’s full name,
  student’s department name}
  Sorted by course code




              COMP 231 Database Management   17
                 System Fall, 2008 Lab 3

								
To top