Views (IOSY3)

CREATE OR REPLACE VIEW Name_1 AS
SELECT *
FROM StaffVSHKL;



/* View for offices in Vitebsk */
CREATE OR REPLACE VIEW BranchesInVitebsk AS
SELECT bno, street, tel_no
FROM BranchVSHKL
WHERE city = 'Vitebsk';

SELECT * FROM BranchesInVitebsk;



/* View for rent objects with minimal price */
CREATE OR REPLACE VIEW MinimalPrice AS
SELECT pno, street, city, TYPE, rooms, rent
FROM PropertForRentVSHKL

SELECT
MIN(
    CASE
    WHEN PropertyForRentVSHKL.TYPE = 'h'
    THEN rent
    ELSE NULL
    END
    ) AS Cheap_house,
MIN(
    CASE
    WHEN PropertyForRentVSHKL.TYPE = 'f'
    THEN rent
    ELSE NULL
    END
    ) AS Cheap_flat
FROM PropertyForRentVSHKL;
/*
CHEAP_HOUSE CHEAP_FLAT
----------- ----------
        300        300
*/

SELECT pno, MIN(rent) AS Cheap_house
FROM PropertyForRentVSHKL
WHERE TYPE = 'h'
GROUP BY pno;

SELECT P.TYPE AS TYPE, MIN(P.rent) AS PRICE
FROM PropertyForRentVSHKL P
WHERE P.TYPE = 'h'
GROUP BY P.TYPE
UNION
SELECT P.TYPE AS TYPE, MIN(P.rent) AS PRICE
FROM PropertyForRentVSHKL P
WHERE P.TYPE = 'f'
GROUP BY P.TYPE;

SELECT pno, MIN(rent) AS Cheap_house
FROM PropertyForRentVSHKL
WHERE TYPE = 'h'
GROUP BY pno;

SELECT
MIN(
    CASE
    WHEN PropertyForRentVSHKL.TYPE = 'h'
    THEN rent
    ELSE NULL
    END
    ) AS Rent
FROM PropertyForRentVSHKL
GROUP BY pno;

SELECT street, city
MAX(
    CASE
    WHEN PropertyForRentVSHKL.TYPE = 'h'
    THEN rent
    ELSE NULL
    END
    ) AS Rent
FROM PropertyForRentVSHKL
UNION
SELECT street, city,
MAX(
    CASE
    WHEN PropertyForRentVSHKL.TYPE = 'f'
    THEN rent
    ELSE NULL
    END
    ) AS Rent
FROM PropertyForRentVSHKL;

SELECT pno, TYPE, rent
FROM PropertyForRentVSHKL
WHERE TYPE = 'f';

CREATE OR REPLACE VIEW MinimalPrice AS
SELECT *
FROM(
    SELECT *
    FROM PropertyForRentVSHKL
    WHERE (TYPE, rent) IN (
        SELECT  TYPE, MIN(rent)
        FROM PropertyForRentVSHKL
        WHERE TYPE = 'h'
        GROUP BY TYPE
        )
    UNION
    SELECT *
    FROM PropertyForRentVSHKL
    WHERE (TYPE, rent) IN (
        SELECT  TYPE, MIN(rent)
        FROM PropertyForRentVSHKL
        WHERE TYPE = 'f'
        GROUP BY TYPE
        )
    );

SELECT * FROM MinimalPrice;


/* View for number of views with comment */
CREATE OR REPLACE VIEW NumberOfViewsWithComment AS
SELECT COUNT(date1) AS Comments
FROM ViewingVSHKL
WHERE comment1 IS NOT NULL;

SELECT * FROM NumberOfViewsWithComment;

/*
  COMMENTS
----------
        18
*/

CREATE OR REPLACE VIEW NumberOfViewsWithoutComment AS
SELECT COUNT(date1) AS Comments
FROM ViewingVSHKL
WHERE comment1 IS NULL;

SELECT * FROM NumberOfViewsWithoutComment;

/*
  COMMENTS
----------
         7
*/


/* View for renters who want to get flat with 3 rooms */
CREATE OR REPLACE VIEW RentersWhoWant3RoomFlat AS

-- Renters who want flat and can spend over 500 money
CREATE OR REPLACE VIEW RentersWhoWantFlatOver500 AS
SELECT *
FROM RenterVSHKL
WHERE pref_type = 'f' AND max_rent > 500;

SELECT * FROM RentersWhoWantFlatOver500;

-- Renters who want to rent flat with 3 rooms
SELECT DISTINCT p.rooms, v.rno
FROM PropertyForRentVSHKL p, ViewingVSHKL v
WHERE p.pno = v.pno AND p.rooms = 3;

SELECT pno
FROM PropertyForRentVSHKL
WHERE rooms = 3;
/*     PNO
----------
      4332
      4346
      4360
      4388
      4395
      4437*/

