SQL Select Database

The USE DATABASE statement in SQL sets a specific database as the default for the current session. It helps users run queries and manage objects within that selected database.

• Used to select and activate a database for the current session.
• Ensures all queries run within the chosen database context.
• Not a part of standard SQL, but supported in systems like MySQL and SQL Server.
• Often used before running commands such as SELECT to retrieve data.

Syntax:

USE database_name;

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

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:

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:

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:

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:

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:

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:

Explanation: Calculates the average salary for each department.

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.