What is SQL?

  • SQL = Structured Query Language
  • used to query Relational Database Management Systems (RDBMS)
  • based on relational algebra and tuple relational calculus
  • world’s most widely used database language

Reference

SQL in a Nutshell

Flavors & Standards

  • ANSI standard since 1986…
  • …but that is not perfectly followed
  • Converting databases from one implementation to another is not trivial
  • Major implementations:
    • Oracle
    • Microsoft SQL Server
    • SQLite
      • lightweight, embedded
    • MySQL
      • most popular client-server RDBMS
      • open source, but now owned by Oracle
      • MariaDB is community fork
      • Facebook, Google, LinkedIn and Twitter
    • PostgreSQL
      • open source, feature-rich, standards compliant
      • Reddit, Instagram

Hierarchical Data Structure

  • Databases/Schemas contain tables
    • aside: DATABASE vs. SCHEMA
    • psql=# \l
    • psql=# \dn
  • Tables (rectangular arrays of data)
    • psql=# \dt
    • columns (aka fields, variables, \(p\))
    • rows (aka records, cases, observations, \(n\))
  • Fields
    • psql=# \d tablename
    • must have a specified data type
    • want to choose most appropriate/efficient data type for your data!