CODEWITHSUNDEEP
sqloracle-databaseview
YouNeverKnow
YouNeverKnow

Reputation: 19

create 2+ view in sql

I try to solve this problem https://www.hackerrank.com/challenges/placements/problem the following code jumps with error

CREATE VIEW T AS (
SELECT STUDENTS.ID ,STUDENTS.NAME,PACKAGES.SALARY,FRIENDS.FRIEND_ID
FROM STUDENTS JOIN FRIENDS ON STUDENTS.ID=FRIENDS.ID
    JOIN PACKAGES ON STUDENTS.ID=PACKAGES.ID
);
CREATE VIEW T2 AS (
SELECT T.NAME
FROM  T JOIN PACKAGES ON T.FRIEND_ID=PACKAGES.ID
WHERE T.SALARY<PACKAGES.SALARY
ORDER BY PACKAGES.SALARY;
);
SELECT *
FROM T2;

But this

CREATE VIEW T AS (
SELECT STUDENTS.ID ,STUDENTS.NAME,PACKAGES.SALARY,FRIENDS.FRIEND_ID
FROM STUDENTS JOIN FRIENDS ON STUDENTS.ID=FRIENDS.ID
    JOIN PACKAGES ON STUDENTS.ID=PACKAGES.ID
);

SELECT T.NAME
FROM  T JOIN PACKAGES ON T.FRIEND_ID=PACKAGES.ID
WHERE T.SALARY<PACKAGES.SALARY
ORDER BY PACKAGES.SALARY;

works. why is that? Is that due to I create more than 1 view or something else?

Upvotes: 1

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Try dropping the parentheses:

CREATE VIEW T AS 
    SELECT S.ID, S.NAME, P.SALARY, F.FRIEND_ID
    FROM STUDENTS S JOIN
        FRIENDS F
        ON S.ID = F.ID JOIN
        PACKAGES P
        ON S.ID = P.ID;

CREATE VIEW T2 AS
    SELECT T.NAME
    FROM T JOIN 
         PACKAGES P
         ON T.FRIEND_ID = P.ID
    WHERE T.SALARY < P.SALARY
    ORDER BY P.SALARY;

You will note that I introduced table aliases so the queries are easier to write and to read.

The parentheses are not part of the CREATE VIEW syntax, so the query is interpreted as a subquery. I think that throws off the interpretation of the ORDER BY.

Upvotes: 1

Related Questions