SELECT use:
Remove the data syntax of the specified column from the specified table:
SELECT Column_name (s) from table_name
Explanation:
Select the data column from the database and allow you to select one or more data columns or data lines from one or more data sheets. The complete syntax of the SELECT statement is quite complex, but the main clauses 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]] Example:
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 data name "lastname", "firstname"
Select Lastname, Firstname from Persons
Return the result:
Lastname
Firstname
Hansen
OLA
Svendson
TOVE
Pettersen
Kari
Select the data of all fields
Select * from Persons
Return the result:
Lastname
Firstname
Address
CITY
Hansen
OLA
TIMOTEIVN 10
Sandnes
Svendson
TOVE
Borgvn 23
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
WHERE
use:
It is used to specify a standard for selecting queries
grammar:
Select Column from Table Where COLMN CONDITION VALUE
The following operator can be used in WHERE:
=, <>,>, <,> =, <=, Beetween, LIKE
Note: Do not equal in some sql versions <> can be written as! =
Explanation:
SELECT statement Returns the data for the WHERE clause in TRUE
example:
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 the 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 OR
use:
And and or or more conditions are used to connect two or more in the WHERE clause
Explanation: When combined with two Boolean expressions, only two expressions are True when two expressions are true.
When combined with two Boolean expressions, as long as one of the conditions is True, OR passes back TRUE.
example:
Original 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 Operators to find data in the "Persons" table "TOVE" and LastName is "Svendson"
Select * from Persons
Where firstname = 'TOVE'
And lastname = 'svendson'
Return the result:
Lastname
Firstname
Address
CITY
Svendson
TOVE
Borgvn 23
Sandnes
Use the OR operator to find data in the "persons" table in the "Persons" table or "TOVE" or lastname "Svendson"
Select * from Persons
Where firstname = 'TOVE'
Or lastname = 'svendson'
Return the result:
Lastname
Firstname
Address
CITY
Svendson
TOVE
Borgvn 23
Sandnes
Svendson
Stephen
KAIVN 18
Sandnes
You can also combine and or or to form a complex expression in brackets), such as:
Select * from Persons Where
(Firstname = 'tove' or firstname = 'stephen')
And lastname = 'svendson'
Return the result:
Lastname
Firstname
Address
CITY
Svendson
TOVE
Borgvn 23
Sandnes
Svendson
Stephen
KAIVN 18
Sandnes
Between ... and use:
Specifies the scope syntax that needs to return data:
SELECT Column_name from table_name
Where colorn_name
Between Value1 and Value2
example:
Original 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
Returns LastName to "Hansen" to "Pettersen" data:
SELECT * from Persons Where Lastname
Between 'Hansen' and 'Pettersen'
Return the result:
Lastname
Firstname
Address
CITY
Hansen
OLA
TIMOTEIVN 10
Sandnes
Nordmann
Anna
NESET 18
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
In order 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 the result:
Lastname
Firstname
Address
CITY
Svendson
TOVE
Borgvn 23
Sandnes
Distinct use:
The Distinct keyword is used as a unique value syntax:
Select Distinct Column-Name (s) from Table-Name
Explanation:
When there is a repeated value in the column-name (s), the return result leavies only one case:
Ordest data in the "Orders" table
Company
ORDERNUMBER
Sega
3412
W3Schools
2312
TRIO
4678
W3Schools
6798
Returns the only value in the Company field with a DistINCT keyword:
Select Distinct Company from Orders
Return the result:
Company
Sega
W3Schools
TRIO
Order by use:
Specify the ordering syntax of the result set:
Select column-name (s) from table-name order by {order_by_expression [ASC | DESC]}
Explanation:
The sort of the specified result set can be sorted in the way as ASC (Sort, from the lowest value to the highest) or DESC (decrement mode, from the highest value to the lowest value), the default method is the ASC Example:
Original data in the "ORDERS" table:
Company
ORDERNUMBER
Sega
3412
ABC SHOP
5678
W3Schools
2312
W3Schools
6798
Returns the result set according to the ascending order of the Company field:
Select Company, ORDERNUMBER from ORDERS
ORDER BY COMPANY
Return the result:
Company
ORDERNUMBER
ABC SHOP
5678
Sega
3412
W3Schools
6798
W3Schools
2312
Returns the result set according to the descending order of the Company field:
Select Company, ORDERNUMBER from ORDERS
ORDER by Company DESC
Return the result:
Company
ORDERNUMBER
W3Schools
6798
W3Schools
2312
Sega
3412
ABC SHOP
5678
Group by use:
Grouping result sets is often used with the summary function. grammar:
Select Column, Sum (Column) from Table Group by Column
example:
Original data in the "Sales" table:
Company
Amount
W3Schools
5500
IBM
4500
W3Schools
7100
Follow the COMPANY field to find the total AMOUT of each Company:
Select Company, SUM (Amount) from sales
GROUP BY Company
Return the result:
Company
Sum (Amount)
W3Schools
12600
IBM
4500
Having use:
Specify the group or summary search criteria. grammar:
SELECT Column, Sum (Column) from TableGroup by Column
Having SUM (Column) Condition Value
Explanation:
Having is usually used at the same time as the Group By clause. When you don't use Group by, Having is similar to where clauses.
example:
Original data in the "Sales" table:
Company
Amount
W3Schools
5500
IBM
4500
W3Schools
7100
Follow the COMPANY field to find data for each Company's AMOUT at 10,000 data:
Select Company, SUM (Amount) from sales
Group by Company Having Sum (Amount)> 10000
Return the result:
Company
Sum (Amount)
W3Schools
12600
Join Uses:
You will use Join when you want to select a result set from two or more. example:
The data in the "EMPLOYEES" table is as follows, (where 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 Employees ID and Orders:
Select Employees.name, Orders.Product
From Employees, Orders
WHERE EMPLOYEES.ID = Orders.ID
Return the result:
Name
PRODUCT
Hansen, OLA
PRINTER
Svendson, Stephen
TABLE
Svendson, Stephen
Chair
Or you can also use the Join keyword to complete the above operations:
Select Employees.name, Orders.Product
From Employees
Inner Join ORDERS
ON Employees.id = Orders.ID
Inner Join Syntax:
SELECT FIELD1, FIELD2, FIELD3
From first_table
Inner Join Second_Table
ON first_TABLE.KEYFIELD = SECOND_TABLE.FOREIGN_KEYFIELD
Explanation:
The result set returned by Inner Join is all the matched data in two tables.
LEFT JOIN Syntax:
SELECT FIELD1, FIELD2, FIELD3
From first_table
Left join second_table
ON first_TABLE.KEYFIELD = SECOND_TABLE.FOREIGN_KEYFIELD
Use "Employees" table to go to the left "Orders" table to find relevant data:
Select Employees.name, Orders.Product
From Employees
Left Join Orders
ON Employees.id = Orders.ID
Return the result:
Name
PRODUCT
Hansen, OLA
PRINTER
Svendson, TOVE
Svendson, Stephen
TABLE
Svendson, Stephen
Chair
Pettersen, Kari
Explanation:
LEFT JOIN Returns all rows in "first_table" although there is no matching data in "Second_Table".
Right Join Syntax:
SELECT FIELD1, FIELD2, FIELD3
From first_table
Right Join Second_Table
ON first_TABLE.KEYFIELD = SECOND_TABLE.FOREIGN_KEYFIELD
Use "Employees" to go to the right out of the "Orders" table to find the relevant data:
Select Employees.name, Orders.Product
From Employees
Right Join ORDERS
ON Employees.id = Orders.ID
Return the result:
Name
PRODUCT
Hansen, OLA
PRINTER
Svendson, Stephen
TABLE
Svendson, Stephen
Chair
Explanation:
Right Join returns all rows in "Second_Table" although there is no matching data in "first_table".
Alias:
Available in a table, result set, or column, take a logical name syntax for them:
Give the column acquisition name:
SELECT Column As Column_Alias from Table
Take the alias for the table:
SELECT Column from Table as Table_Alias
example:
Original 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 the result:
Family
Name
Hansen
OLA
Svendson
TOVE
Pettersen
Kari
Run the following SQL:
Select Lastname, Firstname
From Persons as Employees
Return the result:
The data in Employees is:
Lastname
Firstname
Hansen
OLA
Svendson
TOVE
Pettersen
Kari
INSERT INTO use:
Insert a new line grammar in the table:
Insert a line of data
INSERT INTO TABLE_NAME
Values (Value1, Value2, ....)
Insert a line of data on the specified field
INSERT INTO TABLE_NAME (Column1, Column2, ...)
Values (Value1, Value2, ....)
example:
Original data in the "Persons" table:
Lastname
Firstname
Address
CITY
Pettersen
Kari
Storgt 20
Stavanger
Run the following SQL insert a row of data:
INSERT INTO PERSONS
VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')
The data in the "Persons" table is:
Lastname
Firstname
AddressCity
Pettersen
Kari
Storgt 20
Stavanger
Hetland
Camilla
HAGABAKKA 24
Sandnes
Run the following SQL insert a row of data on the specified field:
INSERT INTO PERSONS (Lastname, Address)
VALUES ('Rasmussen', 'Storgt 67')
The data in the "Persons" table is:
Lastname
Firstname
Address
CITY
Pettersen
Kari
Storgt 20
Stavanger
Hetland
Camilla
HAGABAKKA 24
Sandnes
Rasmussen
Storgt 67
Update use:
Update the original data syntax in the table:
Update Table_name Set Column_name = New_VALUE
Where colorn_name = some_value
example:
Original data in the "Person" table:
Lastname
Firstname
Address
CITY
Nilsen
Fred
Kirkegt 56
Stavanger
Rasmussen
Storgt 67
Run the following SQL updates the lastname field in the Person table for "rasmussen" to "Nina":
Update Person set firstname = 'nina'
WHERE Lastname = 'rasmussen'
The data in the "Person" table in the update is:
Lastname
Firstname
Address
CITY
Nilsen
Fred
Kirkegt 56
Stavanger
Rasmussen
Nina
Storgt 67
Similarly, use UPDATE statements to update multiple fields at the same time:
Update Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE Lastname = 'rasmussen'
The data in the "Person" table in the update is:
Lastname
Firstname
Address
CITY
Nilsen
Fred
Kirkegt 56
Stavanger
Rasmussen
Nina
Stien 12
Stavanger
DELETE use:
Delete the data syntax in the table:
Delete from table_name where column_name = Some_Value
example:
Original data in the "Person" table:
Lastname
Firstname
Address
CITY
Nilsen
Fred
Kirkegt 56
Stavanger
Rasmussen
Nina
Stien 12
Stavanger
Delete the data of the lastname "rasmussen" in the Person table:
Delete from person where lastname = 'rasmussen'
The data in the "Person" table after executing the delete statement is:
Lastname
Firstname
Address
CITY
Nilsen
Fred
Kirkegt 56
Stavanger
CREATE TABLE use:
Establish a new information table. grammar:
Create Table Table_Name
(
COLUMN_NAME1 DATA_TYPE, Column_Name2 Data_Type,
.......
)
example:
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 of the field, you can do this:
CREATE TABLE PERSON
(
Lastname varchar (30),
Firstname varchar (30),
Address Varchar (120),
Age Int (3)
)
The following table lists some of the data types in SQL:
Data Type
Description
Integer (Size) Smallint (size) Tinyint (size)
Hold Integers ONLY. The Maximum Number of Digits Are Specified in Parenthes.
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 isot "d".
Char (size)
Holds a fixed length string (can contact size, numbers). The Fixed Size IS Specified in Parenthesis.
VARCHAR (SIZE)
Holds a Variable Length String (CAN Contain Letters, NumBers, and Special Characters). The maximum size isot.
Date (YYYYMMDD)
Holds a date
ALTER TABLE use:
Add the latter removal field syntax in the existing table:
Alter Table Table_name
Add Column_name DataType
Alter Table Table_name
DROP Column Column_name
Note: Some database management systems do not allow fields in the table
example:
Original data in the "Person" table:
Lastname
Firstname
Address
Pettersen
Kari
Storgt 20
Add a field called City in the Person table:
ALTER TABLE PERSON ADD City VARCHAR (30)
Increase the data in the table below:
Lastname
Firstname
Address
CITY
Pettersen
Kari
Storgt 20
Remove the original Address field in the Person table:
ALTER TABLE PERSON DROP COLUMN Address
The data in the removal table is as follows:
Lastname
Firstname
CITY
Pettersen
Kari
DROP TABLE use:
Remove a data table definition in the database and all information, indexes, triggers, condition constraints, and permissions in the data table. Syntax: Drop Table Table_Name
CREATE DATABASE use:
Create a new database. Syntax:
Create Database data_name
DROP DATABASE use:
Remove the original database syntax:
DROP DATABASE DATABASE_NAME
