sql injection

Uncategorized

SET Operations in SQL

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

SQL supports few Set operations which can be performed on the table data. These are used to get meaningful results from data stored in the table, under different special conditions.

In this tutorial, we will cover 4 different types of SET operations, along with example:

UNION
UNION ALL
INTERSECT
MINUS

UNION Operation

UNION is used to combine the results of two or more SELECT statements. However it will eliminate duplicate rows from its resultset. In case of union, number of columns and datatype must be same in both the tables, on which UNION operation is being applied.

Example of UNION

The First table,

ID	Name
1	abhi
2	adam

The Second table,

ID	Name
2	adam
3	Chester

Union SQL query will be,

SELECT * FROM First 
UNION
SELECT * FROM Second;

The resultset table will look like,

ID	NAME
1	abhi
2	adam
3	Chester

UNION ALL

This operation is similar to Union. But it also shows the duplicate rows.

Example of Union All

The First table,

ID	NAME
1	abhi
2	adam

The Second table,

ID	NAME
2	adam
3	Chester

Union All query will be like,

SELECT * FROM First 
UNION ALL
SELECT * FROM Second;

The resultset table will look like,

ID	NAME
1	abhi
2	adam
2	adam
3	Chester

INTERSECT

Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements. In case of Intersect the number of columns and datatype must be same.

NOTE: MySQL does not support INTERSECT operator.

Example of Intersect

The First table,

ID	NAME
1	abhi
2	adam

The Second table,

ID	NAME
2	adam
3	Chester

Intersect query will be,

SELECT * FROM First 
INTERSECT
SELECT * FROM Second;

The resultset table will look like

ID	NAME
2	adam

MINUS

The Minus operation combines results of two SELECT statements and return only those in the final result, which belongs to the first set of the result.

Example of Minus

The First table,

ID	NAME
1	abhi
2	adam

The Second table,

ID	NAME
2	adam
3	Chester

Minus query will be,

SELECT * FROM First 
MINUS
SELECT * FROM Second;

The resultset table will look like,

ID	NAME
1	abhi

< prev

Next >

Uncategorized

SQL Constraints

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints can be divided into the following two types,

Column level constraints: Limits only column data.
Table level constraints: Limits whole table data.

Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table.

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

`NOT NULL` Constraint

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value.

One important point to note about this constraint is that it cannot be defined at table level.

Example using `NOT NULL` constraint

CREATE TABLE Student(s_id int NOT NULL, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will not take NULL value.

`UNIQUE` Constraint

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. This constraint can be applied at column level or table level.

Using `UNIQUE` constraint when creating a Table (Table Level)

Here we have a simple CREATE query to create a table, which will have a column s_id with unique values.

CREATE TABLE Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.

Using `UNIQUE` constraint after Table is created (Column Level)

ALTER TABLE Student ADD UNIQUE(s_id);

The above query specifies that s_id field of Student table will only have unique value.

Primary Key Constraint

Primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.

Using PRIMARY KEY constraint at Table Level

CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);

The above command will creates a PRIMARY KEY on the s_id.

Using PRIMARY KEY constraint at Column Level

ALTER table Student ADD PRIMARY KEY (s_id);

The above command will creates a PRIMARY KEY on the s_id.

Foreign Key Constraint

FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables. To understand FOREIGN KEY, let’s see its use, with help of the below tables:

Customer_Detail Table

c_id	Customer_Name	address
101	Adam	Noida
102	Alex	Delhi
103	Stuart	Rohtak

Order_Detail Table

Order_id	Order_Name	c_id
10	Order1	101
11	Order2	103
12	Order3	102

In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detail table where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detail table.

If you try to insert any incorrect data, DBMS will return error and will not allow you to insert the data.

Using FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(
    order_id int PRIMARY KEY, 
    order_name varchar(60) NOT NULL,
    c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id)
);

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column in Customer_Detail table.

Using FOREIGN KEY constraint at Column Level

ALTER table Order_Detail ADD FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in the main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which a record exits in the child table, then we must have some mechanism to save the integrity of data in the child table.

foriegn key behaviour on delete - cascade and Null

On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.
On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is deleted from the main table.
If we don’t use any of the above, then we cannot delete data from the main table for which data in child table exists. We will get an error if we try to do so.

`CHECK` Constraint

CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column.

Using `CHECK` constraint at Table Level

CREATE table Student(
    s_id int NOT NULL CHECK(s_id > 0),
    Name varchar(60) NOT NULL,
    Age int
);

The above query will restrict the s_id value to be greater than zero.

Using `CHECK` constraint at Column Level

ALTER table Student ADD CHECK(s_id > 0);

< prev

next >

Uncategorized

`DISTINCT` keyword

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

The distinct keyword is used with SELECT statement to retrieve unique values from the table. Distinct removes all the duplicate records while retrieving records from any table in the database.

Syntax for `DISTINCT` Keyword

SELECT DISTINCT column-name FROM table-name;

Example using `DISTINCT` Keyword

Consider the following Emp table. As you can see in the table below, there is employee name, along with employee salary and age.

In the table below, multiple employees have the same salary, so we will be using DISTINCT keyword to list down distinct salary amount, that is currently being paid to the employees.

eid	name	age	salary
401	Anu	22	5000
402	Shane	29	8000
403	Rohan	34	10000
404	Scott	44	10000
405	Tiger	35	8000

SELECT DISTINCT salary FROM Emp;

The above query will return only the unique salary from Emp table.

salary
5000
8000
10000

< prev

Next >

Uncategorized

SQL `HAVING` Clause

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group by based SQL queries, just like WHERE clause is used with SELECT query.

Syntax for HAVING clause is,

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition

Example of SQL Statement using `HAVING`

Consider the following Sale table.

oid	order_name	previous_balance	customer
11	ord1	2000	Alex
12	ord2	1000	Adam
13	ord3	2000	Abhi
14	ord4	1000	Adam
15	ord5	2000	Alex

Suppose we want to find the customer whose previous_balance sum is more than 3000.

We will use the below SQL query,

SELECT *
FROM sale GROUP BY customer
HAVING sum(previous_balance) > 3000

Result will be,

oid	order_name	previous_balance	customer
11	ord1	2000	Alex

The main objective of the above SQL query was to find out the name of the customer who has had a previous_balance more than 3000, based on all the previous sales made to the customer, hence we get the first row in the table for customer Alex.

< prev

Next >

Uncategorized

SQL `Group By` Clause

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the result from one or more tables.

Syntax for using Group by in a statement.

SELECT column_name, function(column_name)
FROM table_name 
WHERE condition 
GROUP BY column_name

Example of `Group by` in a Statement

Consider the following Emp table.

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	9000
405	Tiger	35	8000

Here we want to find name and age of employees grouped by their salaries or in other words, we will be grouping employees based on their salaries, hence, as a result, we will get a data set, with unique salaries listed, along side the first employee’s name and age to have that salary. Hope you are getting the point here!

group by is used to group different row of data together based on any one column.

SQL query for the above requirement will be,

SELECT name, age 
FROM Emp GROUP BY salary

Result will be,

name	age
Rohan	34
Shane	29
Anu	22

Example of `Group by` in a Statement with `WHERE` clause

Consider the following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	9000
405	Tiger	35	8000

SQL query will be,

SELECT name, salary 
FROM Emp 
WHERE age > 25
GROUP BY salary

Result will be.

name	salary
Rohan	6000
Shane	8000
Scott	9000

You must remember that Group By clause will always come at the end of the SQL query, just like the Order by clause.

< prev

Next >

Uncategorized

SQL `ORDER BY` Clause

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Order by clause is used with SELECT statement for arranging retrieved data in sorted order. The Order by clause by default sorts the retrieved data in ascending order. To sort the data in descending order DESC keyword is used with Order by clause.

Syntax of `Order By`

SELECT column-list|* FROM table-name ORDER BY ASC | DESC;

Using default `Order by`

Consider the following Emp table,

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SELECT * FROM Emp ORDER BY salary;

The above query will return the resultant data in ascending order of the salary.

eid	name	age	salary
403	Rohan	34	6000
402	Shane	29	8000
405	Tiger	35	8000
401	Anu	22	9000
404	Scott	44	10000

Using Order by `DESC`

Consider the Emp table described above,

SELECT * FROM Emp ORDER BY salary DESC;

The above query will return the resultant data in descending order of the salary.

eid	name	age	salary
404	Scott	44	10000
401	Anu	22	9000
405	Tiger	35	8000
402	Shane	29	8000
403	Rohan	34	6000

< prev

Next >

Uncategorized

`SELECT` SQL Query

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

SELECT query is used to retrieve data from a table. It is the most used SQL query. We can retrieve complete table data, or partial by specifying conditions using the WHERE clause.

Syntax of `SELECT` query

