project
Never
CREATE sequence tenant_id_seq
START WITH 1 INCREMENT BY 1;
CREATE TABLE
tenants (
tenant_id NUMBER(20),
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
phone_number VARCHAR(20),
date_of_birth DATE,
PRIMARY KEY (tenant_id),
CONSTRAINT email_check CHECK (email LIKE '%@%')
);
CREATE
or replace trigger auto_generate_tenant_id BEFORE
INSERT
ON tenants FOR EACH ROW when (new.tenant_id is null)
BEGIN
select
tenant_id_seq.nextval into :new.tenant_id
from
dual;
END;
/ CREATE sequence apartment_id_seq
START WITH 1 INCREMENT BY 1;
CREATE TABLE
apartments (
apartment_id NUMBER(20),
apartment_number VARCHAR(20),
building_name VARCHAR(50),
rent_amount NUMBER(20),
number_of_bedrooms NUMBER(20),
number_of_bathrooms NUMBER(20),
PRIMARY KEY (apartment_id)
);
CREATE
or replace trigger auto_generate_apartment_id BEFORE
INSERT
ON apartments FOR EACH ROW when (new.apartment_id is null)
BEGIN
select
apartment_id_seq.nextval into :new.apartment_id
from
dual;
END;
/
CREATE TABLE
tenants_apartments (
tenant_id NUMBER(20),
apartment_id NUMBER(20),
PRIMARY KEY (tenant_id, apartment_id),
FOREIGN KEY (tenant_id) REFERENCES tenants (tenant_id) ON DELETE CASCADE,
FOREIGN KEY (apartment_id) REFERENCES apartments (apartment_id) ON DELETE CASCADE
);
CREATE sequence lease_id_seq
START WITH 1 INCREMENT BY 1;
CREATE TABLE
leases (
lease_id NUMBER(20),
tenant_id NUMBER(20),
apartment_id NUMBER(20),
lease_start_date DATE,
lease_end_date DATE,
PRIMARY KEY (lease_id),
FOREIGN KEY (tenant_id) REFERENCES tenants (tenant_id) ON DELETE CASCADE,
FOREIGN KEY (apartment_id) REFERENCES apartments (apartment_id) ON DELETE CASCADE,
CONSTRAINT lease_date_check CHECK (lease_start_date < lease_end_date)
);
CREATE
or replace trigger auto_generate_lease_id BEFORE
INSERT
ON leases FOR EACH ROW when (new.lease_id is null)
BEGIN
select
lease_id_seq.nextval into :new.lease_id
from
dual;
END;
/ CREATE sequence payment_id_seq
START WITH 1 INCREMENT BY 1;
CREATE TABLE
payments (
payment_id NUMBER(20),
lease_id NUMBER(20),
payment_date DATE,
amount_paid NUMBER(20),
amount_due NUMBER(20),
PRIMARY KEY (payment_id),
FOREIGN KEY (lease_id) REFERENCES leases (lease_id) ON DELETE CASCADE,
CONSTRAINT amount_check CHECK (amount_paid <= amount_due)
);
CREATE
or replace trigger auto_generate_payment_id BEFORE
INSERT
ON payments FOR EACH ROW when (new.payment_id is null)
BEGIN
select
payment_id_seq.nextval into :new.payment_id
from
dual;
END;
/
CREATE TABLE
lease_updates (
lease_id NUMBER(20),
old_lease_end_date DATE,
new_lease_end_date DATE,
PRIMARY KEY (lease_id),
FOREIGN KEY (lease_id) REFERENCES leases (lease_id) ON DELETE CASCADE
);
CREATE
or replace TRIGGER update_lease_end_date_check AFTER
UPDATE ON leases FOR EACH ROW
DECLARE p_lease leases % ROWTYPE;
cursor c_lease is
select
*
from
leases
where
lease_id = :new.lease_id;
BEGIN OPEN c_lease;
FETCH c_lease INTO p_lease;
while c_lease % FOUND
LOOP if :new.lease_end_date between p_lease.lease_start_date and p_lease.lease_end_date then raise_application_error (
-20001,
'Lease end date cannot be extended, as the apartment is not available at that time'
);
end if;
FETCH c_lease INTO p_lease;
end
loop;
INSERT INTO
lease_updates
VALUES
(
:old.lease_id,
:old.lease_end_date,
:new.lease_end_date
);
END;
/ CREATE
or replace trigger update_tenant_apartment AFTER
INSERT
ON leases FOR EACH ROW
BEGIN
INSERT INTO
tenants_apartments
VALUES
(:new.tenant_id, :new.apartment_id);
END;
/ CREATE
or replace trigger apartment_available_check BEFORE
INSERT
ON leases FOR EACH ROW
DECLARE p_lease leases % ROWTYPE;
cursor c_lease is
select
*
from
leases
where
apartment_id = :new.apartment_id;
BEGIN OPEN c_lease;
FETCH c_lease INTO p_lease;
while c_lease % FOUND
LOOP if :new.lease_start_date between p_lease.lease_start_date and p_lease.lease_end_date then raise_application_error (-20001, 'apartment is not available');
end if;
FETCH c_lease INTO p_lease;
end
loop;
CLOSE c_lease;
END;
/ CREATE
OR REPLACE PROCEDURE add_tenant (
p_first_name IN tenants.first_name % TYPE,
p_last_name IN tenants.last_name % TYPE,
p_email IN tenants.email % TYPE,
p_phone_number IN tenants.phone_number % TYPE,
p_date_of_birth IN tenants.date_of_birth % TYPE
) AS
BEGIN
INSERT INTO
tenants (
first_name,
last_name,
email,
phone_number,
date_of_birth
)
VALUES
(
p_first_name,
p_last_name,
p_email,
p_phone_number,
p_date_of_birth
);
END;
/ CREATE
OR REPLACE PROCEDURE add_apartment (
p_apartment_number IN apartments.apartment_number % TYPE,
p_building_name IN apartments.building_name % TYPE,
p_rent_amount IN apartments.rent_amount % TYPE,
p_number_of_bedrooms IN apartments.number_of_bedrooms % TYPE,
p_number_of_bathrooms IN apartments.number_of_bathrooms % TYPE
) AS
BEGIN
INSERT INTO
apartments (
apartment_number,
building_name,
rent_amount,
number_of_bedrooms,
number_of_bathrooms
)
VALUES
(
p_apartment_number,
p_building_name,
p_rent_amount,
p_number_of_bedrooms,
p_number_of_bathrooms
);
END;
/ CREATE
OR REPLACE PROCEDURE lease_apartment (
p_tenant_id IN tenants.tenant_id % TYPE,
p_apartment_id IN apartments.apartment_id % TYPE,
p_lease_start_date IN leases.lease_start_date % TYPE,
p_lease_end_date IN leases.lease_end_date % TYPE
) AS v_tenant_count INT;
v_apartment_count INT;
BEGIN
SELECT
COUNT(*) INTO v_tenant_count
FROM
tenants
WHERE
tenant_id = p_tenant_id;
IF v_tenant_count = 0 THEN RAISE_APPLICATION_ERROR (-20001, 'Invalid Tenant ID: ' || p_tenant_id);
END IF;
SELECT
COUNT(*) INTO v_apartment_count
FROM
apartments
WHERE
apartment_id = p_apartment_id;
IF v_apartment_count = 0 THEN RAISE_APPLICATION_ERROR (
-20002,
'Invalid Apartment ID: ' || p_apartment_id
);
END IF;
IF p_lease_end_date <= p_lease_start_date THEN RAISE_APPLICATION_ERROR (
-20003,
'Lease end date must be after the start date'
);
END IF;
delete_obsolete_leases ();
INSERT INTO
leases (
tenant_id,
apartment_id,
lease_start_date,
lease_end_date
)
VALUES
(
p_tenant_id,
p_apartment_id,
p_lease_start_date,
p_lease_end_date
);
END;
/ lease_apartment (22, 1, '2022-JAN-01', '2022-JAN-31');
CREATE
OR REPLACE PROCEDURE pay_rent (p_lease_id IN NUMBER, p_amount IN NUMBER) AS p_amount_due NUMBER;
BEGIN
SELECT
amount_due INTO p_amount_due
FROM
payments
WHERE
lease_id = p_lease_id
ORDER BY
payment_id DESC
FETCH FIRST
1 ROW ONLY;
IF p_amount > p_amount_due THEN RAISE_APPLICATION_ERROR (-20001, 'Amount paid is greater than amount due');
END IF;
INSERT INTO
payments (
payment_id,
lease_id,
payment_date,
amount_paid,
amount_due
)
VALUES
(
payment_id_seq.NEXTVAL,
p_lease_id,
SYSDATE,
p_amount,
p_amount_due - p_amount
);
END;
/ CREATE
OR REPLACE PROCEDURE extend_lease (
p_lease_id IN NUMBER,
p_new_lease_end_date IN DATE
) AS
BEGIN
UPDATE leases
SET
lease_end_date = p_new_lease_end_date
WHERE
lease_id = p_lease_id;
END;
/ CREATE
OR REPLACE PROCEDURE find_leases (p_tenant_id IN NUMBER) AS
BEGIN FOR rec IN (
SELECT
*
FROM
tenants_apartments
WHERE
tenant_id = p_tenant_id
)
LOOP DBMS_OUTPUT.PUT_LINE (
'Tenant ID: ' || rec.tenant_id || ', Apartment ID: ' || rec.apartment_id
);
END
LOOP;
END;
/ CREATE
OR REPLACE PROCEDURE delete_obsolete_leases AS
BEGIN FOR rec IN (
SELECT
tenant_id,
apartment_id
FROM
leases
WHERE
lease_end_date < SYSDATE
)
LOOP
DELETE FROM tenants_apartments
WHERE
tenant_id = rec.tenant_id
AND apartment_id = rec.apartment_id;
END
LOOP;
COMMIT;
END;
/
set
serveroutput on;
CREATE
OR REPLACE PROCEDURE show_leased_apartments AS
BEGIN FOR rec IN (
SELECT
t.tenant_id,
t.first_name || ' ' || t.last_name AS tenant_name,
ap.apartment_number,
ap.rent_amount,
l.lease_start_date,
l.lease_end_date
FROM
tenants t
JOIN leases l ON t.tenant_id = l.tenant_id
JOIN tenants_apartments ta ON t.tenant_id = ta.tenant_id
JOIN apartments ap ON ta.apartment_id = ap.apartment_id
)
LOOP DBMS_OUTPUT.PUT_LINE (
rec.tenant_name || ' - Apartment: ' || rec.apartment_number || ', Rent: ' || rec.rent_amount || ', Lease from: ' || TO_CHAR(rec.lease_start_date, 'DD-MON-YYYY') || ' to ' || TO_CHAR(rec.lease_end_date, 'DD-MON-YYYY')
);
END
LOOP;
END;
/ CREATE
OR REPLACE function second_most_expensive_apartment RETURN NUMBER IS v_apartment_id apartments.apartment_id % TYPE;
v_rent_amount apartments.rent_amount % TYPE;
BEGIN
SELECT
apartment_id,
rent_amount INTO v_apartment_id,
v_rent_amount
FROM
(
SELECT
apartment_id,
rent_amount FROMapartments
ORDER BY
rent_amount DESC
)
WHERE
rownum = 2;
RETURN v_apartment_id;
END;
/
SELECT
apartment_id,
rent_amount
from
(
select
apartment_id,
rent_amount
from
apartments
order by
rent_amount desc
)
where
rownum = 2;
insert into
tenants (
first_name,
last_name,
email,
phone_number,
date_of_birth
)
values
(
'John',
'Doe',
'jdoe1@123',
'123456784540',
'01-JAN-1990'
);
insert into
tenants (
first_name,
last_name,
email,
phone_number,
date_of_birth
)
values
(
'Jane',
'Doe',
'jdoe344@123',
'1234567455450',
'01-FEB-2000'
);
insert into
tenants (
first_name,
last_name,
email,
phone_number,
date_of_birth
)
values
(
'Jim',
'Doe',
'jdoe111@123',
'45455567890',
'01-01-1985'
);
insert into
tenants (
first_name,
last_name,
email,
phone_number,
date_of_birth
)
values
(
'Jill',
'Doe',
'jdoe@123',
'54544334',
'31-DEC-1997'
);
insert into
tenants (
first_name,
last_name,
email,
phone_number,
date_of_birth
)
values
(
'Jack',
'Doe',
'jdoe1213@123',
'5455467890',
'01-OCT-1985'
);
insert into
apartments (
apartment_number,
building_name,
rent_amount,
number_of_bedrooms,
number_of_bathrooms
)
values
('101', 'Building 1', 10000, 2, 1);
insert into
apartments (
apartment_number,
building_name,
rent_amount,
number_of_bedrooms,
number_of_bathrooms
)
values
('202', 'Building 3', 15000, 2, 2);
insert into
apartments (
apartment_number,
building_name,
rent_amount,
number_of_bedrooms,
number_of_bathrooms
)
values
('303', 'Building 2', 20000, 3, 2);
insert into
apartments (
apartment_number,
building_name,
rent_amount,
number_of_bedrooms,
number_of_bathrooms
)
values
('404', 'Building 2', 25000, 3, 3);
insert into
apartments (
apartment_number,
building_name,
rent_amount,
number_of_bedrooms,
number_of_bathrooms
)
values
('505', 'Building 1', 30000, 4, 3);
insert into
leases (
tenant_id,
apartment_id,
lease_start_date,
lease_end_date
)
values
(21, 1, '01-JAN-2022', '01-FEB-2022');
insert into
leases (
tenant_id,
apartment_id,
lease_start_date,
lease_end_date
)
values
(2, 2, '01-JAN-2021', '01-FEB-2023');
insert into
leases (
tenant_id,
apartment_id,
lease_start_date,
lease_end_date
)
values
(3, 3, '01-JAN-2022', '01-FEB-2022');
insert into
leases (
tenant_id,
apartment_id,
lease_start_date,
lease_end_date
)
values
(4, 4, '01-JAN-2022', '01-FEB-2022');
insert into
leases (
tenant_id,
apartment_id,
lease_start_date,
lease_end_date
)
values
(5, 5, '01-JAN-2022', '01-FEB-2022');
begin add_apartment ('123', 'Main', 1000, 2, 1);
add_tenant (
'John',
'Doe',
'jdoe@me.com',
'123-456-7890',
'01-JAN-1990'
);
4 end;
/
SELECT
apartment_id,
rent_amount
from
(
select
apartment_id,
rent_amount
from
apartments
order by
rent_amount desc
)
where
rownum = 1;
--function to get the id of second most expensive apartment
CREATE
OR REPLACE function second_expensive_apartment RETURN NUMBER IS v_apartment_id apartments.apartment_id % TYPE;
v_rent_amount apartments.rent_amount % TYPE;
BEGIN
SELECT
apartment_id,
rent_amount INTO v_apartment_id,
v_rent_amount
FROM
(
SELECT
apartment_id,
rent_amount
FROM
apartments
ORDER BY
rent_amount DESC
)
WHERE
rownum = 2;
RETURN v_apartment_id;
END;
/
create VIEW
longest_leases AS
SELECT
lease_id,
lease_end_date - lease_start_date AS duration
FROM
leases
ORDER BY
duration DESC;
SELECT
*
FROM
longest_leases;
Raw Text
-
Quantum Algorithm to Develop New Materials
1 hour ago
-
Emerging Technologies: Shaping Our Future
1 hour ago
-
bh.0.c.h1l latest direct vi.d.s links
1 hour ago
-
The Alchemist’s Apprentice: The Secret of the Golden Elixir
1 hour ago
-
Free Monopoly Go dice links
1 hour ago
-
✅✅ Spy cam, incest, banned videos ✅✅
2 hours ago
-
https://www.facebook.com/people/Monster-XL-Male-Enhancement-UK/61559959046563/
2 hours ago
-
✅✅ Cheating wife/GF with audio ✅✅
2 hours ago
-
monopoly-go-free-dice-links-may-202
2 hours ago
-
Health First: Promoting Wellness and Well-being
2 hours ago