Thứ Tư, Tháng Hai 12, 2025
spot_img
HomeSELECT command in SQL Server

SELECT command in SQL Server

SELECT statement in SQL Server How is it used? Let's find out with Quantrimang.com what you need to know select in SQL Server okay.

If you are learning SQL Server, you should definitely master basic functions or statements, including SELECT.

SELECT function in SQL Server

Database tables are objects that contain all the data in the database. In a table, data is arranged logically in a row and column format, similar to a spreadsheet.

Each row represents a unique record in the table, and each column represents a field in the record. For example, the customers table contains customer data such as customer identification number, first name, last name, phone, email and address information as shown below:

Customers table in SQL Server

SQL Server uses schemas to logically group tables and other database objects. In the sample database above, we have two schemas: sales and production. Schema sales groups all tables related to sales, while schema production groups all tables related to production.

To query data from a table, you use SELECT statements in SQL Server. Here's everything you need to know about the SQL Server SELECT statement.

Things to know when using select in SQL Server:

  • Vast amounts of data are available everywhere.
  • Data is stored sequentially in table form.
  • Each table consists of a row representing a single record and a column representing a field.
  • Schemas – Schemas are used to arrange tables in a logical order.
  • To extract specific data from the table, queries written in SQL are used.
  • To query data, use the select command.

Basic syntax of select command in SQL Server

select
select_list;
from
schema_name.table.name;

To retrieve all columns from a table, select in SQL Server is used.

Syntax -
select*
from
table_name;

Points to remember:

  • In real-time databases, the select command is not recommended because it retrieves more data than you require.
  • As a result, it causes the application to run slowly.
  • In case, the user adds more columns to the table, the select command retrieves the entire column, including the new column, causing the application to crash.

In simple form, the syntax of the SELECT command is as follows:

SELECT “biểu thức”
 FROM “bảng”
 [WHERE “điều kiện”];

In its full form, the syntax of the SELECT statement in SQL Server would be:

SELECT [ ALL | DISTINCT ]
[ TOP (gia_tri_dau) [ PERCENT ] [ WITH TIES ] ]
“Biểu thức”
FROM “bảng”
[WHERE “điều kiện”]
[GROUP BY “biểu thức”]
[HAVING “điều kiện”]
[ORDER BY “biểu thức” [ ASC | DESC ]];

Variable name or variable value:

ALL: Optional, return all matching rows.

DISTINCT: Optional. Remove all duplicate values ​​from the result set.

TOP (price_price): Optional. If specified will return the top values ​​in the based result set price_value has chosen. For example, TOP(10) will return the first 10 rows in the result set.

PERCENT: Optional. If specified, the first rows are based on a percentage of the result set (specify equals price_value). For example, TOP(10) PERCENT will return the top 10% of results in the result set.

WITH TIES: Optional. If specified, fixed rows at the end of the bounded result will be returned. This may cause more rows to be returned than the TOP variable allows.

Expression: Column or calculated value you want to retrieve. Use * if you want to get all columns.

Board: Table from which you want to retrieve results. There must be at least 1 table listed in the FROM statement.

WHERE “condition”: Optional. The condition that the returned result must meet.

GROUP BY “expression”: Optional. Collect data from multiple records and group the results by one or more columns.

HAVING “condition”: Optional. Use in combination with GROUP BY to limit the group of rows returned when the condition is met as TRUE.

ORDER BY “expression”: Optional. Used to filter the result set. ASC will filter in ascending order and DESC will filter in descending order.

Example of SELECT in SQL Server

Suppose we have a data table Quantrimang.com as follows:


+-------------+--------------+-------------+-------+
|IDChuyenmuc  |Muccon        |Chuyenmuclon |Sobai  |
+-------------+--------------+-------------+-------+
|    1        |SQL Server    |Lap trinh    |101    |
|    2        |Facebook      |Mang xa hoi  |152    |
|    3        |Python        |Lap trinh    |111    |
|    4        |JavaScript    |Lap trinh    |122    |
|    5        |Chrome        |Web          |94     |
|    6        |Instagram     |Mang xa hoi  |165    |
+-------------+--------------+-------------+-------+

For example, use SELECT to select all fields in a table

SELECT * FROM [Quantrimang.com]
  WHERE Sobai > 111
  ORDER BY Sobai ASC;

In this example, * is used to indicate that all available fields will be selected Sobai greater than 111 in the table Quantrimang.com. The result set is sorted in ascending order of Sobai.


