SQL stands for Structured Query Language;

Provides many extensions to ANSI SQL.

SQL is standard language for interfacing with a relational database.

DATA DEFINATION LANGUAGE:

This is the definition level language which includes the following statements.

1)CREATE Statement

2)ALTER Statement

3)DROP Statement

4)TRUNCATE Statement

CREATE Statement:

This Statement is used for creating the database and its objects (Tables, Views, Indexes, Views, User Defined Stored Procedures, User Defined Functions, Triggers, Rules, Defaults)

ALTER Statement:

This Statement is used for modifying the database and its objects (Tables, Views, Indexes, Views, User Defined Stored Procedures, User Defined Functions, Triggers, Rules, Defaults)

DROP Statement:

This Statement is used for deleting the database and its objects (Tables, Views, Indexes, Views, User Defined Stored Procedures, User Defined Functions, Triggers, Rules, Defaults)

TRUNCATE Statement:

This statement is used to delete the data available in a table in Row-By-Row manner but with out disturbing its structure (columns).

Syntax to create the database:

CREATE DATABASE DATABASENAME

DATA MANIPULATION LANGUAGE:

This sub language concentrates on the values of a specific table. It includes the following statements.

1)INSERT Statement

2)SELECT Statement

3)UPDATE Statement

4)DELETE Statement

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

Syntax to INSERT Statement:

INSERT INTO TABLENAME [(columns 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 “columns list” optional part specifies that “List of columns for which user supplying the values”.

INSERT INTO EMP VALUES (11, ‘RAM’, 15000, 10)

INSERT INTO EMP VALUES (22, ‘RAJ’, 5000, 20)

INSERT INTO EMP VALUES (33, ‘ANIL’, 10000, 10)

INSERT INTO EMP VALUES (44, ‘ABIRAM’, 150000, 20)

INSERT INTO EMP (EMPNO, ENAME, DEPTNO)VALUES (55, ‘DP’, 10)

INSERT INTO EMP (EMPNO, ENAME,SAL)VALUES (66, ‘INDU’, 12000)

In the above example 5 user was unable 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 E.g5 and E.g6 case SAL and DEPTNO column value will be given as NULL.

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

SELECT * FROM EMP

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

DISPLAYING THE DATA IN THE EMP TABLE:

SELECT * FROM EMP

EMPNO	ENAME	SAL	DEPTNO
11	RAM	15000	10
22	RAJ	5000	20
33	ANIL	10000	10
44	ABIRAM	15000	20
55	DP	NULL	10
66	INDU	12000	NULL

SELECT EMPNO, ENAME FROM EMP

The above statement displays only EMPNO, ENAME columns and their rows from EMP table.

SELECT SAL, DEPTNO FROM EMP

The above statement displays only SAL, DEPTNO columns and their rows from EMP table.

OPERATORS:

Arithmetic Operators: +, -, *, /, %

Relational Operators: <, >, <=, >=, =,! =,! <,! >

Logical Operators: AND, OR, NOT

Truth table for AND

C1 C2 R

T T T
T F F
F T F
F F F

Truth table for OR

C1 C2 R

T T T

T F T

F T T

F F F

Truth table for NOT

C R

T F

F T

*C1: Condition 1

*C2: Condition 2

*R: Result

WHERE CLAUSE in SQL:

This clause used for placing 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]

Where clause examples in SQL:

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

SELECT * FROM EMP WHERE DEPTNO=10

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

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

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

SELECT * FROM EMP WHERE EMPNO=22

Write a Query to select employ details whose department is null?

SELECT * FROM EMP WHERE DEPTNO IS NULL

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

3. UPDATE Statement:

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

Syntax:

UPDATE TABLENAME SET COLUMNNAME =NEWVALUE

[, COLUMNNAME= NEWVALUE….]

Update query examples in SQL:

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

UPDATE EMP SET SAL=SAL+1000

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

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

Write a Query to modify (increase) the salaries of all employees who are working under 10^th 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 20^th department?

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

CASE Statement in SQL:

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

Case statement in SQL examples;

Write Query to arrange BONUS column values according to the following specification

SAL BONUS

<=5000 1000

>5000 and <=10000 2000

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

4. DELETE Statement:

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

Syntax:

DELETE FROM TABLE_NAME [WHERE (CONDITION)]

DELETE FROM EMP

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

DELETE statement in SQL example:

Write a Query to delete all employees who are working under 10^th department?

DELETE FROM EMP WHERE DEPTNO=10

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

DELETE FROM EMP WHERE DEPTNO=20 AND EMPNO=33

E.g.: CREATE DATABASE SAMPLE

After writing the above Query in SQL Server Management Studio then select it and press F5. Then server creates database with name SAMLE, that we can check it in Database list box in SSMS

Database files and File groups in SQL

A database file is nothing but an operating system file. A database spans at least two, and possible several database files. These files are specified when database is created or altered. Every database must span at least two files. One for the data And one for transaction log.

SQL Server 2005 allows three types of database files.

Primary data files:

Every database has one primary data file. This file contains the startup information for the database and is used to store data. The name of primary database file has the extension MDF(master data file).

Secondary data files:

These files hold all of the data that does not fit into the primary data file. A database can have zero or more Secondary data files. The name of secondary database file has the extension NDF(next data file).

Transaction Log files:

These files hold the log information used to recover the database. There must be at least one log file for each database. The name of a Log file has the extension LDF(log data file).

Each database file has five properties:

1)Logical File Name.

2)Physical File Name.

3)Initial Size.

4)Maximum Size.

5)Growth increment.

After Creating the Database server arranges internally two files.

1.Primary File:

It was defined by the server with 1.18 mb size and with extension “. mdf” (master data file) for holding start up information of the database

EX:SAMPLE. Mdf

2.Log File:

It was defined by the server with 504 kb size and with extension

“. ldf” (log data file) for holding transaction information of the database.

Ex: SAMPLE. ldf

Syntax to create a TABLE:

CREATE TABLE Tablename(Column1 Datatype,

Column2 Datatype,…………………)

CREATE TABLE CUSTOMER (CNO INT, CNAME VARCHAR (20),CITY VARCHAR (20))

CREATE TABLE EMP (EMPNO INT, ENAME VARCHAR (20),SAL MONEY, DEPTNO INT)

The above two queries creates two tables with names CUSTOMER, EMP

Syntax to ALTER the Database:

ALTER DATABASE DATABASENAME ADD FILE

(NAME= ’Any NAME’,

FILENAME =‘PHYSICAL ADDRESS’,

SIZE =<n>MB,

MAXSIZE =<n.>MB,

FILEGROWTH=<n>MB)

ALTER statement in SQL examples:

ALTER DATABASE SAMPLE ADD FILE

(NAME=‘RAM’,

FILENAME=’D:\ProgramFiles\MicrosoftSQLServer\MsSQL.1\MSSQL\Data\SAMPLE.NDF’, SIZE=5 MB,

MAXSIZE =25 MB,

FILEGROWTH=5 MB)

The above query extends the SAMPLE database to 5mb by adding new file SAMPLE.NDF.

Syntax to ALTER the Table:

In Three ways we can modify the table

1.By Adding the new column to the Existing Table

Syntax:

ALTER TABLE TABLENAME ADD NEWCOLUMN DATATYPE [,……..N]

ALTER TABLE EMP ADD BONUS MONEY

The above statement adds Bonus column to EMP table

2.By Changing the Data type of an Existing column

Syntax:

ALTERTABLE TABLENAME ALTER COLUMN COLUMNNAME NEWDATATYPE

ALTER TABLE EMP ALTER COLUMN EMPNO BIGINT

The above statement changes the EMPNO data type from INT to BIGINT.

3. By Dropping the Existing column from Existing Table

Syntax:

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME [,…….N]

ALTER TABLE EMP DROP COLUMN ENAME, SAL

The above statement deletes two existing columns ENAME, SAL columns from EMP Table.

Syntax to DROP Database

DROP DATABASE DATABASENAME

DROP DATABASE SAMPLE

In the above statement deletes SAMPLE database and its objects

Note: It is not possible to drop a particular database which is currently in use it means it is not possible to drop SAMPLE database and till we enter in to another database

Syntax to DROP the Table:

DROP TABLE TABLENAME [,…………..n]

DROP TABLE EMP

The above statement deletes EMP table including its structure (columns)

DROP TABLE CUSTOMER, STUDENT

The above statement deletes customer and student tables at a time. It means we can drop multiple tables at a time.

Note: Users can DROP multiple Databases at a time by placing comma (,) between the database names but it is not recommended approach.

Syntax for TRUNCATE Statement:

TRUNCATE TABLE TABLENAME

TRUNCATE TABLE EMP

The above statement deletes all data available in EMP table in PAGE-BY-PAGE manner (all at once) with out disturbing its structure (Columns)

Data Types in SQL SERVER:

Data Type means the type of data which users provided to a specific column

