Oracle 9i

1
Oracle 9i

• Puneet Shadija
• Lecture - 6

2
Agenda

• Normalization
• 1 NF
• 2 NF
• 3 NF
• Example

3
SQL

• Structured Query Language (SQL), pronounced
  "sequel", is a language that provides an
  interface to relational database systems.
• It was developed by IBM in the 1970s for use in
  System R. SQL is a de facto standard, as well as
  an ISO and ANSI standard.
• It is Non-Procedural

4
SQL Statements Categories

• Data Definition Language
• DDL statements define our database objects and
  result in updates to the Oracle data dictionary.
• Operations allowed
• Create
• Modify
• Delete
• for objects like Table, Views, Stored Procedures,
  Triggers, Sequences etc

5
SQL Statements Categories

• Data Manipulation Language
• DML manipulates data with the following
  statements
• INSERT
• UPDATE
• DELETE
• SELECT
• Transaction Control
• Allows user to bundle DML statements under
  all-or-nothing domain. It consists of mainly 3
  keywords
• Commit
• Rollback
• Savepoint

6
Data Type

• Numeric Datatypes (INTEGER)
• BINARY_INTEGER
• INTEGER
• SMALLINTINT
• POSITIVE
• NATURAL
• NUMBER
• Numeric Datatypes (DECIMAL)
• FLOAT
• DECIMAL
• DOUBLE PRECISION
• NUMBER
• NUMERIC
• REAL

7
Data Type

• CHAR datatype
• VARCHAR2 and VARCHAR datatypes
• BOOLEAN datatype
• DATE datatype
• LONG datatype
• RAW datatype
• ROWID datatype
• NCHAR and NVARCHAR2 datatype
• LOB datatype
• BFile, BLOB, CLOB, NCLOB

8
Create TABLE

• Syntax

Create ltTable_Namegt ( ltfield_namegt ltdata_typegt
Primary KeyUnique Key NOT NULL, ,
CONSTRAINT )
9
Examples

• Create Table ABC
• (
• A number(5,2),
• B varchar2(50),
• C date
• )
• Create Table ABC
• (
• A number(5,2),
• B varchar2(50),
• C date,
• Constraint PK_ABC PRIMARY KEY(A)
• )

10
Examples

• Create Table ABC
• (
• A number(5,2) PRIMARY KEY,
• B varchar2(50),
• C date,
• )
• Create Table ABC
• (
• A number(5,2) PRIMARY KEY,
• B varchar2(50) DEFAULT SOME VALUE,
• C date,
• )

11
Alter Table
ALTER TABLE table_name ADD ( column_1 column-defi
nition, column_2 column-definition, ... column
_n column_definition ) ALTER TABLE
table_name MODIFY ( column_1 column_type, column
_2 column_type, ... column_n column_type )
12
Alter Table
ALTER TABLE table_name DROP COLUMN
column_name ALTER TABLE table_name SET UNUSED
column_name ALTER TABLE table_name RENAME
COLUMN old_name to new_name
13
Alter Table

• The Rules for Adding or Modifying a Column
• You may add a column at any time if NOT NULL
  isnt specified.
• You may add a NOT NULL column in three steps
• Add the column without NOT NULL specified.
• Fill every row in that column with data.
• Modify the column to be NOT NULL.

14
Alter Table

• These are the rules for modifying a column
• You can increase a character columns width at
  any time.
• You can increase the number of digits in a NUMBER
  column at any time.
• You can increase or decrease the number of
  decimal places in a NUMBER column at any time.
• In addition, if a column is NULL for every row of
  the table, you can make any of these changes
• You can change the columns datatype.
• You can decrease a character columns width.
• You can decrease the number of digits in a
  NUMBER column.

15
Alter Table

• e.g.
• - Alter table mytable
• Add constraint pk_mytable PRIMARY KEY (a)
• - alter table mytable
• add b varchar2(50)
• Alter table emp_hourly
• MODIFY (HOURRATE NUMBER(5,2) DEFAULT 7.25)
• Alter table mytable
• RENAME COLUMN a TO z
• - Alter table mytable
• RENAME to mynewTable

16
RENAME
RENAME old_table_name TO new_table_name

• e.g.
• Rename mynewtable to mytable

17
DROP Table
DROP TABLE table_name

• e.g.
• DROP table mytable

18
SQL Select

• The SQL SELECT statement is used to select data
  from a SQL database table.
• General SQL SELECT syntax
• SELECT ltCol_1gt, ltCol_2gt,
• FROM ltTable_namegt
• SELECT FROM ltTable_Namegt

19
SQL WHERE

• e.g.
• Select from student
• where age BETWEEN 1/1/1983 AND 12/31/1985
• Select from student
• where agegt10 AND agelt25
• Select from student
• where gradeA or gradeB
• Select from student
• where cgpa gt7 AND NOT cgpagt9.2

20
SQL WHERE with LIKE

• Syntax
• match_expression NOT LIKE pattern ESCAPE
  escape_character
• Any string of zero or more characters
• _ Any single character
• e.g.
• select description from PROJECT where like
• description like '\_2003\_' ESCAPE '\'

21
SQL DISTINCT

• The SQL DISTINCT clause is used together with the
  SQL SELECT keyword, to return a dataset with
  unique entries for certain database table column.
• e.g.
• Select DISTINCT city from AREA_TABLE

22
SQL WHERE(With operators)

• The SQL WHERE clause is used to select data
  conditionally, by adding it to already existing
  SQL SELECT query.
• e.g.
• SELECT FROM Customers
• WHERE LastName ltgt 'Smith
• SELECT FROM Customers
• WHERE DOB gt '1/1/1970
• SELECT FROM Customers
• WHERE DOB gt '1/1/1970

23
Questions ???