DATABASE MANAGEMENT SYSTEM Unit 2
CH-1 INTERACTIVE SQL
Table Fundamentals
A table is database object that holds user data. Each column of the table will have a specific data
type bound to it. Oracle ensures that only data , which is identical to the data type of the column
,will be stored within the column.
Data types in Oracle
Oracle supports the following data types.
1. Char
2. Varchar /Varchar2
3. Number
4. Date
5. Long
1. CHAR :The CHAR datatype stores fixed-length character strings. When you create a table with
a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000
bytes for the CHAR column width. The default is 1 byte. Oracle then guarantees that: When you
insert or update a row in the table, the value for the CHAR column has the fixed length.
If you give a shorter value, then the value is blank-padded to the fixed length.
If a value is too large, Oracle Database returns an error.
2. VARCHAR2: The VARCHAR2 data type stores variable-length character strings. When you
create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or
characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle
Database stores each value in the column as a variable-length field unless a value exceeds the
column's maximum length, in which case Oracle Database returns an error. Using VARCHAR2
and VARCHAR saves on space used by the table.
BCA III SEM Page 1
,DATABASE MANAGEMENT SYSTEM Unit 2
For example, assume you declare a column VARCHAR2 with a maximum size of 50
characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2
column value in a particular row, the column in the row's row piece stores only the 10
characters.
3. NUMBER(P,S): The NUMBER datatype stores fixed and floating-point numbers. They are upto
38 digits of precision. For numeric columns, you can specify the column as: column_name
NUMBER
Optionally, number can also specify a precision (total number of digits) and scale (number of
digits to the right of the decimal point): column_name NUMBER (precision, scale)
If a precision is not specified, the column stores values as given. If no scale is specified, the scale
is zero.
4. DATE: The DATE datatype stores point-in-time values (dates and times) in a table. The DATE
datatype stores the year (including the century), the month, the day, the hours, the minutes, and
the seconds (after midnight). For input and output of dates, the standard Oracle date format is
DD-MON-YY e.g. 13-AUG-89
5. LONG: This data type is used to store variable length character strings containing upto 2
GB.LONG data can be used to store arrays of binary data in ASCII format.
CREATE TABLE Command
The CREATE TABLE statement is used to create a new table in the database.
This command defines each column of the table uniquely . Each column has a minimum of three
attributes a name, datatype and size. Each column has a specific data type which specifies how
data is stored in the column. Each column definition is separated from the other by a comma and
the SQL statement is terminated with a semicolon.
The general format of CREATE command is
Syntax:
BCA III SEM Page 2
,DATABASE MANAGEMENT SYSTEM Unit 2
CREATE TABLE tablename
(column1 data type(size),
column2 data type(size),
column3 data type(size),
….
column-n data type(size));
Example:
a. Create a table called student that contains roll number, name and marks in three subjects
SQL> create table student
(rollno number(2),
name varchar2(20),
m1 number(2),
m2 number(2),
m3 number(2));
Table created.
Inserting data into tables
INSERT: The INSERT statement is used to insert records into a table. This statement loads the
table with data to be manipulated later. When inserting a single row into the table the insert
operation does the following:
Creates a new row in the database table
Loads the values passed into the columns specified
Note: Character data type values must be enclosed in single quotes(‘).
Syntax:
INSERT INTO <table name> (column1, column2,…., column n)
VALUES(<expression1>, <expression2>,…..<expression n>);
BCA III SEM Page 3
, DATABASE MANAGEMENT SYSTEM Unit 2
Example:
Method 1
SQL> insert into student(rollno,name,m1,m2,m3) values (11,'Rama',65,75,50);
1 row created.
Method 2
SQL> insert into student values(&rollno,'&name',&m1,&m2,&m3);
Enter value for rollno: 12
Enter value for name: Raju
Enter value for m1: 70
Enter value for m2: 80
Enter value for m3: 65
old 1: insert into student values(&rollno,'&name',&m1,&m2,&m3)
new 1: insert into student values(12,'Raju',70,80,65)
1 row created.
Method 3
insert into student values (11,'Rama',65,75,50);
1 row created
Viewing data in the table
SELECT : Once data is inserted into the tables, the next logical operation would be to view the
data that has been inserted. The SELECT verb in SQL is used to achieve this. The SELECT
command is used to retrieve rows from one or more table.
Syntax:
a. To select all rows and all columns
SELECT * FROM <table name>;
b. To retrieve selected columns and all rows
SELECT column1, column2 FROM <table name>;
BCA III SEM Page 4
CH-1 INTERACTIVE SQL
Table Fundamentals
A table is database object that holds user data. Each column of the table will have a specific data
type bound to it. Oracle ensures that only data , which is identical to the data type of the column
,will be stored within the column.
Data types in Oracle
Oracle supports the following data types.
1. Char
2. Varchar /Varchar2
3. Number
4. Date
5. Long
1. CHAR :The CHAR datatype stores fixed-length character strings. When you create a table with
a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000
bytes for the CHAR column width. The default is 1 byte. Oracle then guarantees that: When you
insert or update a row in the table, the value for the CHAR column has the fixed length.
If you give a shorter value, then the value is blank-padded to the fixed length.
If a value is too large, Oracle Database returns an error.
2. VARCHAR2: The VARCHAR2 data type stores variable-length character strings. When you
create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or
characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle
Database stores each value in the column as a variable-length field unless a value exceeds the
column's maximum length, in which case Oracle Database returns an error. Using VARCHAR2
and VARCHAR saves on space used by the table.
BCA III SEM Page 1
,DATABASE MANAGEMENT SYSTEM Unit 2
For example, assume you declare a column VARCHAR2 with a maximum size of 50
characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2
column value in a particular row, the column in the row's row piece stores only the 10
characters.
3. NUMBER(P,S): The NUMBER datatype stores fixed and floating-point numbers. They are upto
38 digits of precision. For numeric columns, you can specify the column as: column_name
NUMBER
Optionally, number can also specify a precision (total number of digits) and scale (number of
digits to the right of the decimal point): column_name NUMBER (precision, scale)
If a precision is not specified, the column stores values as given. If no scale is specified, the scale
is zero.
4. DATE: The DATE datatype stores point-in-time values (dates and times) in a table. The DATE
datatype stores the year (including the century), the month, the day, the hours, the minutes, and
the seconds (after midnight). For input and output of dates, the standard Oracle date format is
DD-MON-YY e.g. 13-AUG-89
5. LONG: This data type is used to store variable length character strings containing upto 2
GB.LONG data can be used to store arrays of binary data in ASCII format.
CREATE TABLE Command
The CREATE TABLE statement is used to create a new table in the database.
This command defines each column of the table uniquely . Each column has a minimum of three
attributes a name, datatype and size. Each column has a specific data type which specifies how
data is stored in the column. Each column definition is separated from the other by a comma and
the SQL statement is terminated with a semicolon.
The general format of CREATE command is
Syntax:
BCA III SEM Page 2
,DATABASE MANAGEMENT SYSTEM Unit 2
CREATE TABLE tablename
(column1 data type(size),
column2 data type(size),
column3 data type(size),
….
column-n data type(size));
Example:
a. Create a table called student that contains roll number, name and marks in three subjects
SQL> create table student
(rollno number(2),
name varchar2(20),
m1 number(2),
m2 number(2),
m3 number(2));
Table created.
Inserting data into tables
INSERT: The INSERT statement is used to insert records into a table. This statement loads the
table with data to be manipulated later. When inserting a single row into the table the insert
operation does the following:
Creates a new row in the database table
Loads the values passed into the columns specified
Note: Character data type values must be enclosed in single quotes(‘).
Syntax:
INSERT INTO <table name> (column1, column2,…., column n)
VALUES(<expression1>, <expression2>,…..<expression n>);
BCA III SEM Page 3
, DATABASE MANAGEMENT SYSTEM Unit 2
Example:
Method 1
SQL> insert into student(rollno,name,m1,m2,m3) values (11,'Rama',65,75,50);
1 row created.
Method 2
SQL> insert into student values(&rollno,'&name',&m1,&m2,&m3);
Enter value for rollno: 12
Enter value for name: Raju
Enter value for m1: 70
Enter value for m2: 80
Enter value for m3: 65
old 1: insert into student values(&rollno,'&name',&m1,&m2,&m3)
new 1: insert into student values(12,'Raju',70,80,65)
1 row created.
Method 3
insert into student values (11,'Rama',65,75,50);
1 row created
Viewing data in the table
SELECT : Once data is inserted into the tables, the next logical operation would be to view the
data that has been inserted. The SELECT verb in SQL is used to achieve this. The SELECT
command is used to retrieve rows from one or more table.
Syntax:
a. To select all rows and all columns
SELECT * FROM <table name>;
b. To retrieve selected columns and all rows
SELECT column1, column2 FROM <table name>;
BCA III SEM Page 4
