SQL Tutorial

SQL - Introduction:

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database.

SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

However, the standard SQL commands such as "Select", "Insert", "Update","Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database

SQL - Table Fundamentals

A table is database object that holds user data

or

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables.

Each table in SQL associated with the specific data types-

Char(size) : This data type is used to store character strings values of fixed length.

Varchar(size) : This data type is used to store variable length alphanumeric data.

Date(size) : This data is used to represent date and time.

Number(size) : The number data type is used to store numbers(fixed or floating point).

Long(size) : This data type is used to store variable length character strings containing upto 2GB.

Raw/Long raw: The RAW/LONG RAW data tupes is used to store binary data, such as digitized picture or image.

SQL - Create Table Command

Table creation is done using the create table syntax.

Syntax:

CREATE TABLE tablename (
     columnname1 datatype(size) constraints(if required),
     columnname2 datatype(size) ,
     columnname3 datatype(size) ,
     .
 .
 .
 columnnameN datatype(size) 
 );

Create table command defines each column of the table uniquely. Each column has a minimum three attribute, a name, datatype and size(i.e. column width)

Example:

Create table employee
CREATE TABLE employee (
     ID int NOT NULL,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255) NOT NULL,
     Age int
 Address varchar(100),
 City varchar(50),
 State varchar(50)
 );

Syntax:

describe <tablename>;

SQL- Inserting data into table

The insert statement is used to insert or add a row of data into the table.

Once Table is created the most natural thing to do is load this table with data to be manipulated later.

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis.

Syntax:

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

Example:

INSERT INTO employee (ID, lastName, FirstName, Age, Address, City, State)
 VALUES (101570, ‘Kumar’, ‘Naveen’, 28, ‘XYZ’, ‘Etah’, ‘UP’);

Note: Character statement placed within the insert into statement must be enclosed in single quotes(‘).

It have a one to one relationship, means we insert one value in each column at a time.

SQL - Viewing data in the Table

The select statement is used to query the database and retrieve selected data that match the criteria that you specify.

Syntax:

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

Example:

SELECT FirstName, LastName FROM employee;
or
SELECT *FROM employee;

Note: In this we have use meta character asterisk(*), which used to expand all columns of the table.

SQL- Filtering table data

SQL must give us a method of filtering out data that is not required.

Three way of filtering table data will be:

1-Selected columns and all rows

2-Selected rows and all columns

3-Selected columns and selected rows

Selected columns and all rows

-------------------------------

Retrieval of specific column from a table.

Syntax:

SELECT column1, column2, ...
 FROM table_name;

Example:

SELECT FirstName, LastName FROM employee;

Selected rows and all columns:

-----------------------------

Syntax:

SELECT FROM table_name WHERE <condition>;

Example:

SELECT *FROM employee WHERE FirstName = “naveen”;

Selected columns and selected rows

----------------------------------

To view the specific set of rows and columns.

Syntax:

SELECT column1, column2, ...
 FROM table_name WHERE <condition>;

Example:

SELECT lastName, FirstName, ...
 FROM employee WHERE City = “etah”;

SQL- Eliminating duplicate row using select statement

If table could hold duplicate rows, in this case it necessary to remove delicacy using this

Syntax:

SELECT DISTINCT lastName, FirstName, ...
 FROM employee; 
Or 
SELECT DISTINCT *FROM employee;

Example:

Select DISTINCT city from employee;

-------------------------------------------------------

SQL - Sorting data in a table

The rows retrieved from table in both ascending or descending order.

Syntax:

Select *from tablename ORDER BY columnname1, columnname2 <[sort order]>;
Where [sort order] means either “desc”, by default it take ascending order.

Example:

Select *from employee ORDER BY firstname
or
Select *from employee ORDER BY firstname desc;

SQL – Creating a table from a table

In this we create one table by using another table

In this “AS SELECT” syntax use to select data from one table.

Syntax:

Create table <tablename><[(columnname,columnname)]> AS SELECT <columnname>, <columnname> from <tablename>;

Example:

Create table employee1(firstname1,lastname1) as select fristname, lastname from employee;

------------------------------------------------------

SQL-Inserting data into a table from another table

After creating table from source table if you want to insert record into the new table use this procedure

Syntax:

Insert into <tablename> select <columnname1>, <columnname2> from <tablename>;

Example:

Insert into employee1 select firstname, lastname from employee;

Insert selected record from another table

Example:

