Oracle Exercise

1. Create tables in Oracle
 
Candidate(CID,CNAME,CADDRESS,CBIRTH_DT)
TEST(TID,TNAME,TOT_MARKS,PASS_MARKS)
TEST_CENTRE(TCID,LOCATION,MRG,CAPACITY)
TEST_TAKEN(CID,TID,TCID,TEST_DT,SCORE)


CREATE TABLE candidate
(
cid NUMBER(3) CONSTRAINT pk_candidate PRIMARY KEY,
cname VARCHAR2(20) CONSTRAINT nn_cname NOT NULL,
caddress VARCHAR2(20),
cdob DATE
);

CREATE TABLE test
(
tid NUMBER(3) CONSTRAINT pk_test PRIMARY KEY,
tname VARCHAR2(20) CONSTRAINT nn_tname NOT NULL,
total_marks NUMBER(3) DEFAULT 100,
pass_marks NUMBER(3) DEFAU
LT 40
);

CREATE TABLE testcenter
(
tcid NUMBER(3) CONSTRAINT pk_testcenter PRIMARY KEY,
tcloc VARCHAR2(20),
tcmanager VARCHAR2(20),
tccapacity NUMBER(3)
);

CREATE TABLE testtaken
(
cid NUMBER(3),
tid NUMBER(3),
tcid NUMBER(3),
testdate DATE,
testscore NUMBER(3),

CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES candidate(cid) ON DELETE CASCADE,
CONSTRAINT fk_tid FOREIGN KEY(tid) REFERENCES test(tid) ON DELETE CASCADE,
CONSTRAINT fk_tcid FOREIGN KEY(tcid) REFERENCES testcenter(tcid) ON DELETE CASCADE,
CONSTRAINT pk_testtaken PRIMARY KEY(cid, tid, tcid)
);



1.2 INSERT DATA INTO A TABLE IN ORACLE

INSERT INTO candidate VALUES
(1, 'JAYESH', 'MALIBU', '09-APR-1991', 1010101010);

INSERT INTO candidate VALUES
(2, 'TONY', 'MALIBU', '09-APR-1980', 2020202020);

INSERT INTO candidate VALUES
(3, 'JARVIS', 'CALIFORNIA', '01-JAN-1990', 3030303030);

INSERT INTO candidate VALUES
(4, 'PEPPER', 'DOWNTOWN', '01-DEC-1991', 4040404040);

INSERT INTO candidate VALUES
(5, 'OBIDIAN', 'HOLLYWOOD', '01-FEB-1985', 5050505050);



INSERT INTO test 
(tid, tname)
VALUES
(1, 'TEST1');

INSERT INTO test 
(tid, tname)
VALUES
(2, 'TEST2');

INSERT INTO test 
(tid, tname)
VALUES
(3, 'TEST3');

INSERT INTO test 
(tid, tname)
VALUES
(4, 'TEST4');

INSERT INTO test 
(tid, tname)
VALUES
(5, 'TEST5');



INSERT INTO testcenter VALUES
(1, 'MALIBU', 'JAYESH', 100);

INSERT INTO testcenter VALUES
(2, 'HOLLYWOOD', 'OBEDIAN', 200);

INSERT INTO testcenter VALUES
(3, 'CALIFORNIA', 'TONY', 300);

INSERT INTO testcenter VALUES
(4, 'DOWNTOWN', 'PEPPER', 400);

INSERT INTO testcenter VALUES
(5, 'BOSTON', 'JARVIS', 500);




INSERT INTO testtaken VALUES
(2, 2, 2, '2-JUL-2013', 80);

INSERT INTO testtaken VALUES
(3, 3, 3, '3-JUL-2013', 70);

INSERT INTO testtaken VALUES
(4, 4, 4, '4-JUL-2013', 80);

INSERT INTO testtaken VALUES
(5, 5, 5, '5-JUL-2013', 60);

INSERT INTO testtaken VALUES
(1, 2, 2, '2-JUL-2013', 90);

INSERT INTO testtaken VALUES
(2, 3, 3, '3-JUL-2013', 80);

INSERT INTO testtaken VALUES
(3, 4, 4, '4-JUL-2013', 60);

INSERT INTO testtaken VALUES
(4, 5, 5, '5-JUL-2013', 70);

INSERT INTO testtaken VALUES
(5, 1, 1, '1-JUL-2013', 90);

