SQL>

SQL> DROP TABLE CAR_DETAILS;

Table dropped.

SQL> DROP TABLE CAR_NAMES;

Table dropped.

SQL> DROP TABLE MODEL_DETAILS;

Table dropped.

SQL> DROP TABLE CAR_MAKERS;

Table dropped.

SQL> DROP TABLE COUNTRIES;

Table dropped.

SQL> DROP TABLE CONTINENTS;

Table dropped.

SQL>

SQL> Rem CREATING TABLES

SQL>

SQL> CREATE TABLE CONTINENTS

2 (

3 CONT_ID NUMBER(1),

4 CONTINENT VARCHAR2(15),

5 CONSTRAINT PKEY4 PRIMARY KEY(CONT_ID)

6 );

Table created.

SQL>

SQL> CREATE TABLE COUNTRIES

2 (

3 COUNTRYID NUMBER(2),

4 COUNTRYNAME VARCHAR2(20),

5 CONTINENT NUMBER(2),
 6 CONSTRAINT PKEY5 PRIMARY KEY(COUNTRYID),

7 CONSTRAINT FKEY4 FOREIGN KEY(CONTINENT) REFERENCES CONTINENTS(CONT_ID) ON DELETE

CASCADE

8 );

Table created.

SQL>

SQL> CREATE TABLE CAR_MAKERS

2 (

3 ID NUMBER(2),

4 MAKER VARCHAR2(20),

5 FULLNAME VARCHAR2(30),

6 COUNTRY NUMBER(1),

7 CONSTRAINT PKEY1 PRIMARY KEY(ID),

8 CONSTRAINT FKEY1 FOREIGN KEY(COUNTRY) REFERENCES COUNTRIES(COUNTRYID) ON DELETE

CASCADE

9 );

Table created.

SQL>

SQL>

SQL> CREATE TABLE MODEL_DETAILS

2 (

3 MODELID NUMBER(3),

4 MAKER NUMBER(3),

5 MODEL VARCHAR2(20) unique,

6 CONSTRAINT PKEY6 PRIMARY KEY(MODELID),

7 CONSTRAINT FKEY5 FOREIGN KEY(MAKER) REFERENCES CAR_MAKERS(ID) ON DELETE CASCADE

8 );
Table created.

SQL>

SQL> CREATE TABLE CAR_NAMES

2 (

3 ID NUMBER(3),

4 MODEL VARCHAR2(20),

5 DESCRIPTION VARCHAR2(40),

6 CONSTRAINT PKEY2 PRIMARY KEY(ID),

7 CONSTRAINT FKEY2 FOREIGN KEY(MODEL) REFERENCES MODEL_DETAILS(MODEL) ON DELETE

CASCADE

8 );

Table created.

SQL>

SQL> CREATE TABLE CAR_DETAILS

2 (

3 ID NUMBER(3),

4 MPG NUMBER(3, 1),

5 CYLINDERS NUMBER(2),

6 EDISPL NUMBER(3),

7 HORSEPOWER NUMBER(3),

8 WEIGHT NUMBER(4),

9 ACCELERATE NUMBER(3,1),

10 YEAR NUMBER(4),

11 CONSTRAINT PKEY3 PRIMARY KEY(ID),

12 CONSTRAINT FKEY3 FOREIGN KEY(ID) REFERENCES CAR_NAMES(ID) ON DELETE CASCADE

13 );

Table created.
SQL> Rem 1. Create a view named Datsun_Cars, which display the car id, model and descriptions of
Datsun model.

SQL>

SQL> CREATE OR REPLACE VIEW Datsun_Cars AS

2 SELECT *

3 FROM CAR_NAMES WHERE CAR_NAMES.MODEL='datsun';

View created.

SQL> REM DELETE FROM CAR_DETAILS WHERE ID='407';

SQL> REM DELETE FROM Datsun_Cars WHERE MAKE LIKE 'datsun D';

SQL> SELECT *

2 FROM Datsun_Cars;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

25 datsun datsun pl510

36 datsun datsun pl510

