SQL for manipulating database objects and information from database

 

Some of The Most Important SQL Commands

• 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
• 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 

 The SQL SELECT Statement

The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name,column_name
FROM table_name;

and

SELECT * FROM table_name;

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE dbname;

The SQL DELETE Statement

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

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

The SQL UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.
The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.
Tables are organized into rows and columns; and each table must have a name.

SQL CREATE TABLE Syntax

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

The column_name parameters specify the names of the columns of the table.
The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).
The size parameter specifies the maximum length of the column of the table.

Example

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

QL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.

---

CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX PIndex
ON Persons (LastName)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

Advantage of SQL over Access:

1) Reliability
With Access each client reads and writes directly to the raw data tables. If a client machine crashes while writing data this will usually cause the back-end database to also crash and become corrupt. The same thing will occur if the network fails, has a glitch or temporarily becomes overloaded. This problem becomes more apparent as the amount of data or the number of users increases.

With SQL Server the clients do not talk directly with the tables but with an intelligent data manager on the server. This in turn reads and writes data from and to the tables. If a client machine crashes, or the network hiccups, this will not affect the underlying tables; instead the data manager realises that the transaction has not been completed and does not commit the partially transmitted data to the database. The database therefore continues to run without problem.

The client/server system also maintains an automatic 'transaction log'. If a backup has to be restored the transaction log can be run and should restore all completed transactions up to the time of the crash.

The client/server software itself is designed for mission critical systems and is orders of magnitude more reliable than a file server system. On one system that we support the client used to experience around one to two crashes per year (admittedly their network was not exactly state of the art!) when running with an Access database. After we converted it to SQL Server two years ago the system has not experienced a single crash.

2) Data Integrity
Data integrity in SQL Server is enhanced by the use of 'triggers' which can be applied whenever a record is added, updated or deleted. This occurs at the table level and cannot thus be forgotten about, ignored or bypassed by the client machine. For example audit processes cannot be avoided (accidentally or deliberately) with this scenario.

3) Better Performance
With Access all tables involved in a form, report or a query are copied across the network from the server to the client's machine. The tables are then processed and filtered to generate the required recordset. For example if looking up details for one particular order from an orders table containing, say, 50,000 records then the whole table (all 50,000 records) is dragged over the network and then 49,999 of these records are thrown away (this is an over-simplification since indexing can be used to mitigate this to some extent). Contrast this with SQL Server where the filtering takes place on the server (if designed properly) and only 1 record is transmitted over the network.

This can affect performance in two ways. Firstly SQL Server is highly optimised and can usually perform the required filtering much more quickly than the client machine and secondly the amount of data sent across the network link is vastly reduced. For most databases the main performance bottleneck is data transmission over the network hence reducing this can give a really dramatic improvement in performance.

Predicting likely performance improvements is very difficult but an average overall speed improvement of 3 to 5 times, and possibly much more, would not be unexpected.

4) Network Traffic/Speed
As can be seen from the previous section, network traffic is greatly reduced in a client/server scenario, often by many orders of magnitude. This both improves network reliability (by reducing collisions, etc.) and also improves the performance of the network for other software (as there is less traffic on the network). Where there is a slow connection, such as over a telephone dial-up, Access is usually so slow as to be all but unusable (obviously this does depend upon the amount of data) whereas a SQL Server application, if designed for this environment, can still be perfectly useable.

5) Low Bandwidth
This occurs when you are accessing your database over a connection that only supports low data speeds, which, for all practical situations, means anything other than a LAN. In all low bandwidth situations Access/JET usually performs so slowly as to be unusable whilst a correctly designed SQL Server system can be similar in speed to running it over a LAN.

6) Scalability
A file-server system such as Access is designed for small workgroups and is scalable to perhaps 10 concurrent clients. Above this level performance starts to degrade rapidly as more users are added. With the SQL Server client/server architecture many hundreds, or even thousands (with the appropriate infrastructure), of concurrent users can be supported without significant performance degradation.

Advantages of SQL:* High Speed: SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.

* Well Defined Standards Exist:
SQL databases use long-established standard, which is being adopted by ANSI & ISO. Non-SQL databases do not adhere to any clear standard.
* No Coding Required:
Using standard SQL it is easier to manage database  systems without having to write substantial amount
of code.
* Emergence of ORDBMS:
Previously SQL databases were synonymous with  relational database. With the emergence of Object
Oriented DBMS, object storage capabilities are extended to relational databases.

Disadvantages of SQL:

* Difficulty in Interfacing:
Interfacing an SQL database is more complex than adding a few lines of code.
* More Features Implemented in Proprietary way:
Although SQL databases conform to ANSI & ISO  standards, some databases go for proprietary extensions to standard SQL to ensure vendor lock-in.