The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Querying Data

Next ►
►

In this section, we will cover the basics of querying data in PostgreSQL. Querying is one of the most fundamental operations in SQL, allowing you to retrieve and manipulate data stored in your database. We will explore the SELECT statement, filtering results with WHERE, sorting data with ORDER BY, and limiting results with LIMIT.

Key Concepts

  1. SELECT Statement: The primary statement used to query data from a database.
  2. WHERE Clause: Used to filter records based on specific conditions.
  3. ORDER BY Clause: Used to sort the result set.
  4. LIMIT Clause: Used to limit the number of rows returned.

SELECT Statement

The SELECT statement is used to fetch 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

SELECT first_name, last_name
FROM employees;

This query retrieves the first_name and last_name columns from the employees table.

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

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';

This query retrieves the first_name and last_name of employees who work in the 'Sales' department.

ORDER BY Clause

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

Syntax

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

Example

SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC;

This query retrieves the first_name and last_name of employees, sorted by last_name in ascending order.

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

SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC
LIMIT 10;

This query retrieves the first 10 first_name and last_name of employees, sorted by last_name in ascending order.

Practical Exercises

Exercise 1: Basic SELECT

Task: Retrieve the name and price columns from the products table.

SELECT name, price
FROM products;

Exercise 2: Filtering with WHERE

Task: Retrieve the name and price of products that cost more than $50.

SELECT name, price
FROM products
WHERE price > 50;

Exercise 3: Sorting with ORDER BY

Task: Retrieve the name and price of products, sorted by price in descending order.

SELECT name, price
FROM products
ORDER BY price DESC;

Exercise 4: Limiting Results with LIMIT

Task: Retrieve the name and price of the top 5 most expensive products.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

Common Mistakes and Tips

  1. Missing Semicolon: Always end your SQL statements with a semicolon (;).
  2. Case Sensitivity: SQL keywords are case-insensitive, but table and column names might be case-sensitive depending on the database settings.
  3. Logical Errors in WHERE Clause: Ensure that the conditions in the WHERE clause are logically correct to avoid unexpected results.

Conclusion

In this section, we covered the basics of querying data in PostgreSQL using the SELECT statement, filtering results with WHERE, sorting data with ORDER BY, and limiting results with LIMIT. These fundamental operations are essential for retrieving and manipulating data in your database. In the next section, we will explore how to update data in PostgreSQL.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved