Category : C Source Code
Archive   : SQLVER1.ZIP
Filename : HOSPITAL.CMD

DROP TABLE
HOSPITAL;

DROP TABLE
WARD;

DROP TABLE
LAB;

DROP TABLE
PATIENT;

DROP TABLE
HLAB;

DROP TABLE
STAFF;

DROP TABLE
DOCTOR;


CREATE TABLE HOSPITAL
(
HCODE INT 3,
HNAME CHAR 15,
HADDRESS CHAR 20,
HPHONE CHAR 8,
HBEDS INT 4
);

CREATE TABLE WARD
(
HCODE INT 3,
WCODE INT 3,
WNAME CHAR 15,
WBEDS INT 4
);

CREATE TABLE STAFF
(
HCODE INT 3,
WCODE INT 3,
SEMPNO INT 4,
SNAME CHAR 15,
SDUTY CHAR 8,
SSHIFT CHAR 1,
SSALARY INT 6
);

CREATE TABLE DOCTOR
(
HCODE INT 3,
DOCNO INT 3,
DOCNAME CHAR 15,
DOCSPLTY CHAR 15
);

CREATE TABLE PATIENT
(
PREG INT 6,
PDOCNO INT 3,
PNAME CHAR 15,
PADDRESS CHAR 15,
PBIRTH CHAR 15,
PSEX CHAR 1,
PSSN CHAR 9
);

CREATE TABLE LAB
(
LABNO INT 2,
LNAME CHAR 6,
LADDRESS CHAR 20,
LPHONE CHAR 8
);

CREATE TABLE HLAB
(
HCODE INT 3,
LABNO INT 2
);


INSERT INTO PATIENT VALUES (63827,607,'Rasky P. ','60 Bathurst','Jun 1, 1945','M','100973253');
INSERT INTO PATIENT VALUES (36658,386,'Domb B. ','55 Patina ','Apr 8, 1954','M','660657471');
INSERT INTO PATIENT VALUES (64823,585,'Fraser A.','11 Massey ','May 5, 1960','F','985201776');
INSERT INTO PATIENT VALUES (74835,607,'Bower C. ','15 Ontario ','Oct 16, 1933','M','654811767');
INSERT INTO PATIENT VALUES (18004,453,'Shiu W. ','14 Ivy ','Jan 22, 1916','F','914991452');
INSERT INTO PATIENT VALUES (59076,982,'Miller G.','80 Lawton ','Jun 4, 1971','F','611969044');
INSERT INTO PATIENT VALUES (24024,435,'Fourie M.','40 Donora ','Jul 9, 1966','F','321790059');
INSERT INTO PATIENT VALUES (10995,398,'Lista M. ','58 Olsen ','Nov 7, 1963','M','980862482');
INSERT INTO PATIENT VALUES (39217,982,'Birze H. ','51 Dallas ','Aug 20, 1958','M','740294390');
INSERT INTO PATIENT VALUES (38702,386,'Neal R. ','65 Halsey ','Nov 3, 1949','F','380010217');

INSERT INTO HOSPITAL VALUES (22,'Doctors ','45 Brunswick ','923-5411',412);
INSERT INTO HOSPITAL VALUES (13,'Central ','333 Sherbourne','964-4264',502);
INSERT INTO HOSPITAL VALUES (45,'Childrens','555 University','597-1500',845);
INSERT INTO HOSPITAL VALUES (18,'General ','101 College ','595-3111',987);

INSERT INTO WARD VALUES (22,1,'Recovery ', 10);
INSERT INTO WARD VALUES (13,3,'Intensive Care', 21);
INSERT INTO WARD VALUES (22,6,'Psychiatric ',118);
INSERT INTO WARD VALUES (45,4,'Cardiac ', 55);
INSERT INTO WARD VALUES (22,3,'Maternity ', 34);
INSERT INTO WARD VALUES (13,6,'Psychiatric ', 67);
INSERT INTO WARD VALUES (18,3,'Intensive Care', 10);
INSERT INTO WARD VALUES (45,1,'Recovery ', 13);
INSERT INTO WARD VALUES (18,4,'Cardiac ', 53);
INSERT INTO WARD VALUES (45,3,'Maternity ', 24);
INSERT INTO WARD VALUES (22,2,'Neurology ', 12);

INSERT INTO STAFF VALUES (22,6,1009,'Holmes D. ','Nurse ','M',18500);
INSERT INTO STAFF VALUES (13,6,3754,'Delagi B. ','Nurse ','A',17400);
INSERT INTO STAFF VALUES (22,6,8422,'Bell G. ','Orderly','M',12600);
INSERT INTO STAFF VALUES (22,2,9901,'Newport C. ','Intern ','M',17000);
INSERT INTO STAFF VALUES (45,4,1280,'Anderson R.','Intern ','E',17000);
INSERT INTO STAFF VALUES (22,1,6065,'Ritchie G. ','Nurse ','E',20200);
INSERT INTO STAFF VALUES (13,6,3106,'Hughes J. ','Orderly','A',13500);
INSERT INTO STAFF VALUES (45,1,8526,'Frank H. ','Nurse ','A',19400);
INSERT INTO STAFF VALUES (18,4,6357,'Karplus W. ','Intern ','M',18300);
INSERT INTO STAFF VALUES (22,1,7379,'Colony R. ','Nurse ','M',16300);

INSERT INTO HLAB VALUES (22,56);
INSERT INTO HLAB VALUES (22,84);
INSERT INTO HLAB VALUES (13,16);
INSERT INTO HLAB VALUES (18,56);
INSERT INTO HLAB VALUES (45,16);
INSERT INTO HLAB VALUES (18,84);
INSERT INTO HLAB VALUES (18,16);
INSERT INTO HLAB VALUES (13,42);
INSERT INTO HLAB VALUES (18,42);

INSERT INTO LAB VALUES (56,'Alpha','18 Kipling','929-9611');
INSERT INTO LAB VALUES (84,'Nucro','62 Lyons ','368-9703');
INSERT INTO LAB VALUES (16,'Acton','14 Main ','532-4453');
INSERT INTO LAB VALUES (42,'Clini','55 King ','447-6448');

INSERT INTO DOCTOR VALUES (45,607,'Ashby W. ','Pediatrics');
INSERT INTO DOCTOR VALUES (18,585,'Miller G.','Gynecology');
INSERT INTO DOCTOR VALUES (22,453,'Glass D. ','Pediatrics');
INSERT INTO DOCTOR VALUES (13,435,'Lee A. ','Cardiology');
INSERT INTO DOCTOR VALUES (45,522,'Adams C. ','Neurology ');
INSERT INTO DOCTOR VALUES (22,398,'Best K. ','Urology ');
INSERT INTO DOCTOR VALUES (18,982,'Russ J. ','Cardiology');
INSERT INTO DOCTOR VALUES (22,386,'Stone C. ','Psychiatry');

SELECT*FROM HOSPITAL;
SELECT*FROM WARD;
SELECT*FROM LAB;
SELECT*FROM PATIENT;
SELECT*FROM DOCTOR;
SELECT*FROM HLAB;
SELECT*FROM STAFF;

EXIT;