Insert into employee1 select firstname, lastname from employee where firstname = “naveen”;

SQL – Delete Operation

The verb DELETE used to remove:

All rows from a table

Or

A rows from a table

Syntax:

Delete from <tablename>;
Or
Delete from <tablename> where <condition>;

Example:

Delete all rows-

Delete from employee;

Delete selected row-

Delete from employee where firstname = “naveen”;

SQL - Updating content of a table

-------------------------------------------------------

The update command used to change or modify data values in a table.

To update –

All the rows

Or

Selected row

All the rows

Syntax:

Update <tablename> set <columnname> = <expression>, <columnname> = <expression>;

Example:

Update employee set address = “noida”;

Selected row

Example:

Update employee set address = “noida” where firstname = “naveen”;

SQL – Adding new column

----------------------------------------------------------------------------------

Used to modify the structure of the table. Use “alter” command to modify structure of table

Syntax:

Alter table <tablename> add (<newcolumnname> <datatype>(<size>), <newcolumnname><datatype><size>…)

Example:

Alter table employee add (middlename char(20));

----------------------------------------------------------------------------------------------

SQL – Dropping a column from a table

Use alter command to drop column form a table

Syntax:

Alter table <tablename> drop column <columnname>;

Example:

Alter table employee drop column address;

-------------------------------------------------------------------------------------------------

SQL – Modifying Existing columns

Syntax:

Alter table <tablename> modify (<columnname> <newdatatype>(<newsize>));

Example:

Alter table employee modify (address varchar(50));

Here we modigy size of columnname address.

Restriction of alter table : these cannot perform

Change the name of the table

Change the name of column

Decrease the size of column if table data exists

SQL – Renaming, truncate, and destroying tables

Rename:

Use for rename the exiting table

Syntax:

Rename <oldtablename> to <newtablename>;

Example:

Rename employee to employee2;

Truncating table:

Syntax:

Truncate table <tablename>;

Example:

Truncate table employee;

Destroying table:

Syntax:

Drop table <tablename>;

Example:

Drop table employee;

SQL - Key constraints

SQL constraints are used to specify rules for data in a table.

SQL Create Constraints
CREATE TABLE table_name (
     column1 datatype constraint,
     column2 datatype constraint,
     column3 datatype constraint,
     ....
 );

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value

• UNIQUE - Ensures that all values in a column are different

• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

• FOREIGN KEY - Uniquely identifies a row/record in another table

• CHECK - Ensures that all values in a column satisfies a specific condition

• DEFAULT - Sets a default value for a column when no value is specified

• INDEX - Used to create and retrieve data from the database very quickly

SQL – UNIQUE and NOT NULL constraints

NOT NULL constraints

The NOT NULL constraint enforces a column to NOT accept NULL values.

Example:

CREATE TABLE employee (
     ID int NOT NULL,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255) NOT NULL,
     Age int
 );

UNIQUE constraints

The UNIQUE constraint ensures that all values in a column are different.

Example:

CREATE TABLE employee (
     ID int NOT NULL UNIQUE,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Age int
 );

SQL – Primary key

What is primary key?

A column or columns is called primary key that uniquely identifies each row in the table.

If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.

What is Composite key?

When multiple columns are used as a primary key, it is known as composite primary key.

Example:

CREATE TABLE employee (
     ID int NOT NULL PRIMARY KEY,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Age int
 );

Or

CREATE TABLE employee (
     ID int NOT NULL,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Age int,
 PRIMARY KEY (ID)
 );

Or

Using with multiple columns

PRIMARY KEY  (columnname1,columnname2..)

SQL – Foreign key

A Foreign key is constraint that enforces referential integrity in SQL server database.

It uses a column or combination of columns that is used establish link between the data in two tables to control the data that can be stored in the foreign key table.

Let suppose two tables are:

Customers and Orders

Customers table has a field like- Cus_ID(Primary key), Cus_Name, Cus_address
Orders table has a field like- ID(primary key), Order_id(foreign key), Order_address

Then syntax can be define as:

MySQL-

CREATE TABLE Orders
ID integer,
 (Order_Id integer, 
 Order_address varchar(50), 
 Primary Key (ID), 
 Foreign Key (Order_id) REFERENCES Customers(Cus_ID));

SQL server and Oracle -

CREATE TABLE Orders
ID integer Primary Key,
 (Order_Id integer Foreign Key, 
 Order_address varchar(50), 
Order_id integer REFERENCES Customers(Cus_ID));