SQL Basics

Chapter 1 - Introduction

NL

01 - Data Storage (Databases)

  • Card index
  • Word document
  • Excel workbook
  • (Relational) Database Management System
    • Manage large volumes of data
    • Support multiple users simultaneously
    • Support multiple locations
    • Secure access to data
    • Enforce business rules
    • Powerful analysis and reporting
    • Applications between users and data

A database is one or more files used to store data in a structured way. A database contains one or more tables. Each table has a unique name and contains data about one type of entity.

02 - DBMS – Database Management System

Relational DBMS

  • Provides access to data
  • Protects data
  • Manages data

Examples

  1. (Commercial) Oracle
  2. (Open source) MySQL
  3. (Commercial) MS SQL Server
  4. (Open source) PostgreSQL
  5. (Commercial) Snowflake
  6. (Commercial) IBM DB2
  7. (Open source) SQLite

RDBMS Rankings

03 - SQL - Structured Query Language

  • ANSI/ISO standard language for relational DBMSs
  • Used to modify and query data
  • SQL is a fourth-generation language (4GL)
  • Developed by IBM in the 1970s
  • Executed through queries
DDLData Definition Language DMLData Manipulation Language DCLData Control Language TCLTransaction Control Language CREATE *ALTER *DROP *TRUNCATE *COMMENTRENAME * SELECT *INSERT *UPDATE *DELETE *MERGECALLEXPLAIN PLANLOCK TABLE GRANTREVOKE COMMITROLLBACKSAVEPOINTSET TRANSACTION SQLCOMMANDS

  • This SQL Basics course covers only the statements marked with an *

04 - What Is Wrong?

Last Name First Name Years of Service Department Location Region Course1 Course2 Course3
de Jong Daan 12 Sales Utrecht 030 Office 365 MySQL HTML & CSS
Jansen Sanne 6 Administration Amsterdam 020 Office 365
de Vries Sanne 3 HRM Amsterdam 002 HTML & CSS Photoshop
van de Berg Tim 21 Purchasing Maarssen 030 Indesign WordPress
van Dijk Julia 1 HRM Amsterdam 020
de Vries Sem 0 ICT Utreg 030 Dreamweaver SQL PHP
de Jong Daan 0 Sales Utrecht 030 Sales 1
Smith Jan 8 Sales Utr. 030 Sales 1 Sales 2
Schilder Annie 33 Administration Amsterdam 020 Photoshop Office 365

05 - Unnormalised Form (0NF)

Last Name First Name Years of Service Department location Region Course1 Course2 Course3
de Jong Daan 12 Sales Utrecht 030 Office 365 MYSQL HTML & CSS
Jansen Sanne 6 Administration Amsterdam 020 Office 365
de Vries Sanne 3 HRM Amsterdam 002 HTML & CSS Photoshop
van de Berg Tim 21 Purchasing Maarssen 030 Indesign WordPress
van Dijk Julia 1 HRM Amsterdam 020
de Vries Sem 5 ICT Utreg 030 Dreamweaver SQL PHP
de Jong Daan 0 Sales Utrecht 030 Sales 1
Smith Jan 8 Sales Utr. 030 Sales 1 Sales 2
Schilder Annie 33 Administration Amsterdam 020 Photoshop Office 365

06 - Normalisation in 3 Steps

Goals:

  • Minimise data storage
  • Avoid redundancy (duplication) and inconsistency (conflicting data)

Steps:

  1. Move repeating groups into separate tables
  2. Move fields that depend on only part of a composite primary key into separate tables
  3. Move fields with transitive dependencies into separate tables (fields that depend on another non-key field)

07 - Keys



  • Every table has a primary key (PK)
  • A foreign key (FK) links a table to the primary key of another table
  • Relationships are usually one-to-many
  • One parent record can have zero or more child records

08 - Unnormalised Form (0NF)

  • Create the unnormalised table and identify (or add) the primary key


09 - First Normal Form (1NF)

  • Move repeating groups into separate tables

One problem with repeating groups is that, when creating a table,
you do not know in advance how many fields you will need (course1, course2, course3, ...).


10 - Second Normal Form (2NF)

  • Move fields that depend on only part of a composite primary key into separate tables

Data that does not depend on the whole primary key leads to redundancy and inconsistency.
(course_name)

11 - Third Normal Form (3NF)

  • Move fields with transitive dependencies into separate tables (fields that depend on another non-key field)

Data that depends on the primary key through another non-key field has a transitive dependency and also leads to redundancy.
(branch, job role)



More about normalisation Wiki

12 - Terminology

Term Description
Database A container for organised data in one or more tables
Table A structured object in a database representing a group of entities
Row A row of data about a specific entity in a table
Column A data field in a table representing a specific property of entities
Data Type Specifies the kind of data a column can contain
Primary Key (PK) One or more columns with unique values used to identify records
Foreign Key (FK) A column that refers to the primary key in another table

13 - Attributes / Fields / Columns

  • Stable attributes (non-volatile)
    • Date of birth
    • Year of manufacture
    • Product number
  • Changing attributes (volatile)
    • Age
    • Vehicle inspection date
  • Required attributes (mandatory)
    • Last Name
    • Car make
  • Unique attributes (identifiers)
    • Vehicle registration number

14 - Data Types

  • Text (string)
    • CHAR (fixed length in the database)
    • VARCHAR (variable length in the database)
    • BLOB (Binary Large Objects (*.jpg))
  • Numeric
    • INTEGER (whole number; INT is also valid)
    • DECIMAL (number with a decimal fraction; DEC is also valid)
    • BOOLEAN (1 or 0 - true or false)
    • BIT (0, 1, NULL)
  • Date/time
    • DATE (date only)
    • TIME (time only)
    • DATETIME (date and time)

More data types at W3Schools

15 - NULL Values

  • A missing value is treated as unknown: NULL
  • NULL is not the same as 0 (zero) or '' (an empty string)
  • Pronounced: null
  • Aggregate functions generally ignore NULL values
  • More about filtering NULL values in Chapter 4