CROSS -JOIN

It is also known as CROSS PRODUCT OR CARTESIAN PRODUCT because it produces the product of multiple tables. Every row from first table is multiplied with all rows of another table. Simply it is the multiplication of two tables.

Syntax: SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.....TABLE2.COLUMN1,TABLE2.COLUMN2,....FROM TABLE1,TABLE2

Note:Now I want multiply EMP table with EMP table.

Select * from DEPT,DEPT

The above statement shows an error message because it not possible to multiply a table by itself with the same name,so that we to project the same table DEPT as two tables to the Sql Server. To show a single DEPT table as two tables to server we have to use the concept of table Alias Names.

CROSS -JOIN

OUTER JOIN

It is the extension of Inner Join operation because Inner selects only matched records from multiple tables where Outer Join selects matched records as well as unmatched records. It includes

a). Left Outer Join
b). Right Outer Join
c). Full Outer Join

Left Outer Join:It selects matched records from both the tables as well as unmatched records from Left side table. For doing this operation we have to keep a special symbol
'*' at the left side of the equality condition.

Syntax: SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.....TABLE2.COLUMN1,TABLE2.COLUMN2,.... FROM TABLE1.TABLE2 WHERE TABLE1.COMMON COLUMN *=TABLE2.COMMON COLUMN

Right Outer Join: It selects matched records from both the tables as well as unmatched records from Right side table. For doing this operation we have to keep a special symbol '*' at the right side of the equality condition.

Syntax: SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.....TABLE2.COLUMN1,TABLE2.COLUMN2,.... FROM TABLE1.TABLE2 WHERE TABLE1.COMMON COLUMN =* TABLE2.COMMON COLUMN

Full Outer Join: It is just combination of Left Join + Right Join. It selects matched records as well as unmatched records from the given tables. For Full Outer Join we have to follow a special syntax called ANSI SQL Syntax.

ANSI SQL Syntax:

SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.....TABLE2.COLUMN1,TABLE2.COLUMN2,........FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COMMON COLUMN=TABLE2.COMMON COLUMN

OUTER JOIN

INNER JOIN

Inner Join selects the data from multiple tables based on the equality condition it means it selects only matched records from the multiple tables. For doing this Inner Join operation we should have to maintain one common valued column in the multiple tables.

Syntax:

SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,.......TABLE2.COLUMN1,TABLE2.COLUMN2,.....FROM TABLE1,TABLE2 WHERE TABLE1.COLUMN COLUMN = TABLE2.COMMON COLUMN

INNER JOIN

Joins

Joins in Sql Server are used to select the data from multiple tables using a single select statement.

In real time scenario Main table divided into number of sub tables but while selecting data from multiple tables we to JOIN the tables.

In Sql Server there existed three types of joins which includes

1. INNER JOIN
2. OUTER JOIN
3. CROSS JOIN

Joins

Dropping The Constraints

Syntax:

ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRAINTNAME.

Example: ALTER TABLE DEPT DROP CONSTRAINT PK

From above example primary constraint have removed from DEPTNO columns.

Dropping The Constraints

Adding constraints for the existing table with constraint names

Step1: Create any table with out any constraints

Example:CREATE TABLE DEPT (DEPTNO INT,DNAME VARCHAR(20),LOC VARCHAR(20))

Step2: Make a single column as NOT NULL for which we want provide Primary Key constraint.

Syntax: ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME DATATYPE NOT NULL

Example1: ALTER TABLE DEPT ALTER COLUMN DEPTNO INT NOT NULL.

Step3: Add your required constraints to columns expect DEFAULT and NOT NULL by using the following approach.

Syntax:

ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN1),CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN2,..........

Example: ALTER TABLE DEPT ADD CONSTRAINT PK PRIMARY KEY (DEPTNO),CONSTRAINT UQ UNIQUE(DNAME)

Adding constraints for the existing table with constraint names

Adding constraints for the existing table without any constraint names

We can place constraints on columns after creating the table but before inserting the values.

Note:

1. In Table Level constraints DEFAULT and NOT NULL constraints are not allowed.

2. The below approach is applicable only when there is no data available in the table.

Step1: Create any table with out any constraints

