just change me uneescry naming in records like which doent implesnt on other query results or asked in quetion thsoe data bit chnage i wanna cpoy my frinds assignmtn

Set-9
Create the database EXAM and create given tables with all necessary constraints such as
primary key, foreign key, unique key, not null and check constraints.
APPLICANT (AID, ANAME, ADDR, ABIRTH_DT) ENTRANCE_TEST (ETID,
ETNAME, MAX_SCORE, CUT_SCORE) ETEST_CENTRE (ETCID, LOCATION,
INCHARGE, CAPACITY) ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT,
SCORE)
(This database is for a common entrance test which is being conducted at a number of centers
and can be taken by an applicant on any day except holidays).
Applicant Table:
INSERT INTO APPLICANT (AID, AName, ADDR, ABIRRG_DT) VALUES ('A1123',
'Ajaykumar', 'Mumbai', TO_DATE('1995-05-01', 'YYYY-MM-DD'));
INSERT INTO APPLICANT (AID, AName, ADDR, ABIRRG_DT) VALUES ('A1124',
'Ramesh', 'Delhi', TO_DATE('1994-06-10', 'YYYY-MM-DD'));
INSERT INTO APPLICANT (AID, AName, ADDR, ABIRRG_DT) VALUES ('A1125',
'Suresh', 'Chennai', TO_DATE('1996-07-15', 'YYYY-MM-DD'));
INSERT INTO APPLICANT (AID, AName, ADDR, ABIRRG_DT) VALUES ('A1126',
'Madhavi', 'Bangalore', TO_DATE('1993-08-20', 'YYYY-MM-DD'));
INSERT INTO APPLICANT (AID, AName, ADDR, ABIRRG_DT) VALUES ('A1127',
'Nisha', 'Pune', TO_DATE('1995-09-25', 'YYYY-MM-DD'));
INSERT INTO APPLICANT (AID, AName, ADDR, ABIRRG_DT) VALUES ('1130', 'Nisha',
'Pune', TO_DATE('1995-09-25', 'YYYY-MM-DD'));
Output:

Page 64 
ENTRANCE_TEST Table:
INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE)
VALUES (1, 'ORACLE FUNDAMENTALS', 100, 50);
INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE)
VALUES (2, 'JAVA PROGRAMMING', 100, 60);
INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE)
VALUES (3, 'DATA STRUCTURES', 100, 65);
INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE)
VALUES (4, 'DATABASE MANAGEMENT', 100, 55);
INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE)
VALUES (5, 'SYSTEM DESIGN', 100, 70);
Output:

Page 65 
ETEST_CENTER Table:
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY)
VALUES (1, 'Mumbai', 'John Doe', 50);
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY)
VALUES (2, 'Delhi', 'Jane Smith', 40);
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY)
VALUES (3, 'Chennai', 'Rahul Kumar', 45);
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY)
VALUES (4, 'Bangalore', 'Sunita Singh', 60);
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY)
VALUES (5, 'Pune', 'Ajay Kumar', 55);
Output:
ETEST_DETAILS Table:
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES
('A1123', 1, 1, TO_DATE('2023-01-15', 'YYYY-MM-DD'), 85);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES
('A1124', 2, 2, TO_DATE('2023-02-10', 'YYYY-MM-DD'), 75);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES
('A1125', 3, 3, TO_DATE('2023-03-20', 'YYYY-MM-DD'), 90);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES
('A1126', 4, 4, TO_DATE('2023-04-25', 'YYYY-MM-DD'), 88);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES
('A1127', 5, 5, TO_DATE('2023-05-30', 'YYYY-MM-DD'), 78);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES
('A1127', 1, 1, TO_DATE('2023-05-30', 'YYYY-MM-DD'), 40);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES
('A1123', 3, 3, TO_DATE('2023-03-20', 'YYYY-MM-DD'), 99);
Output:

