Database Mangement Systems lab

VISWAJYOTHI COLLEGE OF ENGINEERING AND

TECHNOLOGY, VAZHAKULAM

VISION
Moulding Engineers par Excellence with integrity, fairness and human values

MISSION

 We commit to develop the institution as a Center of Excellence of International Standards.

 We guide our students in the attainment of intellectual and professional competence for suc-
cessfully coping with the rapid advancements in technologies and the ever changing world of
business, industry and services.
 We help each and every student in their personal growth into mature and responsible indi-
viduals.
 We strive to cultivate a sense of social and civic responsibility in our students, thus empower-
ing them to serve the humanity.
 We promise to ensure a free environment where quest for the truth is encouraged.

Department of Computer Science and Engineering, VJCET 1

Database Mangement Systems lab

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

VISION
Moulding socially responsible and professionally competent Computer Engineers to adapt to the
dynamic technological landscape.

MISSION
 Foster the principles and practices of computer science to empower life-long learning and build
careers in software and hardware development.
 Impart value education to elevate students to be successful, ethical and effective problem-solvers
to serve the needs of the industry, government, society and the scientific community.
 Promote industry interaction to pursue new technologies in Computer Science and provide excel-
lent infrastructure to engage faculty and students in scholarly research activities.

Department of Computer Science and Engineering, VJCET 2

Database Mangement Systems lab

DEPARTMENT OF COMPUTER SCIENCE AND

ENGINEERING

PROGRAM EDUCATIONAL OBJECTIVES

1. A graduate must be a proficient computer connoisseur able to solve a wide range of computing-related
problems.
2. To equip graduates with integrity and ethical values so that they become responsible Engineers.
3. Apply computer science knowledge to application areas from science and industry.
4. A graduate must have the background and perspective necessary to pursue post-graduate education

PROGRAM OUTCOMES
1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering funda-
mentals, and an engineering specialization to the solution of complex engineering problems.
2. Problem analysis: Identify, formulate, review research literature, and analyze complex engin-
eering problems reaching substantiated conclusions using first principles of mathematics, natural
sciences, and engineering sciences.
3. Design / development of solutions: Design solutions for complex engineering problems and
design system components or processes that meet the specified needs with appropriate considera-
tion for the public health and safety, and the cultural, societal, and environmental considerations.
4. Conduct investigations of complex problems: Use research-based knowledge and research
methods including design of experiments, analysis and interpretation of data, and synthesis of the
information to provide valid conclusions.
5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern en-
gineering and IT tools including prediction and modeling to complex engineering activities with
an understanding of the limitations.
6. The engineer and society: Apply reasoning informed by the contextual knowledge to assess so-
cietal, health, safety, legal and cultural issues and the consequent responsibilities relevant to the
professional engineering practice.
7. Environment and sustainability: Understand the impact of the professional engineering solu-
tions in societal and environmental contexts, and demonstrate the knowledge of, and need for
sustainable development.

Department of Computer Science and Engineering, VJCET 3

Database Mangement Systems lab

8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities and
norms of the engineering practice.
9. Individual and team work: Function effectively as an individual, and as a member or leader in
diverse teams, and in multidisciplinary settings.
10. Communication: Communicate effectively on complex engineering activities with the engineer-
ing community and with society at large, such as, being able to comprehend and write effective
reports and design documentation, make effective presentations, and give and receive clear in-
structions.
11. Project management and finance: Demonstrate knowledge and understanding of the engineer-
ing and management principles and apply these to ones own work, as a member and leader in a
team, to manage projects and in multidisciplinary environments.
12. Life-long learning : Recognize the need for, and have the preparation and ability to engage in
independent and life-long learning in the broadest context of technological change.

PROGRAM SPECIFIC OUTCOMES

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

 Ability to integrate theory and practice to construct software systems of varying complexity.
 Able to Apply Computer Science skills, tools and mathematical techniques to analyze, design
and model complex systems.
 Ability to design and manage small-scale projects to develop a career in a related industry.

Department of Computer Science and Engineering, VJCET 4

Database Mangement Systems lab