Example:CREATE TABLE DEPT (DEPTNO INT,DNAME VARCHAR(20),LOC VARCHAR(20))

Step2: Make a single column as NOT NULL for which we want provide Primary Key constraint.

Syntax: ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME DATATYPE NOT NULL

Example1: ALTER TABLE DEPT ALTER COLUMN DEPTNO INT NOT NULL.

Step3: Add your required constraints to columns expect DEFAULT and NOT NULL by using the following approach.

Syntax: ALTER TABLE TABLENAME ADD CONSTRAINTTYPE (COLUMN1),CONSTRAINTTYPE (COLUMN2),...

Example1: ALTER TABLE DEPT ADD PRIMARY KEY(DEPTNO),UNIQUE(DNAME)

Adding constraints for the existing table without any constraint names

Table Level Constraints

Here constraints are going to be placed on columns after the definition of all columns and their corresponding data types.It means at the end of the table definition constraints will be placed on columns.

Note:In Table Level constraints DEFAULT and NOT NULL constraints are not allowed.

Syntax:(With out Constraints names)

CREATE TABLE TABLENAME (COLUMN1 DATATYPE,COLUMN2 DATATYPE,.....CONSTRAINT TYPE (COLUMN1),CONSTRAINT TYPE (COLUMN2),.............)

Example1: CREATE TABLE DEPT (DEPTNO INT,DNAME VARCHAR(20),LOC VARCHAR(20),PRIMARY KEY (DEPTNO),UNIQUE (DNAME))

Syntax: (With Constraint names)

