Database.sql

use master
go
create database kupatcholim collate hebrew_100_ci_as
use kupatcholim
go
CREATE TABLE doctors (
doctor_id int identity ,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    specialty VARCHAR(100) NOT NULL,
    working_hours TEXT NOT NULL
	constraint pk_doctor_id primary key (doctor_id)
)
CREATE TABLE patients (
    patients_id INT identity,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
	tz bigint not null,
		constraint pk_patients_id primary key ( patients_id)
)
CREATE TABLE medications (
  medications_id INT identity,
    name varchar(20) NOT NULL, 
    create_date DATE NOT NULL,
    expiration_date DATE NOT NULL,
			constraint pk_medications_id primary key (  medications_id)
)

alter TABLE medications
add  help_for  varchar (50)

alter TABLE medications
add price int  null
 

----èáìú úåøéí
----ìòùåú ááøéøú îçãì ùúîéã ëúåá ìà ì÷çú úøåôä
CREATE TABLE appointments (
    appointments_id INT identity,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    appointment_time DATETIME NOT NULL,
    medication_id INT null,  
   constraint pk_appointments_id primary key (appointments_id)
)
alter TABLE appointments
add constraint fk_appointments_doctors foreign key(doctor_id)
references doctors (doctor_id)

alter TABLE appointments
add constraint fk_appointments_patients foreign key(patient_id)
references patients (patients_id)

alter TABLE appointments
add constraint fk_appointments_medications foreign key( medication_id)
references medications (medications_id)

CREATE TABLE rooms (
   rooms_id INT identity,
    floor_number INT NOT NULL,
    doctor_id INT NOT NULL,
)
alter table rooms
add   constraint pk_rooms_id primary key (rooms_id)
alter TABLE rooms
add constraint fk_rooms_doctors foreign key(doctor_id)
references doctors(doctor_id)

CREATE TABLE more_employees (
   employees_id INT identity,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    job_title VARCHAR(100) NOT NULL,
    working_hours VARCHAR(100) NOT NULL,
	  constraint pk_employees_id primary key (employees_id)
)
CREATE TABLE snifim (
   snifim_id INT identity,
    city VARCHAR(100) NOT NULL,
    street VARCHAR(100) NOT NULL,
    building_number INT NOT NULL,
	  constraint pk_snifim_id primary key (snifim_id)
)

----äëðñú ðúåðéí
INSERT INTO doctors (first_name, last_name, phone, specialty, working_hours) 
VALUES 
('îàéø', 'ëäï', '0501234567', 'øåôà îùôçä', 'øàùåï-çîéùé 08:00-16:00'),
('ãéðä', 'ìåé', '0502345678', 'ëéøåøâéä', 'øàùåï-ùéùé 10:00-18:00'),
('àåøé', 'îøãëé', '0503456789', '÷øãéåìåâéä', 'ùðé-ùéùé 09:00-17:00'),
('ùøä', 'âåìï', '0504567890', 'øåôà îùôçä', 'øàùåï-çîéùé 08:00-14:00'),
('éåñé', 'ñéåï', '0505678901', 'àåøåìåâéä', 'ùðé-ùáú 10:00-20:00'),
('øåðéú', 'òæøéä', '0506789012', 'îåîçéú ìøôåàú éìãéí', 'øàùåï-çîéùé 08:00-12:00'),
('âáøéàì', 'áø÷', '0507890123', 'øåôà òåø', 'ùìéùé-ùéùé 11:00-19:00'),
('çðä', 'ôåâì', '0508901234', 'øôåàä ôðéîéú', 'øàùåï-çîéùé 09:00-16:00'),
('àìé', 'îùä', '0509012345', 'àåøúåôãéä', 'ùðé-ùéùé 09:00-15:00'),
('ìéä', 'èåìãå', '0500123456', 'øåôà îùôçä', 'øàùåï-ùéùé 08:00-18:00');

INSERT INTO patients (first_name, last_name, tz) 
VALUES 
('îðçí', 'áø÷', 123456789),
('éòì', 'ëäï', 987654321),
('øçì', 'ìåé', 123123123),
('éåñé', 'ôåâì', 321321321),
('äãñ', 'îøãëé', 456789123),
('àåøé', 'âåìï', 654987321),
('ìéä', 'øåè', 789654123),
('ãåã', 'òæøéä', 321456987),
('úîø', 'ñéåï', 987123456),
('ùîåàì', 'áøåê', 654321789);