CSL 333Database Mangement Systems lab

INTRODUCTION

Oracle has many tools such as SQL * PLUS, Oracle Forms, Oracle Report Writer, Oracle
Graphics etc.
 SQL * PLUS: The SQL * PLUS tool is made up of two distinct parts. These are
 Interactive SQL: Interactive SQL is designed for create, access and manipulate data
structures like tables and indexes.
 PL/SQL: PL/SQL can be used to developed programs for different applications.
 Oracle Forms: This tool allows you to create a data entry screen along with the suitable
menu objects. Thus it is the oracle forms tool that handles data gathering and data validation
in a commercial application.
 Report Writer: Report writer allows programmers to prepare innovative reports using data
from the oracle structures like tables, views etc. It is the report writer tool that handles the re-
porting section of commercial application.
 Oracle Graphics: Some of the data can be better represented in the form of pictures. The or-
acle graphics tool allows programmers to prepare graphs using data from oracle structures
like tables, views etc.
SQL (Structured Query Language):
Structured Query Language is a database computer language designed for
managing data in relational database management systems(RDBMS), and originally based
upon Relational Algebra. Its scope includes data query and update, schema creation and
modification, and data access control. SQL was one of the first languages for Edgar F.
Codd's relational model in his influential 1970 paper, "A Relational Model of Data for Large Shared
Data Banks"[3] and became the most widely used language for relational databases.
 IBM developed SQL in mid of 1970’s.
 Oracle incorporated in the year 1979.
 SQL used by IBM/DB2 and DS Database Systems.
 SQL adopted as standard language for RDBS by ASNI in 1989.

Department of Computer Science and Engineering, VJCET 5

Database Mangement Systems lab

DATA TYPES:
CHAR (Size): This data type is used to store character strings values of fixed length. The size in
brackets determines the number of characters the cell can hold. The maximum number of character
is 255 characters.
1. VARCHAR (Size) / VERCHAR2 (Size): This data type is used to store variable length alpha-
numeric data. The maximum character can hold is 2000 character.
2. NUMBER (P, S): The NUMBER data type is used to store number (fixed or floating point).
Number of virtually any magnitude may be stored up to 38 digits of precision. Number as large

as 9.99 * 10 124. The precision (p) determines the number of places to the right of the decimal.
If scale is omitted then the default is zero. If precision is omitted, values are stored with their
original precision up to the maximum of 38 digits.
3. DATE: This data type is used to represent date and time. The standard format is DD-MM-YY
as in 17-SEP-2009. To enter dates other than the standard format, use the appropriate functions.
Date time stores date in the 24-Hours format. By default the time in a date field is 12:00:00 am,
if no time portion is specified. The default date for a date field is the first day the current month.
4. LONG: This data type is used to store variable length character strings containing up to 2GB.
Long data can be used to store arrays of binary data in ASCII format. LONG values cannot be
indexed, and the normal character functions such as SUBSTR cannot be applied.
5. RAW: The RAW data type is used to store binary data, such as digitized picture or image. Data
loaded into columns of these data types are stored without any further conversion. RAW data
type can have a maximum length of 255 bytes. LONG RAW data type can contain up to 2GB.
INTERACTIVE SQL:
Syntax : VERB(Parameter_1,Parameter_2,Parameter_3,........Parameter_n);
SQL language is sub-divided into several language elements, including:
 Clauses, which are in some cases optional, constituent components of statements and queries.
 Expressions, which can produce either scalar values or tables consisting of columns and
rows of data.
 Predicates which specify conditions that can be evaluated to SQL three-valued logic
(3VL) Boolean truth values and which are used to limit the effects of statements and queries,
or to change program flow.
 Queries which retrieve data based on specific criteria.
 Statements which may have a persistent effect on schemas and data, or which may control
transactions, program flow, connections, sessions, or diagnostics.
Department of Computer Science and Engineering, VJCET 6
Database Mangement Systems lab

 SQL statements also include the semicolon (";") statement terminator. Though not required on
every platform, it is defined as a standard part of the SQL grammar.
 Insignificant white space is generally ignored in SQL statements and queries, making it easier
