Chapter Eight Data Manipulation Language DML

1
Chapter EightData Manipulation Language (DML)

• Objectives
• Oracle DBMS
• Understanding the DML
• General format of SQL
• Capability of SELECT statement
• Use of Operators
• String Processing Concept of NULL
• Conditional Statement

2
SQL

• Structured Query Language
• Developed by IBM
• Used in most Commercial DBMS
• Statements are not case sensitive.
• Statements can be on one or more lines.
• Reserved words cannot be abbreviated or split
  over lines.
• Terminated with a semicolon.
• Statements are entered at SQL prompt. The
  subsequent lines are numbered (SQL buffer)
• Only one statement can be current at any time in
  the buffer.

3
Tables

• Example

4
General Format

• SELECT fieldnames
• FROM relation
• WHERE condition
• GROUP BY group_field
• HAVING condition
• ORDER BY fieldname

5
Select Attributes

• Example
• Show the name and GPA of the students (ALL).
•  
• SELECT name, GPA
• FROM student

6
Select Attributes

• Example
• List all the columns in course
•  
• SELECT
• FROM course

7
General Format

• Table display
• Default justification
• -Date and characters LEFT
• -Number RIGHT
• Default display
• -Uppercase

8
General Format

• NAME GPA
• ------------ ----------
• MARY 3.1

9
Duplicated Rows

• Example
• List of the course credits offered at FSU.
• SELECT Cr
• FROM Course

10
Use of Distinct

• Example
• Type of the course credits offered at FSU.
• SELECT DISTinct Cr
• FROM Course

11
Use of Aliases

• Example
• List of the facultys name and their salary per
  month.
•  
• SELECT name, salary / 12
• FROM faculty

12
Use of Aliases

• Rename column heading
• Example List of the faculty salary for next year
  with 5 increase.
•  
• SELECT name, salary Pay,
• salarysalary0.05 AS New_Salary
• FROM faculty

13
Use of Arithmetic Operations

• ()
• -, , /
• , -
• Operation of some priority is evaluated from left
  to right
• 5 (21)

14
Use of Arithmetic Operations

• Example
• List the course numbers and credits in a quarter
  system
•  
• SELECT C_Num, Cr 0.75
• FROM Course

15
Use of Concatenation

• Example
• List of faculty and department as a unit
• SELECT name dept Name
• FROM faculty
• Name
• -----------------------------
• CHITSAZCOSC

16
Use of Literal

• Example
• List of faculty and department
• SELECT name is in dept
  Department Name
• FROM faculty
• Department Name
• -------------------------
• CHITSAZ is in COSC

17
Condition statements

• SELECT name, GPA
• FROM student
• WHERE GPA gt 2

18
Condition Operators

• , gt, gt, lt, lt, ltgt !
• IN
• BETWEEN ..... AND .....
• LIKE
• IS NULL
• AND, OR, NOT

19
String Date Comparison

• Example
• List the students who born on March 2, 99
•  
• SELECT name
• FROM student
• WHERE B_Date 02-MAR-99
• Date Format DD-MON-YY

20
Use of Boolean Operations

• Example
• List of Student names that have a GPA gt 3
• and majoring in COSC
• SELECT name
• FROM student
• WHERE GPA gt 3 AND major 'COSC'
• Character string is case sensitive should be in
  a single quotation mark

21
Precedence Rule

• gt, gt, ltgt, lt, ,
• NOT
• AND
• OR

22
Null vs. No Value

• Null value is
• Unavailable
• Unassigned
• Unknown
• Inapplicable
• Examples
• Null is not the same as zero or blank

23
Null vs. No Value

• SELECT name, Major
• FROM Student
• SELECT name, Num_Faculty
• FROM Department

24
Null Values in Expressions

• Result of an arithmetic expression with a null
  value is null.
• SELECT name, GPA0.75
• FROM Student

25
Null vs. No Value

• List of students with no major
• SELECT name
• FROM Student
• WHERE major IS NULL
• SELECT name
• FROM Student
• WHERE major IS NOT NULL

26
Null vs. No Value

• NVL Null Value substitution
• We can substitute a value for a NULL value record
  by using NVL.
• List of students and their major
• SELECT name, NVL(major, unknown)
• FROM Student
• SELECT name, NVL(GPA, 0.0)
• FROM Student
• COALESCE

27
Null vs. No Value

• List of students and their GPA base 20 points
• SELECT name, NVL(GPA, 0.0)5
• FROM Student
• NVL2 (Exp1, Exp2, Exp3)

28
Use of Between

• BETWEEN Test against a list of values (Check
  the data in a range)
• List description of courses with the course
  number between 200 AND 299
• SELECT Title
• FROM Course
• WHERE C_Num BETWEEN 200 AND 299

29
Use of Between

• SELECT Title
• FROM Course
• WHERE C_Num NOT (BETWEEN 200 AND 299)
• SELECT Title
• FROM Course
• WHERE (C_Num gt 200) AND (C_Num lt 299)

30
Use of Between

• List of Facultys name from D to E
• SELECT name
• FROM faculty
• WHERE name BETWEEN D AND E

31
Use of IN

• IN Tests against a list of values (Set of
  values used for comparison)
• List of students with ID 1111, ID 2111 or ID
  3111
• SELECT name
• FROM Student
• WHERE ID IN (1111, 2111, 3111)

32
Use of IN

• SELECT name
• FROM Student
• WHERE major IN (COSC, MATH)
• SELECT name
• FROM Student
• WHERE major NOT IN (COSC, MATH)

33
Example of IN

• page IN (2, 3 ,4, 5, 6)
• page NOT IN (2, 3 ,4, 5, 6)
• page BETWEEN 2 AND 6
• section IN (A, B, C, D, E)
• section NOT IN (A, B, C, D, E)
• section BETWEEN A AND E

34
Use of LIKE

• LIKE Determines the presence of a sub string
• (_) a single unknown character
• () any number of unknown characters

35
Use of LIKE

• List all the students records so that the
  students name starts with a K
• SELECT
• FROM Student
• WHERE name LIKE K

36
Use of LIKE

• List of students records with the second
  character to be K
• SELECT
• FROM Student
• WHERE name LIKE _K