INSERT INTO medications (name, create_date, expiration_date, help_for, price) 
VALUES 
('àñôéøéï', '2023-01-01', '2026-01-01', 'ä÷ìú ëàáéí åãì÷ú', 200),
('àðèéáéåèé÷ä', '2023-03-15', '2025-03-15', 'æéäåîéí çééã÷ééí', 290),
('àåôèìâéï', '2023-02-10', '2025-02-10', 'ä÷ìú ëàá', 240),
('ôøåôøàðåìåì', '2022-06-20', '2025-06-20', 'äôçúú ìçõ ãí', 75),
('àåîôøæåì', '2023-07-01', '2026-07-01', 'äôçúú çåîöéåú á÷éáä', NULL),
('îâðæéåí', '2022-11-15', '2025-11-15', 'äùìîú øîåú îâðæéåí áãí', 200),
('öéôøì÷ñ', '2023-05-25', '2026-05-25', 'èéôåì áãéëàåï åçøãä', 10),
('äéãøå÷ñéæéï', '2023-01-10', '2026-01-10', 'ä÷ìú çøãä', NULL),
('ôðéöéìéï', '2023-04-01', '2025-04-01', 'æéäåîéí çééã÷ééí', 200),
('ñèèéðéí', '2022-09-05', '2025-09-05', 'äôçúú ëåìñèøåì', 20),
('ååéèîéï D', '2023-02-20', '2026-02-20', 'äùìîú åéèîéï D', 20),
('ìåñèøì', '2022-12-10', '2025-12-10', 'ä÷ìú çøãä åãéëàåï', 200),
('ã÷ñîåì', '2023-08-15', '2025-08-15', 'ä÷ìú ëàáéí åãì÷ú', 104);

INSERT INTO appointments (doctor_id, patient_id, appointment_time, medication_id)
VALUES
(1, 1, '2025-02-20 09:00:00', 19),
(2, 2, '2025-02-21 10:30:00', 20),
(1, 3, '2025-02-22 11:00:00', 23),
(3, 4, '2025-02-23 14:00:00', 24),
(6, 5, '2025-02-24 15:30:00', 25),
(5, 6, '2025-02-25 16:00:00', 26),
(7, 7, '2025-02-26 17:00:00', 27);


INSERT INTO rooms (floor_number, doctor_id)
VALUES
(1, 1),
(1, 2),
(2, 3),
(2, 4),
(3, 5),
(3, 6),
(4, 7),
(4, 8),
(5, 9),
(5, 10);

INSERT INTO snifim (city, street, building_number)
VALUES
('úì àáéá', 'øåèùéìã', 12),
('çéôä', 'äâòúåï', 45),
('éøåùìéí', 'áï éäåãä', 33),
('áàø ùáò', 'äãø', 8),
('ðúðéä', 'äéí', 21),
('àùãåã', 'äîàá÷', 17),
('ôúç ú÷ååä', 'äîøõ', 29),
('çåìåï', 'ùãøåú éøåùìéí', 5);

INSERT INTO more_employees (first_name, last_name, phone, job_title, working_hours)
VALUES
('îéëì', 'ëäï', '0523456789', 'îð÷ä', 'øàùåï-ùéùé 07:00-15:00'),
('ãðéàì', 'ìåé', '0509876543', 'îæëéøä', 'øàùåï-çîéùé 08:00-16:00'),
('øðé', 'áø÷', '0531234567', 'éåòõ îñ', 'øàùåï-ùéùé 09:00-18:00'),
('ùøä', 'âåìï', '0548765432', 'ôñéëåìåâéú', 'øàùåï-çîéùé 10:00-18:00'),
('éåàá', 'ñéåï', '0552345678', 'øåàú çùáåï', 'øàùåï-ùéùé 09:00-17:00'),
('ìéä', 'òæøéä', '0563456789', 'òåæø àéùé', 'ùðé-ùáú 09:00-18:00'),
('àåøé', 'îøãëé', '0574567890', 'îðäì àãîéðéñèøèéáé', 'øàùåï-ùéùé 08:00-16:00');

alter table doctors
add  snif_id int

alter TABLE doctors
add constraint fk_asnif_doctors foreign key(snif_id)
references snifim (snifim_id)

alter table doctors
add  amount_of_hours int
UPDATE doctors
SET amount_of_hours = CASE
    WHEN doctor_id = 1 THEN 8
    WHEN doctor_id = 2 THEN 8
    WHEN doctor_id = 3 THEN 6
    WHEN doctor_id = 4 THEN 6
    WHEN doctor_id = 5 THEN 10
    WHEN doctor_id = 6 THEN 4
    WHEN doctor_id = 7 THEN 8
    WHEN doctor_id = 8 THEN 7
    WHEN doctor_id = 9 THEN 6
    WHEN doctor_id = 10 THEN 6
    WHEN doctor_id = 11 THEN 10
    WHEN doctor_id = 12 THEN 8
    END

UPDATE doctors
SET snif_id = CASE
    WHEN doctor_id = 1 THEN 8
    WHEN doctor_id = 2 THEN 6
    WHEN doctor_id = 3 THEN 4
    WHEN doctor_id = 4 THEN 5
    WHEN doctor_id = 5 THEN 8
    WHEN doctor_id = 6 THEN 7
    WHEN doctor_id = 7 THEN 6
    WHEN doctor_id = 8 THEN 2
    WHEN doctor_id = 9 THEN 1
    WHEN doctor_id = 10 THEN 1
    WHEN doctor_id = 11 THEN 2
    WHEN doctor_id = 12 THEN 3
    END






select * from doctors
select * from patients
select * from medications
select * from appointments
select * from more_employees
select * from rooms
select * from snifim