to format SQL code for readability.
There are five types of SQL statements. They are:
1. DATA DEFINITION LANGUAGE (DDL)
2. DATA MANIPULATION LANGUAGE (DML)
3. DATA RETRIEVAL LANGUAGE (DRL)
4. TRANSATIONAL CONTROL LANGUAGE (TCL)
5. DATA CONTROL LANGUAGE (DCL)

1. DATA DEFINITION LANGUAGE (DDL): The Data Definition Language (DDL) is used to
create and destroy databases and database objects. These commands will primarily be used by
database administrators during the setup and removal phases of a database project. Let's take a look
at the structure and usage of four basic DDL commands:
1. CREATE 2. ALTER 3. DROP 4. RENAME
1. CREATE:
(a)CREATE TABLE: This is used to create a new relation and the corresponding
Syntax: CREATE TABLE relation_name
(field_1 data_type(Size),field_2 data_type(Size), .. . );
Example:
SQL>CREATE TABLE Student (sno NUMBER(3),sname CHAR(10),class CHAR(5));
(b)CREATE TABLE..AS SELECT....: This is used to create the structure of a new relation from
the structure of an existing relation.
Syntax: CREATE TABLE (relation_name_1, field_1,field_2,.....field_n) AS SELECT
field_1,field_2,...........field_n FROM relation_name_2;
Example: SQL>CREATE TABLE std(rno,sname) AS SELECT sno,sname FROM student;
2. ALTER:
(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.
Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2
data_type(size),..);
Example : SQL>ALTER TABLE std ADD(Address CHAR(10));

(b)ALTER TABLE...MODIFY...: This is used to change the width as well as data type of fields of
existing relations.

Department of Computer Science and Engineering, VJCET 7

Database Mangement Systems lab

Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size), field_2

newdata_type(Size),....field_newdata_type(Size));
Example:SQL>ALTER TABLE student MODIFY(sname VARCHAR(10),class VARCHAR(5));
3. DROP TABLE: This is used to delete the structure of a relation. It permanently deletes the
records in the table.
Syntax: DROP TABLE relation_name;
Example: SQL>DROP TABLE std;

4. RENAME: It is used to modify the name of the existing database object.

Syntax: RENAME TABLE old_relation_name TO new_relation_name;
Example: SQL>RENAME TABLE std TO std1;
5. TRUNCATE: This command will remove the data permanently. But structure will not be
removed.
Syntax: TRUNCATE TABLE <Table name>
Example TRUNCATE TABLE student;

Difference between Truncate & Delete:-

 By using truncate command data will be removed permanently & will not get back where as
by using delete command data will be removed temporally & get back by using roll back
command.
 By using delete command data will be removed based on the condition where as by using
truncate command there is no condition.
 Truncate is a DDL command & delete is a DML command.

2. DATA MANIPULATION LANGUAGE (DML): The Data Manipulation Language (DML) is

used to retrieve, insert and modify database information. These commands will be used by all
database users during the routine operation of the database. Let's take a brief look at the basic DML
commands:
1. INSERT 2. UPDATE 3. DELETE
1. INSERT INTO: This is used to add records into a relation. These are three type of INSERT
INTO queries which are as
a) Inserting a single record
Syntax: INSERT INTO relationname(field_1,field_2,.field_n)VALUES
(data_1,data_2,........data_n);
Example: SQL>INSERT INTO student(sno,sname,class,address)VALUES
(1,’Ravi’,’M.Tech’,’Palakol’);

Department of Computer Science and Engineering, VJCET 8

Database Mangement Systems lab