In SQL Server 2 types of data types available which includes

1. System Defined Data Types

2. User Defined Data Types.

System Defined Data Types:

SQL Server already contains some data types called System Defined data types or Predefined Data types or Built-in Data types. System Defined Data Types again categorized into 4 types

1)Numeric Data types

2)String Data types

3)Date Time Data types

4)Special Data types

1)Numeric Data types:

These Data types are used to provide numeric information for the columns, these includes

Data Type Size

BIT 0 or 1

TINYINT 1 BYTE

SMALLINT 2 BYTES

INT 4BYTES

BIGINT 8BYTES

REAL 4BYTES

FLOAT 8BYTES

DECIMAL (P, S) 5-17 BYTES

TINYINT, SMALLINT, INT, BIGINT are called Exact Numerics where as REAL, FLOAT are called

Approximate Numerics.

2)String Data types:

These Data types are used to provide character information for the columns, these includes

Data Type Size

CHAR [(N)] 1BYTE

VARCHAR [(N)] 1BYTE

TEXT 16BYTES

VARCHAR(MAX) 16 GB

CHAR [(N)]: It is a fixed length data type, which occupies by default 1 byte memory. When we N value then it occupies N bytes of memory where N is an integer. It follows Static memory allocation process.

VARCHAR [(N)]: It is a variable length data type, which occupies by default 1 byte memory. When we N value then it occupies N bytes of memory where N is an integer. It follows dynamic memory allocation process.

Note : The Maximum limit for N is 8000,if it is more than 8000 characters we will use TEXT or VARCHAR(MAX)

3)Date Time Data Type:

These data types are used to provide date oriented information to the columns, these include

Data Type Size Range

SMALLDATETIME 2BYTES 1900-01-01 TO 2079-06-06

DATETIME 4BYTES 1753-01-01 TO 9999-12-31

4)Special Data types:

These data types are used to provide miscellaneous information to the columns, these includes

Data Type Size

SMALLMONEY 4 BYTE

MONEY 8 BYTES

IMAGE 16 BYTES

VARBINARY (MAX) Unlimited

SQL_VARIANT

Binary Data types:

These stores binary values of a given string in ordered to hide the original string values.

Data Type Size

BINARY [(N)] 1BYTE

VARBINARY [(N)] 1BYTE

Unicode Data types:

These Data types are used to store Unicode information, these includes

Data Type Size

NCHAR [(N)] 2BYTE

NVARCHAR [(N)] 2BYTE

NTEXT 16BYTES

II. User Defined Data Types:

When user create a data type then that data type is called user defined data type

Syntax:

CREATE TYPE USER_DEFINED_DATATYPE FROM SYS_DEFINED_DATATYPE

Ex:

CREATE TYPE MYINT FROM INT

CREATE TYPE MYFLOAT FROM FLOAT

CREATE TYPE CASH FROM MONEY

GROUP BY CLAUSE in SQL

This clause is used to divide the table into number of subgroups based on a specific column.

Syntax:

SELECT {COLUMNS LIST} FROM TABLENAME

GROUP BY COLUMN NAME

Write a query to illustrate GROUPBY clause in SQL?

SELECT DEPTNO, MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL,

SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP

GROUP BY DEPTNO

DEPTNO HISAL LOSAL TOTSAL AVGSAL

10 15000 5000 30000 10000

20 20000 5000 25000 12500

HAVING CLAUSE in SQL:

This clause is used to evaluate a condition with group by clause. Generally for evaluating conditions we will use WHERE clause, but where clause does not support by group by clause.

Syntax:

SELECT STATEMENT GROUP BY COLUMN NAME [HAVING CONDITION]

Write a query to illustrate HAVING clause in SQL?

SELECT DEPTNO, MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL,

SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP

GROUP BY DEPTNO HAVING AVG (SAL)>10000

DEPTNO HISAL LOSAL TOTSAL AVGSAL

20 20000 5000 25000 12500

SPECIAL FUNCTIONS in SQL

COUNT( ):

Count function counts the number of rows available for a specific column. It counts only values except NULL values. It is associated with three parameters.

COUNT (*)

COUNT (Column Name)

COUNT() function example in SQL?

SELECT COUNT (*) AS NO_OF_ROWS FROM EMP

NO_OF_ROWS

SELECT COUNT (DEPTNO) AS NO_OF_ROWS FROM EMP

NO_OF_ROWS

DISTINCT ( ):

This function displays the different values available for a specific column. It considers one NULL value.

DISTINCT ( ) function example in SQL?

