gtu mca 3 set 5 gtu

SET – 5

HOSTEL (HNO, HNAME, HADDR, TOTAL_CAPACITY, WARDEN)

create table hostel

(

hnonumber(10) primary key,

hname varchar2(20) NOT NULL,

haddr varchar2(20) NOT NULL,

total_capacitynumber(10),

warden varchar2(20)

);

HNO	HNAME	HADDR	TOTAL_CAPACITY	WARDEN
1	Tejash Patel	navsari	500	vidhi
2	Shashi Patel	surat	100	vidhi
3	Dhaval Shah	surat	500	kamini
4	Raju	bombay	500	pooja
5	viren	bombay	100	pooja

ROOM (HNO, RNO, RTYPE, LOCATION, NO_OF_STUDENTS, STATUS)

create table room

(

hnonumber(10) references hostel(hno),

rnonumber(10) not null,

rtype varchar2(10) not null,

          location varchar2(10) not null,

no_of_studentsnumber(5),

          status varchar2(20),

          primary key(hno,rno)

);

HNO	RNO	RTYPE	LOCATION	NO_OF_STUDENTS	STATUS
1	1	s	haj	500	vacant
2	1	f	haj	500	vacant
2	3	s	haj	500	vacant
2	2	t	haj	500	vacant
2	4	f	haj	500	vacant

CHARGES (HNO, RTYPE, CHARGES)

create table charges

(

hnonumber(10) references hostel(hno),

rtype varchar2(10) not null,

          charges number(10),

          primary key(hno,rtype)

);

HNO	RTYPE	CHARGES
1	d	5000
2	s	5000
3	s	5000

STUDENT (SID, SNAME, MOBILE-NO, GENDER, FACULTY, DEPT, CLASS, HNO, RNO)

create table student

(

sidnumber(10) primary key,

sname varchar2(20),

saddr varchar2(20),

          faculty varchar2(20),

          dept varchar2(10),

          class varchar2(10),

hnonumber(10) references hostel(hno),

rnonumber(10)

);

SID	SNAME	SADDR	FACULTY	DEPT	CLASS	HNO	RNO
1	meet	city light	Tejash sir	1	mca2	1	2
2	karan	Undhana road	Shashi sir	2	mca2	2	2
3	jay	city light	neha mam	3	mca2	3	2
4	sami	Undhana road	khusbhoo mam	2	mca2	4	2
5	kunal	city light	Tejash sir	3	mca2	5	2

FEES (SID, FDATE, FAMOUNT)

create table fees
(
          sidnumber(10),
          fdate date,
          famount number(10),
          primary key(sid,fdate)
);

SID	FDATE	FAMOUNT
1	02-OCT-18	500
2	08-OCT-18	500
3	12-OCT-18	500
4	22-OCT-18	500
5	27-OCT-18	500

Do as directed:

1.     Display the total number of rooms that are presently vacant.

select count(rno) from room where status = 'vacant';

COUNT(RNO)

5

2.     Display number of students of each faculty and department wise staying in each hostel.

select count(sid),faculty from student group by faculty;

COUNT(SID)	FACULTY
1	Shashi sir
2	Tejash sir
1	neha mam
1	khusbhoo mam

3.     Display hostels, which have at least one single-seated room.

select a.* from hostel a, room b

where b.hno = a.hno and b.rtype = 's';

HNO	HNAME	HADDR	TOTAL_CAPACITY	WARDEN
1	Tejash Patel	navsari	500	vidhi
2	Shashi Patel	surat	100	vidhi

4.     Display the warden name and hostel address of students of Computer Science department.

select warden, haddr from hostel a, student b

where b.hno = a.hno and b.dept = 3;

WARDEN	HADDR
kamini	surat
pooja	bombay

5.     Display those hostel details where single seated or double-seated rooms are vacant.

select * from hostel

where hno in (

select b.hno from hostel a, room b

where a.hno = b.hno and b.rtype in( 's','f') and status = 'vacant' group by b.hno

);

HNO	HNAME	HADDR	TOTAL_CAPACITY	WARDEN
1	Tejash Patel	navsari	500	vidhi
2	Shashi Patel	surat	100	vidhi

6.     Display details of hostels occupied by medical students.

select a.* from hostel a, room b

where b.hno = a.hno and b.status = 'occupied by medical';

HNO	HNAME	HADDR	TOTAL_CAPACITY	WARDEN
1	Tejash Patel	navsari	500	vidhi

7.     Display hostels, which are totally occupied to its fullest capacity.

select a.* from hostel a, room b

where b.hno = a.hno and b.status = 'occupied';

HNO	HNAME	HADDR	TOTAL_CAPACITY	WARDEN
1	Tejash Patel	navsari	500	vidhi

8.     List details about students who are staying in the double-seated rooms of Chanakya Hostel.

select * from student where hnoin(

select a.hno from hostel a, room b

where b.hno = a.hno and a.hname = 'Shashi Patel' and rtype = 'f'

);

SID	SNAME	SADDR	FACULTY	DEPT	CLASS	HNO	RNO
2	karan	Undhana road	Shashi sir	2	mca2	2	2

9.     Display the total number of students staying in each room type of each hostel.

select sum(no_of_students), rtype from room

group by rtype;

SUM(NO_OF_STUDENTS)	RTYPE
1000	s
500	t
2000	f

10.  Display details about students who have paid fees in the month of Nov. 2017.

select * from student where sidin(

select sid from fees where to_char(fdate, 'mon') = 'nov'

);

SID	SNAME	SADDR	FACULTY	DEPT	CLASS	HNO	RNO
5	kunal	city light	Tejash sir	3	mca2	5	2

11.  For those hostels where total capacity is more than 300, display details of students studying in Science faculty.

select a.*, b.* from student a, hostel b

where a.hno = b.hno and a.faculty = 'Tejash sir' and b.TOTAL_CAPACITY> 300

SID

SNAME

SADDR

FACULTY

DEPT

CLASS

HNO

RNO

HNO

HNAME

HADDR

TOTAL_CAPACITY

WARDEN

1

meet

city light

Tejashsir

1

mca2

1

2

1

Tejash Patel

navsari

500

vidhi

12.  Display hostel details where there are at least 10 vacant rooms.

select a.*, b.rtype from hostel a, room b

where a.hno = b.hno and b.status = 'vacant' and b.no_of_students>10;

HNO	HNAME	HADDR	TOTAL_CAPACITY	WARDEN	RTYPE
1	Tejash Patel	navsari	500	vidhi	s
2	Shashi Patel	surat	100	vidhi	f
2	Shashi Patel	surat	100	vidhi	s
2	Shashi Patel	surat	100	vidhi	t
2	Shashi Patel	surat	100	vidhi	f

13.  Display details of students who have still not paid fees.

select * from student

where sid not in (

select sid from fees where to_char(fdate, 'mon') = 'nov'

);

SID	SNAME	SADDR	FACULTY	DEPT	CLASS	HNO	RNO
1	meet	city light	Tejash sir	1	mca2	1	2
2	karan	Undhana road	Shashi sir	2	mca2	2	2
3	jay	city light	neha mam	3	mca2	3	2
4	sami	Undhana road	khusbhoo mam	2	mca2	4	2

14.  Display those hostels where single-seated room is the costliest.

select a.* from hostel a, charges b

where a.hno = b.hno and rtype = 's' and charges = (

select max(charges) from charges

);

HNO	HNAME	HADDR	TOTAL_CAPACITY	WARDEN
2	Shashi Patel	surat	100	vidhi
3	Dhaval Shah	surat	500	kamini

15.  Write a trigger which do not allow to insert or update student record if mobile_no length is less than 10 digits.

                                                                                                                     

16.  Write a PL/SQL block which will count total number of student’s gender wise.

Male Students: 999 students

Female Students: 999 students