SQL DELETE
Delete
Rows
The DELETE statement is
used to delete rows in a table.
| 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 |
SQL COUNT
FUNCTION
SQL has built-in functions for counting
database records.
Count
Function Syntax
The syntax for the built-in
COUNT functions is:
| SELECT COUNT(column) FROM
table |
Function
COUNT(*)
The COUNT(*) function
returns the number of selected rows in a
selection.
With this "Persons"
Table:
| Name |
Age |
| Hansen, Ola |
34 |
| Svendson, Tove |
45 |
| Pettersen, Kari |
19 |
This example returns the
number of rows in the table:
| SELECT COUNT(*) FROM
Persons |
Result:
This example returns the
number of persons that are older than 20
years:
| SELECT COUNT(*) FROM Persons WHERE
Age>20 |
Result:
Function
COUNT(column)
The COUNT(column)
function returns the number of rows without a NULL value
in the specified column.
With this "Persons"
Table:
| Name |
Age |
| Hansen, Ola |
34 |
| Svendson, Tove |
45 |
| Pettersen, Kari |
|
This example finds the
number of persons with a value in the "Age" field in the
"Persons" table:
| SELECT COUNT(Age) FROM
Persons |
Result:
The COUNT(column) function
is handy for finding columns without a value. Note that
the result is one less than the number of rows in the
original table because one of the persons does not have
an age value stored.
COUNT
DISTINCT
The keyword DISTINCT and
COUNT can be used together to count the number of
distinct results.
The syntax is:
| SELECT DISTINCT COUNT(column(s)) FROM
table |
With this "Orders"
Table:
| Company |
OrderNumber |
| Sega |
3412 |
| W3Schools |
2312 |
| Trio |
4678 |
| W3Schools |
6798 |
This SQL
statement:
| SELECT COUNT(Company) FROM
Orders |
Will return this
result:
This SQL
statement:
| SELECT DISTINCT COUNT(Company) FROM
Orders |
Will
return this result:
|
