TABLE CREATION:
Design a student database with the following tables:
str(regno, name, addr, city)
SQL>create table stu12(regno char(10) primary key, name varchar2(25)not null, addr varchar2(20), city
varchar(10));
Table created.
cou(courseid, cname, staffid)
SQL>create table cou(courseid varchar2(10), cname varchar2(25), staffid number);
Table created.
staff(deptid, deptname, staffid, staffname)
SQL> create table staff(deptid number, deptname varchar2(30), staffid number, staffname varchar(30));
Table created.
mark(regno, courseid, sub1, sub2, sub3, sub4, sub5)
SQL>create table mark(regno char(10), courseid varchar2(30), sub1 number, sub2 number, sub3 number, sub4
number, sub5 number);
Table created.
CONSTRAINTS ADDED TO TABLES:
SQL> alter table staff add constraint staff10_pk primary key(staffid, deptid);
Table altered.
SQL> alter table mark add constraint chk10_mks check(sub1>0 and sub1<100);
Table altered.
SQL> alter table mark add constraint chk20_mks check(sub2>0 and sub2<100);
Table altered.
SQL> alter table mark add constraint chk30_mks check(sub3>0 and sub3<100);
Table altered.
SQL> alter table mark add constraint chk40_mks check(sub4>0 and sub4<100);
Table altered.
SQL> alter table mark add constraint chk50_mks check(sub5>0 and sub5<100);
Table altered.
SQL> alter table mark add constraint fok_rno foreign key(regno) references stu12(regno) on delete cascade;
Table altered.
SQL> alter table cou add constraint cot_course primary key(courseid);
Table altered.
SQL> alter table mark add constraint for_cid foreign key(courseid) references cou(courseid);
Table altered.
TABLE DESCRIPTION:
SQL> desc stu12;
Name Null? Type
----------------------------- ----------------- ----------------------------
REGNO NOT NULL CHAR(10)
NAME NOT NULL VARCHAR2(25)
ADDR VARCHAR2(20)
CITY VARCHAR2(10)
SQL> desc cou;
Name Null? Type
----------------------------------------- ------------------ ----------------------------
COURSEID NOT NULL VARCHAR2(10)
CNAME VARCHAR2(25)
STAFFID NUMBER
SQL> desc mark;
Name Null? Type
----------------------------------------- -------- ----------------------------
REGNO CHAR(10)
COURSEID VARCHAR2(30)
SUB1 NUMBER
SUB2 NUMBER
SUB3 NUMBER
SUB4 NUMBER
SUB5 NUMBER
SQL> desc staff;
Name Null? Type
----------------------------------------- --------------- ----------------------------
DEPTID NOT NULL NUMBER
DEPTNAME VARCHAR2(30)
STAFFID NOT NULL NUMBER
STAFFNAME VARCHAR2(30)
INSERTING VALUE IN STU12 TABLE:
SQL> insert into stu12 values('®no', '&name', '&addr', '&city');
Enter value for regno: 01
Enter value for name: anu
Enter value for addr: mtp road
Enter value for city: cbe
old 1: insert into stu12 values('®no', '&name', '&addr', '&city')
new 1: insert into stu12 values('01', 'anu', 'mtp road', 'cbe')
1 row created.
SQL> /
Enter value for regno: 02
Enter value for name: banu
Enter value for addr: rs puram
Enter value for city: chennai
old 1: insert into stu12 values('®no', '&name', '&addr', '&city')
new 1: insert into stu12 values('02', 'banu', 'rs puram', 'chennai')
1 row created.
SQL> /
Enter value for regno: 03
Enter value for name: ram
Enter value for addr: ganapathy
Enter value for city: trichy
old 1: insert into stu12 values('®no', '&name', '&addr', '&city')
new 1: insert into stu12 values('03', 'ram', 'ganapathy', 'trichy')
1 row created.
INSERTING VALUES IN COU TABLE:
SQL> insert into cou values('1011', 'java', 9100);
1 row created.
SQL> insert into cou values('1022', 'Graphics', 9200);
1 row created.
SQL> insert into cou values('1033', 'php', 9300);
1 row created.
INSERTING VALUES TO STAFF TABLE:
SQL> insert into staff values(20, 'computer science', 9200, 'rajesh');
1 row created.
SQL> insert into staff values(20, 'computer science', 9300, 'arthi');
1 row created.
SQL> insert into staff values(15, 'chemistry', 9100, 'kala');
1 row created.
INSERTING VALUES INTO MARK TABLE:
SQL> insert into mark values('01', '1011', 76,90,56,40,89);
1 row created.
SQL> insert into mark values('02', '1022', 85,95,90,96,87);
1 row created.
SQL> insert into mark values('03', '1033', 78,89,90,97,96);
1 row created.
DISPLAYING TABLE CONTENTS:
SQL> select * from stu12;
REGNO NAME ADDR CITY
-------------- -------------------- -------------------- ----------
01 anu mtp road cbe
02 banu rs puram chennai
03 ram ganapathy trichy
SQL> select * from cou;
COURSEID CNAME STAFFID
---------- ------------------------- -------------
1011 java 9100
1022 Graphics 9200
1033 php 9300
SQL> select * from staff;
DEPTID DEPTNAME STAFFID STAFFNAME
---------- ------------------------ ------------- ------------------------
20 computer science 9200 rajesh
20 computer science 9300 arthi
15 chemistry 9100 kala
SQL> select * from mark;
REGNO COURSEID SUB1 SUB2 SUB3 SUB4 SUB5
---------- ------------------------------ ---------- ---------- --------- ---------- ----------
01 1011 76 90 56 40 89
02 1022 85 95 90 96 87
03 1033 78 89 90 97 96
DML QUERIES:
a. Display the details of all the Staff in all courses with Staff ID, Name, Dept Name, and group by
course.
SQL> create or replace view studview1 as(select * from staff join cou using(staffid));
View created.
SQL> select * from studview1;
STAFFID DEPTID DEPTNAME STAFFNAME COURSEID CNAME
---------- ---------- ----------------- --------------------- -------------- ------------------
9100 15 chemistry kala 1011 java
9200 20 computer science rajesh 1022 Graphics
9300 20 computer science arthi 1033 php
b. Delete a student from stu12 table.
Before Deletion:
SQL> delete from stu12 where regno=®no;
Enter value for regno: 03
old 1: delete from stu12 where regno=®no
new 1: delete from stu12 where regno=03
1 row deleted.
After Deletion:
SQL> select * from stu12;
REGNO NAME ADDR CITY
---------- ----------------- -------------------- ------------
01 anu mtp road cbe
02 banu rs puram Chennai
c. Update a record in stu12 table.
SQL> update stu12 set name='brintha' where regno='01';
1 row updated.
SQL> select * from stu12;
REGNO NAME ADDR CITY
---------- --------------------- -------------------- ------------------
01 brintha mtp road cbe
02 banu rs puram Chennai
CREATE A CURSOR TO DISPLAY THE DETAILS OF STUDENTS WHO HAS PASSED WITH
DISTINCTION.
SQL> set serveroutput on
SQL> declare
2 cursor distinctions is
3 select *
4 from stu12 where regno
5 in(select regno
6 from mark
7 where((sub1+sub2+sub3+sub4+sub5)/5)>75);
8 begin
9 for dr in distinctions
10 loop
11 dbms_output.put_line(dr.regno||','||dr.name||',
12 '||dr.addr||','||dr.city);
13 end loop;
14 end;
15 /
02 ,banu, rs puram,chennai
PL/SQL procedure successfully completed.
Comments