Home / Database

Create New Data Table From Select Statement Results

SELECT masv,hodem,name,CASE gioitinh WHEN 1 THEN 'Nam' ELSE

'Female' END AS gioitinh FROM sinhvien

or

SELECT name, address, name, CASE WHEN gioitinh=1 THEN 'Male' ELSE 'Female' END AS gioitinh FROM sinhvien

The results of the two statements above are in the following form:


Constants and expressions in the select list

In addition to the field list, expressions can also be used in the select list of the SELECT statement. Each expression in the select list becomes a column in the query result.

The following statement gives the names and number of periods of the subjects.

SELECT namemonhoc,sodvht*15 AS sotiet FROM monhoc




presently

If a constant value is present in the select list, it will appear in a column of the query result in all rows.

Command

SELECT subject name, number of periods:', period number*15 AS period number FROM subject

gives the following results:


Remove duplicate data rows in query results

In the query results, duplicate data rows may appear. To remove these rows, we specify the DISTINCT keyword right after the SELECT keyword.

The two statements below SELECT khoa FROM lop And

SELECT DISTINCT faculty FROM class

The results are as follows:


Limit the number of rows in query results

The query results displayed will usually be all the rows of data that are retrieved. In case you need to limit the number of rows that appear in the query results, you specify the TOP clause right before the select list of the SELECT statement.

The following statement displays the names and dates of birth of the first 5 students in the list.

SELECT TOP 5 host, name, student date FROM student

In addition to specifying the specific number of rows to display in the query results, we can specify the number of rows to display as a percentage by using the PERCENT keyword as in the example below.

The following statement displays the full name and date of birth of 10% of the current number of students in the SINHVIEN table.

SELECT TOP 10 PERCENT hodem, name, birthday FROM student

Specify data query conditions

The WHERE clause in the SELECT statement is used to specify the conditions for retrieving data. The WHERE clause is followed by a logical expression and only those data rows that satisfy the specified conditions are displayed in the query results.

The following statement displays the list of subjects with the number of credits greater than 3 SELECT * FROM monhoc WHERE sodvht>3

The result of this command is as follows:


In the WHERE clause often used:

• Conditional combination operators (AND, OR)

• Comparison operators

• Check data limits (BETWEEN/ NOT BETWEEN)

• List

• Check data format.

• NULL values

Comparison operators


Death Note

Meaning

=

Equal

>

Bigger

<

Smaller

>=

Greater than or equal to

<=

Less than or equal to

<>

other

!>

Not greater than

!<

Not less than

Maybe you are interested!


Command:

SELECT masv,hodem,name,,birthday FROM student WHERE (name='Anh') AND (YEAR(GETDATE())-YEAR(birthday)<=20)

Give the code, full name and date of birth of students whose name is Anh and whose age is less than or equal to 20.


Check data limits

To check if a data value is within (outside) a certain range, we use the BETWEEN (NOT BETWEEN) operator as follows:

How to use Meaning

value BETWEEN a AND ba ≤ value ≤ b value NOT BETWEEN a AND b (value < a) AND (value>b)

The following statement gives the full name and age of students whose name is Binh and whose age is between 20 and 22.

SELECT hodem,name,year(getdate())-year(date of birth) AS age

FROM student WHERE name ='Binh' AND YEAR(GETDATE())- YEAR(daysinh) BETWEEN 20 AND 22

List (IN and NOT IN)

The IN keyword is used when we need to specify the search condition for the SELECT statement as a list of values. After IN (or NOT IN) can be a list of values ​​or another SELECT statement.

To get a list of courses with 2, 4 or 5 credits, instead of using the command

SELECT * FROM monhoc WHERE sodvht=2 OR sodvht=4 OR sodvht=5 we can use the statement SELECT * FROM monhoc WHERE sodvht IN (2,4,5)

LIKE Operator and Wildcards

