How Data is Extracted From Databases?

You use SQL’s SELECT command to extract data from your database. This command helps you fetch the desired data set, as well as specify the presentation order. In this chapter, you’ll execute all the options of this command to fetch data from the database.

Before you proceed, go through the following guidelines to write valid SQL statements that are easy both to read and to edit.

·         SQL commands and statements are case insensitive.

·         You can enter SQL statements on one line or can split them on multiple lines.

·         Command words can neither be split across lines, nor can they be abbreviated.

·         You can use tabs and indents for better readability.

·         Place a semicolon (;) as a terminator before you execute a statement (not required in SQL Developer GUI).

·         In SQL syntax, text defined under square brackets [ ] is optional, and that mentioned under curly braces { } is mandatory. Keywords (such as SELECT, FROM etc.) are presented in upper case letters, while user provided values (column/table names, conditions etc.) are displayed in lower case.

The SELECT Command

    Syntax  

SELECT      [DISTINCT]   {* | column [alias], …}

FROM       {table name}

[WHERE       condition(s)]

[GROUP BY  expression]

[HAVING      group condition]

[ORDER BY  {column, expression} [ASC|DESC]];

    Syntax Explained :

Clause	Explanation
SELECT	It’s a keyword followed by at least one column from the desired table.
DISTINCT	It’s an optional clause which suppresses duplicates.
*	Asterisk is a wild card character which is used to select all columns from a table. The vertical bar sign ( | ) means that you can use either * or specific column(s).
column [alias]	A list of specific column(s) of a table with optional custom headings.
FROM table name	It is the name of table you wish to fetch data from.
WHERE condition(s)	By using this clause you specify the desired data. The condition can have column names, expressions, and comparison operators.
GROUP BY   expression	It divides the rows in a table into smaller groups.
HAVING  group condition	Used in conjunction with GROUP BY, it is used to return only those groups which are specified  in the condition.
ORDER BY	With this clause you specify the display order of the fetched data set.
ASC|DESC	Orders the fetched rows in ascending or descending order.

Selecting All Data from a Table

You can use the SELECT command in its simplest form to retrieve all data from a table. For this purpose you use the asterisk (*) character to fetch data from all columns. In the following example, the SELECT statement requests data from all columns in all rows contained in the Departments table. Assuming that you’re connected to the HR schema using the Learn SQL connection, enter the following statement in the Worksheet pane – as shown in the figure below – and click the Run Statement button. The Query Result pane will appear, carrying the result for the executed query.

    SQL Statement :

SELECT         *

FROM          departments;            

    Output :

Excerpt from the book: SQL - The Shortest Route For Beginners