What is a Structured Query Language (SQL)?
SQL is a standard language for storing, retrieving and manipulating data on a relational database. All the relational database like MySql, Oracle, MS Access, SQL server uses Structured query language(SQL) for accessing and manipulating data.
SQL provides wide range of effective command to perform all sort of required operations on data such as create tables, insert record, view recodes, update, alter, delete, drop, etc.
What is DDL and DML?
All the SQL commands are categorized into five categories: DDL,DML,DCL,DQL,TCL. In this course we are going to cover only DDL and DML commands in detail.
Data definition Language(DDL): Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. Example: Create, Drop, Alter, Truncate.
Data Manipulation Language(DML): The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. Example: Insert, Delete, Update.
Data Types in MySQL
Data stored in a database table are of different types, As SQL developers we have chose the suitable data types for each field while defining a table. SQL offers supports a wide range of data types from which the developer can choose the most appropriate data types for each column.
char(size): used for fixed length string data.
Example: A column defined as char(10) , it can contain string values of maximum 10 length. SQL allocates 10 bytes of memory irrespective of legth of data to be stored.
varchar(size): used for variable length string data.
Example: If a column defined as varchar(10) , SQL allocates maximum 10 bytes to each value, but bytes allocated may vary depending on the length of data.
int( ): Used for integer/digits data without decimal. Can accommodate maximum 11 digit numbers.
float(M,D): Permits real numbers upto M digits, out of which may be D digits after decimal .
Example: a column data type defined as float(6,3) may have 234.684
Date: used to store date in YYYY-MM-DD format.
Constraints In SQL
constraints are used to specify rules for the data in a table. Commonly used constraints are:
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- Prevents actions that would destroy links between tables
Check – Ensures that the values in a column satisfies a specific condition
Default- Sets a default value for a column if no value is specified
MySql Commands
CREATE Database: Used to create a new database.
Syntax: CREATE DATABASE <database name>
e.g. CREATE Database MySchool;
SHOW Databases: Used to list all existing databases.
Syntax: SHOW Databases;
DROP Database: Used to delete an existing database.
Syntax: DROP Database <databasename>
e.g. DROP Database MyStore;
USE Database: Used to select/enter a existing database.
e.g. USE MySchool;
Show Tables: After a database has been selected this command can be Used to list all the tables in the database. e.g. SHOW TABLES;
CREATE Table:
Syntax: CREATE TABLE <table name>( column1 datatype,
column2 datatype,
……..
columnN datatype,
PRIMARY KEY( one or more columns ) );
E.g. CREATE TABLE cs_students(sid int(3), sname varchar(30), sclass int(2), smark int(3), skill varchar(30), primary key(sid));
Creating a table with multiple constraints:
CREATE TABLE Employee (Eid int(5) Primary Key,
Ename varchar(30) Not Null,
age int(2),
Dept varchar(20) Default “Manufacturing”,
contactno int(10) unique,
Constraint ChkAge Check(Age>18));
DESCRIBE Tables: A DDL command to display the structure of the table.
Syntax: DESCRIBE <table name>;
ALTER Tables:
ALTER TABLE is a DDL command that can change the structure of the table. Using ALTER TABLE command we can add, delete or modify the attributes/constraints of a table.
Adding a column using Alter table:
Syntax: ALTER TABLE <table name> ADD column <Column Name Data type>;
Deleting a column using Alter table:
Syntax: ALTER TABLE <table name> DROP column <Column Name >;
Modify a column using Alter table:
Syntax: ALTER TABLE <table name> MODIFY column <Column Name Data type>;
E.g. MODIFY the data type of an existing column
Adding a Primary Key Constraint using Alter table:
Syntax: ALTER TABLE <table name> ADD Primary Key (<column names>);
Drop Primary Key Constraint using Alter table:
Syntax: ALTER TABLE <table name> DROP Primary Key;
DROP Tables: DROP TABLE is a DDL command used to delete a table from the database.
Syntax: DROP TABLE <table name>;
E.g. DROP Table Employee;
INSERT INTO:
INSERT is a DML command used to insert a new record/row in an existing table.
Syntax: INSERT INTO <Table Name> values (val1,val2,val3..);
Insert a new record in the table with specific field value.
Syntax: INSERT INTO <Table Name> (Column1,Column2,..ColumnN) values (val1,val2,..valN);
SELECT Command:
Used to retrieve and display data from the tables.
Syntax: SELECT column1, column2,..
FROM <Table Name>;
To Select all the columns from the table:
SELECT * FROM <Table Name>;
WHERE Clause:
The WHERE Clause can be used with SELECT command to select the data from the table based on some condition.
Syntax: SELECT column1, column2,..
FROM <Table Name>
WHERE <condition>;
Operators That Can Be Used In Where Clause :
Mathematical: +, -, *, /
Relational: >, >=, <, <=, =, <>
Logical: AND, OR, NOT
E.g. Select * From cs_students WHERE smark>90;
To select ID and Name of the students whose skill is Database:
Using Logical Operators in Where clause:
IN Operator: Used To specify multiple possible values for a column
E.g. Select * from cs_student where skill in(“Networking”, ”Database”);
BETWEEN Operator: Used To specify values in a certain range.
E.g. Select * from cs_student where smark BETWEEN 95 AND 100;
DISTINCT Clause: Used to retrieve the distinct values in a field.
Syntax: Select * from student where mark is null;
ORDER BY: It is used to sort the data in ascending or descending order.
By default ORDER BY sort the data in ascending order, for descending order we need to use ”DESC”.
Handling NULL Values: To handle NULL entries in a field we can use “IS” and “IS NOT”, as NULL value is a Value which is Unknown so we can use =, <> operators to select NULL values.
Lets Consider the Employee table above, to select all the employees whose salary is specified as NULL in the salary field we must use IS NULL operator.
LIKE OPERATOR
LIKE is used for string matching in MySql, it can be used for comparison of character strings using pattern. LIKE uses the following two wildcard characters to create string patterns.
•Percent(%): used to match a substring of any length.
•Underscore( _ ): Used to match any single character.
The LIKE keyword selects the rows having column values that matches with the wildcard pattern.
Update Command
UPDATE is a DML command used to change values in the rows of a existing table. It specifies the rows to be changed using WHERE clause and the new values to be updated using SET keyword.
Syntax: UPDATE <Table Name> SET column=<new value> WHERE <condition>
E.g. To change the salary to 70000 of the employee having Eid 204.
UPDATE employee SET salary=70000 WHERE Eid=204.
To change the Department of an employee
UPDATE employee SET Dept=“Marketing” where Ename=“Kunal”;
Delete Command :
Delete is a DML command used to delete rows of an existing table. It specifies the rows to be deleted using WHERE clause.
Syntax: DELETE FROM <Table Name> WHERE <condition;
To delete the record/row of the employee having Eid 204.
DELETE FROM employee WHERE Eid=204;
To delete the records of all the employee working in Sales Department.
DELETE FROM employee WHERE Dept=“salary”;
To delete all rows of employee table
DELETE FROM employee;
Aggregate Functions :
MySql supports the following aggregate/multiple row functions:
•count( ): returns the number of rows in the given column or expression.
•min( ): returns the minimum value in the given column or expression.
•max( ): returns the maximum value in the given column or expression.
•sum( ): returns the sum of values in the given column or expression.
•avg( ): returns the average of values in the given column or expression.
Let us Consider the employee table:
SELECT sum(salary) FROM employee;
Result: 80000
SELECT avg(salary) FROM employee;
Result: 26666.6666
SELECT max(salary) FROM employee;
Result: 32000
SELECT min(salary) FROM employee;
Result: 23000
SELECT count(salary) FROM employee;
Result: 3
SELECT count(*) FROM employee;
Result: 5
GROUP BY:
•GROUP BY clause combines all those records that have identical values in a particular field or a group of fields.
•It is used in SELECT statement to divide the table into groups. Grouping can be done by a column name or with aggregate functions.
For Example let up consider the cs_students table,
To find the number of students in each skill, we can use the command.
SELECT skill, count(*) from cs_students GROUP BY skill;
Let us now consider the Employee Table
To find the average salary of employees of each department, we can use the command.
SELECT dept, avg(salary) from employee GROUP BY dept;
HAVING Clause:
HAVING clause is used to apply conditions on groups in contrast to WHERE clause which is used to apply conditions on individual rows.
Let us consider the cs_students table,
To find the average marks of the group of students having a particular skill , where the skill group must have at least 5 students.
SELECT skill, avg(smark) FROM cs_students GROUP BY skill HAVING count(*)>=5;
Let us consider the employee table,
To find the average salary of employees of each department, where the average age of all the employees working in the department is less then 32.
SELECT dept,avg(salary) FROM employee GROUP BY dept HAVING avg(age)>32;
JOIN:
A JOIN clause combines rows from two or more tables. In a join query, more then one table are listed in FORM clause.
Types of Join Operation:
•Cartesian product on two tables,
•Equi-join
•Natural join
Cartesian Product (X):
The Cartesian Product operation of two tables produces all possible concatenations of all the rows of both tables.
The Cartesian product(also known as Cross Join) multiplies all rows present in the first table with all the rows present in the second table
Syntax: SELECT * FROM Table1,Table2;
Or
SELECT * FROM Table1 CROSS JOIN Table2;
The Cardinality of cartesian product of two relations R1 and R2 is equal to the multiplication of cardinalities of R1 and R2. Whereas The Degree of cartesian Product is equal to addition of degrees of R1 and R2.
Cartesian Product (X) Example:
Let us Consider the Cartesian Product/Cross Join the of following Customer and Order Tables
Equi Join :
To perform Equi/Inner Join on two relations R1 and R2, we have to specify a equality condition using the common attribute in both the relations R1 and R2. Syntax: SELECT * FROM R1 Inner Join R2.
Natural Join :
The Join in which only one of the identical columns(coming from joined tables) exists, is called as Natural Join.
The Equi Join and Natural join are equivalent except that duplicate columns are eliminated in the Natural Join that would otherwise appear in Equi Join.
Syntax: SELECT * FROM Table1 Natural Join Table2
JOIN Examples:
