Chapter Six Data Manipulation Language DML

1
Chapter SixData 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
Tables

• Example

3
General Format

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

4
Select Attributes

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

5
Select Attributes

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

6
General Format

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

7
General Format

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

8
Practice

• List all columns in customer table

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
Practice

• List of cities in customer table. (Unique city
  name)

12
Use of Aliases

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

13
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

14
Use of Aliases

• NAME PAY NEW_SALARY
• -------- ------ -----------------

15
Practice

• Produce the following list
• Customer_City Customer_State Customer_Zip_Code
• ----------------- ------------------
  ------------------------

16
Use of Arithmetic Operations

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

17
Use of Arithmetic Operations

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

18
Use of Concatenation

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

19
Use of Literal

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

20
Condition statements

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

21
Condition Operators

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

22
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

23
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

24
Question

• What kind of information you get if you use this
  condition
• SELECT name
• FROM student
• WHERE GPA gt 3 OR major 'COSC'

25
Question

• What kind of information you get if you use this
  condition
• SELECT name
• FROM student
• WHERE (GPA gt 3 AND major 'COSC')
• OR (GPAgt2.5 AND majorART )

26
Practice

• List of Last name, First name of customers with
  a balance gt 2000 and their birth date is before
  March, 01, 1985.

27
Precedence Rule

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

28
Practice

• List Last name of customer which have a balance
  gt100 or credit limit lt2000 and live in MD from
  the customer table.

29
Null vs. No Value

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

30
Null vs. No Value

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

31
Null Values in Expressions

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

32
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

33
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

34
Practice

• List of customer first and last names which have
  not been assigned a sales rep. number.

35
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

36
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)

37
Use of Between

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

38
Question

• List of names starting with D only!

39
Practice

• List the order numbers of items with a order
  quoted price between 100 and 1000

40
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)

41
Use of IN

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

42
Practice

• List customer number, first and last name of
  customers with Zip code of 11011 or 12011 or
  10012 or 11001

43
Use of LIKE

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

44
Use of LIKE

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

45
Use of LIKE

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

46
Practice

• List of customers with the last name ending with
  SON like Jackson, Nelson, Larson.

47
Practice

• List of customer Last names who live in a street
  name which has a character string upper like
  South upper Potomac, upper Dakota, Magnolia
  upper