I Need To Create A View That Pre-joins The Three Tables, Including All Of The Records From Student And Course Tables ( Shown Below)

-- I am trying to create a view for the tables shown below but my attempt is not successfull. I am using Oracle SQL Developer!! Where is the mistake here CREATE VIEW student_view

Solution 1:

You should create the underlying tables before composing them into a view.

Solution 2:

Please follow below sequence,

CREATE TABLE Student(
   StudentID INT PRIMARY KEY NOT NULL,
   Name CHAR(50),
   Address CHAR(50),
   GradYear INT
);

-- create table Grade
CREATE TABLE Grade(
    CName CHAR(50) NOT NULL,
    StudentID INT NOT NULL,
    CGrade CHAR(2),
    PRIMARY KEY(CName, StudentID)
);

-- create table Course
CREATE TABLE Course(
   CName CHAR(50) PRIMARY KEY NOT NULL,
   Department CHAR(50),
   Credits INT
);

CREATE VIEW student_view AS 
SELECT Student.StudentID , Student.Name, Student.Address, Student.GradYear, 
Course.CName, Course.CGrade, Grade.Department, Grade.Credits FROM Student 
LEFT OUTER JOIN Grade 
on (Student.StudentID = Grade.StudentID)
LEFT OUTER JOIN Course
on (Grade.CName = Course.CName);

Solution 3:

Corrected version:

• Defined tables in the right order
• Changed CHAR to VARCHAR2
• Added foreign key constraints (inheriting data types)
• Removed table aliases not allowed by NATURAL JOIN syntax
• Removed redundant brackets from view.

Tables:

create table student
( studentid   integer primary key not null
, name        varchar2(50) not null
, address     varchar2(50)
, gradyear    integer );

create table course
( cname       varchar2(50) primary key not null
, department  varchar2(50)
, credits     integer );

create table grade
( cname       references course not null
, studentid   references student not null
, cgrade      varchar2(2) not null
, primary key(cname, studentid) );

View:

create or replace view student_view as 
select studentid, name, address, gradyear
     , cname, department, credits
     , cgrade
from   student
       natural left outer join grade
       natural left outer join course
union all
select studentid, name, address, gradyear
     , cname, department, credits
     , cgrade
from   course
       natural left outer join grade
       natural left outer join student
where  studentid is null;

And just to add, NATURAL JOIN is never a good idea in real code.