SQL Command

Uncategorized

Division Operator in SQL

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

The division operator is used when we have to evaluate queries which contain the keyword ALL.

Some instances where division operator is used are:

Which person has account in all the banks of a particular city?
Which students have taken all the courses required to graduate?

In above specified problem statements, the description after the keyword 'all' defines a set which contains some elements and the final result contains those units which satisfy these requirements.

Another way how you can identify the usage of division operator is by using the logical implication of if...then. In context of the above two examples, we can see that the queries mean that,

If there is a bank in that particular city, that person must have an account in that bank.
If there is a course in the list of courses required to be graduated, that person must have taken that course.

Do not worry if you are not clear with all this new things right away, we will try to expain as we move on with this tutorial.

We shall see the second example, mentioned above, in detail.

Table 1: Course_Taken → It consists of the names of Students against the courses that they have taken.

Student_Name	Course
Robert	Databases
Robert	Programming Languages
David	Databases
David	Operating Systems
Hannah	Programming Languages
Hannah	Machine Learning
Tom	Operating Systems

Table 2: Course_Required → It consists of the courses that one is required to take in order to graduate.

Course
Databases
Programming Languages

Using Division Operator

So now, let’s try to find out the correct SQL query for getting results for the first requirement, which is:

Unfortunately, there is no direct way by which we can express the division operator. Let’s walk through the steps, to write the query for the division operator.

1. Find all the students

Create a set of all students that have taken courses. This can be done easily using the following command.

CREATE TABLE AllStudents AS SELECT DISTINCT Student_Name FROM Course_Taken

This command will return the table AllStudents, as the resultset:

Student_name
Robert
David
Hannah
Tom

2. Find all the students and the courses required to graduate

Next, we will create a set of students and the courses they need to graduate. We can express this in the form of Cartesian Product of AllStudents and Course_Required using the following command.

CREATE table StudentsAndRequired AS 
SELECT AllStudents.Student_Name, Course_Required.Course
FROM AllStudents, Course_Required

Now the new resultset – table StudentsAndRequired will be:

Student_Name	Course
Robert	Databases
Robert	Programming Languages
David	Databases
David	Programming Languages
Hannah	Databases
Hannah	Programming Languages
Tom	Databases
Tom	Programming Languages

3. Find all the students and the required courses they have not taken

Here, we are taking our first step for finding the students who cannot graduate. The idea is to simply find the students who have not taken certain courses that are required for graduation and hence they wont be able to graduate. This is simply all those tuples/rows which are present in StudentsAndRequired and not present in Course_Taken.

CREATE  table StudentsAndNotTaken AS 
SELECT * FROM StudentsAndRequired WHERE NOT EXISTS 
(Select * FROM Course_Taken WHERE StudentsAndRequired.Student_Name = Course_Taken.Student_Name 
AND StudentsAndRequired.Course = Course_Taken.Course)

The table StudentsAndNotTaken comes out to be:

Student_Name	Course
David	Programming Languages
Hannah	Databases
Tom	Databases
Tom	Programming Languages

4. Find all students who cannot graduate

All the students who are present in the table StudentsAndNotTaken are the ones who cannot graduate. Therefore, we can find the students who cannot graduate as,

CREATE table CannotGraduate AS SELECT DISTINCT Student_Name FROM StudentsAndNotTaken

Student_name
David
Hannah
Tom

5. Find all students who can graduate

The students who can graduate are simply those who are present in AllStudents but not in CannotGraduate. This can be done by the following query:

CREATE Table CanGraduate AS SELECT * FROM AllStudents 
WHERE NOT EXISTS 
(SELECT * FROM CannotGraduate WHERE 
    CannotGraduate.Student_name = AllStudents.Student_name)

The results will be as follows:

Student_name
Robert

Hence we just learned, how different steps can lead us to the final answer. Now let us see how to write all these 5 steps in one single query so that we do not have to create so many tables.

SELECT DISTINCT  x.Student_Name FROM Course_Taken AS x WHERE NOT 
EXISTS(SELECT * FROM Course_Required AS y WHERE NOT 
EXISTS(SELECT * FROM Course_Taken AS z 
    WHERE z.Student_name = x.Student_name 
    AND z.Course = y.Course ))

Student_name
Robert

This gives us the same result just like the 5 steps above.

< prev

Next >

Uncategorized

What is an SQL Sequence?

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Sequence is a feature supported by some database systems to produce unique values on demand. Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.

AUTO_INCREMENT is applied on columns, it automatically increments the column value by 1 each time a new record is inserted into the table.

Sequence is also some what similar to AUTO_INCREMENT but it has some additional features too.

Creating a Sequence

Syntax to create a sequence is,

CREATE SEQUENCE sequence-name
    START WITH initial-value
    INCREMENT BY increment-value
    MAXVALUE maximum-value
    CYCLE | NOCYCLE;

The initial-value specifies the starting value for the Sequence.
The increment-value is the value by which sequence will be incremented.
The maximum-value specifies the upper limit or the maximum value upto which sequence will increment itself.
The keyword CYCLE specifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining.
And, NO CYCLE specifies that if sequence exceeds MAXVALUE value, an error will be thrown.

Using Sequence in SQL Query

Let’s start by creating a sequence, which will start from 1, increment by 1 with a maximum value of 999.

CREATE SEQUENCE seq_1
START WITH 1
INCREMENT BY 1
MAXVALUE 999
CYCLE;

Now let’s use the sequence that we just created above.

Below we have a class table,

ID	NAME
1	abhi
2	adam
4	alex

The SQL query will be,

INSERT INTO class VALUE(seq_1.nextval, 'anu');

Resultset table will look like,

ID	NAME
1	abhi
2	adam
4	alex
1	anu

Once you use nextval the sequence will increment even if you don’t Insert any record into the table.

< prev

Next >

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 Alias – `AS` Keyword

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

Alias is used to give an alias name to a table or a column, which can be a resultset table too. This is quite useful in case of large or complex queries. Alias is mainly used for giving a short alias name for a column or a table with complex names.

Syntax of Alias for table names,

SELECT column-name FROM table-name AS alias-name

Following is an SQL query using alias,

SELECT * FROM Employee_detail AS ed;

Syntax for defining alias for columns will be like,

SELECT column-name AS alias-name FROM table-name;

Example using alias for columns,

SELECT customer_id AS cid FROM Emp;

Example of Alias in SQL Query

Consider the following two tables,

The class table,

ID	Name
1	abhi
2	adam
3	alex
4	anu
5	ashish

and the class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Below is the Query to fetch data from both the tables using SQL Alias,

SELECT C.id, C.Name, Ci.Address from Class AS C, Class_info AS Ci where C.id = Ci.id;

and the resultset table will look like,

ID	Name	Address
1	abhi	DELHI
2	adam	MUMBAI
3	alex	CHENNAI

SQL Alias seems to be quite a simple feature of SQL, but it is highly useful when you are working with more than 3 tables and have to use JOIN on them.

< prev

Next >

Uncategorized

SQL JOIN

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. It is used for combining column from two or more tables by using values common to both tables.

JOIN Keyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is (n-1) where n, is number of tables. A table can also join to itself, which is known as, Self Join.

Types of JOIN

Following are the types of JOIN that we can use in SQL:

Inner
Outer
Left
Right

Cross JOIN or Cartesian Product

This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table.

Cross JOIN Syntax is,

SELECT column-name-list
FROM 
table-name1 CROSS JOIN table-name2;

Example of Cross JOIN

Following is the class table,

ID	NAME
1	abhi
2	adam
4	alex

and the class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Cross JOIN query will be,

SELECT * FROM 
class CROSS JOIN class_info;

The resultset table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	1	DELHI
4	alex	1	DELHI
1	abhi	2	MUMBAI
2	adam	2	MUMBAI
4	alex	2	MUMBAI
1	abhi	3	CHENNAI
2	adam	3	CHENNAI
4	alex	3	CHENNAI

As you can see, this join returns the cross product of all the records present in both the tables.

INNER Join or EQUI Join

This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the SQL query.

Inner Join Syntax is,

SELECT column-name-list FROM 
table-name1 INNER JOIN table-name2 
WHERE table-name1.column-name = table-name2.column-name;

Example of INNER JOIN

Consider a class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu

and the class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Inner JOIN query will be,

SELECT * from class INNER JOIN class_info where class.id = class_info.id;

The resultset table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI

Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

The syntax for Natural Join is,

SELECT * FROM 
table-name1 NATURAL JOIN table-name2;

Example of Natural JOIN

Here is the class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu

and the class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info;

The resultset table will look like,

ID	NAME	Address
1	abhi	DELHI
2	adam	MUMBAI
3	alex	CHENNAI

In the above example, both the tables being joined have ID column(same name and same datatype), hence the records for which value of ID matches in both the tables will be the result of Natural Join of these two tables.

OUTER JOIN

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

Left Outer Join
Right Outer Join
Full Outer Join

LEFT Outer Join

The left outer join returns a resultset table with the matched data from the two tables and then the remaining rows of the left table and null from the right table’s columns.

Syntax for Left Outer Join is,

SELECT column-name-list FROM 
table-name1 LEFT OUTER JOIN table-name2
ON table-name1.column-name = table-name2.column-name;

To specify a condition, we use the ON keyword with Outer Join.

Left outer Join Syntax for Oracle is,

SELECT column-name-list FROM 
table-name1, table-name2 on table-name1.column-name = table-name2.column-name(+);

Example of Left Outer Join

Here is the class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

and the class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Left Outer Join query will be,

SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id = class_info.id);

The resultset table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
4	anu	null	null
5	ashish	null	null

RIGHT Outer Join

The right outer join returns a resultset table with the matched data from the two tables being joined, then the remaining rows of the right table and null for the remaining left table’s columns.

Syntax for Right Outer Join is,

SELECT column-name-list FROM 
table-name1 RIGHT OUTER JOIN table-name2 
ON table-name1.column-name = table-name2.column-name;

Right outer Join Syntax for Oracle is,

SELECT column-name-list FROM 
table-name1, table-name2 
ON table-name1.column-name(+) = table-name2.column-name;

Example of Right Outer Join

Once again the class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

and the class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info ON (class.id = class_info.id);

The resultant table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
null	null	7	NOIDA
null	null	8	PANIPAT

Full Outer Join

The full outer join returns a resultset table with the matched data of two table then remaining rows of both left table and then the right table.

Syntax of Full Outer Join is,

SELECT column-name-list FROM 
table-name1 FULL OUTER JOIN table-name2
ON table-name1.column-name = table-name2.column-name;

Example of Full outer join is,

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

and the class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info ON (class.id = class_info.id);

The resultset table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
4	anu	null	null
5	ashish	null	null
null	null	7	NOIDA
null	null	8	PANIPAT

< prev

Next >

Uncategorized

What are SQL Functions?

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

SQL provides many built-in functions to perform operations on data. These functions are useful while performing mathematical calculations, string concatenations, sub-strings etc. SQL functions are divided into two categories,

Aggregate Functions
Scalar Functions

Aggregate Functions

These functions return a single value after performing calculations on a group of values. Following are some of the frequently used Aggregrate functions.

`AVG()` Function

Average returns average value after calculating it from values in a numeric column.

Its general syntax is,

SELECT AVG(column_name) FROM table_name

Using AVG() function

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

SQL query to find average salary will be,

SELECT avg(salary) from Emp;

Result of the above query will be,

avg(salary)
8200

`COUNT()` Function

Count returns the number of rows present in the table either based on some condition or without condition.

Its general syntax is,

SELECT COUNT(column_name) FROM table-name

Using COUNT() function

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

SQL query to count employees, satisfying specified condition is,

SELECT COUNT(name) FROM Emp WHERE salary = 8000;

Result of the above query will be,

count(name)
2

Example of COUNT(distinct)

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

SQL query is,

SELECT COUNT(DISTINCT salary) FROM emp;

Result of the above query will be,

count(distinct salary)
4

`FIRST()` Function

First function returns first value of a selected column

Syntax for FIRST function is,

SELECT FIRST(column_name) FROM table-name;

Using FIRST() function

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

SQL query will be,

SELECT FIRST(salary) FROM Emp;

and the result will be,

first(salary)
9000

`LAST()` Function

LAST function returns the return last value of the selected column.

Syntax of LAST function is,

SELECT LAST(column_name) FROM table-name;

Using LAST() function

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

SQL query will be,

SELECT LAST(salary) FROM emp;

Result of the above query will be,

last(salary)
8000

`MAX()` Function

MAX function returns maximum value from selected column of the table.

Syntax of MAX function is,

SELECT MAX(column_name) from table-name;

Using MAX() function

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

SQL query to find the Maximum salary will be,

SELECT MAX(salary) FROM emp;

Result of the above query will be,

MAX(salary)
10000

`MIN()` Function

MIN function returns minimum value from a selected column of the table.

Syntax for MIN function is,

SELECT MIN(column_name) from table-name;

Using MIN() function

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

SQL query to find minimum salary is,

SELECT MIN(salary) FROM emp;

Result will be,

MIN(salary)
6000

`SUM()` Function

SUM function returns total sum of a selected columns numeric values.

Syntax for SUM is,

SELECT SUM(column_name) from table-name;

Using SUM() function

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

SQL query to find sum of salaries will be,

SELECT SUM(salary) FROM emp;

Result of above query is,

SUM(salary)
41000

Scalar Functions

Scalar functions return a single value from an input value. Following are some frequently used Scalar Functions in SQL.

`UCASE()` Function

UCASE function is used to convert value of string column to Uppercase characters.

Syntax of UCASE,

SELECT UCASE(column_name) from table-name;

Using UCASE() function

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

SQL query for using UCASE is,

SELECT UCASE(name) FROM emp;

Result is,

UCASE(name)
ANU
SHANE
ROHAN
SCOTT
TIGER

`LCASE()` Function

LCASE function is used to convert value of string columns to Lowecase characters.

Syntax for LCASE is,

SELECT LCASE(column_name) FROM table-name;

Using LCASE() function

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

SQL query for converting string value to Lower case is,

SELECT LCASE(name) FROM emp;

Result will be,

LCASE(name)
anu
shane
rohan
scott
tiger

`MID()` Function

MID function is used to extract substrings from column values of string type in a table.

Syntax for MID function is,

SELECT MID(column_name, start, length) from table-name;

Using MID() function

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

SQL query will be,

SELECT MID(name,2,2) FROM emp;

Result will come out to be,

MID(name,2,2)
nu
ha
oh
co
ig

`ROUND()` Function

ROUND function is used to round a numeric field to number of nearest integer. It is used on Decimal point values.

Syntax of Round function is,

SELECT ROUND(column_name, decimals) from table-name;

Using ROUND() function

Consider the following Emp table

eid	name	age	salary
401	anu	22	9000.67
402	shane	29	8000.98
403	rohan	34	6000.45
404	scott	44	10000
405	Tiger	35	8000.01

SQL query is,

SELECT ROUND(salary) from emp;

Result will be,

ROUND(salary)
9001
8001
6000
10000
8000

< 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

SQL `AND` & `OR` operator

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

The AND and OR operators are used with the WHERE clause to make more precise conditions for fetching data from database by combining more than one condition together.

`AND` operator

AND operator is used to set multiple conditions with the WHERE clause, alongside, SELECT, UPDATE or DELETE SQL queries.

Example of `AND` operator

Consider the following Emp table

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

SELECT * FROM Emp WHERE salary < 10000 AND age > 25

The above query will return records where salary is less than 10000 and age greater than 25. Hope you get the concept here. We have used the AND operator to specify two conditions with WHERE clause.

eid	name	age	salary
402	Shane	29	8000
405	Tiger	35	9000

`OR` operator

OR operator is also used to combine multiple conditions with WHERE clause. The only difference between AND and OR is their behaviour.

When we use AND to combine two or more than two conditions, records satisfying all the specified conditions will be there in the result.

But in case of OR operator, atleast one condition from the conditions specified must be satisfied by any record to be in the resultset.

Example of `OR` operator

Consider the following Emp table

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

SELECT * FROM Emp WHERE salary > 10000 OR age > 25

The above query will return records where either salary is greater than 10000 or age is greater than 25.

402	Shane	29	8000
403	Rohan	34	12000
404	Scott	44	10000
405	Tiger	35	9000

< 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 >