SELECT DISTINCT v.rno
FROM ViewingVSHKL v, PropertyForRentVSHKL p
WHERE v.pno IN (
    SELECT pno
    FROM PropertyForRentVSHKL
    WHERE rooms = 3
    );
/*     RNO
----------
      5325
      5332
      5381
      5360
      5353
      5374*/

SELECT DISTINCT r.fname, r.lname, r.tel_no
FROM ViewingVSHKL v, RenterVSHKL r
WHERE v.pno IN (
    SELECT pno
    FROM PropertyForRentVSHKL
    WHERE rooms = 3
    ) AND v.rno = r.rno;
/*FNAME                  LNAME                    TEL_NO
------------------------ ------------------------ ------------
Farah                    Martinez                 375292544448
Rebecca                  Rogers                   375296601048
Ronald                   Brooks                   375296621408
Carol                    Richardson               375296621558
Eugene                   Miller                   375296651008
Shirley                  Morris                   375296012448*/

CREATE OR REPLACE VIEW RentersWhoWantFlatWith3Rooms AS
SELECT DISTINCT r.fname, r.lname, r.tel_no
FROM ViewingVSHKL v, RenterVSHKL r
WHERE v.pno IN (
    SELECT pno
    FROM PropertyForRentVSHKL
    WHERE rooms = 3
    ) AND v.rno = r.rno;

SELECT * FROM RentersWhoWantFlatWith3Rooms;


/* View for info about branch with max number of personnel */
CREATE OR REPLACE VIEW BranchWithMaxPersonnel AS

SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
FROM StaffVSHKL
GROUP BY bno
ORDER BY Personnel DESC;
/*
  STAFFBNO  PERSONNEL
---------- ----------
      1332          4
      1325          4
      1367          4
      1346          4
      1374          3
      1360          3
      1339          3
      1353          2
*/

SELECT *
FROM (
    SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
    FROM StaffVSHKL
    GROUP BY bno
    )
WHERE Personnel = (
    SELECT MAX(Personnel)
        FROM
        (
            SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
            FROM StaffVSHKL
            GROUP BY bno
        )
    );
/*
  STAFFBNO  PERSONNEL
---------- ----------
      1332          4
      1346          4
      1325          4
      1367          4
*/

SELECT StaffBNO
FROM (
    SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
    FROM StaffVSHKL
    GROUP BY bno
    )
WHERE Personnel = (
    SELECT MAX(Personnel)
        FROM
        (
            SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
            FROM StaffVSHKL
            GROUP BY bno
        )
    )
JOIN
BranchVSHKL
ON BranchVSHKL.bno = B.StaffBNO;

CREATE OR REPLACE VIEW BranchWithMaxPersonnel AS
SELECT A.*, B.bno, B.street, B.city, B.tel_no
FROM BranchVSHKL B, (
    SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
    FROM StaffVSHKL
    GROUP BY bno
    ) A
WHERE Personnel = (
    SELECT MAX(Personnel)
        FROM
        (
            SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
            FROM StaffVSHKL
            GROUP BY bno
        )
    ) AND StaffBNO = B.bno;

CREATE OR REPLACE VIEW BranchWithMaxPersonnel AS
SELECT BNO, city, street, tel_no
FROM(
    SELECT A.*, B.bno, B.street, B.city, B.tel_no
    FROM BranchVSHKL B, (
        SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
        FROM StaffVSHKL
        GROUP BY bno
        ) A
    WHERE Personnel = (
        SELECT MAX(Personnel)
            FROM
            (
                SELECT bno AS StaffBNO, COUNT(sno) AS Personnel
                FROM StaffVSHKL
                GROUP BY bno
            )
        ) AND StaffBNO = B.bno
    );
/*
BNO   CITY     STREET        TEL_NO
----- -------- ------------- ------------
1332  Minsk    Kabushkina    375296954454
1325  Minsk    Timiryazeva   375296545454
1346  Minsk    Pritytskogo   375296651754
1367  Grodno   Kirova        375296541004
*/


/* View for staff and objects they represent */
CREATE OR REPLACE VIEW StaffAndRent AS
SELECT S.sno, S.fname, S.lname, S.tel_no, S.bno,
       P.pno, P.street, P.city, P.TYPE, P.rooms, P.rent
FROM StaffVSHKL S, PropertyForRentVSHKL P
WHERE S.sno = P.sno;

SELECT * FROM StaffAndRent;


/* View for info about owners whose houses or flats
 * was viewed
 */
CREATE OR REPLACE VIEW OwnersViewed AS

SELECT O.ono, O.fname, O.lname, O.address, O.tel_no,
       P.pno, P.ono,
       V.pno
FROM OwnerVSHKL O, PropertyForRentVSHKL P, ViewingVSHKL V
WHERE O.ono = P.ono AND V.pno = P.pno;

SELECT DISTINCT fname, lname, address, tel_no
FROM(
    SELECT O.ono, O.fname, O.lname, O.address, O.tel_no,
           P.pno, P.ono,
           V.pno
    FROM OwnerVSHKL O, PropertyForRentVSHKL P, ViewingVSHKL V
    WHERE O.ono = P.ono AND V.pno = P.pno
    );



-- 5) redo
SELECT DISTINCT bno
FROM BranchVSHKL;
/*
       BNO
----------
      1325
      1332
      1339
      1346
      1353
      1360
      1367
      1374
*/

SELECT DISTINCT s.bno, s.sno
FROM StaffVSHKL s;
/*
       BNO        SNO
---------- ----------
      1325       2325
      1325       2339
      1325       2346
      1325       2353

      1332       2367
      1332       2374
      1332       2381
      1332       2388

      1339       2395
      1339       2402
      1339       2409

      1346       2416
      1346       2423
      1346       2430
      1346       2444

      1353       2451
      1353       2458

      1360       2465
      1360       2479
      1360       2486

      1367       2493
      1367       2500
      1367       2507
      1367       2514

      1374       2521
      1374       2528
      1374       2535
*/

SELECT s.bno, COUNT(s.bno) COUNT_BNO
FROM StaffVSHKL s
GROUP BY s.bno;
/*
       BNO    COUNT_BNO
---------- ------------
      1332            4
      1353            2
      1346            4
      1325            4
      1367            4
      1339            3
      1374            3
      1360            3
*/

SELECT BNO, MAX(count_bno)
FROM(
    SELECT s.bno, COUNT(s.bno) COUNT_BNO
    FROM StaffVSHKL s
    GROUP BY s.bno
    )
GROUP BY BNO;

SELECT
CASE
    WHEN COUNT_BNO =
    (
        SELECT MAX(COUNT_BNO)
        FROM(
            SELECT s.bno, COUNT(s.bno) COUNT_BNO
            FROM StaffVSHKL s
            GROUP BY s.bno
            )
    )
    THEN BNO
END BNO_
FROM(
    SELECT s.bno, COUNT(s.bno) COUNT_BNO
    FROM StaffVSHKL s
    GROUP BY s.bno
    );

-- SELECT b.city, b.street, b.tel_no
-- FROM BranchVSHKL b
-- HAVING MAX_BNO =
--  (
--      SELECT MAX(COUNT_BNO) MAX_BNO
--      FROM(
--          SELECT s.bno, COUNT(s.bno) COUNT_BNO
--          FROM StaffVSHKL s
--          GROUP BY s.bno
--          )
--  );

SELECT BNO
FROM(
    SELECT s.bno, COUNT(s.bno) COUNT_BNO
    FROM StaffVSHKL s
    GROUP BY s.bno
    )
WHERE COUNT_BNO =
    (
    SELECT MAX(COUNT_BNO)
    FROM(
        SELECT s.bno, COUNT(s.bno) COUNT_BNO
        FROM StaffVSHKL s
        GROUP BY s.bno
        )
    );
--        BNO
-- ----------
--       1332
--       1346
--       1325
--       1367

SELECT b.city, b.street, b.tel_no
FROM BranchVSHKL b
WHERE b.bno IN(
    SELECT BNO
    FROM(
        SELECT s.bno, COUNT(s.bno) COUNT_BNO
        FROM StaffVSHKL s
        GROUP BY s.bno
        )
    WHERE COUNT_BNO =
        (
        SELECT MAX(COUNT_BNO)
        FROM(
            SELECT s.bno, COUNT(s.bno) COUNT_BNO
            FROM StaffVSHKL s
            GROUP BY s.bno
            )
        )
    );






---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------
---------------------------------------------------------------------

-- 1) Информация об офисах в Витебске
CREATE OR REPLACE VIEW BranchesInVitebsk AS
SELECT bno, street, tel_no
FROM BranchVSHKL
WHERE city = 'Vitebsk';

SELECT * FROM BranchesInVitebsk;

/*  BNO    STREET           TEL_NO
    ------------------------------------
    1353   Leningradskaya   375296595644
    1360   Gertsena         375296511024  */



-- 2) Информация об объектах недвижимости с минимальной стоимостью
CREATE OR REPLACE VIEW MinimalPrice AS
    SELECT *
    FROM PropertyForRentVSHKL
    WHERE (TYPE, rent) IN (
        SELECT  TYPE, MIN(rent)
        FROM PropertyForRentVSHKL
        WHERE TYPE = 'h'
        GROUP BY TYPE
        )
    UNION
    SELECT *
    FROM PropertyForRentVSHKL
    WHERE (TYPE, rent) IN (
        SELECT  TYPE, MIN(rent)
        FROM PropertyForRentVSHKL
        WHERE TYPE = 'f'
        GROUP BY TYPE
        );