62 datsun datsun 1200

89 datsun datsun 510 (sw)

118 datsun datsun 610

137 datsun datsun b210

153 datsun datsun 710

181 datsun datsun 710

212 datsun datsun b-210

228 datsun datsun f-10 hatchback

249 datsun datsun 810

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

255 datsun datsun b210 gx

 276 datsun datsun 510

281 datsun datsun 200-sx

311 datsun datsun 210

320 datsun datsun 310

328 datsun datsun 510 hatchback

332 datsun datsun 210

341 datsun datsun 280-zx

355 datsun datsun 210 mpg

365 datsun datsun 200sx

371 datsun datsun 810 maxima

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

394 datsun datsun 310 gx

23 rows selected.

SQL>

SQL> SAVEPOINT S1;

Savepoint created.

SQL>

SQL> rem INSERT CHECK

SQL>

SQL> INSERT INTO Datsun_Cars VALUES('800','datsun','newlyadded');

1 row created.

SQL>

SQL> SELECT * FROM CAR_NAMES

 2 WHERE ID=800;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

800 datsun newlyadded

SQL>

SQL> rem UPDATE CHECK

SQL>

SQL> UPDATE Datsun_Cars

2 SET DESCRIPTION='Updated' WHERE ID=800;

1 row updated.

SQL>

SQL> SELECT * FROM CAR_NAMES

2 WHERE ID=800;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

800 datsun Updated

SQL>

SQL> rem DELETE CHECK

SQL>

SQL> DELETE FROM Datsun_cars;

24 rows deleted.

SQL>

SQL> SELECT * FROM CAR_NAMES

 2 WHERE MODEL='datsun';

no rows selected

SQL>

SQL> ROLLBACK TO S1;

Rollback complete.

SQL>

SQL> rem META DATA CHECK

SQL>

SQL> SELECT *

2 FROM user_updatable_columns

3 WHERE table_name='DATSUN_CARS';

OWNER TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME UPD INS DEL

------------------------------ --- --- ---

SYSTEM DATSUN_CARS

ID YES YES YES

SYSTEM DATSUN_CARS

MODEL YES YES YES

SYSTEM DATSUN_CARS

DESCRIPTION YES YES YES

SQL>
SQL> Rem 2.Create a view called Car_List that shows the car id, model, description and operational
parameters of all cars produced during 1974. Make sure that, the year should not be reassigned to
any other value through views.

SQL>

SQL> CREATE OR REPLACE VIEW Car_List AS

2 SELECT
CAR_NAMES.ID,MODEL,DESCRIPTION,MPG,CYLINDERS,EDISPL,HORSEPOWER,WEIGHT,ACCELERATE,
YEAR

3 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

4 WHERE CAR_DETAILS.YEAR=1974;

View created.

SQL> SELECT *

2 FROM Car_List;

ID MODEL DESCRIPTION MPG CYLINDERS EDISPL HORSEPOWER WEIGHT ACCELERATE YEAR

---------- ---------- ---------- ---------- ---------- ---------- -------- -------------------- ------------ ---------------- -------
133 plymouth plymouth duster 20 6 198 95 3102 16.5 1974
134 ford ford maverick 21 6 200 28 75 17 1974
135 amc amc horne 19 6 232 100 2901 16 1974
136 chevrolet chevrolet nov 15 6 250 100 3336 17 1974

137 datsun datsun b 21 31 4 79 1950 19 1974

138 ford ford pinto 26 4 122 80 2451 16.5 1974

139 toyota toyota corolla 1200 32 4 71 65 1836 21 1974

140 chevrolet chevrolet vega 25 4 140 75 2542 17 1974

141 chevrolet chevrolet chevelle malibu classic 16 6 250 100 3781 17 1974
142 amc amc matador 16 6 258 110 3632 18 1974

143 plymouth plymouth satellite sebring 18 6 225 105 3613 16.5 1974

144 ford ford gran torino 16 8 302 140 4141 14 1974

145 buick buick century luxus (sw) 13 8 350 150 4699 14.5 1974

