SQL Syntax

Some of the most important SQL commands are listed below. These below SQL Syntaxes will be suitable for quick reference.



SELECT [hint][DISTINCT] select_list

FROM table_list



[WHERE conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]



[ORDER BY order_list [ASC DESC] ]

[FOR UPDATE for_update_options]





SQL Select Statement



SELECT "column name" FROM "table name"



Example:

Select salary from emp;



SQL Where



SELECT "column name"

FROM "table name"

WHERE "condition"



Example:

Select salary from emp

Where salary > 2000



SQL Distinct



SELECT DISTINCT "column name"

FROM "table name"



Example:

Select DISTINCT name from emp;



SQL And/Or



SELECT "column name"

FROM "table name"

WHERE "condition"

{[ANDOR] "condition"}+



Example:

SELECT salary

FROM emp

WHERE Salary > 1000

OR (Salary <> 275)



SQL Between



SELECT "column name"

FROM "table name"

WHERE "column name" BETWEEN 'value1' AND 'value2'



Example:

SELECT *

FROM emp

WHERE Date BETWEEN 'Jan-01-1999' AND 'Jan-15-1999'



SQL In



SELECT "column name"

FROM "table name"

WHERE "column name" IN ('value1', ‘value2’ ...)



Example:

SELECT *

FROM emp

WHERE last_name IN ('sharma', 'dhall')



SQL Like



SELECT "column name"

FROM "table name"

WHERE "column name" LIKE {PATTERN}



Example:

SELECT *

FROM emp

WHERE last_name LIKE '%EN%'



SQL Order By



SELECT "column name"

FROM "table name"

[WHERE "condition"]

ORDER BY "column name" [ASC, DESC]



Example:

SELECT name, Salary

FROM emp

ORDER BY name DESC



SQL Count



SELECT COUNT ("column name")

FROM "table name"



Example:

SELECT COUNT (salary)

FROM emp



SELECT * FROM Table;

In the above query, SELECT and FROM are SQL keywords, * is a wildcard which means “all columns”



Commonly available SQL keywords related to SELECT include:

1. FROM is used to indicate from which tables the data is to be taken, as well as how the tables JOIN to each other.


2. WHERE is used to identify which rows to be retrieved, or applied to GROUP BY. WHERE is evaluated before the GROUP BY.


3. GROUP BY is used to combine rows with related values into elements of a smaller set of rows.


4. HAVING is used to identify which of the "combined rows" (combined rows are produced when the query has a GROUP BY keyword or when the SELECT part contains aggregates), are to be retrieved. HAVING acts much like a WHERE, but it operates on the results of the GROUP BY and hence can use aggregate functions.


5. ORDER BY is used to identify which columns are used to sort the resulting data.

Returning a Single Column from a table

The syntax is as follows:

SELECT Column FROM Table;

Oracle uses the ANSI standard concatenation operator, . Because this operator is reserved for string concatenation, the operands don't need to be cast to a string type—they'll be converted automatically:

SELECT FirstName ' ' LastName AS Name FROM EMP_Database;

Sorting with the ORDER BY Clause

The ORDER BY clause is always applied after all other clauses are applied, such as the WHERE and GROUP BY clauses. Without an ORDER BY clause in an SQL statement, rows will often be retrieved in the physical order in which they were added to the table. The default behavior is to sort rows in ascending order.

SELECT ColumnA, ColumnB FROM Table

ORDER BY ColumnA ASC;

Although this is the default behavior so you don't need to explicitly specify the ASC keyword, it's a better idea to include it to ensure that your queries are as easy to read as possible.

Also, you may want to sort your rows in descending order, in which case you use the DESC keyword:

SELECT ColumnA, ColumnB FROM Table

ORDER BY ColumnA DESC;

Filtering Data

DISTINCT always retrieves the first value from a repeating group. If there are multiple repeating groups DISTINCT will retrieve the first row from each group.

Hence, DISTINCT will always require a sort. DISTINCT can operate on a single or multiple columns.

SELECT DISTINCT ColumnA FROM Table;

In order to filter out duplicate rows, you use the DISTINCT keyword.

If you want to retrieve multiple columns, you can guarantee that every row you obtain contains unique data for the specified set of columns.

For example, the following query will return only unique combinations of customer names and debit card details:

SELECT DISTINCT CustomerName, DebitCard FROM Customers;

This doesn't mean that you won't have duplicates in either column, only that each combination is unique. You could, for example, have several customers with the same name but with different debit card numbers.

Using WHERE Clause

We can use the WHERE clause to restrict the rows returned by a query.

SELECT ColumnA, ColumnB, ColumnC FROM Table

WHERE Condition;

Condition is very flexible, allowing you to test for equalities and inequalities in column data, ranges of values to look for etc. You achieve all this using a simple syntax that includes various operators and keywords that, when combined, allow you to search for pretty much anything.

We can use following comparison operators with the WHERE clause:

Operator	Meaning
=	Equal
<>, !=, ^=	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal
<=	Less Than Or Equal

And many more for example BETWEEN ...AND..., LIKE, IS NULL etc.

Some examples of where

1)

SELECT name FROM bedroom

WHERE bedcolor = 'BLACK'

OR ceilingcolor = 'GREEN'

OR wallcolor = 'YELLOW'

2)

SELECT z

FROM t

WHERE x = 6 AND y > 7 ;

3)

Find the average price of Bud.

Sells (bar, beer, price)

SELECT AVG (price)

FROM Sells

WHERE beer = 'Bud';