b) Inserting all records from another relation

Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n
FROM relation_name_2 WHERE field_x=data;
Example: SQL>INSERT INTO std SELECT sno,sname FROM student
WHERE name = ‘Ramu‘;
c) Inserting multiple records
Syntax: INSERT INTO relation_name field_1,field_2,.....field_n) VALUES
(&data_1,&data_2,........&data_n);
Example: SQL>INSERT INTO student(sno,sname,class,address)
VALUES(&sno,’&sname’,’&class’,’&address’);
Enter value for sno: 101
Enter value for name: Ravi
Enter value for class: M.Tech
Enter value for name: Palakol
2. UPDATE-SET-WHERE: This is used to update the content of a record in a relation.
Syntax: SQL>UPDATE relation name SET Field_name1=data,field_name2=data,
WHERE field_name=data;
Example: SQL>UPDATE student SET sname = ‘kumar’ WHERE sno=1;
3. DELETE-FROM: This is used to delete all the records of a relation but it will retain the structure
of that relation.
a) DELETE-FROM: This is used to delete all the records of relation.
Syntax: SQL>DELETE FROM relation_name;
Example: SQL>DELETE FROM std;
b) DELETE -FROM-WHERE: This is used to delete a selected record from a relation.
Syntax: SQL>DELETE FROM relation_name WHERE condition;
Example: SQL>DELETE FROM student WHERE sno = 2;
3. DRL(DATA RETRIEVAL LANGUAGE): Retrieves data from one or more tables.
1. SELECT FROM: To display all fields for all records.
Syntax : SELECT * FROM relation_name;
Example : SQL> select * from dept;
DEPTNO DNAME LOC
-------- ----------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Department of Computer Science and Engineering, VJCET 9

Database Mangement Systems lab

2. SELECT FROM: To display a set of fields for all records of relation.

Syntax: SELECT a set of fields FROM relation_name;
Example: SQL> select deptno, dname from dept;
DEPTNO DNAME
------- ----------
10 ACCOUNTING
20 RESEARCH
30 SALES
3. SELECT - FROM -WHERE: This query is used to display a selected set of fields for a selected
set of records of a relation.
Syntax: SELECT a set of fields FROM relation_name WHERE condition;
Example: SQL> select * FROM dept WHERE deptno<=20;
DEPTNO DNAME LOC
------ ----------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
4. SELECT - FROM -GROUP BY: This query is used to group to all the records in a relation
together for each and every value of a specific key(s) and then display them for a selected set of
fields the relation.
Syntax: SELECT a set of fields FROM relation_name GROUP BY field_name;
Example: SQL> SELECT EMPNO, SUM (SALARY) FROM EMP GROUP BY EMPNO;
EMPNO SUM (SALARY)
------ ----------
1 3000
2 4000
3 5000
4 6000
4 rows selected.
5. SELECT - FROM -ORDER BY: This query is used to display a selected set of fields from a
relation in an ordered manner base on some field.
Syntax: SELECT a set of fields FROM relation_name
ORDER BY field_name;
Example: SQL> SELECT empno,ename,job FROM emp ORDER BY job;
EMPNO ENAME JOB
------ --------- --------
4 RAVI MANAGER

Department of Computer Science and Engineering, VJCET 10

Database Mangement Systems lab

2 aravind Manager
1 sagar clerk
3 Laki clerk
4rows selected.
6. JOIN using SELECT - FROM - ORDER BY: This query is used to display a set of fields from
two relations by matching a common field in them in an ordered manner based on some fields.
Syntax: SELECT a set of fields from both relations FROM relation_1, relation_2 WHERE
relation_1.field_x = relation_2.field_y ORDER BY field_z;
Example: SQL>SELECT empno,ename,job,dname FROM emp,dept
WHERE emp.deptno = 20 ORDER BY job;
EMPNO ENAME JOB DNAME
------ ------ ------- ----------
7788 SCOTT ANALYST ACCOUNTING
7902 FORD ANALYST ACCOUNTING
------
7566 JONES MANAGER OPERATIONS
7566 JONES MANAGER SALES
20 rows selected.
7. JOIN using SELECT - FROM - GROUP BY: This query is used to display a set of fields from
two relations by matching a common field in them and also group the corresponding records for each
and every value of a specified key(s) while displaying.
Syntax: SELECT a set of fields from both relations FROM relation_1,relation_2 WHERE
relation_1.field-x=relation_2.field-y GROUP BY field-z;
Example: SQL> SELECT empno,SUM(SALARY) FROM emp,dept
WHERE emp.deptno =20 GROUP BY empno;
EMPNO SUM (SALARY)
------- --------
7369 3200
7566 11900
7788 12000
7876 4400
8. UNION: This query is used to display the combined rows of two different queries, which are
having the same structure, without duplicate rows.
Syntax: SELECT field_1,field_2,....... FROM relation_1 WHERE (Condition) UNION
SELECT field_1,field_2,....... FROM relation_2 WHERE (Condition);
Example:

Department of Computer Science and Engineering, VJCET 11

Database Mangement Systems lab

SQL> SELECT * FROM STUDENT;

SNO SNAME
----- -------
1 kumar
2 ravi
3 ramu

SQL> SELECT * FROM STD;

SNO SNAME
----- -------
3 ramu
5 lalitha
9 devi
1 kumar

SQL> SELECT * FROM student UNION SELECT * FROM std;

SNO SNAME
---- ------
1 kumar
2 ravi
3 ramu
5 lalitha
9 devi
9. INTERSET: This query is used to display the common rows of two different queries, which are
having the same structure, and to display a selected set of fields out of them.
Syntax: SELECT field_1,field_2,.. FROM relation_1 WHERE
(Condition) INTERSECT SELECT field_1,field_2,.. FROM relation_2 WHERE(Condition);
Example : SQL> SELECT * FROM student INTERSECT SELECT * FROM std;
SNO SNAME
---- -------
1 Kumar
10. MINUS: This query is used to display all the rows in relation_1,which are not having in the
relation_2.
Syntax: SELECT field_1,field_2,......FROM relation_1
WHERE(Condition) MINUS SELECT field_1,field_2,.....
FROM relation_2 WHERE(Conditon);

Department of Computer Science and Engineering, VJCET 12

Database Mangement Systems lab

SQL> SELECT * FROM student MINUS SELECT * FROM std;

SNO SNAME
---- -------
2 RAVI
3 RAMU
3. TRANSATIONAL CONTROL LANGUAGE (T.C.L):
A transaction is a logical unit of work. All changes made to the database can be referred to as
a transaction. Transaction changes can be mode permanent to the database only if they are
committed a transaction begins with an executable SQL statement & ends explicitly with either role
back or commit statement.

1. COMMIT: This command is used to end a transaction only with the help of the commit
command transaction changes can be made permanent to the database.

Syntax: SQL>COMMIT;
Example: SQL>COMMIT;
2. SAVE POINT: Save points are like marks to divide a very lengthy transaction to smaller once.
They are used to identify a point in a transaction to which we can latter role back. Thus, save point is
used in conjunction with role back.

Syntax: SQL>SAVE POINT ID;

Example: SQL>SAVE POINT xyz;
3. ROLE BACK: A role back command is used to undo the current transactions. We can role back
the entire transaction so that all changes made by SQL statements are undo (or) role back a
transaction to a save point so that the SQL statements after the save point are role back.
Syntax: ROLE BACK( current transaction can be role back)
ROLE BACK to save point ID;

Example: SQL>ROLE BACK;

SQL>ROLE BACK TO SAVE POINT xyz;
4. DATA CONTROL LANGUAGE (D.C.L):
DCL provides uses with privilege commands the owner of database objects (tables), has the
soul authority ollas them. The owner (data base administrators) can allow other data base uses to
access the objects as per their requirement
1. GRANT: The GRANT command allows granting various privileges to other users and allowing
them to perform operations with in their privileges

Department of Computer Science and Engineering, VJCET 13

Database Mangement Systems lab

For Example, if a uses is granted as ‘SELECT’ privilege then he/she can only view data but cannot
perform any other DML operations on the data base object GRANTED privileges can also be
withdrawn by the DBA at any time
Syntax: SQL>GRANT PRIVILEGES on object_name To user_name;
Example: SQL>GRANT SELECT, UPDATE on emp To hemanth;
2. REVOKE: To with draw the privileges that has been GRANTED to a uses, we use the REVOKE
command

Syntax: SQL>REVOKE PRIVILEGES ON object-name FROM user_name;

