Lesson 3: Basic SQL Queries

In this lesson, we will explore fundamental SQL commands that allow you to retrieve data from databases. SQL (Structured Query Language) is a powerful tool for managing and manipulating relational databases. The ability to query data is one of the most essential skills in SQL.

SELECT Statement

The SELECT statement is used to select data from a database. The data returned is stored in a result table, sometimes called the result set.

Syntax

SELECT column1, column2, ... FROM table_name;

Example

Let's say we have a table called Employees:

To select all employees from the Employees table:

SELECT * FROM Employees;

Selecting Specific Columns

You can also select specific columns:

SELECT FirstName, LastName FROM Employees;

WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

Syntax

SELECT column1, column2, ... FROM table_name WHERE condition;

Example

To select employees who are older than 30:

SELECT * FROM Employees WHERE Age > 30;

ORDER BY Clause

The ORDER BY clause is used to sort the result set in either ascending or descending order.

Syntax

SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC];

Example

To sort employees by their last names in ascending order:

SELECT * FROM Employees ORDER BY LastName ASC;

LIMIT Clause

The LIMIT clause is used to specify the number of records to return.

Syntax

SELECT column1, column2, ... FROM table_name LIMIT number;

Example

To select only the first two employees:

SELECT * FROM Employees LIMIT 2;

Best Practices

• Always specify the columns you need instead of using * to improve performance.
• Use descriptive aliases for columns when necessary to enhance readability.
• Comment your SQL code to explain complex queries for future reference.

Common Mistakes

Remember: SQL is case insensitive, but it is a good practice to use uppercase for SQL keywords (e.g., SELECT, FROM, WHERE) and lowercase for column and table names.

Be careful with the WHERE clause! Omitting it may lead to retrieving all records, which could be overwhelming.

Summary

In this lesson, we covered: - The SELECT statement for retrieving data. - The use of the WHERE clause to filter results. - Sorting results with the ORDER BY clause. - Limiting results using the LIMIT clause.

With these fundamental commands, you can start querying databases effectively!

Exercise 1: Select Specific Columns

Write a SQL query to select only the FirstName and Department of all employees from the Employees table.

Exercise 2: Filter Results

Write a SQL query to select all employees who work in the 'IT' department.

Exercise 3: Sort and Limit

Write a SQL query to select the first three employees sorted by their age in descending order.

• The SELECT statement retrieves data from a database.
• Use the WHERE clause to filter records based on conditions.
• The ORDER BY clause sorts the result set.
• The LIMIT clause restricts the number of records returned.
• Always practice good SQL coding habits for better performance and readability.