INSERT INTO testtaken VALUES
(4, 2, 2, '2-JUL-2013', 80);

INSERT INTO testtaken VALUES
(5, 3, 3, '3-JUL-2013', 70);

INSERT INTO testtaken VALUES
(1, 4, 4, '4-JUL-2013', 80);

INSERT INTO testtaken VALUES
(2, 5, 5, '5-JUL-2013', 60);

INSERT INTO testtaken VALUES
(3, 2, 2, '2-JUL-2013', 90);

INSERT INTO testtaken VALUES
(5, 3, 3, '3-JUL-2013', 80);

INSERT INTO testtaken VALUES
(3, 4, 4, '4-JUL-2013', 60);

INSERT INTO testtaken VALUES
(2, 5, 5, '5-JUL-2013', 70);

INSERT INTO testtaken VALUES
(1, 1, 1, '1-JUL-2013', 90);



2.Display the number of candidates who have appeared for the test ‘OCA’ at the test centre ’TC2’.

SELECT COUNT(*) FROM testtaken
WHERE
tid IN ( SELECT tid FROM test WHERE UPPER(tname) = 'TEST1')
AND
tcid IN ( SELECT tcid FROM testcenter WHERE UPPER(tcloc) = 'MALIBU')




3. Change the structure of the candidate table by adding a column passpartno which has the unique constraint.

ALTER TABLE candidate
ADD(cpassportno NUMBER(10) CONSTRAINT un_passportno UNIQUE)




4.Drop the primary key of the test table and add it again.

ALTER TABLE testtaken
DROP CONSTRAINT fk_tid;

ALTER TABLE test
DROP CONSTRAINT pk_test;

ALTER TABLE test
ADD CONSTRAINT pk_test PRIMARY KEY(tid)

ALTER TABLE testtaken
ADD CONSTRAINT fk_tid FOREIGN KEY(tid) REFERENCES test(tid)




5.Display candidate details for those candidate who have scored more than martin in the test ‘T10’ and less than Luther in the test ‘T12’

SELECT * FROM testtaken
INNER JOIN candidate ON candidate.cid = testtaken.cid
INNER JOIN test ON test.tid = testtaken.tid
WHERE

testscore > (
SELECT testscore FROM testtaken
INNER JOIN candidate ON candidate.cid = testtaken.cid
INNER JOIN test ON test.tid = testtaken.tid
WHERE UPPER(cname)='JARVIS' AND UPPER(tname)='TEST2')
AND
testscore <
(SELECT testscore FROM testtaken
INNER JOIN candidate ON candidate.cid = testtaken.cid
INNER JOIN test ON test.tid = testtaken.tid
WHERE UPPER(cname)='JAYESH' AND UPPER(tname)='TEST4')




6.Display details about test centres where at least one test has been conducted.

SELECT * FROM testcenter 
WHERE tcid NOT IN ( SELECT tcid FROM testtaken GROUP BY tcid)




7.Display details of candidate who have failed in test ‘OCP‘ at Mumbai.

SELECT * FROM testtaken
INNER JOIN candidate ON candidate.cid = testtaken.cid
INNER JOIN test ON test.tid = testtaken.tid
INNER JOIN testcenter ON testcenter.tcid = testtaken.tcid
WHERE
UPPER(tname) = 'TEST1'
AND
UPPER(tcloc) = 'MALIBU'
AND
testscore > pass_markS




8.Display details of the test conducted in the month of October

select * from testtaken
where to_char(testdate, 'MON') = 'JUL'





9.Display how many candidate appeared for tests in each test centre.

select tcid, count(*) from testtaken group by tcid





10.Display details about candidate who appeared for tests in the same month as the month in which they were born.

select * from testtaken
inner join candidate on candidate.cid = testtaken.cid
where
to_char(cdob, 'mon') = to_char(testdate, 'mon')




11.Create  a unique index on the test name in the test table.

CREATE UNIQUE INDEX ix_tname ON test(tname)




12.Grant the select and insert privilege to user xyz and allow him to give the same rights to other users too.


GRANT SELECT, INSERT ON testtaken TO mca202 WITH GRANT OPTION



13.Design a read only view which has details about candidate and the test that he has appeared for.


CREATE VIEW viewname AS SELECT cid, tid FROM testtaken WITH READ ONLY 
 
 
 
 

Post a Comment