08 Aug Connect to your TitanAuto pluggable database and execute the following commands to build the tables
you don’t have to submit anything other than the answers to the questions in Step 3 (with screen shot), 6 (with screen shot) and 8 (with screen shot).
1) Connect to your TitanAuto pluggable database and execute the following commands to build the tables we will need for this exercise:
Create TABLE Brands (
BRAND_ID Number,
BRAND_NAME VARCHAR2(50));
INSERT INTO brands (BRAND_ID, BRAND_NAME) VALUES (1,’Audi’);
INSERT INTO brands (BRAND_ID, BRAND_NAME) VALUES (2,’BMW’);
INSERT INTO brands (BRAND_ID, BRAND_NAME) VALUES (3,’Ford’);
INSERT INTO brands (BRAND_ID, BRAND_NAME) VALUES (4,’Honda’);
INSERT INTO brands (BRAND_ID, BRAND_NAME) VALUES (5,’Toyota’);
Create TABLE cars (
CAR_ID Number,
CAR_NAME VARCHAR2(50),
BRAND_ID Number);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (1, ‘Audi R8 Coupe’, 1);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (2, ‘Audi Q2’, 1);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (3, ‘Audi S1’, 1);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (4, ‘BMW 2-Series Cabrio’, 2);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (5, ‘BMW i8’, 2);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (6, ‘Ford Edge’, 3);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (7, ‘Ford Mustang Fastback’, 3);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (8, ‘Honda S2000’, 4);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (9, ‘Honda Legend’, 4);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (10, ‘Toyota GT86’, 5);
INSERT INTO cars (CAR_ID, CAR_NAME, BRAND_ID) VALUES (11, ‘Toyota C-HR’, 5);
2) Create a view called AUDI_CARS that Selects all fields from the CARS table and returns only the Audi cars
3) Select * from AUDI_CARS.
What kind of cars are listed in the AUDI_CARS view (there should be only 1 kind). Attach a screen shot.
4) INSERT some data into the AUDI_CARS view:
car_id = 12 car_name = ‘BMW 1-series Coupe’, brand_id = 2
You are able to update the CARS table by updating the AUDI_CARS view
5) UPDATE the AUDI_CARS view and set
car_name = ‘BMW 1-series Coupe’, brand_id = 2
WHERE card_id = 3
6) Do a select * from AUDI_CARS
Attach a screen shot. What is wrong with this view now?
7) Now, create a view called FORD_CARS that Selects all fields from the CARS table and returns only Ford cars. In there WHERE clause, add WITH CHECK OPTION.
8) Once the view is created, Try the following INSERT statement
INSERT INTO ford_cars(car_name, brand_id) VALUES(‘Audi RS6 Avant’, 1);
Attach a screen shot
What Happened?
Why?