SELECT DISTINCT (DEPTNO) AS DIFF_VALUES FROM EMP

DIFF_VALUES

SELECT COUNT (DISTINCT( DEPTNO)) AS NO_OF_ROWS FROM EMP

NO_OF_ROWS

Note: COUNT function does not count the rows of the column whose data type is BIGINT, In such situations we have to use COUNT_BIG function

JOINS in SQL

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

T-SQL provides the join concept, which allows retrieval of data from more than one table. This concept is probably the most important for RDBMS, because it allows data to be spread over many tables.

In SQL Server there existed three types of joins which includes

INNER JOIN
OUER JOIN
CROSS JOIN

Sample Tables EMP

EMPNO ENAME SAL DEPTNO

11 RAGHU 10000.0000 10

22 RAZ 20000.0000 20

33 AMAR 10000.0000 10

44 MANI 15000.0000 20

55 CHARN 15000.0000 40

66 ANIL 20000.0000 50

DEPT

DEPTNO DNAME LOC

10 SALES HYD

20 HR CHE

30 IR BAN

1.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 INNER JOIN TABLE2

ON TABLE1.COMMON COLUMN=TABLE2.COMMON COLUMN

INNER JOIN example in SQL?

SELECT EMP.EMPNO, EMP.ENAME,

DEPT.DEPTNO, DEPT.DNAME FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

EMPNO ENAME DEPTNO DNAME

11 RAGHU 10 SALES

22 RAZ 20 HR

33 AMAR 10 SALES

44 MANI 20 HR

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

Left Outer Join
Right Outer Join
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 LEFT OUTER JOIN TABLE2

ON TABLE1.COMMON COLUMN =TABLE2.COMMON COLUMN

Left Outer Join example in SQL?

SELECT EMP.EMPNO, EMP.ENAME,

DEPT.DEPTNO, DEPT.DNAME

FROM EMP LEFT OUTER JOIN DEPT

ON EMP.DEPTNO =DEPT.DEPTNO

EMPNO ENAME DEPTNO DNAME

11 RAGHU 10 SALES

22 RAZ 20 HR

33 AMAR 10 SALES

44 MANI 20 HR

55 CHARN NULL NULL

66 ANIL NULL NULL

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 RIGHT OUTER JOIN TABLE2

ON TABLE1.COMMON COLUMN = TABLE2.COMMON COLUMN

Right Outer Join example in SQL?

SELECT EMP.EMPNO, EMP.ENAME,

DEPT.DEPTNO, DEPT.DNAME

FROM EMP RIGHT OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO

EMPNO ENAME DEPTNO DNAME

11 RAGHU 10 SALES

22 RAZ 20 HR

33 AMAR 10 SALES

44 MANI 20 HR

NULL NULL 30 IR

Full Outer Join: It is just combination of Left outer Join + Right outer join. It selects matched records as well as unmatched records from the given tables.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,

TABLE2.COLUMN1, TABLE2.COLUMN2,……

FROM TABLE1 FULL OUTER JOIN TABLE2

ON TABLE1.COMMON COLUMN =TABLE2.COMMON COLUMN

Full Outer Join example in SQL?

SELECT EMP.EMPNO, EMP.ENAME,

DEPT.DEPTNO, DEPT.DNAME

FROM EMP FULL OUTER JOIN DEPT

ON EMP.DEPTNO =DEPT.DEPTNO

EMPNO ENAME DEPTNO DNAME

11 RAGHU 10 SALES

22 RAZ 20 HR

33 AMAR 10 SALES

44 MANI 20 HR

55 CHARN NULL NULL

66 ANIL NULL NULL

NULL NULL 30 IR

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 CROSS JOIN TABLE2

Cross Join example in SQL?

SELECT EMP.EMPNO, EMP.ENAME,

DEPT.DEPTNO, DEPT.DNAME

FROM EMP CROSS JOIN DEPT

EMPNO ENAME DEPTNO DNAME

11 RAGHU 10 SALES

22 RAZ 10 SALES

33 AMAR 10 SALES

44 MANI 10 SALES

55 CHARN 10 SALES

66 ANIL 10 SALES

11 RAGHU 20 HR

22 RAZ 20 HR

33 AMAR 20 HR

44 MANI 20 HR

55 CHARN 20 HR

66 ANIL 20 HR

11 RAGHU 30 IR

22 RAZ 30 IR

33 AMAR 30 IR

44 MANI 30 IR

55 CHARN 30 IR

66 ANIL 30 IR

