| by Achyut Kendre | No comments

SQL Constraints and Nested Queries in SQL

Learn SQL
Nested Queries

While creating tables you can apply constrains/rules to column –

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

The following constraints are commonly used in SQL:

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.

Create table DeptTbl(DeptID bigint primary key identity(1,1), DeptName varchar(100) unique)

Will create the depttbl where deptid will be primary key and deptname can not be duplicate. 

Create Table EmpTbl(EmpID bigint primary key identity(1,1), EmpName varchar(200), Address varchar(max) not null, MobileNo varchar(10) unique, Salary decimal(10,2) check(salary > 1000), DeptID bigint references(DepttTbl))

Nested or Inner Queries

Query inside another query is called nested query. In nested query the result of inner query will be given to outer query. In general we nest the query with where part of select query. You can nest the queries up to 32 levels.

select */list of fields from tablename where fieldname operator (select * /list of fields from tablename where fieldname operator (select */listoffields from tablename where …))

Assume following tables for example or demo –

nested query
Nested Query table structure

Using above table structure suppose we want to find all the customers from the area akurdi, how we can do it –

select * from customertbl where areaid=(select areaid from areatbl where areaname='Akurdi')

using above structure suppose we want to find all the customers from the city pune, we can do it as follows –

select * from customertbl where areaid in (select areaid from areatbl where cityid =(select cityid from citytbl where cityname='Pune'))

« Prev