CREATE TABLE students(id serial, name text, quality1 text, quality2 text);
INSERT INTO students(id, name, quality1, quality2) VALUES
(1,'Bennie Hodkiewicz','brave','cunning'),
(2,'Octavia Huels','lazy','hopeless'),
(3,'Elta Okuneva','materialistic','generous'),
(4,'Mr. Destin Murazik','generous','hopeless'),
(5,'Shaniya Hane','happy','hyper'),
(6,'Abbie Ratke','happy','sad'),
(7,'Mrs. Michelle Lubowitz','generous','lazy'),
(8,'Kassandra Emmerich PhD','flighty','hufflepuff'),
(9,'Mallie Kautzer','shy','sad'),
(10,'Nichole Swaniawski','daring','materialistic'),
(11,'Modesta Ritchie','lazy','flighty'),
(12,'Alena Runte','daring','shy'),
(13,'Dr. Aurelia Wisozk','studious','hyper'),
(14,'Aubrey Larkin I','hufflepuff','cowardly'),
(15,'Miss Jarrell Zulauf','imaginative','sad'),
(16,'Craig Stroman DDS','evil','imaginative'),
(17,'Erica Weissnat','hyper','daring'),
(18,'Miss Clementina Kihn','sad','hyper'),
(19,'Christopher Hilpert','complacent','cowardly'),
(20,'Mrs. Aileen Stark','happy','hopeless'),
(21,'Nikolas Davis','daring','complacent'),
(22,'Claud OKeefe','courageous','materialistic'),
(23,'Claudie Fahey DVM','hufflepuff','imaginative'),
(24,'Berenice Casper','cowardly','courageous'),
(25,'Rolando Olson','imaginative','naive'),
(26,'Wilber Stark','hyper','materialistic'),
(27,'Geovanni Stamm','imaginative','cowardly'),
(28,'Lennie Hodkiewicz','hufflepuff','naive'),
(29,'Destinee Cummings','hufflepuff','flighty'),
(30,'Liliane Cruickshank','brave','lonely'),
(31,'Ms. Katherine Boyer','studious','courageous'),
(32,'Freddie Torp','brave','shy'),
(33,'Mr. Laury Bins','courageous','devious'),
(34,'Brooke Bechtelar','daring','materialistic'),
(35,'Dr. Shana Schuster','imaginative','evil'),
(36,'Oral Gleichner','lazy','intelligent'),
(37,'Sheridan Reynolds','hyper','complacent'),
(38,'Ms. Evelyn Keebler','generous','cunning'),
(39,'Junior Stark','complacent','flighty'),
(40,'Duane Fahey Sr.','materialistic','daring'),
(41,'Ophelia Hilll MD','daring','courageous'),
(42,'Hobart Streich','shy','generous'),
(43,'Stanley Mohr','daring','lonely'),
(44,'Deon Stokes','imaginative','complacent'),
(45,'Garth Schneider','hyper','courageous'),
(46,'Dejon Waters DDS','materialistic','lazy'),
(47,'Miss Elva Feeney','imaginative','naive'),
(48,'Regan Howe','naive','intelligent'),
(49,'Marc Homenick','hyper','complacent'),
(50,'Lenore Trantow','materialistic','evil'),
(51,'Emmet Cruickshank','flighty','shy'),
(52,'Ben Tremblay','imaginative','evil'),
(53,'Madie Bergnaum V','hyper','complacent'),
(54,'Eladio Wehner','brave','lazy'),
(55,'Omer Conroy III','daring','naive'),
(56,'Helen Lind','hufflepuff','courageous'),
(57,'Mrs. Johnathon Hettinger','studious','evil'),
(58,'Ms. Ardella Rosenbaum','naive','complacent'),
(59,'Shany Kohler','flighty','imaginative'),
(60,'Abbey Spinka','lazy','cunning'),
(61,'Haylee Powlowski','generous','happy'),
(62,'Rosario Schulist PhD','happy','courageous'),
(63,'Dovie Torphy','sad','happy'),
(64,'Otis Boyer','happy','sad'),
(65,'Johann Brekke','complacent','materialistic'),
(66,'Ernest Willms','materialistic','hufflepuff'),
(67,'Terrell Oberbrunner','complacent','happy'),
(68,'Ottis Romaguera','evil','devious'),
(69,'Alysha Volkman','lazy','hufflepuff'),
(70,'Leonardo Aufderhar','cowardly','generous'),
(71,'Carolyne Leffler II','evil','complacent'),
(72,'Mrs. Eve Carter','hyper','sad'),
(73,'Loren Steuber','cowardly','sad'),
(74,'Arianna Wiegand','courageous','daring'),
(75,'Miss Sid Little','studious','happy'),
(76,'Hermina Rowe','generous','happy'),
(77,'Maudie Keebler','sad','devious'),
(78,'Julia Ferry','lonely','sad'),
(79,'Kristofer Bartoletti','studious','naive'),
(80,'Amalia Zboncak','happy','evil'),
(81,'Jo Bernier','evil','daring'),
(82,'Zola Ernser DDS','complacent','materialistic'),
(83,'Santina Larson','naive','complacent'),
(84,'Oda Pfeffer','studious','hyper'),
(85,'Sidney Koch','daring','shy'),
(86,'Mrs. Adeline Ryan','complacent','naive'),
(87,'Haylee Kautzer','evil','materialistic'),
(88,'Emanuel Bins','generous','flighty'),
(89,'Gerhard Ziemann','shy','hyper'),
(90,'Saul Rolfson','complacent','courageous'),
(91,'Billy Haag','happy','devious'),
(92,'Evie Beier PhD','evil','lazy'),
(93,'Gudrun Mitchell','devious','naive'),
(94,'Pierce Batz V','lonely','sad'),
(95,'Walton Shanahan','devious','cowardly'),
(96,'Miss Patience Gutkowski','happy','materialistic'),
(97,'Rozella Johnson','evil','cowardly'),
(98,'Dr. Rod Conn','brave','courageous'),
(99,'Trevor Kilback','cowardly','cowardly'),
(100,'Raheem Grady','complacent','hyper');
-- 1
Select name, quality1, quality2,
Case
When quality1 = 'evil' and quality2 = 'cunning' Then 'Slytherin'
When quality1 = 'brave' and not quality2 = 'evil' Then 'Gryffindor'
When quality1 = 'studious' or quality2 = 'intelligent' Then 'Ravenclaw'
When quality1 = 'hufflepuff' or quality2 = 'hufflepuff' Then 'Hufflepuff'
else 'no suitable'
End faculty
From students;
--2
CREATE TABLE hogwarts_students(name text, quality1 text, quality2 text, faculty text, total_points int default 0);
INSERT INTO hogwarts_students(
WITH tmp AS(
Select name, quality1, quality2,
Case
When quality1 = 'evil' and quality2 = 'cunning' Then 'Slytherin'
When quality1 = 'brave' and not quality2 = 'evil' Then 'Gryffindor'
When quality1 = 'studious' or quality2 = 'intelligent' Then 'Ravenclaw'
When quality1 = 'hufflepuff' or quality2 = 'hufflepuff' Then 'Hufflepuff'
else 'no suitable'
End faculty
From students
)
Select name, quality1, quality2, faculty
From tmp
Where not (faculty = 'no suitable')
);
--3
CREATE VIEW Gryffindor AS
SELECT name, quality1, quality2, total_points
FROM hogwarts_students
WHERE faculty = 'Gryffindor';
--4
CREATE MATERIALIZED VIEW sum_points_students AS
SELECT faculty, Sum(total_points)
FROM hogwarts_students
group by faculty;
--5
update ravenclaw
set total_points = 2
where name = 'Dr. Rod Conn' or name = 'Eladio Wehner';
SELECT * FROM ravenclaw
--6
Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.
PostgreSQL is a open source relational database system and is also knows as Postgres.
CREATE command is used to create a table, schema or an index.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
ALTER command is used to add, modify or delete columns or constraints from the database table.
ALTER TABLE Table_name ADD column_name datatype;
TRUNCATE command is used to delete the data present in the table but this will not delete the table.
TRUNCATE table table_name;
DROP command is used to delete the table along with its data.
DROP TABLE table_name;
RENAME command is used to rename the table name.
ALTER TABLE table_name1 RENAME to new_table_name1;
INSERT Statement is used to insert new records into the database table.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Select statement is used to select data from database tables.
SELECT column1, column2, ...
FROM table_name;
UPDATE statement is used to modify the existing values of records present in the database table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE statement is used to delete the existing records present in the database table.
DELETE FROM table_name where condition;