https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-data-types/#:~:text=PostgreSQL%20supports%20the%20following%20data%20types%3A%201%20Boolean,data%208%20hstore%20stores%20key-value%20pair%20More%20items

https://www.enterprisedb.com/postgres-tutorials/postgresql-query-introduction-explanation-and-50-examples

https://www.postgresql.org/docs/current/sql-commands.html

REVIEW:
Vermont State University CIS uses Lemuria as a departmental server.
https://lemuria.cis.vermontstate.edu/
PLEASE NOTE: If Lemuria fails, it will be replaced by Zeta: 
https://zeta.cis.vermontstate.edu/
Lemuria is a Linux machine.

Copying files to Lemuria:
Many programs can be used to copy files to Lemuria.
I use FileZilla.
https://filezilla-project.org/download.php

After you get FileZilla installed on your system (ask for help if you need it) run it.
(See illustrations.)
HOST: sftp://lemuria.cis.vtc.edu
USERNAME: iAmAStudent [use your student Lemuria id]
PASSWORD: french-fry	[use your Lemuria password]
PORT:22
You can copy and paste into those fields.
Hit Quickconnect.

Putty is an open source SSH and telnet client, available at:
https://putty.org/
Once it is downloaded and installed on your system, you can use it to actually log on to Lemuria.
HOST NAME: lemuria.cis.vermontstate.edu
PORT: 22
Click OPEN
USERNAME: iAmAStudent [use your student Lemuria id]
PASSWORD: french-fry	[use your Lemuria password]

If you COPY text, you can paste it into the Putty window by using RIGHT-CLICK. (This can be very handy when typing your password, but even handier when you need to type large numbers of SQL commands.)

Some of my most commonly used LINUX command include:
cd
ls
chmod

To create a database, use ‘createdb’; 
then ALTER ROLE so it can be accessed with a password.

createdb database		//creates a database
dropdb database		//disposes of a databasee
psql database		//use a database
ALTER ROLE iAmAStudent WITH PASSWORD ‘whatever’;
				//associate the password ‘whatever’
				//owned by iAmAStudent
				//with the current database
NORMALLY, to use a database:
psql database
database=>
\q				//to quit

BEGINNING SQL

PostgreSQL data types include Boolean, character, numeric, temporal, array, json, uuid, and special types.

Once a database has been created, you need to create tables.  Examples:
CREATE TABLE tableName(
	ID CHAR(10) PRIMARY KEY,
	someData CHAR(10),
	moreData CHAR(80)
);
CREATE TABLE episodeTable(
	episodeID CHAR(10) PRIMARY KEY,
	episodeDate CHAR(10),
	episodeTitle CHAR(80)
);
You need to have the table name,
A list of variable names and types separated by commas
Label the PRIMARY KEY.

CREATE TABLE episodeTable(
	episodeID CHAR(10) PRIMARY KEY,
	episodeDate CHAR(10),
	episodeTitle CHAR(80)
);
CREATE TABLE descriptionTable(
	episodeID CHAR(10) PRIMARY KEY,
	episodeDescription CHAR(500)
);
CREATE TABLE linkTable(
	episodeID CHAR(10) PRIMARY KEY,
	episodeLink CHAR(500)
);

Once you have your tables, it is appropriate to enter data.
Obviously, the number and types of inserted data must match the table definition.

INSERT INTO episodeTable VALUES
	('17666918', '01/24/2021', 'Bananas: Myth Busting');   

INSERT INTO descriptionTable VALUES
	('17666918', 'I hear a lot of my friends who strictly watch their diet that they avoid bananas out of concern for weight loss. I also hear a lot of my diabetic patients are worried about having a banana could spike their blood sugars. We take a closer look at some of the macro and micronutrients in bananas and how they are metabolized by the body.'); 

INSERT INTO linkTable VALUES
	('17666918', 
'<iframe title="Libsyn Player" style="border: none" 
Src = "//html5-player.libsyn.com/embed/episode/id/17666918/height/90/theme/custom/thumbnail/yes/direction/forward/render-playlist/no/custom-color/000000/" 
height="90" width="100%" scrolling="no"  allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen>
</iframe>');

To get information from a database, usually involves the SELECT statement.

SELECT * FROM someTable;
//gives the whole table

SELECT * FROM episodeTable WHERE POSITION('Banana' IN episodeTitle)>0;
//gives all values where Banana is in the episodeTitle

SELECT episodeTitle FROM episodeTable WHERE POSITION('Banana' IN episodeTitle)>0;
//gives only the title where Banana is in the episodeTitle

Compound SELECT statements can get complicated!

You can change data in existing rows with UPDATE:

update dummy_table set age=50 where name='PQR';

It will change the given attribute for any row on which the condition is met.

You can even return a result, for verification:
update dummy_table set age=30 where name='XYZ' returning age as age_no;

You can even conditionally delete rows:
delete from dummy_table where age=65;

Greater than or equal: 	>=
Not equal: 		<>
Equal:			=
Less than or equal:	<=

Often, you wish to remove duplicates.
select age from dummy_table order by 1;
select distinct age from dummy_table order by 1;

DISTINCT automatically removes duplicates.

AND OR and NOT work as logical operators for more complex conditions.
UNION, a set operation, can be used to combine the results of queries.
There are date, time, and string predefined functions.

JOINS
A JOIN is when you combine tables, to make a (temporary) larger table. This is common because you rarely have all of the data you want to retrieve in one table. (See Normalization)

NATURAL JOIN
In a natural join, all entries which match on attributes which have the same name are combined.
Many believe specifying the attributes is both safer and has more clarity.

INNER JOIN
In an inner join, all entries where the specified attributes match are combined.
If the specified attributes do not match, it is not included.

LEFT JOIN
In a left join, all entries of the first (left) table are kept, 
even if the second (right) table has no matching entry. 
(A RIGHT JOIN is the mirror image)

SELF JOIN (can be done with INNER or LEFT JOIN) is when you join a table to itself.
This might be needed in recursive situations, such as ‘supervise’.

EXAMPLES:
select * from episodeTable where episodeID = '20766050';
//episodeTable information about a certain ID

select * FROM episodetable where position('21:' IN episodeTable.episodeDate)>0;
//in this case, find all references to the year 2021

SELECT episodedate, episodetitle, episodelink FROM episodetable natural join descriptionTable natural join linktable 
WHERE POSITION(lower('AIDS') IN lower(descriptiontable.episodeDescription))>0 
OR POSITION(lower('AIDS') IN lower(episodetable.episodetitle))>0;
//AIDS is in either the description or the title
//’lower’ removes capitalization //POSITION returns the location within a string

//manually replace an incorrect linktable
delete from linkTable where episodeID ='24334176';
INSERT INTO linkTable VALUES ('24334176','<br/><iframe title="Libsyn Player" style="border: none" src="//html5-player.libsyn.com/embed/episode/id/24334176/height/90/theme/custom/thumbnail/yes/direction/forward/render-playlist/no/custom-color/000000/" height="90" width="100%" scrolling="no"allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen>');

//look for episodes missing in linkTable
select * from descriptionTable left join linkTable
on  linkTable.episodeID = NULL;