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 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
Who is assigned to department D21?
SELECT WORKDEPT, FIRSTNAME
FROM EMP
WHERE WORKDEPT = 'D21'
|
= |
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'
A special value that represents absence of information.
SELECT DEPTNO, DEPTNAME, MGRNO
FROM DEPT
WHERE MGRNO IS NULL
Reverse test: IS NOT NULL
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 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')
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'
All rows:
SELECT WORKDEPT
FROM EMP
Distinct rows:
SELECT DISTINCT WORKDEPT
FROM EMP
SELECT DISTINCT WORKDEPT, JOB
FROM EMP
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
|
+ |
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:
|
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(*) 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
SELECT 'Total Salary:', SUM(SALARY)
FROM EMP
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
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
What are number of years worked for sales reps?
SELECT LASTNAME, HIREDATE, DATEDIFF(year, HIREDATE, GETDATE())
FROM EMP
WHERE (JOB = 'FIELDREP')
Join implemented by FROM clause
Result can contain columns from any table in FROM clause
Join Types:
List managers 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
|
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'
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'
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 employees 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.
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
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