SQL Syntax

Basic Select statement.

Introduction

SQL (Structured Query Language) is used to communicate with relational databases. SQL statements are composed of clauses, expressions, and predicates that define what actions to perform on the database.

SELECT Statement

Here’s a basic structure of an SQL statement:

Select statement
  • SELECT: Specifies the columns to retrieve.
  • FROM: Specifies the table to retrieve data from.

Syntax

  SELECT Column_names                                                  
  FROM table_name ;

Example:

  SELECT first_name, last_name 
  FROM Customers

Select comand will select columns: first_name, last_name from table 'Customers'

  • To select all columns in the table use SELECT *

Example:

  SELECT * 
  FROM Customers

It will select all columns that are there in table: Customers

  • To select Distinct rows in the table use SELECT DISTINCT

Example:

  SELECT DISTINCT province 
  FROM Customers

It will return distinct department values in table Customers

  • To select top few rows use SELECT TOP

Example:

  SELECT Top(5) first_name 
  FROM Customers

WHERE Clause

Where clause is used for filtering data. It allows you to select rows that meet the criteria.

Where

Example

  SELECT first_name, last_name
  FROM Customers 
  WHERE province= 'ON'

WHERE condition filters Customers who has Province= 'ON'

The WHERE condition can include various operators

Example

  1. Equal to: WHERE Province= 'ON'
  2. Not equal to: WHERE Province != 'ON'
  3. Greater than: WHERE customer_id > 100000
  4. Less than: WHERE customer_id < 100000
  5. Between a certain range: WHERE customer_id BETWEEN 100000 and 200000
  6. Pattern matching: WHERE first_name Like 'James%'
  7. Matches any value in a list: WHERE Province IN ('ON', 'MB')

You can combine multiple conditions using logical operators like AND and OR.

  • AND: returns true when both conditions are true
  • OR: returns true when either of conditions is true

Example

  1. AND: WHERE Province = 'ON' AND Where customer_id > 100000

This query selects Customers who are from Ontario and has customer_id > 100000

  1. OR: Where Province IN ('ON', 'MB') OR first_name Like 'James%'

This query selects Customers who are from Ontario and Manitoba and first_name is like James

ORDER BY Clause

The ORDER BY clause is used to sort the result of a query by one or more columns. This clause helps organize the data in either ascending or descending order based on the values in the specified columns. By default, if not specified, it will sort the results in ascending order.

Order By

Example

SELECT *
FROM Customers
ORDER BY customer_id;

This query retrieves all columns from the Customers table and sorts the results by the customer_id column in ascending order.

DESC: for sorting in Descending Order

Example

SELECT *
FROM Customers
ORDER BY customer_id DESC;

ASC: for sorting in Ascending Order

Example

SELECT *
FROM Customers
ORDER BY customer_id ASC;

Sorting by Multiple Columns

Example

SELECT *
FROM Customers
ORDER BY last_name ASC, first_name DESC;

This query first sorts the result set by last_name in ascending order. For Customers with the same last_name, it further sorts them by first_name in descending order.

Points to keep in mind before using ORDER BY clause:

  • Sorting Order: The ORDER BY clause allows multiple columns for sorting. Sorting is done first on the first column, and if there are duplicate values, it moves to the second column, and so on.
  • Null Values: By default, NULL values appear in the end for ascending order (ASC) and on the top for descending order (DESC).
  • Performance: Sorting large datasets can be time taking.

Aliases

Aliases are temporary names assigned to a table or column for the duration of a query. They make queries easier to read, especially when dealing with complex queries, or when the table or column names are lengthy.

  1. Column Alias A column alias renames a column heading in the result set. Useful for renaming a column to something more user-friendly or descriptive.

Syntax:

SELECT column_name AS alias_name
FROM table_name;

Example

SELECT first_name AS 'First Name', last_name AS 'Last Name'
FROM Customers;

In this example, first_name is renamed to 'First Name', and last_name is renamed to 'Last Name' in the query result.

  1. Table Alias A table alias provides a temporary name for a table. It’s particularly useful when joining multiple tables or referring to a table multiple times in a query.

Syntax:

SELECT alias_name.column_name
FROM table_name AS alias_name;

Example:

SELECT c.first_name
FROM Customers AS c

In this case, Customers table is aliased as c making the query easier to read.

Points to Remember:

  • Aliases are not permanent; they only last for the duration of the query.
  • The AS keyword is optional for aliases in most databases but improves readability.
  • Aliases are particularly helpful in self-joins and complex queries involving multiple tables or subqueries.

Conclusion

SELECT is used to retrieve data from a table, WHERE is used to filter data, and ORDER BY is used to sort data in ascending or descending order. You can use aliases to make querry easy to read, they are temporary names assigned to a table or column for the duration of a query.

Asked in Interview

  • Write an SQL query to retrieve all customer_id and order_date from the Orders table for orders placed between '2023-01-01' and '2023-12-31', sorted by order_date.
  • How do you select distinct values from a column in SQL?
  • How do you filter rows based on a condition?
  • What is the difference between = and LIKE in SQL?
  • How would you select records where a column’s value falls within a range?
  • How can you fetch records where a column value contains a specific substring?