What is SQL?

1. SQL stands for Structured Query Language

2. SQL lets you access and manipulate databases

3  SQL is an ANSI (American National Standards Institute) standard

Note: SQL is not case sensitive. SELECT is the same as select.

What Can SQL do?

1. SQL can retrieve data from a database/table
2. SQL can insert records in a database/table
3. SQL can update records in a database/table
4. SQL can delete records from a database/table
5. SQL can create new databases/table
6. SQL can create new tables in a database/table

SQL DML and DDL

SQL can be divided into two parts:The Data Manipulation Language (DML) and the Data Definition Language (DDL).

DML Commands:

1. SELECT – extracts data from a table

2. UPDATE – updates data in a table

3. DELETE – deletes data from a table

4.INSERT INTO– inserts new data into a table

DDL Commands:

1. CREATE DATABASE– creates a new database
2. CREATE TABLE– creates a new table
3. ALTER TABLE– modifies a table
4. DROP TABLE– delete a table
5. CREATE INDEX– creates an index
6. DROP INDEX– deletes an index

Data Types

Data type	Description	Max Size:
VARCHAR2(size)	Variable length character string having maximum length size bytes. You must specify size	32767 bytes minimum is 1
CHAR(size)	Fixed length character data of length size bytes.	32767
Integer/INT	Integer Number.
DECIMAL(p,s)	Number having precision p and scale s.	Magnitude  1E-130 .. 10E125
DATETIME	A date and time combination in YYYY-MM-DD HH:MM:SS format
DATE	A date in YYYY-MM-DD format

The CREATE DATABASE Command

The CREATE DATABASE statement is used to create a Database.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

CREATE DATABASE Example

CREATE DATABASE KVPALAMPUR ;
This command will create a Database with the name KVPALAMPUR

The CREATE TABLE Command

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

SQL CREATE TABLE Syntax

CREATE TABLE table_name

(
column_name1 data_type,

column_name2 data_type,

….
)

The data type specifies what type of data the column can hold.

CREATE TABLE Example

Now we want to create a table called “Student” that contains four columns: Roll_No, Name, Class, City.

We use the following CREATE TABLE statement:

CREATE TABLE Student

(

Roll_No integer,

Name varchar(30),

Class varchar(5),

City varchar(30)

)

The Roll_No column is of type integer and will hold a number. The Name, Class and City columns are of type varchar and will hold character data. The empty “Student” table will now look like this:

The DROP TABLE Command

The DROP TABLE statement is used to delete a table .

Syntax:

DROP TABLE table_name

Example:

Suppose if we want to drop table student, we can use DROP TABLE command like this

Drop Table Student ;

Note: Before using Drop Command make sure that table has no records exist. Drop command will not work on a table if it has has some records.

The ALTER TABLE Command

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

SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name data-type

To delete a column in a table, use the following syntax 

ALTER TABLE table_name DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name MODIFY column_name data-type

SQL ALTER TABLE Example

Look at the “Student” table:

Now we want to add a column named “DateOfBirth” in the “Student” table.

We use the following SQL statement:

ALTER TABLE Student ADD DateOfBirth date

The “Student” table will now like this:

Change Data Type Example

Now we want to change the data type of the column named “Class” in the “Student” table.

We use the following SQL statement:

ALTER TABLE Student ALTER COLUMN Class integer

Notice that the “Class” column is now of type integer.

DROP COLUMN Example

Next, we want to delete the column named “DateOfBirth” in the “Student” table.

We use the following SQL statement:

ALTER TABLE Student DROP COLUMN DateOfBirth

The “Student” table will now like this:

DML Commands:

Quotes Around Text Fields

SQL uses single quotes around text values (most database systems will also accept double quotes).

However, numeric values should not be enclosed in quotes.

For text values:

This is correct:

 
SELECT * FROM Student WHERE Name=’Abhey’

This is wrong:

SELECT * FROM Student WHERE Name=Abhey

For numeric values:

This is correct:

SELECT * FROM Student WHERE Roll_No=2

This is wrong:

SELECT * FROM Student WHERE Roll_No=’2′

The INSERT INTO Command

The INSERT INTO Command is used to insert a new record in a table.

INSERT INTO Syntax

We can write INSERT INTO command in two forms.

The first form doesn’t specify the column names where the data will be inserted, only their values:

INSERT INTO table_name VALUES (value1, value2, value3,…)

The second form specifies both the column names and the values to be inserted:

 INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)

SQL INSERT INTO Example

We have the following “Student” table:

Now we want to insert some records in the “Student” table.

We use the following SQL statement:

INSERT INTO Student VALUES (1,’Amisha’, ‘XII’, ‘Palampur’)

INSERT INTO Student VALUES (2,’Usha’, ‘XII’, ‘Holta’)

INSERT INTO Student VALUES (3,’Sneh’, ‘XII’, ‘Palampur)

 INSERT INTO Student VALUES (4,’Nishant’, ‘XI’, ‘Baijnath’)

INSERT INTO Student VALUES (5,’Gaurav’, ‘XI’, ‘Paprola’)

