Introduction

Introduction to SQL

SQL, Structured Query Language, is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and helps maintain the integrity of databases, regardless of size.

The SQL language is widely used today across web frameworks and database applications. Knowing SQL gives you the freedom to explore your data, and the power to make better decisions. By learning SQL, you will also learn concepts that apply to nearly every data storage system.

The statements covered in this course use SQLite Relational Database Management System (RDBMS).

Relational Database

A relational database is a database that organizes information into one or more tables.

A table is a collection of data organized into rows and columns. Tables are sometimes referred to as relations.

A column is a set of data values of a particular type.

A row is a single record in a table.

All data stored in a relational database is of a certain data type.
Some of the most common data types are:

• INTEGER, a positive or negative whole number
• TEXT, a text string
• DATE, the date formatted as 'YYYY-MM-DD'
• REAL, a decimal value

Statements

The code below is a SQL statement. A statement is text that the database recognizes as a valid command.
Statements always end in a semicolon ;.

SQL

CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);

Let’s break down the components of a statement:

• CREATE TABLE is a clause. Clauses perform specific tasks in SQL. By convention, clauses are written in capital letters. Clauses can also be referred to as commands.
• table_name refers to the name of the table that the command is applied to.
• (column_1 data_type, column_2 data_type, column_3 data_type) is a parameter. A parameter is a list of columns, data types, or values that are passed to a clause as an argument. Here, the parameter is a list of column names and the associated data type.

Info

The structure of SQL statements vary.

The number of lines used does not matter.

A statement can be written all on one line, or split up across multiple lines if it makes it easier to read.

Example

Let’s open our first database file and enter a SQL command.

• Click on the following link to open the Online SQL Editor we are going to use.
• Click on File>Open DB and select the file myFirstDb.db in ClassesICT.
• Enter your first SQL command in the code editor:

SQL

    SELECT * FROM celebs;

• Click on Run
• Answer the below questions.

What are the table, columns and rows names?

• The table is celebs.
• id, name, and age are the columns.
• The first row in the celebs table has:
  • An id of 1
  • A name of Justin Bieber
  • An age of 22

Which parts of our statement are the clauses?

SELECT and FROM are clauses.

What table are we applying the command to?

We are applying the command to the table celebs.