SQL Detailed syntax Introduction--the most basic knowledge of learning a database

Data | database | Grammar Select
Use:

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

Hansen

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

The following operators can be used in the Where:

=,<>,>,<,>=,<=,between,like

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

Explain:

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

Cases:

Select people living in "Sandnes" from the "Persons" table

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

return Result:

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

Use the AND operator to find data that is FirstName "Tove" and LastName "Svendson" in the "Persons" table

SELECT * from Persons

WHERE firstname= ' Tove '

and Lastname= ' Svendson '

return Result:

LastName

FirstName

Address

City

Svendson

Tove

BORGVN 23

Sandnes

Use the OR operator to find data FirstName "Tove" or LastName "Svendson" in the Persons table

SELECT * from Persons

WHERE firstname= ' Tove '

OR lastname= ' Svendson '

return Result:

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 '

return Result:

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:

Raw data in the "Persons" table

LastName

FirstName

Address

City

Hansen

Ola

TIMOTEIVN 10

Sandnes

Nordmann

Anna

Neset 18

Sandnes

Pettersen

Kari

STORGT 20

Stavanger

Svendson

Tove

BORGVN 23

Sandnes

Use between ... and returns LastName to data from "Hansen" to "Pettersen":

SELECT * from Persons WHERE LastName

BETWEEN ' Hansen ' and ' Pettersen '

return Result:

LastName

FirstName

Address

City

Hansen

Ola

TIMOTEIVN 10

Sandnes

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

Group BY Company field to find the total of amout for each company:

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 the data that totals more than 10000 for each company's amout:

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 is the primary key):

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

return Result:

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 update the FirstName of the LastName field in the person table as "Rasmussen" to "Nina":

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" and "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