PostgreSQL

docker-compose

postgres:
  image: postgres:alpine
  container_name: postgres
  environment:
    - POSTGRES_PASSWORD=$POSTGRES_PASSWORD
    - POSTGRES_DB=$POSTGRES_DB
    - POSTGRES_USER=$POSTGRES_USER
  restart: always
  volumes:
    - /mnt/ssd/postgres:/var/lib/postgresql/data
  healthcheck:
    test: ["CMD", "pg_isready", "-U", "airflow"]
    interval: 5s
    retries: 5
  ports:
    - "15432:5432"
  networks:
    slate:
      ipv4_address: 172.16.8.9

pgadmin:
  image: dpage/pgadmin4:8.14.0
  container_name: pgadmin
  environment:
    - PGADMIN_DEFAULT_PASSWORD=$POSTGRES_PASSWORD
    - PGADMIN_DEFAULT_EMAIL=example@domain-name.com
  restart: always
  volumes:
    - /mnt/ssd/pgadmin:/var/lib/pgadmin
  ports:
    - "15433:80"
  networks:
    slate:
      ipv4_address: 172.16.8.20

Common SQL Data types

CHAR(L)
VARCHAR(L)
INTEGER
FLOAT(P)
DECIMAL(W,R)
Date/Time: DATE, TIME, TIMESTAMP

CREATE DATABASE

-- Database: slatepencil

-- DROP DATABASE IF EXISTS slatepencil;

CREATE DATABASE slatepencil
    WITH
    OWNER = mini
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    LOCALE_PROVIDER = 'libc'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

CREATE TABLE

-- Executes in both Oracle and PostgreSQL

-- Drop tables if exists
-- DROP TABLE Enrollment;
-- DROP TABLE Offering;
-- DROP TABLE Student;
-- DROP TABLE Course;
-- DROP TABLE Faculty;

CREATE TABLE Student
( StdNo 	    CHAR(11)    CONSTRAINT StdNoRequired NOT NULL,
  StdFirstName  VARCHAR(50) CONSTRAINT StdFirstNameRequired NOT NULL,
  StdLastName   VARCHAR(50) CONSTRAINT StdLastNameRequired NOT NULL,
  StdCity	    VARCHAR(50) CONSTRAINT StdCityRequired NOT NULL,
  StdState	    CHAR(2)	    CONSTRAINT StdStateRequired NOT NULL,
  StdZip	    CHAR(10)    CONSTRAINT StdZipRequired NOT NULL,
  StdMajor	    CHAR(6),
  StdClass	    CHAR(6),
  StdGPA	    DECIMAL(3,2) DEFAULT 0,
  CONSTRAINT PKStudent PRIMARY KEY (StdNo),
  CONSTRAINT ValidGPA CHECK ( StdGPA BETWEEN 0 AND 4 ),
  CONSTRAINT ValidStdClass CHECK (StdClass IN ('FR','SO', 'JR','SR')),
  CONSTRAINT MajorDeclared CHECK
               ( StdClass IN ('FR','SO') OR StdMajor IS NOT NULL ) );

CREATE TABLE Faculty (
FacNo		CHAR(11),
FacFirstName	VARCHAR(30) CONSTRAINT FacFirstNameRequired NOT NULL,
FacLastName	VARCHAR(30) CONSTRAINT FacLastNameRequired NOT NULL,
FacCity		VARCHAR(30) CONSTRAINT FacCityRequired NOT NULL,
FacState	CHAR(2) CONSTRAINT FacStateRequired NOT NULL,
FacZipCode	CHAR(10) CONSTRAINT FacZipRequired NOT NULL,
FacRank		CHAR(4),
FacHireDate	DATE,
FacSalary	DECIMAL(10,2),
FacSupervisor	CHAR(11),
FacDept		CHAR(6),
CONSTRAINT FacultyPK PRIMARY KEY (FacNo),
CONSTRAINT SupervisorFK FOREIGN KEY (FacSupervisor) REFERENCES Faculty );

CREATE TABLE Course (
CourseNo	CHAR(6),
CrsDesc		VARCHAR(50) CONSTRAINT CrsDescRequired NOT NULL,
CrsUnits	INTEGER,
CONSTRAINT CoursePK PRIMARY KEY (CourseNo),
CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc)  );

