SQL notes

The Basic Select Statement
SELECT *
FROM table-name

This statement selects all columns in a table.
SELECT *
FROM Employee
ORDER BY deptno

 This statement selects all columns in a table and sequences them by 'deptno' .
SELECT *
FROM Employee
ORDER BY deptno, name

Order is name within department.
SELECT *
FROM Employee
ORDER BY salary DESC

Order is of table is salary in decending sequence. (default is ASC i.e. Ascending)
SELECT name, job, salary, empno
FROM Employee
ORDER BY salary

This orders columns of a table and displays selected columns.
SELECT DISTINCT column-names
FROM table-name

 Eleminates duplicate rows.

Selecting Rows from a Table
SELECT *
FROM Employee
WHERE deptno = 40

Selects only rows where deptno = 40

Comparison Operators

  • = equal to
  • ^= not equal to
  • > greater than
  • >= greater than or equal to
  • < less than
  • <= less than or equal to
SELECT *
FROM Employee
WHERE name = 'Reed'

Selects only columns with the name Reed
SELECT *
FROM Employee
WHERE job = 'Manager'
AND Salary > 40000

Selects manager making more than 40,000
SELECT *
FROM Employee
WHERE job = 'Clerk'
OR Salary > 30000

Selects all clerks and employees making over 30,000
SELECT name, job, deptno
FROM Employee
WHERE job = 'Manager'
AND deptno ^= 30

Selects all managers that do not work in deptno 30.
SELECT *
FROM Employee
WHERE job = 'Clerk'
OR Salary > 30000

Selects all clerks and employees making over 30,000
SELECT *
FROM Employee
WHERE deptno IN (10,20,50)

Selects all employees working in deptno 10, 20 or 50.
SELECT *
FROM Employee
WHERE job = salary
BETWEEN 35000 AND 45000

Selects all employees with a salary >= 35000 and <= 45000.
SELECT *
FROM Employee
WHERE name
LIKE 'W%'

Selects all names starting with 'W'.
Note: '_' represents one character; '%' represents any number of characters.

Built-in Functions

There are five built-in SQL functions:

  • AVG provides the average set of values.
  • SUM provides the sum of a set of values.
  • MIN provides the minimum value in a set of values
  • MAX provides the maximum value in a set of values
  • COUNT provides a count of the members of a set
SELECT AVG(salary)
FROM Employee

 Results in the average of all salaries in the table
SELECT COUNT(*)
FROM Employee

 Results in the number of employees in the table
SELECT COUNT(column-name)
FROM Employee

 Counts the number of fields of a column.
SELECT COUNT DISTINCT(column-name)
FROM Employee

 Counts the number of non-duplicate fields of a column.
SELECT MAX(salary)
FROM Employee

 Returns the higest salary in the employee table.
SELECT MAX(salary), MIN(salary), AVG(salary)
FROM Employee

 Multiple functions can be performed at one time.

Calculations

Operators:

  • + addition
  • - subtraction
  • * multiplicaton
  • / division

 May be used within SELECT or WHERE clauses
SELECT name, job, salary+comm
FROM Employee
ORDER BY salary+comm

 Total compensation is used in both SELECT and ORDER BY clauses.

SubQueries

Result of the sub-query is passed to the next level query (main query) where the sub-query result is used. Note the lowest level query is processed first.
SELECT name, job
FROM Employee
WHERE job =
(
SELECT job
FROM Employee
WHERE name = 'Johnson'
)

List all employees with the same job as Johnson.

Home Links Notebook Tools Map

Pro bono
--------
Marketplace
----US----

<>-<>