SQL (Structured Query Language)
 

[B][U][SIZE="5"][FONT="Century Gothic"]SQL(Structured query language)[/FONT][/SIZE][/U][/B]

· SQL stands for Structured Query Language
· SQL allows you to access a database
· SQL is an ANSI standard computer language
· SQL can execute queries against a database
· SQL can retrieve data from a database
· SQL can insert new records in a database
· SQL can delete records from a database
· SQL can update records in a database
SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc
Almost all modern Relational Database Management Systems like MS SQL Server, Microsoft Access, MSDE, Oracle, DB2, Sybase, MySQL, Postgres and Informix use SQL as standard database language


SQL is a keyword based language. Each statement begins with a unique keyword. SQL statements consist of clauses which begin with a keyword. SQL syntax is not case sensitive.


The other lexical elements of SQL statements are:
· names -- names of database elements: tables, columns, views, users, schemas; names must begin with a letter (a - z) and may contain digits (0 - 9) and underscore (_)
· literals -- quoted strings, numeric values, datetime values
· delimiters -- + - , ( ) = < > <= >= <> . * / || ? ;



[B][COLOR="Sienna"]Semicolon after SQL Statements?[/COLOR][/B]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.

[B][COLOR="sienna"]Tables [/COLOR][/B]
A table is a set of columns and rows. Each column is referred to as a field. Each value in a field represents a single type of data. For example, a table might have three fields: name, city, and state. The table will consist of three columns: one for name, one for city, and one for state. For every row in the table, the name field contains the name, the city field contains the city, and the state field contains the state.

Every database consists of one or more tables, which store the database’s data/information

The database table columns (called also table fields) have their own unique names and have a pre-defined data types.
While table columns describe the data types, the table rows contain the actual data for the columns


[COLOR="sienna"][U]SQL Data Manipulation Language (DML)[/U][/COLOR]SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.

These query and update commands together form the Data Manipulation Language (DML) part of SQL:
[B]· SELECT - extracts data from a database table
· UPDATE - updates data in a database table
· DELETE - deletes data from a database table
· INSERT INTO - inserts new data into a data[/B]base table


[B][U][COLOR="sienna"]SQL Data Definition Language (DDL)[/COLOR][/U][/B]

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
[B]· CREATE TABLE - creates a new database table
· ALTER TABLE - alters (changes) a database table
· DROP TABLE - deletes a database table
· CREATE INDEX - creates an index (search key)
· DROP INDEX - deletes an index [/B]


[B][U][SIZE="4"]SQL INSERT INTO[/SIZE][/U][/B]The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.
[B]Syntax[/B]
[B]INSERT INTO table_nameVALUES (value1, value2,....)[/B]You can also specify the columns for which you want to insert data:
[B]INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,....)[/B]

[B][U]Insert a New Row[/U][/B]
This "Persons" table:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger

And this SQL statement:

[B]INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')[/B]
Will give this result:
LastName FirstName Address C ity
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes


[B][U]Insert Data in Specified Columns[/U][/B]
This "Persons" table:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes
And This SQL statement:
[B]INSERT INTO Persons (LastName, Address)VALUES ('Rasmussen', 'Storgt 67')[/B]Will give this result:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes
Rasmussen Storgt 67


[B][U][SIZE="4"]The Update Statement[/SIZE][/U][/B]
The UPDATE statement is used to modify the data in a table.
[B]Syntax
UPDATE table_nameSET column_name = new_valueWHERE column_name = some_value[/B]
Person:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Storgt 67


[B]Update one Column in a Row[/B]
We want to add a first name to the person with a last name of "Rasmussen":
[B]UPDATE Person SET FirstName = 'Nina'WHERE LastName = 'Rasmussen'[/B]Result:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67

[B]Update several Columns in a Row[/B]
We want to change the address and add the name of the city:
UPDATE PersonSET Address = 'Stien 12', City = 'Stavanger'WHERE LastName = 'Rasmussen'
Result:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger


[B][U][COLOR="sienna"][SIZE="4"]The DELETE Statement[/SIZE][/COLOR][/U][/B]The DELETE statement is used to delete rows in a table.
Syntax
[B]DELETE FROM table_nameWHERE column_name = some_value[/B]

Person:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger


[U]Delete a Row[/U]
"Nina Rasmussen" is going to be deleted:
[U]DELETE FROM Person WHERE LastName = 'Rasmussen'[/U]Result
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger


[U][B]Delete All Rows[/B][/U]
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
[B]DELETE FROM table_nameorDELETE * FROM table_name[/B]Sort the Rows


The [U][B]ORDER BY [/B][/U]clause is used to sort the rows.
Orders:
Company OrderNumber
Sega 3412
ABC Shop 5678
W3Schools 6798
W3Schools 2312

Example
To display the company names in alphabetical order:
[U][B]SELECT Company, OrderNumber FROM OrdersORDER BY Company[/B][/U]Result:
Company OrderNumber
ABC Shop 5678
Sega 3412
W3Schools 6798
W3Schools 2312


Example
To display the company names in reverse alphabetical order:
[U][B]SELECT Company, OrderNumber FROM OrdersORDER BY Company DESC[/B][/U]Result:
Company OrderNumber
W3Schools 6798
W3Schools 2312
Sega 3412
ABC Shop 5678

[B][U][COLOR="sienna"][SIZE="4"]Column Name Alias[/SIZE][/COLOR][/U][/B]
The syntax is:
[B][U]SELECT column AS column_alias FROM table[/U][/B]


[U][B]Table Name Alias[/B][/U]
The syntax is:
[B]SELECT column FROM table AS table_alias[/B]

Example:[B][U] Using a Column Alias[/U][/B]
This table (Persons):
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger

And this SQL:
[B]SELECT LastName AS Family, FirstName AS NameFROM Persons[/B]
Returns this result:
Family Name
Hansen Ola
Svendson Tove
Pettersen Kari


[U][B]Example: Using a Table Alias[/B][/U]This table (Persons):
LastName FirstName Address City
Hansen Ola Ti moteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger


And this SQL:
[B]SELECT LastName, FirstNameFROM Persons AS Employees[/B]Returns this result:
Table Employees:
LastName FirstName
Hansen Ola
Svendson Tove
Pettersen Kari


SELECT STATEMENT COMING SOON