More Oracle Examples

1. Create tables in Oracle

CREATE TABLE worker
(
wid NUMBER(3) CONSTRAINT pk_worker PRIMARY KEY,
wname VARCHAR2(20) CONSTRAINT nn_wname NOT NULL,
wageperhour NUMBER(5),
specialisedin VARCHAR(10),
mid NUMBER(3)
);


CREATE TABLE job
(
jid NUMBER(3) CONSTRAINT pk_job PRIMARY KEY,
jtype VARCHAR(20) CONSTRAINT nn_jtype NOT NULL,
jstatus VARCHAR(20)
);


CREATE TABLE jobassigned
(
wid NUMBER(3),
jid NUMBER(3),
startdate DATE,
noofdays NUMBER(3),
CONSTRAINT fk_wid FOREIGN KEY(wid) REFERENCES worker(wid),
CONSTRAINT fk_jid FOREIGN KEY(jid) REFERENCES job(jid),
CONSTRAINT pk_jobassigned PRIMARY KEY(wid, jid)
);





Insert data into tables

INSERT INTO worker VALUES
(0, 'JAYESH', 100, 'EVERYTHING', 5);

INSERT INTO worker VALUES
(1, 'TONY', 500, 'ROBOTICS', 0)

INSERT INTO worker VALUES
(2, 'JARVIS', 1000, 'ASSISTING', 1)

INSERT INTO worker VALUES
(3, 'PEPPER', 1500, 'MANAGING', 0);

INSERT INTO worker VALUES
(4, 'MARK', 2000, 'CLEANING', 3)

INSERT INTO worker VALUES
(5, 'JOKER', 3000, 'JOKING', 0)



INSERT INTO job VALUES
(0, 'JOB1', 'I');

INSERT INTO job VALUES
(1, 'JOB2', 'C');

INSERT INTO job VALUES
(2, 'JOB3', 'I');

INSERT INTO job VALUES
(3, 'JOB4', 'C');

INSERT INTO job VALUES
(4, 'JOB5', 'I');

INSERT INTO job VALUES
(5, 'JOB6', 'C');



INSERT INTO jobassigned VALUES
(0, 0, '20-JULY-2013', 10);

INSERT INTO jobassigned VALUES
(1, 1, '21-JULY-2013', 20);

INSERT INTO jobassigned VALUES
(2, 2, '22-JULY-2013', 10);

INSERT INTO jobassigned VALUES
(3, 3, '23-JULY-2013', 15);

INSERT INTO jobassigned VALUES
(4, 4, '22-JULY-2013', 12);

INSERT INTO jobassigned VALUES
(5, 5, '21-JULY-2013', 40);



2. change structure of job table by adding a constraint which will allow only c or i in the status column

ALTER TABLE JOB
ADD(CONSTRAINT ck_status CHECK(jstatus = 'I' OR jstatus = 'C'))



3. CREATE A UNIQUE INDEX ON THE COLUMN TYPEOFJOB OF JOB TABLE

CREATE UNIQUE INDEX ix_typeofjob ON job(jtype)


4. DISPLAY THE DATE ON WHICH EACH WORKER IS GOING TO END HIS PRESENTLY ASSIGNED JOB.

SELECT wid, (startdate+noofdays) "jobenddate" FROM jobassigned;



5.DISPLAY HOW MANY DAYS ARE REMAINING TO FINISH HIS JOB

SELECT wid, ROUND((startdate+noofdays)-SYSDATE) "noofdaysleft" FROM jobassigned
WHERE
startdate+noofdays >= SYSDATE





6. DISPLAY JOB DETAILS OF ALL THOSE JOBS WHERE MORE THAN 100 WORKERS ARE WORKING

SELECT jid, COUNT(wid) "noofworkers" FROM jobassigned
GROUP BY  jid
HAVING COUNT(wid) >= 2




7. DISPLAY ALL WORKER DETAILS AND THEIR JOB DETAILS OF JOB WHICH WILL START IN THE MONTH OF AUGUST.

SELECT * FROM jobassigned
INNER JOIN worker ON worker.wid = jobassigned.wid
INNER JOIN job ON job.jid = jobassigned.wid
WHERE TO_CHAR(startdate, 'MON') = 'AUG'




8. LIST ALL WORKER WHO HAVE THEIR WAGEPERHOUR 10 TIMES THAN THEIR MANAGER.

SELECT * FROM worker A
INNER JOIN worker B  ON B.mid = A.wid
WHERE
A.wageperhour >= 10*(B.wageperhour)




9. DISPLAY THE MAX AND MIN WAGEPERHOUR OF WORKER WHO ARE SPECIALISED IN MANAGING OR EVERYTHING

SELECT SPECIALISEDIN,MAX(wageperhour) "MAXWAGE", MIN(wageperhour) "MINWAGE" FROM worker
GROUP BY SPECIALISEDIN
HAVING
(SPECIALISEDIN = 'EVERYTHING' OR SPECIALISEDIN = 'MANAGING')




10. WHICH WORKERS OWN AS MUCH WAGEPERHOUR AS MR. JARVIS

SELECT * FROM worker
WHERE
wageperhour IN (SELECT wageperhour FROM worker WHERE wname = 'JARVIS')



11. CREATE A VIEW WORKS WHICH HAS THE COLUMNS WID WNAME AND SPECIALISEDIN OF WORKER TABLE AS WELL AS JOBID AND TYPE OF WORK OF JOB TABLE.

CREATE VIEW view_works AS
SELECT wid, wname, specialisedin, jid, jtype FROM WORKER




Post a Comment