recheck this first check records and than check if query may perform corectly.. ot not based on record not nullreult


-- APPLICANT Table
CREATE TABLE APPLICANT ( 
AID VARCHAR2(20) PRIMARY KEY, 
ANAME VARCHAR2(100) NOT NULL, 
ADDR VARCHAR2(200), 
ABIRTH_DT DATE, 
-- Constraint to ensure AID starts with 'A'
CONSTRAINT CHK_AID_FORMAT CHECK (AID LIKE 'A%') 
); 
-- ENTRANCE_TEST Table
CREATE TABLE ENTRANCE_TEST ( 
ETID VARCHAR2(10) PRIMARY KEY, 
ETNAME VARCHAR2(100) NOT NULL, 
MAX_SCORE NUMBER(5,2) NOT NULL, 
CUT_SCORE NUMBER(5,2) NOT NULL, 
-- Constraint to ensure cut score is less than max score
CONSTRAINT CHK_SCORE_RANGE CHECK (CUT_SCORE <= MAX_SCORE) 
); 
-- ETEST_CENTRE Table
CREATE TABLE ETEST_CENTRE ( 
ETCID VARCHAR2(10) PRIMARY KEY, 
LOCATION VARCHAR2(100) NOT NULL, 
INCHARGE VARCHAR2(100) NOT NULL, 
CAPACITY NUMBER(4) NOT NULL 
); 
-- ETEST_DETAILS Table
CREATE TABLE ETEST_DETAILS ( 
AID VARCHAR2(20), 
ETID VARCHAR2(10), 
ETCID VARCHAR2(10), 
ETEST_DT DATE NOT NULL, 
SCORE NUMBER(5,2), 
-- Composite Primary Key
CONSTRAINT PK_ETEST_DETAILS PRIMARY KEY (AID, ETID, ETCID), 
-- Foreign Key Constraints
CONSTRAINT FK_ETEST_APPLICANT FOREIGN KEY (AID) REFERENCES APPLICANT(AID), 
CONSTRAINT FK_ETEST_ENTRANCE_TEST FOREIGN KEY (ETID) REFERENCES ENTRANCE_TEST(ETID), 
CONSTRAINT FK_ETEST_CENTRE FOREIGN KEY (ETCID) REFERENCES ETEST_CENTRE(ETCID), 
CONSTRAINT CHK_TEST_DAY CHECK (TO_CHAR(ETEST_DT, 'DY') NOT IN ('SAT', 'SUN')) 
); 

INSERT INTO APPLICANT (AID, ANAME, ADDR, ABIRTH_DT) VALUES ('A1123', 'Ajaykumar', '123 Street, City', '10-05-1995');
INSERT INTO APPLICANT (AID, ANAME, ADDR, ABIRTH_DT) VALUES ('A1124', 'Suresh', '456 Avenue, City', '15-07-1996');
INSERT INTO APPLICANT (AID, ANAME, ADDR, ABIRTH_DT) VALUES ('A1125', 'Ramesh', '789 Boulevard, City', '20-09-1994');
INSERT INTO APPLICANT (AID, ANAME, ADDR, ABIRTH_DT) VALUES ('A1126', 'Geeta', '321 Road, City', '12-11-1995');

INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE) VALUES (101, 'ORACLE FUNDAMENTALS', 100, 50);
INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE) VALUES (102, 'JAVA PROGRAMMING', 90, 45);
INSERT INTO ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE) VALUES (103, 'DATABASE MANAGEMENT', 80, 40);

INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY) VALUES (201, 'Mumbai', 'Mr. Sharma', 100);
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY) VALUES (202, 'Chennai', 'Ms. Rani', 150);
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY) VALUES (203, 'Delhi', 'Mr. Kapoor', 120);
INSERT INTO ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY) VALUES (204, 'Kolkata', 'Mr. Sen', 100);

INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1123', 101, 201, '10-02-2016', 75);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1123', 102, 202, '12-02-2016', 65);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1124', 101, 202, '15-02-2016', 55);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1125', 103, 203, '18-02-2016', 70);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1126', 102, 203, '22-02-2016', 80);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1126', 101, 201, '23-02-2016', 85);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1125', 101, 202, '15-03-2017', 60);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1124', 103, 202, '20-03-2017', 90);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1126', 103, 203, '10-04-2017', 70);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1123', 103, 201, '10-03-2016', 80);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1124', 103, 202, '15-03-2016', 85);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1126', 101, 201, '10-10-2016', 90);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1125', 101, 202, '15-03-2017', 60);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1126', 102, 203, '10-04-2017', 70);
INSERT INTO ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE) VALUES ('A1124', 101, 202, '15-07-1996', 55);

