1022 Silva 29 Taman Imbi 29 male Indian hall of residence
22 rows selected.
SQL> select * from apartment;
AID ROOM_TYPE PLACE_NO ROOM_NO cost
--- ------------- -------- ------- ------
A1 A-Group by3 A-01 101-3 RM 150
A2 A-Group by4 A-01 101-4 RM 130
A3 A-Group by5 A-01 101-5 RM 100
A4 A-Group by3 A-02 201-3 RM 150
A5 A-Group by4 A-02 201-4 RM 130
A6 A-Group by5 A-02 201-5 RM 100
A7 A-Group by3 A-03 301-3 RM 150
A8 A-Group by4 A-03 301-4 RM 130
A9 A-Group by5 A-03 301-5 RM 100
A10 A-Group by3 A-04 401-3 RM 150
10 rows selected.
SQL> select * from hall;
HID ROOM_TYPE PLACE_NO ROOM_NO cost
--- ------------- -------- ------- ------
H1 H-Single room H-01 101 RM 80
H2 H-Single room H-01 102 RM 80
H3 H-Single room H-01 103 RM 80
H4 H-Single room H-01 104 RM 80
H5 H-Single room H-01 105 RM 80
H6 H-Single room H-01 106 RM 80
H7 H-Single room H-01 107 RM 80
H8 H-Single room H-01 108 RM 80
H9 H-Single room H-01 109 RM 80
H10 H-Single room H-01 110 RM 80
10 rows selected.
SQL> select * from lease;
LID SID DURATION PLACE_NO ROOM_NO
--- ----- ---------- -------- -------
L1 1001 1 Year A-01 101-5
L2 1002 1 Year A-01 101-5
L3 1003 1 Year A-01 101-5
L4 1004 1 Year A-01 101-5
L5 1005 1 Year A-01 101-5
L6 1006 1 Semester A-01 101-4
L7 1007 1 Semester A-01 101-4
L8 1008 1 Semester A-01 101-4
L9 1009 1 Semester A-01 101-4
L10 1010 1 Year A-01 101-3
L11 1011 1 Year A-01 101-3
LID SID DURATION PLACE_NO ROOM_NO
--- ----- ---------- -------- -------
L12 1012 1 Year A-01 101-3
L13 1013 1 Year H-01 101
L14 1014 1 Semester H-01 102
L15 1015 1 Year H-01 103
L16 1016 1 Semester H-01 104
L17 1017 1 Year H-01 105
L18 1018 1 Year H-01 106
L19 1019 1 Semester H-01 107
L20 1020 1 Semester H-01 108
L21 1021 1 Year H-01 109
L22 1022 1 Year H-01 110
22 rows selected.
SQL>
d) Produce SQL listings on the 3 specific questions (given below) based _ on Case Study.
- List all the different rooms in order of cost.
SQL> select distinct a.room_type,a.monthly_rent_rate ,b.room_type,b.monthly_rent_rate
2 from apartment a,hall b where a.room_type=b.room_type(+)
3 union
4 select distinct a.room_type,a.monthly_rent_rate ,b.room_type,b.monthly_rent_rate
5 from apartment a,hall b where a.room_type(+)=b.room_type;
ROOM_TYPE cost ROOM_TYPE cost
------------- ------ ------------- ------
A-Group by3 RM 150
A-Group by4 RM 130
A-Group by5 RM 100
H-Single room RM 80
- List the student name, room number and lease details.
SQL> select a.sname,b.* from student a,lease b where a.sid =b.sid;
SNAME LID SID DURATION PLACE_NO ROOM_NO
---------- --- ----- ---------- -------- -------
JH low L1 1001 1 Year A-01 101-5
Khar Fai L2 1002 1 Year A-01 101-5
Kenny Lee L3 1003 1 Year A-01 101-5
Eugune Yap L4 1004 1 Year A-01 101-5
Sam Low L5 1005 1 Year A-01 101-5
Suntheran L6 1006 1 Semester A-01 101-4
Lloyd L7 1007 1 Semester A-01 101-4
Mohamad L8 1008 1 Semester A-01 101-4
Alice Tew L9 1009 1 Semester A-01 101-4
Lina Koh L10 1010 1 Year A-01 101-3
Mindy Aw L11 1011 1 Year A-01 101-3
SNAME LID SID DURATION PLACE_NO ROOM_NO
---------- --- ----- ---------- -------- -------
Karena Ng L12 1012 1 Year A-01 101-3
Chris Lee L13 1013 1 Year H-01 101
Alex Wong L14 1014 1 Semester H-01 102
Chifford L15 1015 1 Year H-01 103
Jenny Khoo L16 1016 1 Semester H-01 104
Ken Ng L17 1017 1 Year H-01 105
Kogilar L18 1018 1 Year H-01 106
Jacky Tan L19 1019 1 Semester H-01 107
Sabrina L20 1020 1 Semester H-01 108
Ben Lee L21 1021 1 Year H-01 109
Silva L22 1022 1 Year H-01 110
- List the Room number, the apartment it is in, and the students who are living in those rooms.
select a.room_no,b.sname from lease a,student b where a.sid=b.sid and place_no like'A%';
ROOM_NO SNAME
------- ----------
101-5 JH low
101-5 Khar Fai
101-5 Kenny Lee
101-5 Eugune Yap
101-5 Sam Low
101-4 Suntheran
101-4 Lloyd
101-4 Mohamad
101-4 Alice Tew
101-3 Lina Koh
101-3 Mindy Aw
ROOM_NO SNAME
------- ----------
101-3 Karena Ng
e) 10 Different SQL Statements
- SQL> insert into student
2 values(1001,'JH low','23 Taman Bangi',21,'male','chinese','apartment');
1 row created.
English
THE INSERT INTO Statement
The INSERT INTO statement is used to insert new rows into a table
Insert Student ID, Student Name, Address, Sex,
Nationality, Room type in to Student table.
…………………………………………………………………………………………………………
- SQL> delete from student where sid=1023;
1 row deleted.
English
Delete a record from the System Database
Delete from student table where student ID is 1023
…………………………………………………………………………………………………………
- SQL> select sid,sname,sage,sex from student where room_type='apartment';
SID SNAME SAGE SEX
----- ---------- ---- ------
1001 JH low 21 male
1002 Khar Fai 20 male
1003 Kenny Lee 22 male
1004 Eugune Yap 23 male
1005 Sam Low 26 male
1006 Suntheran 27 male
1007 Lloyd 29 male
1008 Mohamad 26 male
1009 Alice Tew 20 female
1010 Lina Koh 19 female
1011 Mindy Aw 21 female
English
List the student ID,student name,student age and sex where the room type is apartment
- SQL> select sid,duration,room_no from lease;
SID DURATION ROOM_N
----- ---------- ------
1001 1 Year 101-5
1002 1 Year 101-5
1003 1 Year 101-5
1004 1 Year 101-5
1005 1 Year 101-5
1006 1 Semester 101-4
1007 1 Semester 101-4
1008 1 Semester 101-4
1009 1 Semester 101-4
1010 1 Year 101-3
1011 1 Year 101-3
SID DURATION ROOM_N
----- ---------- ------
1012 1 Year 101-3
1013 1 Year 101
1014 1 Semester 102
1015 1 Year 103
1016 1 Semester 104
1017 1 Year 105
1018 1 Year 106
1019 1 Semester 107
1020 1 Semester 108
1021 1 Year 109
1022 1 Year 110
22 rows selected.
English
List all the student id, duration and room number from lease table
…………………………………………………………………………………………………………
- SQL> update student
2 set saddress='8 Jalan Rawang'
3 where sid=1003;
1 row updated.
English
Update student detail set student new address where student ID is 1003
- SQL> select sname,sage,room_type from student order by sname;
SNAME SAGE ROOM_TYPE
---------- ---- -----------------
Alex Wong 23 hall of residence
Alice Tew 20 apartment
Ben Lee 29 hall of residence
Chifford 24 hall of residence
Chris Lee 24 hall of residence
Eugune Yap 23 apartment
JH low 21 apartment
Jacky Tan 24 hall of residence
Jenny Khoo 24 hall of residence
Karena Ng 24 apartment
Ken Ng 23 hall of residence
SNAME SAGE ROOM_TYPE
---------- ---- -----------------
Kenny Lee 28 apartment
Khar Fai 20 apartment
Kogilar 22 hall of residence
Lina Koh 19 apartment
Lloyd 29 apartment
Mindy Aw 21 apartment
Mohamad 26 apartment
Sabrina 20 hall of residence
Sam Low 26 apartment
Silva 29 hall of residence
Suntheran 27 apartment
22 rows selected.
English
List all the name and age and room type from student table order them by sname
…………………………………………………………………………………………………………
- SQL> select a.duration,b.sname
2 from lease a,student
3 b where a.sid=b.sid and room_no='101-5';
DURATION SNAME
---------- ----------
1 Year JH low
1 Year Khar Fai
1 Year Kenny Lee
1 Year Eugune Yap
1 Year Sam Low
English
List all the student name and duration from student table where student living in room no 101-5
- SQL> select a.room_no,b.sname
2 from lease a,student b
3 where a.sid=b.sid and duration='1 Semester';
ROOM_NO SNAME
------- ----------
101-4 Suntheran
101-4 Lloyd
101-4 Mohamad
101-4 Alice Tew
102 Alex Wong
104 Jenny Khoo
107 Jacky Tan
108 Sabrina
8 rows selected.
English
List student name and room No.where duration is 1 semester
…………………………………………………………………………………………………………
- SQL> select sid,sname,sage,room_type from student where sname like'A%';
SID SNAME SAGE ROOM_TYPE
--- ---------- ---- -----------------
009 Alice Tew 20 apartment
014 Alex Wong 23 hall of residence
English
List student name ,age and room type from student where name bigin with ‘A’
…………………………………………………………………………………………………………
- SQL> select * from student where sage between 20 and 23
SID SNAME SADDRESS SAGE SEX NATIONALITY ROOM_TYPE
----- ---------- ----------------- ---- ------ ----------- -----------------
1001 JH low 23 Taman Bangi 21 male chinese apartment
1002 Khar Fai 28 Taman Kepong 20 male chinese apartment
1004 Eugune Yap 76 Taman Kepong 23 male chinese apartment
1009 Alice Tew 88 Taman Rawang 20 female chinese apartment
1011 Mindy Aw 77 Taman Kuantan 21 female chinese apartment
1014 Alex Wong 12 Taman Istana 23 male chinese hall of residence
1017 Ken Ng 4 Taman Bintang 23 male chinese hall of residence
1018 Kogilar 38 Taman Bangsa 22 female indian hall of residence
1020 Sabrina 34 Taman Kepong 20 female indian hall of residence
9 rows selected.
English
List all the student detail from where student age between 20 to 23
Appendix
Reference
