Basic Queries

Overview

Time: 20 min

Objectives

• Build understanding on important keywords of SQL

SELECT:

The SELECT statement is used to indicate what is required to be selected from the table. To select columns from the table, mention the names of the columns after SELECT keyword. To select all columns, indicate the asterisk (*) symbol after SELECT keyword.

Important Note – The SQL statement keywords are NOT case-sensitive and hence the keywords select and SELECT are treated the same way.

Consider the below table called Customers which contains details about various customers. (Only first five rows are shown)

Selecting all the columns

SELECT * FROM Customers

Basically, we are saying show me all the columns (*) from the table Customers.

Selecting particular columns

SELECT CustomerName, City FROM Customers

Renaming columns for display

SELECT CustomerName AS Name, City FROM Customers

Result columns are displayed in the order specified

SELECT Country, ContactName, PostalCode, CustomerID FROM Customers

WHERE:

The WHERE keyword is used to specify conditions. This keyword is used when you need to retrieve information matching certain conditions.

Syntax

SELECT columns FROM table_name WHERE condition

SELECT * FROM Customers WHERE Country="Germany"

Note : While using strings, the inverted commas (“string”) needs to be used.

DISTINCT:

The DISTINCT keyword is used to select and display all the unique values under the specified column. The keywords in SQL can be combined to get necessary results based on what kind of results which is required. For example, to identify all the different countries in the table, to identify the different departments where employees work in a employee table etc.

Syntax

SELECT DISTINCT column_name FROM table_name

SELECT DISTINCT Country FROM Customers

SELECT DISTINCT Country FROM Customers WHERE Country="USA"

ORDER BY:

The ORDER BY keyword is used to display the results in a particular order. You can specify the name of the column (or) a number indicating the column position based on which the results need to be ordered. By default, the results are ordered in ascending order but can be changed to descending using DESC keyword. Ordering can also be done based on more than one column.

Syntax

ASCENDING ORDER

SELECT column_name FROM table_name ORDER BY column_name/column_number

DESCENDING ORDER

SELECT column_name FROM table_name ORDER BY column_name/column_number DESC

FIRST COLUMN DESCENDING AND SECOND COLUMN ASCENDING

SELECT column_name FROM table_name ORDER BY column_1 DESC, column_2

FIRST COLUMN ASCCENDING AND SECOND COLUMN DESCENDING

SELECT column_name FROM table_name ORDER BY column_1, column_2 DESC

Examples:

SELECT CustomerID, CustomerName, ContactName, City FROM Customers ORDER BY CustomerName

SELECT CustomerID, CustomerName, ContactName, City FROM Customers ORDER BY CustomerName DESC

Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Syntax

SELECT column_name AS alias_name FROM table_name

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Key Points

• Introduction to SQL and its keywords like SELECT, FROM, AS, WHERE, DISTINCT, ORDER BY