Use VIEW in Postgresql

Question

Use VIEW in Postgresql

Navigation

#1 by (2 votes)
#2 by (2 votes)

2

Hello, Good morning!

I have encountered a small problem during the SQL class. I've learned how to make a View, but I do not know how to use them.

For example: when you did a search to know the names of teachers who have more than two reservations.

CREATE TABLE Profesor (
Profesor_ID integer Departement_ID integer NOT NULL
Nombre varchar(25) NOT NULL
Appelido varchar(25)
Grade varchar(25)
CONSTRAINT CK_Enseignant_Grade CHECK ( Grade IN('Vacataire,'Moniteur','ATER','MCF','PROF')),
Telephone varchar(10)     
DEFAULT NULL,
Fax varchar(10) DEFAULT NULL,
Email varchar(100) DEFAULT,
CONSTRAINT PK_Enseignement PRIMARY
KEY (Enseignant_ID), CONSTRAINT "FK_Enseignant_Departement_ID
    FOREIGN KEY (Departement_ID)
         REFERENCES Departement (Departement_ID)
            ON UPDATE RESTRICT ON DELETE RESTRICT );

He did:

CREATE VIEW Numero_Reservations AS
    SELECT COUNT(*) FROM Reservaciòn    
        GROUP BY Profesor_ID;

SELECT Nombre, Appelido FROM Profesor e, Numero_Reservaciòn
    WHERE (SELECT COUNT (Profesor_ID)
        FROM Profesor t, Numero_Reservaciòn
        WHERE (t.Profesor_ID = e.Profesor_ID))>=2;

There I do not know how to use them, because it gives me only the names of all the teachers ...

Thank you very much!

sql postgresql

asked by ThePassenger 11.04.2016 в 18:29

source

2 answers

2

The view would be like this:

 CREATE VIEW Numero_Reservations AS
      SELECT Nombre, Appelido FROM Profesor e, Numero_Reservaciòn
        WHERE (SELECT COUNT (Profesor_ID)
            FROM Profesor t, Numero_Reservaciòn
            WHERE (t.Profesor_ID = e.Profesor_ID))>=2
    Group by Nombre, Appelido
    having Count(*)>2;

and to consult

Select *from  Numero_Reservations

answered by 11.04.2016 в 18:50

Retrieve a hidden value in html Problems with Android Manifest

score 2 · Accepted Answer

It can be a solution, to create the view Numero_Reservations from the table Reservacion , the view would contain the Profesor_ID and reservations

CREATE VIEW Numero_Reservations AS
    SELECT Profesor_ID, reservations FROM Reservacion    
        GROUP BY Profesor_ID;

And the query to get teachers with more than 2 reservations would be:

SELECT  * 
        FROM Profesor p, Numero_Reservations r
        WHERE (p.Profesor_ID  = r.Profesor_ID ) and reservations>2

Here you would get all the records of the teacher table and your Numero_Reservations view, of course you would get the full name of the teachers with more than two reservations.