SQL(Structured query language)

· 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 -- + - , ( ) = < > <= >= <> . * / || ? ;



Semicolon after SQL Statements?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.

Tables
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


SQL Data Manipulation Language (DML)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:
· 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 database table


SQL Data Definition Language (DDL)

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:
· 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


SQL INSERT INTOThe INSERT INTO Statement

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

Insert a New Row
This "Persons" table:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger

And this SQL statement:

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


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


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


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

Update several Columns in a Row
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


The DELETE StatementThe DELETE statement is used to delete rows in a table.
Syntax
DELETE FROM table_nameWHERE column_name = some_value

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


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


Delete All Rows
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:
DELETE FROM table_nameorDELETE * FROM table_nameSort the Rows


The ORDER BY 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:
SELECT Company, OrderNumber FROM OrdersORDER BY CompanyResult:
Company OrderNumber
ABC Shop 5678
Sega 3412
W3Schools 6798
W3Schools 2312


Example
To display the company names in reverse alphabetical order:
SELECT Company, OrderNumber FROM OrdersORDER BY Company DESCResult:
Company OrderNumber
W3Schools 6798
W3Schools 2312
Sega 3412
ABC Shop 5678

Column Name Alias
The syntax is:
SELECT column AS column_alias FROM table


Table Name Alias
The syntax is:
SELECT column FROM table AS table_alias

Example: Using a Column Alias
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:
SELECT LastName AS Family, FirstName AS NameFROM Persons
Returns this result:
Family Name
Hansen Ola
Svendson Tove
Pettersen Kari


Example: Using a Table AliasThis 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:
SELECT LastName, FirstNameFROM Persons AS EmployeesReturns this result:
Table Employees:
LastName FirstName
Hansen Ola
Svendson Tove
Pettersen Kari


SELECT STATEMENT COMING SOON