Structured Query Language (SQL) is a specialized language for accessing and manipulating databases.
SQL commands are classified by function:
Data definition language (DDL) - used to define or change database structure(s) (e.g., CREATE, ALTER, DROP)
Data manipulation language (DML) - used to select or change data (e.g., INSERT, UPDATE, DELETE, SELECT)
Transaction Control language - used to control logical units of work (e.g., COMMIT, ROLLBACK)
The CREATE DATABASE statement is used to create a new SQL database. Syntax:
CREATE DATABASE databasename;
CREATE DATABASE school;
The SHOW DATABASES statement is used to know the names of existing databases.
SHOW DATABASES;
In order to use the database, the following SQL statement is required. Syntax:
USE databasename;
USE school;
The DROP DATABASE statement is used to delete a database from system. Syntax:
DROP DATABASE databasename;
DROP DATABASE school;
A database consists of many tables. In order to create a table in database CREATE TABLE statement is used. Syntax:
CREATE TABLE table_name
(
column_name1 data_type (size) constraint,
column_name2 data_type (size) constraint,
column_name3 data_type (size) constraint,
....
);
| char(n) | A FIXED length string. The n specifies the column length. The parameter n can be from 0 to 255. Default is 1 |
| varchar(n) | A VARIABLE length string. The n parameter specifies the maximum column length in characters - can be from 0 to 65535 |
| int | An integer. Range is from -2147483648 to 2147483647. |
| float | A floating point number. |
| date | A date. Format: YYYY-MM-DD. |
Constraints are the certain types of restrictions on the data values that an attribute can have.
| Constraint | Description |
| NOT NULL | Ensures that a column cannot have NULL a value |
| UNIQUE | Ensures that all the values in a column are different |
| DEFAULT | Sets a default value for a column if no value is specified |
| PRIMARY KEY | The column which can uniquely identify each row/record in a table. |
| FOREIGN KEY | The column which refers to value of an attribute defined as primary key in another table |
Example :
CREATE TABLE student
(
rollnumber int NOT NULL,
name char(25) NOL NULL,
fees int DEFAULT 7000,
dob date,
class char(3),
PRIMARY KEY (rollnumber)
);
Provides a description of the specified table. Syntax:
DESCRIBE table_name;
DESCRIBE student;
Inserting a new row at the bottom of the table. Syntax :
INSERT INTO table_name
VALUES (value1, value2, value3,...);
You can also specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,...)
VALUES(value1,value2,value3,...);
INSERT INTO student VALUES(10, 'Alex', 7800, '1998-10-03','K12');
INSERT INTO student(rollnumber, name, fees, dob, class) values(11, 'Peter', 6700, '1997-11-15', 'K12');
The SELECT statement to display the content from a table
SELECT * FROM student;
| rollnumber | name | fees | dob | class |
| 10 | Alex | 7800 | 1998-10-03 | K12 |
| 11 | Peter | 6700 | 1997-11-15 | K12 |
| 12 | Alisha | 7800 | 1999-07-03 | K11 |
| 13 | John |
6900 |
2000-12-13 |
K11 |
SELECT name, fees FROM student;
| name | fees |
| Alex | 7800 |
| Peter | 6700 |
| Alisha | 7800 |
| John | 6900 |
The SELECT DISTINCT statement is used to display only distinct (different) values.
SELECT DISTINCT class FROM student
| class |
| K12 |
| K11 |
=, <, >, <=, >=, <>
AND, OR, NOT
SELECT * FROM student WHERE fees < 7000;
| rollnumber | name | fees | dob | class |
| 11 | Peter | 6700 | 1997-11-15 | K12 |
| 13 | John | 6900 | 2000-12-13 | K11 |
SELECT * FROM student WHERE fees > 7000 AND fees < 8000;
| rollnumber | name | fees | dob | class |
| 10 | Alex | 7800 | 1998-10-03 | K12 |
| 12 | Alisha | 7800 | 1999-07-03 | K11 |
SELECT * FROM student WHERE fees > 7000 OR class = 'K12';
| rollnumber | name | fees | dob | class |
| 10 | Alex | 7800 | 1998-10-03 | K12 |
| 11 | Peter | 6700 | 1997-11-15 | K12 |
| 12 | Alisha | 7800 | 1999-07-03 | K11 |
SELECT name, fees FROM student WHERE NOT (class = 'K12');
| name | fees |
| Alisha | 7800 |
| John | 6900 |
SELECT name, fees FROM student WHERE class <> 'K12';
| name | fees |
| Alisha | 7800 |
| John | 6900 |
SELECT * FROM student WHERE rollnumber IN(10, 12, 13);
| rollnumber | name | fees | dob | class |
| 10 | Alex | 7800 | 1998-10-03 | K12 |
| 12 | Alisha | 7800 | 1999-07-03 | K11 |
| 13 | John | 6900 | 2000-12-13 | K11 |
SELECT * FROM student WHERE rollnumber BETWEEN 11 AND 13;
| rollnumber | name | fees | dob | class |
| 11 | Peter | 6700 | 1997-11-15 | K12 |
| 12 | Alisha | 7800 | 1999-07-03 | K11 |
| 13 | John | 6900 | 2000-12-13 | K11 |
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used with the LIKE operator:
* The percent sign (%) for multiple characters
* The underscore sign (_) for one character
SELECT name FROM student WHERE name LIKE 'A%';
| name |
| Alex |
| Alisha |
SELECT * FROM student WHERE name LIKE '%n';
| rollnumber | name | fees | dob | class |
| 13 | John | 6900 | 2000-12-13 | K11 |
SELECT name FROM student WHERE Name LIKE '%e%';
| name |
| Alex |
| Peter |
SELECT name FROM student WHERE Name LIKE 'Al__';
| name |
| Alex |
The UPDATE statement is used to modify the existing records in a table.
UPDATE student SET fees = '7900' WHERE rollnumber = 12;
SELECT * FROM student;
| rollnumber | name | fees | dob | class |
| 10 | Alex | 7800 | 1998-10-03 | K12 |
| 11 | Peter | 6700 | 1997-11-15 | K12 |
| 12 | Alisha | 7900 | 1999-07-03 | K11 |
| 13 | John | 6900 | 2000-12-13 | K11 |
Arranging the data in ascending or descending order of one/multiple columns
SELECT * FROM student ORDER BY name;
| rollnumber | name | fees | dob | class |
| 10 | Alex | 7800 | 1998-10-03 | K12 |
| 12 | Alisha | 7900 | 1999-07-03 | K11 |
| 13 | John | 6900 | 2000-12-13 | K11 |
| 11 | Peter | 6700 | 1997-11-15 | K12 |
SELECT * FROM student ORDER BY fees DESC;
| rollnumber | name | fees | dob | class |
| 12 | Alisha | 7900 | 1999-07-03 | K11 |
| 10 | Alex | 7800 | 1998-10-03 | K12 |
| 13 | John | 6900 | 2000-12-13 | K11 |
| 11 | Peter | 6700 | 1997-11-15 | K12 |
SELECT class, name, dob, fees FROM student ORDER BY class, name DESC;
| class | name | dob | fees |
| K11 | John | 2000-12-13 | 6900 |
| K11 | Alisha | 1999-07-03 | 7900 |
| K12 | Peter | 1997-11-15 | 6700 |
| K12 | Alex | 1998-10-03 | 7800 |
SQL aliases are used to give a column a temporary name to make column names more readable.
SELECT class, name, fees, fees*12 AS annualfees FROM student;
| class | name | fees | annualfees |
| K12 | Alex | 7800 | 93600 |
| K12 | Peter | 6700 | 80400 |
| K11 | Alisha | 7900 | 94800 |
| K11 | John | 6900 | 82800 |
The IS NULL operator is used to check empty values (NULL values).
SELECT * FROM student WHERE fees IS NULL;
SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
COUNT() To count the number of rows
SUM() To find the sum of values in the column
MAX() To find the maximum value in the column
MIN() To find the minimum value in the column
AVG() To find the average of values in the column
SELECT COUNT(*) FROM student;
| COUNT(*) |
| 4 |
SELECT COUNT(rollnumber) FROM student;
| COUNT(rollnumber) |
| 4 |
SELECT SUM(fees) FROM student;
| SUM(fees) |
| 29300 |
SELECT AVG(fees) FROM student;
| AVG(fees) |
| 7325.0000 |
SELECT MAX(fees), MIN(fees) FROM student;
| MAX(fees) | MIN(fees) |
| 7900 | 6700 |
The GROUP BY statement groups rows that have the same values. The GROUP BY statement is used with aggregate functions
SELECT class, SUM(fees) FROM student GROUP BY class;
| class | SUM(fees) |
| K11 | 14800 |
| K12 | 14500 |
SELECT class, MAX(fees), MIN(fees) FROM student GROUP BY class;
| class | MAX(fees) | MIN(fees) |
| K11 | 7900 | 6900 |
| K12 | 7800 | 6700 |
SELECT class, MAX(dob) FROM student GROUP BY class HAVING COUNT(*)>1;
| class | MAX(dob) |
| K11 | 2000-12-13 |
| K12 | 1998-10-03 |
The DELETE statement is used to delete existing records in a table.
DELETE FROM Student WHERE rollnumber = 13;
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
ALTER TABLE student ADD grade CHAR(2);
ALTER TABLE student MODIFY grade CHAR(1);
ALTER TABLE student DROP COLUMN grade;
To create a PRIMARY KEY constraint when the table is already created:
ALTER TABLE student ADD PRIMARY KEY (rollnumber);
To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE student DROP PRIMARY KEY;
The DROP TABLE statement is used to drop an existing table in a database.
DROP TABLE student;
SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;
Table - product
| product_id | product_name | supplier_name | unit_price |
| 100 | Camera | Nikon | 300 |
| 101 | Television | Onida | 100 |
| 102 | Refrigerator | Videocon | 150 |
| 103 | Ipod | Apple | 75 |
| 104 | Mobile | Nokia | 50 |
Table - order_items
| order_id | product_id | total_units | customer |
| 5100 | 104 | 30 | Infosys |
| 5101 | 102 | 5 | Satyam |
| 5102 | 103 | 25 | Wipro |
| 5103 | 101 | 10 | TCS |
SELECT order_id, product_name, unit_price, supplier_name FROM product, order_items WHERE order_items.product_id = product.product_id;
| order_id | product_name | unit_price | supplier_name |
| 5100 | Mobile | 50 | Nokia |
| 5101 | Refrigerator | 150 | Videocon |
| 5102 | Ipod | 75 | Apple |
| 5103 | Television | 100 | Onida |