CREATE TABLE TABLENAME (COLUMN TABLE TABLENAME (COLUMN1 DATATYPE,COLUMN2 DATATYPE,.... CONSTRAINT CONSTRAINTNAME CONSTRAINT TYPE (COLUMN1),CONSTRAINT CONSTRAINTNAME CONSTRAINT TYPE (COLUMN2),.....................)

Example1:

CREATE TABLE DEPT (DEPTNO INT,DNAME VARCHAR(20),LOC VARCHAR (20),CONSTRAINT PK PRIMARY KEY (DEPTNO),CONSTRAINT UQ UNIQUE (DNAME))

Table Level Constraints

Column Level Constraints

Constraints can be added to table in two levels.

1. Column Level

2. Table Level

Column Level Constraints: Here constraints are going to placed on column the definition of each and every individual column and their corresponding type.

Syntax: (With out Constraint names)

CREATE TABLE TABLENAME
(COLUMN1 DATATYPE CONSTRAINTTYPE,COLUMN2 DATATYPE CONSTRAINTTYPE,......)

Example: CREATE TABLE DEPT (DEPTNO INT PRIMARY KEY, DNAME VARCHAR(20)UNIQUE,LOC VARCHAR(10)DEFAULT 'USA')

Example: CREATE TABLE EMP (EMPNO INT PRIMARY KEY,ENAME VARCHAR(20) NOT NULL,SAL MONEY CHECK(SAL>=1000),DEPTNO INT FOREIGN KEY REFERENCES DEPT (DEPTNO) ON DELETE CASCADE ON UPDATE CASCADE)

Advantage of ON DELETE CASCADE:

With out specifying the ON DELETE CASCADE it is not possible to delete the record in the PARENT table if there are dependent records from the child table for that record. ON DELETE CASCADE if used when the record in the PARENT table is deleted all the dependent records in the child table will be also be deleted.

Advantages of ON UPDATE CASCADE:

With out specifying the ON UPDATE CASCADE it is not possible to update the record in the PARENT table if there are dependent records from the child table for that record. ON UPDATE CASCADE if used when the record in the PARENT table is updated all the dependent records in the child table will be also be updated.

Syntax:(With Constraint Names)

CREATE TABLE TABLENAME (COLUMN 1 DATA TYPE CONSTRAINT CONSTRIANTNAME CONSTRAINTTYPE,
COLUMN 1 DATA TYPE CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE,......)

Example:

CREATE TABLE DEPT (DEPTNO INT CONSTRAINT PK PRIMARY KEY,DNAME VARCHAR(20) CONSTRAINT UQ UNIQUE,LOC VARCHAR(10) CONSTRAINT DF DEFAULT 'HYD')

In the above example constraint have been placed on columns with constraint names like

Pk name of Primary Key Constraint on Deptno column

UK name of Unique Constraint on Dname column

DF name of Default Constraint of Loc column

Example: CREATE TABLE EMP (EMPNO INT CONSTRAINT PRK PRIMARYKEY,ENAME VARCHAR(20) CONSTRAINT NN NOT NULL, SAL MONEY CONSTRAINT CK CHECK(SAL>=1000),DEPTNO INT FOREIGN KEY CONSTRAINT FK REFERENCES DEPT (DEPTNO) ON DELETE CASECADE ON UPDATE CASCADE)

In the above example constraint have been placed on columns with constraint names like

PRK name of Primary Key Constraint on Empno column

NN name of Not Null Constraint on Ename column

CK name of Check Constraint on Sal column

Fk name of Foreign Key Constraint on Deptno column.

Column Level Constraints

Types of Constraints

1. Unique Constraint: When we place UNIQUE constraint on any column.It will not allow duplicate values but it allows single null value.

2. NOt null Constraint: When we place NOT NULL constraint on any column,it will not allow any null values. Entering value for that column is mandatory.

3. Primary Key Constraint: It is a combination of UNIQUE + NOT NULL + CLUSTERED INDEX.

It means when we place PRIMARY KEY constraint on any column then it will not any duplicate values and it does not accept any null values mean time the data in that column will be arranged in ascending order due to CLUSTERED INDEX.

4. Check Constraint: It is used for evaluating range condition. It will check values provided for column. Like salary should greater than 5000 and less than 40000.

5. Foreign Key Constraint:

a. Foreign Key must be Primary Key.
b. Foreign Key can accept duplicate values and Null values.
c. Foreign Key has to take the values from its corresponding Primary Key.

6. Default Constraint: It is useful to insert to default value into a column when user will not provide any value while inserting the data into the table.

Very Improtant Tips or Note:

1. Only one PRIMARY KEY is allowed per table.
2. PRIMARY KEY table is called parent table and FOREIGN KEY table is called child table.
3. While providing FOREIGN KEY we should have to give the reference of its corresponding PRIMARY KEY.

Types of Constraints

Data Integrity

Data Integrity means data validation or data checking process or Type Checking process.

Before storing user supplied information into the table server performs data integrity process in order to verify whether user supplying valid information or not.

If user supplies valid information then only it will stored into the table otherwise server raises an error message like 'Data Type Mismatch'.

We can achieve this Data Integrity in Three ways.

1. Data Types

2. Constraints

3. Triggers

Constraints: is nothing but condition on column.If we perform any operation against to constraint server raises an error message.

OR

It is mechanism automatically activated when user performs DML operations on the table.

We can place constraints (with constraint names or with out constraint names) on columns while creating the table or after creating the table but before inserting the values.

Data Integrity

ALIAS NAMES

It is a mechanism in SQL Server which allows users to provide alternative or another name of the Entities (Tables) and their Attributes (columns). These Alias names are mainly used to hide the original name of the table or column. These are always associated with SELECT statement.

Note: Alias names are only for temporary displaying purpose. Those names not stored permanently into the database.

Example:

SELECT EMPNO AS ENO,ENAME AS EMPLOYNAME,SAL AS BASICSAL,DEPTNO AS DNO FROM EMP

Note: While providing alias names for the columns we have use a Keyword called 'AS'
in between original column name and alias column name.

The above statement simply displays employ details by providing temporary column names.

Alias names are also used to provide alternative names for the newly derived values.

Example: Write a query to select SAL as BASIC_SAL,DA,HRA,PF,and NET,GROSS by taking the following specification.

30% of salary as DA
20% of salary as HRA
10% of salary as PF
SAL+DA+HRA as GROSS
GROSS-PF as NET

SELECT SAL AS BASIC_SAL,

SAL*30/100 AS DA,
SAL*20/100 AS HRA,
SAL*10/100 AS PF,
(SAL+SAL*30/100+SAL*20/100) AS GROSS
(SAL+SAL*30/100+SAL*20/100) - (SAL*10/100) AS NET
FROM EMP

In the above query the DA,HRA,PF,GROSS,NET are alias names provided for the newly derived values.

ALIAS NAMES

Stored Procudure - ORDER BY Clause

It is used to display the table data in stored order. The order may be either ascending (or) descending order depending on the specified column. It always appears at the end of select statement.

Syntax:

SELECT {*/Column List} FROM TABLENAME [WHERE CONDITION] ORDER BY COLUMNNAME {ASC/DESC}

The default order is ascending order. It means if we does not specify any specific order then it automatically takes ascending order.

Example1: Write a query to display employ details in ascending order based on their department numbers(DEPTNO).

SELECT * FROM EMP ORDER BY DEPTNO ASC

Example2: Write a query to display employ details in descending order based on their employ numbers(EMPNO).

SELECT * FROM EMP ORDER BY EMPNO DESC

Example3: Write a query to display employ details in descending order based on their salaries(SAL)

SELECT * FROM EMP ORDER BY SAL DESC

Exampe4: Write a querey to display employ names and their salaries in descending order based on their salaries(SAL).

SELECT ENAME,SAL FROM ORDER BY SAL DESC

Example5: Write a query to display 10th department employs in ascending order based on their employ names(ENAME).

SELECT * FROM EMP WHERE DEPTNO=10 ORDER BY ENAME

Stored Procudure - ORDER BY Clause

Stored Procedure - SP_DATABASES

This stored procedure displays the list of databases available in SQL Server.

Syntax:

SP_DATABASES

SP_TABLES: This stored procedure displays the list of tables available in the current database.

Syntax:

SP_TABLES

Stored Procedure - SP_DATABASES

Stored Procedure - SP_HELP

This stored procedure is used to display the description of a specific table.

Description means

A) Name of the Table

B) Columns contained by the Table

c) Data Types of the corresponding columns

D) Sizes of the Data Types.

Syntax:

SP_HELP TABLENAME

Example:

SP_HELP EMP

The above stored procedure displays the description of EMP table.

Stored Procedure - SP_HELP

Stored Procedure - SP_RENAME

This stored procedure is used for changing the name of the table and for changing the name of the column.

Syntax to change the name of the table:

SP_RENAME 'OLD TABLENAME','NEW TABLENAME'

Example:

SP_RENAME 'EMP','EMPLOY'

The above stored procudure changes the name of EMP table to EMPLOY

Syntax to change the name of the column

SP_RENAME 'TABLEOLDCOLUMNNAME','NEW COLUMNNAME'

Example:

SP_RENAME 'STUDENT.ADR', ADDRESS

The above stored procudure changes the name of ADR column to ADDRESS in STUDENT table.

Stored Procedure - SP_RENAME

Stored Procedure - SP_RENAMEDB

Here SP stands for Stored Procedure. This stored procedure is used to change the name of the existing database.

Syntax:

SP_RENAMEDB 'MK','MKR'

The above statement renames (changes the database name) MK to MKR

Stored Procedure - SP_RENAMEDB

Data Manipulation Language - DELETE Statement

Delete statement is used to delete the data from a specific table in ROW – BY – ROW (one by one) manner without disturbing its structure (columns).

Syntax:

DELETE FROM TABLE_NAME [WHERE (CONDTION)]

Example:

DELETE FROM EMP

The statement deletes all records from EMP table without disturbing its structure (columns). This is called high level deletion.

Example:

Write a Query to delete all employee who are working under 10th department

DELETE FROM EMP WHRE DEPTNO=10

Example:

Write a Query to delete all employ who is working under 20th department and employ number 33

DELETE FROM EMP WHERE DEPTNO=20 AND EMPNO=33

Data Manipulation Language - DELETE Statement

Data Manipulation Languagde - UPDATE Statement

Update statement in SQL Server is used for modifying the data, which is a available in a specific table.

Syntax:

UPDATE TABLENAME SET COLUMNNAME =NEWVALUE [, COLUMNNAME= NEWVALUE….]

Example:

Write a query to modify (increase) the salaries of all the employees in EMP table

UPDATE EMP SAL =SAL+1000

The above statement (modifies) increases all employees salaries by 10000

This type of updating operation is called HIGH LEVEL UPDATE operation.

Example:

Write a Query to modify (increase) the salaries of all employees who are working under 10th department.

