SQL PART 4B

SELECT * FROM Persons WHERE FirstName LIKE 'O%'

The following SQL statement will return persons with first names that end with an 'a':

SELECT * FROM Persons WHERE FirstName LIKE '%a'

The following SQL statement will return persons with first names that contain the pattern 'la':

SELECT * FROM Persons WHERE FirstName LIKE '%la%'

The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.

Syntax

INSERT INTO table_name

VALUES (value1, value2,....)

You can also specify the columns for which you want to insert data:

INSERT INTO table_name (column1, column2,...)

VALUES (value1, value2,....)

Insert a New Row

This "Persons" table:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger

And this SQL statement:

INSERT INTO Persons
VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

Will give this result:

LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes

Insert Data in Specified Columns

This "Persons" table:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes
And This SQL statement:

INSERT INTO Persons (LastName, Address)
VALUES ('Rasmussen', 'Storgt 67')

Will give this result:

LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes
RasmussenStorgt 67

SQL The UPDATE Statement

The Update Statement

The UPDATE statement is used to modify the data in a table.

Syntax

UPDATE table_name

SET column_name = new_value
WHERE column_name = some_value

Person:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
RasmussenStorgt 67

Update one Column in a Row

We want to add a first name to the person with a last name of "Rasmussen":
UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'

Result:

LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67

Update several Columns in a Row
We want to change the address and add the name of the city:

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

Result:

LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger

Product Spotlight

SQL The Delete Statement

The Delete Statement

The DELETE statement is used to delete rows in a table.

Syntax

DELETE FROM table_name WHERE column_name = some_value

Person:

LastName FirstName Address City

Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger

Delete a Row

"Nina Rasmussen" is going to be deleted:
DELETE FROM Person WHERE LastName = 'Rasmussen'
Result
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_nameorDELETE * FROM table_name

SQL ORDER BY

The ORDER BY keyword is used to sort the result.

Sort the Rows
The ORDER BY clause is used to sort the rows.
Orders:
Company OrderNumber
Sega 3412
ABC Shop 5678
W3Schools 2312
W3Schools 6798

Example
To display the companies in alphabetical order:
SELECT Company, OrderNumber FROM Orders
ORDER BY Company
Result:
Company OrderNumber
ABC Shop5678
Sega 3412
W3Schools 6798
W3Schools 2312

Example
To display the companies in alphabetical order AND the ordernumbers in numerical order:
SELECT Company, OrderNumber FROM Orders
ORDER BY Company, OrderNumber

Result:
Company OrderNumber

ABC Shop 5678

Sega 3412
W3Schools 2312
W3Schools 6798

Example

To display the companies in reverse alphabetical order:

SELECT Company, OrderNumber FROM Orders
ORDER BY Company DESC

Result:

Company OrderNumber
W3Schools 6798
W3Schools 2312
Sega 3412
ABC Shop 5678
Example

To display the companies in reverse alphabetical order AND the ordernumbers in numerical order:

SELECT Company, OrderNumber FROM Orders
ORDER BY Company DESC, OrderNumber ASC

Result:

Company OrderNumber
W3Schools 2312
W3Schools 6798
Sega 3412
ABC Shop 5678

SQL AND & OR

AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.