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:
- FROM is used to indicate from which tables the data is to be taken, as well as how the tables JOIN to each other.
- WHERE is used to identify which rows to be retrieved, or applied to GROUP BY. WHERE is evaluated before the GROUP BY.
- GROUP BY is used to combine rows with related values into elements of a smaller set of rows.
- 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.
- 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';