+-------------+--------------+-------------+-------+
|IDChuyenmuc  |Muccon        |Chuyenmuclon |Sobai  |
+-------------+--------------+-------------+-------+
|    4        |JavaScript    |Lap trinh    |122    |
|    2        |Facebook      |Mang xa hoi  |152    |
|    6        |Instagram     |Mang xa hoi  |165    |
+-------------+--------------+-------------+-------+

For example, use SELECR to select some fields in a table

SELECT IDChuyenmuc, Chuyenmuclon, Sobai
  FROM [Quantrimang.com]
  WHERE IDChuyenmuc > 2
  AND Chuyenmuclon = 'Lap trinh'
  Order By Sobai DESC;

In the above example, the returned results will only include ID Chuyenmuc, Chuyenmuclon, Sobai with IDChuyenmuc greater than 2 and Chuyenmuclon is 'Lap virgin'. Returned results are sorted by Sobai descending as below:


+-------------+-------------+-------+
|IDChuyenmuc  |Chuyenmuclon |Sobai  |
+-------------+-------------+-------+
|    4        |Lap trinh    |122    |
|    3        |Lap trinh    |111    |
+-------------+-------------+-------+

For example, using SELECT to select from multiple tables

To implement this example, we add a table Muc as follows:


+----+-----------+-------------+
| ID |Ten muc    |Trang thai   |
+----+-----------+-------------+
| 1  |Lap trinh  |Hien         |
| 2  |Mang xa hoi|An           |
| 3  |Web        |An           |
+----+-----------+-------------+

Suppose we need to get these Muccon Have Trangthai To be Hienand the records must satisfy Chuyenmuclon of table Quantrimang.com and Tenmuc of table Muc must match and the returned results are sorted in ASC order of the column Muccon.

SELECT [Quantrimang.com].Muccon,[Quantrimang.com].Chuyenmuclon,Muc.Trangthai
  FROM [Quantrimang.com]
  INNER JOIN Muc
  ON [Quantrimang.com].Chuyenmuclon=Muc.Tenmuc AND Muc.Trangthai= 'Hien'
  ORDER BY [Quantrimang.com].Muccon ASC;

The returned result is the following data table:


+----------+------------+----------+
|Muccon    |Chuyenmuclon|Trangthai |
+----------+------------+----------+
|Chrome    |Web         |Hien      |
|JavaScript|Lap trinh   |Hien      |
|Python    |Lap trinh   |Hien      |
|SQL Server|Lap trinh   |Hien      |
+----------+------------+----------+

For example, use SELECT with the keyword TOP

SELECT TOP(2) *
  FROM [Quantrimang.com]
  WHERE Chuyenmuclon='Lap trinh'
  ORDER BY IDChuyenmuc ASC;

In this example, the result will return the first 2 values ​​from the table Quantrimang.com with Chuyemuclon is 'Lap virgin'. Even if there are other values ​​that meet the requirements, they will not be returned.


+-------------+--------------+-------------+-------+
|IDChuyenmuc  |Muccon        |Chuyenmuclon |Sobai  |
+-------------+--------------+-------------+-------+
|    1        |SQL Server    |Lap trinh    |101    |
|    3        |Python        |Lap trinh    |111    |
+-------------+--------------+-------------+-------+

Example – Use the keyword TOP PERCENT

SELECT TOP(10) PERCENT *
  FROM [Quantrimang.com]
  WHERE Chuyenmuclon='Lap trinh'
  ORDER BY IDChuyenmuc ASC;

The returned result will include the first 10% of the entire result Chuyenmuclon is 'Lap virgin' in the table Quantrimang.com. The remaining 90% of results will not be returned.


+-------------+--------------+-------------+-------+
|IDChuyenmuc  |Muccon        |Chuyenmuclon |Sobai  |
+-------------+--------------+-------------+-------+
|    1        |SQL Server    |Lap trinh    |101    |
+-------------+--------------+-------------+-------+

Use DISTINCT with SELECT

The following example uses DISTINCT to prevent retrieval of duplicate headers:

USE AdventureWorks2012;
GO
SELECT DISTINCT JobTitle
FROM HumanResources.Employee
ORDER BY JobTitle;
GO

Create table with SELECT INTO

The first example creates a temporary table named #Bicycles in tempdb.

USE tempdb;
GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT * 
INTO #Bicycles
FROM AdventureWorks2012.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO

The second example creates a permanent table named NewProducts.

USE AdventureWorks2012;
GO
IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL
    DROP TABLE dbo.NewProducts;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
GO

SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25 
AND ListPrice 

Previous article: Analysis services in MS SQL Server

Next article: FROM clause in SQL Server

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments