Introduction to SQL

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

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
  • SHOW DATABASES;
  • USE database;
• Tables (rectangular arrays of data)
  • SHOW TABLES;
  • columns (aka fields, variables, \(p\))
  • rows (aka records, cases, observations, \(n\))
• Fields
  • DESCRIBE tablename;
  • must have a specified data type
  • want to choose most appropriate/efficient data type for your data!