Database step-by-step primer

Database tables

A database typically contains one or more tables. Each table is identified by a name (for example, "customer" or "order"). The table contains records (rows) with data.

The following example is a table named "Persons":

id	lastname	firstname	address	city
1	adams	john	oxford Street	london
2	bush	george	fifth Avenue	new York
3	carter	thomas	changan Street	beijing

The table above contains three records (one per person) and five columns (Id, last name, first name, address, and city).

SQL statements

Most of the work you need to do on the database is done by SQL statements.

The following statement selects the data for the LastName column from the table:

SELECT LastName from Persons

The result set looks like this:

lastname
adams
bush
carter

In this tutorial, we'll walk you through a variety of different SQL statements.

Important issues

Be sure to remember thatSQL is not case sensitive !

Semicolon after the SQL statement?

Some database systems require semicolons to be used at the end of each SQL command. Semicolons are not used in our tutorials.

Semicolons are the standard way to separate each SQL statement in a database system so that more than one statement can be executed in the same request to the server.

If you are using MS Access and SQL Server 2000, you do not have to use semicolons after each SQL statement, although some database software requirements must use semicolons.

SQL DML and DDL can divide SQL into two parts: Data manipulation Language (DML) and data definition language (DDL).

SQL (Structured Query language) is the syntax for executing queries. However, the SQL language also contains syntax for updating, inserting, and deleting records.

The query and update Directives form the DML portion of SQL:

• SELECT -get data from a database table
• Update-updating data in a database table
• Delete-deletes data from the database table
• INSERT INTO-inserts data into a database table

The Data definition language (DDL) portion of SQL gives us the ability to create or delete tables. We can also define indexes (keys), specify links between tables, and impose constraints between tables.

The most important DDL statement in SQL:

• CREATE database-creating new databases
• alter database-Modify Databases
• CREATE TABLE-Creates a new table
• ALTER TABLE -Change (change) database table
• Drop Table-delete tables
• CREATE index-Creating indexes (search key)
• Drop Index-delete indexes

Database step-by-step primer