UPDATE EMP SET SAL = SAL+500 WHERE DEPTNO=10

Write a Query to modify the salary of an employ whose employ number 11 and who is working under 20th department.

UPDATE EMP SET SAL= SAL+300 WHERE EMPNO=11 AND DEPTNO=20

CASE Statement:

In SQL server CASE statement is used for evaluating multiple conditions on a specific column. It is mostly associated with update statement.

Syntax:

UPDATE TABLENAME SET COLUMN NAME= CASE
WHEN CONDITION1 THEN RESULT1
WHEN CONDITION2 THEN RESULT2
-------------------------
-------------------------
WHEN CONDITIONN THEN RESULTN
[ELSE RESULT]
END

Example:

Write a query to arrange BONUS column values according to the following specifications.

1. SAL <5000 BONUS 1000
2. SAL >5000 and <=10000 BONUS 2000
3. SAL > 10000 BONUS 3000

UPDATE EMP SET BONUS=CASE

WHEN SAL<=5000 THEN 1000

WHEN SAL>5000 AND SAL<=10000 THEN 2000

WHEN SAL>10000 THEN 3000 (OR) ELSE 3000

END

The above statement arranges BONUS column values according to the salaries.

Data Manipulation Languagde - UPDATE Statement

Data Manipulation Language - SELECT Statement

This statement is used for retrieving the data from a specific table. It is also known as Data Retrieval Statement.

Syntax:

SELECT {*/ columns list} FROM TABLENAME

In the above syntax the symbol '*' displays all columns and their corresponding rows and the 'columns list' displays specific columns and their corresponding rows.

Example:

SELECT * FROM EMP;

The above statement displays all columns and their corresponding rows from EMP table it means whole EMP table will be displayed.

WHERE CLAUSE: This clause used for evaluating a condition on a specific column of a specific table. It is associated with SELECT, UPDATE, DELETE statements.

Syntax:

SELECT {*/Columns list} FROM TABLENAME [WHERE Condition]

Example:

Write a Query to select employ details who are working under 10th department

SELECT * FROM EMP WHERE DEPTNO=10

Example:

Write a Query to select employ details who are earning salaries between 5000 and 25000

SELECT * FROM EMP WHERE SAL>5000 AND SAL<25000

Example:

Write a Query to select employ details whose employ number is 22

SELECT * FROM EMP WHERE DEPTNO IS NULL

Note:

In the above example we used a special operator called IS operator, which used to compare NULL VALUES.

Data Manipulation Language - SELECT Statement

Data Manipulation Language - INSERT Statement

This statement is used for inserting the values into a specific table.
Syntax to INSERT Statement:

INSERT INTO TABLENAME [(column list)] VALUES (VALUE1,VALUE2….)

Note: While inserting the values into a specific table we should know that table definition (number of columns).

In the above syntax "column list" optional part specifies that "List of columns for which user supplying the values".

Example1:

INSERT INTO EMP VALUES (11,'RAM', 15000, 10);

Example2:

INSERT INTO EMP VALUES (22,'RAJ', 15000, 10);

Example3:

INSERT INTO EMP VALUES (33,'ANIL', 15000, 10);

Example4:

INSERT INTO EMP VALUES (44,'ABIRAM', 15000, 10);

Example5:

INSERT INTO EMP VALUES (EMPNO,'ENAME', DEPTNO) VALUES (66,'DP', 10);

Example6:

INSERT INTO EMP VALUES (EMPNO, ENAME, SAL) VALUES (77,'INDU', 12000);

In the above example 5 users was enables to supply the value for SAL column, then user have to mention the columns list for which he can supply the values.

In the above example 6 user was unable to supply the value for DEPTNO column, then user have to mention the columns list for which he can supply the values.

Note:

Whenever user unable to supply the values for any column then server will arrange an unpredictable or garbage value called NULL value. NULL is different from Zero and empty. We cannot compare null with any other values.

In the above example5 and example6 case SAL and DEPTNO column values is NULL.

Data Manipulation Language - INSERT Statement

Data Manipulation Language

This sub language concentrates on the data of a database object. It includes the following statements.

1. INSERT Statement
2. SELECT Statement
3. UPDATE Statement
4. DELETE Statement

Data Manipulation Language