Questions.sql

USE kupatcholim;
GO
-- ôøåöãåøä ùîðñä ìäëðéñ øåôà çãù ìîòøëú
alter PROCEDURE enterdoctor 
    @first_name VARCHAR(20),
    @last_name VARCHAR(20),
    @phone VARCHAR(15),
    @specialty VARCHAR(50),
    @working_hours VARCHAR(50)
AS
BEGIN
    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM doctors WHERE phone = @phone)
        BEGIN
            INSERT INTO doctors (first_name, last_name, phone, specialty, working_hours)
            VALUES (@first_name, @last_name, @phone, @specialty, @working_hours);
            PRINT 'äøåôà ðåñó áäöìçä';
        END
        ELSE
      BEGIN
            PRINT 'äøùåîä ëáø ÷ééîú áèáìä';
       END
    END TRY
    BEGIN CATCH
        PRINT 'ùâéàä áîäìê äëðñú äðúåðéí: ' + ERROR_MESSAGE();
    END CATCH
END;
GO

EXEC enterdoctor 
    @first_name = 'øçìé', 
    @last_name = 'åøðø', 
    @phone = '0533159911', 
    @specialty = 'ãé÷åø ñéðé', 
    @working_hours = 'øàùåï-çîéùé 08:00-16:00';

	----ôøåöãøåøä ùáåã÷ú àí àôùø ì÷áåò úåø, àéê? äéà áåã÷ú àí øåôà ñôöéôé ðîöà áñðéó ùàðé øåöä åàí ëï ÷åáòú úåø
	----éöéøú ôåð÷öéä ùáåã÷ú äàí äøåôà ùàðé øåöä ðîöà áñðéó ùàðé øåöä
	create function dbo.fn_tor (@snif int,@doctor int)
	returns int	
	as
	begin
	declare @result int
    IF EXISTS (SELECT 1 FROM doctors WHERE doctor_id = @doctor AND snif_id = @snif)
        SET @result = 1;
    ELSE
        SET @result = 0;

    RETURN @result;
END;
----éöéøú äôøåöãåøä ùî÷ééîú àú äáãé÷ä
----ùéîåù áèøðæ÷öéä
	CREATE PROCEDURE ScheduleAppointment
    @patient_id INT,
    @doctor_id INT,
    @snif INT,
    @appointment_time DATETIME,
    @medication_id INT
AS
BEGIN
begin tran
    IF dbo.fn_tor(@snif, @doctor_id) = 1
    BEGIN
        INSERT INTO Appointments (doctor_id, patient_id, appointment_time, medication_id)
        VALUES (@doctor_id, @patient_id, @appointment_time, @medication_id);
        PRINT 'äúåø ð÷áò áäöìçä';
		commit
    END
    ELSE
    BEGIN
        PRINT 'îöèòøéí, àéï áàôùøåúéðå ì÷áåò úåø - äøåôà ìà òåáã áñðéó æä';
		rollback
    END
END;
GO
exec ScheduleAppointment
    @patient_id = 6,  
    @doctor_id = 6,    
    @snif = 7,         
    @appointment_time = '2025-03-01 10:00',
    @medication_id = null;


	
---ôåð÷öéä ùáåã÷ú äàí éù úøåôä ááéú äîø÷çú ìáòéä ùì äîèåôì
	create function dbo.fn_medicition (@sick varchar (50))
	returns varchar (50)
	as
	begin
	declare @result varchar (50)
if exists (select 1 from medications m where m.help_for=@sick)
set @result='äúøåôä ðîöàú áîìàé'
    ELSE
set @result='îöèòøéí àáì àéï'	
  
    RETURN @result;
END;
go
---drop function dbo.fn_medicition
declare @sick varchar(50)
select dbo.fn_medicition('ä÷ìú çøãä')

---ôøåöãåøä ùáåã÷ú òé ôøåöãåøä ðåñôú àéæä úøåôåú éåøãåú áîçéø ëé äú÷åó ùìäï îú÷øá
ALTER PROCEDURE pr_check_expiration_date
AS
BEGIN
    -- éåöøú øùéîä ùì úøåôåú ùäúå÷ó ùìäï òáø á-10 éîéí
    SELECT  name
    FROM medications
    WHERE expiration_date <= GETDATE() - 10; -- úå÷ó ÷øåá
