Basic SQL Queries

Categories

Retrieving Data

To retrieve data in SQL and to run any query at all, you need to use at least the SELECT and FROM statement, otherwise you will get an error.

Syntax:

Syntax-1

SELECT column1, column2
FROM database.table

In Syntax-1, you are telling the query to retrieve column1 and column2 from the location database.table.

Syntax-2

SELECT *
FROM database.table

In Syntax-2, you are telling the query to retrieve all available columns from the location database.table. The * represents all columns.

Example:

Example-1

SELECT id, first_name
FROM company.employees

In Example-1, you are telling the query to retrieve the columns id and first_name from the location company.employees.

Example-2

SELECT *
FROM company.employees

In Example-2, you are telling the query to retrieve all available columns from the location company.employees.

Filtering Data

To filter data in SQL you will need to use the WHERE statement. In this statement you will be able to specify conditions that must be met for rows to be included in the results output.

Syntax:

Syntax-1

SELECT *
FROM database.table
WHERE condition

In Syntax-1, after the WHERE statement you specify the condition that must be met. This can include comparisons, logical operators, functions, etc.

- Comparison Operators -

  • = (equal to)

  • != or <> (not equal to)

  • < (less than)

  • > (greater than)

  • <= (less than or equal to)

  • >= (greater than or equal to)

Comparison Operators Examples:

Example-1

SELECT *
FROM company.employees
WHERE salary > 115000

In Example-1, we are filtering for only the rows that have a salary greater than 115000.

Example-2

SELECT *
FROM company.employees
WHERE id = 101

In Example-2, we are filtering for only the rows that have an id that is equal to 101.

Example-3

SELECT *
FROM company.employees
WHERE job_title = 'Manager'

In Example-3, we are filtering for only the rows that have a job_title that is equal to ‘Manager’.

- Logical Operators -

  • AND - Returns true if all conditions separated by AND are true

  • OR - Returns true if any conditions separated by OR are true

Logical Operators Examples:

Example-1

SELECT *
FROM company.employees
WHERE salary > 115000 AND department_id = 3

In Example-1, we are filtering for only the rows that have a salary greater than 115000 AND have a department_id equal to 3.

Example-2

SELECT *
FROM company.employees
WHERE salary > 115000 OR department_id = 3

In Example-2, we are filtering for only the rows that have a salary greater than 115000 OR have a department_id equal to 3.

- IN Operator -

Tests whether a value matches any value in a list.

IN Operators Example:

Example-1

SELECT *
FROM company.employees
WHERE job_title IN ('Manager', 'Developer')

In Example-1, we are filtering for only the rows that have a job_title IN ‘Manager’ or ‘Developer’.

Example-2

SELECT *
FROM company.employees
WHERE department_id IN (1, 2)

In Example-2, we are filtering for only the rows that have a department_id IN 1 or 2.

- LIKE Operator -

Used to match patterns in strings with wildcard characters % and _.

LIKE Operator Example:

Example-1

SELECT *
FROM company.employees
WHERE first_name LIKE 'J%'

In Example-1, we are using the % wildcard character along with the LIKE Operator. % matches any string of zero or more characters so in this example, ‘J%’ can be seen as match any string that starts with the letter J.

Example-2

SELECT *
FROM company.employees
WHERE first_name LIKE 'J___'

In Example-2, we are using the _ wildcard character along with the LIKE Operator. _ matches any single character so in this example, ‘J___’ can be seen as match any string that starts with the letter J and is followed by exactly 3 characters. The reason it is 3 characters is because there are 3 _ in ‘J___’.

- IS NULL / IS NOT NULL -

Tests whether a value is null or not null. IS NULL means that a value is blank or missing and IS NOT NULL means that a value is not blank or missing.

IS NULL / IS NOT NULL Examples:

Example-1

SELECT *
FROM company.employees
WHERE first_name IS NULL

In Example-1, we are only returning the rows where the first_name column IS NULL.

Example-2

SELECT *
FROM company.employees
WHERE first_name IS NOT NULL

In Example-2, we are only returning the rows where the first_name column IS NOT NULL.

Sorting Data

To sort data in SQL you will need to use the ORDER BY statement. In this statement you will be able to specify the columns by which you want to sort the results and in which order (ascending or descending).

Syntax:

Syntax-1

SELECT *
FROM database.table
ORDER BY column1 [ ASC | DESC ], column2 [ ASC | DESC ], ...

In Syntax-1, after the ORDER BY statement, you specify the column that you want to sort by and whether you want it in ASC (ascending) or DESC (descending) order. You can sort by either a single column or multiple columns and if you don’t specify ASC or DESC, the sorting order will default to ASC.

- Sorting Directions, Single Column, and Multiple Columns Examples -

Example-1

SELECT *
FROM company.employees
ORDER BY first_name

In Example-1, we are ordering by the first_name and since we did not specify in what order we want the first_name to be ordered in, the results will be returned in ASC (ascending) order.

Example-2

SELECT *
FROM company.employees
ORDER BY first_name DESC

In Example-2, we are ordering by the first_name and we specify that we want the results returned in DESC (descending) order.

Example-3

SELECT *
FROM company.employees
ORDER BY job_title DESC , id ASC

In Example-3, we are ordering by multiple columns. The first column that is being sorted is the job_title column in DESC (descending) order and after that the id column is being sorted in ASC (ascending) order.