SELF JOIN:

Join a table with itself by providing two table alias names is called SELF-JOIN.

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

SELECT * FROM DEPT A, DEPT B

In the above example we provided two table alias names for the single table DEPT those are A and B. Then server identifies that there are two tables available and it performs join operation in a normal way. According user point of view there existed only one table but according to Server point of view there are two tables available those are A and B.

DEPTNO DNAME LOC DEPTNO DNAME LOC

10 SALES HYD 10 SALES HYD

20 HR CHE 10 SALES HYD

30 IR BAN 10 SALES HYD

10 SALES HYD 20 HR CHE

20 HR CHE 20 HR CHE

30 IR BAN 20 HR CHE

10 SALES HYD 30 IR BAN

20 HR CHE 30 IR BAN

30 IR BAN 30 IR BAN

NORMALIZATION in SQL

Normalization is process of splitting the base table into multiple tables based on the theory of Functional Dependency.

Normalization is repetitive process in order to identify the functional dependencies among the columns and to remove them. If any functional dependency is occurred after the normalization process again we have to start the same process until all functional dependencies have been removed.

To do this Normalization we have to follow rules or conditions called Normal Forms.

Un-Normalized Table

EMPNO PROJNO ENAME PNAME SAL BUD DEPTNO DNAME LOC

11 (P1, P2) ---- (Pn1, Pn2) ----- ----- 10 -------- -----

22 (P2, P3) ---- (Pn2, Pn3) ----- ----- 10 -------- -----

33 (P1, P3) ---- (Pn1, Pn3) ----- ----- 20 -------- -----

EMPNO and PROJNO are Primary Keys called ‘COMPOSITE PRIMARY KEY’

FIRST NORMAL FORM (1NF):

According to first normal form table should contain only single values columns. But in the above un-normalized table the columns PROJNO and PNAME contains multiple values.

To make the table into first normal form we should have to split the multiple values into single values.

EMPNO PROJNO ENAME PNAME SAL BUD DEPTNO DNAME LOC

11 P1 ---- Pn1 ----- ----- 10 -------- -----

11 P2 ---- Pn2 ----- ----- 10 -------- -----

22 P2 ---- Pn2 ----- ----- 10 -------- -----

22 P3 ---- Pn3 ----- ----- 10 -------- -----

33 P1 ---- Pn1 ----- ----- 20 -------- -----

33 P3 ---- Pn3 ----- ----- 20 -------- -----

SECOND NORMAL FORM (2NF):

According to second normal form table should be in 1NF and we should have to remove Partial Functional Dependency.

In the above table DEPTNO non-key column dependent part of the Primary key column i.e.EMPNO. It means there existed Partial functional dependency.

To make the table into second normal form we have to divide the table into multiple tables.

PROJ-INFO

PROJNO PNAME BUD

P1 Pn1 ------

P2 Pn2 ------

P3 Pn3 ------

EMP-INFO

EMPNO ENAME SAL DEPTNO DNAME LOC

11 ---- ---- 10 ------ -----

22 ---- ---- 10 ------ -----

33 ---- ---- 20 ------ -----

THIRD NORMAL FORM (3NF):

According to second normal form table should be in 2NF and we should have to remove Transitive Functional Dependency.

In the above EMP-INFO table non-key column DNAME dependent part on the other non- key column i.e.DEPTNO. It means there existed Transitive functional dependency.

To make the table into third normal form we have to divide the table into multiple tables.

PROJ-INFO

PROJNO PNAME BUD

P1 Pn1 ------

P2 Pn2 ------

P3 Pn3 ------

EMP-INFO DEPT-INFO

EMPNO ENAME SAL DEPTNO DNAME LOC

11 ---- ---- 10 ------ -----

22 ---- ---- 20 ------ -----

33 ---- ----

2. Named Batches:

Set of T-SQL statements can written and executed as a single unit with a proper name called Named batch. These include

1.Stored procedures

2.User defined Functions

3.Triggers

1.Stored Procedures:

Stored procedures are one of the database objects. There are two types of stored procedures available in SQL Server.

System Defined Stored Procedures
User Defined Stored Procedures

System Defined Stored Procedures: These are also known as predefined or built-in stored procedures.

E.g.:

SP_HELP

SP_RENAMEDB

SP_RENAME

SP_HELPCONSTRAINT

SP_HELPTEXT

------

-----

-------

User Defined Stored Procedures:

Procedures created by the user are called used defined stored procedures.

Syntax:

CREATE PROC [EDURE] PROCEDURENAME

[@PARA 1 DATATYPE (SIZE)[=DEFAULT_VALUE][OUTPUT]

@PARA 2 DATATYPE (SIZE)[=DEFAULT_VALUE][VALUE],….]

BEGIN

SELECT STATEMENT

END

Syntax to execute the user defined stored procedure:

EXEC [UTE] PROCEDURENAME [VALUE1,VALUE2,…]

Note: The number of values supplied through EXEC statement must be equal to the number parameters.

Stored procedure example in SQL?

Write a procedure to select the data from EMP table?

CREATE PROCEDURE P1

BEGIN

SELECT * FROM EMP

END

Write a procedure to select the data from EMP table based on user supplied DEPTNO?

CREATE PROCEDURE P2 @X INT

BEGIN

SELECT * FROM EMP WHERE DEPTNO=@X

END

EXEC P2 20

E.g.3: Write a procedure to add two numbers

CREATE PROCEDURE P3 @A INT=10,@B INT=20

BEGIN

DECLARE @C INT

SET @C=@A+@B

PRINT @C

END

EXEC P3

Output: 30

EXEC P3 25, 45

Output: 70

Note: Server will give highest priority to the user supplied values rather than default values.

USER DEFINED FUNCTIONS in SQL:

Functions created by user are called user defined functions

Types of user defined functions:

1. SCALAR VALUED FUNCTIONS

2. TABLE VALUED FUNCTIONS

Scalar valued functions:

These functions will return a scalar value to the calling environment

Syntax:

CREATE FUNCTION < FUNCTION_NAME> (@PARA 1 DATA TYPE ,

@ PARA 2 DATATYPE ,…..)

RETURNS< DATATYPE>

BEGIN

DECLARE @VARIABLE DATATYPE

--------

----------

RETURN @VARIABLE

END

Syntax to execute the user defined function:

SELECT/PRINT DBO.FUNCTIONNAME (VALUE1,VALUE2,……….)

Note: The number of values supplied through PRINT/SELECT statement must be equal to the number parameters.

Function example in SQL:

Write a function to find the product of two numbers?

CREATE FUNCTION F1 (@ A INT, @B INT)

RETURNS INT

BEGIN

DECLARE @ C INT

SET @C = @A * @B

RETURN @C

END

SELECT/PRINT DBO.F1 (3,5)

Write function to find the net salary of an employee read EMPNO though parameter and display the net to return value?

CREATE FUNCTION F2 (@ VNO INT)

RETURNS INT

BEGIN

DECLARE @ VSAL INT, @VCOM INT, @NET INT

SELECT @VSAL = SAL, @VCOM=COM

FROM EMP WHERE EMPNO =@VNO

IF @ VCOM IS NULL

BEGIN

PRINT ‘COMMISION IS NULL’

SET @NET = @VSAL

END

ELSE

BEGIN

SET @ NET = @VSAL + @VCOM

END

RETURN (@NET)

END

PRINT/SELECT DBO.F2(22)

2) Table valued function:

These functions will return entire table to the calling environment.

Syntax:

CREATE FUNCTION <FUNCTION_NAME>(PARA 1 DATA TYPE ……….)

RETURNS TABLE

BEGIN

<FUNCTION BODY>

RETURN (SELECT STATEMENT)

END

Write a function to return entire dept table?

CREATE FUNCTION F3()

RETURNS TABLE

BEGIN

RETURN (SELECT * FROM DEPT)

END

SELECT * FROM F3()

DEPT	DNAME	LOC

CREATE FUNCTION F4()

RETURN TABLE

AS BEGIN

RETURN(SELECT ENAME, DNAME FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO)

END

SELECT * FROM F4()

ENAME DNAME

SMITH RESEARCH

MILLER ACCOUNTING

For Part-2 Click here

Privacy policy , contact us

Pages

SQLServer Core Concepts

Truth table for AND

C1 C2 R

T T T
T F F
F T F
F F F

Truth table for OR

DEPTNO HISAL LOSAL TOTSAL AVGSAL

DEPTNO HISAL LOSAL TOTSAL AVGSAL

JOINS in SQL

No comments:

Post a Comment

Pages

SQLServer Core Concepts

Truth table for AND

C1 C2 R

T T T T F F F T F F F F

Truth table for OR

DEPTNO HISAL LOSAL TOTSAL AVGSAL

DEPTNO HISAL LOSAL TOTSAL AVGSAL

JOINS in SQL

No comments:

Post a Comment

T T T
T F F
F T F
F F F