How to join Views with aggregate functions?-CodePudding

My problem:

In #4, I'm having trouble joining two Views because the other has an aggregate function. Same with #5

Question:

Create a view name it as studentDetails, that would should show the student name, enrollment date, total price per unit and subject description of students who are enrolled on the subject Science or History.
Create a view, name it as BiggestPrice, that will show the subject id and highest total price per unit of all the subjects. The view should show only the highest total price per unit that are greater than 1000.

--4.)   Create a view name it as studentDetails, that would should show the student name, 
--      enrollment date  the total price per unit and subject description of students who are 
--      enrolled on the subject Science or History.

CREATE VIEW StudentDetails AS 
SELECT StudName, EnrollmentDate



--5.)   Create a view, name it as BiggestPrice, that will show the subject id and highest total 
--      price per unit of all the subjects. The view should show only the highest total price per unit
--      that are greater than 1000.

CREATE VIEW BiggestPrice AS
SELECT SubjId, SUM(Max(Priceperunit)) FROM Student, Subject
GROUP BY Priceperunit

Here is my table:

CREATE TABLE Student(
    StudentId char(5) not null,
    StudName varchar2(50) not null,
    Age NUMBER(3,0),
    CONSTRAINT Student_StudentId PRIMARY KEY (StudentId)
);

CREATE table Enrollment(
    EnrollmentId    varchar2(10) not null, 
    EnrollmentDate  date not null,
    StudentId    char(5) not null,
    SubjId    Number(5) not null,
    constraint Enrollment_EnrollmentId primary key (EnrollmentId),
    constraint Enrollment_StudentId_FK foreign key (StudentId) references Student(StudentId),
    constraint Enrollment_SubjId_Fk foreign key (SubjId) references Subject(SubjId)
);

Create table Subject(
    SubjId number(5,0) not null,
    SubjDescription varchar2(200) not null,
    Units   number(3,0) not null,
    Priceperunit   number(9,0) not null,
    Constraint Subject_SubjId_PK primary key (SubjId)
);

CodePudding user response：

Since this appears to be a homework question.

You need to use JOINs. Your current query:

CREATE VIEW StudentDetails AS 
SELECT StudName, EnrollmentDate

Does not have a FROM clause and the query you have for question 5 uses the legacy comma join syntax with no WHERE filter; this is the same as a CROSS JOIN and will connect every student to every subject and is not what you want.

Don't use the legacy comma join syntax and use ANSI joins and explicitly state the join condition.

SELECT <expression list>
FROM   student s
       INNER JOIN enrollment e ON ...
       INNER JOIN subject j ON ...

Then you can fill in the ... based on the relationships between the tables (typically the primary key of one table = the foreign key of another table).

Then for the <expression list> you need to include the columns asked for in the question: student name and enrolment date and subject name would just be those columns from the appropriate tables; and total price-per-unit (which I assume is actually total-price-per-subject) would be a calculation.

Then for the last part of question 4.

who are enrolled on the subject Science or History.

Add a WHERE filter to only include rows for those subjects.

For question 5, you do not need any JOINS as the question only asks about details in the SUBJECT table.

You need to add a WHERE filter to show "only the highest total price per unit that are greater than 1000". This is a simple multiplication and then you can filter by comparing if it is > 1000.

Then you need to limit the query to return only the row with the "highest total price per unit of all the subjects". From Oracle 12, this would be done with an ORDER BY clause in descending order of total price and then using FETCH FIRST ROW ONLY or FETCH FIRST ROW WITH TIES.

CodePudding user response：

Not sure if i get it fully, but i think its this :

Notes:

Always use Id's to filter records:

where su.SubjId in (1,2)

You can find max record using max() at subquery and join it with main query like this :

```
where su2.SubjId = su.SubjId
```

You cannot use alias as filter so you can filter it like:

( su.Units * su.Priceperunit ) > 1000

 CREATE VIEW StudentDetails AS 
select s.StudName,
       e.EnrollmentDate,
       su.SubjDescription,
       su.Units * su.Priceperunit TotalPrice
  from student s
 inner join Enrollment e
    on e.StudentId = s.StudentId
 inner join Subject su
    on su.SubjId = e.SubjId
    where su.SubjId   in (1,2)
    
 CREATE VIEW BiggestPrice AS  
 select su.SubjId, ( su.Units * su.Priceperunit ) TotalPrice 
 from Subject su
 where ( su.Units * su.Priceperunit ) = 
 (
select max(su2.Units * su2.Priceperunit)
from  Subject su2
where su2.SubjId = su.SubjId
 )
 and ( su.Units * su.Priceperunit ) > 1000