Skip to content
# Start coding here...

Relational Databases

Before writing any SQL queries, it's important to understand the underlying data. This chapter discusses the role of SQL in creating and querying relational databases. Using a database for a local library, we will explore database and table organisation, data types and storage, and best practices for database construction.

Izzy Weber - SQL Coach

Course Goals

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

Database has data organised in tables with columns and rows

Relational databases "Define relationships between tables of data inside the database"

May look similar to tables found in excel. However they are far more powerful. Database Advantages

  • more storage space
  • can be secured with encryption
  • many people can use at once

SQL Structured Query Language

Most widely used programming language for databases

Tables

Databases are organised into tables that contain relational data. Rows referred to as records Columns as fields Fields are set at database creation; there is no limit to the number of records

Table names should:

  • be lowercase
  • have no spaces - use underscores instead
  • refer to a collevtive group (inventory) or be plural (plural)

Field 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

Better to have more tables then less to keep the information more useable.

SQL Data Types

  • Different types of data are stored differently and take up different space
  • Some operations only apply to certain data types

A string is a sequence of characters such as letters of punctuation

  • different data types can hold different types of strings
  • VARCHAR is a flexible and populat string data type in SQL. Is able to hold long or short strings.

A integer data type store whole numbers

  • similar to string, there are several integer data types used depending on the size of the integer
  • INT is a flexible and popular integer data type in SQL. (less than -2 billion to more than +2 billion)

A float store numbers that include a fractional part

  • several float data types depending on how many digits there are expected to be
  • NUMERIC is a flexible and populat float data type in SQL (storing up to 38 digits toal including before and after decimal point)

Schemas

A schema show's a databases design. Such as:

  • what tables are included in the database
  • any relationships between its tables
  • Let's the reader know what data type each field can hold.

Database Storage

Information we find in a database table is physically stored on the ahrd disk of a server. Servers are centralised computers that perform services via requests made over a network. Currently we are discussing the service, data access.

Querying

Learn your first SQL keywords for selecting relevant data from database tables! After practicing querying skills in a databse of books, you'll customise query results using aliasing and save them as views so they can be shared. Finally you'll explore the differences between SQL flavors and databases such as PostgreSQL and SQL server.

Keywords

keywords are reserved words for operations. Common keywords: SELECT, FROM

The SELECT keyword indiates which fields should be selected.

The FROM keyword indicates the table in which these fields are located.

Query should be written with:

  • Select statement first followed on the next line by the from statement.
  • Finished with a semicolon to indicate the query is complete.
  • Capitalise keywords. Keep table and field names all lowercase.

You can select multiple field by using a , break. The order which it is written will show in the results. The order in the table does not matter. If selecting all field use * in place of field name.

Results of the query are called result set

Spinner
DataFrameas
df
variable
SELECT field_name, second_field_name
FROM tablename;

Further Keywords

Use aliasing to rename columns. AS This renaming only happens in the result set. This does not affect the database you are calling from.

Add the DISTINCT function to gather a field of values without any repeats. This could be useful for example if wanting to see what years the firm had hired new employees.

Spinner
DataFrameas
df1
variable
SELECT name AS first_name, year_hired
FROM employees;
'This SQL code renames name as first_name in the results'

SELECT DISTINCT year_hired
FROM employees;
'This SQL code will only return unique years of people hired'

SELECT dept_id, year_hired
FROM employees;
'This will return that for each department what year did they hire. (dept 1 -hired 2020, dept 2 hired 2017 & 2022 etc...)'
Hidden output

Views

A view is a virtual table that is the result of a saved SQL SELECT statement When accessed, views automatically update in response to updates in the underlying data. Creating a view dosen't lead to a result being outputted. But once a view is created then we can query it simialr with a normal table by selecting FROM the view.

Spinner
DataFrameas
df2
variable
CREATE VIEW employee_hire_years AS
SELECT id, name, year_hired
FROM employees;

SQL Flavors

  • Both free and paid
  • All used with relational databases
  • Vast majority of keywords are the same
  • All must follow univeral standards
  • Only the additions on top of these standards make flavors unique

Two popular SQL flavors

Postgre SQL
  • Free and open-source relational database system
  • Created at the University of California, Berkeley
  • "PostgreSQL" refers to both the PostgreSQL database system and its assocaited SQL flavor
SQL Server
  • Has free and paid versions
  • Created by Microsoft
  • Queried using T-SQL

Like dialects of the same language

Limiting results is useful when testing code, since many result sets can have thousands of results!

The next step is to learn more keywords and to choose which flavor you'll learn them in.