Example: SQL>REVOKE SELECT, UPDATE ON emp FROM ravi;
1. Creation, altering and dropping of tables and inserting rows into a table (use constraints
while creating tables) examples using SELECT command.
1. CREATE:
(a)CREATE TABLE: This is used to create a new relation
Syntax: CREATE TABLE relation_name
(field_1 data_type(Size),field_2 data_type(Size), .. . );

Example:
SQL>CREATE TABLE Student (sno NUMBER(3) PRIMARY KEY ,sname
CHAR(10),class CHAR(5));
2. ALTER:
(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.
Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2
data_type(size),..);
Example : SQL>ALTER TABLE std ADD(Address CHAR(10));
(b)ALTER TABLE...MODIFY...: This is used to change the width as well as data type of fields of
existing relations.
Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size), field_2
newdata_type(Size),....field_newdata_type(Size));
Example: SQL>ALTER TABLE student MODIFY(sname VARCHAR(10),class
VARCHAR(5));
3. DROP TABLE: This is used to delete the structure of a relation. It permanently deletes the
records in the table.
Syntax: DROP TABLE relation_name;
Example: SQL>DROP TABLE std;
4. INSERT:

Department of Computer Science and Engineering, VJCET 14

Database Mangement Systems lab

Syntax: INSERT INTO relation_name field_1,field_2,.....field_n) VALUES

(&data_1,&data_2,........&data_n);
Example: SQL>INSERT INTO student(sno,sname,class,address)
VALUES(&sno,’&sname’,’&class’,’&address’);
Enter value for sno: 101
Enter value for name: SIRISHA
Enter value for class: CSE
Enter value for address: Palakol

5. SELECT FROM: To display all fields for all records.

Syntax : SELECT * FROM relation_name;
Example : SQL> select * from student;
SNO SNAME CLASS ADDRESS
---- -------- ------ -----
101 SIRISHA CSE PALAKOL
102 DEVAKI CSE NARSAPUR
103 KUMAR CAD BHIMAVARAM
104 RAVI VLSI PALAKOL
2. SELECT FROM: To display a set of fields for all records of relation.
Syntax: SELECT a set of fields FROM relation_name;
Example: SQL> select sno, sname from student;
SNO SNAME
---- --------
101 SIRISHA
102 DEVAKI
103 KUMAR
104 RAVI
3. SELECT - FROM -WHERE: This query is used to display a selected set of fields for a selected
set of records of a relation.
Syntax: SELECT a set of fields FROM relation_name WHERE condition;
Example: SQL> select * FROM student WHERE class=’CSE’;
SNO SNAME CLASS ADDRESS
---- -------- ------ -------
101 SIRISHA CSE PALAKOL
102 DEVAKI CSE NARSAPUR
There are 5 constraints available in ORACLE:

Department of Computer Science and Engineering, VJCET 15

Database Mangement Systems lab

1. NOT NULL: When a column is defined as NOTNULL, then that column becomes a mandatory
column. It implies that a value must be entered into the column if the record is to be accepted for
storage in the table.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) NOT NULL, );
Example:
CREATE TABLE student (sno NUMBER(3)NOT NULL, name CHAR(10));
2. UNIQUE: The purpose of a unique key is to ensure that information in the column(s) is unique
i.e. a value entered in column(s) defined in the unique constraint must not be repeated across the
column(s). A table may have many unique keys.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) UNIQUE, ….);
Example:
CREATE TABLE student (sno NUMBER(3) UNIQUE, name CHAR(10));
3. CHECK: Specifies a condition that each row in the table must satisfy. To satisfy the constraint,
each row in the table must make the condition either TRUE or unknown (due to a null).
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical expression), ….);
Example: CREATE TABLE student (sno NUMBER (3), name CHAR(10),class
CHAR(5),CHECK(class IN(‘CSE’,’CAD’,’VLSI’));
4. PRIMARY KEY: A field which is used to identify a record uniquely. A column or combination
of columns can be created as primary key, which can be used as a reference from other tables. A
table contains primary key is known as Master Table.
 It must uniquely identify each record in a table.
 It must contain unique values.
 It cannot be a null field.
 It cannot be multi port field.
 It should contain a minimum no. of fields necessary to be called unique.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) PRIMARY KEY, ….);
