Transcript Computer Programming Lab II - The Institute of Finance

Computer Programming
Lab II
Practical 1: Introduction to
Managing Data
Agenda


Creating Tables using SQL
Managing Data using DML




INSERT records in tables
UPDATE records in tables
DELETE records from tables
Queries records in Tables with SELECT
statement
Tables



In RDBMS data are represented using
tables (relations).
SQL uses the terms table, row, and
column for relation, tuple, and attribute,
respectively.
These terms can be used
interchangeably.
Tables and Data types


A table can have up to 254 columns
which may have different or same data
types and sets of values (domains),
respectively.
Oracle offers the following basic data
types:


char(n): Fixed-length character data
(string), n characters long.
varchar2(n): Variable-length character
string
Tables and Data types




number(o, d): Numeric data type for
integers and reals. o = overall number
of digits, d = number of digits to the
right of the decimal point.
date: Date data type for storing date
and time.
long: Character data up to a length of
2GB.
Blob: Store binary data upto length 4GB
Table creation using DDL
statement

CREATE TABLE command of Data
Definition Language will be used for
creating tables.




Table name must begin with a letter
Maximum length of table name is 30
character.
Table name cannot be a reserved word.
User creating table must have CREATE
TABLE privilege to create the table
Table creation using DDL
statement

Syntax:
Create Table <tablename>
(column datatype,……);
Table creation using DDL
statement

Using SQL Plus tool create three tables
basing on the following schemas and
relationships:



Dept(Dept_No: number(5), Dept_Name:
varchar2(15), Region_ID: number(5))
Employee(Emp_id: number(7), Last_Name:
varchar2(25), First_Name: Varchar(25),
Dept_id: number(7))
Region(Region_Name: varchar2(15),
Region_ID: number(5))
Table creation using DDL
statement
CREATE TABLE Dept (
Dept_No number(5),
Dept_Name varchar2(15),
Region_ID number(5),
UNIQUE (Dept_Name),
CONSTRAINT DeptKey PRIMARY KEY
(Dept_No),
CONSTRAINT FK_Region_ID FOREIGN KEY
(Region_ID) REFERENCES Region(Region_ID)
)
Table creation using DDL
statement
CREATE TABLE Employee (
Emp_id number(7),
Last_Name varchar2(25),
First_Name Varchar(25),
Job Varchar2(9),
Hiredate date
Sal number(8),
Dept_No number(7),
CONSTRAINT PK_Emp_id PRIMARY KEY(Emp_id),
CONSTRAINT FK_Dept_No FOREIGN KEY(Dept_No)
REFERENCES Dept(Dept_No)
)
Table creation using DDL
statement
CREATE TABLE Region(
Region_ID number(5),
Region_Name varchar2(15),
CONSTRAINT PK_Region_ID PRIMARY
KEY(Region_ID)
)
Tables and Data types


A database schema is a set of relation
schemas.
The extension of a database schema at
database run-time is called a database
instance or database, for short.