END;
ALTER PROCEDURE pr_update_medication_price (@medication_name VARCHAR(50))
AS
BEGIN
    -- éåöøú èáìä æîðéú ì÷áìú äúåöàåú îäôøåöãåøä ä÷èðä
    CREATE TABLE #TempMedications (name VARCHAR(50));

    -- îëðéñ àú äðúåðéí îäôøåöãåøä ä÷èðä ìèáìä äæîðéú
    INSERT INTO #TempMedications (name)
    EXEC pr_check_expiration_date;

    -- áåã÷ àí äúøåôä ùäåæðä ðîöàú áèáìä äæîðéú
    IF EXISTS (SELECT 1 FROM #TempMedications WHERE name = @medication_name)
    BEGIN
        -- àí äúøåôä ðîöàú, òãëåï äîçéø
        UPDATE medications
        SET price = price * 0.5
        WHERE name = @medication_name;

        PRINT 'äîçéø òåãëï áäöìçä!';
    END
    ELSE
    BEGIN
        PRINT 'äúøåôä ìà ðîöàú áøùéîú äúøåôåú òí úå÷ó ÷øåá!';
    END

END;
declare @medication_name varchar(50)
exec pr_update_medication_price @medication_name='àåôèìâéï'

select * from medications







---áãé÷ä
update  medications
set price=200
where name='àåôèìâéï'


----éöéøú èøéâø ùîúáññ òì äôøåöãåøä 
----åëì ôòí ùäîçéø ùì úøåôä îúòãëï 
----äåà áåã÷ ùäîçéø âãåì éåúø î30 ù÷ì
---åàí æä ÷èï îäñëåí
----îòãëï àú îçéø äúøåôä ìîçéø âáåä éåúø
alter trigger tr_cheak_price_medications
on medications 
for update
as
if exists (select 1 from inserted where price>=30)
select 'ä÷ðéä îàåùøú äîùê áúùìåí'
else
       UPDATE medications
        SET price = 100
        WHERE medications_id IN (SELECT medications_id FROM inserted WHERE price < 30);
go

----îùäå ìà îäøùéîä
-----ôåð÷öéä ùáåã÷ú îä äîçéø äî÷ñéîìé ùì äúøåôåú  áùéîåù áVIEW åàåîøú àí äúøåôä ùäåëðñä äéà òåìä äëé é÷ø
alter function dbo.who_is_max (@name varchar(50))
returns varchar(30)
as
begin
declare @price int
select @price= m.price from medications m
where m.name=@name
DECLARE @max INT;
SELECT @max =ms.max_price
FROM MedicationStats ms;
declare @result varchar(50)
if @price=@max
select @result= 'äúøåôä äëé é÷øä'
else
select @result= 'éù é÷øåú éåúø'
return @result
end
go
declare @name varchar(50)
select dbo.who_is_max('àðèéáéåèé÷ä')


select  m.price from medications m
where m.name='àðèéáéåèé÷ä'

SELECT TOP 1 name, price
FROM medications
ORDER BY price DESC;


-----ñì÷è ùîùúîù áôåð÷öéä
----ðùúîù áôåð÷öéä ùáåã÷ú äàí úøåôä äéà äéà äîçéø äî÷ñéîìé 
---åðåñéó àú äñì÷è

select m.name,dbo.who_is_max(m.name) AS price_status from medications m


----VIEW ùîöéâä àú äøåôà ìëì úåø ùì îèåôì
---ùéîåù á  JOIN 
create view join_doctors_patients
as
select d.first_name,d.last_name,d.snif_id,a.appointments_id,p.first_name as family,p.last_name as name from doctors d
join appointments a on a.doctor_id=d.doctor_id
join patients p on p.patients_id=a.patient_id

select * from join_doctors_patients

----îëàï åäìàä ìôé äøùéîä 
----ùéîåù áôåð÷öéä/ôøåöãåøä/VIEW/èøéâø


---ùéîåù áôåð÷öéåú àâøâèéáéåú
CREATE VIEW MedicationStats
AS
SELECT 
    COUNT(medications_id) AS total_medications,
    AVG(price) AS avg_price,          
    MIN(price) AS min_price,        
    MAX(price) AS max_price          
FROM medications

select * from MedicationStats

----ùéîåù áGROUP BY
---ùéîåù áORDER BY
alter VIEW doctors_snifim
AS
SELECT 
    doctor_id, 
    COUNT(DISTINCT snif_id) AS num_of_snifim
FROM doctors
GROUP BY doctor_id;

select  doctor_id, num_of_snifim from doctors_snifim
order by  num_of_snifim desc

--- îùäå ùìà äéä ëúåá áãó---áøéøú îçãì òáåø äúøåôä ùäîèåôì ÷éáì 
	ALTER TABLE Appointments 
ADD CONSTRAINT DF_Medication_Default 
DEFAULT N'ìà éãåò' FOR medication_id;


DELETE FROM appointments
WHERE appointments_id= 13;

-----îçé÷ú ðúåðé èáìä
truncate table rooms

----ùéîåù áàåðéåï
select d.first_name,d.last_name,'øåôà' as status from doctors d
union
select m.first_name,m.last_name,m.job_title from more_employees m

---éöéøú èøéâø ùáëì äåñôä éåñéó ìèáìú ìåâ àú äðúåðéí
create table log(
appointments_id int, 
doctor_id int,
patient_id int,
appointment_time datetime, 
medication_id int
)
create trigger tr_insert_log
on appointments
for insert
as
    INSERT INTO log (appointments_id, doctor_id, patient_id, appointment_time, medication_id)
    SELECT a.appointments_id, a.doctor_id, a.patient_id, a.appointment_time, a.medication_id
    FROM inserted a;
go

select * from log

----ùéîåù áUPDATE åáJOIN
---ðòãëï àú èáìú äçãøéí ðùðä àú äøåôàéí ùòåáãéí áëì çãø   
----åðñúîê òì èáìú äøåôàéí ìøàåú ùéù äúàîä
UPDATE r
SET r.doctor_id = d.doctor_id
FROM rooms r
JOIN doctors d
    ON r.doctor_id = d.doctor_id
WHERE r.floor_number = 3;

select * from rooms

----ùéîåù áWHILE
----ðéöåø ôåð÷öéä ùú÷áì ùí øåôà
-----åúáãå÷ ìôé ëîåú äùòåú ùäåà òåáã áéåí
-----ëîä úåøéí äåà éëåì ìäëðéñ áéåí
alter function dbo.how_many_tores (@name varchar(50),@lastname varchar(50))
returns int
as
begin
declare  @result int=0
declare @amount_of_hours int
set @amount_of_hours=( select  d.amount_of_hours from doctors d
where d.first_name=@name and d.last_name=@lastname)
select @amount_of_hours=@amount_of_hours*60
----ðâéã ùäæîï ääîåöò ùì úåø äåà 20 ã÷åú
while (@amount_of_hours>=20)
begin
 set @amount_of_hours=@amount_of_hours-20
set @result=@result+1
end
return @result
end
go

select dbo.how_many_tores('âáøéàì','áø÷')as amount_of_tores

-----ñì÷è áúåê ñì÷è
----ìà äéä ìé øòéåï àéê ìùìá àú æä áôåð÷öéä
----åäáðúé ùäîåøä àîøä ùàí ìà îåöàéí àôùø áìé
----äùàéìúà ùåìôú àú äøåôà ùéù àìéå äëé äøáä úåøéí

select a.doctor_id,d.first_name,d.last_name,count(a.appointments_id)as amount_of_tores  from appointments a
join doctors d on d.doctor_id=a.doctor_id
group by a.doctor_id,d.first_name,d.last_name
having count(a.appointments_id)=
(select  top 1 count(a.appointments_id) from appointments a
group by doctor_id
)

----ùìåùú ñåâé äñ÷ìè äîáå÷ùéí
----left join
select a.patient_id,p.first_name,p.last_name,m.name
from appointments a
join patients p on p.patients_id=a.patient_id
left join medications m on m.medications_id=a.medication_id

----left join where null
select a.patient_id,p.first_name,p.last_name,m.name
from appointments a
join patients p on p.patients_id=a.patient_id
left join medications m on m.medications_id=a.medication_id
where m.name is null

----left join and
select   a.patient_id,p.first_name,p.last_name,m.name
from appointments a
join patients p on p.patients_id=a.patient_id
left join medications m on m.medications_id=a.medication_id and m.name like 'à%'

-----ùéîåù áCROSS APPLY
select * from appointments a
cross apply(
select  *from medications m 
where m.medications_id=a.medication_id
)m

----outer apply
select * from appointments a
outer apply(
select  *from medications m 
where m.medications_id=a.medication_id
)m

    -----------------sql dynamic
---ùìéôä ùùåìôú àú ëì äùãåú ááú àçú      
        ---ëì èáìä áðôøã
        select 'select'+' ' +STRING_AGG(c.name,',')+' '+'from '+t.name from sys.tables t
        join sys.columns c on t.object_id=c.object_id
        group by t.name





select appointment_time,appointments_id,doctor_id,medication_id,patient_id from appointments
select amount_of_hours,doctor_id,first_name,last_name,phone,snif_id,specialty,working_hours from doctors
select appointment_time,appointments_id,doctor_id,medication_id,patient_id from log
select create_date,expiration_date,help_for,medications_id,name,price from medications
select employees_id,first_name,job_title,last_name,phone,working_hours from more_employees
select first_name,last_name,patients_id,tz from patients
select doctor_id,floor_number,rooms_id from rooms
select building_number,city,snifim_id,street from snifim