SQL Server Tutorial: SQL syntax

Select Purpose:

Removes the specified column's data from the specified table

Grammar:

SELECT column_name (s) from table_name

Explain:

Select a row from the database and allow one or more columns or rows to be selected from one or more tables. The complete syntax for a SELECT statement is quite complex, but the main clause can be summarized as:

SELECT select_list
[Into new_table]
From Table_source
[WHERE search_condition]
[GROUP by Group_by_expression]
[Having search_condition]
[Order by Order_expression [ASC DESC]]

Cases:

The data in the "Persons" table has

LastName
FirstName
Address
City

Ola
TIMOTEIVN 10
Sandnes

Svendson
Tove
BORGVN 23
Sandnes

Pettersen
Kari
STORGT 20
Stavanger

Select the field name "LastName", "FirstName" data

SELECT Lastname,firstname from Persons

return Result:

LastName
FirstName

Hansen
Ola

Svendson
Tove

Pettersen
Kari

Select data for all fields

SELECT * from Persons

return Result:

LastName
FirstName
Address
City

Hansen
Ola
TIMOTEIVN 10
Sandnes

Svendson
Tove
BORGVN 23
Sandnes

Pettersen
Kari
STORGT 20
Stavanger

Where
Use:

is used to specify a criteria for selecting a query

Grammar:

SELECT column from table WHERE column condition value

Note: In some versions of SQL , no equal sign < > can be written as!=

The SELECT statement returns data with the condition true in the WHERE clause

Cases:

SELECT * from Persons WHERE city= ' Sandnes '

The data in the "Persons" table is:

LastName
FirstName
Address
City
Year

Hansen
Ola
TIMOTEIVN 10
Sandnes
1951

Svendson
Tove
BORGVN 23
Sandnes
1978

Svendson
Stale
KAIVN 18
Sandnes
1980

Pettersen
Kari
STORGT 20
Stavanger
1960

LastName

FirstName

Address

City

Year

Hansen

Ola

TIMOTEIVN 10

Sandnes

1951

Svendson

Tove

BORGVN 23

Sandnes

1978

Svendson

Stale

KAIVN 18

Sandnes

1980

and & Or
Use:

In the WHERE clause, and and or are used to connect two or more conditions

Explain:

And when combining two Boolean expressions, returns true only if two expressions are true

Or when a two Boolean expression is combined, or returns true whenever one of the conditions is true

Cases:

Raw data in the "Persons" table:
LastName
FirstName
Address
City

Hansen
Ola
TIMOTEIVN 10
Sandnes

Svendson
Tove
BORGVN 23
Sandnes

Svendson
Stephen
KAIVN 18
Sandnes

In the table

SELECT * from Persons
WHERE firstname= ' Tove '
and Lastname= ' Svendson '

LastName

FirstName

Address

City

Svendson

Tove

BORGVN 23

Sandnes

operator to find data in a table that is firstnamelastname as "Svendson"

SELECT * from Persons
WHERE firstname= ' Tove '
OR lastname= ' Svendson '

LastName

FirstName

Address

City

Svendson

Tove

BORGVN 23

Sandnes

Svendson

Stephen

KAIVN 18

Sandnes

You can also combine and and or (use parentheses to form complex expressions), such as:

SELECT * from Persons WHERE
(Firstname= ' Tove ' OR firstname= ' Stephen ')
and Lastname= ' Svendson '

LastName

FirstName

Address

City

Svendson

Tove

BORGVN 23

Sandnes

Svendson

Stephen

KAIVN 18

Sandnes

Between ... and
Use:

Specify the range to return data to

Grammar:

SELECT COLUMN_NAME from table_name
WHERE column_name
BETWEEN value1 and value2

Cases:

"Persons"

LastName
FirstName
Address
City

Hansen
Ola
TIMOTEIVN 10
Sandnes

Nordmann
Anna
Neset 18
Sandnes

Pettersen
Kari
STORGT 20
Stavanger

Svendson
Tove
BORGVN 23
Sandnes

Returns the data lastname from "Hansen" to "Pettersen":

SELECT * from Persons WHERE LastName
BETWEEN ' Hansen ' and ' Pettersen '

return Result:

LastName
FirstName
Address
City

Hansen
Ola
TIMOTEIVN 10


Nordmann
Anna
Neset 18
Sandnes

Pettersen
Kari
STORGT 20
Stavanger

To display data outside the specified range, you can also use the NOT operator:

SELECT * from Persons WHERE LastName
Not BETWEEN ' Hansen ' and ' Pettersen '

return Result:

LastName
FirstName
Address
City

Svendson
Tove
BORGVN 23
Sandnes

Distinct
Use:

The DISTINCT keyword is used to return a unique value

Grammar:

SELECT DISTINCT Column-name (s) from Table-name

Explain:

When duplicate values exist in Column-name (s), the returned result leaves only one

Cases:

Raw data in the Orders table

Company
OrderNumber

Sega
3412

W3Schools
2312

Trio
4678

W3Schools
6798

Returns the unique value in the Company field with the DISTINCT keyword:

SELECT DISTINCT Company from Orders

return Result:

Company

Sega

W3Schools

Trio

ORDER BY
Use:

Specifying ordering of result sets

Grammar:

SELECT Column-name (s) from Table-name order by {order_by_expression [ASC DESC]}

Explain:

Specifies the sort of result set, which can be sorted by ASC (incrementally sorted, from lowest to highest) or desc (descending sort, from highest to lowest), by the ASC

Cases:

Raw data in the Orders table:

Company
OrderNumber

Sega
3412

ABC Shop
5678

W3Schools
2312

W3Schools
6798

Returns the result set in ascending order of the company field:

SELECT Company, OrderNumber from Orders
ORDER BY Company

return Result:

Company
OrderNumber

ABC Shop
5678

Sega
3412

W3Schools
6798

W3Schools
2312

Returns the result set in descending order of the company field:

SELECT Company, OrderNumber from Orders
ORDER BY Company DESC

return Result:

Company
OrderNumber

W3Schools
6798

W3Schools
2312

Sega
3412

ABC Shop
5678

Group by
Use:

Groups The result set, often used with summary functions.

Grammar:

SELECT column,sum (column) from table GROUP by column

Cases:

Raw data in the Sales table:

Company
Amount

W3Schools
5500

Ibm
4500

W3Schools
7100

Total of Amout:

SELECT Company,sum (Amount) from Sales
GROUP by Company

return Result:

Company
SUM (Amount)

W3Schools
12600

Ibm
4500

Having
Use:

Specify the search criteria for the group or Rollup.

Grammar:

SELECT column,sum (column) from table
GROUP by column
Having SUM (column) condition value

Explain:

Having is usually used concurrently with the GROUP by clause. When you do not use GROUP by, the having is similar to the WHERE clause functionality.

Cases:

Raw data in the Sales table:

Company
Amount

W3Schools
5500

Ibm
4500

W3Schools
7100

Group BY Company field to find out the total of amout for each company in 10000

SELECT Company,sum (Amount) from Sales
GROUP by company has SUM (Amount) >10000
return Result:

Company
SUM (Amount)

W3Schools
12600

Join
Use:

When you select a result set from two or more tables, you use a join.

Cases:

The data in the "Employees" table is as follows, where the ID

Id

Name

01

Hansen, Ola

02

Svendson, Tove

03

Svendson, Stephen.

04

Pettersen, Kari

The data in the Orders table is as follows:

Id
Product

01
Printer

03
Table

03
Chair

Select the data with the ID of the employees and the ID of the orders:

SELECT Employees.name, Orders.product
From Employees, Orders
WHERE employees.id = orders.id

Name

Product

Hansen, Ola

Printer

Svendson, Stephen.

Table

Svendson, Stephen.

Chair

Or you can use the Join keyword to do the above operation:

SELECT Employees.name, Orders.product
From Employees
INNER JOIN Orders
On employees.id = Orders.id

Syntax for INNER join:

SELECT field1, Field2, field3
From first_table
INNER JOIN second_table
On First_table.keyfield = Second_table.foreign_keyfield

Explain:

The result set returned by the INNER join is all the matching data in the two tables.

Syntax for LEFT join:

SELECT field1, Field2, field3
From first_table
Left JOIN second_table
On First_table.keyfield = Second_table.foreign_keyfield

Use the "Employees" table to the left outer join "Orders" table to find the relevant data:

SELECT Employees.name, Orders.product
From Employees
Left JOIN Orders
On employees.id = Orders.id

return Result:

Name
Product

Hansen, Ola
Printer

Svendson, Tove


Svendson, Stephen.
Table

Svendson, Stephen.
Chair

Pettersen, Kari

Explain:

The LEFT join returns all rows in "first_table", although there is no matching data in "second_table".

The syntax for right join:

SELECT field1, Field2, field3
From first_table
Right JOIN second_table
On First_table.keyfield = Second_table.foreign_keyfield

Use the "Employees" table to connect the "Orders" table to the right outside to find the relevant data:

SELECT Employees.name, Orders.product
From Employees
Right JOIN Orders
On employees.id = Orders.id

return Result:

Name
Product

Hansen, Ola
Printer

Svendson, Stephen.
Table

Svendson, Stephen.
Chair

Explain:

Right join returns all rows in "second_table", although there is no matching data in "first_table".

Alias
Use:

Can be used on tables, result sets, or columns, and take a logical name for them

Grammar:

To alias a column:

SELECT column as Column_alias from table

To alias a table:

SELECT column from table as Table_alias

Cases:

Raw data in the "Persons" table:

LastName
FirstName
Address
City

Hansen
Ola
TIMOTEIVN 10
Sandnes

Svendson
Tove
BORGVN 23
Sandnes

Pettersen
Kari
STORGT 20
Stavanger

Run the following SQL:

SELECT LastName as Family, FirstName as Name
From Persons

return Result:

Family
Name

Hansen
Ola

Svendson
Tove

Pettersen
Kari

Run the following SQL:

SELECT LastName, FirstName
From Persons as Employees

return Result:

The data in the employees are:

LastName
FirstName

Hansen
Ola

Svendson
Tove

Pettersen
Kari

Insert into
Use:

Insert a new row in a table

Grammar:

Insert a row of data

INSERT into table_name
VALUES (value1, value2,....)

Inserts a row of data on the specified field

INSERT into table_name (column1, Column2,...)
VALUES (value1, value2,....)

Cases:

Raw data in the "Persons" table:

LastName
FirstName
Address
City

Pettersen
Kari
STORGT 20
Stavanger

Run the following SQL to insert a row of data:

INSERT into Persons
VALUES (' Hetland ', ' Camilla ', ' Hagabakka ', ' Sandnes ')

The data in the "Persons" table after inserting is:

LastName
FirstName
Address
City

Pettersen
Kari
STORGT 20
Stavanger

Hetland
Camilla
Hagabakka 24
Sandnes

Run the following SQL inserts a row of data on the specified field:

INSERT into Persons (LastName, address)
VALUES (' Rasmussen ', ' STORGT 67 ')

The data in the "Persons" table after inserting is:

LastName
FirstName
Address
City

Pettersen
Kari
STORGT 20
Stavanger

Hetland
Camilla
Hagabakka 24
Sandnes

Rasmussen

STORGT 67

Update
Use:

Update existing data in the table

Grammar:

UPDATE table_name SET COLUMN_NAME = New_value
WHERE column_name = some_value

Cases:

Raw data in the person table:

LastName
FirstName
Address
City

Nilsen
Fred
Kirkegt 56
Stavanger

Rasmussen

STORGT 67

Run the following SQL to lastname the person table

UPDATE person SET FirstName = ' Nina '
WHERE LastName = ' Rasmussen '
The data in the person table after the update is:

LastName
FirstName
Address
City

Nilsen
Fred
Kirkegt 56
Stavanger

Rasmussen
Nina
STORGT 67

Similarly, you can update multiple fields at the same time with the UPDATE statement:

UPDATE person
SET address = ' Stien ', city = ' Stavanger '
WHERE LastName = ' Rasmussen '

The data in the person table after the update is:

LastName
FirstName
Address
City

Nilsen
Fred
Kirkegt 56
Stavanger

Rasmussen
Nina
Stien 12
Stavanger

Delete
Use:

Delete data from a table

Grammar:

DELETE from table_name WHERE COLUMN_NAME = some_value

Cases:

Raw data in the person table:

LastName
FirstName
Address
City

Nilsen
Fred
Kirkegt 56
Stavanger

Rasmussen
Nina
Stien 12
Stavanger

Delete data in the person table that is lastname as "Rasmussen":

DELETE from person WHERE LastName = ' Rasmussen '

The data in the person table after the DELETE statement is executed is:

LastName
FirstName
Address
City

Nilsen
Fred
Kirkegt 56
Stavanger

Create Table
Use:

Create a new table of data.

Grammar:

CREATE TABLE table_name
(
Column_name1 Data_type,
Column_name2 Data_type,
.......
)

Cases:

Create a table called "person", which has 4 fields "LastName", "FirstName", "Address", "age"

CREATE TABLE Person
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)
If you want to specify the maximum storage length for a field, you can do this:

CREATE TABLE Person
(
LastName varchar (30),
FirstName varchar (30),
Address varchar (120),
Age Int (3)
)

Some of the data types in SQL are listed in the following table:

Data Type
Description

Integer (size)
int (size)
smallint (size)
tinyint (size)

Hold integers only. The maximum number of digits are specified in parenthesis.

Decimal (SIZE,D)
Numeric (SIZE,D)
Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "D".

char (size)
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.

varchar (size)
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.

Date (YYYYMMDD)
Holds a date

Alter Table
Use:

Add the latter to the already existing table remove the field

Grammar:

ALTER TABLE table_name
ADD column_name datatype
ALTER TABLE table_name
DROP COLUMN column_name

Note: Some database management systems do not allow the removal of fields from tables

Cases:

Raw data in the person table:

LastName
FirstName
Address

Pettersen
Kari
STORGT 20

Add a field named City to the person table:

ALTER TABLE person ADD city varchar (30)

The data in the following table is as follows:

LastName
FirstName
Address
City

Pettersen
Kari
STORGT 20

Remove the Address field from the person table:

ALTER TABLE person DROP COLUMN Address

The following data is removed from the following table:

LastName
FirstName
City

Pettersen
Kari

Drop Table
Use:

Removes a data table definition in the database and all data, indexes, triggers, conditional constraints, and permission designations in the datasheet.

Grammar:

DROP TABLE table_name

Create Database
Use:

Create a new database

Grammar:

CREATE DATABASE database_name

Drop Database
Use:

Removing an existing database

Grammar:

DROP DATABASE database_name

Aggregate functions
Count
Use:

Returns the number of rows in the selected result set.

Grammar:

SELECT COUNT (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name
Age

Hansen, Ola
34

Svendson, Tove
45

Pettersen, Kari
19

Select Total Records:

SELECT COUNT (Name) from Persons

Execution results:

3

Sum
Use:

Returns the sum of all the values in an expression, or only the DISTINCT value. SUM is available only for numeric data rows. Null value has been ignored.

Grammar:

SELECT SUM (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name
Age

Hansen, Ola
34

Svendson, Tove
45

Pettersen, Kari
19

Select the sum of everyone's age in the Persons table:

SELECT SUM (age) from Persons

Execution results:

98

Select the sum of the ages of persons over 20 years of age in the Persons table:

SELECT SUM (age) from Persons WHERE age>20

Execution results:

79

Avg
Use:

Returns the average of the values in the selected result set. Null value has been ignored.

Grammar:

SELECT AVG (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name
Age

Hansen, Ola
34

Svendson, Tove
45

Pettersen, Kari
19

Select the average age of everyone in the "Persons" table:

SELECT AVG (age) from Persons

Execution results:

32.67

Select the average age of persons over 20 years of age in the "Persons" table:

SELECT AVG (age) from Persons WHERE age>20

Execution results:

39.5

Max
Use:

Returns the maximum value for the selected result set. Null value has been ignored.

Grammar:

SELECT MAX (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name
Age

Hansen, Ola
34

Svendson, Tove
45

Pettersen, Kari
19

Select the maximum age in the Persons table:

SELECT MAX (age) from Persons

Execution results:

45

Min
Use:

Returns the minimum value for the value in the selected result set. Null value has been ignored.

Grammar:

SELECT MIN (column_name) from table_name

Cases:

The original data in the "Persons" table is as follows:

Name
Age

Hansen, Ola
34

Svendson, Tove
45

Pettersen, Kari
19

Select the minimum age in the Persons table:

SELECT MIN (age) from Persons

Execution results:

19

Arithmetic functions
Abs
Use:

Returns the absolute positive value of the specified numeric expression (Numeric Expression).

Grammar:

ABS (numeric_expression)

Cases:

1.0 0.0 1.0

Ceil
Use:

Returns the smallest integer greater than or equal to the given numeric expression.

Grammar:

Ceil (numeric_expression)

Cases:

124.00-123.00

Floor
Use:

Returns the largest integer less than or equal to the given numeric expression.

Grammar:

FLOOR (numeric_expression)

Cases:

123.00-124.00

Cos
Use:

Returns the mathematical function of the trigonometric cosine of the specified angle (in 弪) in the specified expression.

Grammar:

COS (numeric_expression)

Cases:

-0.599465

Cosh
Use:

Returns the angle value in radians, and the rest of the string is the specified float expression, also known as the inverse cosine.

Grammar:

COSH (numeric_expression)

Cases:

3.14159

Sin
Use:

Returns the trigonometric sine function (trigonometric Sine) of the given angle (in radians) with an approximate value (float) expression.

Grammar:

SIN (numeric_expression)

Cases:

0.929607

Sinh
Use:

Returns the angle in 弪, whose sine is the specified float expression (also known as the inverse chord).

Grammar:

SINH (numeric_expression)

Cases:

-1.5708

Tan
Use:

Returns the tangent function of an input expression.

Grammar:

TAN (numeric_expression)

Cases:

1.6331778728383844E+16

Tanh
Use:

Returns the angle in 弪, whose tangent is the specified float expression (also known as tangent).

Grammar:

TANH (numeric_expression)

Cases:

-1.54858

Exp
Use:

Returns the exponent (exponential) value of the given float expression.

Grammar:

EXP (numeric_expression)

Cases:

2.69498e+164

Log
Use:

Returns the natural logarithm of the given float expression.

Grammar:

LOG (numeric_expression)

Cases:

1.64396

Power
Use:

Returns a given expression specifying the value of the power.

Grammar:

Power (NUMERIC_EXPRESSION,V)

Cases:

64

Sign
Use:

Returns the positive (+1), 0 (0), or minus (-1) Number of the given expression.

Grammar:

SIGN (numeric_expression)

Cases:

1 0-1

sqrt
Use:

Returns the square of the given expression.

Grammar:

SQRT (numeric_expression)

Cases:

SQRT (10)

Execution results:

100