SQL Result Refine

WHERE Clause

The WHERE clause is used to filter records

String Pattern

LIKE operator is used in WHERE clause to search for a specified string pattern.

Select all name starts with A

SELECT name FROM tablename WHERE name like "A%"

• The % sign represent zero or more characters
• The _ sign represent one, single character

Maths Operators

Select result within a given range.

SELECT location FROM map
WHERE height >= 50 AND height <= 100

BETWEEN Operator

The BETWEEN operator is inclusive. It can be used in numbers, text, or dates.

SELECT location FROM map
WHERE height BETWEEM 50 and 100

IN Operator

The IN operator allows user to specify multiple values

SELECT col_name FROM tablename
WHERE col_name IN (value1, value2, ...)

Sorting Results

Using ORDER BY clause to sort the result-set in ascending(default) or descending order

SELECT col_name FROM tablename ORDER BY col_name

To order by descending order, use the DESC keyword

SELECT col_name FROM tablename ORDER BY col_name DESC

Specify order by column index

SELECT col_name FROM tablename ORDER BY 2

The output sort the output by the second column of the table

Grouping Results

Using DISTINCT clause

Use DISTINCT keyword is retrieve distinct(different) values

SELECT DISTINCT col_name FROM tablename

Using GROUP BY clause

The GROUP BY statement groups rows that have the same values into summary rows. It’s often followed by aggregate functions such as COUNT(), MAX(), MIN(), SUM(), AVG(), etc.

SELECT col_name, COUNT(col_name)
AS total FROM tablename
GROUP BY col_name

Using HAVING clause

The HAVING clause was added to functioned with aggregate functions. It only works with GROUP BYclause

SELECT col_name, COUNT(col_name)
AS total FROM tablename
GROUP BY col_name
HAVING COUNT(col_name) < 5

Build-in Functions

Agregation functions

SUM(), MIN(), MAX(), AVG(), etc.

Scalar and String functions

ROUND(), LENGTH(), UCASE(), LCASE()

Date and Tume functions

YEAR(), MONTH(), DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), WEEK(), HOUR(), MINUTE(), SECOND()

• Special Registers: CURRENT_DATE(), CURRENT_TIME()

Sub-queries and Nested Selects

SELECT col1 FROM tablename
  WHERE col2 = (select MAX(col2) from table)

Nested SELECT statement

SELECT col_name [, col_name ]
FROM tab1 [, tab2 ]
WHERE col_name OPERATOR
   (SELECT col_name [, col_name1 ]
    FROM tab1 [, tab2 ]
    WHERE condition)

Multiple Tables

CROSS JOIN

The CROSS JOIN keyword selects all records from both tables,

• It can also be performed by having several tables in the FROM clause

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

SELECT column_name(s)
FROM table1, table2;

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name(s)
FROM table1, table2
WHERE table1.column_name = table2.column_name;

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). Missing value from the right side will be set to 0.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1) Missing values from the left side will be set to 0

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL JOIN

The FULL JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

View

A view is a virtual table based on the result set of an SQL statement

CREATE VIEW statement

• CREATE VIEW

  CREATE VIEW view_name AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
  

REPLACE VIEW Statement

REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition

DROP VIEW Statement

DROP VIEW view_name