TABLE OF CONTENTS

Page
Exp.NO. Date Title of the Experiment Marks Signature
no.
EX.NO: 01

DATA DEFINITION LANGUAGE (DDL)

DATE:

AIM:

To create a table using Data Definition Language (DDL).

DDL COMMANDS:

1. Create
2. Drop
3. Alter
4. Truncate
5. Comment
6. Rename

CREATE A TABLE:
SQL> create table Students (ROLL_NO number(3), NAME varchar(20), SUBJECT varchar(20));

Table created.

INSERTING INTO TABLE:

SQL> insert into Students(ROLL_NO,NAME,SUBJECT)values(100,'ARUNA','DBMS');

1 row created.

SQL> insert into Students(ROLL_NO,NAME,SUBJECT)values(101,'Gayatri','DBMS');

1 row created.
SQL> insert into Students(ROLL_NO,NAME,SUBJECT)values(102,'Aashi','OOPs');

1 row created.

SQL> select * from Students;

ALTER TABLE:

1. ADD A COLUMN INTO THE TABLE:

SQL> alter table Students add DEPARTMENT varchar(5);

Table altered.

SQL> update Students set DEPARTMENT = 'MCA' where ROLL_NO = '100';

1 row updated.

SQL> update Students set DEPARTMENT = 'MCA' where ROLL_NO = '101';

1 row updated.
SQL> update Students set DEPARTMENT = 'MCA' where ROLL_NO = '102';

1 row updated.

SQL> select * from Students;

2. MODIFY THE COLUMN IN THE TABLE:

SQL> alter table Students modify DEPARTMENT varchar(12);

Table altered.

SQL> select * from Students;

3. DROP THE COLUMN FROM THE TABLE:

SQL> alter table Students drop column SUBJECT;

Table altered.

SQL> select * from Students;

RENAME:

SQL> alter table Students rename to student;

Table altered.

SQL> select * from student;

TRUNCATE:

SQL> truncate table student;

Table truncated.
SQL> select * from student;

DROP THE TABLE:

SQL> drop table student;

Table dropped.
RESULT:

EX.NO: 2

DATA MANIPULATION LANGUAGE (DML)

DATE:

AIM:
To modify a table using Data Manipulation Language (DML).

DML COMMANDS:

1. Insert
2. Update
3. Delete

CREATE TABLE:

SQL> create table Customer_Table (CUS_ID number(5)primary key,CUS_NAME varchar(15)

NOT NULL,PH_NUM number(10)UNIQUE,CUS_PLACE varchar(15) DEFAULT 'PONDICHERRY');

Table created.

1. INSERT:
SQL> insert into Customer_table
(CUS_ID,CUS_NAME,PH_NUM)values(500,'ARUNA',8300053166);

1 row created.

SQL> insert into Customer_table

(CUS_ID,CUS_NAME,PH_NUM)values(501,'AASHI',7865899399);

1 row created.

SQL> insert into Customer_table

(CUS_ID,CUS_NAME,PH_NUM)values(502,'GAYATRI',987654329);

1 row created.

SQL> select * from Customer_Table;

2. UPDATE:

SQL> alter table Customer_Table add AMT_PURCHASED number(10);

Table altered.
SQL> update Customer_Table set AMT_PURCHASED = '5000' where CUS_ID = '500';

1 row updated.

SQL> update Customer_Table set AMT_PURCHASED = '4500' where CUS_ID = '501';

1 row updated.

SQL> update Customer_Table set AMT_PURCHASED = '6700' where CUS_ID = '502';

1 row updated.

SQL> select * from Customer_Table;

3. DELETE A ROW FROM THE TABLE:

SQL> delete from Customer_Table where CUS_NAME ='ARUNA';

1 row deleted.
SQL> select * from Customer_Table;

RESULT:

EX.NO: 3

DATA CONTROL LANGUAGE (DCL)

DATE:

AIM:

To store and retrieve a table using data through Data Control Language (DCL).

DCL COMMANDS:

1. Grant
2. Revoke

CREATING A TABLE:

SQL> create table employee2(e_id number(5),e_name varchar(20),dob date,salary

number(10)
INSERTING INTO TABLE:

SQL> insert into employee2(e_id,e_name,dob,salary,designation)values(800,'aruna','20-jan-

2000',80000);

SQL> insert into employee2(e_id,e_name,dob,salary,designation)values(801,'aashi','20-may-

2000',90000);

SQL> select * from employee2;

CREATION OF NEW USER:

SQL> create user students1 identified by smvec;

SHOW USER:
SQL> show user;

GRANT:

SQL> grant create session to students1;

SQL> grant select on employee2 to students1;

REVOKE:

SQL> revoke select on employee2 from students1;

CONNECTING TO A NEW USER:

SQL> connect

Enter user-name: students1

Enter password:

RESULT:

EX.NO: 4

CONSTRAINTS AND BUILT – IN FUNCTIONS

DATE:

AIM:

To implement a table using constraints and built - in functions.

CREATING A TABLE:
SQL> create table CUS(C_ID number(5) primary key,C_NAME varchar(20) not null,DEPT
varchar(8),DOB date,SALARY number(10));
INSERTING INTO TABLE:

SQL> insert into CUS(C_ID,C_NAME,DEPT,DOB,SALARY)values(400,'ARUNA','FINANCE','20-

jan-2000','80000');

SQL> insert into CUS(C_ID,C_NAME,DEPT,DOB,SALARY)values(401,'AASHI','ECE','04-FEB-

2000','68000');

SQL> insert into CUS(C_ID,C_NAME,DEPT,DOB,SALARY)values(402,'GAYATRI','MECH','02-apr-

1999','85000');

SQL> insert into CUS(C_ID,C_NAME,DEPT,DOB,SALARY)values(403,'PREETHI','ARCHI','09-

may-1999','76000');

SQL> select * from CUS;

BUILT – IN FUNCTIONS:

SQL> select max(SALARY)from CUS;

SQL> select min(SALARY)from CUS;

SQL> select count(SALARY)from CUS;

SQL> select sum(SALARY)from CUS;

SQL> select avg(SALARY)from CUS;

RESULT:
 EX.NO: 5

JOINS AND GROUP BY FUNCTIONS

DATE:

AIM:

To create a table and perform joins and group by functions.

JOINS:

1. Inner join
2. Natural join
3. Left outer join
4. Right outer join
5. Full outer join

TABLE 1:

CREATING A TABLE:
SQL> create table emp(E_NO number(5),E_NAME varchar(20));

INSERTING INTO TABLE:

SQL> insert into emp(E_NO,E_NAME)values(100,'ARUNA');

SQL> insert into emp(E_NO,E_NAME)values(101,'AASHI');

SQL> insert into emp(E_NO,E_NAME)values(102,'PREETHI');

SQL> select * from emp;

TABLE 2:
CREATING A TABLE:
SQL> create table emp1(E_NO number(5),E_RANK number(5),PH_NO number(10));

INSERTING INTO TABLE:

SQL> insert into emp1(E_NO,E_RANK,PH_NO)values(100,1,8300053166);

SQL> insert into emp1(E_NO,E_RANK,PH_NO)values(102,2,7896543232);

SQL> insert into emp1(E_NO,E_RANK,PH_NO)values(103,3,9832477493);

SQL> select * from emp1;

INNER JOIN:
SQL> select * from emp inner join emp1 on emp.E_NO = emp1.E_NO;

NATURAL JOIN:

SQL> select * from emp natural join emp1;

LEFT OUTER JOIN:

SQL> select * from emp left outer join emp1 on emp.E_NO = emp1.E_NO;

RIGHT OUTER JOIN:

SQL> select * from emp right outer join emp1 on emp.E_NO = emp1.E_NO;
FULL OUTER JOIN:

SQL> select * from emp full outer join emp1 on emp.E_NO = emp1.E_NO;

GROUP BY FUNCTIONS:
CREATING A TABLE:
SQL> create table book(B_ID number(5),B_NAME varchar(12),AUTHOR_NAME varchar(15),PUB
varchar(10),year number(5),price number(10));

INSERTING INTO TABLE:

SQL> insert into
book(B_ID,B_NAME,AUTHOR_NAME,PUB,YEAR,PRICE)values(001,'JAVA','BALA','PEARSON',200
0,500);

SQL> insert into

book(B_ID,B_NAME,AUTHOR_NAME,PUB,YEAR,PRICE)values(002,'C++','STROUSTRUP','PEARS
ON',1989,800);
SQL> insert into
book(B_ID,B_NAME,AUTHOR_NAME,PUB,YEAR,PRICE)values(003,'DBMS','SUDHARSHAN','PHI',
2002,450);

SQL> insert into

book(B_ID,B_NAME,AUTHOR_NAME,PUB,YEAR,PRICE)values(004,'C','YASHWANTH','MCHILL',2
010,250);

SQL> select * from book;

SQL> update book set AUTHOR_NAME = 'BALA' where B_ID = '4';

SQL> select * from book;

SQL> select AUTHOR_NAME,sum(PRICE)"TOTAL" from book group by AUTHOR_NAME;

RESULT:

EX.NO: 6

SIMPLE PROGRAMS IN SQL

DATE:

AIM:
To implement a table using simple programs in sql.

CREATING A TABLE:
SQL> create table emp(E_ID int not null,E_NAME varchar(12)not null,AGE int not null,SALARY
number(10));

INSERTING INTO TABLE:

SQL> insert into emp(E_ID,E_NAME,AGE,SALARY)values(800,'ARUNA',21,80000);

SQL> insert into emp(E_ID,E_NAME,AGE,SALARY)values(801,'AASHI',22,83000);

SQL> insert into emp(E_ID,E_NAME,AGE,SALARY)values(802,'PREETHI',23,67000);

SQL> select * from emp;

PROGRAMS:
SQL> declare

2 name varchar(12);

3 salary number;

4 begin

5 select E_NAME,salary into name,SALARY from emp

6 where E_ID = 802;

7 dbms_output.put_line('Customer:'||name);

8 dbms_output.put_line('Salary:'||salary);

9 end;

10 /

OUTPUT:
WHILE LOOP:
SQL> declare

2 n number;

3 i number:=0;

4 begin

5 dbms_output.put_line('WHILE LOOP');

6 n:=&n;

7 while i<=n

8 loop

9 dbms_output.put_line(i);

10 i:=i+2;

11 end loop;

12 end;

13 /

Enter value for n: 7

old 6: n:=&n;

new 6: n:=7;

OUTPUT:
FOR LOOP:

SQL> declare

2 n number;

3 begin

4 dbms_output.put_line('FOR LOOP');

5 n:=&n;

6 for i in 1..n

7 loop

8 dbms_output.put_line(i);

9 end loop;

10 end;

11 /

Enter value for n: 8

old 5: n:=&n;

new 5: n:=8;

OUTPUT:
RESULT:

EX.NO: 7

SQL PROGRAMS USING FUNCTIONS

DATE:

AIM:
To create a table using functions.

CREATING A TABLE:
SQL> create table emp(e_id int not null,e_name varchar(10)not null,age int not null,salary
number);

INSERTING INTO TABLE:

SQL> insert into emp(e_id,e_name,age,salary)values(800,'aruna',21,80000);
SQL> insert into emp(e_id,e_name,age,salary)values(801,'aashi',22,87000);

SQL> insert into emp(e_id,e_name,age,salary)values(802,'preethi',23,78000);

SELECT:
SQL> select * from emp;

CREATING A FUNCTION:
SQL> create or replace function func(s number)

2 return number is

3 salary number;

4 begin

5 select salary into salary from emp

6 where e_id = 801;

7 return salary;

8 end;

9 /
OUTPUT:

CALLING A FUNCTION:
SQL> declare

2 salary number;

3 e_no number;

4 begin

5 e_no := &e_no;

6 salary := func(e_no);

7 dbms_output.put_line('Salary: '||salary);

8 end;

9 /

OUTPUT:
RESULT:

EX.NO: 8

SQL PROGRAMS USING PROCEDURES

DATE:

AIM:

To create a table and to implement the procedures.

CREATING A TABLE:

SQL> create table product(p_id number,p_name varchar(20),p_available varchar(15));

INSERTING INTO TABLE:

SQL> insert into product(p_id,p_name,p_available)values(300,'chocolates','100 packs');

SQL> insert into product(p_id,p_name,p_available)values(301,'shampoo','250 packs');

SQL> insert into product(p_id,p_name,p_available)values(301,'cereals','50 packs');

SQL> select * from product;

PROGRAM:

SQL> create or replace procedure p_available(x number)as

2 p_id varchar2(15);

3 s_name varchar2(15);

4 begin

5 select p_available into p_id

6 from product where p_id = x;

7 select p_name into s_name

 8 from product where p_id = x;

9 dbms_output.put_line('Product Name:'||s_name);

10 dbms_output.put_line('Product:'||p_id);

11 end;

12 /

OUTPUT:

RESULT:

EX.NO: 9

SQL PROGRAMS USING TRIGGERS

DATE:

AIM:
To create a table and implementing triggers.

TABLE 1:
CREATING A TABLE:

SQL> create table emp(e_id number,e_name varchar(10),dept varchar(8),salary number(8),exp

number(5));

INSERTING INTO TABLE:

SQL> insert into emp(e_id,e_name,dept,salary,exp)values(500,'aruna','FD',80000,5);

SQL> insert into emp(e_id,e_name,dept,salary,exp)values(500,'aashi','IT',65000,2);

SQL> insert into emp(e_id,e_name,dept,salary,exp)values(502,'preethi','IT',76000,1);

SQL> insert into emp(e_id,e_name,dept,salary,exp)values(503,'gayu','cse',89000,4);

SQL> select * from emp;

TABLE 2:
CREATING A TABLE:

SQL> create table emp1 (e_id number(3),e_name varchar(10),b_date date,e_salary

number(10));

INSERTING INTO TABLE:

SQL> insert into emp1(e_id,e_name,b_date,e_salary)values(500,'aruna','20-jan-2000',80000);

SQL> insert into emp1(e_id,e_name,b_date,e_salary)values(502,'preethi','17-may-2000',76000);

SQL> insert into emp1(e_id,e_name,b_date,e_salary)values(503,'gayu','02-apr-1999',89000);

SQL> select * from emp1;

PROGRAM:

CREATING TRIGGER:
SQL> create or replace trigger e_salary

2 after update of salary on emp

3 for each row

4 begin
 5 insert into emp1(e_id,e_name,b_date,e_salary)

6 values(:new.e_id,:new.e_name,sysdate,:new.salary);

7 end;

8 /

OUTPUT:

UPDATE:

SQL> update emp set salary = salary+2500.0 where exp>3 and exp<7;

SQL> select * from emp;

SQL> select * from emp1;

RESULT:
Ex No: 10

STUDENT INFORMATION SYSTEM

Date:

Aim: To create an application for Student Information System in Visual Basic.

Procedure:

1. Create a table exam with necessary attributes.

2. Design a form in VB with the necessary fields.

3. Using connection string establish connection to Oracle server through VB

application.

4. Write code for inserting, deleting, updating and viewing the records.

//Table creation

CREATE TABLE exam

(
id NUMBER,
name VARCHAR2(15),
gen VARCHAR2(6),
dep VARCHAR2(10),
yr NUMBER,
sems VARCHAR2(10),
sub1 NUMBER,
sub2 NUMBER,
sub3 NUMBER,
tot NUMBER,
cg NUMBER,
rest VARCHAR2(10)
);

//Form design

//Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
namespace exam
{
 public partial class Form1 : Form
{
OracleConnection conn = new OracleConnection("Data Source=XE;User

Id=system;Password=password;");
OracleCommand cmd;

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)

{
dept.Items.Add("IT");
dept.Items.Add("CSE");
dept.Items.Add("ECE");
dept.Items.Add("EEE");
dept.Items.Add("MECH");
sem.Items.Add("I");
sem.Items.Add("II");
sem.Items.Add("III");
sem.Items.Add("IV");
sem.Items.Add("V");
sem.Items.Add("VI");
sem.Items.Add("VII");
sem.Items.Add("VIII");
year.Items.Add("1");
year.Items.Add("2");
year.Items.Add("3");
year.Items.Add("4");
}

private void save_Click(object sender, EventArgs e)

{
try
{
String g;
if (male.Checked == true)
g = "male";
else
 g="female";
conn.Open();
cmd = new OracleCommand("Insert into

exam(id,name,gen,dep,yr,sems,sub1,sub2,sub3,tot,cg,rest) values('" +
regno.Text +
"','" + name.Text + "','"+dept.Text+"','"+g+"','"+year.Text+"',
'"+sem.Text+"','"+m1.Text+"','"+m2.Text+"','"+m3.Text+"',
'"+total.Text+"','"+cgpa.Text+"','"+res.Text+"')", conn);
MessageBox.Show("Records Inserted Successfully");
cmd.ExecuteNonQuery();
conn.Close();
}

catch (Exception ex)

{
MessageBox.Show(ex.Message);
}
}

private void select_Click(object sender, EventArgs e)

{
conn.Open();
cmd = new OracleCommand("select * from exam", conn);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["id"].ToString() == regno.Text)
{
string sname= dr["name"].ToString();
if (dr["gen"].ToString() == "male")
Convert.ToBoolean(male.Checked = true);
else
Convert.ToBoolean(female.Checked = true);
string sdept= dr["dep"].ToString();
string syear= dr["yr"].ToString();
string ssem = dr["sems"].ToString();
string sm1 = dr["sub1"].ToString();
string sm2= dr["sub2"].ToString();
string sm3 = dr["sub3"].ToString();
 string stot= dr["tot"].ToString();
string scgpa = dr["cg"].ToString();
string sres= dr["rest"].ToString();
name.Text = sname;
dept.Text=sdept;
year.Text=syear;
sem.Text=ssem;
m1.Text=sm1;
m2.Text=sm2;
m3.Text=sm3;
total.Text=stot;
cgpa.Text=scgpa;
res.Text = sres;
}
}
dr.Close();
cmd.Dispose();
conn.Dispose();
}
{
MessageBox.Show(ex.Message);
}
}

private void exit_Click(object sender, EventArgs e)

{
this.Close();
}

private void calc_Click(object sender, EventArgs e)

{
String rest;
int mar1, mar2, mar3, t;
float c;
mar1 = int.Parse(m1.Text);
mar2 = int.Parse(m2.Text);
mar3 = int.Parse(m3.Text);
t = mar1 +mar2 + mar3;
c = (t)/29 ;
 if (mar1>=50 && mar2>=50 && mar3 >= 50)
rest = "Pass";
else
rest = "Fail";
total.Text = t.ToString();
cgpa.Text = c.ToString();
res.Text = rest;
}

private void report_Click(object sender, EventArgs e)

{
Form2 f=new Form2();
f.Show();
}
}
}

private void update_Click(object sender, EventArgs e)

{
try
{
conn.Open();
cmd = new OracleCommand(" UPDATE exam set name='" + name.Text +
"'
WHERE id = '" + regno.Text + "'", conn);
MessageBox.Show("Records updated Successfully");
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

private void delete_Click(object sender, EventArgs e)

{
try
{
 conn.Open();
cmd = new OracleCommand(" DELETE from exam WHERE id = '" +
regno.Text + "'", conn);
MessageBox.Show("Records deleted Successfully");
cmd.ExecuteNonQuery();
conn.Close();
}

//Output

//Select

//Insert
//Select

//Delete
//Select

//Report
Result: