PostgreSQL, pgAdmin, and JOINs PDBM 7.37.3.1.5 Dr. Chris Mayfield - - PowerPoint PPT Presentation

PostgreSQL, pgAdmin, and JOINs

PDBM 7.3–7.3.1.5

• Dr. Chris Mayfield

Department of Computer Science James Madison University

Feb 06, 2020

What is PostgreSQL?

“PostgreSQL is a powerful, open source

• bject-relational database system. . . ”

“. . . and has more than 30 years of active development on the core platform.” See https://www.postgresql.org/about/ Why are we using it instead of ? ◮ It’s arguably the most advanced open-source DBMS ◮ And it has many advantages for teaching and research

◮ https://blog.panoply.io/postgresql-vs.-mysql ◮ https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/ ◮ https://blog.dumper.io/showdown-mysql-8-vs-postgresql-10/

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 2 of 18

Our (virtual) server

https://en.wikipedia.org/wiki/Server (computing)

Host: data.cs.jmu.edu ◮ Only accessible from JMU network Hardware ◮ Intel Xeon Silver 4116 (4 cores) ◮ 8 GB RAM (plus 4 GB swap) ◮ 100 GB virtual disk space Software ◮ Linux 4.15 / Ubuntu 18.04.3 ◮ PostgreSQL 11.6 / pgAdmin 4.16

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 3 of 18

Demo of pgAdmin

Download here: https://www.pgadmin.org/

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 4 of 18

Other GUI tools

There are many other tools: ◮ https://wiki.postgresql.org/wiki/PostgreSQL Clients Some that I have used: ◮ Postbird https://github.com/paxa/postbird ◮ PSequel http://www.psequel.com/ (macOS only) ◮ DBeaver https://dbeaver.io/ (based on Eclipse) ◮ SQuirrel http://www.squirrelsql.org/ (uses JDBC)

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 5 of 18

Structured Query Language

Tutorial about LIKE, JOIN, and NULL

Some notes on SQL

Whitespace doesn’t matter ◮ But indenting is strongly recommended ◮ All statements should end with a ‘;’ SQL is case-insensitive ◮ By convention, keywords are in CAPS ◮ PostgreSQL makes all names lowercase Many keywords may be used as names ◮ Notice the year attribute − →

CREATE TABLE movie ( id integer, title text, year integer, genres text, mpaa text, budget text );

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 7 of 18

Renaming with AS

◮ Use AS to rename columns or tables

SELECT m.title AS name FROM movie AS m WHERE m.year = 2000;

◮ Without AS, 2nd column would be named split part

SELECT name, split_part(name, ✬,✬, 1) AS last_name FROM person LIMIT 10;

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 8 of 18

Operators in SELECT/WHERE

Logical operators:

= <> < > <= >= NOT AND OR SELECT title FROM movie WHERE (year > 1970 OR season_nr < 3) AND kind_id = 7;

http://www.postgresql.org/docs/11/static/sql-syntax-lexical.html#SQL-PRECEDENCE

Arithmetic operators:

+

• *

/ % ^ |/ ||/ ! !! @ & | # ~ << >>

http://www.postgresql.org/docs/11/static/functions-math.html Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 9 of 18

Pattern matching in SQL

Wildcards ◮ = single character ◮ % = any substring

SELECT title FROM movie WHERE title LIKE ✬Star ____✬; SELECT title FROM movie WHERE title LIKE ✬%✬✬%✬;

PostgreSQL also supports case-insensitive:

SELECT title FROM movie WHERE title ILIKE ✬incep%✬;

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 10 of 18

SQL Exercises: imdb

Look for “Frozen” in the movie table Only display movies after the year 2010 List all movies beginning with “Fro” List unique titles starting with “Fro”

More than one relation

SELECT * FROM movie, movie_info WHERE movie.id = movie_info.movie_id LIMIT 10;

◮ Is this a cross product or a join?

SELECT * FROM movie AS m, movie_info AS i WHERE m.id = i.movie_id LIMIT 10;

◮ How did we disambiguate attributes?

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 12 of 18

SQL join expressions

SELECT * FROM movie, movie_info; -- implicit product SELECT * FROM movie CROSS JOIN movie_info; -- explicit product SELECT * FROM movie AS m

• - theta join (a.k.a. inner join)

JOIN movie_info AS i ON m.id = i.movie_id

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 13 of 18

SQL Exercises: imdb

For each “Frozen” what kind of movie was it? What is the plot of the movie Frozen (2013)? Hint: plot is info id = 98 in movie info

Outer joins in SQL

• - return all movies, with optional plots

SELECT * FROM movie AS m LEFT JOIN movie_info AS i ON m.id = i.movie_id AND i.info_id = 98

• - return all plots, with optional movies

SELECT * FROM movie AS m RIGHT JOIN movie_info AS i ON m.id = i.movie_id AND i.info_id = 98

• - return all movies and plots (side by side)

SELECT * FROM movie AS m FULL JOIN movie_info AS i ON m.id = i.movie_id AND i.info_id = 98

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 15 of 18

See full article on CodeProject

NULL and UNKNOWN

In SQL, attribute values may be NULL ◮ integer may be −2147483648 to +2147483647 or NULL ◮ text may be ✬Hello✬ or ✬✬ or NULL Rules for NULL

• 1. Any operation on NULL is NULL
• 2. Any comparison with NULL is UNKNOWN

What is the value of: ◮ SELECT 5 + NULL ◮ WHERE title = NULL

• - incorrect

◮ WHERE title IS NULL

• - correct

Feb 06, 2020 PostgreSQL, pgAdmin, and JOINs 17 of 18

SQL Exercises: imdb

What is the budget of each movie? (if known) What is the runtime of each movie? (if known) Hint: in movie info, budget is 105, runtime is 1