Umesh Chandra

• What is SQL ?

SQL stands for Structured Query Language. It is used for storing and managing data in relational database management system (RDBMS).

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

SQL COMMANDS

• SELECT

Extracts data from database, The data returned is stored in a result table, called the result – set

Syntax

SELECT column1, column2,.....
FROM table_name;

Here column1, column2, are the field names of the table you want to select data from.

If you want to select all the fields (Columns) available in the table, the syntax is

SELECT * FROM table_name;

SELECT columns Example:

The following SQL statement selects the “Customer Name:, “City” and “Country” columns from the ‘Customers’ table.

SELECT Customer Name, City, Country FROM Customers;

SQL Statements are NOT CASE SENSITIVE, we can use the lower and upper alphabets together in the SQL statements.

---

• Update

Update Data in a Database

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

Syntax

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

Example

UPDATE Customer
SET Contact_name = 'Jessica'
    City = 'London'

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

---

• DELETE

Deletes Data from a Database

The DELETE statement is used to delete existing records in a table

Syntax

DELETE FROM table_name
WHERE Condition;

Example

DELETE FROM Customers
WHERE Customer_name = 'Jessica';

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

---

• INSERT INTO

Inserts new data into a database

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

Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the value to be inserted:

INSERT INTO table_name(column1, column2,, column3,....)
Values(Value1, Value2, Value3,....);

Example

INSERT INTO Customers(Customer_Name, Contact Name, Address, City, Postal Code, Country)
Values('Harris', 'Hary', 'Skagen', 'Stavanger', 4006, 'Norway');

2. Without Specifying the column names

INSERT INTO table_name
Values (Value1, Value2, Value3,....);

Example

INSERT INTO Customers
Values ('Harris', 'Hary', 'Skagen', 'Stavanger', 4006, 'Norway');

---

• CREATE DATABASE

Creates a new Database

The CREATE DATABASE statement is used to create a new SQL Database

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE new_database;

---

• ALTER TABLE

Modifies a table

The ALTER TABLE statement is used to add, delete or modify columns in an existing table and also used to add and drop various constraints on existing table.

ALTER TABLE – ADD COLUMN

ALTER TABLE table_name
ADD column_name datatype;

Example

ALTER TABLE Customers
ADD email VARCHAR(255);

DROP COLUMN

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE Customers
DROP COLUMN email;

---

• CREATE TABLE

Creates a new table

The CREATE TABLE statement is used to create a new table in a database

Syntax

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,....
);

Example

CREATE TABLE Person (
PersonID INT
Lastname VARCHAR(20),
Firstname VARCHAR(20),
Address VARCHAR(20),
City VARCHAR(20)
);

---

• DROP TABLE

Drops the table from the database

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE table_name;

Example

DROP TABLE Person;

---

• CREATE INDEX

Creates and Index(Search Key)

The CREATE INDEX statement is used to create indexes in table

Indexes are used to retrieve data from the database more quickly than otherwise.

The users cannot see the indexes, they are just used to speed up searches/queries.

Syntax

CREATE INDEX Index_name
ON table_name
(column1, column2, column3,....);

Example

CREATE INDEX index_lastname
ON Person
(Lastname, Firstname);

---

• DROP INDEX

Deletes on Index

The DROP INDEX statement is used to delete an index in a table

Syntax

ALTER TABLE table_name
DROP INDEX index_name;

Example

ALTER TABLE Person
DROP INDEX index_lastname;