146 dodge dodge coronet custom (sw) 14 8 318 150 4457 13.5 1974

147 ford ford gran torino (sw) 14 8 302 140 4638 16 1974

148 amc amc matador (sw) 14 8 304 150 4257 15.5 1974
149 audi audi fox 29 4 98 83 2219 16.5 1974

150 volkswagen volkswagen dasher 26 4 79 67 1963 15.5 1974

151 opel opel manta 26 4 97 78 2300 14.5 1974

152 toyota toyota corona 31 4 76 52 1649 16.5 1974

153 datsun datsun 710 32 4 83 61 2003 19 1974

154 dodge dodge colt 28 4 90 75 2125 14.5 1974

155 fiat fiat 128 24 4 90 75 2108 15.5 1974

156 fiat fiat 124 tc 26 4 116 75 2246 14 1974

157 honda honda civic 24 4 120 97 2489 15 1974

158 subaru Subaru 26 4 108 93 2391 15.5 1974

159 fiat fiat x1.9 31 4 79 67 2000 16 1974

27 rows selected.

SQL>

SQL> SAVEPOINT S1;

Savepoint created.

SQL>

SQL> rem INSERT CHECK

SQL>

SQL> INSERT INTO Car_List VALUES('800','datsun','newlyadded','20','2','2','100','20','20','1986');

INSERT INTO Car_List VALUES('800','datsun','newlyadded','20','2','2','100','20','20','1986')

ERROR at line 1:

ORA-01776: cannot modify more than one base table through a join view

SQL>

SQL> rem UPDATE CHECK

SQL>

SQL> UPDATE Car_List SET

2 DESCRIPTION='Updated' WHERE YEAR=1974;

27 rows updated.

SQL>

SQL> SELECT
CAR_NAMES.*,CAR_DETAILS.MPG,CAR_DETAILS.CYLINDERS,CAR_DETAILS.EDISPL,CAR_DETAILS.HOR
SEPOWER,CAR_DETAILS.WEIGHT,CAR_DETAILS.ACCELERATE,CAR_DETAILS.YEAR

2 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

3 WHERE YEAR=1974;

ID MODEL DESCRIPTION MPG CYLINDERS EDISPL HORSEPOWER WEIGHT ACCELERATE YEAR

---------- ---------- ---------- ---------- ---------- ---------- --------- -------------------- ------------ --------------- -------

133 plymouth Updated 20 6 198 95 3102 16.5 1974

134 ford Updated 21 6 200 2875 17 1974

135 amc Updated 19 6 232 100 2901 16 1974

136 chevrolet Updated 15 6 250 100 3336 17 1974

137 datsun Updated 31 4 79 67 1950 19 1974

138 ford Updated 26 4 122 80 2451 16.5 1974

139 toyota Updated 32 4 71 65 1836 21 1974

140 chevrolet Updated 25 4 140 75 2542 17 1974

141 chevrolet Updated 16 6 250 100 3781 17 1974

142 amc Updated 16 6 258 110 3632 18 1974

143 plymouth Updated 18 6 225 105 3613 16.5 1974

144 ford Updated 16 8 302 140 4141 14 1974

145 buick Updated 13 8 350 150 4699 14.5 1974

146 dodge Updated 14 8 318 150 4457 13.5 1974

147 ford Updated 14 8 302 140 4638 16 1974

148 amc Updated 14 8 304 150 4257 15.5 1974

 149 audi Update 29 4 98 83 2219 16.5 1974

150 volkswagen Updated 26 4 79 67 1963 15.5 1974

151 opel Updated 26 4 97 78 2300 14.5 1974

152 toyota Updated 31 4 76 52 1649 16.5 1974

153 datsun Updated 32 4 83 61 2003 19 1974

154 dodge Updated 28 4 90 75 2125 14.5 1974

155 fiat Updated 24 4 90 75 2108 15.5 1974

156 fiat Updated 26 4 116 75 2246 14 1974

157 honda Updated 24 4 120 97 2489 15 1974

158 subaru Updated 26 4 108 93 2391 15.5 1974

