INSERT INTO contracts (property_id, client_id, owner_id, user_id, written_date,
start_date, expire_date, price)
VALUES
(7, 4, 2, 3, '2024-01-01 [Link]', '2024-01-15 [Link]', '2025-01-14 [Link]',
15000.00);
GO
CREATE TRIGGER PreventDuplicateActiveContract
ON contracts
AFTER INSERT
AS
BEGIN
DECLARE @ClinetID INT, @StartDate DATE, @ExpireDate DATE;
SELECT
@ClinetID = client_id,
@StartDate = start_date,
@ExpireDate = expire_date
FROM INSERTED;
IF EXISTS (
SELECT 1
FROM contracts
WHERE client_id = @ClinetID
AND expire_date >= GETDATE()
AND id <> (SELECT id FROM INSERTED)
)
BEGIN
RAISERROR ('Client already has an active contract', 16, 1);
ROLLBACK;
END
END;
GO
--5
------------------------------------------
-- USING FILTERED INDEX ON PRICE WHERE APPROVE = 1
SELECT *
FROM properties
WITH (INDEX(idx_properties_price_filtered))
WHERE approve = 1
AND price > 300;
-- NON-CLUSTERED ON NATIONAL NUMBER (FOR USER OR CLIENT IS THE SAME)
SELECT *
FROM users
WHERE national_number = '01JFZBF790FCZP8ZHQSVEN8GBP';
SELECT *
FROM clients
WHERE national_number = '01JFZAXTEQ383PD36VNF1YME8R';
-- NON-CLUSTERED ON PHONE NUMBER (FOR USER OR CLIENT IS THE SAME)
SELECT *
FROM users
WHERE phone_number = '+595 605 287 2832';
SELECT *
FROM clients
WHERE phone_number = '+33 511 126 1415';
-- USING INCLUDING INDEX
SELECT property_id, written_date
FROM contracts
WHERE client_id = 30;
--------------------------
select * from clients;
select * from users;
select * from properties;
select * from cities;
select * from regions;
select * from states;
select * from contracts;
select * from client_property;
--------------------------
delete from contracts where id = 3;
SELECT
COUNT(DISTINCT national_number) AS distinct_values,
COUNT(*) AS total_rows,
CAST(COUNT(DISTINCT national_number) AS FLOAT) / COUNT(*) AS selectivity_ratio
FROM Clients;
/*
DROP TABLE Files;
DROP TABLE Client_Property;
DROP TABLE Properties;
DROP TABLE Regions;
DROP TABLE Cities;
DROP TABLE States;
DROP TABLE Contracts;
DROP TABLE Requests;
DROP TABLE Users;
DROP TABLE Clients;*/
