SQL Basic Activities for students and teachers Part 2 : All SQL Basic Lessons you should know

SQL SYNTAX

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: 

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