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 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:
Example of SQL Select DatabaseUSE 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 DatabaseThe 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 53000Explanation: Retrieves all columns and all rows from the employees table.
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 38Explanation: Retrieves only the name and age columns for all rows.
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 38Explanation: Retrieves names and ages of employees older than 35.
4. Sorting Results withORDER 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 28Explanation: Retrieves names and ages of all employees sorted by age in descending order.
5. Limiting Results withLIMIT 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 60000Explanation: Retrieves the top 3 highest-paid employees, ordered by salary in descending order.
6. Aggregating Data withGROUP 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 50500Explanation: Calculates the average salary for each department.
ConclusionThe 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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4