2 of 19

SQL: What is it?

SQL stands for Structured Query Language.

It was originally developed in the early 1970s by IBM as a way to manipulate and retrieve data stored in IBM’s relational DBMS, System R.

It can be pronounced “Sequel” or “S-Q-L”.

With some variations, SQL is more or less the standard query language for relational databases.

3 of 19

When do we use SQL?

SQL is most commonly used to retrieve data from a database.

It is also commonly used to modify data.

SQL also contains commands for creating tables and other database objects, and for maintaining data security through granting and denying privileges to users.

4 of 19

How is SQL used?

All major DBMS’s come with client tools which allow database managers and users to write SQL queries and send them to the database

The next slide shows a screenshot of one of these client tools, Microsoft’s SQL Server Management Studio

Another slide some shows typical SQL Database Structures

Take note: you will be navigating databases via CLI, so these visuals help!

5 of 19

Choose a database here

Type the SQL here, then Execute

See the results here

6 of 19

SQL Data Structures

Sales Database with 5 Tables,

each containing Columns with Values

Production Database with 4 Tables,

each containing Columns with Values

7 of 19

What can we do with SQL?

SQL has for major components:
DQL (Data Query Language): Used to retrieve data from a database.
DML (Data Manipulation Language): Used to modify the data in a database—inserting, updating, and deleting.
DDL (Data Definition Language): Used to design tables and other database objects.
DCL (Data Control Language): Used to control access to the data using security methods.

8 of 19

Database Basics

Four Basic Operations

CRUD

C – Create (Insert)
R – Read (Select)
U – Update
D – Delete

9 of 19

SQL Basics – Insert

INSERT – Allows Data to be Inserted into Database
Three Basic Components

Table
Column(s)
Values

10 of 19

SQL Basics – Insert

Syntax

INSERT INTO table (column(s)) VALUES (value(s))

Table – Name of Table Data is Being Stored In
Column(s) – Name of Column, or Columns, to Insert Data Into
Value(s) – Values to Insert

Note: Columns and Values Must be in Same Order

11 of 19

SQL Basics - Select

Select – Select Data from Database

Syntax

SELECT column(s) FROM table WHERE condition

Column(s) – Column, or Columns, Names to Retrieve

“*” – Means All Columns from table

Table – Table Name to Get Data From

Can be more than one table

12 of 19

SQL Basics - Select

Example

Select state_name, state_abbr FROM states
Select * FROM agencies

13 of 19

SQL Basics - Where

Where Clause

Added to Refine Result Set
Uses Conditional Operators

=,>,>=,<,<=,!=(<>)
Between x AND y
IN (list)
LIKE ‘%string’ (“%” us a wild-card)
IS NULL
NOT {BETWEEN / IN / LIKE / NULL}

14 of 19

SQL Basics - Where

Examples

SELECT * FROM annual_salaries WHERE gross => ‘100000’�
SELECT state_name FROM states WHERE state_population > 15000000�

15 of 19

SQL Basics – AND & OR

Multiple WHERE conditions are Linked by AND / OR Statements
“AND” – All Conditions True
“OR” – At Least One Condition is TRUE
Group with ()

16 of 19

SQL Basics - Update

Allows Changes to Row(s) of Data in a Table
Three Basic Parts

Name of Table to Update
Column Name to Update
Value to Update

Can Update More Than One Column at a Time
Can Include Where Clause to More Refined Update

17 of 19

SQL Basics - Update

Syntax

UPDATE table SET column = value WHERE column = value

Example

UPDATE clubs SET ClubName = ‘Tresor’ WHERE ClubID = 1

18 of 19

SQL Basics – Delete

Allows for Data to be Removed from the Database
One Required Part

Table Name
Can Delete All Data in Table, or Just Selected Data

One Optional Part

WHERE Clause – Allows for Selective Delete

19 of 19

SQL Basics – Delete

Syntax

DELETE FROM table WHERE column = value

Table – Name of Table to Remove Data from
Column – Name of Column in Table
Value – Value that is in the Column

Example

DELETE FROM clubs (Deletes all Data in Table)
DELETE FROM clubs WHERE ClubID = 1