SQL in Oracle. This is a two part question with a little backgound. This is what
ID: 3585671 • Letter: S
Question
SQL in Oracle.
This is a two part question with a little backgound.
This is what I did for 5-2 (which is used for the question I need help with).
SQL> create table Stutab (
2 "stno" number (38),
3 "sname" varchar2(20),
4 "major" varchar(4),
5 "class" number (38),
6 "bdate" date
7 );
Table created.
SQL> INSERT INTO Stutab
2 SELECT *
3 FROM stutable;
48 rows created.
This is what I did for 5-2c (which is used for the question I need help with).
SQL> create view vstu AS
2 select sname, major
3 from Stutabl
4 where major = 'COSC';
View created.
This is the Student Table we are using for all our assignments which has been pre-loaded and saved into our Oracle account:
stdnt
stno NOT NULL NUMBER (3)
PRIMARY KEY NOT NULL
sname VARCHAR2(20)
major CHAR(4)
class NUMBER(1)
bdate DATE
grdrpt
student_number NOT NULL NUMBER(3)
section_id NOT NULL NUMBER(6)
grade CHAR(1)
PRIMARY KEY (student_number, section_id)
sctn
section_id NOT NULL NUMBER(6)
PRIMARY KEY NOT NULL
course_num CHAR(8)
semester VARCHAR2(6)
year CHAR(2)
instructor CHAR(10)
bldg. NUMBER(3)
room NUMBER(3)
dptmaj
dcode NOT NULL CHAR(4)
PRIMARY KEY NOT NULL
dname CHAR(20)
crs
course_name CHAR(20)
course_number NOT NULL CHAR(8)
PRIMARY KEY NOT NULL
credit_hours NUMBER(2)
offering_dept CHAR(4)
rm
bldg NOT NULL NUMBER(3)
room NOT NULL NUMBER(3)
capacity NUMBER(4)
ohead CHAR(1)
PRIMARY KEY (bldg., room)
preq
course_number CHAR(8)
prereq CHAR(8)
PRIMARY KEY (course_number,prereq)
This is the question I need help with.
5-3
Part 1). Repeat the CREATE VIEW part of the excercise in 5-2c with a column name in the cREATE OR REPLACE VIEW statement like this: CREATE OR REPLACE VIEW vs (Na, Ma) SELECT...using the Stutab table you creted in 5-2. Display the view. What is the effect of the 9Na, Ma)?
Part 2). Repeat the above (Part 1), but this time use column aliases in the view defination. Are there any differences noted between explicity defining the view columns in the heading part of the statement versus using a column in alias?
Explanation / Answer
Hi,
Ans 1-
CREATE VIEW VS (NA, MA) AS
select sname, major from Stutabl where major = 'COSC';
This creates a view in the memory with column names NA and MA.
To view the content of view, we can just write select statement-
select * from VS;
Ans 2-
CREATE VIEW VS1 (NA, MA) AS
select sname as STUDENT_NAME, major as STUDENT_MAJOR from Stutabl where major = 'COSC';
The view definition has the column alias instead og column names of table but the actual structure of the view will hold the same columns NA and MA.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.