Skip to content
Introduction to SQL
  • AI Chat
  • Code
  • Report
  • Introduction to SQL

    1. Understand databases and their structure -> Chapter 1
    2. Extract information from databases using SQL -> Chapter 2

    Introducing databases

    Relotional databases

    • Define relationships between tables of data inside the databases

    Database advantages

    • More storage than spreadsheet applications
    • Storage is more secure

    SQL

    • Short for Structured Query Language
    • The most widely used programming language for databases

    SELECT *

    FROM patrons

    LIMIT 30

    Tables

    A seat at the table

    • Table rows and columns are referred to as records and fields
    • Fields are set at database creation; there is no limit to the number of records

    Good table manners

    Table names should...

    • be lowercase
    • have no spaces-use underscores instead
    • refer to a collective group or be plural

    Laying the table: records

    A record is a row that holds data on an individual obvervation

    Laying the table: fields

    A field is a column that holds one piece of information about all records

    More table manners

    Fields names should...

    • be lowercase
    • have no spaces
    • be singular
    • be different from other field names
    • be different from the table name

    Assigned seats

    • Unique identifiers are used to identify records in a table
    • They are unique and often numbers

    Data

    • Different types of data are stored differently and take up different space
    • Some operations only apply to certain data types
    Strings (2nd part in table)
    • A string is a sequence of charachters such as letters or punctuation
    • VARCHAR is a flexible and popular string data type in SQL
    Integers (3rd part in table)
    • Integers store whole numbers
    • INT is a flexible and popular integer data type in SQL
    Floats (4th part in table)
    • Floats store numbers that include a fractional part
    • NUMERIC is a flexible and popular float data type in SQL
    Schemas
    • Schemas are often referred to as "blueprints" of databases.
    • Shows a database's design, such as what tables are included in the database and any relationships between its tables.
    Database storage
    • The information we find in a database table is physically stored on the hard disk of server.
    • Servers are centralized computers that perform services via requests made over a network.
    • Any computer can be a server if it is set up to provide a service, even a laptop.
    • Servers are generally very powerful and large machines, because they are best equipped to handle a high volume of requests and data.

    Introducing queries

    Best for large datasets

    • In many organizations, SQL is used as a complement to other tools such as spreadsheet applications.
    • We can use SQL queries to uncover trends in website traffic, customer reviews, and product sales.

    Keywords

    • Keywords are reserved words for operations
    • Common keywords: SELECT, FROM

    Our first query

    • Query results often called result set

    SELECT name

    FROM patrons;

    Selecting multiple fields

    SELECT card_num, name

    FROM patrons;

    Selecting all fields

    SELECT *

    FROM patrons;

    Writing queries

    Aliasing

    Use aliasing to rename columns

    SELECT name AS firs_name, year_hired

    FROM employees;

    Selecting distinct records

    SELECT DISTINCT years_hired

    FROM employees

    DISTINCT with multiple fields

    SELECT dept_id, year_hired

    FROM employees;

    Views
    • A view is a virtual table that is the result of a saved SQL SELECT statement
    • When accesssed, views automatically update in response to updates in the underlying data

    CREATE VIEW employee_hire_years AS

    SELECT id, name, year_hired

    FROM employees;

    Using views

    SELECT id, name

    FROM employee_hire_years;

    SQL flavors

    Databases such as Microsoft SQL Server or Oracle Database

    • Both free and paid
    • All used with relational databases
    • Vast majority of keywords are the same
    • All must follow universal standards
    • Only the additions on top of these standards make flavors different
    Two popular SQL flavors
    PostgreSQL
    • Free and open-source relational database system
    • Created at the University of California, Berkeley
    • "PostgreSQL" refers to both PostgreSQL database system and its associated SQL flavor.
    • Research funds provided by DARPA.
    SQL Server
    • Has free and paid versions
    • Created by Microsoft
    • T-SQL is Microsoft's SQL flavor, used with SQL Server databases
    Comparing PostgreSQL and SQL Server
    • Like dialects of the same language
    • Example: limiting number of results

    • Differents are small. A PostgreSQL wizard can become a SQL Server wizard by learning a handful of different keywords!