More

    SQL Concepts – ORDER BY, CASE, GROUP BY, and Expressions

    You need to install and set up a relational database as mentioned in previous notes, for trying out the examples here. 

    ORDER BY

    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.

    CASE

    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

    END, lastname;

    GROUP BY

    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.

    SQL EXPRESSION

    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()
    • SUM()

    AVG(), COUNT(), MAX(), MIN(), SUM() etc. are called aggregate functions

    NULL values are ignored by aggregate functions.

    System Values

    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.)
    • CURRENT_DATE
    • CURRENT_TIME
    • CURRENT_TIMESTAMP

    Operators

    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

    1. What are SQL expressions? Can we use them within an SQL Select?
    2. What are aggregate functions? Give examples.
    3. How does an aggregate function in SQL treat NULL?
    4. Give the basic usage of ORDER BY and GROUP BY?

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox