-- Create a view
CREATE VIEW CS_STUDENT
AS
SELECT *
FROM STUDENT
WHERE Major = 'CS';

-- Select from a view
SELECT Class, Count(*)
FROM CS_STUDENT
GROUP BY Class;

-- Update a view
CREATE OR REPLACE VIEW CS_STUDENT (Class, Num)
AS
SELECT Class, COUNT(*)
FROM STUDENT
WHERE Major = 'CS'
GROUP BY Class;

UPDATE CS_STUDENT
SET class= 4
WHERE sid = 999;

-- Check the insert
INSERT INTO CS_STUDENT (SID, Name, class)
VALUES (167, 'Mat Jones', 3, 'CS');

-- Insert and update
INSERT INTO CS_STUDENT
VALUES (128, 'Ping Chen', 2, 'CS');

UPDATE CS_STUDENT
SET Name = 'Shimin Chen'
WHERE SID = 128;

-- Materialized Views

CREATE MATERIALIZED VIEW
  Majors (major, mtotal) AS SELECT major, count(*)
  FROM Student
  GROUP BY Major;

INSERT INTO STUDENT VALUES (177, 'Constantinos Costa', 3, 'CS');

-- Update the materialized view
REFRESH MATERIALIZED VIEW Majors

-- Temporary tables

CREATE TEMP TABLE temp1 AS
SELECT Class, COUNT(*)
FROM STUDENT
WHERE Major = 'CS'
GROUP BY Class;

select * from temp1

