SQL Tutorial

This Sql tutorial explains the basics of the SQL language. The tutorial shows examples based on two tables: EMP and DEPT. The tables can be created in any database (such as Pubs database) using this script.

INDEX

 


SELECT STATEMENT

 

SELECT col-name, col-name,…

FROM table-name, table-name,…

WHERE search-condition

ORDER BY col-name

 

SELECT     EMPNO, LASTNAME

FROM         EMP

WHERE     (LASTNAME = 'Smith')

 

SELECT     EMPNO, SALARY

FROM         EMP

 

SELECT     *

FROM         DEPT

 


WHERE CLAUSE

 Who is assigned to department D21?

 

SELECT     WORKDEPT, FIRSTNAME

FROM         EMP

WHERE     WORKDEPT = 'D21'

 

 


COMPARISON OPERATIONS

 

=

Equal

<>

Not Equal

>

Greater than

>=

Greater than or equal

<

Less than

<=

Greater than or equal

 

SELECT     EMPNO, LASTNAME, SALARY

FROM         EMP

WHERE     SALARY >= 20000

 

SELECT     LASTNAME, FIRSTNAME, JOB

FROM         EMP

WHERE     JOB <> 'DESIGNER'

 

 


NULL SELECTION

 

A special value that represents absence of information.

 

SELECT     DEPTNO, DEPTNAME, MGRNO

FROM         DEPT

WHERE     MGRNO IS NULL

 

Reverse test: IS NOT NULL

 

 


MULTIPLE PREDICATES

 

Which woman designers have an annual salary over $22,000?

 

SELECT     LASTNAME, SALARY

FROM         EMP

WHERE     SALARY > 22000

AND JOB = 'DESIGNER'

AND SEX = 'F'

 

Who makes more than $40,000 or is a manager?

 

SELECT     LASTNAME, FIRSTNAME, SALARY, JOB

FROM         EMP

WHERE     SALARY > 40000 OR

                      JOB = 'MANAGER'

 

Which Filed Rep makes more than $2,000 in commissions or who has an education lever higher then 18?

 

SELECT     LASTNAME, JOB, COMM

FROM         EMP

WHERE     (COMM > 2000 AND JOB = 'FIELDREP')

OR EDLEVEL > 16

 

 

SELECT     LASTNAME, JOB, COMM

FROM         EMP

WHERE     COMM > 2000 AND (JOB = 'FIELDREP'

            OR EDLEVEL > 16)

 

 


BETWEEN / IN PREDICATES

 

BETWEEN expression AND expression

 

SELECT     EMPNO, LASTNAME, SALARY

FROM         EMP

WHERE     SALARY BETWEEN 25000 AND 40000

 

 

IN (value, value,…)

 

SELECT     EMPNO, LASTNAME, SALARY

FROM         EMP

WHERE     WORKDEPT IN ('D11', 'D21', 'E11')

 

NOT reverse a test

 

WHERE     SALARY NOT BETWEEN 25000 AND 40000

 

WHERE     WORKDEPT NOT IN ('D11', 'D21', 'E11')

 

 


PARTIAL SEARCH

 

To search on subset of characters: Like

 

% Ignore 0 – n characters

_ Ignore one character

 

Examples:

 

SELECT     LASTNAME

FROM         EMP

WHERE     LASTNAME LIKE 'M%'

 

SELECT     LASTNAME

FROM         EMP

WHERE     LASTNAME LIKE '_M%'

 

SELECT     LASTNAME

FROM         EMP

WHERE     LASTNAME LIKE '____'

 

SELECT     LASTNAME

FROM         EMP

WHERE    LASTNAME LIKE '%ER'

 

 


SELECT DISTINCT

 

All rows:

 

SELECT     WORKDEPT

FROM         EMP

 

Distinct rows:

 

SELECT DISTINCT WORKDEPT

FROM         EMP

 

SELECT DISTINCT WORKDEPT, JOB

FROM         EMP

 

 


ORDER BY CLAUSE

 

List alphabetically all employees in D21

 

SELECT     FIRSTNAME, LASTNAME

FROM         EMP

WHERE     WORKDEPT = 'D21'

ORDER BY LASTNAME

 

Comments:

 

More examples:

 

SELECT     LASTNAME, HIREDATE

FROM         EMP