CREATE TABLE Offering (
OfferNo INTEGER,
CourseNo CHAR(6) CONSTRAINT OffCourseNoRequired NOT NULL,
OffTerm CHAR(6) CONSTRAINT OffTermRequired NOT NULL,
OffYear INTEGER DEFAULT 2022 CONSTRAINT OffYearRequired NOT NULL,
OffLocation VARCHAR(30),
OffTime VARCHAR(10),
FacNo CHAR(11),
OffDays CHAR(6) DEFAULT 'MW',
CONSTRAINT OfferingPK PRIMARY KEY (OfferNo),
CONSTRAINT CourseFK FOREIGN KEY (CourseNo) REFERENCES Course,
CONSTRAINT FacultyFK FOREIGN KEY (FacNo) REFERENCES Faculty );

CREATE TABLE Enrollment (
OfferNo		INTEGER,
StdNo		CHAR(11),
EnrGrade	DECIMAL(3,2) DEFAULT 0,
CONSTRAINT EnrollmentPK PRIMARY KEY (OfferNo, StdNo),
CONSTRAINT OfferingFK FOREIGN KEY (OfferNo) REFERENCES Offering ON DELETE CASCADE,
CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student ON DELETE CASCADE );

Insert Data

-- Execute in both Oracle and PostgreSQL
-- Execute CREATE TABLE statements first

-- Course table
INSERT INTO course
	(CourseNo, CrsDesc, CrsUnits)
	VALUES ( 'FIN300','FUNDAMENTALS OF FINANCE',4);

INSERT INTO course
	(CourseNo, CrsDesc, CrsUnits)
	VALUES ( 'FIN450','PRINCIPLES OF INVESTMENTS',4);

INSERT INTO course
	(CourseNo, CrsDesc, CrsUnits)
	VALUES ( 'FIN480','CORPORATE FINANCE',4);

INSERT INTO course
	(CourseNo, CrsDesc, CrsUnits)
	VALUES ('IS320','FUNDAMENTALS OF BUSINESS PROGRAMMING',4 );

INSERT INTO course
	(CourseNo, CrsDesc, CrsUnits)
	VALUES ( 'IS460','SYSTEMS ANALYSIS',4);

INSERT INTO course
	(CourseNo, CrsDesc, CrsUnits)
	VALUES ( 'IS470','BUSINESS DATA COMMUNICATIONS',4);

INSERT INTO course
	(CourseNo, CrsDesc, CrsUnits)
	VALUES ('IS480','FUNDAMENTALS OF DATABASE MANAGEMENT',4 );

-- Student table
INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('123-45-6789','HOMER','WELLS','SEATTLE','WA','IS','FR',3.00,'98121-1111');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('124-56-7890','BOB','NORBERT','BOTHELL','WA','FIN','JR',2.70,'98011-2121');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('234-56-7890','CANDY','KENDALL','TACOMA','WA','ACCT','JR',3.50,'99042-3321');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('345-67-8901','WALLY','KENDALL','SEATTLE','WA','IS','SR',2.80,'98123-1141');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('456-78-9012','JOE','ESTRADA','SEATTLE','WA','FIN','SR',3.20,'98121-2333');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('567-89-0123','MARIAH','DODGE','SEATTLE','WA','IS','JR',3.60,'98114-0021');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('678-90-1234','TESS','DODGE','REDMOND','WA','ACCT','SO',3.30,'98116-2344');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('789-01-2345','ROBERTO','MORALES','SEATTLE','WA','FIN','JR',2.50,'98121-2212');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('876-54-3210','CRISTOPHER','COLAN','SEATTLE','WA','IS','SR',4.00,'98114-1332');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('890-12-3456','LUKE','BRAZZI','SEATTLE','WA','IS','SR',2.20,'98116-0021');

INSERT INTO Student
	(StdNo, StdFirstName, StdLastName, StdCity,
	 StdState, StdMajor, StdClass, StdGPA, StdZip)
	VALUES ('901-23-4567','WILLIAM','PILGRIM','BOTHELL','WA','IS','SO',3.80,'98113-1885');