Page 66 
1. Modify the APPLICANT table so that every applicant id has an ‘A’ before its value. E.g. if
value is ‘1123’, it should become ‘A1123’.
Display test center details where no tests were conducted.
Display details about applicants who have the same score as that of Ajaykumar in ‘ORACLE
FUNDAMENTALS’.
Ans:
UPDATE APPLICANT
SET AID = 'A' || AID
WHERE SUBSTR(AID, 1, 1) != 'A';
SELECT *
FROM ETEST_DETAILS ed
JOIN APPLICANT a ON a.AID = ed.AID
JOIN ENTRANCE_TEST et ON et.ETID = ed.ETID
WHERE ed.SCORE = (
 SELECT ed_inner.SCORE
 FROM ETEST_DETAILS ed_inner
 JOIN APPLICANT a_inner ON a_inner.AID = ed_inner.AID
 JOIN ENTRANCE_TEST et_inner ON et_inner.ETID = ed_inner.ETID
 WHERE a_inner.AName = 'Ajaykumar' AND et_inner.ETNAME = 'ORACLE
FUNDAMENTALS'
)
AND et.ETNAME = 'ORACLE FUNDAMENTALS';
Output:

Page 67 
2. Display details of applicants who appeared for all tests.
Ans:
SELECT *
FROM ETEST_CENTRE
WHERE ETCID NOT IN (
 SELECT ETCID
 FROM ETEST_DETAILS
 WHERE ETCID IS NOT NULL
);
Output:
3. Display those tests where no applicant has failed.
Ans:
SELECT *
FROM ETEST_DETAILS ed
JOIN APPLICANT a ON a.AID = ed.AID
JOIN ENTRANCE_TEST et ON et.ETID = ed.ETID
WHERE et.CUT_SCORE > ed.SCORE;
Output:

