SQL QUERY

1) SELECT QUERY:
Select Column1, Column2 from table_name;

Only select the particular columns mentioned from the table.

Select * from table_name;

Select all the columns from the table mentioned.

2) SELECT DISTINCT QUERY:

Select difficult value from the table, ie ignore the duplicate values from the table use Select distinct
query.

Select distinct column1,column2 from table_name;

To Know the Count of Distinct values , use the following Query,

Select Count(distinct Column1) from table_name;

3) SQL WHERE CLAUSE QUERY:

The Where clause is used to filter the records.

The Where clause used to extract the only records which fulfil the specific requirements.

Syntax:

Select Column1,Column2 from table_name where Condition;

The Where condition not only used with “select” statement and also possible to use “update”,”Delete”,
etc.,

Text fields Vs Numeric Fields:

SQL Requires Single Quotes around the Text values( where other systems are required double Quotes).

Syntax:

Select * from Customers where CustomerID=’ANATR’;

Operators in Where Clause:

 Operator Description

= Equal Example: SELECT * FROM Products WHERE Price = 18;

Operator Description

‘> Greater than Example: SELECT * FROM Products WHERE Price > 30;

Operator Description

< Less Than Example: SELECT * FROM Products WHERE Price < 30;

Operator Description

>= Greater than or equal to Example: SELECT * FROM Products WHERE Price >= 30;

Operator Description

<= Less than or Equal to Example : SELECT * FROM Products WHERE Price <= 30;

Operator Description

‘ <> Not Equal to Example: SELECT * FROM Products WHERE Price <> 18;

Operator Description

Between Between Certain Range Example: SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;

Operator Description

Like Like Pattern Example: SELECT * FROM Customers WHERE City LIKE 's%';

Operator Description

IN To Specify multiple possible values for a column.

Example: SELECT * FROM Customers WHERE City IN ('Paris','London');

4) The SQL AND, OR and NOT Operators:

The Where Clauses are shall be combined with AND, OR and NOT Operators.
The AND and OR operators are used to filter records based on the more than one condition.
The AND Operators displays the records if all the conditions are TRUE.
 The OR Operators displays the records if any one of conditions are TRUE.
The NOT operators displays a records if the conditions are not TRUE.

AND Syntax:
SELECT column1, column2, ...FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax:

SELECT column1, column2, ...FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax:

SELECT column1, column2, ...FROM table_name WHERE NOT condition;

5) SQL Order by Keyword:

The Order by keyword is used to sort the Result –set in ascending or descending Order by keyword
Default sort in Ascending order. If we want to sort in descending order, use DESC Keyword.

Order by Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Order by serveral Columns:

The following SQL statement selects all customers from the "Customers" table, sorted ascending by the
"Country" and descending by the "CustomerName" column:

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

6) SQL INSERT INTO STATEMENT:

The Insert into statement insert new records into the table.

It is possible use the Insert into statement in two ways.

i) The First way specifies both column names and the values to be inserted.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2,  value3, ...);

ii) If you are adding the values to the all columns in the table no need to specify the column name.
use the following syntax.
INSERT INTO table_name VALUES (value1,  value2, value3, ...);
7) SQL NULL Values:

A field with a NULL value is a field with no values.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a
value to this field. Then, the field will be saved with a NULL value.

To Test the Null Value cant able to use the comparative operators, we shall use the “IS NULL” or “IS
NOT NULL” Operator instead.

IS NULL Syntax:

SELECT column_names FROM table_name WHERE column_name IS NULL;

IS NOT NULL Syntax:

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

8) UPDATE Statement:

The Update statement used to modify the existing records in the table.

Update syntax:

UPDATE table_name
SET column1 = value1,  column2 = value2, ...
WHERE condition;

9) DELETE statement:
The Delete statement used to delete the Records from the table,

DELETE Syntax:
DELETE FROM table_name  WHERE condition;

To Delete All Records from the Table without alter the table structure.

DELETE FROM table_name;

10) SQL SELECT TOP Clause:

SELECT TOP Clause is used to specify the number of records to return.

SELECT TOP Syntax:

 SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

Example: SELECT TOP 50 PERCENT * FROM Customers;
Or
SELECT TOP 3  * FROM Customers;

Additional Condition statement in SELECT TOP Clause:

SELECT TOP 3 * FROM Customers WHERE Country='Germany';

11) MIN() and MAX() Functions:

The MIN() function return the Minimum values from the column.

The MAX() function return the Maximum values from the column.

MIN() Syntax:

SELECT MIN(column_name) FROM table_name WHERE condition;

MAX() Syntax:

SELECT MAX(column_name)FROM table_name WHERE condition;

12) COUNT,AVG() and SUM() Function:

The Count Function returns the Number of Rows which fullful the specific condition.

The AVG function returns the average values of the Numeric Column.

The Sum function returns the total sum of a numeric Column.

COUNT() Syntax:

SELECT COUNT(column_name)FROM table_name WHERE condition;

Example: Select COUNT(OrderID)from Orders where ShipVia=3;

AVG() Syntax:

SELECT AVG(column_name) FROM table_name WHERE condition;

Example: Select COUNT(OrderID)from Orders where ShipVia=3;

SUM() Syntax:

SELECT SUM(column_name) FROM table_name WHERE condition;

Example: SELECT SUM(Freight) FROM Orders WHERE ShipVia=1;

 13) LIKE Operator:
The Like Operator is used in a WHERE Clause to search for a Specified pattern in a column.
There are two wild cards often used in conjunction with LIKE Operator:

 % the Percentage sign represents zero , one or multiple characters.

 _ the Underscore represents a single characters.

LIKE Syntax:
SELECT column1, column2, ...FROM table_name WHERE columnN LIKE pattern;

Examples:

LIKE Operator Description

WHERE CustomerName LIKE Finds any values that start with "a"
'a%'

WHERE CustomerName LIKE Finds any values that end with "a"
'%a'

WHERE CustomerName LIKE Finds any values that have "or" in any position
'%or%'

WHERE CustomerName LIKE Finds any values that have "r" in the second position
'_r%'

WHERE CustomerName LIKE Finds any values that start with "a" and are at least 3
'a__%' characters in length

WHERE ContactName LIKE 'a Finds any values that start with "a" and ends with "o"
%o'

14) SQL Wild Card Character:

A wild card character is used to substitute one or more characters in a string.

Wildcard characters are used with SQL LIKE operator. The LIKE Operator is used in a WHERE clause to
search for Specified pattern in a column.
Wildcard Characters in SQL Server

Symbol Description Example

% Represents zero or more characters bl% finds bl, black, blue, and
blob

_ Represents a single character h_t finds hot, hat, and hit

[] Represents any single character within h[oa]t finds hot and hat, but
the brackets not hit

^ Represents any character not in the h[^oa]t finds hit, but not hot
brackets and hat

- Represents a range of characters c[a-b]t finds cat and cbt

15) SQL IN operator:

IN Operator allows to specify multiple values in a WHERE Clause.

The IN Operator is a shorthand for multiple OR Conditions.

IN Syntax:
SELECT column_name(s)FROM table_name
WHERE column_name IN (value1,  value2, ...);

Or
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Example:
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

16) SQL BETWEEN Operator:

 The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN Operator is inclusive : begin and end values are included.

BETWEEN Syntax:

SELECT column_name(s) FROM table_name
WHERE column_name  BETWEEN value1 AND value2;

Example:

select freight from Orders where Freight Between 10 and 20;

select freight from Orders where Freight not Between 10 and 20;

17) SQL Aliases:

SQL Aliases are used to give a table , or a column in a table , a temporary name.

Aliases are often used to make column names more readable.

An Aliases are only exists during for the Duration of Query.

Alias Column Syntax:

SELECT column_name AS alias_name
FROM table_name;

Alias Tale Syntax:

