select Count(*), col1
from table_name,
group by col1
having count(*) > 1;
SELECT col1, col2
FROM table A
LEFT JOIN table B AS B ON A.col1 = B.col2
WHERE B.col2 IS NULL
SELECT e.emp_id 'Emp_id', e.name, m.emp_id 'Mgr_Id', m.name 'Manager'
FROM emp e join emp m
ON (e.mgr_id = m.emp_id);
LAST
select LAST_DAY(ADD_MONTHS(sysdate, -1)) from dual;
SELECT *
FROM (SELECT SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC)ranking FROM
EMPLOYEES) WHERE ranking = 3;
SELECT t.* from
(select EMPLOYEES .* ROW_NUMBER()over (order by [Row Number]) as seqnum,
count(*) over () as cnt
from EMPLOYEES)t
where seqnum in(1,2) or seqnum in (cnt,cnt-1);
project assign and how man days and new or old and how the project
joining bonus
laptop
data metrics
CREATE TABLE physician (
employeee_id NUMBER(10),
name VARCHAR(10) NOT NULL,
position VARCHAR(10) NOT NULL,
ssn NUMBER(10)
);
CREATE TABLE suppliers (
supplier_id NUMBER(10),
supplier_name VARCHAR(10) NOT NULL,
contact_name VARCHAR(10) NOT NULL
);
CREATE TABLE procedure (
code NUMBER(10),
name VARCHAR(10) NOT NULL,
cost NUMBER(10)
);
CREATE TABLE trained_in (
physician NUMBER(10),
treatment NUMBER(10),
certificationdate DATE,
certificationexpires DATE
);
CREATE TABLE room (
roomnumber NUMBER(10),
roomtype VARCHAR(10) NOT NULL,
blockfloor NUMBER(10),
blockcode NUMBER(10),
unavailable VARCHAR(1)
);
CREATE TABLE stay (
stayid NUMBER(10),
patient NUMBER(10),
room NUMBER(10),
start_time TIMESTAMP,
end_time TIMESTAMP
);
CREATE TABLE nurse (
employeeid NUMBER(10),
name VARCHAR(10) NOT NULL,
position VARCHAR(10),
registered VARCHAR(1),
ssn NUMBER(10)
);
CREATE TABLE affiliated_with (
physician NUMBER(10),
department NUMBER(10),
primaryaffiliation VARCHAR(1)
);
CREATE TABLE prescribes (
physician NUMBER(10),
patient NUMBER(10),
medication NUMBER(10),
"date" TIMESTAMP,
appointment NUMBER(10),
dose VARCHAR(10) NOT NULL
);
CREATE TABLE patient (
ssn NUMBER(10),
name VARCHAR(10) NOT NULL,
address VARCHAR(10) NOT NULL,
phone VARCHAR(10) NOT NULL,
insuranceid NUMBER(10),
pcp NUMBER(10)
);
CREATE TABLE appointment (
appointmentid NUMBER(10),
patient NUMBER(10),
prepnurse NUMBER(10),
physician NUMBER(10),
start_dt_time TIMESTAMP,
end_dt_time TIMESTAMP,
examinationroom VARCHAR(10) NOT NULL
);
CREATE TABLE block (
blockfloor NUMBER(10),
blockcode NUMBER(10)
);
CREATE TABLE medication (
code NUMBER(10),
name VARCHAR(10) NOT NULL,
brand VARCHAR(10) NOT NULL,
description VARCHAR(10) NOT NULL
);
CREATE TABLE undergoes (
patient NUMBER(10),
procedure NUMBER(10),
stay NUMBER(10),
"date" TIMESTAMP,
physician NUMBER(10),
assistingnurse NUMBER(10)
);
CREATE TABLE on_call (
nurse NUMBER(10),
blockfloor NUMBER(10),
blockcode NUMBER(10),
oncallstart TIMESTAMP,
oncallend TIMESTAMP
);
null---- without record, col wil be created
not null---- we need to enter the record
Syntax for primary key
ALTER TABLE [TABLE_NAME] ADD CONSTRAINT [CONSTRAINT_NAME] PRIMARY KEY
([COLUMN_NAME])
Syntax for foreign key
ALTER TABLE [TABLE_NAME] ADD CONSTRAINT [CONSTRAINT_NAME] FOREIGN KEY
([COLUMN_NAME])
REFERENCES [REFERENCE_TABLE(REFERENCE_TABLE_COLUMN)]
CREATE TABLE physician (
employeee_id NUMBER(10),
name VARCHAR(10) NOT NULL,
position VARCHAR(10) NOT NULL,
ssn NUMBER(10)
);
ALTER TABLE physician ADD PRIMARY KEY (employeee_id);
FOREIGN KEY (physician)
REFERENCES trained_in(physician,treatment);
INSERT INTO physician (employeee_id, name, position, ssn)
VALUES (1, 'Tom', 'doctor', 123);
INSERT INTO physician (employeee_id, name, position, ssn)
VALUES (2, 'Michael', 'managing_director', 456)
);
CREATE TABLE hosp_departments (
departmentid NUMBER(10),
name VARCHAR(10),
head NUMBER(10)
);
ALTER TABLE hosp_departments ADD PRIMARY KEY (employeee_id);
FOREIGN KEY (head)
REFERENCES physician(employeeeid);
INSERT INTO hosp_departments (deprtmentid, name, head)
VALUES (1, 'PETER', 123);
INSERT INTO physician (employeeid, name, position, ssn)
VALUES (2, 'Michael', 456)
);
-------------------------------------------------------------
CREATE TABLE procedure (
code NUMBER(10),
name VARCHAR(10) NOT NULL,
cost NUMBER(10)
)
ALTER TABLE procedure ADD CONSTRAINTS procedure_cons_key PRIMARY KEY (code);
INSERT INTO procedure (code, name, cost)
VALUES (456, 'Tom', 123);
INSERT INTO procedure (code, name, cost)
VALUES (756, 'JAM', 756)
);
CREATE TABLE trained_in (
physician NUMBER(10),
treatment NUMBER(10),
certificationdate DATE,
certificationexpires DATE
);
ALTER TABLE trained_in ADD CONSTRAINTS trained_in_cons_key PRIMARY KEY
(physician,treatment),
INSERT INTO trained_in (physician, treatment, certificationdate,
certificationexpires)
VALUES (1, 45, 26-10-2010, 26-11-2011);
INSERT INTO physician (employeeid, name, position, ssn)
VALUES (2, 58',26-1-2010, 30-12-2011);
CREATE TABLE room (
roomnumber NUMBER(10),
roomtype VARCHAR(10) NOT NULL,
blockfloor NUMBER(10),
blockcode NUMBER(10),
unavailable VARCHAR(1)
)
ALTER TABLE room ADD CONSTRAINTS room_cons_key PRIMARY KEY (roomnumber),
FOREIGN KEY (patient)
REFERENCES trained_in(roomnumber);
INSERT INTO room (roomnumber, roomtype, blockfloor, blockcode, unavailable )
VALUES (47, 'xx',2, 004, 'y');
INSERT INTO room (roomnumber, roomtype, blockfloor, blockcode, unavailable )
VALUES (48, 'xx',3, 005, 'N');
CREATE TABLE stay (
stayid NUMBER(10),
patient NUMBER(10),
room NUMBER(10),
start_time TIMESTAMP,
end_time TIMESTAMP
)
ALTER TABLE stay ADD CONSTRAINTS stay_cons_key PRIMARY KEY (stayid);
ALTER TABLE stay ADD CONSTRAINT stay_pk FOREIGN KEY (patient)
REFERENCES trained_in(roomnumber);
INSERT INTO stay ( stayid, patient, room,start_time, end_time)
VALUES (1, 745, 56, 25-04-2010, 26-05-2010);
INSERT INTO physician (employeeid, name, position, ssn)
VALUES (1, 565, 76, 27-04-2010, 29-05-2010);
CREATE TABLE nurse (
employeeid NUMBER(10),
name VARCHAR(10) NOT NULL,
position VARCHAR(10),
registered VARCHAR(1),
ssn NUMBER(10)
)
ALTER TABLE nurse ADD CONSTRAINTS nurse_cons_key PRIMARY KEY (employeeid);
INSERT INTO nurse (employeeid, name, position,registered, ssn)
VALUES (1, 'Tom', 'doctor',3, 'Y', 123);
INSERT INTO physician (employeeid, name, position,registered, ssn)
VALUES (2, 'Michael', 'managing_director', 'N',456);
CREATE TABLE affiliated_with (
physician NUMBER(10),
department NUMBER(10),
primaryaffiliation VARCHAR(1)
);
ALTER TABLE affiliated_with ADD CONSTRAINTS affiliated_with_cons_key PRIMARY KEY
(physician,department),
FOREIGN KEY (physician,departmentid)
REFERENCES affiliated_with(physician,department);
INSERT INTO affiliated_with (physician, department, primaryaffiliation)
VALUES (1,45,'xx');
INSERT INTO affiliated_with (physician, department, primaryaffiliation)
VALUES (2,96,'xx');
CREATE TABLE prescribes (
physician NUMBER(10),
patient NUMBER(10),
medication NUMBER(10),
"date" TIMESTAMP,
appointment NUMBER(10),
dose VARCHAR(10) NOT NULL
)
ALTER TABLE prescribes ADD CONSTRAINTS prescribes_with_cons_key PRIMARY KEY
(physician,patient,medication,"date"),
FOREIGN KEY (physician)
REFERENCES prescribes(physician,patient,medication,"date");
INSERT INTO prescribes (employeeid, patient,medication,"date", appointment, dose)
VALUES (123, 456, 741, 25-04-2010, 963, 'xx');
INSERT INTO prescribes (employeeid, patient,medication,"date", appointment, dose)
VALUES (456, 789, 753, 26-04-2010, 7463, 'xx');
CREATE TABLE patient (
ssn NUMBER(10),
name VARCHAR(10) NOT NULL,
address VARCHAR(10) NOT NULL,
phone VARCHAR(10) NOT NULL,
insuranceid NUMBER(10),
pcp NUMBER(10)
)
ALTER TABLE patient ADD CONSTRAINTS patient_with_cons_key PRIMARY KEY (ssn),
FOREIGN KEY (patient)
REFERENCES patient(ssn);
INSERT INTO patient (ssn, name, address, phone,insuranceid,pcp )
VALUES (1,'TOM', 'CHICAGO', +782334 , XXXX, 745 );
INSERT INTO patient (ssn, name, address, phone,insuranceid,pcp )
VALUES (2,'RICKY', 'CANADA', +562334 , XXXX, 345 );
CREATE TABLE appointment (
appointmentid NUMBER(10),
patient NUMBER(10),
prepnurse NUMBER(10),
physician NUMBER(10),
start_dt_time TIMESTAMP,
end_dt_time TIMESTAMP,
examinationroom VARCHAR(10) NOT NULL
)
ALTER TABLE appointment ADD CONSTRAINTS appointment_with_cons_key PRIMARY KEY
(appointmentid),
ALTER TABLE appointment ADD CONSTRAINT appointment_pk_key FOREIGN KEY
(appointmentid)
FOREIGN KEY (appointment)
REFERENCES trained_in(physician,treatment);
ALTER TABLE prescribes ADD CONSTRAINTS prescribes_with_cons_key FOREIGN KEY
(physician)
REFERENCES prescribes(physician,patient,medication,"date");
INSERT INTO appointment (appointmentid, patient, prepnurse, name, position, ssn)
VALUES (1, 7456, 7489,4586, 25-10-2010, 26-10-2010, xxx);
INSERT INTO appointment (appointmentid,patient, prepnurse, name, position, ssn)
VALUES (2, 4865,7456,5876, 27-10-2010, 28-10-2010, xxx);
CREATE TABLE block (
blockfloor NUMBER(10),
blockcode NUMBER(10)
)
ALTER TABLE block ADD CONSTRAINTS block_with_cons_key PRIMARY KEY
(blockfloor,blockcode),
FOREIGN KEY (blockfloor,blockcode)
REFERENCES block(blockfloor,blockcode);
INSERT INTO physician (blockfloor, blockcode)
VALUES (456,788);
INSERT INTO physician (blockfloor, blockcode)
VALUES (475,457)
);
CREATE TABLE medication (
code NUMBER(10),
name VARCHAR(10) NOT NULL,
brand VARCHAR(10) NOT NULL,
description VARCHAR(10) NOT NULL
)
ALTER TABLE medication ADD CONSTRAINTS medication_cons_key PRIMARY KEY (code);
INSERT INTO physician (code, name, brand, description)
VALUES (1, 'zzzz', 'xxxx, 'verified');
INSERT INTO physician (code, name, brand, description)
VALUES (2, 'zzzzz', 'xxxx', 456)
);
CREATE TABLE undergoes (
patient NUMBER(10),
procedure NUMBER(10),
stay NUMBER(10),
"date" TIMESTAMP,
physician NUMBER(10),
assistingnurse NUMBER(10)
)
ALTER TABLE undergoes ADD CONSTRAINTS undergoes_with_cons_key PRIMARY KEY
(patient,procedure,stay,"date"),
ALTER TABLE undergoes ADD CONSTRAINTS undergoes_with_cons_key FOREIGN KEY
(physician)
REFERENCES affiliated_with(physician);
ALTER TABLE undergoes ADD CONSTRAINTS undergoes_with_cons_key FOREIGN KEY
(physician)
REFERENCES affiliated_with(physician);
INSERT INTO physician(patient,procedure,stay,"date",physician,assistingnurse)
VALUES (1,456,854,25-06-2010,856,123);
INSERT INTO physician (patient,procedure,stay,"date",physician,assistingnurse)
VALUES (2,486,884,28-06-2010,756,786 )
);
CREATE TABLE on_call (
nurse NUMBER(10),
blockfloor NUMBER(10),
blockcode NUMBER(10),
oncallstart TIMESTAMP,
oncallend TIMESTAMP
)
ALTER TABLE on_call ADD CONSTRAINTS on_call_with_cons_key PRIMARY KEY
(nurse,blockfloor,blockcode,oncallstart,oncallend),
INSERT INTO on_call (nurse, blockfloor, blockcode, oncallstart, oncallend)
VALUES (1, 123, 586,28-06-2010, 28-06-2012);
INSERT INTO on_call (nurse, blockfloor, blockcode, oncallstart, oncallend)
VALUES (2,156,856, 25-06-2010, 27-06-2012)
);
select avg(salary) from employees;
update employees
set salary = case when salary > (select avg(salary) from employees) then salary+
((salary*5)/100)
when salary = (select avg(salary) from employees) then salary+
((salary*10)/100)
when salary < (select avg(salary) from employees) then salary+
((salary*15)/100)
else salary end;
CREATE TABLE physician (
employeee_id NUMBER(10),
name VARCHAR(10) NOT NULL,
position VARCHAR(10) NOT NULL,
ssn NUMBER(10)
);
ALTER TABLE physician ADD PRIMARY KEY (employeee_id);
INSERT INTO physician (employeeid, name, position, ssn)
VALUES (1, 'Tom', 'doctor', 123);
INSERT INTO physician (employeeid, name, position, ssn)
VALUES (2, 'Michael', 'managing_director', 456);
);
CREATE TABLE procedure (
code NUMBER(10),
name VARCHAR(10) NOT NULL,
cost NUMBER(10)
)
ALTER TABLE procedure ADD CONSTRAINTS procedure_cons_key PRIMARY KEY (code);
CREATE TABLE trained_in (
physician NUMBER(10),
treatment NUMBER(10),
certificationdate DATE,
certificationexpires DATE
);
ALTER TABLE trained_in ADD CONSTRAINTS trained_in_cons_key PRIMARY KEY
(physician,treatment),
FOREIGN KEY (physician)
REFERENCES trained_in(physician,treatment);
CREATE TABLE room (
roomnumber NUMBER(10),
roomtype VARCHAR(10) NOT NULL,
blockfloor NUMBER(10),
blockcode NUMBER(10),
unavailable VARCHAR(1)
)
ALTER TABLE room ADD CONSTRAINTS room_cons_key PRIMARY KEY (roomnumber),
FOREIGN KEY (patient)
REFERENCES trained_in(roomnumber);
CREATE TABLE stay (
stayid NUMBER(10),
patient NUMBER(10),
room NUMBER(10),
start_time TIMESTAMP,
end_time TIMESTAMP
)
ALTER TABLE stay ADD CONSTRAINTS stay_cons_key PRIMARY KEY (stayid);
CREATE TABLE nurse (
employeeid NUMBER(10),
name VARCHAR(10) NOT NULL,
position VARCHAR(10),
registered VARCHAR(1),
ssn NUMBER(10)
)
ALTER TABLE nurse ADD CONSTRAINTS nurse_cons_key PRIMARY KEY (employeeid);
CREATE TABLE hosp_departments (
departmentid NUMBER(10),
name VARCHAR(10),
head NUMBER(10)
);
FOREIGN KEY (head)
REFERENCES physician(employeeeid);
CREATE TABLE affiliated_with (
physician NUMBER(10),
department NUMBER(10),
primaryaffiliation VARCHAR(1)
);
ALTER TABLE affiliated_with ADD CONSTRAINTS affiliated_with_cons_key PRIMARY KEY
(physician,department),
FOREIGN KEY (physician,departmentid)
REFERENCES affiliated_with(physician,department);
CREATE TABLE prescribes (
physician NUMBER(10),
patient NUMBER(10),
medication NUMBER(10),
"date" TIMESTAMP,
appointment NUMBER(10),
dose VARCHAR(10) NOT NULL
)
ALTER TABLE prescribes ADD CONSTRAINTS prescribes_with_cons_key PRIMARY KEY
(physician,patient,medication,"date"),
FOREIGN KEY (physician,patient)
REFERENCES prescribes(physician,patient,medication,"date");
CREATE TABLE patient (
ssn NUMBER(10),
name VARCHAR(10) NOT NULL,
address VARCHAR(10) NOT NULL,
phone VARCHAR(10) NOT NULL,
insuranceid NUMBER(10),
pcp NUMBER(10)
)
ALTER TABLE patient ADD CONSTRAINTS patient_with_cons_key PRIMARY KEY (ssn),
FOREIGN KEY (patient)
REFERENCES patient(ssn);
CREATE TABLE appointment (
appointmentid NUMBER(10),
patient NUMBER(10),
prepnurse NUMBER(10),
physician NUMBER(10),
start_dt_time TIMESTAMP,
end_dt_time TIMESTAMP,
examinationroom VARCHAR(10) NOT NULL
)
ALTER TABLE appointment ADD CONSTRAINTS appointment_with_cons_key PRIMARY KEY
(appointmentid),
FOREIGN KEY (appointment)
REFERENCES appointment(appointmentid);
CREATE TABLE block (
blockfloor NUMBER(10),
blockcode NUMBER(10)
)
ALTER TABLE block ADD CONSTRAINTS block_with_cons_key PRIMARY KEY
(blockfloor,blockcode),
FOREIGN KEY (blockfloor,blockcode)
REFERENCES block(blockfloor,blockcode);
CREATE TABLE medication (
code NUMBER(10),
name VARCHAR(10) NOT NULL,
brand VARCHAR(10) NOT NULL,
description VARCHAR(10) NOT NULL
)
ALTER TABLE medication ADD CONSTRAINTS medication_cons_key PRIMARY KEY (code);
CREATE TABLE undergoes (
patient NUMBER(10),
procedure NUMBER(10),
stay NUMBER(10),
"date" TIMESTAMP,
physician NUMBER(10),
assistingnurse NUMBER(10)
)
ALTER TABLE undergoes ADD CONSTRAINTS undergoes_with_cons_key PRIMARY KEY
(patient,procedure,stay,"date"),
FOREIGN KEY (patient)
REFERENCES undergoes(patient,procedure,stay,"date");
CREATE TABLE on_call (
nurse NUMBER(10),
blockfloor NUMBER(10),
blockcode NUMBER(10),
oncallstart TIMESTAMP,
oncallend TIMESTAMP
)
ALTER TABLE on_call ADD CONSTRAINTS on_call_with_cons_key PRIMARY KEY
(nurse,blockfloor,blockcode,oncallstart,oncallend),
FOREIGN KEY (prepnurse)
REFERENCES on_call(nurse,blockfloor,blockcode,oncallstart,oncallend);
SELECT
e.first_name,
e.last_name,
d.department_name
FROM
employees e,
departments d
WHERE
d.department_id = e.department_id
AND E.EMPLOYEE_ID IN
(SELECT M.MANAGER_ID
FROM departments m );