The LIKE (NOT LIKE) keyword is used in the SELECT statement to describe the format of the data to be searched. It is often combined with the following wildcard characters


Wildcard

Meaning

%

Any string of zero or more characters

-

Any single character

[]

Any single character within a specified range (e.g. [af]) or set (e.g. [abcdef])

[^]

Any single character not within the specified range (e.g. [^af]) or a set (e.g. [^abcdef]).


The command below

SELECT hodem,name FROM student WHERE hodem LIKE 'Le%'

given the names of students whose last name is Le and the results are as follows




AND

Command:

SELECT hodem,name FROM student WHERE hodem LIKE 'Le%' name LIKE '[AB]%'

The result is:


NULL value

Data in a column that allows NULL will receive the value NULL in the following cases:

• If no data is entered for the column and there is no default for the column or data type on that column.

• The user directly enters a NULL value into that column.

• A column with a numeric data type will contain a NULL value if the specified value causes a numeric overflow.

In the WHERE clause, to check if a column's value has a NULL value or not, we use the following writing method:

WHEREcolumn_nameIS NULL

Or :

WHERE column_name IS NOT NULL

1.1.3 Create a new data table from the results of the SELECT statement

The SELECT ... INTO statement creates a new table whose structure and data are determined from the results of the query. The newly created table will have the same number of columns as the specified columns.

specified in the select list and the row number will be the number of rows of the query result. The following statement queries data from the SINHVIEN table and creates a TUOISV table consisting of the fields HODEM, TEN and TUOI

SELECT hodem,name,YEAR(GETDATE())YEAR(birthday) AS age INTO age FROM student

Note: If there are expressions in the selection list, these expressions must be titled.

1.1.4 Sort query results

By default, the rows of data in the query result follow their order in the data table or are sorted by index (if the table has an index). In case you want the data to be sorted in ascending or descending order of the value of one or more fields, you use the ORDER BY clause in the SELECT statement; After ORDER BY is a list of columns to be sorted (maximum 16 columns). The data can be sorted in ascending (ASC) or descending (DESC) order, the default is to sort in ascending order.

The following statement displays the list of subjects and sorts them in descending order of

number of credits

SELECT * FROM module ORDER BY sodvht DESC


If there are multiple columns after ORDER BY, the data will be sorted in order from left to right.

Command

SELECT hodem,name,sex YEAR(GETDATE())- YEAR(birth date)

AS age FROM student WHERE name='Binh' ORDER BY gender,age

The result is:


Instead of specifying the column name after ORDER BY, we can specify the column number to be sorted. The statement in the above example can be rewritten as follows:

SELECT hodem,name,sex YEAR(GETDATE())- YEAR(birth date)

AS age FROM student WHERE name ='Binh' ORDER BY 3, 4

1.1.5 Union

Union is used when we need to combine the results of two or more queries into a single result set. SQL provides the UNION operator to perform union. The syntax is as follows

Command_1 UNION [ALL] Command_2 [UNION [ALL] Command_3]

...

[UNION [ALL] Command_n] [ORDER BY sort_column] [COMPUTE merge_function_list [BY column_list]]

In there

Command_1 has the form

SELECT column_list [INTO new_table_name] [FROM table_list|view] [WHERE condition] [GROUP BY column_list] [HAVING condition]

and

Statement_i (i = 2,..,n) has the form

SELECT column_list [FROM table_list|view] [WHERE condition] [GROUP BY column_list] [HAVING condition]

Suppose we have two tables Table1 and Table2 as follows:


command

SELECT A,B FROM Table1 UNION SELECT D,E FROM table2

The result is as follows:


By default, if the same data rows appear in the component queries of a union, only one row will be kept in the query result. If you want to keep these rows, you must use the ALL keyword in the component query.

Command

SELECT A,B FROM Talbe1 UNION ALL SELECT D,E FROM

table2

Gives the following results

Comment


Agree Privacy Policy *