CodeToLive

SELECT Queries in SQL

The SELECT statement is the most commonly used command in SQL for retrieving data from databases. It allows you to specify exactly which data you want to retrieve and how it should be presented.

Basic SELECT Syntax


SELECT column1, column2, ...
FROM table_name;

Selecting All Columns


-- Select all columns from the employees table
SELECT * FROM employees;

Selecting Specific Columns


-- Select only first_name and last_name from employees
SELECT first_name, last_name 
FROM employees;

The WHERE Clause

The WHERE clause filters records to include only those that meet specified conditions. 


-- Select employees in department 10
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id = 10;

-- Select employees hired after January 1, 2020
SELECT * FROM employees
WHERE hire_date > '2020-01-01';

Comparison Operators

SQL supports various comparison operators in WHERE clauses:

Logical Operators

Combine multiple conditions using AND, OR, and NOT: 


-- Employees in department 10 OR 20
SELECT * FROM employees
WHERE department_id = 10 OR department_id = 20;

-- Employees in department 10 AND salary > 50000
SELECT * FROM employees
WHERE department_id = 10 AND salary > 50000;

-- Employees NOT in department 10
SELECT * FROM employees
WHERE NOT department_id = 10;

Sorting Results with ORDER BY


-- Sort employees by last name (ascending)
SELECT * FROM employees
ORDER BY last_name;

-- Sort by salary descending
SELECT * FROM employees
ORDER BY salary DESC;

-- Sort by department then by last name
SELECT * FROM employees
ORDER BY department_id, last_name;

Limiting Results

Different databases have different syntax for limiting results: 


-- MySQL, PostgreSQL, SQLite
SELECT * FROM employees
LIMIT 10;

-- SQL Server, MS Access
SELECT TOP 10 * FROM employees;

-- Oracle
SELECT * FROM employees
WHERE ROWNUM <= 10;

DISTINCT for Unique Values


-- Get unique department IDs
SELECT DISTINCT department_id 
FROM employees;

-- Get unique combinations of city and state
SELECT DISTINCT city, state 
FROM locations;

Column Aliases


-- Using AS keyword
SELECT first_name AS "First Name", 
       last_name AS "Last Name"
FROM employees;

-- Without AS keyword (also valid)
SELECT first_name "First Name",
       last_name "Last Name"
FROM employees;

Calculated Columns


-- Calculate annual salary (monthly salary * 12)
SELECT first_name, last_name, 
       salary * 12 AS annual_salary
FROM employees;

-- Concatenate first and last names
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Pattern Matching with LIKE


-- Names starting with 'J'
SELECT * FROM employees
WHERE first_name LIKE 'J%';

-- Names containing 'an'
SELECT * FROM employees
WHERE first_name LIKE '%an%';

-- Names exactly 5 characters long
SELECT * FROM employees
WHERE first_name LIKE '_____';

Working with NULL Values


-- Employees with no department assigned
SELECT * FROM employees
WHERE department_id IS NULL;

-- Employees with department assigned
SELECT * FROM employees
WHERE department_id IS NOT NULL;

Aggregate Functions

While we'll cover aggregation in detail later, here are basic examples: 


-- Count all employees
SELECT COUNT(*) FROM employees;

-- Average salary
SELECT AVG(salary) FROM employees;

-- Highest salary
SELECT MAX(salary) FROM employees;

-- Lowest salary
SELECT MIN(salary) FROM employees;

-- Total salary expense
SELECT SUM(salary) FROM employees;

Grouping Data with GROUP BY


-- Count employees per department
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- Average salary per department
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Filtering Groups with HAVING


-- Departments with more than 5 employees
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- Departments with average salary > 50000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
Back to Tutorials