You need to install and set up a relational database as mentioned in previous notes, for trying out the examples here.
We can use ORDER BY to sort our data and it comes at the end of SELECT statements. The COLUMN you sort on does not need to be returned by SELECT query. We can sort on the value returned by SQL expressions, not just columns. To do a multiple column sort, you specify the columns to sort on, separated by commas. SQL will sort on the columns in the order you give them. Default sort order is ascending. We can make it descending using DESC keyword.
- SELECT columns FROM table_name ORDER BY specifiers DESC;
- The keyword for ascending sort is ASC. However since it is default you don’t have to use it.
We can use CASE along with ORDER BY to specify custom sort order.
SELECT * FROM Employees ORDER BY CASE id
WHEN id<1600 THEN 1
WHEN id<1700 THEN 2
We can create groups with the GROUP BY statements.
GROUP BY can be used with aggregate functions.
For example we can find the number of employees in each department.
Many DBMS do not allow grouping based on variable-length fields. You can specify multiple columns in which to group.
SQL groups by the first column, then by the second column, then third etc.
An SQL Expression is a term that SQL can evaluate to return a value. Expressions include:
- Function calls – These are calls to built-in function.
- System values – These include the time, the database user etc.
- Numeric or string operators – These include + and – for numeric values and so on.
Built-in functions in SQL
Few built-in functions in SQL are:
- SORT() – taking square roots.
- LOWER() – returns the lower case.
- ABS() – returns the absolute value.
- YEAR() – extract year from date.
- CONCAT() – joins or concatenates text strings.
- MAX() – return a column with the largest value.
AVG(), COUNT(), MAX(), MIN(), SUM() etc. are called aggregate functions.
NULL values are ignored by aggregate functions.
System values are legal to use in place of column names or with function calls in SELECT statements.
Few built in system values are:
- USER (Current SQL username.)
- SESSION_USER (Current SQL session user.)
- SYSTEM_USER (Current operating system user.)
You can use operators to concatenate text strings, but this varies by DBMS. For Example oracle uses a ‘||’ operator to concatenate text strings while SQL server uses ‘+’.
COMMONLY ASKED QUESTIONS BASED ON CONCEPTS IN THIS PAGE
- What are SQL expressions? Can we use them within an SQL Select?
- What are aggregate functions? Give examples.
- How does an aggregate function in SQL treat NULL?
- Give the basic usage of ORDER BY and GROUP BY?