WHERE     WORKDEPT = 'D21'

ORDER BY LASTNAME DESC

 

SELECT     LASTNAME, HIREDATE

FROM         EMP

WHERE     WORKDEPT = 'D21'

ORDER BY 2 DESC, 1

  

 


CALCULATED VALUES

 

+

Add

*

Multiply

-

Subtract

/

Divide

 

What are total earnings for sales reps?

 

SELECT     LASTNAME, SALARY + BONUS + COMM

FROM         EMP

WHERE      JOB = 'FIELDREP'

 

Which operators have total earnings less then $20,000?

 

SELECT     LASTNAME, SALARY + BONUS + COMM

FROM         EMP

WHERE     JOB = 'OPERATOR'

AND SALARY + BONUS + COMM < 20000

 

Order of evaluation:

  

 


COLUMN FUNCTIONS

 

SUM

Total values in a column

AVG

Averages column values

MIN

Finds lowest value in a column

MAX

Finds highest value in a column

COUNT

Counts number of occurrences

 

 

Example:

SELECT     COUNT(*), MIN(SALARY), MAX(SALARY), SUM(SALARY),

        AVG(SALARY)

FROM         EMP

 

Comments:

  

 


COUNT / COUNT DISTINCT

 

COUNT(*) number of rows meeting the search condition

 

COUNT(DISTINCT column-name) number of unique values in that column. Nulls will not count.

 

SELECT     COUNT(*), COUNT(DISTINCT WORKDEPT)

FROM         EMP

 

 


LITERALS

 

SELECT     'Total Salary:', SUM(SALARY)

FROM         EMP

   

 


SELECT AS CLAUSE

 

Purpose: Designate derived column headings

 

SELECT     LASTNAME, SALARY + BONUS + COMM

FROM         EMP

WHERE     WORKDEPT = 'C01'

ORDER BY 2 DESC

 

SELECT     LASTNAME, SALARY + BONUS + COMM AS TOTAL_EANINGS

FROM         EMP

WHERE     WORKDEPT = 'C01'

ORDER BY TOTAL_EANINGS DESC

 

GROUP BY CLAUSE

 

Specifies an intermediate result table that consists of a grouping of rows from that table.

 

 What is the average salary by department?

 

SELECT     WORKDEPT, AVG(SALARY)

FROM         EMP

GROUP BY WORKDEPT

 

 

What is the number of employees and average education by job title?

 

SELECT     JOB, COUNT(*), AVG(EDLEVEL)

FROM         EMP

GROUP BY JOB

 

 


HAVING CLAUSE

 

SELECT…
FROM…
WHERE…
GROUP BY…
HAVING…

 

WHERE Clause – chooses rows

HAVING clause – chooses groups

 

Which departments have more then five employees?

 

SELECT     WORKDEPT, COUNT(*)

FROM         EMP

GROUP BY WORKDEPT

HAVING     COUNT(*) > 5

  

 


DATE / TIME EXAMPLE

 

What are number of years worked for sales reps?

 

SELECT     LASTNAME, HIREDATE, DATEDIFF(year, HIREDATE, GETDATE())

FROM         EMP

WHERE     (JOB = 'FIELDREP')

   

 


JOINING TABLES

  

Join implemented by FROM clause

Result can contain columns from any table in FROM clause

 

Join Types:

 

List manager’s last name and the department name for each department.

 

INNER

 

SELECT          LASTNAME, DEPTNAME

FROM EMP

            INNER JOIN DEPT ON EMPNO = MGRNO

 

RIGHT

Includes unmatched rows from DEPT

SELECT          LASTNAME, DEPTNAME

FROM EMP

            RIGHT OUTER JOIN DEPT ON EMPNO = MGRNO

 

LEFT

Includes unmatched rows from EMP

SELECT          LASTNAME, DEPTNAME

FROM EMP

            LEFT OUTER JOIN DEPT ON EMPNO = MGRNO

 

FULL

Includes unmatched rows from both tables

SELECT          LASTNAME, DEPTNAME

FROM EMP

            FULL OUTER JOIN DEPT ON EMPNO = MGRNO

 

 

  


UNION

 

Purpose: To merge results from multiple queries

 

 

Would operator and clerk salary ranges overlap if we gave clerks 4% raise and operators a 15% raise?

 

