- Intro to Postgresql


- Intro to SQL

SQL
------------

DDL - data definition language
      CREATE TABLEs
      drop tables
      

DML - data manipulation language


------------------

SQL - industry standard

core SQL

SQL - tuple at a time

SQL - bag semantics implementation (not sets)

   -> relations must have a primary key is you do not want duplicate
      tuples

   -> queries may return copies of tuples unless you remove them!

SQL ->
  - case insensitive
  - ; for end of command
  - strings are delimited by single quotes
  
------------

CREATE USER kkuzmin WITH NOSUPERUSER CREATEDB LOGIN PASSWORD 'password';
\c postgres kkuzmin
CREATE DATABASE baking;
SET client_encoding = 'UTF8';
\i c:/Downloads/baking_database.sql

GBBO: https://en.wikipedia.org/wiki/The_Great_British_Bake_Off
Saving password (insecure!!!): https://www.postgresql.org/docs/current/libpq-pgpass.html


Select - like projection but no duplicate removal!
--------------------------------------------------

SELECT
    *
FROM
    bakers;


SELECT DISTINCT
   age
FROM
   bakers ;


SELECT DISTINCT
   age
   , fullname
FROM
   bakers ;

SELECT DISTINCT
   baker
   , rank
FROM
   technicals ;

String functions: https://www.postgresql.org/docs/15/functions-string.html

FROM bakers relation -> return all tuples -> but for each tuple return
attributes in the SELECT

SELECT
   age
   , upper(fullname) as fullnameofcontestant
   , 'season 4' as season
   , fullname || ' ' || age::char(4) || ' ' || hometown as bakerinfo
   , substring(fullname from strpos(fullname, ' ') + 1)
FROM
   bakers ;




WHERE statement
---------------

SELECT
   fullname
   , age
FROM
   bakers
WHERE
   age <= 30
   and length (fullname)>15;

SELECT
   fullname
   , age
FROM
   bakers
WHERE
   hometown = 'London';


Special characters in strings
-----------------------------

You can tell SQL not to treat a character as part of the regular expression by escaping it

SELECT '100% done' LIKE '100%', '100 out of 1,000 are done' LIKE '100%' ;

SELECT '100% done' LIKE '100\%%', '100 out of 1,000 are done' LIKE '100\%%' ;

You can change the escape character with the keyword ESCAPE.

SELECT '100% done' LIKE '100x%%' ESCAPE 'x', '100 out of 1,000 are done' LIKE '100x%%' ESCAPE 'x' ;

Escape single quote by repeating it:

SELECT
    'professor''s cat' ;

Any special character needs to be escaped. The general escape character is `\`.

SELECT 
    fullname || E'\n' || hometown
FROM
    bakers ;


LIKE:

% -> 0 more occurrence of any char
_ -> 1 character

SELECT
   fullname
   , age
   , occupation
FROM
   bakers
WHERE
   occupation LIKE '%parent' ;

SELECT
   fullname
   , age
   , occupation
FROM
   bakers
WHERE
   lower(occupation) LIKE '%project%' ;

Escape for single quote

SELECT 'abc''def' ;

SELECT 'abc' || E'\n' || 'def' ;

SELECT * FROM tmp WHERE name LIKE '%c\%%';

SELECT * FROM tmp WHERE name LIKE '% \\___\\%';

SELECT * FROM tmp WHERE name LIKE '%cz%%' escape 'z';

SELECT * FROM tmp WHERE name LIKE '% \___\ez%%' escape 'z';

SELECT * FROM bakers WHERE occupation ~ '^.*er$';

SELECT id, regexp_replace(name, '^a\s', 'the ') FROM tmp WHERE regexp_count(name, '\s(of|in)\s') > 0;

SELECT $$'\n'"_%$$;

SELECT U&'\0441\043B\043E\043D';

-------------