SQL Programming
SQL Programming Training Materials
IMPORTANT NOTE for trainers only: Please DO NOT change these materials, especially the EXERCISES, without direct permission from: GRZEGORZ PRUSZCZYNSKI or LUKASZ SOKOLOWSKI.
Copyright Notice
Copyright © 2004-2023 by NobleProg Limited All rights reserved.
This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.
Relational Database
There are three main types of database:
- Flat Database
- Relational Database (RDBMS) (1974)
- Object Database (ODBMS) (1980)
Flat Database
In this model all the data is stored in one “table”, e.g. an address book, roll, etc.
Advantages
- Simplicity
Disadvantages
- Redundancy
- Very slow inserts and updates
Relational Database
Every ENTITY is stored in a separate flat table. All the tables may be JOINED with relations.
Advantages
- Fast inserts and updates
- No redundant data
- Easy data searching
Disadvantages
- Complex structures
- Use in Object Oriented Programming Languages (like C#, Java, etc.) is quite cumbersome
Object Database
Everything is stored as an object which is an instance of a class.
Advantages
- Fast inserts and updates
- No redundant data
- Easy data searching
- No complex mechanism involved in translating to OOP
Disadvantages
- It is difficult for mere mortals and non-programmers to understand
Overview
Tables used during course
EMP Table
+------+----------+-----------+------+------------+---------+---------+--------+ | ID | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------+----------+-----------+------+------------+---------+---------+--------+ | 7839 | BUSH | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7698 | BLAIR | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | MERKEL | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7566 | PUTIN | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | CHIRACK | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7499 | BAROSSO | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7844 | GATES | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7900 | BUFFETT | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7521 | WALTON | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7902 | TOOSK | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7369 | THATCHER | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7788 | CARNEGIE | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 | | 7876 | FORD | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 | | 7934 | ELISON | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +------+----------+-----------+------+------------+---------+---------+--------+
DEPT Table
+--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | LONDON | | 30 | SALES | PARIS | | 40 | OPERATIONS | BERLIN | +--------+------------+----------+
SALGRADE Table
+-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
DB prep
-- Creating tables CREATE TABLE dept ( DEPTNO NUMERIC(2) NOT NULL, DNAME CHAR(14), LOC CHAR(13) ); CREATE TABLE emp ( ID NUMERIC(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4) , HIREDATE DATE, SAL NUMERIC(7,2), COMM NUMERIC(7,2), DEPTNO NUMERIC(2) NOT NULL ); CREATE TABLE salgrade ( GRADE NUMERIC, LOSAL NUMERIC, HISAL NUMERIC); -- Feeding tables with values INSERT INTO dept(DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept(DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'LONDON'); INSERT INTO dept(DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'PARIS'); INSERT INTO dept(DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BERLIN'); INSERT INTO EMP VALUES (7839,'BUSH','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO EMP VALUES (7698,'BLAIR','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO EMP VALUES (7782,'MERKEL','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO EMP VALUES (7566,'PUTIN','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO EMP VALUES (7654,'CHIRACK','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO EMP VALUES (7499,'BAROSSO','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO EMP VALUES (7844,'GATES','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO EMP VALUES (7900,'BUFFETT','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO EMP VALUES (7521,'WALTON','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO EMP VALUES (7902,'TOOSK','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO EMP VALUES (7369,'THATCHER','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO EMP VALUES (7788,'CARNEGIE','ANALYST',7566,'1982-12-09',3000,NULL,20); INSERT INTO EMP VALUES (7876,'FORD','CLERK',7788,'1983-01-12',1100,NULL,20); INSERT INTO EMP VALUES (7934,'ELISON','CLERK',7782,'1982-01-23',1300,NULL,10); INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999);
| DQL
Data Query Language |
DML
Data Modification Language |
DDL
Data Definition Language |
DCL
Data Control Language |
|---|---|---|---|
| Pulls data from database | Modifies data in database | Modifies structures of database | Manages user and objects
rights and permissions |
| SELECT | INSERT | CREATE | GRANT |
| UPDATE | ALTER | REVOKE | |
| DELETE | DROP |
MySQL Client Program
mysql
- Delivered with MySQL database
- Text-based interface
- Good for batch working
Toad
- www.quest.com/Toad_for_MySQL/
- Third party program
- Has a free version
- Probably the best SQL and Procedure editor
Query Browser
- www.mysql.com/products/tools/query-browser/
- Quite cumbersome
- Supports some extra functions
- Poor script support
SQL Developer
- http://www.oracle.com/technology/products/database/sql_developer/index.html
- Supports many different databases
SQL Language
Entering query
Query example
Select all columns and rows from emp table.
select * from emp
+------+----------+-----------+------+------------+---------+---------+--------+ | ID | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------+----------+-----------+------+------------+---------+---------+--------+ | 7839 | BUSH | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7698 | BLAIR | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | MERKEL | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7566 | PUTIN | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | CHIRACK | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7499 | BAROSSO | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7844 | GATES | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7900 | BUFFETT | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7521 | WALTON | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7902 | TOOSK | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7369 | THATCHER | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7788 | CARNEGIE | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 | | 7876 | FORD | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 | | 7934 | ELISON | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +------+----------+-----------+------+------------+---------+---------+--------+
Select three columns: ename, job and sal and all rows from emp table.
select ename, job, sal from emp;
+----------+-----------+---------+ | ename | job | sal | +----------+-----------+---------+ | BUSH | PRESIDENT | 5000.00 | | BLAIR | MANAGER | 2850.00 | | MERKEL | MANAGER | 2450.00 | | PUTIN | MANAGER | 2975.00 | | CHIRACK | SALESMAN | 1250.00 | | BAROSSO | SALESMAN | 1600.00 | | GATES | SALESMAN | 1500.00 | | BUFFETT | CLERK | 950.00 | | WALTON | SALESMAN | 1250.00 | | TOOSK | ANALYST | 3000.00 | | THATCHER | CLERK | 800.00 | | CARNEGIE | ANALYST | 3000.00 | | FORD | CLERK | 1100.00 | | ELISON | CLERK | 1300.00 | +----------+-----------+---------+
Use "--" (hyphen hyphen) characters to comment whole line
-- This is commented line
Use C langue comment notation (/* and */) to comment some text inside line or couple of lines.
select ename, /* job, */ sal from emp; /* This is commented text */
SQL Rules:
- Every query must be followed by a semicolon ";"
- SQL language is case insensitive
- White characters are ignored
- New line characters (enters) are ignored
- The order of CLAUSES is determined and cannot be changed
Examples
select ename,
job,
hiredate
from emp
where job='MANAGER';
Good practices
- Every clause (i.e. word which starts part of SQL sentence) should start on new line.
- Use indent (e.g. spaces, tabs) to show logical structure
- Use a comment to clarify your goals
- Don’t make lines too long
Oracle SQL language documentation
Go to the https://docs.oracle.com/en/database/database.html
MySQL SQL language documentation
Go to the http://dev.mysql.com/doc/ web site.
Get familiar with the documentation structure.
Learn how to read SQL Syntax
SQL syntax example
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FOR UPDATE | LOCK IN SHARE MODE]]
SQL syntax explanation Upper case are SQL language keywords.
Lower case words are your database specific expressions (e.g. column names, table names and constants, etc).
Square brackets [] means that the keyword is optional (not required)
The simplest query looks like this
select 'literal'; +---------+ | literal | +---------+ | literal | +---------+
The ‘literal’ word doesn’t mean anything in particular. It might be anything. Pipe character | means exclusive alternative, i.e. you can choose one and only one of the keywords.
Example: SELECT [ALL | DISTINCT ] .....
It means you can use either ALL OR DISTINCT but you cannot use them both in one query. The following query is correct because it uses only the DISTINCT keyword.
select DISTINCT deptno
from emp;
+--------+
| deptno |
+--------+
| 10 |
| 30 |
| 20 |
+--------+
The statement below returns an error because you cannot use DISTINCT and ALL keywords in one query.
select DISTINCT ALL deptno
from emp;
ERROR 1221 (HY000): Incorrect usage of ALL and DISTINCT
DQL Language and SELECT clause
Arithmetic operators
- Addition (+)
- Subtraction (-)
- Unary minus (-) changes the sign of the argument.
- Multiplication (*)
- Division (/)
select 2+2, 12-3, -(10+2), 4*3, 13/4; +-----+------+---------+-----+--------+ | 2+2 | 12-3 | -(10+2) | 4*3 | 13/4 | +-----+------+---------+-----+--------+ | 4 | 9 | -12 | 12 | 3.2500 | +-----+------+---------+-----+--------+
select 4/0; +------+ | 4/0 | +------+ | NULL | +------+
(SQL Server) Divide by zero error encountered.
select 2+2; +-----+ | 2+2 | +-----+ | 4 | +-----+
select sal,
comm,
sal*12+comm
from emp;
+---------+---------+-------------+
| sal | comm | sal*12+comm |
+---------+---------+-------------+
| 5000.00 | NULL | NULL |
| 2850.00 | NULL | NULL |
| 2450.00 | NULL | NULL |
| 2975.00 | NULL | NULL |
| 1250.00 | 1400.00 | 16400.00 |
| 1600.00 | 300.00 | 19500.00 |
| 1500.00 | 0.00 | 18000.00 |
| 950.00 | NULL | NULL |
| 1250.00 | 500.00 | 15500.00 |
| 3000.00 | NULL | NULL |
| 800.00 | NULL | NULL |
| 3000.00 | NULL | NULL |
| 1100.00 | NULL | NULL |
| 1300.00 | NULL | NULL |
+---------+---------+-------------+
Column aliases
You can replace the column headers with more friendly names. You may use an AS keyword but you don’t have to.
select sal as Salary,
sal Salary
from emp;
+---------+---------+ | Salary | Salary | +---------+---------+ | 5000.00 | 5000.00 | .....
If your alias contains a space character use single quotes ‘’.
select sal 'Annual salary' from emp; +---------------+ | Annual salary | +---------------+ | 5000.00 | ..........
Column aliases are useful with complex expressions and functions.
(curdate() should be replaced in getdate() in SQL SERVER, sysdate in Oracle)
select sal*12+comm 'Annual remuneration',
curdate() AS 'Current date'
from emp;
+---------------------+--------------+ | Annual renumeration | Current date | +---------------------+--------------+ | NULL | 2006-05-12 | | NULL | 2006-05-12 | | NULL | 2006-05-12 | | NULL | 2006-05-12 | | 16400.00 | 2006-05-12 | | 19500.00 | 2006-05-12 | | 18000.00 | 2006-05-12 | | NULL | 2006-05-12 | | 15500.00 | 2006-05-12 | | NULL | 2006-05-12 | | NULL | 2006-05-12 | | NULL | 2006-05-12 | | NULL | 2006-05-12 | | NULL | 2006-05-12 | +---------------------+--------------+
Literals
You can use literal values.
select ename, ' is ' , job from emp;
+----------+------+-----------+ | ename | is | job | +----------+------+-----------+ | BUSH | is | PRESIDENT | | BLAIR | is | MANAGER | | MERKEL | is | MANAGER | | PUTIN | is | MANAGER | | CHIRACK | is | SALESMAN | | BAROSSO | is | SALESMAN | | GATES | is | SALESMAN | | BUFFETT | is | CLERK | | WALTON | is | SALESMAN | | TOOSK | is | ANALYST | | THATCHER | is | CLERK | | CARNEGIE | is | ANALYST | | FORD | is | CLERK | | ELISON | is | CLERK | +----------+------+-----------+
Concatenation function
You can concatenate (join) two or more columns into one column.
| MySQL | SQL Server | Oracle | Ms Access |
|---|---|---|---|
| select concat(id,ename,job) from emp; | select id + ename + job from emp; | select id // ename // job from emp; | select id & ename & job from emp; |
+----------------------+ | concat(id,ename,job) | +----------------------+ | 7839BUSHPRESIDENT | | 7698BLAIRMANAGER | | 7782MERKELMANAGER | | 7566PUTINMANAGER | | 7654CHIRACKSALESMAN | | 7499BAROSSOSALESMAN | | 7844GATESSALESMAN | | 7900BUFFETTCLERK | | 7521WALTONSALESMAN | | 7902TOOSKANALYST | | 7369THATCHERCLERK | | 7788CARNEGIEANALYST | | 7876FORDCLERK | | 7934ELISONCLERK | +----------------------+
It is also possible to concatenate literals and functions.
select concat(id,' ',ename,' is ',job,' today ',curdate()) from emp;
+-----------------------------------------------------+ | concat(id,' ',ename,' is ',job,' today ',curdate()) | +-----------------------------------------------------+ | 7839 BUSH is PRESIDENT today 2006-05-12 | | 7698 BLAIR is MANAGER today 2006-05-12 | | 7782 MERKEL is MANAGER today 2006-05-12 | | 7566 PUTIN is MANAGER today 2006-05-12 | | 7654 CHIRACK is SALESMAN today 2006-05-12 | | 7499 BAROSSO is SALESMAN today 2006-05-12 | | 7844 GATES is SALESMAN today 2006-05-12 | | 7900 BUFFETT is CLERK today 2006-05-12 | | 7521 WALTON is SALESMAN today 2006-05-12 | | 7902 TOOSK is ANALYST today 2006-05-12 | | 7369 THATCHER is CLERK today 2006-05-12 | | 7788 CARNEGIE is ANALYST today 2006-05-12 | | 7876 FORD is CLERK today 2006-05-12 | | 7934 ELISON is CLERK today 2006-05-12 | +-----------------------------------------------------+
Concatenation in Oracle
In Oracle we cannot use concat() function. The same functionality is acheived by double pipe operator ||. Two analogical examples below:
select id || ename || job from emp;
select id || ' ' || ename || ' is ' || job || ' today ' || sysdate from emp;
Concatenation in SQL Server
In SQL Server sometimes you need to convert numeric values into text.
select ename + convert(varchar,deptno) from EMP; Select distinct rows
Sometimes a duplicate row may occur.
select job from emp; +-----------+ | job | +-----------+ | PRESIDENT | | MANAGER | | MANAGER | | MANAGER | | SALESMAN | | SALESMAN | | SALESMAN | | CLERK | | SALESMAN | | ANALYST | | CLERK | | ANALYST | | CLERK | | CLERK | +-----------+
You can eliminate repeated rows using the DISTINCT keyword.
select distinct job from emp;
+-----------+ | job | +-----------+ | PRESIDENT | | MANAGER | | SALESMAN | | CLERK | | ANALYST | +-----------+
DISTINCT may occur only once in a query and affects the whole row, not the first column only.
select DISTINCT job, deptno from emp;
+-----------+--------+ | job | deptno | +-----------+--------+ | PRESIDENT | 10 | | MANAGER | 30 | | MANAGER | 10 | | MANAGER | 20 | | SALESMAN | 30 | | CLERK | 30 | | ANALYST | 20 | | CLERK | 20 | | CLERK | 10 | +-----------+--------+
For example the query below is not correct.
select DISTINCT job,
DISTINCT deptno
from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT deptno from emp' at line 1
Dealing with null value
The NULL value isn’t just zero (0) or an empty string “”. It’s something like a void. Almost every operation with NULL returns NULL value.
select null*2,
null+2,
NULL/NULL,
12/NULL,
NULL>2,
NULL=NULL;
+--------+--------+-----------+---------+--------+-----------+ | null*2 | null+2 | NULL/NULL | 12/NULL | NULL>2 | NULL=NULL | +--------+--------+-----------+---------+--------+-----------+ | NULL | NULL | NULL | NULL | NULL | NULL | +--------+--------+-----------+---------+--------+-----------+
NULL value may cause problems as in the following example.
select sal, comm, sal+comm from emp;
+---------+---------+----------+ | sal | comm | sal+comm | +---------+---------+----------+ | 5000.00 | NULL | NULL | | 2850.00 | NULL | NULL | | 2450.00 | NULL | NULL | | 2975.00 | NULL | NULL | | 1250.00 | 1400.00 | 2650.00 | | 1600.00 | 300.00 | 1900.00 | | 1500.00 | 0.00 | 1500.00 | | 950.00 | NULL | NULL | | 1250.00 | 500.00 | 1750.00 | | 3000.00 | NULL | NULL | | 800.00 | NULL | NULL | | 3000.00 | NULL | NULL | | 1100.00 | NULL | NULL | | 1300.00 | NULL | NULL | +---------+---------+----------+
ISNULL (SQL Server)
SELECT ENAME,
COMM,
ISNULL(COMM,0),
ISNULL(COMM,0)+SAL
from emp;
IFNULL function (MySQL)
One of the solutions to the NULL value problem is IFNULL function. This function will return a second parameter (0) if the first parameter (comm) is null.
SELECT ENAME,
COMM,
IFNULL(COMM,0),
IFNULL(COMM,0)+SAL
from emp;
+----------+---------+----------------+--------------------+ | ENAME | COMM | IFNULL(COMM,0) | IFNULL(COMM,0)+SAL | +----------+---------+----------------+--------------------+ | BUSH | NULL | 0.00 | 5000.00 | | BLAIR | NULL | 0.00 | 2850.00 | | MERKEL | NULL | 0.00 | 2450.00 | | PUTIN | NULL | 0.00 | 2975.00 | | CHIRACK | 1400.00 | 1400.00 | 2650.00 | | BAROSSO | 300.00 | 300.00 | 1900.00 | | GATES | 0.00 | 0.00 | 1500.00 | | BUFFETT | NULL | 0.00 | 950.00 | | WALTON | 500.00 | 500.00 | 1750.00 | | TOOSK | NULL | 0.00 | 3000.00 | | THATCHER | NULL | 0.00 | 800.00 | | CARNEGIE | NULL | 0.00 | 3000.00 | | FORD | NULL | 0.00 | 1100.00 | | ELISON | NULL | 0.00 | 1300.00 | +----------+---------+----------------+--------------------+
NVL function (Oracle)
In Oracle we can use nvl() function instead of ifnull() function.
SELECT ENAME,
COMM,
nvl(COMM,0),
nvl(COMM,0)+SAL
from emp;
The results are identical to select with ifnull() functioning above.
COALESCE function (MySQL)
This function returns the first parameter which isn’t null.
SELECT ENAME,
SAL*12+COALESCE(COMM,0) 'Annual remuneration'
FROM EMP;
+----------+--------------------+ | ENAME | Annual emuneration | +----------+--------------------+ | BUSH | 60000.00 | | BLAIR | 34200.00 | | MERKEL | 29400.00 | | PUTIN | 35700.00 | | CHIRACK | 16400.00 | | BAROSSO | 19500.00 | | GATES | 18000.00 | | BUFFETT | 11400.00 | | WALTON | 15500.00 | | TOOSK | 36000.00 | | THATCHER | 9600.00 | | CARNEGIE | 36000.00 | | FORD | 13200.00 | | ELISON | 15600.00 | +----------+--------------------+
Sorting results
You can only sort results, not the data in table. To sort results use the ORDER BY keyword.
select ename from emp ORDER BY ename;
+----------+ | ename | +----------+ | BAROSSO | | BLAIR | | BUFFETT | | BUSH | | CARNEGIE | | CHIRACK | | ELISON | | FORD | | GATES | | MERKEL | | PUTIN | | THATCHER | | TOOSK | | WALTON | +----------+
You can reverse the order of sorting using the DESC (for DESCENDING) keyword.
select ename from emp order by ename DESC;
+----------+ | ename | +----------+ | WALTON | | TOOSK | | THATCHER | | PUTIN | | MERKEL | | GATES | | FORD | | ELISON | | CHIRACK | | CARNEGIE | | BUSH | | BUFFETT | | BLAIR | | BAROSSO | +----------+
To sort the multiple columns use their names separated by commas.
select job, ename
from emp
order by JOB ASC,
ENAME DESC;
+-----------+----------+ | job | ename | +-----------+----------+ | ANALYST | TOOSK | | ANALYST | CARNEGIE | | CLERK | THATCHER | | CLERK | FORD | | CLERK | ELISON | | CLERK | BUFFETT | | MANAGER | PUTIN | | MANAGER | MERKEL | | MANAGER | BLAIR | | PRESIDENT | BUSH | | SALESMAN | WALTON | | SALESMAN | GATES | | SALESMAN | CHIRACK | | SALESMAN | BAROSSO | +-----------+----------+
The ASC keyword means ASCENDING order and it is default.
WHERE clause
The WHERE clause allows you to specify which rows will be selected. If the expression is true, the row will be returned, otherwise the row will not show up.
select ename,
sal,
sal > 1500
from emp;
+----------+---------+------------+ | ename | sal | sal > 1500 | +----------+---------+------------+ | BUSH | 5000.00 | 1 | | BLAIR | 2850.00 | 1 | | MERKEL | 2450.00 | 1 | | PUTIN | 2975.00 | 1 | | CHIRACK | 1250.00 | 0 | | BAROSSO | 1600.00 | 1 | | GATES | 1500.00 | 0 | | BUFFETT | 950.00 | 0 | | WALTON | 1250.00 | 0 | | TOOSK | 3000.00 | 1 | | THATCHER | 800.00 | 0 | | CARNEGIE | 3000.00 | 1 | | FORD | 1100.00 | 0 | | ELISON | 1300.00 | 0 | +----------+---------+------------+
(SQL Server) Incorrect syntax near ‘>’
In this example the third column (sal > 1500) returns a Boolean expression (1 stands for true, 0 for false). If we use the WHERE clause only those rows will be selected where the SAL > 1500 expression is true.
select ename,
sal,
sal > 1500
from emp
WHERE SAL > 1500;
+----------+---------+------------+ | ename | sal | sal > 1500 | +----------+---------+------------+ | BUSH | 5000.00 | 1 | | BLAIR | 2850.00 | 1 | | MERKEL | 2450.00 | 1 | | PUTIN | 2975.00 | 1 | | BAROSSO | 1600.00 | 1 | | TOOSK | 3000.00 | 1 | | CARNEGIE | 3000.00 | 1 | +----------+---------+------------+
This query selects employees earning more than 1500.
WHERE and operators
There are different kinds of operators:
- Arithmetic operators (+, -, *, /, %, MOD, DIV)
- Relational operators (>, >=, =, !=)
- Logical operators (AND, OR, XOR, NOT)
- SQL specific operators (IS NULL, BETWEEN, IN and LIKE)
You can use them almost anywhere in the query.
Relational operators
Select only managers.
select ename, job from emp where job='MANAGER';
+--------+---------+ | ename | job | +--------+---------+ | BLAIR | MANAGER | | MERKEL | MANAGER | | PUTIN | MANAGER | +--------+---------+
Select people hired before or on 1st of April 1981.
select ename, job from emp where hiredate <= '1981-04-01';
+----------+----------+ | ename | job | +----------+----------+ | BAROSSO | SALESMAN | | WALTON | SALESMAN | | THATCHER | CLERK | +----------+----------+
Select all except clerks.
select ename, job from emp where job <> 'CLERK';
+----------+-----------+ | ename | job | +----------+-----------+ | BUSH | PRESIDENT | | BLAIR | MANAGER | | MERKEL | MANAGER | | PUTIN | MANAGER | | CHIRACK | SALESMAN | | BAROSSO | SALESMAN | | GATES | SALESMAN | | WALTON | SALESMAN | | TOOSK | ANALYST | | CARNEGIE | ANALYST | +----------+-----------+
BETWEEN ... AND ... operator
To select people earning from 1000 to 2000 (inclusive) you can type:
select ename, sal from emp where sal BETWEEN 1000 AND 2000;
+---------+---------+ | ename | sal | +---------+---------+ | CHIRACK | 1250.00 | | BAROSSO | 1600.00 | | GATES | 1500.00 | | WALTON | 1250.00 | | FORD | 1100.00 | | ELISON | 1300.00 | +---------+---------+
IN operator
The IN operator checks if the value is IN a set.
Select people working in department no 10 or 20.
select ename, deptno from emp where deptno IN (10,20);
+----------+--------+ | ename | deptno | +----------+--------+ | BUSH | 10 | | MERKEL | 10 | | PUTIN | 20 | | TOOSK | 20 | | THATCHER | 20 | | CARNEGIE | 20 | | FORD | 20 | | ELISON | 10 | +----------+--------+
Select managers or clerks.
select ename, job
from emp
where JOB IN ('CLERK','MANAGER');
+----------+---------+ | ename | job | +----------+---------+ | BLAIR | MANAGER | | MERKEL | MANAGER | | PUTIN | MANAGER | | BUFFETT | CLERK | | THATCHER | CLERK | | FORD | CLERK | | ELISON | CLERK | +----------+---------+
LIKE operator
The LIKE operator checks whether the values match the pattern.
You can use two wildcard characters in the pattern:
% (percent character) matches any number of characters, even an empty string.
_ (underscore) matches exactly one character
Select people with names starting with “B”.
select ename from emp where ename LIKE 'B%';
+---------+ | ename | +---------+ | BUSH | | BLAIR | | BAROSSO | | BUFFETT | +---------+
Select people with second letter ‘a’.
select ename from emp where ename LIKE '_A%';
+----------+ | ename | +----------+ | BAROSSO | | GATES | | WALTON | | CARNEGIE | +----------+
To find % and _ characters use the ESCAPE character: \% and \_
select 'New_York' LIKE '%\_York';
+---------------------------+ | 'New_York' LIKE '%\_York' | +---------------------------+ | 1 | +---------------------------+
IS NULL Operator
As we said previously, if you use the NULL value with almost every expression you obtain NULL value.
select ename,
comm,
comm=NULL
from emp;
+----------+---------+-----------+ | ename | comm | comm=NULL | +----------+---------+-----------+ | BUSH | NULL | NULL | | BLAIR | NULL | NULL | | MERKEL | NULL | NULL | | PUTIN | NULL | NULL | | CHIRACK | 1400.00 | NULL | | BAROSSO | 300.00 | NULL | | GATES | 0.00 | NULL | | BUFFETT | NULL | NULL | | WALTON | 500.00 | NULL | | TOOSK | NULL | NULL | | THATCHER | NULL | NULL | | CARNEGIE | NULL | NULL | | FORD | NULL | NULL | | ELISON | NULL | NULL | +----------+---------+-----------+
The NULL value is implicitly converted to FALSE, therefore (AnyThingEvenNull=NULL) expression always returns FALSE. To find rows which contain the NULL value you have to use the IS NULL operator.
select ename,
comm,
comm IS NULL
from emp;
+----------+---------+--------------+ | ename | comm | comm IS NULL | +----------+---------+--------------+ | BUSH | NULL | 1 | | BLAIR | NULL | 1 | | MERKEL | NULL | 1 | | PUTIN | NULL | 1 | | CHIRACK | 1400.00 | 0 | | BAROSSO | 300.00 | 0 | | GATES | 0.00 | 0 | | BUFFETT | NULL | 1 | | WALTON | 500.00 | 0 | | TOOSK | NULL | 1 | | THATCHER | NULL | 1 | | CARNEGIE | NULL | 1 | | FORD | NULL | 1 | | ELISON | NULL | 1 | +----------+---------+--------------+
select ename, comm from emp where comm IS NULL;
+----------+------+ | ename | comm | +----------+------+ | BUSH | NULL | | BLAIR | NULL | | MERKEL | NULL | | PUTIN | NULL | | BUFFETT | NULL | | TOOSK | NULL | | THATCHER | NULL | | CARNEGIE | NULL | | FORD | NULL | | ELISON | NULL | +----------+------+
Compound conditions
You may use logical operators to build compound conditions.
- AND or && (conjunction)
- OR or || (disjunction)
- XOR (exclusive disjunction) (MySQL)
- NOT or ! (negation)
In SQL language, all logical operators evaluate to TRUE (value 1), FALSE (value 0), or NULL (UNKNOWN).
Logical NOT, &&
Evaluates to TRUE if the operand is FALSE or zero, to FALSE if the operand is TRUE or non-zero, and NOT NULL returns NULL.
Logical AND, &&
Logical AND. Evaluates to:
- TRUE if all operands are TRUE, non-zero and not NULL,
- FALSE if one or more operands are FALSE or 0,
Otherwise NULL is returned.
Logical OR, ||
When both operands are non-NULL, the result is:
- TRUE if any operand is TRUE or non-zero,
- FALSE otherwise (i.e. all are FALSE, 0)
With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL.
SELECT NULL OR TRUE, NULL OR FALSE, NULL OR NULL;
+--------------+---------------+--------------+ | NULL OR TRUE | NULL OR FALSE | NULL OR NULL | +--------------+---------------+--------------+ | 1 | NULL | NULL | +--------------+---------------+--------------+
Logical XOR
Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, the result is 1 if an odd number of operands is non-zero, otherwise 0 is returned.
SELECT TRUE XOR TRUE, TRUE XOR FALSE, FALSE XOR TRUE, FALSE XOR FALSE;
+---------------+----------------+----------------+-----------------+ | TRUE XOR TRUE | TRUE XOR FALSE | FALSE XOR TRUE | FALSE XOR FALSE | +---------------+----------------+----------------+-----------------+ | 0 | 1 | 1 | 0 | +---------------+----------------+----------------+-----------------+
(a XOR b) is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b). Another way: (a or b) and not (a and b).
Examples
Select employees with commission.
SELECT ENAME, JOB, COMM FROM EMP WHERE COMM IS NOT NULL;
+---------+----------+---------+ | ENAME | JOB | COMM | +---------+----------+---------+ | CHIRACK | SALESMAN | 1400.00 | | BAROSSO | SALESMAN | 300.00 | | GATES | SALESMAN | 0.00 | | WALTON | SALESMAN | 500.00 | +---------+----------+---------+
Let’s find managers earning more than 2000.
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL>2000 AND JOB='MANAGER';
+--------+---------+---------+ | ENAME | JOB | SAL | +--------+---------+---------+ | BLAIR | MANAGER | 2850.00 | | MERKEL | MANAGER | 2450.00 | | PUTIN | MANAGER | 2975.00 | +--------+---------+---------+
Operators priority
Let’s find managers earning more than 1000 and all clerks (regardless of earnings).
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL>1000 AND JOB='MANAGER' OR JOB='CLERK';
+----------+---------+---------+ | ENAME | JOB | SAL | +----------+---------+---------+ | BLAIR | MANAGER | 2850.00 | | MERKEL | MANAGER | 2450.00 | | PUTIN | MANAGER | 2975.00 | | BUFFETT | CLERK | 950.00 | | THATCHER | CLERK | 800.00 | | FORD | CLERK | 1100.00 | | ELISON | CLERK | 1300.00 | +----------+---------+---------+
Select managers and clerks earning more than 1000.
SELECT ENAME, JOB, SAL FROM EMP WHERE SAL>1000 AND (JOB='MANAGER' OR JOB='CLERK');
+--------+---------+---------+ | ENAME | JOB | SAL | +--------+---------+---------+ | BLAIR | MANAGER | 2850.00 | | MERKEL | MANAGER | 2450.00 | | PUTIN | MANAGER | 2975.00 | | FORD | CLERK | 1100.00 | | ELISON | CLERK | 1300.00 | +--------+---------+---------+
Operator precedence is shown in the following list, from the lowest precedence to the highest. Operators that are shown together on a line have the same precedence.
- OR, XOR
- AND
- NOT
- BETWEEN, CASE, WHEN, THEN, ELSE
- =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
- -, +
- , /, DIV, %, MOD
- - (unary minus),
- !
Exercises
1. Exercise
Select all rows from SALGRADE table.
+-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
2. Exercise
Select all rows from EMP table (don't use asterisk)
+------+----------+-----------+------+------------+---------+---------+--------+ | ID | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------+----------+-----------+------+------------+---------+---------+--------+ | 7839 | BUSH | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7698 | BLAIR | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | MERKEL | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7566 | PUTIN | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | CHIRACK | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7499 | BAROSSO | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7844 | GATES | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7900 | BUFFETT | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7521 | WALTON | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7902 | TOOSK | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7369 | THATCHER | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7788 | CARNEGIE | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 | | 7876 | FORD | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 | | 7934 | ELISON | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +------+----------+-----------+------+------------+---------+---------+--------+
3. Exercise
Select shown data about employees who earn between 1000 and 2000. Use compound conditions and SQL operator.
+---------+--------+---------+ | ename | deptno | sal | +---------+--------+---------+ | CHIRACK | 30 | 1250.00 | | BAROSSO | 30 | 1600.00 | | GATES | 30 | 1500.00 | | WALTON | 30 | 1250.00 | | FORD | 20 | 1100.00 | | ELISON | 10 | 1300.00 | +---------+--------+---------+
4. Exercise
Select number and name of departments. Order them by department name.
+--------+------------+ | deptno | dname | +--------+------------+ | 10 | ACCOUNTING | | 40 | OPERATIONS | | 20 | RESEARCH | | 30 | SALES | +--------+------------+
5. Exercise
Select all distinct jobs.
+-------------------------+ | All jobs in our company | +-------------------------+ | SALESMAN | | CLERK | | PRESIDENT | | MANAGER | | ANALYST | +-------------------------+
6. Exercise
Select the data shown below about employees working in departments 10 and 20. Sort the results by their names in descending order.
+----------+-----------+---------+--------+ | ename | job | sal | deptno | +----------+-----------+---------+--------+ | TOOSK | ANALYST | 3000.00 | 20 | | THATCHER | CLERK | 800.00 | 20 | | PUTIN | MANAGER | 2975.00 | 20 | | MERKEL | MANAGER | 2450.00 | 10 | | FORD | CLERK | 1100.00 | 20 | | ELISON | CLERK | 1300.00 | 10 | | CARNEGIE | ANALYST | 3000.00 | 20 | | BUSH | PRESIDENT | 5000.00 | 10 | +----------+-----------+---------+--------+
7. Exercise
Select clerks working in department no 20.
+----------+-------+--------+ | ename | job | deptno | +----------+-------+--------+ | THATCHER | CLERK | 20 | | FORD | CLERK | 20 | +----------+-------+--------+
8. Exercise
Select employees who have a boss. Try to solve it in 3 different ways.
+----------+------+ | ename | mgr | +----------+------+ | BLAIR | 7839 | | MERKEL | 7839 | | PUTIN | 7839 | | CHIRACK | 7698 | | BAROSSO | 7698 | | GATES | 7698 | | BUFFETT | 7698 | | WALTON | 7698 | | TOOSK | 7566 | | THATCHER | 7902 | | CARNEGIE | 7566 | | FORD | 7788 | | ELISON | 7782 | +----------+------+
9. Exercise
Select manager’s annual remuneration.
+--------+---------------------+ | ename | Annual remuneration | +--------+---------------------+ | BLAIR | 34200.00 | | MERKEL | 29400.00 | | PUTIN | 35700.00 | +--------+---------------------+
10. Exercise
Select monthly remuneration (salary and commission).
+----------+----------------------+ | ename | Monthly remuneration | +----------+----------------------+ | BUSH | 5000.00 | | BLAIR | 2850.00 | | MERKEL | 2450.00 | | PUTIN | 2975.00 | | CHIRACK | 2650.00 | | BAROSSO | 1900.00 | | GATES | 1500.00 | | BUFFETT | 950.00 | | WALTON | 1750.00 | | TOOSK | 3000.00 | | THATCHER | 800.00 | | CARNEGIE | 3000.00 | | FORD | 1100.00 | | ELISON | 1300.00 | +----------+----------------------+
11. Exercise*
Select workers hired in 1982.
+----------+------------+ | ename | hiredate | +----------+------------+ | CARNEGIE | 1982-12-09 | | ELISON | 1982-01-23 | +----------+------------+
12. Exercise
Select employees whose commission is greater than their salary.
+---------+---------+---------+ | ename | sal | comm | +---------+---------+---------+ | CHIRACK | 1250.00 | 1400.00 | +---------+---------+---------+
13. Exercise
Write a select statement which returns output below (use concatenation). In MSSQL server you need to convert department number using convert(varchar,deptno).
+----------------------------------------------------+ | Employees information | +----------------------------------------------------+ | BUSH is PRESIDENT and works in department no 10 | | BLAIR is MANAGER and works in department no 30 | | MERKEL is MANAGER and works in department no 10 | | PUTIN is MANAGER and works in department no 20 | | CHIRACK is SALESMAN and works in department no 30 | | BAROSSO is SALESMAN and works in department no 30 | | GATES is SALESMAN and works in department no 30 | | BUFFETT is CLERK and works in department no 30 | | WALTON is SALESMAN and works in department no 30 | | TOOSK is ANALYST and works in department no 20 | | THATCHER is CLERK and works in department no 20 | | CARNEGIE is ANALYST and works in department no 20 | | FORD is CLERK and works in department no 20 | | ELISON is CLERK and works in department no 10 | +----------------------------------------------------+
14. Exercise
Find employees with second letter “L” and third “A”.
+-------+ | ename | +-------+ | BLAIR | +-------+
15. Exercise
Find employees whose name contains T and ends with N.
+--------+ | ename | +--------+ | PUTIN | | WALTON | +--------+
16. Exercise
Find employees who either work as managers or work in department no 10, but not both.
+--------+-----------+--------+ | ename | job | deptno | +--------+-----------+--------+ | BUSH | PRESIDENT | 10 | | BLAIR | MANAGER | 30 | | PUTIN | MANAGER | 20 | | ELISON | CLERK | 10 | +--------+-----------+--------+
Dealing with date and time in MySQL*
Date Data Types
DATE
The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format.
DATETIME
It is a time and date combination.
The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.
TIME
The range is '-838:59:59' to '838:59:59'.
MySQL displays TIME values in 'HH:MM:SS' format.
YEAR[(2|4)]
A year in two-digit or four-digit (default) format.
In four-digit format, the allowable values are 1901 to 2155, and 0000.
In two-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069. MySQL displays YEAR values in YYYY format.
MySQL allows you to assign values to DATE, DATETIME, TIME and YEAR columns using either strings or numbers.
Date formatting
DATE_FORMAT function
You can choose the format of the date using the DATE_FORMAT function.
SELECT ENAME,
DATE_FORMAT(HIREDATE,'%D OF %M ')
FROM EMP;
+----------+-----------------------------------+ | ENAME | DATE_FORMAT(HIREDATE,'%D OF %M ') | +----------+-----------------------------------+ | BUSH | 17th OF November | | BLAIR | 1st OF May | | MERKEL | 9th OF June | | PUTIN | 2nd OF April | | CHIRACK | 28th OF September | | BAROSSO | 20th OF February | | GATES | 8th OF September | | BUFFETT | 3rd OF December | | WALTON | 22nd OF February | | TOOSK | 3rd OF December | | THATCHER | 17th OF December | | CARNEGIE | 9th OF December | | FORD | 12th OF January | | ELISON | 23rd OF January | +----------+-----------------------------------+
CURRENT_DATE() and CURRENT_TIME() functions.
Shows the current date and time.
select current_time(),
current_date();
+--------------+--------------+ | current_time | current_date | +--------------+--------------+ | 11:05:16 | 2006-05-13 | +--------------+--------------+
Useful Date Function
select year(curdate()), month(curdate()), day(curdate());
+-----------------+------------------+----------------+ | year(curdate()) | month(curdate()) | day(curdate()) | +-----------------+------------------+----------------+ | 2006 | 5 | 13 | +-----------------+------------------+----------------+
DATE_ADD() and DATE_SUB() functions.
You can add or deduct values in date.
select hiredate,
date_add(hiredate, interval 1 day) as hire_add from emp;
+------------+------------+ | hiredate | hire_add | +------------+------------+ | 1980-12-17 | 1980-12-18 | +------------+------------+ | 1981-02-20 | 1981-02-21 | +------------+------------+ | 1981-02-22 | 1981-02-23 | +------------+------------+ | 1981-04-02 | 1981-04-03 | +------------+------------+ | 1981-09-28 | 1981-09-29 | +------------+------------+ | 1981-05-01 | 1981-05-02 | +------------+------------+ | 1981-06-09 | 1981-06-10 | +------------+------------+ | 1982-12-09 | 1982-12-10 | +------------+------------+ | 1981-11-17 | 1981-11-18 | +------------+------------+ | 1981-09-08 | 1981-09-09 | +------------+------------+ | 1983-01-12 | 1983-01-13 | +------------+------------+ | 1981-12-03 | 1981-12-04 | +------------+------------+ | 1981-12-03 | 1981-12-04 | +------------+------------+ | 1982-01-23 | 1982-01-24 | +------------+------------+
select hiredate, date_sub(hiredate, interval 3 month) as hire_sub from emp
+------------+------------+ | hiredate | hire_sub | +------------+------------+ | 1980-12-17 | 1980-09-17 | +------------+------------+ | 1981-02-20 | 1980-11-20 | +------------+------------+ | 1981-02-22 | 1980-11-22 | +------------+------------+ | 1981-04-02 | 1981-01-02 | +------------+------------+ | 1981-09-28 | 1981-06-28 | +------------+------------+ | 1981-05-01 | 1981-02-01 | +------------+------------+ | 1981-06-09 | 1981-03-09 | +------------+------------+ | 1982-12-09 | 1982-09-09 | +------------+------------+ | 1981-11-17 | 1981-08-17 | +------------+------------+ | 1981-09-08 | 1981-06-08 | +------------+------------+ | 1983-01-12 | 1982-10-12 | +------------+------------+ | 1981-12-03 | 1981-09-03 | +------------+------------+ | 1981-12-03 | 1981-09-03 | +------------+------------+ | 1982-01-23 | 1981-10-23 | +------------+------------+
Related docs:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
Dealing with date and time in Oracle*
sysdate function
You can use sysdate function to show the current date.
SELECT SYSDATE FROM DUAL;
SYSDATE ------------------- 2004-08-09 01:44:50
The format of the date depends on:
- Client settings
- Session settings
- Database settings
to_char function
If we don’t want to depnd on settings, we can convert date format to character type.
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DD') ----------------------------- 2004-08-09
SELECT TO_CHAR(SYSDATE,'YYYY-MON-DD') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MON-DD') ------------------------------ 2004-aug-09
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY:MI:HH:SS') FROM DUAL;
TO_CHAR(SYSDATE,'DD-MON-YYYY:M ------------------------------ 09-aug-2004:31:08:27
Default date language and format
In some client application (e.g. SQL*Plus) a query, who returns a date may looks different depend on NLS_DATE_FORMAT and NSL_DATE_LANGUAGE parameters.
SQL> select hiredate from emp where ename='KING';
HIREDATE -------- 81/11/17
SQL> alter session set nls_date_format='DD-mon-YYYY:mi:hh:ss'; Session altered.
SQL> select hiredate from emp where ename='KING';
HIREDATE -------------------- 17-nov-1981:00:12:00
By default, those parameters uses NLS_TERRITORY parameter.
Changing default language
SQL> select hiredate from emp where ename='KING';
HIREDATE -------------------- 17-nov-1981:00:12:00
SQL> alter session set NLS_DATE_LANGUAGE='polish'; Session altered.
SQL> select hiredate from emp where ename='KING';
HIREDATE -------------------- 17-lis-1981:00:12:00
The month name nov (november) has changed to lis (listopad).
If you do not want to display your date in different way depend on language settings you can use to_char function.
If you do not specify format (second) parameter in to_char, the format of the date will be read from NLS_DATE_FORMAT variable.
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH:MI'; Session altered
SELECT TO_CHAR(SYSDATE) FROM DUAL; TO_CHAR(SYSDATE) ----------------- 09-aug-2004 08:49
to_date function
This function converts a string (char) to date type. It is very useful when you want to compare dates. For example if you want to select employees hired after 30th of June 1981:
SELECT * FROM EMP WHERE HIREDATE > TO_DATE('30-06-1981','DD-MM-YYYY');
ID ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ---------- --------- ---------- ---------- ---------- 7839 BUSH PRESIDENT 17-NOV-81 5000 10 7654 CHIRACK SALESMAN 7698 28-SEP-81 1250 1400 30 7844 GATES SALESMAN 7698 08-SEP-81 1500 0 30 7900 BUFFETT CLERK 7698 03-DEC-81 950 30 7902 TOOSK ANALYST 7566 03-DEC-81 3000 20 7788 CARNEGIE ANALYST 7566 09-DEC-82 3000 20 7876 FORD CLERK 7788 12-JAN-83 1100 20 7934 ELISON CLERK 7782 23-JAN-82 1300 10
Related docs: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm
extract() function
SELECT EXTRACT(YEAR FROM sysdate) -- year, month, day, etc FROM DUAL;
Dealing with date in SQL Server*
Let's start with the easiest one first. You can use the GETDATE() function to return the date and time of the server. For example,
SELECT Today=GETDATE() Today --------------------------- 2000-07-31 20:01:19.957
which is actually both the date and time.
You can return just the date by using the formatting feature of the CONVERT function.
SELECT Today=convert(varchar, GETDATE(), 101) Today ------------------------------ 07/31/2000
This converts the result of GETDATE to a VARCHAR and sets the style to 101. This happens to be the code for MM/DD/YYYY. There are numerous styles to choose from.([1])
Inserting a date into a table using SQL shouldn't be very difficult. Converting from CHAR and VARCHAR to DATETIME is an implicit conversion that SQL Server should handle properly. If you are having problems I would make sure you have the date in one of the formats listed in the documentation for the CONVERT statement explicitly convert it.
Insert MyTable(DateField)
Values ( convert (datetime, '3/15/2004'))
Date functions examples
SELECT DATENAME(month,'2007-10-30 12:15:32.1234567 +05:10');
SELECT
year('2007-10-30 12:15:32.1234567 +05:10') - year('2007-10-30 12:15:32.1234567 +05:10');
SELECT month('2007-10-30 12:15:32.1234567 +05:10');
SELECT day('2007-10-30 12:15:32.1234567 +05:10');
SELECT DATEDIFF( year , '2007-10-30', '2010-10-30' );
SELECT DATEADD(day,1,'2007-10-20');
SELECT convert(varchar, getdate(), 101);
PostgresSQL
Exercises
17. Exercise
Compute job seniority (in MSSQL Server use YEAR function).
+----------+-----------+ | ename | Seniority | +----------+-----------+ | BUSH | 25 | | BLAIR | 25 | | MERKEL | 25 | | PUTIN | 25 | | CHIRACK | 25 | | BAROSSO | 25 | | GATES | 25 | | BUFFETT | 25 | | WALTON | 25 | | TOOSK | 25 | | THATCHER | 26 | | CARNEGIE | 24 | | FORD | 23 | | ELISON | 24 | +----------+-----------+
18. *Exercise
Compute job seniority (in MSSQL Server use DATEDIFF function ).
+----------+-----------+ | ename | Seniority | +----------+-----------+ | BUSH | 25.14 | | BLAIR | 25.69 | | MERKEL | 25.59 | | PUTIN | 25.78 | | CHIRACK | 25.28 | | BAROSSO | 25.89 | | GATES | 25.33 | | BUFFETT | 25.09 | | WALTON | 25.88 | | TOOSK | 25.09 | | THATCHER | 26.07 | | CARNEGIE | 24.06 | | FORD | 23.97 | | ELISON | 24.95 | +----------+-----------+
19. Exercise
Select employees hired before 1st of April 1981.
+----------+------------+ | ename | hiredate | +----------+------------+ | BAROSSO | 1981-02-20 | | WALTON | 1981-02-22 | | THATCHER | 1980-12-17 | +----------+------------+
20. Exercise
Select employees hired in January.
+--------+------------+ | ename | hiredate | +--------+------------+ | FORD | 1983-01-12 | | ELISON | 1982-01-23 | +--------+------------+
21. Exercise
Select the information below.
Related docs:
- Mysql https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
- Oracle https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Format-Models.html
- MSSQL Server https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql
- PostgresSQL: https://www.postgresql.org/docs/current/functions-datetime.html
+----------+--------------------------------------------+ | ename | When hired? | +----------+--------------------------------------------+ | BUSH | was hired 17th of November 1981 Tuesday | | BLAIR | was hired 1st of May 1981 Friday | | MERKEL | was hired 9th of June 1981 Tuesday | | PUTIN | was hired 2nd of April 1981 Thursday | | CHIRACK | was hired 28th of September 1981 Monday | | BAROSSO | was hired 20th of February 1981 Friday | | GATES | was hired 8th of September 1981 Tuesday | | BUFFETT | was hired 3rd of December 1981 Thursday | | WALTON | was hired 22nd of February 1981 Sunday | | TOOSK | was hired 3rd of December 1981 Thursday | | THATCHER | was hired 17th of December 1980 Wednesday | | CARNEGIE | was hired 9th of December 1982 Thursday | | FORD | was hired 12th of January 1983 Wednesday | | ELISON | was hired 23rd of January 1982 Saturday | +----------+--------------------------------------------+
22. Exercise*
Show all employees hired 50 days before (inclusive) to 50 days after (inclusive) 28th February 1982 (in MSSQL Server use datediff() function)
+--------+------------+ | ename | hiredate | +--------+------------+ | ELISON | 1982-01-23 | +--------+------------+
Aggregate Functions (GROUP BY)
Functions such as IFNULL, YEAR are single row functions. Their arguments may be taken only from one row.
In contrast, aggregate functions can compute results using many rows.
Find the average salary in the whole company.
select AVG(sal) from emp;
+-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+
If not explicitly specified, the group contains all the rows selected by the query.
Groups
Groups contain all rows which have the same values in a column or columns.
select ename, job, sal from emp ORDER BY job;
+----------+-----------+---------+ | ename | job | sal | +----------+-----------+---------+ | CARNEGIE | ANALYST | 3000.00 | | TOOSK | ANALYST | 3000.00 | | ELISON | CLERK | 1300.00 | | FORD | CLERK | 1100.00 | | THATCHER | CLERK | 800.00 | | BUFFETT | CLERK | 950.00 | | PUTIN | MANAGER | 2975.00 | | MERKEL | MANAGER | 2450.00 | | BLAIR | MANAGER | 2850.00 | | BUSH | PRESIDENT | 5000.00 | | GATES | SALESMAN | 1500.00 | | BAROSSO | SALESMAN | 1600.00 | | WALTON | SALESMAN | 1250.00 | | CHIRACK | SALESMAN | 1250.00 | +----------+-----------+---------+
There are five JOB groups above: ANALYST, CLERK, MANAGER, PRESIDENT and SALESMAN.
Example
select job, avg(sal), min(sal), max(sal), sum(sal), count(sal) from emp GROUP BY job;
+-----------+---------+----------+----------+----------+------------+ | job | avg(sal)| min(sal) | max(sal) | sum(sal) | count(sal) | +-----------+---------+----------+----------+----------+------------+ | ANALYST | 3000.000| 3000.00 | 3000.00 | 6000.00 | 2 | | CLERK | 1037.500| 800.00 | 1300.00 | 4150.00 | 4 | | MANAGER | 2758.333| 2450.00 | 2975.00 | 8275.00 | 3 | | PRESIDENT | 5000.000| 5000.00 | 5000.00 | 5000.00 | 1 | | SALESMAN | 1400.000| 1250.00 | 1600.00 | 5600.00 | 4 | +-----------+---------+----------+----------+----------+------------+
Some useful aggregate functions
- AVG([DISTINCT] expr)
- COUNT(expr)
- COUNT(DISTINCT expr,[expr...])
- GROUP_CONCAT(expr) -- not in Oracle db
- MIN([DISTINCT] expr), MAX([DISTINCT] expr)
- STD(expr) or STDDEV(expr)
- SUM([DISTINCT] expr)
- VARIANCE(expr)
AVG([DISTINCT] expr)
Returns the average value of expr.
The DISTINCT option can be used to return the average of the distinct values of expr.
AVG()
returns NULL if there were no matching rows.
COUNT(expr)
Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement.
COUNT() returns 0 if there were no matching rows.
COUNT(*) returns a count of the number of rows retrieved, whether or not they contain NULL values.
GROUP_CONCAT(expr)
This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The
full syntax is as follows:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
select job, group_concat(ename) from emp group by job;
+-----------+------------------------------+ | job | group_concat(ename) | +-----------+------------------------------+ | ANALYST | CARNEGIE,TOOSK | | CLERK | ELISON,FORD,THATCHER,BUFFETT | | MANAGER | PUTIN,MERKEL,BLAIR | | PRESIDENT | BUSH | | SALESMAN | GATES,BAROSSO,WALTON,CHIRACK | +-----------+------------------------------+
(SQL Server) 'group_concat' is not a recognized built-in function name.
MIN([DISTINCT] expr), MAX([DISTINCT] expr)
Returns the minimum or maximum value of expr.
MIN() and MAX() may take a string argument; in such cases they return the minimum or maximum string value.
STD(expr) or STDDEV(expr)
Returns the population standard deviation of expr.
SUM([DISTINCT] expr)
Returns the sum of expr.
If the return set has no rows, SUM() returns NULL
VARIANCE(expr) or VAR(expr)
Returns the population standard variance of expr.
*DISTINCT with aggregate functions. (MySQL)
Count how many employees work in a specific post.
select job,count(deptno) from emp group by job;
+-----------+---------------+ | job | count(deptno) | +-----------+---------------+ | ANALYST | 2 | | CLERK | 4 | | MANAGER | 3 | | PRESIDENT | 1 | | SALESMAN | 4 | +-----------+---------------+ 5 rows in set (0.01 sec)
Count in how many distinct departments the selected posts exist.
select job,count(distinct deptno) from emp group by job;
+-----------+------------------------+ | job | count(distinct deptno) | +-----------+------------------------+ | ANALYST | 1 | | CLERK | 3 | | MANAGER | 3 | | PRESIDENT | 1 | | SALESMAN | 1 | +-----------+------------------------+
To understand this problem better look at the following example.
select job,count(distinct deptno),group_concat(deptno) from emp group by job;
+-----------+------------------------+----------------------+ | job | count(distinct deptno) | group_concat(deptno) | +-----------+------------------------+----------------------+ | ANALYST | 1 | 20,20 | | CLERK | 3 | 10,20,20,30 | | MANAGER | 3 | 20,10,30 | | PRESIDENT | 1 | 10 | | SALESMAN | 1 | 30,30,30,30 | +-----------+------------------------+----------------------+
select job,deptno from emp order by job,deptno;
+-----------+--------+ | job | deptno | +-----------+--------+ | ANALYST | 20 | | ANALYST | 20 | | CLERK | 10 | | CLERK | 20 | | CLERK | 20 | | CLERK | 30 | | MANAGER | 10 | | MANAGER | 20 | | MANAGER | 30 | | PRESIDENT | 10 | | SALESMAN | 30 | | SALESMAN | 30 | | SALESMAN | 30 | | SALESMAN | 30 | +-----------+--------+
Aggregate function with WHERE and HAVING
You can use the WHERE clause with the GROUP BY clause.
select JOB, avg(sal) from emp
where job in ('MANAGER','CLERK')
GROUP BY JOB;
+---------+-------------+ | JOB | avg(sal) | +---------+-------------+ | CLERK | 1037.500000 | | MANAGER | 2758.333333 | +---------+-------------+
This query selects managers and clerks and then computes the average salary.
You cannot use the WHERE clause with the GROUP function.
select job, avg(sal)
from emp
WHERE avg(sal) > 2000
group by job ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
to use near 'WHERE avg(sal) > 2000' at line 1
To solve this problem you have to use the HAVING keyword.
select job, avg(sal) from emp group by job HAVING avg(sal) > 2000;
+-----------+-------------+ | job | avg(sal) | +-----------+-------------+ | ANALYST | 3000.000000 | | MANAGER | 2758.333333 | | PRESIDENT | 5000.000000 | +-----------+-------------+
This query computes averages in groups and AFTER that that finds the groups that have an average salary greater than 2000.
Sometimes you may use both. But remember that the WHERE clause selects rows BEFORE grouping (it works on rows) and HAVING selects GROUPS rather than rows, so it is computed AFTER grouping.
The syntax must show this dependency.
select job, max(sal) from emp WHERE job<>'MANAGER' group by job HAVING max(sal) >= 3000; +-----------+----------+ | job | max(sal) | +-----------+----------+ | ANALYST | 3000.00 | | PRESIDENT | 5000.00 | +-----------+----------+
HAVING or WHERE?
Sometimes you may solve a problem with both HAVING or WHERE.
How many employees work in department 10?
select count(*) from emp
WHERE deptno=10;
+----------+ | count(*) | +----------+ | 3 | +----------+
select count(*) from emp GROUP BY deptno HAVING deptno=10;
+----------+ | count(*) | +----------+ | 3 | +----------+
Of course both solutions are correct, but the first (with WHERE) is faster, because it does not have to group anything.
Aggregate functions and selected columns
If you use the aggregate function you should not select columns different from those in the GROUP BY clause.
select ENAME, count(*) from emp GROUP BY job;
+---------+----------+ | ename | count(*) | +---------+----------+ | TOOSK | 2 | | BUFFETT | 4 | | BLAIR | 3 | | BUSH | 1 | | CHIRACK | 4 | +---------+----------+
(SQL Server) Column ‘emp.ENAME’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Every GROUP may contain many ENAMEs, therefore the ENAME column is arbitrary.
Sometimes we cannot select a column without the GROUP BY clause.
select DEPTNO, count(*) from emp WHERE deptno=10; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
To solve this problem use the GROUP BY clause.
select deptno,count(*) from emp WHERE deptno=10 GROUP BY deptno;
+--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | +--------+----------+
Column aliases and HAVING, ORDER BY, GROUP BY
It is quite cumbersome to sort results by columns containing aggregate functions or other complex expressions.
select deptno, count(*) AS 'No of employees' from emp group by deptno ORDER BY COUNT(*);
+--------+-----------------+ | deptno | No of employees | +--------+-----------------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+-----------------+
You can use alias in HAVING, ORDER BY and GROUP BY clause to refer to columns.
select deptno, count(*) AS EmpCount from emp group by deptno ORDER BY EmpCount;
+--------+----------+ | deptno | EmpCount | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+----------+
It is useful especially with complex formulas.
select deptno, sal+ifnull(comm,0) AS Remun from emp ORDER BY Remun;
+--------+---------+ | deptno | Remun | +--------+---------+ | 20 | 800.00 | | 30 | 950.00 | | 20 | 1100.00 | ......
Let’s find out how many people were hired in 1981 and 1983.
select count(*), YEAR(hiredate) AS HY from emp GROUP BY HY HAVING HY IN (1981,1983) ;
+----------+------+ | count(*) | HY | +----------+------+ | 10 | 1981 | | 1 | 1983 | +----------+------+
(SQL Server) Invalid column name ‘HY’
Standard SQL doesn't allow you to resfer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal:
select ename, sal*12 AnnualSal from emp where AnnualSal > 10000; ERROR 1054 (42S22): Unknown column 'AnnualSal' in 'where clause'
Clauses order
You have to remember that you cannot change the order of the clauses.
SELECT [ALL | DISTINCT ] select_expr, ... FROM table1, table2, ... WHERE where_condition GROUP BY expr HAVING where_condition ORDER BY [ASC | DESC], …]
Exercises
23. Exercise
Find the minimal, maximal and average salaries in the whole company.
+---------+---------+-------------+ | Minimal | Maximal | Avarage | +---------+---------+-------------+ | 800.00 | 5000.00 | 2073.214286 | +---------+---------+-------------+
24. Exercise
Find the difference between maximal and minimal salary.
+------------+ | Difference | +------------+ | 4200.00 | +------------+
25. Exercise
Find the average salary for every post.
+-----------+-------------+ | job | Average | +-----------+-------------+ | ANALYST | 3000.000000 | | CLERK | 1037.500000 | | MANAGER | 2758.333333 | | PRESIDENT | 5000.000000 | | SALESMAN | 1400.000000 | +-----------+-------------+
26. Exercise
How many managers work for the company?
+---------+-----------+ | job | How many? | +---------+-----------+ | MANAGER | 3 | +---------+-----------+
27. Exercise
Find the average annual salaries in departments.
+--------+-----------------+ | deptno | Annual Salaries | +--------+-----------------+ | 10 | 35000.000000 | | 20 | 26100.000000 | | 30 | 18800.000000 | +--------+-----------------+
28. Exercise
Find departments with more than 3 workers.
+--------+----------+ | deptno | count(*) | +--------+----------+ | 20 | 5 | | 30 | 6 | +--------+----------+
29. *Exercise
Check that every id is unique (use an aggregate function).
30. *Exercise
For each of the managers find a minimum salary of their subordinates.
+------+----------+ | mgr | min(sal) | +------+----------+ | 7566 | 3000.00 | | 7698 | 950.00 | | 7782 | 1300.00 | | 7788 | 1100.00 | | 7839 | 2450.00 | | 7902 | 800.00 | +------+----------+
Selecting data from many tables
To select data from many tables you have to JOIN them. You can join tables in many ways, but the result should be the same.
Scenario
We want to select the name of an employee (ENAME column in EMP table) and the department name (DNAME column in DEPT table) in which the employee works. This information is stored in two different tables (EMP and DEPT).
Cartesian product (direct product)
If you select data from many tables without joining them, you obtain a Cartesian product.
The Cartesian product is the set of all possible pairs whose first component is from the first table and whose second component is from the second table.
select emp.ename, dept.dname from emp,dept;
+----------+------------+ | ename | dname | +----------+------------+ | BUSH | ACCOUNTING | | BUSH | RESEARCH | | BUSH | SALES | | BUSH | OPERATIONS | | BLAIR | ACCOUNTING | | BLAIR | RESEARCH | | BLAIR | SALES | | BLAIR | OPERATIONS | | MERKEL | ACCOUNTING | | MERKEL | RESEARCH | | MERKEL | SALES | | MERKEL | OPERATIONS | .... | ELISON | ACCOUNTING | | ELISON | RESEARCH | | ELISON | SALES | | ELISON | OPERATIONS | +----------+------------+
To choose the right pairs you have to join the tables using special criteria. A criterion or set of criteria constitute a relation.
In the example, the deptno from the first table (emp) should be equal to the deptno from the second table (dept).
You have to use a table prefix (e.g. emp.detpno and dept.deptno) to avoid ambiguity. If a column name occurs only in one table you don’t need to use a table prefix.
select ename, EMP.DEPTNO, DEPT.DEPTNO, dname from emp, dept;
+----------+--------+--------+------------+ | ename | deptno | deptno | dname | +----------+--------+--------+------------+ | BUSH | 10 | 10 | ACCOUNTING | | BUSH | 10 | 20 | RESEARCH | | BUSH | 10 | 30 | SALES | | BUSH | 10 | 40 | OPERATIONS | | BLAIR | 30 | 10 | ACCOUNTING | | BLAIR | 30 | 20 | RESEARCH | | BLAIR | 30 | 30 | SALES | | BLAIR | 30 | 40 | OPERATIONS | | MERKEL | 10 | 10 | ACCOUNTING | | MERKEL | 10 | 20 | RESEARCH | | MERKEL | 10 | 30 | SALES | | MERKEL | 10 | 40 | OPERATIONS | | PUTIN | 20 | 10 | ACCOUNTING | | PUTIN | 20 | 20 | RESEARCH | | PUTIN | 20 | 30 | SALES | ...
In this example only bold rows should be selected, because they meet relation criteria.
To do so, you can use the WHERE clause as well.
select ename, emp.deptno, dept.deptno, dname from emp,dept WHERE emp.deptno=dept.deptno;
+----------+--------+--------+------------+ | ename | deptno | deptno | dname | +----------+--------+--------+------------+ | BUSH | 10 | 10 | ACCOUNTING | | BLAIR | 30 | 30 | SALES | | MERKEL | 10 | 10 | ACCOUNTING | | PUTIN | 20 | 20 | RESEARCH | | CHIRACK | 30 | 30 | SALES | | BAROSSO | 30 | 30 | SALES | | GATES | 30 | 30 | SALES | | BUFFETT | 30 | 30 | SALES | | WALTON | 30 | 30 | SALES | | TOOSK | 20 | 20 | RESEARCH | | THATCHER | 20 | 20 | RESEARCH | | CARNEGIE | 20 | 20 | RESEARCH | | FORD | 20 | 20 | RESEARCH | | ELISON | 10 | 10 | ACCOUNTING | +----------+--------+--------+------------+
There is no need to select columns used in the WHERE clause.
select ename, dname from emp,dept where emp.deptno=dept.deptno;
+----------+------------+ | ename | dname | +----------+------------+ | BUSH | ACCOUNTING | | BLAIR | SALES | | MERKEL | ACCOUNTING | | PUTIN | RESEARCH | | CHIRACK | SALES | | BAROSSO | SALES | | GATES | SALES | | BUFFETT | SALES | | WALTON | SALES | | TOOSK | RESEARCH | | THATCHER | RESEARCH | | CARNEGIE | RESEARCH | | FORD | RESEARCH | | ELISON | ACCOUNTING | +----------+------------+
We may say that DEPTNO is a key, which JOINS two tables.
The EMP.DEPTNO=DEPT.DEPTNO condition is a JOIN CONDITON.
EQUI JOIN and NON-EQUI JOIN
If we use equal sign “=” to join the table, it is EQUI JOIN, if any other, it is NON-EQUI join.
NON-EQUI JOIN example
If we want to find the name of an employee and their salary grade, we have to get data from two tables: EMP and SALGRADE.
select ename,sal, losal, hisal, grade from emp, salgrade;
+----------+---------+-------+-------+-------+ | ename | sal | losal | hisal | grade | +----------+---------+-------+-------+-------+ | BUSH | 5000.00 | 700 | 1200 | 1 | | BUSH | 5000.00 | 1201 | 1400 | 2 | | BUSH | 5000.00 | 1401 | 2000 | 3 | | BUSH | 5000.00 | 2001 | 3000 | 4 | | BUSH | 5000.00 | 3001 | 9999 | 5 | | BLAIR | 2850.00 | 700 | 1200 | 1 | | BLAIR | 2850.00 | 1201 | 1400 | 2 | | BLAIR | 2850.00 | 1401 | 2000 | 3 | | BLAIR | 2850.00 | 2001 | 3000 | 4 | | BLAIR | 2850.00 | 3001 | 9999 | 5 | | MERKEL | 2450.00 | 700 | 1200 | 1 | | MERKEL | 2450.00 | 1201 | 1400 | 2 | | MERKEL | 2450.00 | 1401 | 2000 | 3 | | MERKEL | 2450.00 | 2001 | 3000 | 4 | | MERKEL | 2450.00 | 3001 | 9999 | 5 | …
To select only the proper one, we have to check if the salary matches the grade’s criteria (i.e. SAL is between LOSAL and HISAL).
select ename,sal, losal, hisal, grade from emp, salgrade where SAL BETWEEN LOSAL AND HISAL;
+----------+---------+-------+-------+-------+ | ename | sal | losal | hisal | grade | +----------+---------+-------+-------+-------+ | BUSH | 5000.00 | 3001 | 9999 | 5 | | BLAIR | 2850.00 | 2001 | 3000 | 4 | | MERKEL | 2450.00 | 2001 | 3000 | 4 | | PUTIN | 2975.00 | 2001 | 3000 | 4 | | CHIRACK | 1250.00 | 1201 | 1400 | 2 | | BAROSSO | 1600.00 | 1401 | 2000 | 3 | | GATES | 1500.00 | 1401 | 2000 | 3 | | BUFFETT | 950.00 | 700 | 1200 | 1 | | WALTON | 1250.00 | 1201 | 1400 | 2 | | TOOSK | 3000.00 | 2001 | 3000 | 4 | | THATCHER | 800.00 | 700 | 1200 | 1 | | CARNEGIE | 3000.00 | 2001 | 3000 | 4 | | FORD | 1100.00 | 700 | 1200 | 1 | | ELISON | 1300.00 | 1201 | 1400 | 2 | +----------+---------+-------+-------+-------+
Now we don’t need anything except ENAME and GRADE.
select ename, grade from emp, salgrade where SAL BETWEEN LOSAL AND HISAL; +----------+-------+ | ename | grade | +----------+-------+ | BUSH | 5 | | BLAIR | 4 | | MERKEL | 4 | | PUTIN | 4 | | CHIRACK | 2 | | BAROSSO | 3 | | GATES | 3 | | BUFFETT | 1 | | WALTON | 2 | | TOOSK | 4 | | THATCHER | 1 | | CARNEGIE | 4 | | FORD | 1 | | ELISON | 2 | +----------+-------+
Joining many tables
You may join as many tables as you wish, but remember that every table has to be joined with at least one other table.
If you have n tables you need at least n-1 join conditions joined with the AND operator.
If you don’t meet this criterion you will see a Cartesian product as result.
The following example joins three tables.
select ename,loc,grade from emp,dept,salgrade WHERE dept.deptno=emp.deptno AND sal between losal and hisal;
+----------+----------+-------+ | ename | loc | grade | +----------+----------+-------+ | BUSH | NEW YORK | 5 | | BLAIR | PARIS | 4 | | MERKEL | NEW YORK | 4 | | PUTIN | LONDON | 4 | | CHIRACK | PARIS | 2 | | BAROSSO | PARIS | 3 | | GATES | PARIS | 3 | | BUFFETT | PARIS | 1 | | WALTON | PARIS | 2 | | TOOSK | LONDON | 4 | | THATCHER | LONDON | 1 | | CARNEGIE | LONDON | 4 | | FORD | LONDON | 1 | | ELISON | NEW YORK | 2 | +----------+----------+-------+
Table aliases
You may simplify long names of tables by using aliases.
To create an alias, write its name just behind the table name in the FROM clause.
select ename, e.deptno, dname from emp e, dept d where e.deptno = d.deptno;
+----------+--------+------------+ | ename | deptno | dname | +----------+--------+------------+ | BUSH | 10 | ACCOUNTING | | BLAIR | 30 | SALES | | MERKEL | 10 | ACCOUNTING | | PUTIN | 20 | RESEARCH | | CHIRACK | 30 | SALES | | BAROSSO | 30 | SALES | | GATES | 30 | SALES | | BUFFETT | 30 | SALES | | WALTON | 30 | SALES | | TOOSK | 20 | RESEARCH | | THATCHER | 20 | RESEARCH | | CARNEGIE | 20 | RESEARCH | | FORD | 20 | RESEARCH | | ELISON | 10 | ACCOUNTING | +----------+--------+------------+
You CANNOT use the table name if you created an alias.
select emp.ename from emp e; ERROR 1054 (42S22): Unknown column 'emp.ename' in 'field list'
Table aliases in Oracle
Oracle 10XE by default does not support an AS keyword for table aliases.
Exercises
31. Exercise
Select the name of the employee and the city (LOC column in DEPT table) in which they work.
+----------+----------+ | ename | loc | +----------+----------+ | BUSH | NEW YORK | | BLAIR | PARIS | | MERKEL | NEW YORK | | PUTIN | LONDON | | CHIRACK | PARIS | | BAROSSO | PARIS | | GATES | PARIS | | BUFFETT | PARIS | | WALTON | PARIS | | TOOSK | LONDON | | THATCHER | LONDON | | CARNEGIE | LONDON | | FORD | LONDON | | ELISON | NEW YORK | +----------+----------+
32. Exercise
Select the names of the employees, and the name and number of their department.
+----------+------------+--------+ | ename | dname | deptno | +----------+------------+--------+ | BUSH | ACCOUNTING | 10 | | BLAIR | SALES | 30 | | MERKEL | ACCOUNTING | 10 | | PUTIN | RESEARCH | 20 | | CHIRACK | SALES | 30 | | BAROSSO | SALES | 30 | | GATES | SALES | 30 | | BUFFETT | SALES | 30 | | WALTON | SALES | 30 | | TOOSK | RESEARCH | 20 | | THATCHER | RESEARCH | 20 | | CARNEGIE | RESEARCH | 20 | | FORD | RESEARCH | 20 | | ELISON | ACCOUNTING | 10 | +----------+------------+--------+
33. Exercise
Select the names of the employees, their salary and salary grade, but only those whose salary is more than 2000.
+----------+---------+-------+ | ename | sal | grade | +----------+---------+-------+ | BUSH | 5000.00 | 5 | | BLAIR | 2850.00 | 4 | | MERKEL | 2450.00 | 4 | | PUTIN | 2975.00 | 4 | | TOOSK | 3000.00 | 4 | | CARNEGIE | 3000.00 | 4 | +----------+---------+-------+
34. Exercise
Select employees working in London.
+----------+--------+ | ename | loc | +----------+--------+ | PUTIN | LONDON | | TOOSK | LONDON | | THATCHER | LONDON | | CARNEGIE | LONDON | | FORD | LONDON | +----------+--------+
35. Exercise
Select employees and their salary grades except those from London.
+---------+----------+-------+ | ename | loc | grade | +---------+----------+-------+ | BUSH | NEW YORK | 5 | | BLAIR | PARIS | 4 | | MERKEL | NEW YORK | 4 | | CHIRACK | PARIS | 2 | | BAROSSO | PARIS | 3 | | GATES | PARIS | 3 | | BUFFETT | PARIS | 1 | | WALTON | PARIS | 2 | | ELISON | NEW YORK | 2 | +---------+----------+-------+
Joining table with JOIN clause
You may use a JOIN clause to join two tables. The result is exactly the same as using a WHERE clause.
select ename, dname FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
+----------+------------+ | ENAME | DNAME | +----------+------------+ | BUSH | ACCOUNTING | | BLAIR | SALES | | MERKEL | ACCOUNTING | | PUTIN | RESEARCH | | CHIRACK | SALES | | BAROSSO | SALES | | GATES | SALES | | BUFFETT | SALES | | WALTON | SALES | | TOOSK | RESEARCH | | THATCHER | RESEARCH | | CARNEGIE | RESEARCH | | FORD | RESEARCH | | ELISON | ACCOUNTING | +----------+------------+
INNER and OUTER JOIN
All the joins in the examples and exercises were INNER, which means that you selected only those records which have their related rows in other tables.
Sometimes there is data in one table which doesn’t have any related rows in other tables. You can select them using OUTER join.
The outer join may be LEFT or RIGHT, depending on in which table ‘dangling’ rows are stored.
Dept tables contain a department called OPERATIONS in which nobody works. To select it type the following query.
select ename,dname FROM EMP RIGHT OUTER JOIN DEPT ON dept.deptno=emp.deptno;
+----------+------------+ | ename | dname | +----------+------------+ | BUSH | ACCOUNTING | | MERKEL | ACCOUNTING | | ELISON | ACCOUNTING | | PUTIN | RESEARCH | | TOOSK | RESEARCH | | THATCHER | RESEARCH | | CARNEGIE | RESEARCH | | FORD | RESEARCH | | BLAIR | SALES | | CHIRACK | SALES | | BAROSSO | SALES | | GATES | SALES | | BUFFETT | SALES | | WALTON | SALES | | | OPERATIONS | +----------+------------+
It is a RIGHT join because the DEPT table which contains extra rows (with OPERATION department) stands on the right side of the JOIN clause.
You may rewrite this query to LEFT join as follows.
select ename,dname FROM DEPT LEFT OUTER JOIN EMP on dept.deptno=emp.deptno;
+----------+------------+ | ename | dname | +----------+------------+ | BUSH | ACCOUNTING | | MERKEL | ACCOUNTING | | ELISON | ACCOUNTING | | PUTIN | RESEARCH | | TOOSK | RESEARCH | | THATCHER | RESEARCH | | CARNEGIE | RESEARCH | | FORD | RESEARCH | | BLAIR | SALES | | CHIRACK | SALES | | BAROSSO | SALES | | GATES | SALES | | BUFFETT | SALES | | WALTON | SALES | | | OPERATIONS | +----------+------------+
In this query any row from EMP table has NULL value. You may use it to select departments without employees. E.g.:
select ename,dname from dept left outer join emp on dept.deptno=emp.deptno WHERE ename IS NULL; +-------+------------+ | ename | dname | +-------+------------+ | | OPERATIONS | +-------+------------+
The query above can create surprises if ENAME can legitimately be NULL in the EMP table. To avoid this situation, it is better to use the right-side ID column in the test. This query pattern is known as "anti-join".
select dname from dept left outer join emp on dept.deptno=emp.deptno WHERE emp.deptno IS NULL; +------------+ | dname | +------------+ | OPERATIONS | +------------+
Outer Join in Oracle
The same results as in the query above may be achieved by using (+) characters.
select ename,dname FROM DEPT, EMP where dept.deptno=emp.deptno(+);
The (+) is placed at the end of a column name of the table where you do not have corresponding (by join condition) rows.
Join table with itself.
We need to report who is whose boss. All the required information is stored in the EMP table. To obtain such information we have to check MGR and ID number.
select id,ename,mgr from emp; +------+----------+------+ | id | ename | mgr | +------+----------+------+ | 7839 | BUSH | NULL | | 7698 | BLAIR | 7839 | | 7782 | MERKEL | 7839 | | 7566 | PUTIN | 7839 | | 7654 | CHIRACK | 7698 | | 7499 | BAROSSO | 7698 | | 7844 | GATES | 7698 | | 7900 | BUFFETT | 7698 | | 7521 | WALTON | 7698 | | 7902 | TOOSK | 7566 | | 7369 | THATCHER | 7902 | | 7788 | CARNEGIE | 7566 | | 7876 | FORD | 7788 | | 7934 | ELISON | 7782 | +------+----------+------+
If we want to know who FORD reports to (who is Ford’s boss or manager), we have to:
- know his MGR number: 7788
- find it in the ID column
- find ENAME with this ID: it’s CARNEGIE
As we may see it is a kind of equality relation. To obtain such information in one query we have to join the EMP table to the same EMP table.
To do so, we will use table aliases.
Let’s imagine that we have two identical EMP tables.
The first (let us call it E) stores information about employees.
The second (let us call it M) stores information about managers.
All we have to do is join these two tables using E.MGR = M.ID condition.
select E.ename 'Employee name',
E.mgr 'No of Emp Manager',
M.id 'No of Manager',
M.ename 'Manager name'
from emp E, emp M
where E.MGR=M.ID;
+---------------+-------------------+---------------+--------------+ | Employee name | No of Emp Manager | No of Manager | Manager name | +---------------+-------------------+---------------+--------------+ | BLAIR | 7839 | 7839 | BUSH | | MERKEL | 7839 | 7839 | BUSH | | PUTIN | 7839 | 7839 | BUSH | | CHIRACK | 7698 | 7698 | BLAIR | | BAROSSO | 7698 | 7698 | BLAIR | | GATES | 7698 | 7698 | BLAIR | | BUFFETT | 7698 | 7698 | BLAIR | | WALTON | 7698 | 7698 | BLAIR | | ELISON | 7782 | 7782 | MERKEL | | TOOSK | 7566 | 7566 | PUTIN | | CARNEGIE | 7566 | 7566 | PUTIN | | THATCHER | 7902 | 7902 | TOOSK | | FORD | 7788 | 7788 | CARNEGIE | +---------------+-------------------+---------------+--------------+
If we want to see employees without any boss, we need to use outer join.
select E.ename Employee,
M.ename Manager
from emp E left join emp M
on E.MGR=M.ID;
+----------+----------+ | Employee | Manager | +----------+----------+ | BUSH | NULL | | BLAIR | BUSH | | MERKEL | BUSH | | PUTIN | BUSH | | CHIRACK | BLAIR | | BAROSSO | BLAIR | | GATES | BLAIR | | BUFFETT | BLAIR | | WALTON | BLAIR | | TOOSK | PUTIN | | THATCHER | TOOSK | | CARNEGIE | PUTIN | | FORD | CARNEGIE | | ELISON | MERKEL | +----------+----------+
To see “bosses” without subordinates we can use the query below
select E.ename Employee,
M.ename Manager
from emp E right join emp M
on E.MGR=M.ID;
+----------+----------+ | Employee | Manager | +----------+----------+ | BLAIR | BUSH | | MERKEL | BUSH | | PUTIN | BUSH | | CHIRACK | BLAIR | | BAROSSO | BLAIR | | GATES | BLAIR | | BUFFETT | BLAIR | | WALTON | BLAIR | | TOOSK | PUTIN | | THATCHER | TOOSK | | CARNEGIE | PUTIN | | FORD | CARNEGIE | | NULL | CHIRACK | | NULL | BAROSSO | | NULL | GATES | | NULL | BUFFETT | | NULL | WALTON | | NULL | THATCHER | | NULL | FORD | | NULL | ELISON | +----------+----------+
If you want to see employees without bosses and “bosses” without subordinates you can use FULL JOIN.
select E.ename Employee,
M.ename Manager
from emp E FULL JOIN emp M
on E.MGR=M.ID;
All joining possibilities example: commons.wikimedia.org/wiki/File:SQL_Joins.svg
Exercises
36. Exercise
Find departments without employees.
+--------+------------+ | deptno | dname | +--------+------------+ | 40 | OPERATIONS | +--------+------------+
37. *Exercise
Select an employee’s name and his/her boss’s name (use JOIN clause).
+----------+----------+ | ename | ename | +----------+----------+ | BLAIR | BUSH | | MERKEL | BUSH | | PUTIN | BUSH | | CHIRACK | BLAIR | | BAROSSO | BLAIR | | GATES | BLAIR | | BUFFETT | BLAIR | | WALTON | BLAIR | | ELISON | MERKEL | | TOOSK | PUTIN | | CARNEGIE | PUTIN | | THATCHER | TOOSK | | FORD | CARNEGIE | +----------+----------+
38. Exercise
Select an employee’s name, his/her boss’s name and show the employees who have no superior.
+----------+----------+ | Employee | Boss | +----------+----------+ | BUSH | | | BLAIR | BUSH | | MERKEL | BUSH | | PUTIN | BUSH | | CHIRACK | BLAIR | | BAROSSO | BLAIR | | GATES | BLAIR | | BUFFETT | BLAIR | | WALTON | BLAIR | | TOOSK | PUTIN | | THATCHER | TOOSK | | CARNEGIE | PUTIN | | FORD | CARNEGIE | | ELISON | MERKEL | +----------+----------+
39. Exercise
Select an employee’s name, his/her boss’s name and show all employees even though they have no subordinates.
+----------+----------+ | Employee | Boss | +----------+----------+ | BLAIR | BUSH | | MERKEL | BUSH | | PUTIN | BUSH | | CHIRACK | BLAIR | | BAROSSO | BLAIR | | GATES | BLAIR | | BUFFETT | BLAIR | | WALTON | BLAIR | | ELISON | MERKEL | | TOOSK | PUTIN | | CARNEGIE | PUTIN | | | CHIRACK | | | BAROSSO | | | GATES | | | BUFFETT | | | WALTON | | THATCHER | TOOSK | | | THATCHER | | FORD | CARNEGIE | | | FORD | | | ELISON | +----------+----------+
40. Exercise*
Select an employee’s name, his/her boss’s name and show all employees even though they have no subordinates and also those who have no superior.
+----------+----------+ | ename | ename | +----------+----------+ | BUSH | | | BLAIR | BUSH | | MERKEL | BUSH | | PUTIN | BUSH | | CHIRACK | BLAIR | | BAROSSO | BLAIR | | GATES | BLAIR | | BUFFETT | BLAIR | | WALTON | BLAIR | | TOOSK | PUTIN | | THATCHER | TOOSK | | CARNEGIE | PUTIN | | FORD | CARNEGIE | | ELISON | MERKEL | | | CHIRACK | | | BAROSSO | | | GATES | | | BUFFETT | | | WALTON | | | THATCHER | | | FORD | | | ELISON | +----------+----------+
41. Exercise
Select employees hired earlier than their bosses.
+----------+------------+-------+------------+ | Employee | hiredate | Boss | hiredate | +----------+------------+-------+------------+ | BLAIR | 1981-05-01 | BUSH | 1981-11-17 | | MERKEL | 1981-06-09 | BUSH | 1981-11-17 | | PUTIN | 1981-04-02 | BUSH | 1981-11-17 | | BAROSSO | 1981-02-20 | BLAIR | 1981-05-01 | | WALTON | 1981-02-22 | BLAIR | 1981-05-01 | | THATCHER | 1980-12-17 | TOOSK | 1981-12-03 | +----------+------------+-------+------------+
42. Exercise*
Show employee, their boss and boss's boss
+----------+----------+----------+ | employee | boss | bossboss | +----------+----------+----------+ | BUSH | NULL | NULL | | BLAIR | BUSH | NULL | | MERKEL | BUSH | NULL | | PUTIN | BUSH | NULL | | CHIRACK | BLAIR | BUSH | | BAROSSO | BLAIR | BUSH | | GATES | BLAIR | BUSH | | BUFFETT | BLAIR | BUSH | | WALTON | BLAIR | BUSH | | TOOSK | PUTIN | BUSH | | THATCHER | TOOSK | PUTIN | | CARNEGIE | PUTIN | BUSH | | FORD | CARNEGIE | PUTIN | | ELISON | MERKEL | BUSH | +----------+----------+----------+
UNION, INTERSECT AND MINUS(EXCEPT)
UNION Operator
Imagine that you have some data across many tables (e.g. in different databases, like UK, Germany, etc) which contain the same data. If you want to show them all you may use the UNION operator.
select ename,sal,job,loc from emp,dept where dept.deptno = emp.deptno and loc = 'PARIS' UNION select ename,sal,job,loc from emp,dept where dept.deptno = emp.deptno and loc = 'LONDON';
+----------+---------+----------+--------+ | ename | sal | job | loc | +----------+---------+----------+--------+ | BLAIR | 2850.00 | MANAGER | PARIS | | CHIRACK | 1250.00 | SALESMAN | PARIS | | BAROSSO | 1600.00 | SALESMAN | PARIS | | GATES | 1500.00 | SALESMAN | PARIS | | BUFFETT | 950.00 | CLERK | PARIS | | WALTON | 1250.00 | SALESMAN | PARIS | | PUTIN | 2975.00 | MANAGER | LONDON | | TOOSK | 3000.00 | ANALYST | LONDON | | THATCHER | 800.00 | CLERK | LONDON | | CARNEGIE | 3000.00 | ANALYST | LONDON | | FORD | 1100.00 | CLERK | LONDON | +----------+---------+----------+--------+
INTERSECT Operator
INTERSECT operator (set intersection) allows you to select rows common for two queries.
SELECT JOB FROM EMP WHERE DEPTNO=30 INTERSECT SELECT JOB FROM EMP WHERE DEPTNO=20
+----------+ | JOB | +----------+ | CLERK | | MANAGER | +----------+
MINUS Operator (Oracle)
This operator allowes to to show rows present in the first query but not present in the second query.
SELECT JOB FROM EMP WHERE DEPTNO=30 MINUS SELECT JOB FROM EMP WHERE DEPTNO=20
+----------+ | JOB | +----------+ | SALESMAN | +----------+
EXCEPT Operator (SQL Server, MySQL. PostgreSQL)
Exercises
43. *Exercise
Find post filled in 1982 and 1983.
+----------+ | JOB | +----------+ | CLERK | +----------+
44. Exercise
Find jobs which are in department 10 but not in department 20.
+----------+ | JOB | +----------+ | PRESIDENT| +----------+
Subqueries
You may nest a query into another query:
- in a where clause
- in a table reference
- in a column expression
In this section we will discuss only subqueries in a WHERE clause.
A subquery can return:
- a scalar (a single value)
- a single row
- a single column
- a table
These are called scalar, column, row, and table subqueries.
Subqueries that return a particular kind of result often can only be used in certain contexts.
Scalar subqueries
Find employees earning more than the company’s average.
To understand it better, let’s divide it into three steps:
1. Find the company’s average salary.
select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+
2. Remember it (or copy it).
3. Select employees whose salary is greater than the copied value
select ename, sal from emp where sal > 2073.214286 ;
+----------+---------+ | ename | sal | +----------+---------+ | BUSH | 5000.00 | | BLAIR | 2850.00 | | MERKEL | 2450.00 | | PUTIN | 2975.00 | | TOOSK | 3000.00 | | CARNEGIE | 3000.00 | +----------+---------+
What will happen if the average salary changes? Unfortunately our query will be outdated.
Fortunately we can combine these three steps into one using subqueries.
select ename, sal from emp where sal > (SELECT AVG(SAL) FROM EMP) ;
+----------+---------+ | ename | sal | +----------+---------+ | BUSH | 5000.00 | | BLAIR | 2850.00 | | MERKEL | 2450.00 | | PUTIN | 2975.00 | | TOOSK | 3000.00 | | CARNEGIE | 3000.00 | +----------+---------+
In this query (SELECT AVG(SAL) FROM EMP) is called a subquery. It will be computed once, every time the query is run.
Subqueries returning a scalar may be used as an operand in any operation.
Row subqueries
What if we want to find all the people working in the same position (job) as Blair.
select ename,job from emp
where job = (select job from emp
where ename='BLAIR');
+--------+---------+ | ename | job | +--------+---------+ | BLAIR | MANAGER | | MERKEL | MANAGER | | PUTIN | MANAGER | +--------+---------+
It looks ok and even works fine. But what if there is another Blair?
The subquery will return two rows instead of one, and MySQL returns an error because it is impossible to compare two or more rows (AKA a vector) with a scalar.
insert into emp (id ,ename ,job ,deptno) values (9999,'BLAIR','CLERK',20); Query OK, 1 row affected (0.47 sec)
select ename,job from emp
where job = (select job from emp
where ename='BLAIR');
ERROR 1242 (21000): Subquery returns more than 1 row
The subquery indeed returns two rows.
select job from emp where ename='BLAIR';
+---------+ | job | +---------+ | MANAGER | | CLERK | +---------+
To solve these problems we have to use the IN operator to compare one value with all the data in a set of rows returned by the subquery.
select ename,job from emp
where job IN (select job from emp
where ename='BLAIR');
+----------+---------+ | ename | job | +----------+---------+ | BLAIR | MANAGER | | MERKEL | MANAGER | | PUTIN | MANAGER | | BUFFETT | CLERK | | THATCHER | CLERK | | FORD | CLERK | | ELISON | CLERK | | BLAIR | CLERK | +----------+---------+
This solves the problem and we don’t need Blair any more, so let’s delete him from our database.
delete from emp where id=9999; Query OK, 1 row affected (0.00 sec)
Column subqueries (All servers except SQL Server)
We want to find the employees earning the most in department 20.
select max(sal),deptno from emp where deptno=20 group by deptno;
+----------+--------+ | max(sal) | deptno | +----------+--------+ | 3000.00 | 20 | +----------+--------+
To find the best earners we have to check two values: their salary and their department.
We cannot simply compare only their salaries because it is possible that somebody else earns the same amount, but works in a different department.
Now we can find out who the luckiest are.
select ename,deptno,sal from emp where sal = 3000 and deptno=20;
+----------+--------+---------+ | ename | deptno | sal | +----------+--------+---------+ | TOOSK | 20 | 3000.00 | | CARNEGIE | 20 | 3000.00 | +----------+--------+---------+
The shortcut to the query above.
select ename,deptno,sal from emp where (sal,deptno) = (3000,20);
+----------+--------+---------+ | ename | deptno | sal | +----------+--------+---------+ | TOOSK | 20 | 3000.00 | | CARNEGIE | 20 | 3000.00 | +----------+--------+---------+
The (sal,deptno) = (3000,20) expression returns true only if the sal value is 3000 and the deptno is 20.
Semantically and logically, this is equivalent to this (sal = 3000 and deptno=20) expression.
Remember that order in the set is important.
For example (sal,deptno) = (3000,20) works and
(sal,deptno) = (20,3000) doesn’t.
Table subqueries
We want to find out who earns the most in each department.
First we have to find the maximum salaries in all the departments.
select max(sal),deptno from emp group by deptno ;
+----------+--------+ | max(sal) | deptno | +----------+--------+ | 5000.00 | 10 | | 3000.00 | 20 | | 2850.00 | 30 | +----------+--------+
Now, we select those employees whose sal and deptno meet one of the criteria.
select ename,deptno,sal from emp
where (sal,deptno) in (select max(sal), deptno
from emp
group by deptno);
+----------+--------+---------+ | ename | deptno | sal | +----------+--------+---------+ | BUSH | 10 | 5000.00 | | BLAIR | 30 | 2850.00 | | TOOSK | 20 | 3000.00 | | CARNEGIE | 20 | 3000.00 | +----------+--------+---------+
Exercises
45. Exercise
Find employees earning more than the manager’s average.
+----------+---------+ | ename | sal | +----------+---------+ | BUSH | 5000.00 | | BLAIR | 2850.00 | | PUTIN | 2975.00 | | TOOSK | 3000.00 | | CARNEGIE | 3000.00 | +----------+---------+
46. Exercise*(does not work on SQL Server)
Select employees earning the maximum salaries in their positions (jobs). Sort results by salary.
+----------+-----------+---------+ | ename | job | sal | +----------+-----------+---------+ | ELISON | CLERK | 1300.00 | | BAROSSO | SALESMAN | 1600.00 | | PUTIN | MANAGER | 2975.00 | | TOOSK | ANALYST | 3000.00 | | CARNEGIE | ANALYST | 3000.00 | | BUSH | PRESIDENT | 5000.00 | +----------+-----------+---------+
47. Exercise*(does not work on SQL Server)
Find employees hired first in their departments.
+----------+--------+------------+ | ename | deptno | hiredate | +----------+--------+------------+ | MERKEL | 10 | 1981-06-09 | | BAROSSO | 30 | 1981-02-20 | | THATCHER | 20 | 1980-12-17 | +----------+--------+------------+
ALL, ANY (SOME)
Let’s find employees earning more than anyone in department 10.
select ename, sal, deptno from emp where sal > any (select distinct sal from emp where deptno=10);
+----------+---------+--------+ | ename | sal | deptno | +----------+---------+--------+ | BUSH | 5000.00 | 10 | | BLAIR | 2850.00 | 30 | | MERKEL | 2450.00 | 10 | | PUTIN | 2975.00 | 20 | | BAROSSO | 1600.00 | 30 | | GATES | 1500.00 | 30 | | TOOSK | 3000.00 | 20 | | CARNEGIE | 3000.00 | 20 | +----------+---------+--------+
Let’s find employees earning more than all the employees in department 30.
select ename, sal, deptno from emp where sal > ALL (select distinct sal from emp where deptno=30);
+----------+---------+--------+ | ename | sal | deptno | +----------+---------+--------+ | BUSH | 5000.00 | 10 | | PUTIN | 2975.00 | 20 | | TOOSK | 3000.00 | 20 | | CARNEGIE | 3000.00 | 20 | +----------+---------+--------+
Subqueries and HAVING
Let ‘s find jobs which have an average salary higher than the average salary of managers.
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) > (SELECT AVG(SAL)
FROM EMP
WHERE JOB='MANAGER');
+-----------+-------------+ | JOB | AVG(SAL) | +-----------+-------------+ | ANALYST | 3000.000000 | | PRESIDENT | 5000.000000 | +-----------+-------------+
Correlated Subqueries
A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query.
Correlated subqueries cannot refer to the results of aggregate functions from the outer query.
Correlated subqueries are run for every row in an outer query (Non-correlated subqueries are run once before the outer query).
Let’s find all employees earning more than average in their positions.
select ename, sal, job
from emp osub
where sal > (select avg(sal)
from emp isub
where isub.job = osub.job);
+---------+---------+----------+ | ename | sal | job | +---------+---------+----------+ | BLAIR | 2850.00 | MANAGER | | PUTIN | 2975.00 | MANAGER | | BAROSSO | 1600.00 | SALESMAN | | GATES | 1500.00 | SALESMAN | | FORD | 1100.00 | CLERK | | ELISON | 1300.00 | CLERK | +---------+---------+----------+
*Correlated Subqueries and performance
Because correlated subqueries are run for every outer query’s record, they are inefficient and likely to be slow.
If it is possible you should rewrite them to table join.
For example the above query might be re-written as follows.
select ename, sal, avg_sal, e.job
from emp e, (select avg(sal) avg_sal, job
from emp
group by job) ae
where e.job = ae.job and e.sal > ae.avg_sal;
+---------+---------+-------------+----------+ | ename | sal | avg_sal | job | +---------+---------+-------------+----------+ | BLAIR | 2850.00 | 2758.333333 | MANAGER | | PUTIN | 2975.00 | 2758.333333 | MANAGER | | BAROSSO | 1600.00 | 1400.000000 | SALESMAN | | GATES | 1500.00 | 1400.000000 | SALESMAN | | FORD | 1100.00 | 1037.500000 | CLERK | | ELISON | 1300.00 | 1037.500000 | CLERK | +---------+---------+-------------+----------+
EXISTS keyword
If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.
Let’s find employees who have subordinates.
SELECT ENAME, SAL, DEPTNO
FROM EMP E
WHERE EXISTS (SELECT ID
FROM EMP
WHERE EMP.MGR=E.ID);
+----------+---------+--------+ | ENAME | SAL | DEPTNO | +----------+---------+--------+ | BUSH | 5000.00 | 10 | | BLAIR | 2850.00 | 30 | | MERKEL | 2450.00 | 10 | | PUTIN | 2975.00 | 20 | | TOOSK | 3000.00 | 20 | | CARNEGIE | 3000.00 | 20 | +----------+---------+--------+
Find workerless departments.
select dname, deptno from dept
where NOT EXISTS (select 1 from emp
where emp.deptno=dept.deptno);
+------------+--------+ | dname | deptno | +------------+--------+ | OPERATIONS | 40 | +------------+--------+
Exercises
48. Exercise (use correlated query)
Select employees earning maximum salaries in their positions (jobs). Sort results by salary.
+----------+-----------+---------+ | ename | job | sal | +----------+-----------+---------+ | ELISON | CLERK | 1300.00 | | BAROSSO | SALESMAN | 1600.00 | | PUTIN | MANAGER | 2975.00 | | TOOSK | ANALYST | 3000.00 | | CARNEGIE | ANALYST | 3000.00 | | BUSH | PRESIDENT | 5000.00 | +----------+-----------+---------+
49. Exercise (use correlated query)
Find employees hired first in their departments.
+----------+--------+------------+ | ename | deptno | hiredate | +----------+--------+------------+ | MERKEL | 10 | 1981-06-09 | | BAROSSO | 30 | 1981-02-20 | | THATCHER | 20 | 1980-12-17 | +----------+--------+------------+
50. Exercise
Find employees earning more than the average salary in their department.
+----------+---------+--------+ | ename | sal | deptno | +----------+---------+--------+ | BUSH | 5000.00 | 10 | | BLAIR | 2850.00 | 30 | | PUTIN | 2975.00 | 20 | | BAROSSO | 1600.00 | 30 | | TOOSK | 3000.00 | 20 | | CARNEGIE | 3000.00 | 20 | +----------+---------+--------+
51. Exercise*
Find employees earning more than the average salary in their department.
+----------+---------+------------------------------+--------+ | ename | sal | Average deprtment's salaries | deptno | +----------+---------+------------------------------+--------+ | BUSH | 5000.00 | 2917 | 10 | | BLAIR | 2850.00 | 1567 | 30 | | PUTIN | 2975.00 | 2175 | 20 | | BAROSSO | 1600.00 | 1567 | 30 | | TOOSK | 3000.00 | 2175 | 20 | | CARNEGIE | 3000.00 | 2175 | 20 | +----------+---------+------------------------------+--------+
52. Exercise
Use correlated subqueries to find departments without employees.
+--------+------------+ | deptno | dname | +--------+------------+ | 40 | OPERATIONS | +--------+------------+
53. *Exercise
Generate a report, which computes the following sums.
+-----------+---------+---------+---------+--------+ | job | DEPT10 | DEPT20 | DEPT30 | DEPT40 | +-----------+---------+---------+---------+--------+ | ANALYST | NULL | 6000.00 | NULL | NULL | | CLERK | 1300.00 | 1900.00 | 950.00 | NULL | | MANAGER | 2450.00 | 2975.00 | 2850.00 | NULL | | PRESIDENT | 5000.00 | NULL | NULL | NULL | | SALESMAN | NULL | NULL | 5600.00 | NULL | +-----------+---------+---------+---------+--------+
54. *Exercise
Find post(s) filled in 1980 and also filled 1982.
+---------+ | job | +---------+ | CLERK | +---------+
55. *Exercise
For each of the managers find a minimum salary of their subordinates.
+-----------+-----------+----------------------------------------+ | id | Managers | Minimum salary of their subordinates | +-----------+-----------+----------------------------------------+ | 7698 | BLAIR | 950.00 | | 7782 | MERKEL | 1300.00 | | 7566 | PUTIN | 3000.00 | +-----------+-----------+----------------------------------------+
More exercises
City Examples
- Date and time
- Select price and paper's market id for 10th day in a month. Use extract() function. Sort it by market id.
MARKET_ID PRICE -------------------------- ---------- ISIN_1 51 ISIN_2 33 ISIN_3 33 ISIN_4 209 ISIN_5 212 ISIN_6 10 ISIN_7 10 ISIN_8 71
- Aggregation
- Find the average price for every paper.
MARKET_ID AVERAGE -------------------------- ---------- ISIN_1 49,4 ISIN_2 32,1 ISIN_3 32,2 ISIN_4 195,2 ISIN_5 216,9 ISIN_6 11,3 ISIN_7 12,3 ISIN_8 72,1
- Set operators
- Find dates which has price 10 and 12 but not for the paper ISIN_7.
PDATE PRICE MARKET_ID -------- ---------- -------------------------- 21/04/06 10 ISIN_6 21/04/07 10 ISIN_6 21/04/10 10 ISIN_6 21/04/03 12 ISIN_6
- Subqueries
- Select all dates with the biggest price per paper. Sort results by market_id.
PDATE MARKET_ID PRICE -------- -------------------------- ---------- 21/04/07 ISIN_1 52 21/04/08 ISIN_2 33 21/04/06 ISIN_2 33 21/04/05 ISIN_2 33 21/04/09 ISIN_2 33 21/04/07 ISIN_2 33 21/04/10 ISIN_2 33 21/04/09 ISIN_3 34 21/04/07 ISIN_3 34 21/04/04 ISIN_4 227 21/04/03 ISIN_5 229 21/04/09 ISIN_6 14 21/04/04 ISIN_7 14 21/04/01 ISIN_8 75
Recap of Basics, Basic Cheat Sheet (Oracle)
SELECT -- selection query, filtering columns
DISTINCT -- unique rows only
* -- all raws, expensive
, ename -- column name
, 'Dick was here' -- literal string, single quotes
, sal + comm -- expression
, nvl(comm, 0) -- dealing with null, a replacement
, coalesce(comm, sal, 0) -- yet another null killer
, 'ugly_expression_is_here'
AS "Nice alias" -- nick name for column, double quotes, AS can be omitted
, round(sal/12.3, 2) -- single row function, built in
, extract(YEAR from hiredate) -- single row func, gets year from the date, returns a number
, max(sal) -- multi-row function, built in; grouping function
, ename || ' is a ' || job -- concatenation, also with a function: CONCAT(ename, ' is a ')
FROM -- the source of data
emp -- table name
, dept TableAlias
emp LEFT OUTER JOIN dept -- joins: INNER - only matched data, OUTER - also non matches from one table(LEFT or RIGHT), FULL - also non matches from both tables
ON emp.deptno = dept.deptno -- here should go only joining conditions (best practice)
WHERE -- business logic goes here, filtering rows
sal >= 1500 -- comparison operators: <=, !=, <>
OR -- grouping logic operators: OR, AND; NOT - negation; XOR logic: (a XOR b) => (a or b) and not (a and b)
comm IS NULL -- dealing with null, removal
AND
emp.deptno = dept.deptno (+) -- joins: (+) here means RIGHT OUTER
AND
sal BETWEEN 1000 AND 2000 -- equivalent of compound conditions (AND), only weak comparison (>= and <=)
OR
deptno IN (10, 30) -- equivalent of compound conditions (OR)
OR
ename LIKE '_A%' -- simple regexp, '_' - one any char, '%'- zero or many any chars
AND
sal > ( SELECT avg(sal)
FROM emp
GROUP BY job ) -- subquery, nested SELECT clause - useful to show more data with grouping or instead of joins
GROUP BY -- aggregation clause, here should go all the columns from SELECT list if not grouped with grouping functions
job, deptno
HAVING -- aggregation clause, here should go all the filters for groups
avg(sal) > 1500
ORDER BY -- sorting rows after all the filtering
sal ASC -- ascending is by default
, "Nice alias" DESC -- descending, by alias instead of column name
, 3 -- position number of the element in the SELECT clause
UNION -- set operator, works on sets of selects; other set opts - UNION ALL, INTERSECT, MINUS
SELECT -- SET OPERATORS RULES: same number, order and data types of elements; only one "ORDER BY" clause and always at the end
dname
, deptno
-- , (...)
FROM
emp, dept
FETCH FIRST 2 ROWS ONLY -- to simplify the BIG results, also last can be done with changing the order via ORDER BY (DESC, ASC)
; -- end of instruction, important!