159 fiat Updated 31 4 79 67 2000 16 1974

27 rows selected.

SQL>

SQL> rem DELETE CHECK

SQL>

SQL> DELETE FROM Car_List WHERE MAKE='Updated';

DELETE FROM Car_List WHERE MAKE='Updated'

27 rows deleted

SQL>

SQL> ROLLBACK TO S1;

Rollback complete.

SQL>

SQL> rem META DATA CHECK

SQL>

SQL> SELECT *

2 FROM user_updatable_columns

3 WHERE table_name='CAR_LIST';

OWNER TABLE_NAME COLUMN_NAME UPD INS DEL

------------------------------ --- --- ---------- ----------------------- ------------------------------------

SYSTEM CAR_LIST ID YES YES YES

SYSTEM CAR_LIST MODEL YES YES YES

SYSTEM CAR_LIST DESCRIPTION YES YES YES

SYSTEM CAR_LIST MPG YES YES YES

SYSTEM CAR_LIST CYLINDERS YES YES YES

SYSTEM CAR_LIST EDISPL YES YES YES

SYSTEM CAR_LIST HORSEPOWER YES YES YES

SYSTEM CAR_LIST WEIGHT YES YES YES

SYSTEM CAR_LIST ACCELERATE YES YES YES

SYSTEM CAR_LIST YEAR YES YES YES

10 rows selected.

SQL>

SQL> Rem 3. Create a view named European_Makers that will display the maker name, full name
and the country name of car makers from Europe.

SQL>

SQL> CREATE OR REPLACE VIEW European_Makers AS

2 SELECT MAKER,FULLNAME,COUNTRY FROM CAR_MAKERS CM, COUNTRIES C,CONTINENTS CO

3 WHERE CM.COUNTRY=C.COUNTRYID AND C.CONTINENT=CO.CONT_ID AND

CO.CONTINENT='europe';

View created.

SQL> SELECT *
 2 FROM European_Makers;

MAKER FULLNAME COUNTRY

-------------------- ------------------------------ ----------

opel Opel 2

daimler benz Daimler Benz 2

bmw BMW 2

volkswagen Volkswagen 2

renault Renault 3

peugeaut Peugeaut 3

citroen Citroen 3

fiat Fiat 5

volvo Volvo 6

saab Saab 6

triumph Triumph 7

11 rows selected.

SQL>

SQL> SAVEPOINT S1;

Savepoint created.

SQL>

SQL> rem INSERT CHECK

SQL>

SQL> INSERT INTO European_Makers VALUES('Sruthi','Sweden','800');

INSERT INTO European_Makers VALUES('Sruthi','Sweden','800')

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SYSTEM"."CAR_MAKERS"."ID")

SQL>

SQL> rem UPDATE CHECK

SQL>

SQL> UPDATE European_Makers SET

2 FULLNAME='Updated';

11 rows updated.

SQL>

SQL> SELECT CAR_MAKERS.MAKER,CAR_MAKERS.FULLNAME,COUNTRIES.COUNTRYNAME

2 FROM COUNTRIES INNER JOIN CAR_MAKERS ON

CAR_MAKERS.COUNTRY=COUNTRIES.COUNTRYID INNER JOIN CONTINENTS ON
COUNTRIES.CONTINENT=CONTINENTS.CONT_ID

3 WHERE CONTINENTS.CONTINENT='europe';

MAKER FULLNAME COUNTRYNAME

-------------------- ------------------------------ --------------------

opel Updated germany

daimler benz Updated germany

bmw Updated germany

volkswagen Updated germany

renault Updated france

peugeaut Updated france

citroen Updated france

fiat Updated italy

volvo Updated sweden

saab Updated sweden

triumph Updated uk

11 rows selected.
SQL>

SQL> rem DELETE CHECK

SQL>

SQL> DELETE FROM European_Makers WHERE FULLNAME='Updated';

11 rows deleted.

SQL>

SQL> SELECT CAR_MAKERS.MAKER,CAR_MAKERS.FULLNAME,COUNTRIES.COUNTRYNAME

2 FROM COUNTRIES INNER JOIN CAR_MAKERS ON

CAR_MAKERS.COUNTRY=COUNTRIES.COUNTRYID INNER JOIN CONTINENTS ON
COUNTRIES.CONTINENT=CONTINENTS.CONT_ID

3 WHERE CONTINENTS.CONTINENT='europe';

no rows selected

SQL>

SQL> ROLLBACK TO S1;

Rollback complete.

SQL>

SQL> rem META DATA CHECK

SQL>

SQL>

SQL> SELECT *

2 FROM user_updatable_columns

3 WHERE table_name='EUROPEAN_MAKERS';

OWNER TABLE_NAME

------------------------------ ------------------------------
COLUMN_NAME UPD INS DEL

------------------------------ --- --- ---

SYSTEM EUROPEAN_MAKERS

FULLNAME YES YES YES

SYSTEM EUROPEAN_MAKERS

COUNTRY YES YES YES

SYSTEM EUROPEAN_MAKERS

MAKER YES YES YES

SQL>

SQL>

SQL> Rem 4. Create a view named Cars_Count which displays total number of cars manufactured
by each country.

SQL>

SQL> CREATE OR REPLACE VIEW Cars_Count AS

2 SELECT COUNTRYNAME,count(MAKER) AS Count FROM CAR_MAKERS CM,COUNTRIES C

3 WHERE C.COUNTRYID=CM.COUNTRY

4 GROUP BY COUNTRYNAME;

View created.

SQL> SELECT *

2 FROM Cars_Count;

COUNTRYNAME COUNT

-------------------- ----------

sweden 2

korea 2
japan 5

uk 1

germany 4

italy 1

usa 4

france 3

8 rows selected.

SQL>

SQL> CREATE OR REPLACE VIEW Cars_Count AS

2 SELECT COUNTRYNAME,count(MAKER) AS Count FROM CAR_MAKERS CM INNER JOIN

COUNTRIES C

3 ON C.COUNTRYID=CM.COUNTRY

4 GROUP BY COUNTRYNAME;

View created.

SQL> SELECT *

2 FROM Cars_Count;

COUNTRYNAME COUNT

-------------------- ----------

sweden 2

korea 2

japan 5

uk 1

germany 4

italy 1

usa 4

france 3
8 rows selected.

SQL>

SQL> SAVEPOINT S1;

Savepoint created.

SQL>

SQL> rem INSERT CHECK

SQL>

SQL> INSERT INTO Cars_Count VALUES('India','100');

INSERT INTO Cars_Count VALUES('India','100')

ERROR at line 1:

ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL>

SQL> rem UPDATE CHECK

SQL>

SQL> UPDATE Cars_Count SET

2 COUNTRYNAME='India';

UPDATE Cars_Count SET

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL>

SQL>
SQL> rem DELETE CHECK

SQL>

SQL> DELETE FROM Cars_Count WHERE COUNTRYNAME='sweden';

DELETE FROM Cars_Count WHERE COUNTRYNAME='sweden'

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL>

SQL> ROLLBACK TO S1;

Rollback complete.

SQL>

SQL> rem META DATA CHECK

SQL>

SQL> SELECT *

2 FROM user_updatable_columns

3 WHERE table_name='CARS_COUNT';

OWNER TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME UPD INS DEL

------------------------------ --- --- ---

SYSTEM CARS_COUNT

COUNTRYNAME NO NO NO

SYSTEM CARS_COUNT

COUNT NO NO NO
SQL>

SQL>

SQL> Rem 5. Create a view Car_List_VU from Car_List that shows only the car id, model, description
and mileage of cars.

SQL>

SQL> CREATE OR REPLACE VIEW Car_List_VU AS

2 SELECT ID,MODEL,DESCRIPTION,MPG FROM Car_List;

View created.

SQL> SELECT *

2 FROM Car_List_VU;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

133 plymouth plymouth duster

20

134 ford ford maverick

21

135 amc amc hornet

19

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG
----------

136 chevrolet chevrolet nova