Example:
CREATE TABLE faculty (fcode NUMBER(3) PRIMARY KEY, fname CHAR(10));
5. FOREIGN KEY: It is a table level constraint. We cannot add this at column level. To reference
any primary key column from other table this constraint can be used. The table in which the foreign
key is defined is called a detail table. The table that defines the primary key and is referenced by the
foreign key is called the master table.

Department of Computer Science and Engineering, VJCET 16

Database Mangement Systems lab

Syntax: CREATE TABLE Table_Name(column_name data_type(size)

FOREIGN KEY(column_name) REFERENCES table_name);
Example:
CREATE TABLE subject (scode NUMBER (3) PRIMARY KEY,
subname CHAR(10),fcode NUMBER(3),
FOREIGN KEY(fcode) REFERENCE faculty );
Defining integrity constraints in the alter table command:
Syntax: ALTER TABLE Table_Name ADD PRIMARY KEY (column_name);
Example: ALTER TABLE student ADD PRIMARY KEY (sno);
(Or)
Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name
PRIMARY KEY(colname)
Example: ALTER TABLE student ADD CONSTRAINT SN PRIMARY KEY(SNO)
Dropping integrity constraints in the alter table command:
Syntax: ALTER TABLE Table_Name DROP constraint_name;
Example: ALTER TABLE student DROP PRIMARY KEY;
(or)
Syntax: ALTER TABLE student DROP CONSTRAINT constraint_name;
Example: ALTER TABLE student DROP CONSTRAINT SN;

3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY,
HAVING and Creation and dropping of Views.

Aggregative operators: In addition to simply retrieving data, we often want to perform some
computation or summarization. SQL allows the use of arithmetic expressions. We now consider a
powerful class of constructs for computing aggregate values such as MIN and SUM.
1. Count: COUNT following by a column name returns the count of tuple in that column. If
DISTINCT keyword is used then it will return only the count of unique tuple in the column.
Otherwise, it will return count of all the tuples (including duplicates) count (*) indicates all the
tuples of the column.
Syntax: COUNT (Column name)
Example: SELECT COUNT (Sal) FROM emp;
2. SUM: SUM followed by a column name returns the sum of all the values in that column.
Syntax: SUM (Column name)
Example: SELECT SUM (Sal) From emp;
3. AVG: AVG followed by a column name returns the average value of that column values.

Department of Computer Science and Engineering, VJCET 17

Database Mangement Systems lab

Syntax: AVG (n1,n2..)

Example: Select AVG(10, 15, 30) FROM DUAL;
4. MAX: MAX followed by a column name returns the maximum value of that column.
Syntax: MAX (Column name)
Example: SELECT MAX (Sal) FROM emp;
SQL> select deptno,max(sal) from emp group by deptno;
DEPTNO MAX(SAL)
------ --------
10 5000
20 3000
30 2850
SQL> select deptno,max(sal) from emp group by deptno having max(sal)<3000;
DEPTNO MAX(SAL)
----- --------
30 2850
5. MIN: MIN followed by column name returns the minimum value of that column.
Syntax: MIN (Column name)
Example: SELECT MIN (Sal) FROM emp;
SQL>select deptno,min(sal) from emp group by deptno having min(sal)>1000;
DEPTNO MIN(SAL)
----- --------
10 1300
VIEW: In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as
if the data were coming from one single table.

A view is a virtual table, which consists of a set of columns from one or more tables. It is
similar to a table but it doest not store in the database. View is a query stored as an object.
Syntax: CREATE VIEW view_name AS SELECT set of fields FROM relation_name
WHERE (Condition)
1. Example:
SQL>CREATE VIEW employee AS SELECT empno,ename,job FROM EMP
WHERE job = ‘clerk’;
View created.
Department of Computer Science and Engineering, VJCET 18
Database Mangement Systems lab

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME JOB
---- ------ -------
7369 SMITH CLERK
7876 ADAMS CLERK
7900 JAMES CLERK
7934 MILLER CLERK
2.Example:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
DROP VIEW: This query is used to delete a view , which has been already created.
Syntax: DROP VIEW View_name;
Example : SQL> DROP VIEW EMPLOYEE;
View dropped
4. Queries using Conversion functions (to_char, to_number and to_date), string functions
(Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date
functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc,
round, to_char, to_date)