SELECT query is used to retieve records from a table. We can specify the names of the columns which we want in the resultset.

SELECT 
    column_name1, 
    column_name2, 
    column_name3, 
    ...
    column_nameN 
    FROM table_name;

Time for an Example

Consider the following student table,

s_id	name	age	address
101	Adam	15	Chennai
102	Alex	18	Delhi
103	Abhi	17	Banglore
104	Ankit	22	Mumbai

SELECT s_id, name, age FROM student;

The above query will fetch information of s_id, name and age columns of the student table and display them,

s_id	name	age
101	Adam	15
102	Alex	18
103	Abhi	17
104	Ankit	22

As you can see the data from address column is absent, because we did not specif it in our SELECT query.

Select all records from a table

A special character asterisk * is used to address all the data(belonging to all columns) in a query. SELECT statement uses * character to retrieve all records from a table, for all the columns.

SELECT * FROM student;

The above query will show all the records of student table, that means it will show complete dataset of the table.

s_id	name	age	address
101	Adam	15	Chennai
102	Alex	18	Delhi
103	Abhi	17	Banglore
104	Ankit	22	Mumbai

Select a particular record based on a condition

We can use the WHERE clause to set a condition,

SELECT * FROM student WHERE name = 'Abhi';

The above query will return the following result,

103

Abhi

Rohtak

Performing Simple Calculations using `SELECT` Query

Consider the following employee table.

eid	name	age	salary
101	Adam	26	5000
102	Ricky	42	8000
103	Abhi	25	10000
104	Rohan	22	5000

Here is our SELECT query,

SELECT eid, name, salary+3000  FROM employee;

The above command will display a new column in the result, with 3000 added into existing salaries of the employees.

eid	name	salary+3000
101	Adam	8000
102	Ricky	11000
103	Abhi	13000
104	Rohan	8000

So you can also perform simple mathematical operations on the data too using the SELECT query to fetch data from table.

< prev

Next >

Uncategorized

Commit, Rollback and Savepoint SQL commandscommit commands

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.

`COMMIT` command

COMMIT command is used to permanently save any transaction into the database.

When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.

To avoid that, we use the COMMIT command to mark the changes as permanent.

Following is commit command’s syntax,

COMMIT;

`ROLLBACK` command

This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.

If we have used the UPDATE command to make some changes into the database, and realise that those changes were not required, then we can use the ROLLBACK command to rollback those changes, if they were not commited using the COMMIT command.

Following is rollback command’s syntax,

ROLLBACK TO savepoint_name;

`SAVEPOINT` command

SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever required.

Following is savepoint command’s syntax,

SAVEPOINT savepoint_name;

In short, using this command we can name the different states of our data in any table and then rollback to that state using the ROLLBACK command whenever required.

Using Savepoint and Rollback

Following is the table class,

id	name
1	Abhi
2	Adam
4	Alex

Lets use some SQL queries on the above table and see the results.

INSERT INTO class VALUES(5, 'Rahul');

COMMIT;

UPDATE class SET name = 'Abhijit' WHERE id = '5';

SAVEPOINT A;

INSERT INTO class VALUES(6, 'Chris');

SAVEPOINT B;

INSERT INTO class VALUES(7, 'Bravo');

SAVEPOINT C;

SELECT * FROM class;

The resultant table will look like,

id	name
1	Abhi
2	Adam
4	Alex
5	Abhijit
6	Chris
7	Bravo

Now let’s use the ROLLBACK command to roll back the state of data to the savepoint B.

ROLLBACK TO B;

SELECT * FROM class;

Now our class table will look like,

id	name
1	Abhi
2	Adam
4	Alex
5	Abhijit
6	Chris

Now let’s again use the ROLLBACK command to roll back the state of data to the savepoint A

ROLLBACK TO A;

SELECT * FROM class;

Now the table will look like,

id	name
1	Abhi
2	Adam
4	Alex
5	Abhijit

So now you know how the commands COMMIT, ROLLBACK and SAVEPOINT works.

< prev

Next >

Uncategorized

Using `DELETE` SQL command

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

When you ask any question in Studytonight’s Forum it gets saved into a table. And using the Delete option, you can even delete a question asked by you. How do you think that works? Yes, using the Delete DML command.

Let’s study about the syntax and the usage of the Delete command.

`DELETE` command

DELETE command is used to delete data from a table.

Following is its general syntax,

DELETE FROM table_name;

Let’s take a sample table student:

s_id	name	age
101	Adam	15
102	Alex	18
103	Abhi	17

Delete all Records from a Table

DELETE FROM student;

The above command will delete all the records from the table student.

Delete a particular Record from a Table

In our student table if we want to delete a single record, we can use the WHERE clause to provide a condition in our DELETE statement.

DELETE FROM student WHERE s_id=103;

The above command will delete the record where s_id is 103 from the table student.

S_id	S_Name	age
101	Adam	15
102	Alex	18

Isn’t `DELETE` same as `TRUNCATE`

TRUNCATE command is different from DELETE command. The delete command will delete all the rows from a table whereas truncate command not only deletes all the records stored in the table, but it also re-initializes the table(like a newly created table).

For eg: If you have a table with 10 rows and an auto_increment primary key, and if you use DELETE command to delete all the rows, it will delete all the rows, but will not re-initialize the primary key, hence if you will insert any row after using the DELETE command, the auto_increment primary key will start from 11. But in case of TRUNCATE command, primary key is re-initialized, and it will again start from 1.

< prev

Next >

Uncategorized

Using `UPDATE` SQL command

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Let’s take an example of a real-world problem. These days, Facebook provides an option for Editing your status update, how do you think it works? Yes, using the Update SQL command.

Let’s learn about the syntax and usage of the UPDATE command.

`UPDATE` command

UPDATE command is used to update any record of data in a table. Following is its general syntax,

UPDATE table_name SET column_name = new_value WHERE some_condition;

Lets take a sample table student,

student_id	name	age
101	Adam	15
102	Alex
103	chris	14

UPDATE student SET age=18 WHERE student_id=102;

S_id	S_Name	age
101	Adam	15
102	Alex	18
103	chris	14

In the above statement, if we do not use the WHERE clause, then our update query will update age for all the columns of the table to 18.

Updating Multiple Columns

We can also update values of multiple columns using a single UPDATE statement.

UPDATE student SET name='Abhi', age=17 where s_id=103;

The above command will update two columns of the record which has s_id 103.

s_id	name	age
101	Adam	15
102	Alex	18
103	Abhi	17

`UPDATE` Command: Incrementing Integer Value

When we have to update any integer value in a table, then we can fetch and update the value in the table in a single statement.

For example, if we have to update the age column of student table every year for every student, then we can simply run the following UPDATE statement to perform the following operation:

UPDATE student SET age = age+1;

As you can see, we have used age = age + 1 to increment the value of age by 1.

< prev

Next >

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

UNION Operation

Example of UNION

UNION ALL

Example of Union All

INTERSECT

Example of Intersect

MINUS

Example of Minus

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

NOT NULL Constraint

Example using NOT NULL constraint

UNIQUE Constraint

Using UNIQUE constraint when creating a Table (Table Level)

Using UNIQUE constraint after Table is created (Column Level)

Primary Key Constraint

Using PRIMARY KEY constraint at Table Level

Using PRIMARY KEY constraint at Column Level

Foreign Key Constraint

Using FOREIGN KEY constraint at Table Level

Using FOREIGN KEY constraint at Column Level

Behaviour of Foriegn Key Column on Delete

CHECK Constraint

Using CHECK constraint at Table Level

Using CHECK constraint at Column Level

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Syntax for DISTINCT Keyword

Example using DISTINCT Keyword

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Example of SQL Statement using HAVING

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Example of Group by in a Statement

Example of Group by in a Statement with WHERE clause

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Syntax of Order By

Using default Order by

Using Order by DESC

Database Concept

`NOT NULL` Constraint

Example using `NOT NULL` constraint

`UNIQUE` Constraint

Using `UNIQUE` constraint when creating a Table (Table Level)

Using `UNIQUE` constraint after Table is created (Column Level)

`CHECK` Constraint

Using `CHECK` constraint at Table Level

Using `CHECK` constraint at Column Level

Syntax for `DISTINCT` Keyword

Example using `DISTINCT` Keyword

Example of SQL Statement using `HAVING`

Example of `Group by` in a Statement

Example of `Group by` in a Statement with `WHERE` clause

Syntax of `Order By`

Using default `Order by`

Using Order by `DESC`

Syntax of `SELECT` query

Performing Simple Calculations using `SELECT` Query

`COMMIT` command

`ROLLBACK` command

`SAVEPOINT` command

`DELETE` command

Isn’t `DELETE` same as `TRUNCATE`

`UPDATE` command

`UPDATE` Command: Incrementing Integer Value