15

137 datsun datsun b210

31

138 ford ford pinto

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

139 toyota toyota corolla 1200

32

140 chevrolet chevrolet vega

25

141 chevrolet chevrolet chevelle malibu classic

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

142 amc amc matador

16
 143 plymouth plymouth satellite sebring

18

144 ford ford gran torino

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

145 buick buick century luxus (sw)

13

146 dodge dodge coronet custom (sw)

14

147 ford ford gran torino (sw)

14

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

148 amc amc matador (sw)

14

149 audi audi fox

29
 150 volkswagen volkswagen dasher

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

151 opel opel manta

26

152 toyota toyota corona

31

153 datsun datsun 710

32

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

154 dodge dodge colt

28

155 fiat fiat 128

24

156 fiat fiat 124 tc

26
 ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

157 honda honda civic

24

158 subaru subaru

26

159 fiat fiat x1.9

31

27 rows selected.

SQL>

SQL> SAVEPOINT S1;

Savepoint created.

SQL>

SQL> rem INSERT CHECK

SQL>

SQL> INSERT INTO Car_List_VU VALUES('100','Sruthi','New','100');

INSERT INTO Car_List_VU VALUES('100','Sruthi','New','100')

ERROR at line 1:

ORA-01776: cannot modify more than one base table through a join view
SQL>

SQL> SELECT CAR_NAMES.*,CAR_DETAILS.MPG

2 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

3 WHERE YEAR=1974;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

133 plymouth plymouth duster

20

134 ford ford maverick

21

135 amc amc hornet

19

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

136 chevrolet chevrolet nova

15

137 datsun datsun b210

31
 138 ford ford pinto

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

139 toyota toyota corolla 1200

32

140 chevrolet chevrolet vega

25

141 chevrolet chevrolet chevelle malibu classic

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

142 amc amc matador

16

143 plymouth plymouth satellite sebring

18

144 ford ford gran torino

16
 ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

145 buick buick century luxus (sw)

13

146 dodge dodge coronet custom (sw)

14

147 ford ford gran torino (sw)

14

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

148 amc amc matador (sw)

14

149 audi audi fox

29

150 volkswagen volkswagen dasher

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

 MPG

----------

151 opel opel manta

26

152 toyota toyota corona

31

153 datsun datsun 710

32

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

154 dodge dodge colt

28

155 fiat fiat 128

24

156 fiat fiat 124 tc

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

157 honda honda civic

 24

158 subaru subaru

26

159 fiat fiat x1.9

31

27 rows selected.

SQL>

SQL> rem UPDATE CHECK

SQL>

SQL> UPDATE Car_List_VU SET

2 DESCRIPTION='India';

27 rows updated.

SQL>

SQL> SELECT CAR_NAMES.*,CAR_DETAILS.MPG

2 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

3 WHERE YEAR=1974;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

133 plymouth India

20
 134 ford India

21

135 amc India

19

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

136 chevrolet India

15

137 datsun India

31

138 ford India

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

139 toyota India

32

140 chevrolet India

25
 141 chevrolet India

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

142 amc India

16

143 plymouth India

18

144 ford India

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

145 buick India

13

146 dodge India

14

147 ford India

14
 ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

148 amc India

14

149 audi India

29

150 volkswagen India

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

151 opel India

26

152 toyota India

31

153 datsun India

32

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

 MPG

----------

154 dodge India

28

155 fiat India

24

156 fiat India

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

157 honda India

24

158 subaru India

26

159 fiat India

31

27 rows selected.

SQL>

SQL>

SQL> rem DELETE CHECK

SQL>

SQL> DELETE FROM Car_List_VU WHERE DESCRIPTION='India';

27 rows deleted.

SQL>

SQL> SELECT CAR_NAMES.*,CAR_DETAILS.MPG

2 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

3 WHERE YEAR=1974;

no rows selected

SQL>

SQL> ROLLBACK TO S1;

Rollback complete.

SQL>

SQL> rem META DATA CHECK

SQL>

SQL> SELECT *

2 FROM user_updatable_columns

