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 object-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 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 https://en.wikipedia.org/wiki/Server (computing) ◮ 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 CREATE TABLE movie ( Many keywords may be used as names id integer, title text, ◮ Notice the year attribute − → 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