Skip to content

Latest commit

 

History

History
184 lines (149 loc) · 6.84 KB

SQL_Language.md

File metadata and controls

184 lines (149 loc) · 6.84 KB

Note 0 | SQL Language

  • Main topic is SQL Recap and Introduction to PostegreSQL

The Relational Data Model

  • Model data as a collection of predicates over a finite sat of variables.

The SQL Language

  • SQL stands for Structured Query Language.

  1. SQL: Creating Relations

    • Relations are created using CREATE TABLE function
    CREATE TABLE weather (
	city 		varchar(80),
	temp_lo	int,				-- low temperature
	temp_hi	int,				-- high temperature 
	prcp		real,			-- precipitation 
	date		date
    • To delete a table, use DROP TABLE:
    DROP TABLE weather ;

  2. SQL: Inserting Data

    • Data is inserted using INSERT statement:
    INSERT INTO weather VALUES (‘San Francisco’,46, 50,0.25,’1994-11-27’);
    • If the set or order of columns is different than the table structure, they need to be specified in the query:
    INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
	VALUES (‘San Francisco’, 43,57,0.0, ‘1994-11-29’);

  3. Basic Operations

    • Selection and Projection are two fundamental operations in SQL.
    • Selection chooses which columns are included in the result set of a SELECT query:
    SELECT surname, age FROM employees;
SELECT * FROM employees;
    • Projection chooses which rows are returned by means of the WHERE clause:
    SELECT * FROM employees WHERE age > 30 ;

  4. Aggregate Functions

    • Aggregate Functions combine a set of values into a single one. They include functions such as SUM, AVG, MIN, MAX, COUNT etc.
    SELECT AVG(age) FROM employees;
    • Aggregates may be computed for groups of rows instead of all records; these groups need to be explicit defined.
    SELECT AVG(age), department FROM employees GROUP BY department;

  5. Filtering in Groups

    • The WHERE clause always applies to individual records. To perform selection on groups, one uses HAVING clause.
    SELECT AVG(age), department FROM employees GROUP BY department
	WHERE age > 30; -- use only people older than 30 for averages
SELECT AVG(age), department FROM employees GROUP BY department
	HAVING AVG(age) > 30; -- only show septs where average is over 30

  6. Inner Joins

    • Joins merge two sets of records, creating a single result set.
    • Inner joins include only records which match both source sets.
    SELECT * FROM weather, cities WHERE weather.city = cities.name;
SELECT * FROM weather INNER JOIN cities IN (weather.city = cities.name);
SELECT * FROM weather JOIN cities ON (weather.city = cities.name);

  7. Outer Joins

    • Outer joins take all records from left, right or both source sets:
    SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather LEFT JOIN cities ON (weather.city = cities.name);

  8. JOIN Syntax

    • The most general syntax uses the ON clause with any logical expression:
    SELECT * FROM employees JOIN departments ON
	(employees.dept_id = departments.dept_id);
    • If matching is performed using the equals operator, and matching attributes have the same names in both data sets the USING clause may be employed:
    SELECT * FROM employees JOIN departments USING (dept_id);
    • In addition, if all attributes with identical names are to be matched, we have a natural join:
    SELECT * FROM employees NATURAL JOIN department;

  9. Set Operations

    • Query results may be combined using the following operators:
      • UNION: appends results of second query to those of the first one,
      • INTERSECT: return all rows that are in both sets,
      • EXCEPT: remove rows in the second result set from the first one.
    • All operators remove duplicates unless used with the ALL modifier.

  10. Subqueries

    • SQL queries may contain subqueries in various places - pay attention attributes and rows expected. Subqueries are often used with the set operators:
      • EXISTS: returns true if subquery has at least one row,
      • IN/NOT IN: checks if value belongs/doesn’t belong to set returned by subquery,
      • ANY/SOME/ALL: similar to the above, but use arbitrary operators (instead of = and <>)

Designing Databases

  1. Good practices for Database Design

    • Every real-world object = one entity = one relation.
    • Every attribute occurs once, with its own object.
    • Decompose non-atomic attributes.
    • 1:n relationships: take primary key of “1”, migrate to “n” relation as foreign key.
    • m:n relationships: create associative entity, use sum of primary keys from “m” and “n” as primary key.

  2. Implemanting “Inheritance”

    • The notion of subclassing is difficult to implement in a relational database, and leads to trade-offs.
    • Possible approaches:
      • one table for superclass (and common attributes), one table for each subclass.
      • one table for each subclass; common attributes replicated in each one. Must use UNION to obtain a set on superclass level.
      • one table for all; each row contains attributes of all subclasses (and those of the superclass). This leads to many NULLs.

#PostgreSQL

  1. PostgreSQL Basics

    • PostgreSQL is a relational database management system
    • Cross-platform & open-source
    • Client-server architecture:
      • server listens on 5432/tcp by default
      • clients use libpq to connect

  2. PostgreSQL Schema

    • Creating a schema:
    CREATE SCHEMA my_othe_schema;

    • Objects in schemas can be referred to by schema.object.

    • Otherwise, the DB looks in schemas listed in search_path, which act much like the PATH system environment variable.

    • To set search_path:

    SET search_path TO public, my_other_schema;
SET search_path TO my_other_schema, public;
SET search_path TO my_other_schema;

  3. PostgreSQL Clients

    • Collection of __command-line utilities, including psql and several helper programs.
    • Any application can use libpq, usually via some wrapper.
    • GUI apps: phppgadmin, Adminer, pgAdmin

  4. Using psql Command-line Switches

    • psql [option...] [dbname [username]]
    • Useful switches:
      • -d dbname –> specify database name
      • -h hostname –> Specify host to connect to; if omitted, it will connect to local UNIX domain socket specified in the config
      • -U username –> specify username
      • -l – list databases
      • -c command –> run specified command and exit
      • -f filename –> run commands from file and exit

  5. _Using psql: Interactive Shell_

    • Some useful meta-commands:
      • \l –> list databases
      • \d –> list objects
      • \dE, \di, \dm, \ds, \dt, \dv –> list objects of specific type: foreign table, index, materialized view, sequence, table, and view
      • \i –> read commands from file
      • \q –> quit