SQL Select Database
The USE DATABASE statement is a command in certain SQL-based database management systems that allows users to select and set a specific database as the default for the current session. By selecting a database, subsequent queries are executed within the context of that database, making it easier to interact with tables and other objects contained within it.
Additionally, the SELECT
statement in SQL is used to query and retrieve data from the tables within the selected database. In this article, We will learn about SQL Select Database by understanding various examples in detail and so on.
The USE DATABASE Statement
The USE DATABASE
statement is not a standard SQL command, but rather a variation of the USE
command used in some SQL database management systems (DBMS) to select a specific database for the current session. Once the database is selected, subsequent queries are executed within the context of that database, allowing you to interact with its tables and objects more efficiently. This command sets the default database for subsequent queries in that session.
Important Note: In some DBMS, such as PostgreSQL, the USE command is not supported, and you need to connect to the database at the time of establishing the connection, rather than using the USE command.
Syntax:
USE database_name;
Example of SQL Select Database
Let’s take a look at how to select a database in SQL, using MySQL as an example. Suppose you have a database called company_db that contains employee information.
1. Create a Database: To begin, you can create a new database if it doesn’t already exist
CREATE DATABASE GeeksforGeeks;
2. Select the Database: To set GeeksforGeeks as the active database, use the USE command
USE GeeksforGeeks;
Once you’ve selected the database, any queries you execute will be performed within the context of GeeksforGeeks until you select another database.
How to Query Data from the Selected Database
The SELECT statement in SQL is used to query and retrieve data from the tables within the selected database. Here are some key ways to use the SELECT statement effectively. Consider the following table, employees as an example:
Table: employees
id | name | age | department | salary |
---|---|---|---|---|
1 | Alice | 30 | Sales | 50000 |
2 | Bob | 40 | Marketing | 60000 |
3 | Charlie | 35 | Sales | 55000 |
4 | David | 28 | HR | 45000 |
5 | Eve | 45 | Marketing | 65000 |
6 | Frank | 50 | HR | 70000 |
7 | Grace | 29 | IT | 48000 |
8 | Hannah | 38 | IT | 53000 |
1. Basic SELECT
Statement
The most basic form of a query is the SELECT statement. It is used to retrieve all columns and rows from a table.
SELECT * FROM employees;
Output:
id | name | age | department | salary |
---|---|---|---|---|
1 | Alice | 30 | Sales | 50000 |
2 | Bob | 40 | Marketing | 60000 |
3 | Charlie | 35 | Sales | 55000 |
4 | David | 28 | HR | 45000 |
5 | Eve | 45 | Marketing | 65000 |
6 | Frank | 50 | HR | 70000 |
7 | Grace | 29 | IT | 48000 |
8 | Hannah | 38 | IT | 53000 |
Explanation: Retrieves all columns and all rows from the employees
table.
2. Selecting Specific Columns
We can select specific columns instead of retrieving all columns.
SELECT name, age FROM employees;
Output:
name | age |
---|---|
Alice | 30 |
Bob | 40 |
Charlie | 35 |
David | 28 |
Eve | 45 |
Frank | 50 |
Grace | 29 |
Hannah | 38 |
Explanation: Retrieves only the name
and age
columns for all rows.
3. Filtering Results with WHERE
The WHERE clause filters the records based on a specified condition.
SELECT name, age FROM employees WHERE age >= 35;
Output:
name | age |
---|---|
Bob | 40 |
Charlie | 35 |
Eve | 45 |
Frank | 50 |
Hannah | 38 |
Explanation: Retrieves names and ages of employees older than 35.
4. Sorting Results with ORDER BY
The ORDER BY clause sorts the result set based on one or more columns.
SELECT name, age FROM employees ORDER BY age DESC;
Output:
name | age |
---|---|
Frank | 50 |
Eve | 45 |
Bob | 40 |
Hannah | 38 |
Charlie | 35 |
Alice | 30 |
Grace | 29 |
David | 28 |
Explanation: Retrieves names and ages of all employees sorted by age in descending order.
5. Limiting Results with LIMIT Clause
The LIMIT clause restricts the number of rows returned.
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;
Output:
name | salary |
---|---|
Frank | 70000 |
Eve | 65000 |
Bob | 60000 |
Explanation: Retrieves the top 3 highest-paid employees, ordered by salary in descending order.
6. Aggregating Data with GROUP BY
and Aggregation Functions
The GROUP BY clause groups rows that have the same values into summary rows, often used with aggregation functions like AVG, COUNT, MAX, MIN, and SUM.
SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;
Output:
department | average_salary |
---|---|
Sales | 52500 |
Marketing | 62500 |
HR | 57500 |
IT | 50500 |
Explanation: Calculates the average salary for each department.
Conclusion
The USE DATABASE statement and the SELECT query are fundamental parts of working with SQL databases. By selecting a database, you set the context for all subsequent queries within that session. The SELECT statement, combined with filtering, sorting, and aggregation, allows you to efficiently retrieve and manipulate data stored in your database. Understanding these commands and their syntax will help you create powerful queries and streamline data retrieval processes.