 -- Database: q4

-- DROP DATABASE IF EXISTS q4;

--NAME : TANISHQ DINESH PATEL
--USC ID : 1687601586
--DATABASE USED : postgresql

--The comments table has sentiments and video table has keywords
--The sentiment score varies from 0-100
--I used inner join to join video and comments and get video_id, keywords_name, sentiments.
--Then after getting this table I grouped it by keywords_name
--And ordered it by average score in descending order


create table USER_T
(
	UNIQUE_ID integer primary key,
	NAME CHAR(100) NOT NULL,
	EMAIL VARCHAR(255) NOT NULL,
	AGE integer NOT NULL,
	ADDRESS VARCHAR(255)
);

create table CHANNEL
(
	CHANNEL_ID integer primary key,
	UNIQUE_ID integer NOT NULL,
	CHANNEL_NAME varchar(255) NOT NULL,
	SUBSCRIPTION_COUNT integer NOT NULL,
	PAID_SUBS integer not null,
	UNPAID_SUBS integer not null,
	CREATION_DATE VARCHAR(255) NOT NULL,
	CONSTRAINT fk_usert
		FOREIGN KEY(UNIQUE_ID)
			REFERENCES USER_T(UNIQUE_ID)
			ON DELETE SET NULL
);

create table VIDEO
(
	VIDEO_ID integer primary key,
	CHANNEL_ID integer,
	UNIQUE_ID integer,
	URL varchar(255) not null,
	TITLE varchar(255) not null,
	UPLOAD_DATE varchar(255) not null,
	UPLOAD_TIME varchar(255) not null,
	KEYWORDS_NAME char(100) not null,
	DESCRIPTION varchar(255),
	constraint fk_usert
		FOREIGN KEY(UNIQUE_ID)
			REFERENCES USER_T(UNIQUE_ID)
			ON DELETE SET NULL,
	constraint fk_channel
		foreign key(CHANNEL_ID)
			references CHANNEL(CHANNEL_ID)
			on delete set null
);

create table COMMENTS 
(
	COMMENT_ID integer primary key,
	VIDEO_ID integer not null,
	UNIQUE_ID integer not null,
	COMMENT_TEXT varchar(255) not null,
	SENTIMENTS integer not null,
	constraint fk_usert
		FOREIGN KEY(UNIQUE_ID)
			REFERENCES USER_T(UNIQUE_ID)
			ON DELETE SET NULL,
	constraint fk_video
		FOREIGN KEY(VIDEO_ID)
			REFERENCES VIDEO(VIDEO_ID)
			ON DELETE SET NULL
	
);

insert into USER_T values (001, 'Adam', '[email protected]', 23,null);
insert into USER_T values (002, 'Alex', '[email protected]', 32,null);
insert into USER_T values (003, 'Amar', '[email protected]', 13,null);
insert into USER_T values (004, 'Bablu', '[email protected]', 31,null);
insert into USER_T values (005, 'Shobith Rai', '[email protected]', 31,null);
insert into USER_T values (006, 'Mithul Nayak', '[email protected]', 32,null);
insert into USER_T values (007, 'Amit', '[email protected]', 20,null);
insert into USER_T values (008, 'Chotu', '[email protected]', 15,null);
insert into USER_T values (009, 'Aaruhi', '[email protected]', 23,null);
insert into USER_T values (010, 'Aishwarya', '[email protected]', 32,null);
insert into USER_T values (011, 'Tapsee', '[email protected]', 13,null);
insert into USER_T values (012, 'Babli', '[email protected]', 31,null);
insert into USER_T values (013, 'Akshay', '[email protected]', 31,null);
insert into USER_T values (014, 'Sallu', '[email protected]', 32,null);
insert into USER_T values (015, 'Bhai', '[email protected]', 20,null);
insert into USER_T values (016, 'Carmon', '[email protected]', 15,null);

insert into CHANNEL values (001, 001, 'Adams Channel', 10500,50,10450, '01.01.2023');
insert into CHANNEL values (002, 002, 'Alexs Channel', 105000,1000, 104000, '10.01.2019');
insert into CHANNEL values (003, 003, 'Amars Channel', 11500,1500,10000, '01.10.2003');
insert into CHANNEL values (004, 004, 'Bablus Channel', 1500,10,1490, '01.01.2023');
insert into CHANNEL values (005, 005, 'Tbone', 205000,5000,200000, '01.01.2023');
insert into CHANNEL values (006, 006, 'Binks69', 300500,500, 300000, '01.01.2023');
insert into CHANNEL values (007, 007, 'fa2', 100500,500, 100500, '01.01.2023');