SELECT          JOB, MIN(SALARY*1.15), MAX(SALARY*1.15)

FROM EMP

GROUP BY JOB

HAVING JOB = 'OPERATOR'

 

UNION

 

SELECT          JOB, MIN(SALARY*1.04), MAX(SALARY*1.04)

FROM EMP

GROUP BY JOB

HAVING JOB = 'CLERK'

 

 


STRING FUNCTIONS

 

Second to fifth characters:

 

SELECT     ADMINRDEPT, SUBSTRING(DEPTNAME, 2, 4)

FROM         DEPT

WHERE     ADMINRDEPT = 'E01'

 

First six characters:

 

SELECT     ADMINRDEPT, LEFT(DEPTNAME, 6)

FROM         DEPT

 

Column Size:

 

SELECT     LASTNAME, LEN(LASTNAME)

FROM         EMP

 

Concatenate:

 

SELECT     LASTNAME + ', ' + FIRSTNAME

FROM         EMP

WHERE      LASTNAME = 'Smith'

 

 


SUBQUERY

 

A complete SQL query that appears in WHERE or HAVING clause of another query.

 

SELECT…

FROM…

WHERE… (SELECT…

                    FROM…

                     WHERE…)

 

Result of subquery is a value or set of values used in outer select.

 

List employees whose work department is under administrative department E01.

 

SELECT  LASTNAME, WORKDEPT

FROM    EMP

WHERE           WORKDEPT IN

                        (SELECT     DEPTNO

                        FROM         DEPT

                        WHERE     ADMINRDEPT = 'E01')

 

 

Inner select results: D11, D21, E11

 

Subquery comments:

 

 

Which employees have salary higher than the company average?

 

SELECT          LASTNAME, SALARY

FROM EMP

WHERE           SALARY >

                        (SELECT AVG(SALARY)

                        FROM EMP)

ORDER BY 2 DESC  

 

Inner select result: 30232.5581

 

List alphabetically employees whose salaries are higher that the average salary of any department.

 

SELECT          LASTNAME, SALARY

FROM EMP

WHERE           SALARY >

                        ANY(SELECT AVG(SALARY)

                        FROM EMP

                        GROUP BY WORKDEPT)

ORDER BY LASTNAME

 

Step 1: Find average salary for each department

Step 2: Compare each employee’s salary to list of department averages

 

IN

Indicates that the value in the expression must be among the value returned by the subquery.

ANY

Indicates that the value in the expression must compare in the indicated way to at least one of values returned by the subquery

ALL

Indicates that the value in the expression must compare in the indicated way to all values returned by the subquery

 

 IN is an equivalent to a series of OR on a single column.

 

 


HAVING CLAUSE SUBQUERY

 

List those departments whose average of non-managers salary is above the company average of non-managers salaries.

 

SELECT          WORKDEPT, AVG(SALARY)

FROM EMP

WHERE   JOB <> 'MANAGER'

GROUP BY WORKDEPT

HAVING         AVG(SALARY) >

                        (SELECT         AVG(SALARY)

                        FROM EMP

                        WHERE   JOB <> 'MANAGER')

ORDER BY 2

 

 


CORRELATED SUBQUERY

 

Which employees have a salary higher that the average of their department?

 

SELECT E1.LASTNAME, E1.WORKDEPT, E1.SALARY, E2.SALARY
FROM EMP E1
    INNER JOIN (
        SELECT WORKDEPT, AVG(SALARY) AS SALARY
        FROM EMP
        GROUP BY WORKDEPT
    ) E2 ON E1.WORKDEPT = E2.WORKDEPT
WHERE E1.SALARY > E2.SALARY

 

 

Step 1: Find Employees department number

Step 2: Calculate the department average salary

Step 3: Compare the two values

Step 3: Add to results or reject

Step 5: Go to next employee and repeat the process

 

SUBQUERY: TRUE/FALSE test

 

List information about managers making less than $37,000 if there are any managers making more that $40,000.

 

SELECT          *

FROM EMP

WHERE           JOB = 'MANAGER'

            AND SALARY < 37000

            AND EXISTS

            (SELECT         *

            FROM EMP

            WHERE           JOB = 'MANAGER'

                        AND SALARY > 40000)

 

 

Inner select is evaluated for True/False first.

Could also have NOT EXISTS