Chapter FiveData 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

Example:

Student (Name, ID, GPA, Major, B_Date)

Course (C_Num, Dept, Title, Cr)

Student_Course (ID, C_Num, Dept, Grade)

Faculty (ID, Name, Dept, Salary, Area)

Faculty_Course (ID, C_Num, Dept, Semester)

Department (Name, Num_Faculty)

Tables

3

General Format

SELECT fieldnamesFROM 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, GPAFROM 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 CrFROM Course;

10

Use of Distinct: Example:

Type of the course credits offered at FSU.

SELECT DISTINCT CrFROM Course;

11

Practice:

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

12

Use of Aliases: Example:List of the faculty’s name and their

salary per month.  SELECT name, salary / 12FROM faculty;

13

Use of Aliases: Rename column heading

Example: List of the faculty salary for next year with 5% increase.

 

SELECT name, salary Pay,

salary+salary*0.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* (2+1)

17

Use of Arithmetic Operations: Example: List the course numbers and credits

in a quarter system 

SELECT C_Num, Cr * 0.75FROM 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, GPAFROM studentWHERE GPA > 2;

21

Condition Operators: =, >, >=, <=, <, <> != ^= IN BETWEEN ..... AND ..... LIKE IS NULL AND, OR, NOT

22

String & Date Comparison Example: List the students who born on March 2, 99 

SELECT nameFROM studentWHERE B_Date =’02-MAR-99’ ;

Date Format ‘DD-MON-YY’

23

Use of Boolean Operations: Example:

List of Student names that have a GPA > 3and majoring in COSC

SELECT nameFROM studentWHERE GPA > 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 studentWHERE GPA > 3 OR major = 'COSC';

25

Question

What kind of information you get if you use this condition: SELECT name FROM studentWHERE (GPA > 3 AND major = 'COSC')OR (GPA>2.5 AND major=‘ART ’);

26

Practice:

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

27

Precedence Rule: >, >=, <>, <=, =, NOT AND OR

28

Practice:

List Last name of customer which have a balance >100 or credit limit <2000 and live in MD from the customer table.

29

Null vs. No Value Null value is:

UnavailableUnassignedUnknownInapplicable

Examples: Null is not the same as zero or blank

30

Null vs. No Value SELECT name, Major

FROM Student;

SELECT name, Num_FacultyFROM Department;

31

Null Values in Expressions Result of an arithmetic expression

with a null value is null.

SELECT name, GPA*0.75FROM Student;

32

Null vs. No Value List of students with no major SELECT name

FROM StudentWHERE major IS NULL;

SELECT nameFROM StudentWHERE 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 TitleFROMCourseWHERE C_Num BETWEEN 200 AND 299;

36

Use of BetweenSELECT TitleFROM CourseWHERE C_Num NOT BETWEEN 200

AND 299;

SELECT TitleFROM CourseWHERE (C_Num >= 200) AND

(C_Num <= 299);

37

Use of Between List of Faculty’s name from D to E

SELECT nameFROM facultyWHERE 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 nameFROM StudentWHERE ID IN (1111, 2111, 3111);

41

Use of INSELECT nameFROM StudentWHERE 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 student’s records so

that the student’s name starts with a ‘K’

SELECT *FROM StudentWHERE name LIKE ‘K%’;

45

Use of LIKE List of students records with the

second character to be ‘K’

SELECT *FROM StudentWHERE 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’