DBMS LAB 5
Database Schema for a student-Lab
scenario
Student(stud_no: integer, stud_name: string, class: string)
Class(class: string, descrip: string)
Lab(mach_no: integer, Lab_no:
integer, description: String)
Allotment(Stud_no: Integer, mach_no: integer, dayof
week: string)
For the above schema, perform the
following—
a)
Create
the tables with the appropriate integrity constraints
b)
Insert
around 10 records in each of the tables
c)
List
all the machine allotments with the student names, lab and machine numbers
d)
List
the total number of lab allotments day wise
e)
Give
a count of how many machines have been allocated to the ‘CSIT’ class
f)
Give
a machine allotment etails of the stud_no 5 with his personal and class details
g)
Count
for how many machines have been allocatedin Lab_no 1 for the day of the
week as “Monday”
h)
How
many students class wise have allocated machines in the labs
i)
Create
a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek
j)
Create
a view which lists the machine allotment details for “Thursday”
AIM:
Create the
tables with the appropriate integrity constraints
Insert around 10 records in each
of the tables
HW/SW
requirements:
Processor : AMD Athelon
™ 1.67 GHz
RAM : 256
MB
Hard Disk : 40 GB
Software : Oracle
SQL>create table stu(stud_no number(5) primary
key,stud_nam varchar2(20),class varchar2(20));
SQL> desc stu;
Name null? Type
STUD_NO NOT NULL NUMBER(5)
STUD_NAM
VARCHAR2(20)
CLASS
VARCHAR2(20)
Valid Data
Sets:
SQL> insert into stu
values(&stud_no,’&stud_nam’,’&class’);
SQL> select * from stu;
STUD_NO STUD_NAM CLASS
39 LEON
34 VIKAS CSIT
18 MATHEW ECE
8 HANSEN MECH
24 ALEXIS EEE
SQL> Create table class (class varchar2(20),
descript varchar2(10));
SQL> Describe class;
Name
null
type
CLASS VARCHAR2(10)
DESCRIPT VARCHAR2(20)
SQL> create table lab(match_no number(5), lab_no
number(5), description varchar2(20));
SQL> desc lab;
Name
null
type
MACH_NO NOT
NULL NUMBER(5)
LAB_NO NUMBER(5)
DESCRIPTION VARCHAR2(20)
SQL> insert into lab
values(&mach_no,&lab_no,’&description’);
SQL> select * from lab;
MATCH_NO LAB_NO DESCRIPTION
--------------- --------- --------------------
23 7 physics
78 2 chemistry
87 1 edc
12 10 cds
8 3 java lab
SQL> create table allotment(stud_no number(5)
references stu(stud_no), match_no number(5) references lab(mach_no),
Doweek varchar2(20));
SQL> desc allotment;
Name Null? Type
-------------- ------- ---------
STUD_NO NUMBER(5)
MACH_NO NUMBER(5)
DOWEEK VARCHAR2(20)
SQL>select * from allotment;
STUD_NO MACH_NO DOWEEK
------------- -- ------------ ------------
39 23 sat
34 87 mon
18 78 tue
8 12 wed
24 12 thu
c)
List
all the machine allotments with the student names, lab and machine numbers
SQL>select
s.studname,l.machno from student1 s,lab l,allotment a where a.machno=l.machno
and a.studno=s.studno;
STUDNAME
MACHNO
………………………………………..
ABHIJEET 1
KALYAN 22
ASHWIN 3
ARKA 4
ARVIND 5
d)
List
the total number of lab allotments day wise
SQL>select
l.machno,l.descrip,a.day from lab l,allotment a where a.machno=l.machno;
MACHNO DESCRIP
DAY
……………………………………………………………………
1 UNIX MONDAY
22 UNIX TUESDAY
3 XP
WEDNESDAY
4 WINDOWS THRUSDAY
5 ME
FRIDAY
e)
Give
a count of how many machines have been allocated to the ‘CSIT’ class
SQL>select
count(machno)from allotment where studno in(select studno from student1 where
class=’CSIT’);
COUNT (MACHNO)
……………………..
1
f)
Give
a machine allotment etails of the stud_no 5 with his personal and class details
SQL>select
a.studno,a.machno,s.studname,s.class from allotment a,student1 s where
a.studno=s.studno and a.studno=503;
STUDNO
MACHNO STUDNAME CLASS
………………………………………………………………………………………………………
503 5 ARVIND CSE
g)
Count
for how many machines have been allocatedin Lab_no 1 for the day of the
week as “Monday”
h)
How
many students class wise have allocated machines in the labs
SQL>select count(studno)
“allocated students in the labs”,class from student1 where studno in(select
studno from allotment) group by class;
allocated
students in the lab CLASS
……………………………………………………………………………
2 CSE
1 ECE
1 EEE
1 IT
i)
Create
a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek
j)
Create
a view which lists the machine allotment details for “Thursday”
Comments
Post a Comment