API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 4th, 2016
139
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 34.69 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE person
  2. (
  3.     personID VARCHAR(10)   NOT NULL,
  4.     lname   VARCHAR(50)   NOT NULL,
  5.     fname   VARCHAR(50)   NOT NULL,
  6.     birth_date DATE     NOT NULL,
  7.     city VARCHAR(20)    ,
  8.     zipcode VARCHAR(10)     ,
  9.     state VARCHAR(20)   ,
  10.     CONSTRAINT pk_person PRIMARY KEY (personID)
  11. );
  12.  
  13. CREATE TABLE states
  14. (
  15.     state VARCHAR(20)   ,
  16.     CONSTRAINT pk_state PRIMARY KEY (state)
  17. );
  18.  
  19. CREATE TABLE phonenumbers
  20. (
  21.     personID VARCHAR(10)        NOT NULL,
  22.     phonetype VARCHAR(20)   ,
  23.     phonenumber VARCHAR(20) ,
  24.     CONSTRAINT pk_phonenumbers PRIMARY KEY (personID, phonetype, phonenumber)
  25. );
  26.  
  27. CREATE TABLE emergencycontacts
  28. (
  29.     personID VARCHAR(10)        NOT NULL,
  30.     emergID VARCHAR(10)          NOT NULL,
  31.     relation VARCHAR(20)    NOT NULL,
  32.     CONSTRAINT pk_emergencycontacts PRIMARY KEY (personID, emergID, relation)
  33. );
  34.  
  35. CREATE TABLE relations
  36. (
  37.     relation VARCHAR(20)    NOT NULL,
  38.     CONSTRAINT pk_relations PRIMARY KEY (relation)
  39. );
  40.  
  41. CREATE TABLE employees
  42. (
  43.     eID VARCHAR(10)          NOT NULL,
  44.     hireDate DATE       NOT NULL,
  45.     CONSTRAINT pk_employees PRIMARY KEY (eID)
  46. );
  47.  
  48. CREATE TABLE physicians
  49. (
  50.     phyID VARCHAR(10)            NOT NULL,
  51.     specialty VARCHAR(20)   NOT NULL,
  52.     pager_no VARCHAR(20)    NOT NULL,
  53.     CONSTRAINT pk_physicians PRIMARY KEY (phyID)
  54. );
  55.  
  56. CREATE TABLE volunteers
  57. (
  58.     vID VARCHAR(10)          NOT NULL,
  59.     CONSTRAINT pk_volunteers PRIMARY KEY (vID)
  60. );
  61.  
  62. CREATE TABLE volunteer_skills
  63. (
  64.     vID VARCHAR(10)                 NOT NULL,
  65.     volunteer_skill VARCHAR(20) ,
  66.     CONSTRAINT pk_volunteer_skills PRIMARY KEY (vID, volunteer_skill)
  67. );
  68.  
  69. CREATE TABLE patients
  70. (
  71.     patID VARCHAR(10) ,
  72.     phyID VARCHAR(10) ,
  73.     contact_date DATE,
  74.     hasInsurance BOOLEAN  ,
  75.     CONSTRAINT pk_patients PRIMARY KEY (patID)
  76. );
  77.  
  78. CREATE TABLE outpatient
  79. (
  80.     patID VARCHAR(10) ,
  81.     scheduled_date DATE,
  82.     CONSTRAINT pk_outpatient PRIMARY KEY (patID)
  83. );
  84.  
  85. CREATE TABLE resident
  86. (
  87.     patID VARCHAR(10) ,
  88.     admitted_date DATE,
  89.     discharge_date DATE,
  90.     CONSTRAINT pk_resident PRIMARY KEY (patID)
  91. );
  92.  
  93. CREATE TABLE visit
  94. (
  95.     visitdate DATE,
  96.     patID VARCHAR(10) ,
  97.     phyID VARCHAR(10) ,
  98.     comment VARCHAR (50),
  99.     CONSTRAINT pk_visit PRIMARY KEY (visitdate, patID)
  100. );
  101.  
  102. CREATE TABLE insurance
  103. (
  104.     patID VARCHAR(10) ,
  105.     plan VARCHAR(20),
  106.     coverge VARCHAR(50),
  107.     CONSTRAINT pk_insurance PRIMARY KEY (plan)
  108. );
  109.  
  110. CREATE TABLE plans
  111. (
  112.     plan VARCHAR(20),
  113.     CONSTRAINT pk_plans PRIMARY KEY (plan)
  114. );
  115. CREATE TABLE rn
  116.   (
  117.   eID VARCHAR(10)  NOT NULL,
  118.   start_date    DATE NOT NULL,
  119.   end_date  DATE ,
  120.   CONSTRAINT pk_RN PRIMARY KEY (eID)
  121.   );
  122.  
  123. CREATE TABLE room
  124.   (
  125.   eID VARCHAR(10)  NOT NULL,
  126.   cc_name VARCHAR(15)   NOT NULL,
  127.   cc_loc   VARCHAR(12)  NOT NULL,
  128.   room_no   VARCHAR(5)  NOT NULL,
  129.   CONSTRAINT pk_room PRIMARY KEY (cc_name,cc_loc,room_no)
  130.   );
  131. CREATE TABLE bed
  132.   (
  133.   patID VARCHAR(10)  NOT NULL,
  134.   cc_name VARCHAR(15)   NOT NULL,
  135.   cc_loc   VARCHAR(12)  NOT NULL,
  136.   room_no   VARCHAR(5)  NOT NULL,
  137.   bed_no    VARCHAR(5)  NOT NULL,
  138.   CONSTRAINT pk_bed PRIMARY KEY (patID,cc_name,cc_loc,room_no,bed_no)
  139.   );
  140. CREATE TABLE care_center
  141.   (
  142.   eID VARCHAR(10)            NOT NULL,
  143.   cc_name VARCHAR(15)   NOT NULL,
  144.   cc_loc   VARCHAR(12)  NOT NULL,
  145.   CONSTRAINT pk_Care_Center PRIMARY KEY (eID,cc_name,cc_loc)
  146.   );
  147.  CREATE TABLE nurses
  148.   (
  149.   has_RNcert    varchar(5)    NOT NULL,
  150.   eID       VARCHAR(10)  NOT NULL,
  151.   cc_name   VARCHAR(15) NOT NULL,
  152.   cc_loc    VARCHAR(12)         ,
  153.   CONSTRAINT pk_nurses PRIMARY KEY (eID)
  154.   );
  155. CREATE TABLE patients
  156. (
  157.     patID VARCHAR(10),
  158.     phyID VARCHAR(10),
  159.     contact_date DATE,
  160.     hasInsurance BOOLEAN  ,
  161.     CONSTRAINT pk_patients PRIMARY KEY (patID)
  162. );
  163.  
  164. CREATE TABLE outpatients
  165. (
  166.     patID VARCHAR(10),
  167.     scheduled_date DATE,
  168.     CONSTRAINT pk_outpatient PRIMARY KEY (patID)
  169. );
  170.  
  171. CREATE TABLE residents
  172. (
  173.     patID VARCHAR(10),
  174.     admitted_date DATE,
  175.     discharge_date DATE,
  176.     CONSTRAINT pk_resident PRIMARY KEY (patID)
  177. );
  178.  
  179. CREATE TABLE visit
  180. (
  181.     visitdate DATE,
  182.     patID VARCHAR(10),
  183.     phyID VARCHAR(10),
  184.     comment VARCHAR (50),
  185.     CONSTRAINT pk_visit PRIMARY KEY (visitdate, patID)
  186. );
  187.  
  188. CREATE TABLE insurance
  189. (
  190.     patID VARCHAR(10),
  191.     plan VARCHAR(20),
  192.     coverge VARCHAR(50),
  193.     CONSTRAINT pk_insurance PRIMARY KEY (plan)
  194. );
  195.  
  196. CREATE TABLE plans
  197. (
  198.     plan VARCHAR(20)
  199.     CONSTRAINT pk_plans PRIMARY KEY (plan)
  200. );
  201.  
  202. CREATE TABLE job_classes
  203. (
  204.     job_class   VARCHAR(50),
  205.     CONSTRAINT pf_job_classes PRIMARY KEY (job_class)
  206. );
  207.  
  208. CREATE TABLE staff
  209. (
  210.     eID VARCHAR(10)     NOT NULL,
  211.     job_class   VARCHAR(50),
  212.     CONSTRAINT pk_staff PRIMARY KEY (eID)
  213. );
  214.  
  215. CREATE TABLE technician
  216. (
  217.     eID VARCHAR(10)     NOT NULL,
  218.     CONSTRAINT pk_technician PRIMARY KEY(eID)
  219. );
  220.  
  221. CREATE TABLE tech_skills
  222. (
  223.     tech_skill VARCHAR(20),
  224.     CONSTRAINT pk_tech_skills PRIMARY KEY(tech_skill)
  225. );
  226.  
  227. CREATE TABLE technicianskills
  228. (
  229.     eID VARCHAR(10) NOT NULL,
  230.     tech_skill VARCHAR(20),
  231.     CONSTRAINT pk_technicianskills PRIMARY KEY (eID, tech_skill)
  232. );
  233.  
  234. CREATE TABLE laboratories
  235. (
  236.     lab_name VARCHAR(30) NOT NULL,
  237.     lab_loc VARCHAR(30) NOT NULL,
  238.     CONSTRAINT pk_laboratories PRIMARY KEY (lab_name, lab_loc)
  239. );
  240.  
  241. CREATE TABLE techlaboratory
  242. (
  243.     eID VARCHAR(10) NOT NULL,
  244.     lab_name VARCHAR(30) NOT NULL,
  245.     lab_loc VARCHAR(30) NOT NULL,
  246.     CONSTRAINT pk_techlaboratory PRIMARY KEY(eID, lab_name, lab_loc)
  247. );
  248.    
  249. ALTER TABLE staff
  250.     ADD CONSTRAINT employees_staff_fk
  251.     FOREIGN KEY (eID)
  252.     REFERENCES employees (eID);
  253.    
  254. ALTER TABLE staff
  255.     ADD CONSTRAINT job_class_staff_fk
  256.     FOREIGN KEY (job_class)
  257.     REFERENCES job_classes (job_class);
  258.    
  259. ALTER TABLE technician
  260.     ADD CONSTRAINT employees_technician_fk
  261.     FOREIGN KEY (eID)
  262.     REFERENCES employees (eID);
  263.    
  264. ALTER TABLE technicianskills
  265.     ADD CONSTRAINT technician_technicianskills_fk
  266.     FOREIGN KEY (eID)
  267.     REFERENCES technician (eID);
  268.    
  269. ALTER TABLE technicianskills
  270.     ADD CONSTRAINT tech_skills_technicianskills_fk
  271.     FOREIGN KEY (tech_skill)
  272.     REFERENCES tech_skills (tech_skill);
  273.  
  274. ALTER TABLE techlaboratory
  275.     ADD CONSTRAINT technician_techlaboratory_fk
  276.     FOREIGN KEY (eID)
  277.     REFERENCES technician (eID);
  278.    
  279. ALTER TABLE techlaboratory
  280.     ADD CONSTRAINT laboratory_techlaboratory_fk
  281.     FOREIGN KEY (lab_name, lab_loc)
  282.     REFERENCES laboratories (lab_name, lab_loc);
  283. ALTER TABLE insurance
  284.     ADD CONSTRAINT insurance_patients_fk
  285.     FOREIGN KEY (patID)
  286.     REFERENCES patients (patID);
  287.  
  288. ALTER TABLE insurance
  289.     ADD CONSTRAINT insurance_plans_fk
  290.     FOREIGN KEY (plan)
  291.     REFERENCES plans (plan);
  292.  
  293. ALTER TABLE patients
  294.     ADD CONSTRAINT patients_physicians_fk
  295.     FOREIGN KEY (phyID)
  296.     REFERENCES physician (phyID);
  297.  
  298. ALTER TABLE patients
  299.     ADD CONSTRAINT patients_person_fk
  300.     FOREIGN KEY (patID)
  301.     REFERENCES  person (personID);
  302.  
  303. ALTER TABLE outpatient
  304.     ADD CONSTRAINT outpatient_patients_fk
  305.     FOREIGN KEY (patID)
  306.     REFERENCES patients (patID);
  307.  
  308. ALTER TABLE visit
  309.     ADD CONSTRAINT visit_outpatient_fk
  310.     FOREIGN KEY (patID)
  311.     REFERENCES outpatient (patID);
  312.  
  313. ALTER TABLE visit
  314.     ADD CONSTRAINT visit_physicians_fk
  315.     FOREIGN KEY (phyID)
  316.     REFERENCES physicians (phyID);
  317.  
  318. ALTER TABLE resident
  319.     ADD CONSTRAINT resident_patients_fk
  320.     FOREIGN KEY (patID)
  321.     REFERENCES patients (patID);
  322. ALTER TABLE person
  323.     ADD CONSTRAINT person_states_fk
  324.     FOREIGN KEY (state)
  325.     REFERENCES  states (state);
  326.    
  327. ALTER TABLE phonenumbers
  328.     ADD CONSTRAINT person_phonenumbers_fk
  329.     FOREIGN KEY (personID)
  330.     REFERENCES  person (personID);
  331.    
  332. ALTER TABLE emergencycontacts
  333.     ADD CONSTRAINT emergencycontacts_relations_fk
  334.     FOREIGN KEY (relation)
  335.     REFERENCES relations (relation);
  336.    
  337. ALTER TABLE emergencycontacts
  338.     ADD CONSTRAINT emergencycontacts_person_fk
  339.     FOREIGN KEY (personID)
  340.     REFERENCES person (personID);
  341.    
  342. ALTER TABLE emergencycontacts
  343.     ADD CONSTRAINT emergencycontacts_contacts_fk
  344.     FOREIGN KEY (emergID)
  345.     REFERENCES person (personID);
  346.  
  347. ALTER TABLE employees
  348.     ADD CONSTRAINT person_employees_fk
  349.     FOREIGN KEY (eID)
  350.     REFERENCES person (personID);
  351.    
  352. ALTER TABLE physicians
  353.     ADD CONSTRAINT person_physicians_fk
  354.     FOREIGN KEY (phyID)
  355.     REFERENCES person (personID);
  356.    
  357. ALTER TABLE volunteers
  358.     ADD CONSTRAINT person_volunteers_fk
  359.     FOREIGN KEY (vID)
  360.     REFERENCES person (personID);
  361.    
  362. ALTER TABLE volunteer_skills
  363.     ADD CONSTRAINT person_volunteer_skills_fk
  364.     FOREIGN KEY (vID)
  365.     REFERENCES volunteers (vID);
  366.  
  367. ALTER TABLE insurance
  368.     ADD CONSTRAINT insurance_patients_fk
  369.     FOREIGN KEY (patID)
  370.     REFERENCES patients (patID);
  371.  
  372. ALTER TABLE insurance
  373.     ADD CONSTRAINT insurance_plans_fk
  374.     FOREIGN KEY (plan)
  375.     REFERENCES plans (plan);
  376.  
  377. ALTER TABLE patients
  378.     ADD CONSTRAINT patients_physicians_fk
  379.     FOREIGN KEY (phyID)
  380.     REFERENCES physicians (phyID);
  381.  
  382. ALTER TABLE patients
  383.     ADD CONSTRAINT patients_person_fk
  384.     FOREIGN KEY (patID)
  385.     REFERENCES  person (personID);
  386.  
  387. ALTER TABLE outpatient
  388.     ADD CONSTRAINT outpatient_patients_fk
  389.     FOREIGN KEY (patID)
  390.     REFERENCES patients (patID);
  391.  
  392. ALTER TABLE visit
  393.     ADD CONSTRAINT visit_outpatient_fk
  394.     FOREIGN KEY (patID)
  395.     REFERENCES outpatient (patID);
  396.  
  397. ALTER TABLE visit
  398.     ADD CONSTRAINT visit_physicians_fk
  399.     FOREIGN KEY (phyID)
  400.     REFERENCES physicians (phyID);
  401.  
  402. ALTER TABLE resident
  403.     ADD CONSTRAINT resident_patients_fk
  404.     FOREIGN KEY (patID)
  405.     REFERENCES patients (patID);
  406. ALTER TABLE care_center
  407.     ADD CONSTRAINT care_center_rn_fk
  408.     FOREIGN KEY (eID)
  409.     REFERENCES rn (eID);
  410. ALTER TABLE rn
  411.     ADD CONSTRAINT rn_nurse_fk
  412.     FOREIGN KEY (eID)
  413.     REFERENCES nurses (eID);
  414. ALTER TABLE nurses
  415.     ADD CONSTRAINT nurse_care_center_fk
  416.     FOREIGN KEY (eID, cc_name, cc_loc)
  417.     REFERENCES care_center (eID, cc_name, cc_loc);
  418. ALTER TABLE room
  419.     ADD CONSTRAINT room_care_center_fk
  420.     FOREIGN KEY (eID, cc_name, cc_loc)
  421.     REFERENCES care_center (eID, cc_name, cc_loc);
  422. ALTER TABLE bed
  423.     ADD CONSTRAINT bed_room_fk
  424.     FOREIGN KEY (cc_name, cc_loc,room_no)
  425.     REFERENCES room (cc_name, cc_loc, room_no);
  426. ALTER TABLE bed
  427.     ADD CONSTRAINT bed_resident_fk
  428.     FOREIGN KEY (patID)
  429.     REFERENCES resident (patID);
  430.  
  431. ALTER TABLE room
  432.     ADD CONSTRAINT room_care_center_fk
  433.     FOREIGN KEY (eID, cc_name, cc_loc)
  434.     REFERENCES care_center (eID, cc_name, cc_loc);
  435. ALTER TABLE bed
  436.     ADD CONSTRAINT bed_room_fk
  437.     FOREIGN KEY (cc_name, cc_loc,room_no)
  438.     REFERENCES room (cc_name, cc_loc, room_no);
  439. ALTER TABLE bed
  440.     ADD CONSTRAINT bed_resident_fk
  441.     FOREIGN KEY (patID)
  442.     REFERENCES resident (patID);
  443. INSERT INTO states VALUES('California');
  444.  
  445. INSERT INTO relations VALUES('Father');
  446. INSERT INTO relations VALUES('Mother');
  447. INSERT INTO relations VALUES('Daughter');
  448. INSERT INTO relations VALUES('Son');
  449. INSERT INTO relations VALUES('Sister');
  450. INSERT INTO relations VALUES('Brother');
  451. INSERT INTO relations VALUES('Grandfather');
  452. INSERT INTO relations VALUES('Grandmother');
  453. INSERT INTO relations VALUES('Wife');
  454. INSERT INTO relations VALUES('Husband');
  455.  
  456. INSERT INTO person VALUES('0010', 'Butler', 'Francis',
  457.     '1963-08-16', 'Fountain Valley', '92708', 'California');        /*Contact*/
  458.        
  459. INSERT INTO person VALUES('0110', 'Butler', 'Adam',
  460.     '1965-05-16', 'Fountain Valley', '92708', 'California');        /*Physician*/
  461.    
  462. INSERT INTO person VALUES('4862', 'Cutler', 'Hilary',
  463.     '1982-07-19', 'Fountain Valley', '92708', 'California');        /*Contact*/
  464.  
  465. INSERT INTO person VALUES('0210', 'Cutler', 'Francis',
  466.     '1985-02-08', 'Fountain Valley', '92708', 'California');        /*Outpatient*/
  467.    
  468. INSERT INTO person VALUES('3591', 'Fower', 'Jake',
  469.     '1981-08-28', 'Fountain Valley', '92708', 'California');        /*Contact*/
  470.  
  471. INSERT INTO person VALUES('0310', 'Fower', 'Cauli',
  472.     '1983-08-28', 'Fountain Valley', '92708', 'California');        /*Resident*/
  473.    
  474. INSERT INTO person VALUES('3258', 'Pettel', 'Courtney',
  475.     '1991-04-13', 'Fountain Valley', '92708', 'California');        /*Contact*/
  476.  
  477. INSERT INTO person VALUES('0410', 'Pettel', 'Caurte',
  478.     '1976-04-13', 'Fountain Valley', '92708', 'California');        /*Volunteer*/
  479.    
  480. INSERT INTO person VALUES('0510', 'Barde', 'Keye',
  481.     '1996-09-13', 'Fountain Valley', '92708', 'California');        /*Nurse*/
  482.        
  483. INSERT INTO person VALUES('0610', 'Care', 'Randy',
  484.     '1991-01-29', 'Fountain Valley', '92708', 'California');        /*Staff*/
  485.        
  486. INSERT INTO person VALUES('0710', 'Caise', 'Gaeme',
  487.     '1978-09-07', 'Fountain Valley', '92708', 'California');        /*Technician*/
  488.        
  489. INSERT INTO person VALUES('0810', 'Sanchez', 'Jose',
  490.     '1996-05-13', 'Fountain Valley', '92708', 'California');        /*Contact*/
  491.        
  492. INSERT INTO person VALUES('0910', 'Reed', 'Hert',
  493.     '1996-06-21', 'Fountain Valley', '92708', 'California');        /*RN*/
  494.        
  495. ----------------------------------------------------------------------
  496.    
  497. INSERT INTO person VALUES('9010', 'Glode', 'Diego',
  498.     '1985-11-10', 'Long Beach', '90804', 'California');     /*Nurse*/
  499.    
  500. INSERT INTO person VALUES('8110', 'Hart', 'Adam',
  501.     '1975-11-11', 'Long Beach', '90804', 'California');     /*Nurse*/
  502.    
  503. INSERT INTO person VALUES('7210', 'Remos', 'Melissa',
  504.     '1999-07-15', 'Long Beach', '90804', 'California');     /*Nurse*/
  505.    
  506. INSERT INTO person VALUES('6310', 'Rede', 'Blake',
  507.     '1991-04-12', 'Long Beach', '90804', 'California');     /*Nurse*/
  508.    
  509. INSERT INTO person VALUES('1387', 'Thorne', 'Velma',
  510.     '1990-04-13', 'Long Beach', '90804', 'California');     /*Contact*/
  511.  
  512. INSERT INTO person VALUES('5410', 'Thorne', 'Rosa',
  513.     '1998-04-07', 'Long Beach', '90804', 'California');     /*Volunteer*/
  514.        
  515. INSERT INTO person VALUES('4510', 'Fior', 'Risa',
  516.     '1990-09-24', 'Long Beach', '90804', 'California');     /*Contact*/
  517.    
  518. INSERT INTO person VALUES('3610', 'Gatte', 'Charlotte',
  519.     '1999-01-22', 'Long Beach', '90804', 'California');     /*Nurse*/
  520.        
  521. INSERT INTO person VALUES('2710', 'Cruise', 'Penelope',
  522.     '1986-06-27', 'Long Beach', '90840', 'California');     /*Technician*/
  523.        
  524. INSERT INTO person VALUES('1810', 'Dunn', 'Adam',
  525.     '1995-12-03', 'Long Beach', '90840', 'California');     /*Nurse*/
  526.    
  527. INSERT INTO person VALUES('0911', 'Green', 'Ashley',
  528.     '1996-02-11', 'Long Beach', '90840', 'California');     /*Nurse*/
  529.    
  530. ----------------------------------------------------------------------
  531.  
  532. INSERT INTO person VALUES('9011', 'Barre', 'Patricia',
  533.     '1999-10-17', 'Whittier', '90602', 'California');   /*Outpatient*/
  534.  
  535. INSERT INTO person VALUES('8112', 'Carte', 'Nancy',
  536.     '1986-03-16', 'Whittier', '90602', 'California');   /*Staff*/
  537.    
  538. INSERT INTO person VALUES('7213', 'Juste', 'Vanessa',
  539.     '1979-11-09', 'Whittier', '90602', 'California');   /*Physician*/
  540.    
  541. INSERT INTO person VALUES('6314', 'Flor', 'Susana',
  542.     '1998-04-01', 'Whittier', '90602', 'California');   /*Contact*/
  543.        
  544. INSERT INTO person VALUES('5415', 'Garcia', 'Flor',
  545.     '1991-06-24', 'Whittier', '90602', 'California');   /*Staff*/
  546.    
  547. INSERT INTO person VALUES('6352', 'Hurte', 'Daniel',
  548.     '1995-09-19', 'Whittier', '90602', 'California');   /*Contact*/
  549.        
  550. INSERT INTO person VALUES('4516', 'Hurte', 'Harold',
  551.     '1999-06-16', 'Whittier', '90602', 'California');   /*Volunteer*/
  552.    
  553. INSERT INTO person VALUES('3617', 'Diaz', 'Charlotte',
  554.     '1994-05-02', 'Whittier', '90602', 'California');   /*Physician*/
  555.    
  556. INSERT INTO person VALUES('2058', 'Gomez', 'Amanda',
  557.     '1976-11-21', 'Whittier', '90602', 'California');   /*Contact*/
  558.        
  559. INSERT INTO person VALUES('2718', 'Gomez', 'Laura',
  560.     '2002-08-25', 'Whittier', '90602', 'California');   /*Outpatient*/
  561.    
  562. INSERT INTO person VALUES('1819', 'Giar', 'Janet',
  563.     '1993-11-13', 'Whittier', '90602', 'California');   /*RN*/
  564.    
  565. INSERT INTO person VALUES('0920', 'Knok', 'Cheyenne',
  566.     '1999-11-22', 'Whittier', '90602', 'California');   /*Physician*/
  567.    
  568.  
  569. ----------------------------------------------------------------------
  570.    
  571. INSERT INTO person VALUES('1011', 'Holmes', 'Jessica',
  572.     '1991-10-21', 'Lakewood', '90712', 'California');   /*Contact*/
  573.    
  574. INSERT INTO person VALUES('1112', 'Barde', 'Adriana',
  575.     '1996-04-24', 'Lakewood', '90712', 'California');   /*Technician*/
  576.    
  577. INSERT INTO person VALUES('1213', 'Garde', 'Jeanette',
  578.     '1983-12-14', 'Lakewood', '90712', 'California');   /*Contact*/
  579.    
  580. INSERT INTO person VALUES('1314', 'Carte', 'Melisa',
  581.     '1994-08-30', 'Lakewood', '90712', 'California');   /*Nurse*/
  582.    
  583. INSERT INTO person VALUES('1415', 'Garcia', 'Susana',
  584.     '1991-06-24', 'Lakewood', '90712', 'California');   /*Volunteer*/
  585.        
  586. INSERT INTO person VALUES('1516', 'Castello', 'Gerald',
  587.     '1993-07-11', 'Lakewood', '90712', 'California');   /*Staff*/
  588.    
  589. INSERT INTO person VALUES('1617', 'Rose', 'Marie',
  590.     '1995-06-14', 'Lakewood', '90712', 'California');   /*Staff*/
  591.    
  592. INSERT INTO person VALUES('1718', 'Booke', 'Helena',
  593.     '2005-04-21', 'Lakewood', '90712', 'California');   /*Outpatient*/
  594.    
  595. INSERT INTO person VALUES('1812', 'Cutler', 'Emily',
  596.     '1983-03-23', 'Lakewood', '90712', 'California');   /*RN*/
  597.    
  598. INSERT INTO person VALUES('1920', 'Cooke', 'Gerald',
  599.     '1998-12-12', 'Lakewood', '90712', 'California');   /*Contact*/
  600.    
  601. ----------------------------------------------------------------------
  602.    
  603. INSERT INTO person VALUES('1111', 'Holmes', 'Catherine',
  604.     '1996-02-20', 'Westminster', '92683', 'California');    /*Outpatient*/
  605.    
  606. INSERT INTO person VALUES('1122', 'Garcia', 'Katherine',
  607.     '1985-07-14', 'Westminster', '92683', 'California');    /*Resident*/
  608.    
  609. INSERT INTO person VALUES('1211', 'Hart', 'Jeanette',
  610.     '1989-10-10', 'Westminster', '92683', 'California');    /*Resident*/
  611.  
  612. INSERT INTO person VALUES('1311', 'Cortez', 'Jennifer',
  613.     '1991-07-15', 'Westminster', '92683', 'California');    /*Contact*/
  614.    
  615. INSERT INTO person VALUES('1411', 'Forke', 'Paulina',
  616.     '1964-09-27', 'Westminster', '92683', 'California');    /*Resident*/
  617.    
  618. INSERT INTO person VALUES('1511', 'Forke', 'Paul',
  619.     '1976-09-19', 'Westminster', '92683', 'California');    /*Physician*/
  620.    
  621. INSERT INTO person VALUES('1611', 'Sahl', 'Sean',
  622.     '1989-12-19', 'Westminster', '92683', 'California');    /*RN*/
  623.        
  624. INSERT INTO person VALUES('1711', 'Penn', 'Jack',
  625.     '2000-08-29', 'Westminster', '92683', 'California');    /*Volunteer*/
  626.    
  627. INSERT INTO person VALUES('1811', 'Vasquez', 'Mark',
  628.     '1986-05-12', 'Westminster', '92683', 'California');    /*Contact*/
  629.    
  630. INSERT INTO person VALUES('1921', 'Sterling', 'Ethan',
  631.     '1996-10-18', 'Westminster', '92683', 'California');    /*Outpatient*/
  632.    
  633.     INSERT INTO employees VALUES('0510', '2015-02-08');
  634.     INSERT INTO employees VALUES('0610', '2016-04-29');
  635.     INSERT INTO employees VALUES('0710', '2015-07-18');
  636.     INSERT INTO employees VALUES('0910', '2016-07-28');
  637.     INSERT INTO employees VALUES('6310', '2014-08-20');
  638.     INSERT INTO employees VALUES('7210', '2015-12-16');
  639.     INSERT INTO employees VALUES('8110', '2008-06-15');
  640.     INSERT INTO employees VALUES('9010', '2012-12-14');
  641.     INSERT INTO employees VALUES('3610', '2016-02-12');
  642.     INSERT INTO employees VALUES('1617', '2016-03-17');
  643.     INSERT INTO employees VALUES('1516', '2014-12-29');
  644.     INSERT INTO employees VALUES('1314', '2016-09-27');
  645.     INSERT INTO employees VALUES('1112', '2016-12-01');
  646.     INSERT INTO employees VALUES('1819', '2015-10-21');
  647.     INSERT INTO employees VALUES('5415', '2014-11-08');
  648.     INSERT INTO employees VALUES('8112', '2010-03-12');
  649.     INSERT INTO employees VALUES('0911', '2014-04-20');
  650.     INSERT INTO employees VALUES('2710', '2011-08-16');
  651.     INSERT INTO employees VALUES('1812', '2009-11-07');
  652.     INSERT INTO employees VALUES('1611', '2016-02-18');
  653.  
  654.     INSERT INTO emergencycontacts VALUES('1921', '1811', 'Brother');
  655.     INSERT INTO emergencycontacts VALUES('0110', '0010', 'Brother');
  656.     INSERT INTO emergencycontacts VALUES('0210', '4862', 'Wife');
  657.     INSERT INTO emergencycontacts VALUES('0310', '3591', 'Husband');
  658.     INSERT INTO emergencycontacts VALUES('0410', '3258', 'Daughter');
  659.     INSERT INTO emergencycontacts VALUES('0710', '0810', 'Father');
  660.     INSERT INTO emergencycontacts VALUES('5410', '1387', 'Sister');
  661.     INSERT INTO emergencycontacts VALUES('1111', '1920', 'Brother');
  662.     INSERT INTO emergencycontacts VALUES('1718', '1011', 'Sister');
  663.     INSERT INTO emergencycontacts VALUES('2718', '2058', 'Mother');
  664.     INSERT INTO emergencycontacts VALUES('4516', '6352', 'Brother');
  665.     INSERT INTO emergencycontacts VALUES('1415', '1213', 'Sister');
  666.     INSERT INTO emergencycontacts VALUES('1711', '1311', 'Sister');
  667.     INSERT INTO emergencycontacts VALUES('1411', '1311', 'Daughter');
  668.     INSERT INTO emergencycontacts VALUES('1211', '1311', 'Sister');
  669.     INSERT INTO emergencycontacts VALUES('1111', '4510', 'Sister');
  670.     INSERT INTO emergencycontacts VALUES('9011', '6314', 'Sister');
  671.  
  672.     INSERT INTO physicians VALUES('0110', 'Family Medicine', '7143549412');
  673.     INSERT INTO physicians VALUES('0920', 'Family Medicine', '13102617494');
  674.     INSERT INTO physicians VALUES('3617', 'Dermatology', '3100546148');
  675.     INSERT INTO physicians VALUES('7213', 'Pediatrics', '9497576486');
  676.     INSERT INTO physicians VALUES('1511', 'Family Medicine', '949756486');
  677.    
  678.     INSERT INTO volunteers VALUES('0410');
  679.     INSERT INTO volunteers VALUES('5410');
  680.     INSERT INTO volunteers VALUES('1415');
  681.     INSERT INTO volunteers VALUES('4516');
  682.     INSERT INTO volunteers VALUES('1711');
  683.    
  684.     INSERT INTO volunteer_skills VALUES('5410', 'Database');
  685.     INSERT INTO volunteer_skills VALUES('1415', 'Blood Pressure');
  686.     INSERT INTO volunteer_skills VALUES('1415', 'Dealing with people');
  687.     INSERT INTO volunteer_skills VALUES('4516', 'Taking phones');
  688.  
  689.     INSERT INTO phonenumbers VALUES('0310', 'Home phone', '3236273581');
  690.     INSERT INTO phonenumbers VALUES('3591', 'Cell phone', '6193485214');
  691.     INSERT INTO phonenumbers VALUES('0210', 'Home phone', '3106348520');
  692.     INSERT INTO phonenumbers VALUES('4862', 'Home phone', '7141236549');
  693.     INSERT INTO phonenumbers VALUES('0110', 'Cell phone', '7149364825');
  694.     INSERT INTO phonenumbers VALUES('0010', 'Cell phone', '7144692853');
  695.     INSERT INTO phonenumbers VALUES('3258', 'Home phone', '3230792435');
  696.     INSERT INTO phonenumbers VALUES('0410', 'Home phone', '7144268520');
  697.     INSERT INTO phonenumbers VALUES('0510', 'Home phone', '3102746925');
  698.     INSERT INTO phonenumbers VALUES('0610', 'Cell phone', '6190620316');
  699.     INSERT INTO phonenumbers VALUES('0710', 'Cell phone', '7143261548');
  700.     INSERT INTO phonenumbers VALUES('0810', 'Cell phone', '3102587463');
  701.     INSERT INTO phonenumbers VALUES('0910', 'Home phone', '3106542933');
  702.     INSERT INTO phonenumbers VALUES('1387', 'Cell phone', '3230269632');
  703.     INSERT INTO phonenumbers VALUES('4510', 'Cell phone', '7146325485');
  704.     INSERT INTO phonenumbers VALUES('6314', 'Home phone', '6193258520');
  705.     INSERT INTO phonenumbers VALUES('6352', 'Home phone', '3103265985');
  706.     INSERT INTO phonenumbers VALUES('2058', 'Cell phone', '7140213652');
  707.     INSERT INTO phonenumbers VALUES('1011', 'Home phone', '7143264710');
  708.     INSERT INTO phonenumbers VALUES('1213', 'Cell phone', '7142536542');
  709.     INSERT INTO phonenumbers VALUES('1920', 'Cell phone', '3103696363');
  710.     INSERT INTO phonenumbers VALUES('1311', 'Cell phone', '6193212030');
  711.     INSERT INTO phonenumbers VALUES('1811', 'Home phone', '9493021563');
  712. INSERT INTO rn VALUES   ('0910', '2001-03-22', NULL),
  713.                         ('1819', '2005-12-10', NULL),
  714.             ('1812', '1995-03-05', NULL),
  715.                         ('1611', '2013-04-20', NULL),
  716.         /*Added an employee(9359) that no longer works*/
  717.             ('9359', '1973-01-30', '2015-06-15');
  718. INSERT INTO care_center VALUES  ('0910', 'Pediatrics', '1'),    /*Care Center #1*/
  719.                 ('1819', 'Cardiologist', '2'),  /*Care Center #2*/
  720.                 ('1812', 'ER', '3'),        /*Care Center #3*/
  721.                 ('1611', 'Pharmacy', '4' ), /*Care Center #4*/
  722.                 ('0910', 'Neurology', '4'); /*Care Center #5*/ 
  723.  
  724.                                                        
  725.  
  726. INSERT INTO nurses VALUES
  727.                         ('1','0910', 'Pediatrics', '1'),            /*RN*/
  728.                         ('0','9010', 'Cardiologist', '2'),
  729.                         ('0','0510', 'ER', '3'),
  730.                         ('0','8110', 'ER', '3'),
  731.                         ('1','1819', 'Cardiologist', '2'),          /*RN*/
  732.                         ('0','7210', 'Neurology', '4' ),
  733.                         ('0','6310', 'Pediatrics', '1'),
  734.                         ('0','3610', 'Cardiologist', '2'),
  735.                         ('0','1810', 'ER', '3'),
  736.                         ('1','1611', 'ER', '3'),                    /*RN*/
  737.                         ('0','0911', 'Cardiologist', '2'),
  738.                         ('0','1314', 'Pharmacy', '4'),
  739.                         ('1','1812', 'Pharmacy', '4');              /*RN*/
  740.  
  741.  
  742. INSERT INTO room VALUES ('0910','Pediatrics', '1', '300'),
  743.                         ('0910','Pediatrics', '1', '301'), 
  744.                         ('0910','Pediatrics', '1', '302'), 
  745.                         ('0910','Pediatrics', '1', '303'), 
  746.                         ('0910','Pediatrics', '1', '304'), 
  747.                         ('0910','Pediatrics', '1', '305'), 
  748.                         ('0910','Pediatrics', '1', '306'), 
  749.                         ('0910','Pediatrics', '1', '307'), 
  750.                         ('0910','Pediatrics', '1', '308'), 
  751.                         ('0910','Pediatrics', '1', '309'), 
  752.                         ('0910','Pediatrics', '1', '310'), 
  753.                         /*Care Center #1*/
  754.    
  755.                         /*Care Center #2*/
  756.                         ('1819', 'Cardiologist', '2', '400'),
  757.                         ('1819', 'Cardiologist', '2','401'),   
  758.                         ('1819', 'Cardiologist', '2','402'),   
  759.                         ('1819', 'Cardiologist', '2','403'),   
  760.                         ('1819', 'Cardiologist', '2','404'),   
  761.                         ('1819', 'Cardiologist', '2','405'),               
  762.                         ('1819', 'Cardiologist', '2','406'),   
  763.                         ('1819', 'Cardiologist', '2','407'),   
  764.                         ('1819', 'Cardiologist', '2', '408'),  
  765.                         ('1819', 'Cardiologist', '2','409'),   
  766.                         ('1819', 'Cardiologist', '2','410'),   
  767.                
  768.                         /*Care Center #3*/ 
  769.                         ('1812', 'ER', '3', '500'),
  770.                         ('1812', 'ER', '3', '520'),
  771.                         ('1812', 'ER', '3', '530'),
  772.                         ('1812', 'ER', '3', '540'),
  773.                         ('1812', 'ER', '3', '550'),        
  774.                         ('1812', 'ER', '3', '560'),
  775.                         ('1812', 'ER', '3', '570'),
  776.                         ('1812', 'ER', '3', '580'),
  777.                         ('1812', 'ER', '3', '590'),
  778.                        
  779.                         /*Care Center #4*/
  780.                         ('1611', 'Pharmacy', '4', '1'),
  781.                         ('1611', 'Pharmacy', '4', '2'),
  782.                         ('1611', 'Pharmacy', '4', '3'),
  783.                        
  784.                         /*Care Center #5*/ 
  785.                         ('0910', 'Neurology', '4', '200'),
  786.                         ('0910', 'Neurology', '4', '201'),
  787.                         ('0910', 'Neurology', '4','202'),
  788.                         ('0910', 'Neurology', '4','203'),
  789.                         ('0910', 'Neurology', '4','204'),
  790.                         ('0910', 'Neurology', '4','205'),
  791.                         ('0910', 'Neurology', '4','210');
  792.    
  793.                         /* Resident Patients */
  794. INSERT INTO bed VALUES  ('0310', 'Pediatrics', '1', '300', '01'),       /*Care Center #1*/
  795.                         ('1122', 'Cardiologist', '2', '400', '05'), /*Care Center #2*/
  796.             ('1211', 'ER', '3', '540', '03'),               /*Care Center #3*/
  797.             ('1411', 'Neurology', '4', '210', '0');     /*Care Center #4*/
  798.     INSERT INTO patients VALUES('0210', '0110', '2016-04-28', TRUE);
  799.     INSERT INTO patients VALUES('0310', '3617', '2016-05-01', TRUE);
  800.     INSERT INTO patients VALUES('9011', '0110', '2016-04-23', TRUE);
  801.     INSERT INTO patients VALUES('2718', '3617', '2016-04-10', TRUE);
  802.     INSERT INTO patients VALUES('1718', '7213', '2016-05-02', TRUE);
  803.     INSERT INTO patients VALUES('1111', '1511', '2016-03-30', TRUE);
  804.     INSERT INTO patients VALUES('1122', '1511', '2016-05-03', TRUE);
  805.     INSERT INTO patients VALUES('1211', '0920', '2016-04-16', TRUE);
  806.     INSERT INTO patients VALUES('1411', '3617', '2016-04-08', TRUE);
  807.     INSERT INTO patients VALUES('1921', '1511', '2016-04-19', TRUE);
  808.    
  809.     INSERT INTO outpatient VALUES('0210', '2016-05-08 14:30:00');
  810.     INSERT INTO outpatient VALUES('9011', '2016-05-10 13:00:00');
  811.     INSERT INTO outpatient VALUES('2718', '2016-05-13 13:30:00');
  812.     INSERT INTO outpatient VALUES('1718', '2016-05-11 15:00:00');
  813.     INSERT INTO outpatient VALUES('1111', '2016-05-06 14:00:00');
  814.     INSERT INTO outpatient VALUES('1921', '2016-05-17 16:00:00');
  815.    
  816.     INSERT INTO resident VALUES('0310', '2016-01-12 03:26:00', NULL);
  817.     INSERT INTO resident VALUES('1122', '2016-02-04 11:49:00', NULL);
  818.     INSERT INTO resident VALUES('1211', '2016-03-17 17:13:00', '2016-05-03 19:55:00');
  819.     INSERT INTO resident VALUES('1411', '2016-02-24 16:40:00', NULL);
  820.    
  821.     INSERT INTO visit VALUES('2016-05-03', '0210', '0110', 'Check up');
  822.     INSERT INTO visit VALUES('2016-05-01', '9011', '0110', 'Check up');
  823.     INSERT INTO visit VALUES('2016-05-04', '2718', '3617', 'Flu shot');
  824.     INSERT INTO visit VALUES('2016-05-05', '1718', '7213', 'Physical');
  825.     INSERT INTO visit VALUES('2016-05-03', '1111', '1511', 'Flu shot');
  826.     INSERT INTO visit VALUES('2016-05-02', '1921', '1511', 'Physcial');
  827.    
  828.     INSERT INTO plans VALUES('Obamacare');
  829.     INSERT INTO plans VALUES('Kaiser');
  830.     INSERT INTO plans VALUES('Aetna');
  831.     INSERT INTO plans VALUES('Cigna');
  832.    
  833.     INSERT INTO insurance VALUES('0210', 'Kaiser', 'Surgeries');
  834.     INSERT INTO insurance VALUES('0310', 'Kaiser', 'Lab tests');
  835.     INSERT INTO insurance VALUES('9011', 'Aetna', 'Surgeries');
  836.     INSERT INTO insurance VALUES('2718', 'Obamacare', 'Doctor visits');
  837.     INSERT INTO insurance VALUES('1718', 'Kaiser', 'Surgeries');
  838.     INSERT INTO insurance VALUES('1111', 'Cigna', 'Supplies');
  839.     INSERT INTO insurance VALUES('1122', 'Aetna', 'Surgeries');
  840.     INSERT INTO insurance VALUES('1211', 'Kaiser', 'Supplies');
  841.     INSERT INTO insurance VALUES('1411', 'Obamacare', 'Lab tests');
  842.     INSERT INTO insurance VALUES('1921', 'Kaiser', 'Surgeries');
  843.     INSERT INTO job_classes VALUES('Receptionist');
  844.     INSERT INTO job_classes VALUES('Janitor');
  845.     INSERT INTO job_classes VALUES('Human resources');
  846.     INSERT INTO job_classes VALUES('Accountants');
  847.     INSERT INTO job_classes VALUES('Social Worker');
  848.  
  849.     INSERT INTO staff VALUES('0610', 'Janitor');
  850.     INSERT INTO staff VALUES('8112', 'Receptionist');
  851.     INSERT INTO staff VALUES('5415', 'Janitor');
  852.     INSERT INTO staff VALUES('1516', 'Social Worker');
  853.     INSERT INTO staff VALUES('1617', 'Accountants');
  854.    
  855.     INSERT INTO technician VALUES('0710');
  856.     INSERT INTO technician VALUES('2710');
  857.     INSERT INTO technician VALUES('1112');
  858.  
  859.     INSERT INTO tech_skills VALUES('Electrician');
  860.     INSERT INTO tech_skills VALUES('Repairing');
  861.     INSERT INTO tech_skills VALUES('Programmer');
  862.  
  863.     INSERT INTO technicianskills VALUES('0710', 'Repairing');
  864.     INSERT INTO technicianskills VALUES('2710', 'Electrician');
  865.     INSERT INTO technicianskills VALUES('1112', 'Programmer');
  866.    
  867.     INSERT INTO laboratories VALUES('Blood Collecting', '3rd Floor');
  868.     INSERT INTO laboratories VALUES('Cat Scan', '2nd Floor');
  869.     INSERT INTO laboratories VALUES('xRay Machine', '4th Floor');
  870.  
  871.     INSERT INTO techlaboratory VALUES(0710, 'Blood Collecting', '3rd Floor');
  872.     INSERT INTO techlaboratory VALUES(2710, 'Cat Scan', '2nd Floor');
  873.     INSERT INTO techlaboratory VALUES(1112, 'xRay Machine', '4th Floor');
  874. select * from visit;
  875. select * from bed;
  876. select * from room;
  877. select * from nurses;
  878. select * from rn;
  879. select * from care_center;
  880. select * from insurance;
  881. select * from employees;
  882. select * from person;
  883. select * from volunteers;
  884. select * from relations;
  885. /*select * from staff;*/
  886. select * from emergencycontacts;
  887. select * from phonenumbers;
  888. select * from states;
  889. select * from volunteer_skills;
  890. select * from physicians;
  891. select * from patients;
  892. select * from resident;
  893. select * from outpatient;
  894. select * from laboratories;
  895. select * from techlaboratory;
  896. select * from tech_skills;
  897. select * from technician;
  898.  
  899. SELECT fname, lname, 'volunteers' AS jobs
  900. FROM person INNER JOIN volunteers
  901. ON person.personID = volunteers.vID
  902. UNION
  903. SELECT fname, lname, 'physician' AS jobs
  904. FROM person INNER JOIN physicians
  905. ON person.personID = physicians.phyID
  906. UNION
  907. SELECT fname, lname, 'nurses' AS jobs
  908. FROM person natural join employees INNER JOIN nurses
  909. ON person.personID = nurses.eID
  910. UNION
  911. SELECT fname, lname, 'technician' AS jobs
  912. FROM person INNER JOIN technician
  913. ON person.PERSONID = technician.eID
  914. UNION
  915. SELECT fname, lname, 'staff' AS jobs
  916. FROM person INNER JOIN staff
  917. ON person.personID = staff.eID;
  918.  
  919.  
  920. --Query 2
  921. SELECT fname, lname
  922. FROM person INNER JOIN volunteers
  923. ON person.personID = volunteers.vID
  924. NATURAL JOIN volunteer_skills
  925. WHERE volunteer_skill IS NULL;
  926.  
  927. --Query 3
  928. SELECT fname, lname
  929. FROM person INNER JOIN patients
  930. ON person.personID = patients.patID
  931. INNER JOIN volunteers
  932. ON patients.patID = volunteers.vID;
  933.  
  934. --Query 4
  935. SELECT fname,lname
  936. FROM person INNER JOIN patients
  937. ON person.personID = patients.patID
  938. INNER JOIN outpatient
  939. ON patients.patID = outpatient.patID
  940. INNER JOIN visit
  941. ON outpatient.patID = visit.patID
  942. GROUP BY fname,lname
  943. HAVING COUNT(visit.patID) = 1;
  944.  
  945. --Query 5
  946. SELECT volunteer_skill, COUNT(*) AS "Number of People With skill"
  947. FROM volunteer_skills
  948. GROUP BY volunteer_skill
  949. UNION
  950. SELECT tech_skill, COUNT(*) AS "Number of People With skill"
  951. FROM technicianskills
  952. WHERE tech_skill IS NOT NULL
  953. GROUP BY tech_skill;
  954.  
  955. --Query 6
  956. SELECT care_center.cc_name, care_center.cc_loc, careCenter.eID FROM
  957. (SELECT * FROM careCenterBedsView) t1 INNER JOIN care_center
  958. ON t1.cc_name = care_center.cc_name
  959. WHERE t1.OccupiedBeds = t1.TotalBeds;
  960.  
  961.  
  962. --Query 7
  963. SELECT person.FNAME, person.LNAME, care_center.cc_name FROM
  964. person INNER JOIN employees ON person.personID = employees.eID
  965. INNER JOIN nurses ON nurses.eID = employees.eID
  966. INNER JOIN rn on rn.eID = nurses.eID
  967. RIGHT JOIN care_center ON rn.eID= care_center.eID
  968. WHERE has_RNcert = TRUE;
  969.  
  970. --Query 9
  971. SELECT lab_name, technicianskills.tech_skill FROM techlaboratory
  972. INNER JOIN technician
  973. ON techlaboratory.eID = technician.eID
  974. INNER JOIN technicianskills on technician.EID = technicianskills.EID
  975. GROUP BY lab_name, tech_skill
  976. HAVING COUNT(tech_skill) > 0;
  977.  
  978. --View 1
  979. CREATE VIEW employeesHiredView AS
  980. SELECT FNAME,LNAME,HIREDATE FROM
  981. employees INNER JOIN person
  982. ON employees.eID = person.personID;
  983.  
  984. --View 2
  985. CREATE VIEW nursesInChargeView AS
  986. SELECT FNAME,LNAME,PHONENUMBER FROM
  987. rn INNER JOIN care_center ON care_center.eID = rn.eID
  988. INNER JOIN nurses ON care_center.eID = nurses.eID
  989. INNER JOIN person ON nurses.eID = person.personID
  990. INNER JOIN phonenumbers ON person.PERSONID = phonenumbers.PERSONID
  991. WHERE nurses.eID = rn.eID;
  992.  
  993. --View 3
  994. CREATE VIEW goodTechnicianView AS
  995. SELECT * FROM technician
  996. NATURAL JOIN technicianskills
  997. WHERE tech_skill IS NOT NULL
  998. GROUP BY eID, tech_skill
  999. HAVING COUNT(tech_skill) > 0;
  1000.  
  1001. --View 4
  1002. CREATE VIEW careCenterTB AS
  1003. SELECT cc_name FROM ( select * from bed)sdf;
  1004. care_center NATURAL JOIN room INNER JOIN bed
  1005. ON room.cc_name = bed.cc_name
  1006. INNER JOIN resident ON bed.patID = resident.patID
  1007. ;
  1008.  
  1009. CREATE VIEW careCenterOB AS
  1010. SELECT cc_name, COUNT(cc_name) AS "OccupiedBeds" FROM
  1011. care_center INNER JOIN room ON care_center.eID = room.eID
  1012. INNER JOIN bed
  1013. ON room.cc_name = bed.cc_name
  1014. WHERE patID IS NOT NULL
  1015. GROUP BY cc_name;
  1016.  
  1017. CREATE VIEW careCenterBedsView AS
  1018. SELECT careCenterTB.cc_name, careCenterTB.TotalBeds, COALESCE(careCenterOB.OccupiedBeds,0)
  1019. AS "OccupiedBeds",careCenterTB.TotalBeds - COALESCE(careCenterOB.OccupiedBeds,0) AS "FreeBeds" FROM
  1020. careCenterTB LEFT OUTER JOIN careCenterOB
  1021. ON careCenterTB.cc_name = careCenterOB.cc_name;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!