WikiGalaxy
PostgreSQL Select
Basic Select Statement:
The SELECT statement in PostgreSQL is used to fetch data from a database table. It allows you to specify the columns you want to retrieve.
Selecting All Columns:
Using the asterisk (*) in a SELECT statement retrieves all columns from a table.
Filtering with WHERE Clause:
The WHERE clause is used to filter records based on specified conditions, allowing you to retrieve only the data that meets certain criteria.
Ordering Results with ORDER BY:
ORDER BY is used to sort the result set based on one or more columns, either in ascending or descending order.
Limiting Results with LIMIT:
The LIMIT clause restricts the number of rows returned by the query, useful for pagination or sampling data.
Joining Tables:
Joins are used to combine rows from two or more tables, based on a related column between them.
SELECT * FROM employees WHERE department = 'Sales' ORDER BY salary DESC LIMIT 10;
Using Aliases:
Aliases provide a temporary name to a table or column, often used to make column names more readable in the result set.
Aggregating Data with GROUP BY:
GROUP BY groups rows that have the same values into summary rows, often used with aggregate functions like COUNT, SUM, AVG, etc.
Conditional Aggregates with HAVING:
The HAVING clause is used to filter groups based on aggregate calculations, similar to WHERE but for grouped records.
Using DISTINCT to Avoid Duplicates:
DISTINCT removes duplicate rows from the result set, ensuring each row is unique based on the selected columns.
Combining Results with UNION:
UNION combines the results of two or more SELECT statements into a single result set, removing duplicates by default.
Subqueries within SELECT:
Subqueries are nested queries within a main query, often used for complex filtering or to compute derived values.
Console Output:
John Doe, Jane Smith, etc.
Basic SELECT Statement
The basic SELECT statement is used to query data from a database. You specify the columns you want to retrieve and the table from which to retrieve them.
The SELECT statement can be as simple as selecting all columns from a table or more complex with conditions and joins.
SELECT * FROM employees;
This example retrieves all columns from the 'employees' table. The asterisk (*) is a wildcard character that selects all columns.
Console Output:
Employee data displayed here...
SELECT Specific Columns
This helps in fetching only the necessary data, improving query performance and reducing resource usage.
SELECT first_name, last_name FROM employees;
This query returns only the 'first_name' and 'last_name' columns from the 'employees' table.
Console Output:
John Doe, Jane Smith...
Using WHERE Clause
Conditions can include comparisons, pattern matching, and logical operations.
SELECT * FROM employees WHERE department = 'Sales';
This query retrieves all employees who work in the 'Sales' department.
Console Output:
Sales employee data...
Using ORDER BY Clause
Sorting can be done in ascending (ASC) or descending (DESC) order.
SELECT * FROM employees ORDER BY last_name ASC;
This query sorts the employees by their last names in ascending order.
Console Output:
Sorted employee list...
Using LIMIT Clause
It is useful for pagination or when you need just a few records.
SELECT * FROM employees LIMIT 10;
This query retrieves the first 10 rows from the 'employees' table.
Console Output:
First 10 employee records...
Combining WHERE and ORDER BY
This allows for refined data retrieval according to specific conditions and order preferences.
SELECT * FROM employees WHERE department = 'IT' ORDER BY hire_date DESC;
This query retrieves employees from the 'IT' department and sorts them by their hire date in descending order.
Console Output:
IT department employees sorted by hire date...
Using DISTINCT Keyword
It is useful when you want to ensure that each row in the result is unique.
SELECT DISTINCT department FROM employees;
This query returns a list of unique departments from the 'employees' table.
Console Output:
Unique department names...
Using JOIN Clauses
Different types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
SELECT employees.first_name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
This query retrieves employee names along with their department names using an INNER JOIN.
Console Output:
Employee and department names...
Using Aggregate Functions
They are commonly used with the GROUP BY clause to group the result set by one or more columns.
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
This query counts the number of employees in each department.
Console Output:
Department-wise employee count...
