IE 323
COMPUTER APPLICATION II [IE 323]
SAMPE MAJOR 1 Exam FOR SPRING 2021
MAX. TIME : 90 Min MAX. MARKS: 20
Name :_____________________________
Computer No :_____________________________
Attempt all the questions.
.
a) Member323
MNO, MNAME, DOB, M_HEAD_NO, Gender , EARNED_POINTS
b) COACH323
CNO, CNAME, CTYPE, PRICE_PER_HOUR, C_HEAD_NO
c) Booking323
MNO, CNO, RESERVE_DATE, RESERVE_ TIME , NO_OF_HOURS
d) MLEVEL323
MSTATUS , LPOINT , HOINT
CREATE TABLE Member323
( MNO NUMBER(6) constraint mem_323_pk primary key,
MNAME VARCHAR2(10),
DOB DATE,
M_HEAD_NO NUMBER(6),
Gender VARCHAR2(8),
EARNED_POINTS NUMBER(5))/
CREATE TABLE COACH456
(CNO NUMBER(6) constraint COA_323_pk primary key,
CNAME VARCHAR2(10),
CTYPE VARCHAR2(10),
PRICE_PER_HOUR NUMBER(6) ,
C_HEAD_NO NUMBER(6))
/
CREATE TABLE BOOKING323
(MNO NUMBER(6) CONSTRAINT FK_3BOOKING REFERENCES MEMBER323(MNO),
CNO NUMBER(6) CONSTRAINT FK2_3BOOKING REFERENCES COACH323(CNO),
RESERVE_DATE DATE ,
RESERVE_TIME NUMBER(6,2),
NO_OF_HOURS NUMBER(3))/
CREATE TABLE MLEVEL323
(MSTATUS VARCHAR2(10),
LPOINT NUMBER(5),
HPOINT NUMBER(5))
/
Page | 1 of 7
IE 323
Insert the following data in the database. Using script file (show the method only for MEMBER table).(1.5)
MEMBER323
MN GENDE
O MNAME DOB M_HEAD_NO R EARNED_POINTS
101 FAISAL 12-Jun-91 103 MALE 900
102 IRUM 9-Jan-84 103 FEMALE 1800
MALBAR
103 I 18-Feb-85 MALE 2700
21-Mar-
104 BELADI 80 103 MALE 1400
COACH323
PRICE_PER_HOU C_HEAD_N
CNO CNAME CTYPE R O
1000 SWIMMIN
1 AMMAR G 150 10002
1000 HISHAM
2 M BOXING 250
1000
3 REHAN BOXING 200 10002
1000
4 ATEF YOUGA 120 10002
BOOKING323
MN
O CNO RESERVE_DATE RESERVE_TIME NO_OF_HOURS
1000
102 1 17-Mar-19 13 2
1000
102 4 22-May-18 14.3 3
1000
101 1 28-May-18 9.3 4
1000
103 3 23-May-18 16 6
1000
102 2 29-May-18 13.3 7
MLEVEL323
MSTATU LPOIN HPOIN
S T T
YELLOW 0 999
SILVER 1000 1999
GOLD 2000 2999
INSERT INTO MEMBER323 (MNO, MNAME, Gender , M_HEAD_NO, DOB, EARNED_POINTS)
VALUES(&MNO, '&MNAME', '&Gender', &M_HEAD_NO,TO_DATE('&DOB','DD-MON-YYYY'),
&EARNED_POINTS)/
INSERT INTO COACH323 (CNO, CNAME, CTYPE, PRICE_PER_HOUR, C_HEAD_NO)
VALUES(&CNO, '&CNAME', '&CTYPE', &PRICE_PER_HOUR, &C_HEAD_NO)/
INSERT INTO BOOKING323 (MNO, CNO, RESERVE_DATE, RESERVE_TIME , NO_OF_HOURS )
VALUES(&MNO,&CNO, TO_DATE('&RESERVE_DATE','DD-MON-YYYY'), &RESERVE_TIME, &NO_OF_HOURS )
Page | 2 of 7
IE 323
INSERT INTO MLEVEL323 (MSTATUS , LPOINT , HOINT )
VALUES('&MSTATUS', &LPOINT , &HOINT )
SELECT [Link] , [Link], [Link], L. MSTATUS
FROM MEMBER323 M , COACH456 C, BOOKING456 B , MLEVEL456 L
WHERE [Link] = [Link] AND [Link] = [Link] AND M. EARNED_POINTS BETWEEN [Link] AND [Link]
1. Display the following columns:
MNAME, MEMBER MSTATUS , CNAME , COACH_HEAD_NAME , MEMBER_HEAD_NAME ,
MEMBER_HEAD_MSTATUS TOTAL_AMOUNT(NO_OF_HOURS * PRICE_PER_HOUR). Display
even those COACHS which don’t have Head
COACH_HEAD_NA TOTAL_AMOUN
MNAME MSTATUS CNAME M MEMBER_HEAD_NAME MEMBER_HEAD_STATUS T
IRUM SILVER AMMAR HISHAMM MALBARI GOLD 300
IRUM SILVER ATEF HISHAMM MALBARI GOLD 360
FAISAL YELLOW AMMAR HISHAMM MALBARI GOLD 600
HISHAM
IRUM SILVER M MALBARI GOLD 1750
SELECT [Link] , [Link], [Link] "M_HEAD_NAME", L. MSTATUS , [Link] "C_HEAD_NAME" ,
LH. MSTATUS "M_HEAD_STATUS",B.NO_OF_HOURS* C.PRICE_PER_HOUR "TOTAL_AMOUNT"
FROM MEMBER456 M , MEMBER456 MH,COACH456 C, BOOKING456 B , MLEVEL456 L , COACH456 CH ,
MLEVEL456 LH
WHERE [Link] = [Link] AND [Link] = [Link] AND M.EARNED_POINTS BETWEEN [Link] AND [Link]
AND M.M_HEAD_NO = [Link] AND [Link](+) = C.C_HEAD_NO AND
MH. EARNED_POINTS BETWEEN [Link] AND [Link]
Page | 3 of 7
IE 323
2) Display the coach with lowest total sales.
CNAME MAXIMUM_TOTAL_COACH_PAYMENT
HISHAMM 1750
SELECT [Link], SUM(B.NO_OF_HOURS* C.PRICE_PER_HOUR) "MAXIMUM_TOTAL_COACHPAYMENT"
FROM MEMBER323 M , COACH323 C, BOOKING323 B , MLEVEL323 L
WHERE [Link] = [Link] AND [Link] = [Link] AND M.EARNED_POINTS BETWEEN [Link] AND [Link]
GROUP BY [Link]
HAVING SUM(B.NO_OF_HOURS* C.PRICE_PER_HOUR) =
(SELECT MAX(SUM(B.NO_OF_HOURS* C.PRICE_PER_HOUR) )
FROM MEMBER323 M , COACH323 C, BOOKING323 B , MLEVEL323 L
WHERE [Link] = [Link] AND [Link] = [Link] AND M.EARNED_POINTS BETWEEN [Link] AND [Link]
GROUP BY [Link])
3) Display all the Members who born on the same day of the week as his MEM_head was born and display their
AGE in years (round the age).
MNAM M_AGE_IN_YEA M_HEAD_NA HEAD_AGE_IN_YEA
E R M R
IRUM ####### MALBARI #######
SELECT [Link] , LPAD('#',ROUND(MONTHS_BETWEEN (SYSDATE , [Link])/12)/5,'#') M_AGE_IN_YEAR,
[Link] "M_HEAD_NAME",
LPAD('#',ROUND(MONTHS_BETWEEN (SYSDATE , [Link])/12)/5,'#') HEAD_AGE_IN_YEAR
FROM MEMBER323 M , MEMBER323 MH
Page | 4 of 7
IE 323
WHERE M.M_HEAD_NO = [Link] AND
TO_CHAR([Link],'DAY') = TO_CHAR([Link],'DAY')
4) Increase the price_per_hour for each coach as PER TOTAL AMOUNT . If total amount is between 0 to 899
increase 30% and if between 900 and 1799 than increase 50% rest all increase 60%.
TOTAL_AMOUNT_FOR_EACH_CO PRICE_PER_HOU NEW_PRICE_PER_HO
CNAME AH R UR
REHAN 1200 200 300
ATEF 360 120 156
HISHAM
M 1750 250 375
AMMAR 900 150 225
SELECT [Link], SUM(B.NO_OF_HOURS* C.PRICE_PER_HOUR) TOTAL_AMOUNT_FOR_EACH_COAH,
C.PRICE_PER_HOUR,
DECODE (TRUNC(SUM(B.NO_OF_HOURS* C.PRICE_PER_HOUR)/900) , 0,C.PRICE_PER_HOUR *1.30, 1 ,
C.PRICE_PER_HOUR *1.50,
C.PRICE_PER_HOUR *1.6) NEW_PRICE_PER_HOUR
FROM COACH323 C, BOOKING323 B
where [Link] = [Link]
GROUP BY [Link], C.PRICE_PER_HOUR
5. Find the SECOND occurrence of letter ‘M’ in CNAME , and change second C to W.
Display only those persons who have two MM’s in their names.
CNAME NEW_CNAME
AMMAR AMWAR
HISHAM
M HISHAMW
Page | 5 of 7
IE 323
select Cname , SUBSTR(CNAME ,1, instr(Cname ,'M',1,1)) ||REPLACE (SUBSTR(CNAME , instr(Cname ,'M',1 ,
1)+1,instr(Cname ,'M',1 , 2)),'M','W')
NEW_CNAME
FROM COACH323
where Cname like '%M%M%'
6. Display the MSTATUS with highest members.
MSTATUS NO_OF_MEMBERS
SILVER 2
SELECT [Link] , COUNT(*) NO_OF_MEMBERS
FROM MEMBER456 M , MLEVEL456 L
WHERE M.EARNED_POINTS BETWEEN [Link] AND [Link]
GROUP BY [Link]
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) NO_OF_MEMBERS
FROM MEMBER456 M , MLEVEL456 L
WHERE M.EARNED_POINTS BETWEEN [Link] AND [Link]
GROUP BY [Link])
*** Best of Luck ***
Page | 6 of 7