The “Student” table will now look like this:

Insert Data Only in Specified Columns

It is also possible to only add data in specific columns.

The following SQL statement will add a new row, but only add data in the “Roll_No”, “Name” and the “Class” columns:

INSERT INTO Student (Roll_No, Name, Class)
VALUES (6, ‘Abhay’, ‘XI’)

 The “Student” table will now look like this:

 The UPDATE Statement

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

SQL UPDATE Syntax

UPDATE table_name SET column1=value, column2=value2,… WHERE some_column=some_value

Note: If you omit the WHERE clause, all records will be updated!

SQL UPDATE Example

The “Student” table:

Now we  update the record of student “Abhey” in the “Student” table.

We use the following SQL command:

UPDATE Student SET City=’Sagoor’ WHERE Name=’Abhey’

The “Student” table will now look like this:

The DELETE Statement

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

SQL DELETE Syntax

DELETE FROM table_name WHERE some_column=some_value

Note: If we omit the WHERE clause, all records will be deleted .Be very careful when deleting records. You cannot undo this command!

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

SQL SELECT Syntax

SELECT column_name(s) FROM table_name

and

SELECT * FROM table_name

SQL SELECT Example

The “Student” table:

Now we select all the records from the table above.

 We use the following SELECT statement:

 SELECT * from Student ;

The use of asterisk (*) is an easy way to list all the records from a table.

 The result of this command will look like this:

Now we select the contents of the columns named “Name” and “Class” from Student table.

We use the following SELECT statement:

SELECT Name,Class FROM Student ;

The result will look like this:

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. Sometimes we want to list only the different (distinct) values from a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s)FROM table_name

The “Student” table:

Now we want to select only the distinct values from the column named “City” from the table above.

We use the following SELECT statement:

SELECT DISTINCT City FROM Persons

The result will look like this:

The WHERE Clause 

The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL WHERE Syntax

SELECT column_name(s)FROM table_name WHERE column_name operator value

WHERE Clause Example

The “Student” table:

Now we want to select only the students living in the city “Palampur” from the table above.

We use the following SELECT statement:

SELECT * FROM Student WHERE City=’Palampur’ ;

Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

The AND & OR Operators

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

AND Operator Example

The “Student” table:

Now we want to select only the students with the name equal to “Sneh” AND the City equal to “Palampur”:

We use the following SELECT statement:

SELECT * FROM Student WHERE Name=’Sneh’ AND City=’Palampur’

The result will look like this:

OR Operator Example

Now we want to select only those students with the name equal to “Nishant” OR City equal to “Sagoor”:

We use the following SELECT statement:

SELECT * FROM Student WHERE Name=’Nishant’ OR City=’Sagoor’

The result will look like this:

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result by a specified column. The ORDER BY keyword sort the records in ascending order by default. If we want to sort the records in a descending order, we can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

ORDER BY Example

The “Student” table:

Now we  select all records from the table above, however, we want to sort the students by their name.

We use the following SELECT statement:

SELECT * FROM Student ORDER BY Name

The result will look like this:

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

SQL GROUP BY Example

We have the following “Order” table:

Now we want to find the total sum  of each Customer. We will have to use the GROUP BY clause to group the items. We use the following SQL statement:

SELECT Cname,SUM(Price) FROM Order GROUP BY Customer

The result will look like this:

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value

SQL HAVING Example

We have the following “Order” table:

Now we want to find if any of the customers have a total order of greater than 25000.

We use the following SQL statement:

SELECT CName,SUM(Price) FROM Order GROUP BY CName HAVING SUM(Price)>25000

The result will look like this:

Now we want to find if the customers “Ayan” or “Abhay” have a total order of more than 30000.

We add an ordinary WHERE clause to the SQL statement:

SELECT CName,SUM(Price) FROM Order WHERE CName=’Ayan’ OR CName=’Abhay’ GROUP BY CName HAVING SUM(Price)>30000

The result will look like this:

The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT coumn_name(s)FROM table_name WHERE column_name IN (value1,value2,…)

IN Operator Example

The “Order” table:

Now we want to select all the records with IName equal to “TV” or “AC” from the table above.

We use the following SELECT statement:

SELECT * FROM Order WHERE IName IN (‘TV’,’AC’)

The result will look like this:

The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

BETWEEN Syntax

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

BETWEEN Operator Example

The “Order” table:

Now we want to select the orders with Price between 15000 and 40000 from the table above.

We use the following SELECT statement:

SELECT * FROM Order WHERE Price BETWEEN 15000 AND 40000

The result will look like this:

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

• DATE – format YYYY-MM-DD
• DATETIME – format: YYYY-MM-DD HH:MM:SS
• TIMESTAMP – format: YYYY-MM-DD HH:MM:SS
• YEAR – format YYYY or YY

Note: The date types are chosen for a column when you create a new table in your database!

SQL Working with Dates

The “Order” table:

Now we want to select the records with an OrderDate of “2014-12-25” from the table above.

We use the following SELECT statement:

SELECT * FROM Order WHERE OrderDate=’2014-12-25′

The result will look like this: