SQL basics 01¶

What is SQL?¶

• SQL (Structured Query Language) is standardized query language developed by IBM
• First version of SQL was introduced in 1974
• SQL makes it possible to manage and interact with a database
• With SQL you can
  • Perform queries against the selected database
    • Retrive data from database (SELECT)
    • Adding new data to database (INSERT)
    • Removing data from database (DELETE)
    • Manipulate stored data in database (UPDATE)
  • Create a database with tables and connections
  • Set permissions for all of the above
  • Create views that support database queries

SQL queries¶

• Database queries all go through DBMS (Database management system)
• Below is an example where SELECT query is executed against the database in order to retrieve lines from database
  1. SQL query comes from user or application
  2. DBMS optimizes the query and forwards the query to database
  3. Query results are called result set and is returned to DBMS
  4. Result set includes all necessary lines
  5. Result set will be returned to user's DBMS client application or to other application as variable for further processing

Command types¶

• Below is a list of SQL language command types
• In this course we are mainly focusing on DDL and DML queries
  • DDL (Data Definition Language)
    • Database structure definition
    • CREATE, ALTER, DROP ...
  • DML (Data Manipulation Language)
    • Data manipulation commands
    • SELECT, INSERT, UPDATE, DELETE ...
  • DCL (Data Control Language)
    • Commands for controlling the database (for example user permissions)
    • GRANT, REVOKE ...
  • TCL (Transaction Control Language)
    • Managing DML commands with transactions
    • COMMIT, ROLLBACK ...

SQL query syntax¶

• SQL queries can be written in either one or multiple lines
• Below is an example where these two approaches are presented

SELECT * FROM Employee WHERE surname = 'Johnson';

SELECT *
FROM Employee
WHERE surname = 'Johnson';

• This syntax can be broken down into following clauses:
  • SELECT → choose which columns will be shown in the result set
  • FROM → from what table(s) data will be retrieved
  • WHERE → choose which lines will be shown in the result set
• Important: Select statement requires at least select and from clauses to be included!

Comments¶

• Commenting in SQL can be done with any of the following ways:

  • Two lines '--'
    • Works in all SQL products except in MS Access
    • MySQL requires to use one space after these lines
  • Slash with start '/*'
    • Very widely used
    • Allows the commenting of multiple lines
    • Works at least in MySQL, Oracle and SQL server products
  • Hashtag '#'
    • Used usually in MySQL for commenting one line

• Below is an example where all three commenting methods are shown

-- Comment

/* First line
Second line
Third line */

# Comment

Retrieving information from database (SELECT)¶

• SELECT statement can have the following clauses:

  • SELECT → which columns will be selected
  • FROM → from what table(s) or view will the data be retrieved
  • WHERE → which lines will be selected
  • GROUP BY → how result set will be grouped
  • HAVING → which lines in group by will be shown
  • ORDER BY → how result set will be sorted

• DISTINCT clause can be used with SELECT to drop all duplicates from result set

• Like mentioned below, SELECT and FROM clauses must be included in the statement
• Below are some examples of using SELECT

SELECT * FROM Employee;

SELECT firstname FROM Employee;

SELECT city FROM Employee;

SELECT DISTINCT city FROM Employee;

Column alias (AS)¶

• Column names will be shown in the result set as they are in database by default
• This can however be bypassed by giving an optional alias name for the column
• In SQL language, alias is defined by using the AS clause
• Actually, AS clause is only required in MS Access and can be left out on all other products (Only one space between column name and alias is required)!

SELECT DISTINCT city AS Place FROM Employee;

Order the result set (ORDER BY)¶

• Results retrieved with SELECT statement can be ordered by giving ORDER BY clause at the end of the statement
• Results can be ordered in ascending order by using ASC (the default value and is not required) or in descending order using DESC

SELECT firstname, surname, city FROM Employee ORDER BY surname DESC;

• ORDER BY can be used for more than one column
• The order for columns goes in the order columns are given

SELECT firstname, surname, city, salary FROM Employee ORDER BY city, salary DESC;

Limit the result set (LIMIT)¶

• You may choose to only show part of the result set by using LIMIT clause at the end of SELECT statement
• Results can also be retrieved from particular rows using OFFSET clause with LIMIT
• Important: Next value from given offset value will be included!
• Below are couple of examples for using LIMIT and OFFSET clauses

SELECT firstname, surname, city, salary
FROM Employee
LIMIT 30;

SELECT firstname, surname, city, salary
FROM Employee
LIMIT 6 OFFSET 9;

Row selection (WHERE)¶

• WHERE clause allows filtering unwanted rows from the result set
• Logical operators AND, OR and NOT can be used
• In addition, comparison operators defined in ANSI standard can also be used:
  • = → Equal
  • < → Less than
  • > → Greater than
  • <>, != → Not equal
  • <= → Less than or equal
  • >= → Greater than or equal

SELECT city, salary
FROM Employee
WHERE city='Helsinki';

SELECT firstname, surname, salary
FROM Employee
WHERE city='Helsinki'
AND salary < 3000;

• Usage of brackets is required if there is more than one logical operator included in the statement

SELECT firstname, surname, city, salary
FROM Employee
WHERE city='Helsinki' AND (salary = 2800 OR degree='secondary degree');

String patterns (LIKE)¶

• LIKE clause is used with WHERE when a particular string pattern needs to be used as a filter
• The following two wildcards may be used in LIKE clause:
  • % → Zero, one or multiple characters
  • _ → Single character

SELECT firstname, surname, city
FROM Employee
WHERE surname LIKE 'S%'
ORDER BY surname;

SELECT firstname, surname, city
FROM Employee
WHERE surname NOT LIKE '_A%'
ORDER BY surname DESC;

BETWEEN and IN operators¶

• BETWEEN operator is used to get values from given range including begin and end values
• IN operator is used for retrieving exact values. It can include numbers, characters, strings or dates

SELECT firstname, surname, age
FROM Employee
WHERE age BETWEEN 30 and 40
ORDER BY surname;

SELECT firstname, surname, age
FROM Employee
WHERE age IN (25, 30, 35)
ORDER BY surname;

Null value¶

• Null value means missing value
• Null value does not mean
  • space
  • zero
  • zero length string
• Important: When inserting data to the database and some columns are not defined in query with values, these column values will be Null if NOT NULL restriction is not set for the column
• Null values can be retrieved from database table with SELECT statement using IS clause like in the example below

SELECT firstname, surname, degree
FROM Employee
WHERE degree IS NULL;