3 WHERE table_name='CAR_LIST_VU';

OWNER TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME UPD INS DEL

------------------------------ --- --- ---

SYSTEM CAR_LIST_VU

ID YES YES YES

SYSTEM CAR_LIST_VU

MODEL YES YES YES

SYSTEM CAR_LIST_VU

DESCRIPTION YES YES YES

OWNER TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME UPD INS DEL

------------------------------ --- --- ---

SYSTEM CAR_LIST_VU

MPG YES YES YES

SQL>

SQL> Rem 6. Create a sequence named Maker_No_Seq which generates the Maker number starting
from 24, increment by 1, to maximum of 99.

SQL> Include options of cycle, cache and order. Use this sequence to populate the rows of
car_makers table.

SP2-0734: unknown command beginning "Include op..." - rest of line ignored.

SQL>

SQL> DROP SEQUENCE Maker_No_Seq;

DROP SEQUENCE Maker_No_Seq

ERROR at line 1:

ORA-02289: sequence does not exist

SQL> CREATE Sequence Maker_No_Seq

2 START WITH 24

3 INCREMENT BY 1
 4 MAXVALUE 99

5 CYCLE;

Sequence created.

SQL> CACHE 50;

SP2-0042: unknown command "CACHE 50" - rest of line ignored.

SQL> ORDER;

SP2-0042: unknown command "ORDER" - rest of line ignored.

SQL>

SQL> SAVEPOINT S1;

Savepoint created.

SQL>

SQL> INSERT INTO CAR_MAKERS VALUES(Maker_No_Seq.NEXTVAL,'amc','American Motor

Company',1);

1 row created.

SQL> INSERT INTO CAR_MAKERS VALUES(Maker_No_Seq.NEXTVAL,'amc','American Motor

Company',1);

1 row created.

SQL> INSERT INTO CAR_MAKERS VALUES(Maker_No_Seq.NEXTVAL,'amc','American Motor

Company',1);

1 row created.

SQL> INSERT INTO CAR_MAKERS VALUES(Maker_No_Seq.NEXTVAL,'amc','American Motor

Company',1);
1 row created.

SQL>

SQL> SELECT * FROM CAR_MAKERS

2 WHERE ID IN('24','25','26','27');

ID MAKER FULLNAME COUNTRY

---------- -------------------- ------------------------------ ----------

24 amc American Motor Company 1

25 amc American Motor Company 1

26 amc American Motor Company 1

27 amc American Motor Company 1

SQL>

SQL> ROLLBACK TO S1;

Rollback complete.

SQL>

SQL> Rem 7. Create a synonym named Car_Op_Data for the CAR_DETAILS relation. Perform the DML
operations on it.

SQL>

SQL> DROP SYNONYM Car_Op_Data;

DROP SYNONYM Car_Op_Data

ERROR at line 1:

ORA-01434: private synonym to be dropped does not exist

SQL>
SQL> CREATE OR REPLACE SYNONYM Car_Op_Data FOR CAR_DETAILS;

Synonym created.

SQL>

SQL> SAVEPOINT S1;

Savepoint created.

SQL>

SQL> rem INSERT CHECK

SQL>

SQL> INSERT INTO Car_Op_Data VALUES(410,18,8,307,130,3504,12,1970);

INSERT INTO Car_Op_Data VALUES(410,18,8,307,130,3504,12,1970)

ERROR at line 1:

ORA-02291: integrity constraint (SYSTEM.FKEY3) violated - parent key not found

SQL>

SQL> SELECT CAR_NAMES.*,CAR_DETAILS.MPG

2 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

3 WHERE YEAR=1974;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

133 plymouth plymouth duster

20
 134 ford ford maverick

21

135 amc amc hornet

19

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

136 chevrolet chevrolet nova

15

137 datsun datsun b210

31

138 ford ford pinto

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

139 toyota toyota corolla 1200

32

140 chevrolet chevrolet vega

25
 141 chevrolet chevrolet chevelle malibu classic

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

142 amc amc matador

16

143 plymouth plymouth satellite sebring

18

144 ford ford gran torino

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

145 buick buick century luxus (sw)

13

146 dodge dodge coronet custom (sw)

14

147 ford ford gran torino (sw)

14
 ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

148 amc amc matador (sw)

14

149 audi audi fox

29

150 volkswagen volkswagen dasher

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

151 opel opel manta

26

152 toyota toyota corona

31

153 datsun datsun 710

32

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

 MPG

----------

154 dodge dodge colt

28

155 fiat fiat 128

24

156 fiat fiat 124 tc

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

157 honda honda civic

24

158 subaru subaru

26

159 fiat fiat x1.9

31

27 rows selected.

SQL>

SQL> rem UPDATE CHECK

SQL>
SQL> UPDATE Car_Op_Data SET

2 DESCRIPTION='India';

DESCRIPTION='India'

ERROR at line 2:

ORA-00904: "DESCRIPTION": invalid identifier

SQL>

SQL> SELECT CAR_NAMES.*,CAR_DETAILS.MPG

2 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

3 WHERE YEAR=1974;

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

133 plymouth plymouth duster

20

134 ford ford maverick

21

135 amc amc hornet

19

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------
 136 chevrolet chevrolet nova

15

137 datsun datsun b210

31

138 ford ford pinto

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

139 toyota toyota corolla 1200

32

140 chevrolet chevrolet vega

25

141 chevrolet chevrolet chevelle malibu classic

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

142 amc amc matador

16
 143 plymouth plymouth satellite sebring

18

144 ford ford gran torino

16

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

145 buick buick century luxus (sw)

13

146 dodge dodge coronet custom (sw)

14

147 ford ford gran torino (sw)

14

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

148 amc amc matador (sw)

14

149 audi audi fox

29
 150 volkswagen volkswagen dasher

26

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

151 opel opel manta

26

152 toyota toyota corona

31

153 datsun datsun 710

32

ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

154 dodge dodge colt

28

155 fiat fiat 128

24

156 fiat fiat 124 tc

26
 ID MODEL DESCRIPTION

---------- -------------------- ----------------------------------------

MPG

----------

157 honda honda civic

24

158 subaru subaru

26

159 fiat fiat x1.9

31

27 rows selected.

SQL>

SQL>

SQL> rem DELETE CHECK

SQL>

SQL> DELETE FROM Car_Op_Data;

406 rows deleted.

SQL>

SQL> SELECT CAR_NAMES.*,CAR_DETAILS.MPG

2 FROM CAR_NAMES INNER JOIN CAR_DETAILS ON CAR_NAMES.ID=CAR_DETAILS.ID

3 WHERE YEAR=1974;

no rows selected
SQL>

SQL> ROLLBACK TO S1;

Rollback complete.

SQL>

SQL> Rem 8. Create an index named BModel on model of car_names relation.

SQL>

SQL> CREATE INDEX BModel

2 ON CAR_NAMES(ID,MODEL);

Index created.

SQL>

SQL>

SQL> SELECT index_name,table_name,column_name

2 FROM user_ind_columns

3 WHERE table_name='CAR_NAMES' AND index_name='BMODEL';

INDEX_NAME TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME

--------------------------------------------------------------------------------

BMODEL CAR_NAMES

ID

BMODEL CAR_NAMES

MODEL
SQL>

SQL> rem 9.Drop all the above created database objects.

SQL>

SQL> DROP VIEW Datsun_Cars;

View dropped.

SQL> DROP VIEW Car_List;

View dropped.

SQL> DROP VIEW European_Makers;

View dropped.

SQL> DROP VIEW Cars_Count;

View dropped.

SQL> DROP VIEW Car_List_VU;

View dropped.

SQL> DROP SEQUENCE Maker_No_Seq;

Sequence dropped.

SQL> DROP SYNONYM Car_Op_Data;

Synonym dropped.
SQL> DROP INDEX BModel;

Index dropped.

SQL>

SQL> Rem @C:\Users\Sruthi\Desktop\a4.txt;

SQL>

SQL>

SQL> Rem