24  DQL (data query language) SQL Select

24.1 SQL most basic select example

The most basic select example according to SQL standard will be as following

SELECT * FROM TableName;

According to SQL Standard, from is required in the select statements but some databases like SQL Server and sqlite allow select statements without from.

SELECT 2+2;

We could order select statements in two ways.

1. Syntax ordering: How we write them
2. Semantic ordering: how the database engine process them

We will do give two ordering for simple cases and we will build upon them.

24.2 SQL Select syntax ordering 1

Following order is taken from from sql server documentation and simplified.

• SELECT select_list
• FROM table_source
• [ WHERE search_condition ]
• [ GROUP BY group_by_expression ]
• [ HAVING search_condition ]
• [ ORDER BY order_expression [ ASC | DESC ]]

Syntax ordering is how we should write the select statements. Below table gives the meaning of keywords.

Command Name	ROLE	Definition
SELECT	Column List	which columns are in the result set
FROM	Source Table	Which tables
WHERE	Search filter	filters rows in the tables
GROUP BY	Group column List	group rows by given columns
HAVING	Search - Filter	After Grouping this filter works
ORDER BY	Order Column List	Order latest rows

24.3 SQL Select Semantic Ordering 1

• [ FROM table_source ]
• [ WHERE search_condition ]
• [ GROUP BY group_by_expression ]
• [ HAVING search_condition ]
• [ ORDER BY order_expression [ ASC | DESC ]]
• SELECT select_list

Semantic ordering is the how the select statement is processed by database engine.

24.4 SQL Basic Select examples

Chinook All columns using * character

SELECT * FROM Customer;

Using select with column names

SELECT FirstName, LastName FROM Customer;   

** Calculated Columns **

SELECT FirstName || ' ' || LastName FROM Customer;

** Column Alias **

SELECT FirstName || ' ' || LastName AS FullName FROM Customer;

Column aliases are especially useful with calculated columns.

** Table Alias **

SELECT c.FirstName, c.LastName  FROM Customer c;

Table alias is especially useful when we select rows from more than one table and the column names are same. We need to distinguish which tablename.columnname we are using. See below example

SELECT 
c.FirstName || ' ' || c.LastName As CustomerName
, e.FirstName || ' ' || e.LastName AS SupportEmployeeName 
FROM Customer c inner join Employee e on c.SupportRepId = e.EmployeeId 

More Complex Example

More explanation is later.

select CustomerId
,sum(total) as TotalInvoice 
from Invoice 
group by CustomerId
order by TotalInvoice desc;