Page 68 
4. Display details of entrance test centers which had full attendance between 1st Oct 15 and
15th Oct 16.
Ans:
select ec.ETCID
from ETEST_DETAILS ed
JOIN ETEST_CENTRE ec on ec.ETCID = ed.ETCID
group by ec.ETCID, ec.LOCATION, ec.INCHARGE, ec.CAPACITY, ed.ETEST_DT
having COUNT(ed.AID) = ec.CAPACITY AND ed.ETEST_DT between TO_DATE('2015-10-
01', 'YYYY-MM-DD') and TO_DATE('2016-10-15', 'YYYY-MM-DD');
Output:
5. Display details of the applicants who scored more than the cut score in the tests they
appeared in.
Ans:
select *
from ENTRANCE_TEST et
JOIN ETEST_DETAILS ed ON ed.ETID = et.ETID
where et.CUT_SCORE < ed.SCORE;
Output:

Page 69 
6. Display average and maximum score test wise of tests conducted at Mumbai
Ans:
select AVG(ed.SCORE), MAX(ed.SCORE), ec.location
from ETEST_DETAILS ed
join ETEST_CENTRE ec on ec.ETCID = ed.ETCID
group by ec.LOCATION
having ec.LOCATION = 'Mumbai';
Output:
7. Display the number of applicants who have appeared for each test, test center wise.
Ans:
select COUNT(*), ec.location
from ETEST_DETAILS ed
JOIN ENTRANCE_TEST et On et.ETID = ed.ETID
JOIN ETEST_CENTRE ec On ec.ETCID = ed.ETCID
group by ec.LOCATION;
Output:
8. Display details about test centers where no tests have been conducted.
Ans:
SELECT ec.LOCATION, ec.INCHARGE, ec.CAPACITY
FROM ETEST_CENTRE ec

Page 70 
LEFT JOIN ETEST_DETAILS ed ON ec.ETCID = ed.ETCID
WHERE ed.ETCID IS NULL;
SELECT ec.*, ed.*
FROM ETEST_CENTRE ec
LEFT JOIN ETEST_DETAILS ed ON ec.ETCID = ed.ETCID;
Output:
9. For tests, which have been conducted between 2-3-17 and 23-4-17, show details of the tests
as well as the test centre.
Ans:
SELECT
 ed.ETEST_DT AS Test_Date,
 ed.SCORE AS Test_Score,
 ec.LOCATION AS Test_Center_Location,
 ec.INCHARGE AS Test_Center_Incharge,
 ec.CAPACITY AS Test_Center_Capacity
FROM ETEST_DETAILS ed
JOIN ETEST_CENTRE ec ON ed.ETCID = ec.ETCID
WHERE ed.ETEST_DT BETWEEN TO_DATE('2023-01-15', 'YYYY-MM-DD') AND
TO_DATE('2023-02-10', 'YYYY-MM-DD');
Output:

Page 71 
10. How many applicants appeared in the ‘ORACLE FUNDAMENTALS’ test at Chennai in
the month of February?
Ans:
select COUNT(*) AS Application_Count
from ETEST_DETAILS ed
JOIN ENTRANCE_TEST et on ed.ETID = et.ETID
JOIN ETEST_CENTRE ec on ed.ETCID = ec.ETCID
where et.ETNAME = 'ORACLE FUNDAMENTALS' AND ec.LOCATION = 'Chennai' AND
EXTRACT(month from ed.ETEST_DT) = 2;
Output:
11. Display details about applicants who appeared for tests in the same month as the month in
which they were born.
Ans:
SELECT ed.ETEST_DT, a.ABIRRG_DT
FROM ETEST_DETAILS ed
JOIN APPLICANT a ON a.AID = ed.AID
WHERE ed.ETEST_DT IS NOT NULL AND a.ABIRRG_DT IS NOT NULL;
Output:

Page 72 
12. Display the details about APPLICANTS who have scored the highest in each test, test
centre wise.
Ans:
select MAX(ed.score), ec.location as Exam_location
from APPLICANT a
JOIN ETEST_DETAILS ed ON ed.AID = a.AID
JOIN ETEST_CENTRE ec ON ec.ETCID = ed.ETCID
group by ed.ETCID, ec.location;
Output:

Page 73 
13. Design a read only view, which has details about applicants and the tests that he has
appeared for.
Ans:
create view ApplicationDetails as
select a.AID AS ApplicantID,
 a.ANAME AS ApplicantName,
 a.ABIRRG_DT AS BirthDate,
 et.ETID AS TestID,
 et.ETNAME AS TestName,
 ed.ETEST_DT AS TestDate,
 ec.LOCATION AS TestLocation
from APPLICANT a
JOIN ETEST_DETAILS ed ON a.AID = ed.AID
JOIN ENTRANCE_TEST et ON ed.ETID = et.ETID
JOIN ETEST_CENTRE ec ON ed.ETCID = ec.ETCID;
select * from ApplicationDetails;
Output:
14. Write a procedure which will print maximum score centre wise.
Ans:
CREATE OR REPLACE PROCEDURE find_max_marks IS
 CURSOR max_marks IS
 SELECT MAX(ed.score) AS MAX_SCORE, ec.location
 FROM ETEST_DETAILS ed
 JOIN ETEST_CENTRE ec ON ec.ETCID = ed.ETCID
 GROUP BY ec.location;
BEGIN
 FOR marks IN max_marks LOOP
 DBMS_OUTPUT.PUT_LINE('MAX Marks: ' || marks.MAX_SCORE);
 DBMS_OUTPUT.PUT_LINE('Location: ' || marks.location);

Page 74 
 END LOOP;
END;
/
BEGIN
 find_max_marks;
END;
/
 SELECT MAX(ed.score) AS MAX_SCORE, ec.location
FROM ETEST_DETAILS ed
JOIN ETEST_CENTRE ec ON ec.ETCID = ed.ETCID
GROUP BY ec.location;
Output:
15. Write a procedure which will print details of entrance test.
Ans:
CREATE OR REPLACE PROCEDURE print_date IS
 CURSOR data IS
 SELECT ec.LOCATION, a.AID, ed.ETEST_DT, ed.score
 FROM ETEST_DETAILS ed
 JOIN ETEST_CENTRE ec ON ec.ETCID = ed.ETCID
 JOIN APPLICANT a ON a.AID = ed.AID;
BEGIN
 -- Loop through the cursor
 FOR datas IN data LOOP
 DBMS_OUTPUT.PUT_LINE('Center Name: ' || datas.LOCATION ||
 ' / Candidate Id: ' || datas.AID ||
 ' / Date: ' || TO_CHAR(datas.ETEST_DT, 'YYYY-MM-DD') ||
 ' / Score: ' || datas.score);
 END LOOP;
END;
/
BEGIN
 print_date;

Page 75 
END;
/
SELECT ec.LOCATION, a.AID, ed.ETEST_DT, ed.score
FROM ETEST_DETAILS ed
JOIN ETEST_CENTRE ec ON ec.ETCID = ed.ETCID
JOIN APPLICANT a ON a.AID = ed.AID;
Output:
16. Write a trigger which do not allow insertion / updation / deletion of Enterance test details on
Sunday.
Ans:
create or replace TRIGGER not_sunday
before insert or update or delete ON ETEST_DETAILS
for each row
begin
if TO_CHAR(sysdate, 'DY') = 'SUN' THEN
raise_application_error(-20000, 'Not enter data becaus, today is Sunday');
end if;
end;
Output: