Introduction to SQL

INTRODUCTARY CONCEPT

1.1 What is SQL?

·         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 is easy to learn

 

SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc

1.2 SQL Database Tables:

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

Below is an example of a table called "Persons":

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Pettersen	Kari	Storgt 20	Stavanger

The table above contains three records (one for each person) and four columns (LastName,  FirstName, Address, and City).

2. DATABASE LANGUAGE

2.1 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 a Table

To create a table in a database:

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... )

 

Example

This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":

 

ALTER TABLE

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

ALTER TABLE table_name ADD column_name datatype   ALTER TABLE table_name MODIFY column_name datatype   ALTER TABLE table_name DROP COLUMN column_name

 

Delete a Table or Database

To delete a table (the table structure attributes, and indexes will also be deleted):

DROP TABLE table_name

2.2 SQL Data Manipulation Language (DML)

DML language includes syntax to update, insert, and delete records. These query and update commands together form the Data Manipulation Language (DML) part of SQL:

·                     UPDATE - updates data in a database table

·                     DELETE - deletes data from a database table

·                     INSERT INTO - inserts new data into a database table

---

The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.

Syntax

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

You can also specify the columns for which you want to insert data:

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

 

The Update Statement

The UPDATE statement is used to modify the data in a table.

Syntax

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

The DELETE Statement

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

Syntax

DELETE FROM  table_name WHERE column_name = some_value