SELECT * FROM MinimalPrice;

/*  PNO  STREET CITY     T  ROOMS   RENT    ONO    SNO   BNO
    ---------------------------------------------------------
    4402 Pravdi Vitebsk  f  2       300     3381   2500  1360
    4416 Lenina Grodno   h  2       300     3395   2535  1367  */



-- 3) Информация о количестве сделанных осмотров с комментариями
CREATE OR REPLACE VIEW NumberOfViewsWithComment AS
SELECT COUNT(date1) AS NUMBER_OF_COMMENTS
FROM ViewingVSHKL
WHERE comment1 IS NOT NULL;

SELECT * FROM NumberOfViewsWithComment;

/*  COMMENTS
    --------
          18  */



-- 4) Информация об арендаторах желающих арендовать 3х комн. квартиру
CREATE OR REPLACE VIEW RentersWhoWantFlatWith3Rooms AS
SELECT DISTINCT r.fname RENTER_FIRSTNAME, r.lname RENTER_LASTNAME, r.tel_no RENTER_TEL_NO
FROM ViewingVSHKL v, RenterVSHKL r
WHERE v.pno IN (
    SELECT pno
    FROM PropertyForRentVSHKL
    WHERE rooms = 3
    ) AND v.rno = r.rno;

SELECT * FROM RentersWhoWantFlatWith3Rooms;

/*FNAME                  LNAME                    TEL_NO
------------------------ ------------------------ ------------
Farah                    Martinez                 375292544448
Rebecca                  Rogers                   375296601048
Ronald                   Brooks                   375296621408
Carol                    Richardson               375296621558
Eugene                   Miller                   375296651008
Shirley                  Morris                   375296012448*/



-- 5) Информация об отделениях с максимальным количеством сотрудников
-- Упростить
CREATE OR REPLACE VIEW BranchWithMaxPersonnel AS
SELECT b.city BRANCH_CITY, b.street BRANCH_STREET, b.tel_no BRANCH_TEL_NO
FROM BranchVSHKL b
WHERE b.bno IN(
    SELECT BNO
    FROM(
        SELECT s.bno, COUNT(s.bno) COUNT_BNO
        FROM StaffVSHKL s
        GROUP BY s.bno
        )
    WHERE COUNT_BNO =
        (
        SELECT MAX(COUNT_BNO)
        FROM(
            SELECT s.bno, COUNT(s.bno) COUNT_BNO
            FROM StaffVSHKL s
            GROUP BY s.bno
            )
        )
    );

SELECT * FROM BranchWithMaxPersonnel;

/*  BNO   CITY     STREET        TEL_NO
    ----- -------- ------------- ------------
    1332  Minsk    Kabushkina    375296954454
    1325  Minsk    Timiryazeva   375296545454
    1346  Minsk    Pritytskogo   375296651754
    1367  Grodno   Kirova        375296541004  */



-- 6) Информация о сотрудниках и объектах ими представляемыми
CREATE OR REPLACE VIEW StaffAndRent AS
SELECT S.bno, S.fname, S.lname, S.tel_no STAFF_TEL,
       P.street RENT_STREET, P.city RENT_CITY, P.TYPE, P.rooms, P.rent
FROM StaffVSHKL S, PropertyForRentVSHKL P
WHERE S.sno = P.sno;

SELECT * FROM StaffAndRent;



-- 7) Информация о владельцах, чьи дома осматривали арендаторы
-- Упростить [done]
CREATE OR REPLACE VIEW OwnersViewed AS
SELECT DISTINCT O.fname, O.lname, O.address, O.tel_no
FROM OwnerVSHKL O, PropertyForRentVSHKL P, ViewingVSHKL V
WHERE O.ono = P.ono AND V.pno = P.pno;

SELECT * FROM OwnersViewed;



-- Все запросы
-- 1)
SELECT * FROM BranchesInVitebsk;
-- 2)
SELECT * FROM MinimalPrice;
-- 3)
SELECT * FROM NumberOfViewsWithComment;
-- 4)
SELECT * FROM RentersWhoWantFlatWith3Rooms;
-- 5)
SELECT * FROM BranchWithMaxPersonnel;
-- 6)
SELECT * FROM StaffAndRent;
-- 7)
SELECT * FROM OwnersViewed;



-- Vita
-- MAX AVG 2 rooms branch
SELECT *
FROM PropertyForRentVSHKL p
WHERE p.rooms = 2;

SELECT AVG(RENT)
FROM(
    SELECT *
    FROM PropertyForRentVSHKL p
    WHERE p.rooms = 2
    )
WHERE p.bno IN(
    SELECT bno
    FROM(
            SELECT *
        FROM PropertyForRentVSHKL p
        WHERE p.rooms = 2
        )
);