Skip to content

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!