gistfile1.txt

DROP    DATABASE project_one;
CREATE  DATABASE project_one;
CONNECT          project_one;

CREATE TABLE people  (
       id            int           NOT NULL
                                   AUTO_INCREMENT,
       first_name    varchar(80)   NOT NULL,
       last_name     varchar(80)   NOT NULL,

       PRIMARY KEY   (id)
);

CREATE TABLE rooms   (
       id            int           NOT NULL,
       floor         int           NOT NULL,
       num_beds      int           NOT NULL,
       equipment     text          NOT NULL,

       PRIMARY KEY   (id)
);

CREATE TABLE doctors (
       person_id   int             NOT NULL,
       floor       int             NOT NULL,
       specialty   varchar(80)             ,

       PRIMARY KEY (person_id)             ,
       FOREIGN KEY (person_id)     REFERENCES
                                   people (id)
);

CREATE TABLE patients (
       person_id   int             NOT NULL,
       room        int             NOT NULL,
       floor       int             NOT NULL,
       insurance   int                     ,
       diagnosis   text                    ,

       PRIMARY KEY (person_id)             ,
       FOREIGN KEY (person_id)     REFERENCES
                                   people (id)
                                   ON DELETE
                                     CASCADE,
       FOREIGN KEY (room)          REFERENCES
                                   rooms (id)
);

-- CREATE FUNCTION patients_in_room (room_id int)
--        RETURNS int deterministic
--        return
--         count(pt.room) num_patients
--         FROM rooms     r left join
--              patients pt on
--              r.id = pt.room
--         GROUP BY r.id;

CREATE VIEW xrooms AS
       SELECT r.id room_number,
              r.floor floor,
              r.equipment equipment,
              count(pt.room) num_patients,
              concat(p.first_name) patients
       FROM rooms r        LEFT JOIN
            patients pt    ON
            r.id = pt.room LEFT JOIN
            people p       ON
            pt.person_id = p.id
       GROUP BY r.id;

SOURCE project1.v2.values.sql