-- 1. Test centers with no tests
SELECT EC.* 
FROM ETEST_CENTRE EC 
LEFT JOIN ETEST_DETAILS ED ON EC.ETCID = ED.ETCID 
WHERE ED.ETCID IS NULL; 
-- 2. Applicants who appeared for all tests
SELECT A.* 
FROM APPLICANT A 
WHERE (SELECT COUNT(DISTINCT ETID) FROM ENTRANCE_TEST) = 
(SELECT COUNT(DISTINCT ETID) FROM ETEST_DETAILS WHERE AID = A.AID); 
-- 3. Tests where no applicant has failed
SELECT ET.* 
FROM ENTRANCE_TEST ET 
WHERE NOT EXISTS ( 
SELECT 1 FROM ETEST_DETAILS ED 
WHERE ED.ETID = ET.ETID AND ED.SCORE < ET.CUT_SCORE 
); 
-- 4. Entrance test centers with full attendance
SELECT EC.* 
FROM ETEST_CENTRE EC 
JOIN ETEST_DETAILS ED ON EC.ETCID = ED.ETCID 
WHERE ED.ETEST_DT BETWEEN TO_DATE('2015-10-01', 'YYYY-MM-DD') AND TO_DATE('2016-10-15', 'YYYY-MM-DD') 
GROUP BY EC.ETCID, EC.LOCATION, EC.INCHARGE, EC.CAPACITY 
HAVING COUNT(ED.AID) = EC.CAPACITY; 
-- 5. Applicants who scored more than cut score
SELECT A.*, ET.ETNAME, ED.SCORE, ET.CUT_SCORE 
FROM APPLICANT A 
JOIN ETEST_DETAILS ED ON A.AID = ED.AID 
JOIN ENTRANCE_TEST ET ON ED.ETID = ET.ETID 
WHERE ED.SCORE > ET.CUT_SCORE; 
-- 6. Average and maximum score for tests in Mumbai
SELECT ET.ETNAME, 
AVG(ED.SCORE) AS AVG_SCORE, 
MAX(ED.SCORE) AS MAX_SCORE 
FROM ENTRANCE_TEST ET 
JOIN ETEST_DETAILS ED ON ET.ETID = ED.ETID 
JOIN ETEST_CENTRE EC ON ED.ETCID = EC.ETCID 
WHERE EC.LOCATION = 'Mumbai' 
GROUP BY ET.ETNAME; 
-- 7. Number of applicants per test, test center wise
SELECT EC.ETCID, EC.LOCATION, ET.ETNAME, COUNT(ED.AID) AS APPLICANT_COUNT 
FROM ETEST_CENTRE EC 
JOIN ETEST_DETAILS ED ON EC.ETCID = ED.ETCID 
JOIN ENTRANCE_TEST ET ON ED.ETID = ET.ETID 
GROUP BY EC.ETCID, EC.LOCATION, ET.ETNAME; 
-- 8. Test centers where no tests conducted (same as query 1)
SELECT EC.* 
FROM ETEST_CENTRE EC 
LEFT JOIN ETEST_DETAILS ED ON EC.ETCID = ED.ETCID 
WHERE ED.ETCID IS NULL; 
-- 9. Tests between 2-3-17 and 23-4-17
SELECT ET.*, EC.LOCATION, ED.ETEST_DT 
FROM ENTRANCE_TEST ET 
JOIN ETEST_DETAILS ED ON ET.ETID = ED.ETID 
JOIN ETEST_CENTRE EC ON ED.ETCID = EC.ETCID 
WHERE ED.ETEST_DT BETWEEN TO_DATE('2017-03-02', 'YYYY-MM-DD') AND TO_DATE('2017-04-23', 'YYYY-MM-DD'); 
-- 10. Applicants for Oracle Fundamentals in Chennai in February
SELECT A.*, ED.SCORE 
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 
WHERE ET.ETNAME = 'ORACLE FUNDAMENTALS' 
AND EC.LOCATION = 'Chennai' 
AND EXTRACT(MONTH FROM ED.ETEST_DT) = 2; 
-- 11. Applicants who appeared for tests in their birth month
SELECT A.*, ET.ETNAME, ED.ETEST_DT 
FROM APPLICANT A 
JOIN ETEST_DETAILS ED ON A.AID = ED.AID 
JOIN ENTRANCE_TEST ET ON ED.ETID = ET.ETID 
WHERE EXTRACT(MONTH FROM A.ABIRTH_DT) = EXTRACT(MONTH FROM ED.ETEST_DT); 
-- 12. Highest scorers in each test, test center wise
SELECT A.*, ET.ETNAME, EC.LOCATION, ED.SCORE 
FROM ( 
SELECT ETID, ETCID, MAX(SCORE) AS MAX_SCORE 
FROM ETEST_DETAILS 
GROUP BY ETID, ETCID 
) MaxScores 
JOIN ETEST_DETAILS ED ON ED.ETID = MaxScores.ETID 
AND ED.ETCID = MaxScores.ETCID 
AND ED.SCORE = MaxScores.MAX_SCORE 
JOIN APPLICANT A ON ED.AID = A.AID 
JOIN ENTRANCE_TEST ET ON ED.ETID = ET.ETID 
JOIN ETEST_CENTRE EC ON ED.ETCID = EC.ETCID; 
-- 13. Read-only view of applicants and their tests
CREATE OR REPLACE VIEW ApplicantTestDetails AS 
SELECT A.AID, A.ANAME, A.ADDR, A.ABIRTH_DT, 
ET.ETNAME, ET.MAX_SCORE, ET.CUT_SCORE, 
ED.ETEST_DT, ED.SCORE, 
EC.LOCATION AS TEST_LOCATION 
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 
WITH READ ONLY; 
SELECT * FROM ApplicantTestDetails; 
-- 14. Procedure to print maximum score centre wise
CREATE OR REPLACE PROCEDURE PrintMaxScoreCentre IS 
BEGIN 
FOR centre_rec IN ( 
SELECT EC.ETCID, EC.LOCATION, 
MAX(ED.SCORE) AS MAX_SCORE 
FROM ETEST_CENTRE EC 
JOIN ETEST_DETAILS ED ON EC.ETCID = ED.ETCID 
GROUP BY EC.ETCID, EC.LOCATION 
) LOOP 
DBMS_OUTPUT.PUT_LINE('Centre ID: ' || centre_rec.ETCID); 
DBMS_OUTPUT.PUT_LINE('Location: ' || centre_rec.LOCATION); 
DBMS_OUTPUT.PUT_LINE('Maximum Score: ' || centre_rec.MAX_SCORE); 
DBMS_OUTPUT.PUT_LINE('----------------------------'); 
END LOOP; 
END; 
/ 
set serveroutput on; 
BEGIN 
PrintMaxScoreCentre; 
END; 
-- 15. Procedure to print entrance test details
CREATE OR REPLACE PROCEDURE PrintEntranceTestDetails IS 
v_etid ENTRANCE_TEST.ETID%TYPE; 
v_etname ENTRANCE_TEST.ETNAME%TYPE; 
v_max_score ENTRANCE_TEST.MAX_SCORE%TYPE; 
v_cut_score ENTRANCE_TEST.CUT_SCORE%TYPE; 
BEGIN 
FOR rec IN (SELECT ETID, ETNAME, MAX_SCORE, CUT_SCORE FROM ENTRANCE_TEST) LOOP 
DBMS_OUTPUT.PUT_LINE('Entrance Test ID: ' || rec.ETID); 
DBMS_OUTPUT.PUT_LINE('Test Name: ' || rec.ETNAME); 
DBMS_OUTPUT.PUT_LINE('Max Score: ' || rec.MAX_SCORE); 
DBMS_OUTPUT.PUT_LINE('Cut Score: ' || rec.CUT_SCORE); 
DBMS_OUTPUT.PUT_LINE('----------------------------'); 
END LOOP; 
END; 
/ 

SET SERVEROUTPUT ON;
BEGIN 
PrintEntranceTestDetails; 
END; 
--15
CREATE OR REPLACE TRIGGER PreventSundayModifications 
BEFORE INSERT OR UPDATE OR DELETE ON ETEST_DETAILS 
FOR EACH ROW 
DECLARE 
v_day VARCHAR2(3); 
BEGIN 
v_day := TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH'); 
IF v_day = 'SUN' THEN 
RAISE_APPLICATION_ERROR(-20001, 'Modifications are not allowed on Sundays.'); 
END IF; 
END; 
/ 
UPDATE ETEST_DETAILS 

SET SCORE = SCORE + 5
WHERE AID = 'A1001' AND ETID = 'ET01' AND ETCID = 'ETC01';