3ZmSwVf8

1create tables:
2CREATE TABLE COMPANY (
3  Company_id          INTEGER        not null,
4  Company_name       VARCHAR(50)     not null,
5  Website            VARCHAR(50)     not null 
6  );
7
8
9CREATE TABLE OFFICES (
10  Office_id           INTEGER        not null,
11  Address             VARCHAR(50)    not null,
12  Phone_Number        INTEGER        not null,
13  Company_id          integer        not null
14  );
15
16CREATE TABLE CARS (
17  Car_id              INTEGER         not null,
18  Car_Type            VARCHAR(50)     not null,
19  License_No          VARCHAR(50)     not null,
20  Car_Brand           VARCHAR(50)     not null,
21  Status              VARCHAR(50)     not null
22 );
23
24CREATE TABLE CAR_CLASSES (
25  Class_id            INTEGER         not null,
26  Car_Class           VARCHAR(50)     not null,
27  Price               FLOAT           not null
28  );
29
30CREATE TABLE RENTALS (
31  Rental_id           INTEGER         not null,
32  Start_Date          DATE            not null,
33  End_Date            DATE            not null,
34  Rental_Rate         FLOAT           not null
35  );
36
37CREATE TABLE CUSTOMERS (
38  Customer_id         INTEGER         not null,
39  Customer_name       VARCHAR(50)     not null,
40  Address             VARCHAR(50)     not null
41  );
42  
43  
44  --drop table offices;
45--drop table company;
46/*ALTER TABLE COMPANY 
47ADD CONSTRAINT COMPANY_pk
48 PRIMARY KEY (Company_id);
49
50
51ALTER TABLE OFFICES
52ADD CONSTRAINT OFFICES_pk
53 PRIMARY KEY (Office_id);
54
55
56ALTER TABLE OFFICES
57ADD CONSTRAINT COMPANY_fk
58 FOREIGN KEY(Company_id)
59 REFERENCES COMPANY(Company_id);
60  */
61
62
63queries:
64
65—working
66select LICENSE_NO, CAR_BRAND from cars 
67where car_type = 'A' and status = 'Free';
68
69SELECT CAR_TYPE, CAR_BRAND
70FROM CARS
71WHERE OFFICE_ID = (SELECT OFFICE_ID FROM OFFICES
72WHERE LOCATION_ID = (SELECT LOCATION_ID FROM OFFICE_LOCATION
73WHERE LOCATION = 'Warsaw'))
74AND CAR_CLASS IN (SELECT CLASS_ID FROM CAR_CLASS WHERE PRICE BETWEEN 200 AND 500);
75
76
77—working
78SELECT * FROM CUSTOMERS
79WHERE CUSTOMER_ID IN(SELECT CUSTOMER_ID FROM RENTALS 
80WHERE RENTAL_RATE > (SELECT AVG(RENTAL_RATE) FROM RENTALS));
81
82SELECT MM.MANGER_NAME, CC.CAR_TYPE
83FROM CARS CC, (SELECT OO.OFFICE_ID, MA.MANGER_NAME FROM OFFICES OO JOIN MANGER MA
84ON OO.MANGER_ID = MA.MANGER_ID) MM
85WHERE CC.OFFICE_ID = MM.OFFICE_ID
86ORDER BY MM.MANGER_NAME ;
87
88
89—working
90SELECT * FROM CARS
91WHERE NOT EXISTS (SELECT * FROM RENTALS WHERE RENT.CAR_ID = CAR.CAR_ID )
92ORDER BY CAR.CAR_ID;
93
94UPDATE CAR_CLASS SET PRICE = PRICE + 50
95WHERE CLASS_ID =3 ;
96
97
98
99
100
101
102
103
104created triggers and procedures (with and without exceptions):
105
106
107CREATE OR REPLACE TRIGGER DIS_PRICE_CHANGES
108BEFORE DELETE OR INSERT OR UPDATE ON CAR_CLASS
109FOR EACH ROW
110WHEN (NEW.CLASS_ID > 0)
111DECLARE
112   PRICE_DIFF number;
113   OLDP NUMBER ;
114   NEWP NUMBER;
115BEGIN
116   PRICE_DIFF := :NEW.PRICE  - :OLD.PRICE;
117   OLDP := :OLD.PRICE;
118   NEWP := :NEW.PRICE;
119   sys.dbms_output.put_line('Old Price: ' || :OLD.PRICE);
120   sys.dbms_output.put_line('New Price: ' || :NEW.PRICE);
121   sys.dbms_output.put_line('Price difference: ' || PRICE_DIFF);
122   
123END;
124
125
126Simple Triggers:
127CREATE OR REPLACE TRIGGER DIS_PRICE_CHANGES
128BEFORE DELETE OR INSERT OR UPDATE ON CAR_CLASS
129FOR EACH ROW
130WHEN (NEW.CLASS_ID > 0)
131DECLARE
132   PRICE_DIFF number;
133   OLDP NUMBER ;
134   NEWP NUMBER;
135BEGIN
136   PRICE_DIFF := :NEW.PRICE  - :OLD.PRICE;
137   OLDP := :OLD.PRICE;
138   NEWP := :NEW.PRICE;
139   sys.dbms_output.put_line('Old Price: ' || :OLD.PRICE);
140   sys.dbms_output.put_line('New Price: ' || :NEW.PRICE);
141   sys.dbms_output.put_line('Price difference: ' || PRICE_DIFF);
142   
143END;
144——
145
146
147—This is Trigger for an Error : 
148	
149—This triggers to output something in the dbms output:
150create or replace TRIGGER DIS_PRICE_CHANGES
151BEFORE DELETE OR INSERT OR UPDATE ON CAR_CLASS
152FOR EACH ROW
153WHEN (NEW.CLASS_ID > 0)
154DECLARE
155   PRICE_DIFF number;
156BEGIN
157   PRICE_DIFF := :NEW.PRICE  - :OLD.PRICE;
158   dbms_output.put_line('Old Price: ' || :OLD.PRICE);
159   dbms_output.put_line('New Price: ' || :NEW.PRICE);
160   dbms_output.put_line('Price difference: ' || PRICE_DIFF);
161END;
162
163
164
165create or replace procedure remov_customer(customerID number) as 
166tot_customer number;
167BEGIN
168DELETE FROM customer 
169WHERE customer.customer_id = remov_customer.customerID;
170tot_customer := tot_customer - 1;
171END;
172
173
174
175CREATE OR REPLACE TRIGGER TRIGGER6
176BEFORE DELETE OR UPDATE OF CAR_CLASS,CLASS_ID,PRICE OR INSERT ON CAR_CLASS 
177REFERENCING OLD AS OLD_BUF NEW AS NEW_BUF 
178
179
180BEGIN
181
182IF DELETING AND INSERTING AND UPDATING THEN 
183--SELECT MAX(TRIG_ID )AS QUE FROM TRI_TABL; 
184INSERT INTO TRI_TABL (TRIG_ID, TRIG_DATE,CLASST ,NEWVALUE,OLDVALUE) VALUES (1,SYSDATE,CAR_CLASS.NEW_BUF,PRICE.NEW_BUF,PRICE.OLD_BUF);
185END IF;
186
187
188
189EXCEPTION
190    WHEN OTHERS THEN ErrorCode := SQLCODE;
191
192
193END;
194
195
196
197CREATE OR REPLACE PROCEDURE raise_expenses (Car_ID INTEGER, amount REAL) IS
198 current_expenses REAL;
199 expenses_missing EXCEPTION;
200BEGIN
201 SELECT EXPENSES INTO current_expenses FROM DO_SERVICE
202 WHERE CAR_ID = Car_ID;
203 IF current_expenses IS NULL THEN
204 RAISE expenses_missing;
205 ELSE
206 UPDATE DO_SERVICE SET EXPENSES = EXPENES + amount
207 WHERE CAR_ID = Car_ID;
208 END IF;
209EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 INSERT INTO SERVICE_LOG VALUES (Car_ID, 'No such number');
212 WHEN  expenses_missing THEN
213 INSERT INTO SERVICELOG VALUES (Car_ID, 'EXPENSES is null');
214END; 
215/
216
217EXECUTE raise_expenses(3,100);