Working with Tables: Data Management and Retrieval

1
Working with Tables Data Management and
Retrieval

• Dr. Bernard Chen Ph.D.
• University of Central Arkansas
• Fall 2008

2
DATA MANIPULATION LANGUAGE (DML)

• SQL languages Data Manipulation Language (DML)
  consists of three statements
• INSERT
• UPDATE
• DELETE

3
DATA MANIPULATION LANGUAGE (DML)

• A new row is added to a table with the INSERT
  statement
• The DELETE statement removes row(s) from a table.
• Data in existing row(s) is/are changed with the
  UPDATE statement.

4
ADDING A NEW ROW/RECORD

• The Data Manipulation Language (DML) statement
  INSERT is used to insert a new row/record into a
  table.
• The general syntax for the INSERT statement is as
  follows
• INSERT INTO tablename (column1, column2,
  column3,...)
• VALUES (value1, value2, value3,...)

5
ADDING A NEW ROW/RECORD

• The column names are optional. If column names
  are omitted from the INSERT statement, you must
  enter a value for each column.
• If you know the order of column names in correct
  order, you can enter values in the same order
  following the VALUES keyword.
• (Use the SQLPlus command DESCRIBE to display the
  tables structure to make sure.)

6
ADDING A NEW ROW/RECORD

• If you do enter column names, they do not have to
  be in the same order as they were defined in
  tables structure at the time of creation.
• INSERT INTO student (StudentID, LAST, FIRST, ZIP,
  Bdate, FacultyID)
• VALUES (00100, Will, Smith, 72034,
  12-FEB-80, 123)

7
ADDING A NEW ROW/RECORD

• When entering values, numeric data is not
  enclosed within quotes
• The character and date type values are enclosed
  within single quotes. 
• The default format to enter the date value is
  DD-MON-YY.

8
ADDING A NEW ROW/RECORD

• There are two methods for inserting a NULL value
  in a column.
• 1. Implicit Method. In the implicit method, the
  columns name is omitted from the column list in
  an INSERT statement. For example,
• INSERT INTO dept (DeptId, DeptName)
• VALUES (50, Production)

9
ADDING A NEW ROW/RECORD

• 2. Explicit Method. In the explicit method, the
  value NULL is used as a value for numeric column,
  and an empty string () is used for date or
  character columns. For example,
• INSERT INTO dept (DeptId, DeptName, Location,
  EmployeeId)
• VALUES (60, Personnel, Chicago, NULL)

10
UPDATING EXISTING ROWS/RECORDS

• In SQL, the UPDATE statement is used to modify
  data. 
• Only one table can be updated at one time.
• It is possible to change more than one column at
  a time
• UPDATE tablename
• SET column1 newvalue , column2
  newvalue,...
• WHERE condition(s)

11
UPDATING EXISTING ROWS/RECORDS

• The condition is optional, but it is necessary
• Suppose the student with ID 00103 in the IU
  colleges database switches major from BS---CS to
  BS---EE
• UPDATE student
• Set MajorID 700
• Where studentid00103

12
DELETING EXISTING ROWS/RECORDS

• Deletion is another data maintenance operation. 
• In Oracle, the SQL statement DELETE is used for
  deleting unwanted rows. Its general syntax is
• DELETE FROM tablename
• WHERE condition(s)

13
DELETING EXISTING ROWS/RECORDS

• Example
• DELETE FROM dept
• WHERE DeptID 70

14
RETRIEVING DATA FROM A TABLE

• The main purpose of the SQL language is for
  querying the database
• The most important statement or query is the
  SELECT query
•  The general syntax is
• SELECT columnlist
• FROM tablename

15
RETRIEVING DATA FROM A TABLE

• Example
• SELECT Last, First
• FROM student

16
RETRIEVING DATA FROM A TABLE

• SELECT ()
• If you want to see all columns in a table, you do
  not have to list them all. 
• You can use character asterisk () in place of
  the column list, and all columns will be
  displayed in the same order as the underlying
  table structure. 

17
RETRIEVING DATA FROM A TABLE

• RESTRICTING DATA WITH A WHERE CLAUSE
• A WHERE clause is used with the SELECT query to
  restrict rows picked
• The general syntax of the WHERE clause is
• SELECT columnlist
• FROM tablename
• WHERE condition(s)

18
RETRIEVING DATA FROM A TABLE

• SELECT
• FROM dept
• WHERE Location Monroe
• SELECT Lname, Fname, Salary, Deptid
• FROM employee
• WHERE Salary 50000

19
RETRIEVING DATA FROM A TABLE

• SELECT Lname, Fname, Salary, Deptid
• FROM employee
• WHERE Salary 50000 AND Salary
• SELECT Lname, Fname, Salary, Deptid
• FROM employee
• WHERE Salary BETWEEN 50000 AND 25000

20
RETRIEVING DATA FROM A TABLE

• SORTING
• The order of rows in a table is arbitrary.
• You may want to see rows in a specific order
  based on a column or columns
• For example, you may want to see employees in
  alphabetical order by their name

21
RETRIEVING DATA FROM A TABLE

• The ORDER BY clause is used with the SELECT query
  to sort rows in a table. 
• The general syntax is
• SELECT columnlist
• FROM tablename
• WHERE condition(s)
• ORDER BY columnexpression ASCDESC

22
RETRIEVING DATA FROM A TABLE

• SELECT Last, First
• FROM student
• ORDER BY Last
• SELECT Last, First
• FROM student
• ORDER BY Last DESC