SELECT column_name(s)
FROM table_name  AS alias_name;

If Alias contain Space then mention the Alias name between Square bracket ex: [contact Name]

Example:

select City As CT, Address As ad from Customers;

Select CustomerID,CompanyName+','+PostalCode+','+Country As Adr from Customers;

18) SQL JOIN :

A JOIN Clause used to combine rows from two or more tables , based on the related column between them.

Different Types of JOINS in SQL.

 INNER JOIN: Returns records that have matching values in both tables.
 LEFT(OUTTER) JOIN: Returns All the records from the LEFT table and matched records from Right
table.
 RIGHT (OUTTER)JOIN: Returns All the Records from the RIGHT Table and matched records from LEFT
Table.
  FULL(OUTTER) JOIN: Returns all the records from the Both the table if values matched either in
Right table or left table.

Example:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

19) INNER JOIN Keyword:

The Inner Joint selects the Records that have matching values in both tables.

INNER JOIN Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

JOIN THREE TABLES:

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

20) LEFT JOIN Keyword:

The LEFT JOIN Keyword returns all records from the left table(table1), and the matched records from
the right table(table2). The Result is NULL from the Right side, if there is no match.

LEFT JOIN Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
LEFT OUTER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
LEFT OUTER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
21) RIGHT JOIN Keyword:
The LEFT JOIN Keyword returns all records from the left table(table1), and the matched records
from the right table(table2). The Result is NULL from the Right side, if there is no match.

RIGHT JOIN Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
RIGHT OUTER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
RIGHT OUTER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

22) FULL JOIN Keyword:

The FULL JOIN Keyword Returns all the records when there is match in left (table1) or right (table2)
table records.

Note: FULL JOIN Returns Large set of results.

FULL JOIN Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
 ON table1.column_name = table2.column_name
WHERE condition;

Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

23) Self JOIN keyword:

A self JOIN is a regular join , but the table is joined with itself.

Self JOIN Syntax:

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Where T1 and T2 are different table aliases for the same tables.

Example:
SELECT A.CustomerName AS CustomerName1,
B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

24) UNION Operator:

The UNION operator is used to combine the result- set of two or more SELECT Statements.

 Each SELECT Statement within UNION must have same numbers of Columns.
 The Columns must also have similar Data types.
 The Columns in Each SELECT Statement must also be in same orders.

UNION Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
 Example:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION with Where Clause:

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

UNION ALL Syntax:

UNION Operator only selects the Distinct values by Default. But we require duplicate values also, then
Use UNION ALL

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

UNION ALL with Where Clause:

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

25) GROUP BY Statement:

The GROUP BY Statement groups rows that have the same values into summary rows, like “ find the
number of customers in each country”.
The GROUP BY statement is often used with aggregate functions(COUNT,MIN,MAX, AVG,SUM) to group
the result by one or more columns.
GROUP BY Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
 GROUP BY column_name(s)
ORDER BY column_name(s);

Example:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

26) HAVING Clause:

Having Clause was added to SQL because the WHERE Keyword could not be used in aggregate functions.

HAVING Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example:

Select COUNT(CustomerID)As NO_of_Customer,Country from Customers group by

country having COUNT(CustomerID)>5;

27) EXISTS Operator:

The Exists operator is used to test for the existence of any record in a subquery.

The Exists Operator Returns True if the subquery returns one or more records.

EXISTS Syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Example:

With Exists Statement:

Select CompanyName from Suppliers where exists
(
select ProductName from Products where Products.SupplierID = Suppliers.SupplierID
and UnitPrice >=20.0
)

With Not Exists Statement:

Select CompanyName from Suppliers where not exists
(
select ProductName from Products where Products.SupplierID = Suppliers.SupplierID
and UnitPrice >=20.0
)
28) ANY and ALL Operators:

The ANY and ALL Operators are used with a WHERE or HAVING Clause.

The ANY Operator returns true if any of the sub query values meet the condition.

The ALL Operator returns true if all of the sub query values meet the condition.

Note: The operator must be a standard comparison operator (=, <>,!=, >, >=, <, or <=).

ANY Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

Description:

The following SQL statement returns TRUE and lists the product names if it finds ANY records in the
OrderDetails table that quantity = 10:

Example:

select ProductName from Products where ProductID= ANY(select ProductID from

[Order Details] where Quantity=10);

ALL Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name  WHERE condition);

Description:

The following SQL statement returns TRUE and lists the product names if it finds ALL records in the
OrderDetails table that quantity = 10:

Example:

select ProductName from Products where ProductID= ALL(select ProductID from

[Order Details] where Quantity=10);

29) SELECT INTO Statement:

“SELECT INTO” Statement Copies data from one table to new table.

SELECT INTO Syntax:

Copy all Columns into a new table;

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Example:

Select * into Order1 from Orders;

Copies the Content of Orders and create backup of orders in Order1 Table in same database.

Copies particular Columns from the table and create new table.

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Example:

Select ProductID,ProductName into Pro1 from Products;

To Copies the Table Schema not the data, we shall use the following code,

select * into Ord from Orders where 1=0;

To Copies the data from More than one table we shall use the following code,

SELECT Customers.CompanyName, Orders.OrderID

INTO CustomersOrderBackup2017
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

30) INSERT INTO SELECT Statement:

INSERT INTO SELECT Statement copies data from one table and inserts it into another table.
 INSERT INTO SELECT Requires that data types in source and target table match.
 The Existing records in the target table are unaffected.

INSERT INTO SELECT Syntax:

Copy all columns from one table to another table.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Example:

insert into SupplierNew select * from CustomerNew Where Country='Germany' .

Copy only some columns from one table into another table.

INSERT INTO table2  (column1, column2, column3, ...)

SELECT column1, column2, column3, ...
 FROM table1
WHERE condition;

insert into SupplierNew(City) select City from CustomerNew Where

Country='Mexico'

31) CASE Statement:

The Case Statement goes through conditions and returns a value when the first condition is met(like an
IF-THEN-ELSE Statement).so, once a condition TRUE, it will stop reading the Result. If no conditions are
TRUE , it returns the value in the ELSE Clause.

If there is no ELSE Part and no conditions are true, it returns NULL.

CASE Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Example:

select OrderID,Quantity,
CASE
When Quantity > 30 then 'The Quantity is Greater than 30'
When Quantity = 30 then 'The Quantity is 30'
Else 'The Quantity is under 30'
End As QuantityText
from [Order Details]

select CustomerID,ShipCountry,
CASE
When Freight >20.0 then 'Freight is greater than 20'
when Freight =20 then 'Freight is equal to 20'
Else 'Freight is under 20'
End
from Orders

32) ISNULL() Functions:

Replacement for NULL Value in the Table, ISNULL() Function shall be used.
Example:
select OrderID,CustomerID,Freight,ISNULL(ShipRegion,'DGL') from Orders where
Freight> 20.0

33) SQL Stored Procedures:

 What is stored Procedures?

A Stored Procedure is a prepared SQL Code that you can save, so the code can be reused over and over
again. So if you have an SQL query that you write over and over again, save it as stored procedure and
then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the
parameter values that is passed.

Stored Procedure Syntax:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure:

EXEC procedure_name;

Example:

Create Procedure SelectAllCustomers

As
select * from Customers
go;

Example:

exec SelectAllCustomers

Stored Procedures with One Parameter:

The Following SQL Statement Creates a stores procedures that selects
Customers from the particular City from the “Customer” table.

Example:

Create Procedure SelectCustomers @City nvarchar(30)

As
select * from Customers where City =@City
go;

exec SelectCustomers @City="London";

Stored Procedures with Mutiple Parameters:

The Following SQL Statement Creates a stores procedures that selects

Customers from the particular City & Particular Postal Code from the
“Customer” table.

Example:

Create Procedure SelectCustomers @City nvarchar(30),@PostalCode nvarchar(10)

As
select * from Customers where City =@City and Postalcode= @PostalCode
go;
 Exec SelectCustomers @City =”London” and @Postalcode=”WA1 DP1”

34) SQL Comments:

Comments are used to explain section of SQL Statements or to Prevent execution of SQL statements.

Single Line comments:

Single Line Comments starts with – considered as Comments of the code up to the end of Code.
Example:
--Select all:
SELECT * FROM Customers;
Multi Line Comments:
Multi Line comment starts with / * and end with */. Any Text between the /* and */ considered as
Comments.
Example:
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;

To ignore the Part of Line from the Code /* ,*/ code shall be used.
Example:
SELECT CustomerName, /*City,*/ Country FROM Customers;

35) CREATE DATABASE Statement:

CREATE DATABASE Statement is used to create a new SQL database.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE test_db;

36) DROP DATABASE Statement:

DROP DATABASE Statement used to drop the existing database.
Syntax:

DROP DATABASE test_db;

37) BACKUP DATABASE Statement:

The Backup database statement is used in SQL server to create a full backup of an existing SQL
database.
 Syntax:

BACKUP DATABASE databasename
TO DISK = 'filepath';

Example:

backup database pubs to disk = 'D:\Pubs1.bak';

Backup Database with Differential

A differential database backup only backups the database that have changed since the last full database
backup.

Syntax:

BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;

Example:

backup database pubs to disk ='D:\Pubs1.bak' with differential;

38) CREATE TABLE Statement:

The CREATE TABLE Statement is used to create a new table in database.

Syntax:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....);

The Column Parameters Specify the names of the column of the table.

The datatype parameter specifies the type of data column can hold (e.g: varchar, integer,date etc)

Example:

Create table Persons1

(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

39) DROP TABLE Statement:

 DROP TABLE Statement is used to drop the existing table in database;
Syntax:
DROP TABLE table_Name;
Example:

Use master
Drop table Persons1;

40) TRUNCATE TABLE Statement:

The TRUNCATE TABLE statement is used to delete the data inside a table, not the table itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

use Northwind
Truncate Table SupplierNew;

41) ALTER TABLE Statement:

The Alter Table Statement is used to add, delete, or modify the columns in an existing table.

The Alter table statement is used to add and drop various constraints on the existing tables.

Syntax:

ALTER TABLE-ADD Column:

To add the column in the table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example:

Alter table employees add Email varchar(20);

ALTER TABLE –DROP Column:

To delete a column in a table, use the following syntax.

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

Alter table employees drop column Email;

ALTER TABLE – ALTER/MODIFY Column:

To Change the data type of a column in a table, use the following syntax:
 ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Example:

Alter table employees alter column Email int ;

42) Create CONSTRAINTS:

Constraints can be specified when the table is created with CREATE TABLE Statement , or after
the table is created with the ALTER TABLE Statement.

SQL Constraints are used to specify rules for the data in the table:

Constraints are used to limit the type of data that can go into a table. This Ensures the accuracy
& Reliability of the data in the table. if there is any violation between the constraints and the
data action , the action is aborted.

The Constraint can be column level or table level. Column level constraints apply to Columns
and Table level constraints apply to the whole table.

The Following constraints are commonly used in the SQL:

 NOT NULL- Ensures that column cannot have a NULL Value.

 UNIQUE – Ensures that all values in the columns are different.
 PRIMARY KEY – A combination of a NOT NULL and UNIQUE . Uniquely identifies each row in a table.
 FOREIGN KEY – Uniquely identifies a row/ record in Another Table.
 CHECK- Ensures that all the value in the column satisfies the Specific condition.
 DEFAULT- Sets a default value for a column when no value is specified.
 INDEX- Used to create and retrieve data from the database very quickly.

Syntax:
CREATE TABLE table_name  (
     column1 datatype constraint,
     column2 datatype constraint,
     column3 datatype constraint,
    ....
);

Example:

Create table Aru

(
C_ID int Primary key,
C_Name varchar(100) NOT NULL,
)

43) NOT NULL Constraints:

By Default , a column can hold NULL Value.The NOT NULL Constraint enforces a column to NOT accept the
NULL Values.
NOT NULL on CREATE TABLE:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,Age int);

NOT NULL on ALTER TABLE:

ALTER TABLE Persons
MODIFY Age int NOT NULL;

44) UNIQUE CONSTRAINT on CREATE TABLE:

The Unique constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY Constraint provide the gurantee for uniqueness for a column or set of
column.

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

UNIQUE Constraint on Alter Table:

ALTER TABLE Persons
ADD UNIQUE (ID);

Example:

Alter table Aru Add unique(C_Name);

To Apply Unique constraint to multiple columns & Name the Unique Constraint the following statement shall be
used.

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

Example:

Alter table meena add constraint UC_meena unique( Age,height);

DROP a UNIQUE Constraint:

To drop the Unique constraint the following script shall be used.

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

Alter table meena drop constraint UC_meena

 45) PRIMARY KEY Constraint:
The Primary Key Constraint uniquely identifies each record in the table.
Primary Key must contain UNIQUE Value and cannot contain NULL Values.
A Table should contain only one Primary Key; and in the Table, this Primary Key can consist of single and
Multiple columns(fields).
PRIMARY KEY on CREATE TABLE.
The Following SQL Statement creates a PRIMARY KEY in ID Column when creating Person Table.

create table Person1(

ID int not null primary key,
LasstName varchar(255) not null,
FirstName varchar(255),
Age int);

To Allow naming of Primary key constraint and for defining a PRIMARY KEY constraint on multiple
columns, use the following Syntax:

create table Person2(

ID int not null,
LasstName varchar(255) not null,
FirstName varchar(255),
Age int,
constraint PK_Person2 Primary key(ID,LasstName));

in the above example there is only one primary key (PK_Person2). However the value of the Primary key
is made up of Two Columns(ID +LastName);

PRIMARY KEY on ALTER TABLE:

To create Primary Key constraint on the already created Table.,

alter table Person1 add primary key(ID);

For Multiple Columns:

alter table Person1 add constraint PK_Person primary key(ID,LasstName);

DROP PRIMARY KEY on ALTER TABLE:

To drop a Primary Key Constraint , use the following Codes,

alter table Person1 drop constraint PK_Person;

46) FOREIGN KEY Constraint:

A Foreign Key is used to link two tables together.

A Foreign key is a field in one table refers to the Primary key in another table.

FOREIGN KEY On CREATE TABLE

Create table Orders1(
OrderID int not null primary key,
OrderNumber int not null, ID int foreign key references Person1(ID)
);
To allow naming of Foreign key Constraint and for defining a foreign key constraint on multiple
columns, use following code:
Create table Orders2(

OrderID int not null primary key,

OrderNumber int not null,
PersonID int,
constraint FK_PersonOrder1 Foreign key (PersonID) References Person1(ID)
);
TO Create ForeignKey in PersonID Column when Order2 Table alreaddy created, use the
following SQL code,

Alter table orders2 Add foreign key (PersonID) References Person1(ID);

To Allow the Naming of Foreign key constraint and for defining a foreign key
constriant to multiple column use the following SQL code,

Alter table orders2 add constraint FK_Personorder1 foreign key(PersonID) references

Person1(ID);

To Drop the Foreign Key Constraint use the following

Alter table orders2 drop constraint FK_Personorder1;

47) CHECK Constraint:

The Check constraint is used to limit the value range that can be placed inside the column.
If you define Check constraint in particular column it will allow only certain values for that column.
If you define Check constraint on a table it can limit the values in certain columns based on the values in
the Columns in the same row.

CHECK on CREATE TABLE:

SQL Creates Check constraint in Age column when creating Person table. The check constraint will allow
the Age value only above 18 Years.

Create table Person2

(
FirstName varchar(255) not null,
LastName varchar(255), Age int check( Age>=18)
);

