Database tables
A database most often contains one or more tables. Each table is identified by a name ( e.g. “Customers” or “Order”). Table contain records (rows) with data.
Below is an example of a table called “Persons”;
The table above contains three records (one for each person) and five
columns (P_id, LastName, FirstName, Address, and City).
SQL statements
Most of the actions you need to perform on a database are done with SQL
statements.
The following SQL statement will select all the records in the “Persons”
table;
SELECT * FROM Persons
In this lesson we will teach you all about the different SQL
statements.
- Keep in Mind that…
- SQL is not a case sensitive!
Semicolon after SQL statements?
Some database systems require a semicolon at the end of teach SQL statement.
Semicolon is the standard way to separate each SQL statement in
database systems that allow more than one SQL statement to be executed in the
same call to the server.
We are using MS Access and SQL Server 2000 and we do not have to put a
semicolon after each SQL statement, but some database programs force you to use
it.
SQL DML and DDL
SQL can be divided into two parts: The data Manipulation Language (DML)
and the Data Definition Language (DDL)
The query and update commands form the DML part of SQL:
SELECT – extracts data from a database
UPDATE – updates data in a database
DELETE – deletes data from a database
INSERT INTO – inserts new data into a database
The DDL part of DQL permits database tables to be created or deleted.
It also defines indexes (keys), specifies links between tables, and imposes
constraints between tables. The most important DDL statement in SQL are:
CREATE DATABASE - creates a new database ALTER DATABASE modifies a
database CREATE TABLE creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index
(search key)
DROP INDEX - deletes an index
----------------------------------------------------------------------------------------------------------
SQL UPDATE Statement
The update statement is used to update records in a table.
The UPDATE statement
The UPDATE statement is used to update existing records in a table.
Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
SQL UPDATE Example
The “Persons” table
Now we want to update the person “ Mason, Adam” in the “Persons” table
We use the following SQL statement:
The “Persons” table will now look like this:
SQL UPDATE Warning
Be careful when updating records. If we have omitted the WHERE clause in the example above, like this:
The “Persons “ table would have looked like this:
-------------------------------------------------------------------------------------------------------------------
SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
WHERE Clause Example
The “Persons” table:
SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
WHERE Clause Example
The “Persons” table:
Now we want to select only the person living in the city “Sander” from the table above.
We use the following SELECT statement:
The result-set will look like this:
Quotes Around Text Fields
SQL uses single quotes around text values ( most database systems will also accept double quotes).
Although, numeric values should not be enclosed in quotes.
For text values:
For numeric values:
Operators allowed in the Where Clause
With the WHERE clause, the following operators can be sued
Quotes Around Text Fields
SQL uses single quotes around text values ( most database systems will also accept double quotes).
Although, numeric values should not be enclosed in quotes.
For text values:
For numeric values:
Operators allowed in the Where Clause
With the WHERE clause, the following operators can be sued
