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
-
M3ga folder free download
18 min ago
-
HD mega T3eeeeen C@P
19 min ago
-
Get access Free teeen m3ga folder
19 min ago
-
100gb teen mega pack
20 min ago
-
Free HD folder without ads
20 min ago
-
C-P teeen video links
21 min ago
-
New Age c#p t33n
21 min ago
-
Mega T3eeeeen C@P video
22 min ago
-
https://nippyshare.com/v/02768b
33 min ago
-
Quantum Algorithm to Develop New Materials
1 hour ago