-- Faculty table
INSERT INTO Faculty
	(FacNo, FacFirstName, FacLastName, FacCity, FacState,
	 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
	 VALUES ('543-21-0987','VICTORIA','EMMANUEL','BOTHELL','WA','MS','PROF',120000.0,NULL,'15-Apr-2008','98011-2242');

INSERT INTO Faculty
	(FacNo, FacFirstName, FacLastName, FacCity, FacState,
	 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
	 VALUES ('765-43-2109','NICKI','MACON','BELLEVUE','WA','FIN','PROF',65000.00,NULL,'11-Apr-2009','98015-9945');

INSERT INTO Faculty
	(FacNo, FacFirstName, FacLastName, FacCity, FacState,
	 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
	 VALUES ('654-32-1098','LEONARD','FIBON','SEATTLE','WA','MS','ASSC',70000.00,'543-21-0987','01-May-2006','98121-0094');

INSERT INTO Faculty
	(FacNo, FacFirstName, FacLastName, FacCity, FacState,
	 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
	 VALUES ('098-76-5432','LEONARD','VINCE','SEATTLE','WA','MS','ASST',35000.00,'654-32-1098','10-Apr-2007','98111-9921');

INSERT INTO Faculty
	(FacNo, FacFirstName, FacLastName, FacCity, FacState,
	 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
	 VALUES ('876-54-3210','CRISTOPHER','COLAN','SEATTLE','WA','MS','ASST',40000.00,'654-32-1098','01-Mar-2011','98114-1332');

INSERT INTO Faculty
	(FacNo, FacFirstName, FacLastName, FacCity, FacState,
	 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
	 VALUES ('987-65-4321','JULIA','MILLS','SEATTLE','WA','FIN','ASSC',75000.00,'765-43-2109','15-Mar-2012','98114-9954');

-- Offering table
INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (1111,'IS320','SUMMER',2020,'BLM302','10:30:00',NULL,'MW');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (1234,'IS320','FALL',2019,'BLM302','10:30:00','098-76-5432','MW');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (2222,'IS460','SUMMER',2019,'BLM412','13:30:00',NULL,'TTH');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (3333,'IS320','SPRING',2020,'BLM214','08:30:00','098-76-5432','MW');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (4321,'IS320','FALL',2019,'BLM214','15:30:00','098-76-5432','TTH');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (4444,'IS320','WINTER',2020,'BLM302','15:30:00','543-21-0987','TTH');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (5555,'FIN300','WINTER',2020,'BLM207','08:30:00','765-43-2109','MW');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (5678,'IS480','WINTER',2020,'BLM302','10:30:00','987-65-4321','MW');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (5679,'IS480','SPRING',2020,'BLM412','15:30:00','876-54-3210','TTH');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (6666,'FIN450','WINTER',2020,'BLM212','10:30:00','987-65-4321','TTH');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (7777,'FIN480','SPRING',2020,'BLM305','13:30:00','765-43-2109','MW');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (8888,'IS320','SUMMER',2020,'BLM405','13:30:00','654-32-1098','MW');

INSERT INTO Offering
	(OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays)
	VALUES (9876,'IS460','SPRING',2020,'BLM307','13:30:00','654-32-1098','TTH');

-- Enrollment table
INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(1234,'123-45-6789',3.30);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(1234,'234-56-7890',3.50);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(1234,'345-67-8901',3.20);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(1234,'456-78-9012',3.10);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(1234,'567-89-0123',3.80);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(1234,'678-90-1234',3.40);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(4321,'123-45-6789',3.50);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(4321,'124-56-7890',3.20);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(4321,'789-01-2345',3.50);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(4321,'876-54-3210',3.10);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(4321,'890-12-3456',3.40);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(4321,'901-23-4567',3.10);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5555,'123-45-6789',3.20);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5555,'124-56-7890',2.70);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5678,'123-45-6789',3.20);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5678,'234-56-7890',2.80);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5678,'345-67-8901',3.30);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5678,'456-78-9012',3.40);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5678,'567-89-0123',2.60);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5679,'123-45-6789',2.00);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5679,'124-56-7890',3.70);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5679,'678-90-1234',3.30);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5679,'789-01-2345',3.80);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5679,'890-12-3456',2.9);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(5679,'901-23-4567',3.1);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(6666,'234-56-7890',3.1);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(6666,'567-89-0123',3.6);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(7777,'876-54-3210',3.4);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(7777,'890-12-3456',3.7);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(7777,'901-23-4567',3.4);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(9876,'124-56-7890',3.5);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(9876,'234-56-7890',3.2);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(9876,'345-67-8901',3.2);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(9876,'456-78-9012',3.4);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(9876,'567-89-0123',2.6);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(9876,'678-90-1234',3.3);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(9876,'901-23-4567',4);

INSERT INTO Enrollment
	(OfferNO, StdNo, EnrGrade)
	VALUES(1111,'901-23-4567',0.0);

-- Count rows in each table
SELECT COUNT(*) FROM Student;
-- Should be 11 Student rows
SELECT COUNT(*) FROM Course;
-- Should be 7 Course rows
SELECT COUNT(*) FROM Faculty;
-- Should be 6 Faculty rows
SELECT COUNT(*) FROM Offering;
-- Should be 13 Offering rows
SELECT COUNT(*) FROM Enrollment;
-- Should be 38 Enrollment rows

-- commit changes if auto commit not set
COMMIT;

SELECT Examples

SELECT * FROM Faculty;

SELECT * FROM Faculty WHERE FacNo = '543-21-0987';

SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty
WHERE FacSalary > 65000 AND FacRank = 'PROF';

SELECT FacCity, FacState FROM Faculty;

SELECT DISTINCT FacCity, FacState FROM Faculty;

-- Oracle
SELECT FacFirstName, FacLastName, FacCity,
FacSalary*1.1 AS IncreasedSalary,
FacHireDate
FROM Faculty
WHERE to_number(to_char(FacHireDate, 'YYYY')) > 2008;

-- PostgreSQL
SELECT FacFirstName, FacLastName, FacCity,
FacSalary*1.1 AS IncreasedSalary,
FacHireDate
FROM Faculty
WHERE (date_part('YEAR', FacHireDate)) > 2008;

-- Condition using the EXTRACT function
SELECT FacFirstName, FacLastName, FacCity, 
       FacSalary*1.1 AS IncreasedSalary, 
       FacHireDate 
 FROM Faculty 
 WHERE EXTRACT(YEAR FROM FacHireDate) > 2008;

SELECT * FROM Offering WHERE CourseNo LIKE 'IS%';

-- Oracle and PostgreSQL
SELECT FacFirstName, FacLastName, FacHireDate
FROM Faculty
WHERE FacHireDate BETWEEN '1-Jan-2011'
AND '31-Dec-2012';

-- PostgreSQL
SELECT FacFirstName, FacLastName, FacHireDate
FROM Faculty
WHERE FacHireDate BETWEEN '2011-01-01'
AND '2012-12-31';

SELECT OfferNo, CourseNo
FROM Offering
WHERE FacNo IS NULL
AND OffTerm = 'SUMMER'
AND OffYear = 2020;

SELECT OfferNo, CourseNo, FacNo
FROM Offering
WHERE (OffTerm = 'FALL' AND OffYear = 2019)
OR (OffTerm = 'WINTER' AND OffYear = 2020);

SELECT EventNo, DateHeld, Status, EstAudience
FROM EventRequest
WHERE (Status = 'Approved' AND EstAudience > 9000) 
OR (Status = 'Pending' AND EstAudience > 7000);

JOIN Examples

-- use the `Table.Column` to avoid name qualification problem
SELECT OfferNo, CourseNo, FacFirstName, FacLastName
FROM Offering INNER JOIN Faculty ON Faculty.FacNo = Offering.FacNo
WHERE OffTerm = 'Fall'
AND OffYear = 2019
AND FacRank = 'ASST'
AND CourseNo LIKE 'IS%';

-- cross product style with 3 tables
SELECT OfferNo, Offering.CourseNo, OffDays, CrsUnits, OffLocation, OffTime
FROM Faculty, Course, Offering
WHERE Faculty.FacNo = Offering.FacNo
AND Offering.CourseNo = Course.CourseNo
AND OffYear = 2019
AND OffTerm = 'FALL'
AND FacFirstName = 'LEONARD'
AND FacLastName = 'VINCE';

-- join operators style with 3 tables
SELECT OfferNo, Offering.CourseNo, OffDays, CrsUnits, OffLocation, OffTime
FROM Offering INNER JOIN Course
ON Offering.CourseNo = Course.CourseNo
INNER JOIN Faculty
ON Faculty.FacNo = Offering.FacNo
WHERE OffYear = 2019
AND OffTerm = 'FALL'
AND FacFirstName = 'LEONARD'
AND FacLastName = 'VINCE';

-- Type I nested style
SELECT location.locno, locname
FROM location
WHERE location.facno IN 
   ( SELECT facno FROM facility WHERE facname = 'Basketball arena' );

GROUP BY Examples

query clause evalution order: Rows(FROM/WHERE) => Groups(GROUP BY/HAVING) => Result(ORDER BY/SELECT)

SELECT FacNo, FacRank, FacSalary FROM Faculty ORDER BY FacRank;

SELECT FacRank, AVG(FacSalary) AS AvgSalary FROM Faculty GROUP BY FacRank ORDER BY FacRank;

SELECT StdMajor, AVG(StdGPA) AS AvgGpa FROM Student WHERE StdClass IN ('JR', 'SR') GROUP BY StdMajor HAVING AVG(StdGPA) > 3.1;

-- The AS keywords are optional.  Aggregrate functions do not need to be renamed.
SELECT planno, COUNT(*) "Number of Lines", SUM(resourcecnt) "Resource Sum"
FROM eventplanline
GROUP BY planno;

docs

a slatepencil documentail site

PostgreSQL

docker-compose

Common SQL Data types

CREATE DATABASE

CREATE TABLE

Insert Data

SELECT Examples

JOIN Examples

GROUP BY Examples