create tables:
CREATE TABLE COMPANY (
  Company_id          INTEGER        not null,
  Company_name       VARCHAR(50)     not null,
  Website            VARCHAR(50)     not null
  );


CREATE TABLE OFFICES (
  Office_id           INTEGER        not null,
  Address             VARCHAR(50)    not null,
  Phone_Number        INTEGER        not null,
  Company_id          integer        not null
  );

CREATE TABLE CARS (
  Car_id              INTEGER         not null,
  Car_Type            VARCHAR(50)     not null,
  License_No          VARCHAR(50)     not null,
  Car_Brand           VARCHAR(50)     not null,
  Status              VARCHAR(50)     not null
 );

CREATE TABLE CAR_CLASSES (
  Class_id            INTEGER         not null,
  Car_Class           VARCHAR(50)     not null,
  Price               FLOAT           not null
  );

CREATE TABLE RENTALS (
  Rental_id           INTEGER         not null,
  Start_Date          DATE            not null,
  End_Date            DATE            not null,
  Rental_Rate         FLOAT           not null
  );

CREATE TABLE CUSTOMERS (
  Customer_id         INTEGER         not null,
  Customer_name       VARCHAR(50)     not null,
  Address             VARCHAR(50)     not null
  );


  --drop table offices;
--drop table company;
/*ALTER TABLE COMPANY
ADD CONSTRAINT COMPANY_pk
 PRIMARY KEY (Company_id);


ALTER TABLE OFFICES
ADD CONSTRAINT OFFICES_pk
 PRIMARY KEY (Office_id);


ALTER TABLE OFFICES
ADD CONSTRAINT COMPANY_fk
 FOREIGN KEY(Company_id)
 REFERENCES COMPANY(Company_id);
  */


queries:

—working
select LICENSE_NO, CAR_BRAND from cars
where car_type = 'A' and status = 'Free';

SELECT CAR_TYPE, CAR_BRAND
FROM CARS
WHERE OFFICE_ID = (SELECT OFFICE_ID FROM OFFICES
WHERE LOCATION_ID = (SELECT LOCATION_ID FROM OFFICE_LOCATION
WHERE LOCATION = 'Warsaw'))
AND CAR_CLASS IN (SELECT CLASS_ID FROM CAR_CLASS WHERE PRICE BETWEEN 200 AND 500);


—working
SELECT * FROM CUSTOMERS
WHERE CUSTOMER_ID IN(SELECT CUSTOMER_ID FROM RENTALS
WHERE RENTAL_RATE > (SELECT AVG(RENTAL_RATE) FROM RENTALS));

SELECT MM.MANGER_NAME, CC.CAR_TYPE
FROM CARS CC, (SELECT OO.OFFICE_ID, MA.MANGER_NAME FROM OFFICES OO JOIN MANGER MA
ON OO.MANGER_ID = MA.MANGER_ID) MM
WHERE CC.OFFICE_ID = MM.OFFICE_ID
ORDER BY MM.MANGER_NAME ;


—working
SELECT * FROM CARS
WHERE NOT EXISTS (SELECT * FROM RENTALS WHERE RENT.CAR_ID = CAR.CAR_ID )
ORDER BY CAR.CAR_ID;

UPDATE CAR_CLASS SET PRICE = PRICE + 50
WHERE CLASS_ID =3 ;








created triggers and procedures (with and without exceptions):


CREATE OR REPLACE TRIGGER DIS_PRICE_CHANGES
BEFORE DELETE OR INSERT OR UPDATE ON CAR_CLASS
FOR EACH ROW
WHEN (NEW.CLASS_ID > 0)
DECLARE
   PRICE_DIFF number;
   OLDP NUMBER ;
   NEWP NUMBER;
BEGIN
   PRICE_DIFF := :NEW.PRICE  - :OLD.PRICE;
   OLDP := :OLD.PRICE;
   NEWP := :NEW.PRICE;
   sys.dbms_output.put_line('Old Price: ' || :OLD.PRICE);
   sys.dbms_output.put_line('New Price: ' || :NEW.PRICE);
   sys.dbms_output.put_line('Price difference: ' || PRICE_DIFF);

END;


Simple Triggers:
CREATE OR REPLACE TRIGGER DIS_PRICE_CHANGES
BEFORE DELETE OR INSERT OR UPDATE ON CAR_CLASS
FOR EACH ROW
WHEN (NEW.CLASS_ID > 0)
DECLARE
   PRICE_DIFF number;
   OLDP NUMBER ;
   NEWP NUMBER;
BEGIN
   PRICE_DIFF := :NEW.PRICE  - :OLD.PRICE;
   OLDP := :OLD.PRICE;
   NEWP := :NEW.PRICE;
   sys.dbms_output.put_line('Old Price: ' || :OLD.PRICE);
   sys.dbms_output.put_line('New Price: ' || :NEW.PRICE);
   sys.dbms_output.put_line('Price difference: ' || PRICE_DIFF);

END;
——


—This is Trigger for an Error :

—This triggers to output something in the dbms output:
create or replace TRIGGER DIS_PRICE_CHANGES
BEFORE DELETE OR INSERT OR UPDATE ON CAR_CLASS
FOR EACH ROW
WHEN (NEW.CLASS_ID > 0)
DECLARE
   PRICE_DIFF number;
BEGIN
   PRICE_DIFF := :NEW.PRICE  - :OLD.PRICE;
   dbms_output.put_line('Old Price: ' || :OLD.PRICE);
   dbms_output.put_line('New Price: ' || :NEW.PRICE);
   dbms_output.put_line('Price difference: ' || PRICE_DIFF);
END;



create or replace procedure remov_customer(customerID number) as
tot_customer number;
BEGIN
DELETE FROM customer
WHERE customer.customer_id = remov_customer.customerID;
tot_customer := tot_customer - 1;
END;



CREATE OR REPLACE TRIGGER TRIGGER6
BEFORE DELETE OR UPDATE OF CAR_CLASS,CLASS_ID,PRICE OR INSERT ON CAR_CLASS
REFERENCING OLD AS OLD_BUF NEW AS NEW_BUF


BEGIN

IF DELETING AND INSERTING AND UPDATING THEN
--SELECT MAX(TRIG_ID )AS QUE FROM TRI_TABL;
INSERT INTO TRI_TABL (TRIG_ID, TRIG_DATE,CLASST ,NEWVALUE,OLDVALUE) VALUES (1,SYSDATE,CAR_CLASS.NEW_BUF,PRICE.NEW_BUF,PRICE.OLD_BUF);
END IF;



EXCEPTION
    WHEN OTHERS THEN ErrorCode := SQLCODE;


END;



CREATE OR REPLACE PROCEDURE raise_expenses (Car_ID INTEGER, amount REAL) IS
 current_expenses REAL;
 expenses_missing EXCEPTION;
BEGIN
 SELECT EXPENSES INTO current_expenses FROM DO_SERVICE
 WHERE CAR_ID = Car_ID;
 IF current_expenses IS NULL THEN
 RAISE expenses_missing;
 ELSE
 UPDATE DO_SERVICE SET EXPENSES = EXPENES + amount
 WHERE CAR_ID = Car_ID;
 END IF;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 INSERT INTO SERVICE_LOG VALUES (Car_ID, 'No such number');
 WHEN  expenses_missing THEN
 INSERT INTO SERVICELOG VALUES (Car_ID, 'EXPENSES is null');
END;
/

EXECUTE raise_expenses(3,100);