Views,%20Substitution%20Variables,%20Tables%20and%20Indexes

1
Lecture 3

• Views, Substitution Variables, Tables and Indexes

2
Views

• A view is a logical table that extracts its data
  from one or more underlying tablesCREATE VIEW
  view-name (col1, col2, )ASSELECT
  normal-select-statement

3
Views

• ExampleCREATE VIEW saldataASSELECT empno,
  ename, sal FROM emp
• We can then use the view saldata just like a
  normal table

4
(No Transcript)
5
Views

• Example 2CREATE VIEW location (employee,
  location)ASSELECT ename, loc FROM emp,
  deptWHERE emp.deptno dept.deptno

6
(No Transcript)
7
Views

• RestrictionsViews cannot be created from SELECT
  statements containing ORDER BY or UNION clauses

8
Views

• Views cannot be used to update, insert or delete
  if it was created using
• a join
• a group function
• a GROUP BY clause
• the DISTINCT operator

9
(No Transcript)
10
Substitution Variables

• is used to indicate a substitution variable.
  SELECT empno, deptno FROM empWHERE
  deptnodepartment_number

11
(No Transcript)
12
Substitution Variables

• When the query is run, the deptno will be
  requested and then query run with the entered
  value. This is very useful when a number of rows
  require to be inserted. INSERT INTO
  mydeptVALUES (deptno, dname, location)

13
(No Transcript)
14
Substitution Variables

• To re-run the current SQL statement use /
• To re-run the current SQL statement and see the
  command use run

15
(No Transcript)
16
Creating Tables

• Before it is possible to look at whats in a
  table, we must create it!CREATE TABLE video
  (video_no NUMBER(3) NOT NULL,
  title VARCHAR2(30), category VARCHAR2(15))

17
(No Transcript)
18
Creating Tables

• We have created a table, video, with three
  columns video_no, title, category. The NOT NULL
  constraint has been placed on the video_no
  meaning that every cell in that column must
  contain a value.
• The data types for the columns are
• NUMBER 1 or 2 or 999
• VARCHAR2 characters, i.e. Titanic or LA
  confidential

19
Removing Tables

• To remove a table, useDROP TABLE table
• To remove a view, useDROP VIEW view

20
Adding Columns

• We can add columns to an existing table
• ALTER TABLE table ADD(col-name data-type
  constraint, col-name data-type constraint )

21
Modifying Columns

• Or we can change (modify) the columns in a table
• ALTER TABLE table MODIFY(col-name data-type,
  col-name data-type )
• columns cannot be made smaller (i.e VARCHAR2(20)
  -gt VARCHAR2(10))

22
Indexes

• Indexes improve performance when
• searching for rows with specified index column
  values
• accessing tables in index column order
• However, they slow performance when
• Inserting, updating and deleting

23
Indexes

• Initially create table, insert rows and then
  index
• CREATE INDEX indexON table (column ASC
  DESC, column ASC DESC )
• CREATE INDEX emp_indexON emp (ename)

24
Summary

• Views
• Substitution Variables
• Create Table
• Indexes