To Allow the naming of check constriant and defining for multiple columns , use the
following SQL Code,

Create table Person2(

ID int not null,
FirstName varchar(255) not null,
LastName varchar(255), Age int,
City varchar(255),
Constraint CHK_Person check (Age>=18 and City='DGL'));

CHECK on ALTER Table:

To create Check constraint on the Table which is already created,

Alter table Person2 add check (Age>=18);

To Allow naming of Constraint and for defining in mulitple columns the following
code shall be used,

Alter table Person2 add constraint CHK_Person check(Age>=18 and City='DGL');

DROP the CHECK Constraint:

To Drop the Check constraint use the Following SQL Code,

Alter table Person2 drop constraint CHK_Person

48) DEFAULT Constraint:

The DEFAULT Constraint provides Default value to the Column in the table. The Default value will be
added automatically in the column if no value is specified.

DEFAULT on CREATE TABLE:

The Following SQL Code set the Default value for the city column when the Person Column created.

Create table Person2(

ID int not null,
FirstName varchar(255) not null,
LastName varchar(255), Age int,
City varchar(255) Default 'Dindigul');

The Default Constraint can also be used to insert system values , by using functions like GETDATE();

Create table Orders2(

OrderID int not null primary key,
OrderNumber int not null,
PersonID int,
OrderDate date default getdate()
);

SQL DEFAULT on ALTER TABLE;

To Create Default Contraint in table which is already created.

Alter table person2 add constraint df_city default 'Dindigul' for City;

DROP Default Constraint:

To drop a default constraint , use the following SQL:

Alter table person2 drop constraint df_city

49) CREATE INDEX Statement:

The Create index statement used to create index in the table. Indexes are used to retrieve the data from
the tables more quickly than others. The users cannot see the index specified. They just used to speed
up the searches / queries.
 Note: Updating table contain index also takes more time than without index. Because index itself needs
update.so index shall be used for needs where frequently searched items.

CREATE INDEX Syntax:

CREATE INDEX index_name ON table_name (Column1, Column2,…);

Duplicate values are allowed.

CREATE UNIQUE INDEX Syntax:

CREATE UNIQUE INDEX index_name ON table_name (Column1, Column2,…);

Duplicate values are not allowed.

Example:
Create index idx_Lastname on Person1(LasstName);
Create index with combination of Columns use the following type of code:

Create index idx_Person on Person1(LasstName,FirstName);

DROP INDEX Statement:

To Drop the Index from the Table use the following SQL code;

Drop index Person1.idx_Lastname;

50) AUTO INCREMENT Field:

Auto increment allows a unique number generated automatically when new record being inserted.
Often this field set as primary key.

Syntax for Auto increment:

Create table Person3

(
Personid int identity(1,1)Primary key,
LastName varchar(255) not null,FirstName varchar(255),Age int

);

Inserting Values into the table:

insert into Person3(FirstName,LastName,Age) values ('Siva','subramanian',31);

51) SQL Dates:

Date type format for Data & Time is as follows,

DATE: Format YYYY-MM-DD

DATETIME: Format YYYY-MM-DD HH:MI:SS

SMALLDATETIME: YYYY-MM-DD HH:MI:SS

TIMESTAMP – Format : unique Number.

Use Northwind
SELECT * FROM Orders WHERE OrderDate<='2008-11-11'

52) VIEW Statement:

Create VIEW Statement:
A view is a virtual table resulted from the SQL Query, which is from one or more table.

CREATE VIEW Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

Create View [Brazil Customer] As select CompanyName, ContactName from

Customers where Country='Barzil'

View the VIEW TABLE CREATED:

select * from [Brazil Customer];
Create view [Product Above Average Price] As
Select ProductName,UnitPrice from Products
Where UnitPrice >(select Avg(UnitPrice)from Products);

select * from [Product Above Average Price];

Dropping a view:
drop view [Brazil Customer];

Rasperry Pi 3 model V1.2

Nodemcu Amico CP2102