SQL Programming: Difference between revisions

Revision as of 19:42, 23 October 2024

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

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);

SQL Language Structure
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.

Date and time formats:

https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings

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

Docs
- https://www.postgresql.org/docs/current/functions-datetime.html

SQLite

Docs
- Core - https://www.sqlite.org/lang_corefunc.html
- Date - https://www.sqlite.org/lang_datefunc.html
GUIs
- App - https://sqlitebrowser.org/dl/
- Web - https://alpha.sqliteviewer.app/

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:

+----------+--------------------------------------------+
| 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

Additional exercises

City Examples

Date and time
1. 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
1. 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
1. 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
1. 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!

@@ Line 1,812: / Line 1,812: @@
 == SQLite ==
 * '''Docs'''
-** <small>https://www.sqlite.org/lang_datefunc.html</small>
+** Core - <small>https://www.sqlite.org/lang_corefunc.html</small>
+** Date - <small>https://www.sqlite.org/lang_datefunc.html</small>
 * '''GUIs'''
 ** ''App'' - <small>https://sqlitebrowser.org/dl/</small>

SQL Programming: Difference between revisions

Revision as of 19:42, 23 October 2024

SQL Programming Training Materials

Copyright Notice

Relational Database

Overview

SQL Language

Exercises

Dealing with date and time in MySQL*

Date Data Types

Date formatting

Dealing with date and time in Oracle*

Dealing with date in SQL Server*

PostgresSQL

SQLite

Exercises

Aggregate Functions (GROUP BY)

Exercises

Selecting data from many tables

Exercises

Joining table with JOIN clause

Exercises

UNION, INTERSECT AND MINUS(EXCEPT)

Exercises

Subqueries

Exercises

ALL, ANY (SOME)

Exercises

More exercises

City Examples

Recap of Basics, Basic Cheat Sheet (Oracle)

Navigation menu

Search