insert into VIDEO values
(1, 001, 001, 'adamsvideo.com', 'Adams Video', '01.01.2002', '3:00', 'Gaming',null);
insert into VIDEO values
(2, 001, 001, 'adamsvideo2.com', 'Adams Video 2', '01.01.2002', '4:00', 'Gaming',null);
insert into VIDEO values
(3, 005, 005, 'tbonesvideo.com', 'Tbones Video', '01.01.2012', '4:00', 'Cooking',null);
insert into VIDEO values
(4, 006, 006, 'tbonesvideo2.com', 'Tbones Video 2', '11.01.2012', '5:00', 'Cooking',null);
insert into VIDEO values
(5, 007, 007, 'fa2video.com', 'Fa2 Video', '01.01.2002', '3:00', 'Educational',null);
insert into VIDEO values
(6, 007, 007, 'fa2video2.com', 'fa2 Video 2', '01.01.2002', '4:00', 'Educational',null);
insert into VIDEO values
(7, 007, 007, 'fa2video.com', 'fa2 Video 3', '01.01.2012', '4:00', 'Educational',null);
insert into VIDEO values
(8, 007, 007, 'fa2video2.com', 'fa2 Video 4', '11.01.2012', '5:00', 'Educational',null);

insert into COMMENTS values 
(1, 1, 006, 'Jod', 90);
insert into COMMENTS values 
(2, 1, 007, 'JodJod', 80);
insert into COMMENTS values 
(3, 1, 005, 'Jod', 10);
insert into COMMENTS values 
(4, 1, 006, 'JodJodJod', 15);
insert into COMMENTS values 
(5, 2, 010, 'Jod', 40);
insert into COMMENTS values 
(6, 2, 011, 'JodJod', 20);
insert into COMMENTS values 
(7, 2, 012, 'Jod', 60);
insert into COMMENTS values 
(8, 2, 013, 'JodJodJod', 75);

insert into COMMENTS values 
(9, 3, 006, 'Jod', 10);
insert into COMMENTS values 
(10, 3, 007, 'JodJod', 30);
insert into COMMENTS values 
(11, 3, 005, 'Jod', 5);
insert into COMMENTS values 
(12, 3, 006, 'JodJodJod', 15);
insert into COMMENTS values 
(13, 4, 010, 'Jod', 60);
insert into COMMENTS values 
(14, 4, 011, 'JodJod', 20);
insert into COMMENTS values 
(15, 4, 012, 'Jod', 10);
insert into COMMENTS values 
(16, 4, 013, 'JodJodJod', 75);

insert into COMMENTS values 
(17, 5, 006, 'Jod', 90);
insert into COMMENTS values 
(18, 5, 007, 'JodJod', 80);
insert into COMMENTS values 
(19, 6, 005, 'Jod', 10);
insert into COMMENTS values 
(20, 6, 006, 'JodJodJod', 100);
insert into COMMENTS values 
(21, 7, 010, 'Jod', 40);
insert into COMMENTS values 
(22, 7, 011, 'JodJod', 20);
insert into COMMENTS values 
(23, 8, 012, 'Jod', 60);
insert into COMMENTS values 
(24, 8, 013, 'JodJodJod', 15);


select t.keywords_name, round(avg(t.sentiments), 3) 
as average_sentiments from
(select v.VIDEO_ID, v.KEYWORDS_NAME, c.SENTIMENTS
from VIDEO v 
inner join COMMENTS c
on v.VIDEO_ID = c.VIDEO_ID) t
group by t.keywords_name
order by average_sentiments desc

created 2 years ago by Qianmian Gai

PostgreSQL online editor

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.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

Postgres is not only free and open-source but also it is highly extensible.
Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
ACID(Atomicity, Consistency, Isolation, Durability) compliant.
First DBMS which implemented Multi-version concurrency control (MVCC) feature.
It's the default database server for MacOS.
It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;