1. Conversion functions:
To_char: TO_CHAR (number) converts n to a value of VARCHAR2 data type, using the optional
number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or
BINARY_DOUBLE.
SQL>select to_char(65,'RN')from dual;
LXV
To_number : TO_NUMBER converts expr to a value of NUMBER data type.
SQL> Select to_number('1234.64') from Dual;
1234.64
To_date: TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type
to a value of DATE data type.
SQL>SELECT TO_DATE('January 15, 1989, 11:00 A.M.')FROM DUAL;
TO_DATE
---------
15-JAN-89

Department of Computer Science and Engineering, VJCET 19

Database Mangement Systems lab

2. String functions:
Concat: CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the
datatypes
SQL>SELECT CONCAT(‘ORACLE’,’CORPORATION’)FROM DUAL;
ORACLECORPORATION
Lpad: LPAD returns expr1, left-padded to length n characters with the sequence of characters
in expr2.
SQL>SELECT LPAD(‘ORACLE’,15,’*’)FROM DUAL;
*********ORACLE
Rpad: RPAD returns expr1, right-padded to length n characters with expr2, replicated as many
times as necessary.
SQL>SELECT RPAD (‘ORACLE’,15,’*’)FROM DUAL;
ORACLE*********
Ltrim: Returns a character expression after removing leading blanks.
SQL>SELECT LTRIM(‘SSMITHSS’,’S’)FROM DUAL;
MITHSS
Rtrim: Returns a character string after truncating all trailing blanks
SQL>SELECT RTRIM(‘SSMITHSS’,’S’)FROM DUAL;
SSMITH
Lower: Returns a character expression after converting uppercase character data to lowercase.
SQL>SELECT LOWER(‘DBMS’)FROM DUAL;
dbms
Upper: Returns a character expression with lowercase character data converted to uppercase
SQL>SELECT UPPER(‘dbms’)FROM DUAL;
DBMS
Length: Returns the number of characters, rather than the number of bytes, of the given string
expression, excluding trailing blanks.
SQL>SELECT LENGTH(‘DATABASE’)FROM DUAL;
8
Substr: Returns part of a character, binary, text, or image expression.
SQL>SELECT SUBSTR(‘ABCDEFGHIJ’3,4)FROM DUAL;
CDEF
Instr: The INSTR functions search string for substring. The function returns an integer indicating
the position of the character in string that is the first character of this occurrence.
SQL>SELECT INSTR('CORPORATE FLOOR','OR',3,2)FROM DUAL;
14

Department of Computer Science and Engineering, VJCET 20

Database Mangement Systems lab

3. Date functions:
Sysdate:
SQL>SELECT SYSDATE FROM DUAL;
29-DEC-08
next_day:
SQL>SELECT NEXT_DAY(SYSDATE,’WED’)FROM DUAL;
05-JAN-09
add_months:
SQL>SELECT ADD_MONTHS(SYSDATE,2)FROM DUAL;
28-FEB-09
last_day:
SQL>SELECT LAST_DAY(SYSDATE)FROM DUAL;
31-DEC-08
months_between:
SQL>SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)FROM EMP;
4
Least:
SQL>SELECT LEAST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Greatest:
SQL>SELECT GREATEST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Trunc:
SQL>SELECT TRUNC(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
Round:
SQL>SELECT ROUND(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
to_char:
SQL> select to_char(sysdate, "dd\mm\yy") from dual;
24-mar-05.
to_date:
SQL> select to_date(sysdate, "dd\mm\yy") from dual;
24-mar-05.

Department of Computer Science and Engineering, VJCET 21

Database Mangement Systems lab

Department of Computer Science and Engineering, VJCET 22

Database Mangement Systems lab

LIST OF EXPERIMENTS

Department of Computer Science and Engineering, VJCET 23