Sql Server material 9

FUNCTIONS: SQL Server 2005 provides built-in functions that can be used to perform certain operations. Functions can be used or included in the following:

-          The select list of a query that uses a SELECT statement to return a value.

-          A WHERE clauses search condition of a SELECT statement to limit the rows that qualify for the query.

Syntax for executing a function:

SELECT <Fun Name> ( [ <expressions> ] )

-The expression can be a constant values or a name of a column.

Functions can be classified into 2 types:

            -Single Row Functions

            -Group Functions

A single row function executes once for each row that is present in the table where as group functions take multiple rows into consideration and returns a single value as output.

Single Row Function Categories:

            -Mathematical Functions

            -String Functions

            -Date and Time Functions

            -System Functions

Mathematical Functions: These functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value; they take “n” as input where n is a numeric expression.

ABS (n): A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Select ABS(10)                                   Ouput: 10

Select ABS(-10)                                  Ouput: 10

CEILING (n): Returns the smallest integer greater than, or equal to, the specified numeric expression.

SELECT CEILING(15.6)                  OUTPUT: 16

SELECT CEILING(15.6)                  OUTPUT: -15

CEILING (n): Returns the largest integer less than or equal to the specified numeric expression.

SELECT FLOOR(15.6)                                 OUTPUT: 15

SELECT FLOOR(15.6)                                 OUTPUT: -16

LOG (n): Returns the natural logarithm of the specified expression, i.e. base-e

            SELECT LOG(10)                             OUTPUT: 2.30258509299405

LOG10 (n): Returns base-10 logarithm of the specified expression, i.e. base e

            SELECT LOG10(10)                         OUTPUT: 1

PI(): Returns the constant value of PI.

            SELECT PI()                                      OUTPUT: 3.14159265358979

<<<Previous                                                                                                                              Next >>>

Sql Server material 8

Alter Command: After creating a table if we want to make any modifications to the structure of the table we use the Alter Command. Using alter command we can perform the following:

-Increase/Decrease the width of a column.

-Change the data type of a column.

-Change Null to Not Null and Not Null to Null

-Add a new column to the table.

-Drop an existing column from the table.

-Add a constraint to a column of the table.

-Drop an existing constraint present on a column from the table.

-To perform the first 3 operations the syntax is:

ALTER TABLE <TNAME> ALTER COLUMN <COLNAME> <DTYPE> [WIDTH] [NULL | NOT NULL]

First create a table as following:

            CREATE TABLE Students (SNO int, Sname varchar(50), Class int)

Increasing the width of a column:     

            ALTER TABLE Students ALTER COLUMN Sname varchar(100)

Decreasing the width of a column:    

            ALTER TABLE Students ALTER COLUMN Sname varchar(25)

Changing the data type of the column:         

            ALTER TABLE Students ALTER COLUMN Sname nvarchar(25)

Adding a Not Null Constraint:

            ALTER TABLE Students ALTER COLUMN Sname nvarchar(25) Not Null

Removing a Not Null Constraint:

            ALTER TABLE Students ALTER COLUMN Sname nvarchar(25) Null

Syntax to add a new column:

            ALTER TABLE <TNAME> ADD <COLNAME> <DTYPE> [<WIDTH>] 

[ [CONSTRAINT <CONS NAME>] <CONS TYPE> ]

Adding a column to the table with out any constraint:         

ALTER TABLE Students ADD Fees Money

Adding a column to the table with a constraint:

            ALTER TABLE Students ADD Sid int Constraint Sid_UQ UNIQUE

Syntax to drop an existing column:

            ALTER TABLE <TNAME> DROP COLUMN <COLNAME>

Dropping the Sid Column:

            ALTER TABLE Students DROP COLUMN Sid

Syntax to Add a Constraint:

ALTER TABLE <TNAME> ADD [ CONSTRAINT <CON NAME> ] <CONS TYPE> (COLLIST)

Adding a check constraint on the Fees column:

            ALTER TABLE Students ADD Constraint Fees_CK Check (Fees>1500)

Adding a primary key constraint on the Sno column:

            ALTER TABLE Students ALTER COLUMN SNO INT NOT NULL

            ALTER TABLE Students ADD Constraint Sno_PK Primary Key(SNO)

Syntax to Drop a Constraint:

            ALTER TABLE <TNAME> DROP CONSTRAINT <CONS NAME>

Dropping the check constraint present on the Fees column:

            ALTER TABLE Students DROP Constraint Fees_CK

Drop Command: If we want to destroy the existing tables present in the database we use the Drop Command.

Syntax: DROP TABLE <TNAME>

Dropping the Students Table:

            DROP TABLE Students                 

Truncate Command: Removes all rows from a table. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause specified.

Syntax: TRUNCATE TABLE <TNAME>

Truncating the EMP Table:

            TRUNCATE TABLE Students

The difference between Truncate and Delete is:

• Truncate table is faster in execution.
• Truncate will reset the identity function if present on the table to initial value again which will not happen in delete.

<<< Previous                                                                                                                            Next >>> 

Sql Server material 7

Foreign Key Constraint: it is a column or combination of columns that is used to establish and enforce a link between the data in two tables. In a foreign key reference, a link is created between two tables when the column(s) in a table reference the column(s) that hold the primary key of other table, which becomes a foreign key in the first table.  

For example, the Dept.Deptno table below has a link to the Emp.Deptno table because there is a logical relationship between Dept table and Emp table. The Deptno column in the Emp table matches the primary key column of the Dept table. The Deptno column in the Emp table is the foreign key to the Dept table. In this case the value that is going to be inserted into the Deptno column of the Emp table should be present in the Deptno column of the Dept table or should be a null values.

-          In this case the Dept table is called as Parent table and Emp table is called as Child table.

-          Dept.Deptno is called as Reference Key column on which either Primary Key Constraint or Unique Constraint has to be imposed.

-          Emp.Deptno is called as Foreign Key column on which the Foreign Key Constraint has to be imposed, with this only the link gets established between the 2 tables.

Create Table Dept (Deptno int Constraint Deptno_Pk Primary Key, DName varchar(50), Loc varchar(50))

1.      Insert into Dept values (10, 'Marketing', 'Mumbai')

2.      Insert into Dept values (20, 'Sales', 'Chennai')

3.      Insert into Dept values (30, 'Finance', 'Delhi')

4.      Insert into Dept values (40, 'Production', 'Kolkota')

-Creating Foreign Key Constraint in column level:

Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, Sal Money, Comm Money, Deptno int Constraint Deptno_Ref References Dept (Deptno))

-Creating Foreign Key Constraint in column level: while defining constraint in table level we need to explicitly use the Foreign Key clause to specify the Foreign Key Column:

Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, Sal Money, Comm Money, Deptno int,

Constraint Deptno_Ref Foreign Key (Deptno) References Dept (Deptno))

-Now when we try to insert values into the Emp table the Deptno what we give should be only the 4 values (10, 20, 30, 40) present in the dept table or a null value, if we try to insert any other value the insert statement fails.

1.      Insert into Emp Values (1001, 'Suresh', 'President', NULL, '01/01/78', 5000, NULL, 10)

2.      Insert into Emp Values (1002, 'Ramesh', 'Manager', 1001, '01/01/78', 4000, NULL, 20)

3.      Insert into Emp Values (1003, 'Ravi', 'Manager', 1001, '01/01/78', 3500, NULL, 30)

4.      Insert into Emp Values (1004, 'Vijay', 'Manager', 1001, '01/01/78', 4000, NULL, 40)

5.      Insert into Emp Values (1005, 'Ajay', 'Salesman', 1003, '02/04/79', 3000, NULL, 50)

-In this case the first 4 statements gets executed but last statement fails because the Deptno given is not present in the dept table.

The Foreign Key constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

By using cascading referential integrity constraints, you can define the actions that the SQL Server 2005 takes when a user tries to delete or update a key value in the master table to which existing foreign keys point.

The REFERENCES clauses of the CREATE TABLE statements support the ON DELETE and ON UPDATE clauses:

-ON DELETE <NO ACTION | CASCADE | SET NULL | SET DEFAULT>

-ON UPDATE <NO ACTION | CASCADE | SET NULL | SET DEFAULT>

NO ACTION is the default if ON DELETE or ON UPDATE is not specified.

ON DELETE NO ACTION: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, an error is raised and the DELETE statement will not execute.

ON UPDATE NO ACTION: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, an error is raised and the UPDATE statement will not execute.

When NO ACTION is specified we can delete or update or delete rows in the master table if they are referenced by child table rows, but we can perform those operations when we use CASCADE, SET NULL AND SET DEFAULT CLAUSES:

ON DELETE CASCADE: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are also deleted.

ON UPDATE CASCADE: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all the foreign key values will also be updated to the new value specified for the key.

ON DELETE SET NULL: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to NULL, provided the foreign key column allows NULL values into it.

ON UPDATE SET NULL: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to NULL, provided the foreign key column allows NULL values into it.

ON DELETE SET DEFAULT: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default value set, NULL becomes the implicit default value of the column.

ON UPDATE SET DEFAULT: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default value set, NULL becomes the implicit default value of the column.  

We can use any of the rules beside the column as following:

Deptno int Constraint Deptno_Ref References Dept (Deptno) on delete cascade on update cascade

In the same way you can use any rule there and also not mandatory to specify both the delete and update rule, we can use any rule what we require.

<<< Previous                                                                                                                     Next >>>

Sql Server material 6

Primary Key Constraint.

-While Creating a primary key constraint we need to keep this in mind i.e. a table can contain only a single primary key present on it which can be present on a single column or multiple columns also.

Creating Primary Key in column level:

CREATE TABLE Bank(Custid int Constraint Cusid_PK Primary Key, Cname varchar(50), Bal decimal(7,2) Not Null)

Creating Primary Key in table level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Cusid_PK Primary Key(Custid))

Creating a Composite Primary Key in table level:

CREATE TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint CC_BC_PK Primary Key(CityCode, BranchCode))

Check Constraint: If we want to check the values present in a column to be according to a specified value we use this constraint.

-If we want to restrict the Bal in the bank table should be some specified values then we can use the constraint as following:

            CHECK (Bal>=1000) –> Checking Bal should be greater than equal to 1000

CHECK (Bal BETWEEN 1000 AND 9999) -> Checking Bal should be within the range of 1000 and 9999

CHECK (BAL IN (3000, 5000, 7000)) -> Checking the Bal Should be within any of the three values only

Creating Check Constraint in column level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Constraint Bal_CK Check (Bal>=1000))

Creating Check Constraint in table level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2), Constraint Bal_CK Check(Bal BETWEEN 1000 AND 9999))

Default Value: The default value for any column if a not null constraint is not present on it is “NULL”, which can be changed by using the Default Clause while creating the table as following:

           

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Default 1000)

-In the above case if have not specified any value to the Bal column while inserting then it takes 1000 as default

            INSERT INTO Bank (Custid, Cname) VALUES (101, ‘Ravi’)

Identity Function: Generally for any column if we want to insert only unique values then we can hand over the task to the identity function, so that it takes the responsibility of inserting a unique value in to the column as following:

            Colname <dtype> [width] Identity [(Seed, Incr)]

            Seed – It is the starting value for the identity function.

            Incr – It is the difference between to subsequent values generated by the function.

-Both of them are optional, if not specified 1 and 1 are taken as values.

-When we use the identity function on a column we cannot explicitly insert any values into the column using the insert statement.

-A table can have only one identity column present in it.

-Generally we use this on Primary Key Columns.

CREATE TABLE Bank(Custid int identity(101, 1), Cname varchar(50), Bal decimal(7,2))

-In this case when we insert rows into the table then it automatically generates a identity value starting from 101.

            INSERT INTO Bank (Cname, Bal) VALUES (‘Raju’, 3500)

<<< Previous                                                                                                                               Next >>>

Sql Server material 5

Constraints: used to enforce the integrity of the data in the columns, SQL Server 2005 provides the following mechanisms to enforce the integrity of the data in column:

            -Not Null

            -Unique

            -Primary Key

            -Check

            -Default          

-Foreign Key

Not Null: If it is imposed on a column that column will not allow Null Values into it; this can be imposed on any no of columns.

            -CREATE TABLE <table_name>(

             column_name1 <dtype> [width] [Not Null],

             column_name1 <dtype> [width] [Not Null],

             ………………….

             column_namen <dtype> [width] [Not Null])

Recreating the bank table with Not Null Constraint on it:

CREATE TABLE Bank(Custid int Not Null, Cname varchar(50), Bal decimal(7,2) Not Null)

After creating this if we try to insert a null value into the Custid or Bal columns it will restrict us:

            INSERT INTO Bank VALUES (NULL, ‘RAJU’, 3500)

            INSERT INTO Bank (CUSTID, CNAME) VALUES (101, ‘RAVI’)

The drawback with Not Null Constraint is even if it restricts null values it will not restrict duplicate values, if they has to be restricted we use the Unique Consraints.

Unique: If it is imposed on a column or columns they will not allow duplicate Values into it.

Note: Unique, Primary Key, Check and Foreign Key Constraints can be imposed in two different ways:

            -Column Level Definition

            -Table Level Definition

Column Level Definition: In this case the constraint definition is immediately followed after the column definition. The syntax is:

           

            -CREATE TABLE <table_name>(

             column_name1 <dtype> [width] [ [Constraint <Name>] <Type> ],

             column_name1 <dtype> [width] [ [Constraint <Name>] <Type> ],

             ………………….

             column_namen <dtype> [width] [ [Constraint <Name>] <Type> ],

Recreating the bank table with Unique Constraint on it:

CREATE TABLE Bank(Custid int Unique, Cname varchar(50), Bal decimal(7,2) Not Null)

After creating this if we try to insert a duplicate value into the Custid column it will restrict us:

            INSERT INTO Bank VALUES (101, ‘RAJU’, 3500)

            INSERT INTO Bank VALUES (101, ‘RAVI’, 4500)

Note: Internally Unique, Primary Key, Check and Foreign Key Constraints are identified by using some unique name which has to be given by us or else the system will give a name, so when we violate these constraints it will show the name of the constraint in the error message, by seeing which we require to identify on which column we are getting the problem, but if the table is not created by us or we don’t remember the structure of the table we cannot identify. So it is advised to give a name to the constraint so that when it violates the error message shows the name of the constraint using which we can easily identify where the violation has been done.

While giving a name to the Constraint they follow some conventions like:

                        <ColumnName_ConstraintType>

                        <TableName_ColumnName_ConstraintType>

Recreating the bank table with Unique Constraint by giving a name to it:

CREATE TABLE Bank(Custid int Constraint Cusid_UQ Unique, Cname varchar(50), Bal decimal(7,2) Not Null)

Table Level Definition: In this case the constraint definition is immediately followed after the column definition. The syntax is:

           

            -CREATE TABLE <table_name>(

             column_name1 <dtype> [width],

             column_name1 <dtype> [width],

             ………………….

             column_namen <dtype> [width],

 [ [Constraint <Name>] <Type> (<Collist>)],

…………………….)

Note: A Not Null Constraint Cannot be defined table level.

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Cusid_UQ Unique(Custid))

-In this case because the constraint is defined in the end of all the columns it cannot understand to which column the constraint depends so we need to specify the column name beside the constraint for identification.

-There will not be any difference in execution whether the constraint is defined in table level or column level.

-When we define a constraint in table level we can define composite constraint i.e. a single constraint on multiple columns.

CREATE TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint CC_BC_UQ Unique(CityCode, BranchCode))

INSERT INTO BankDetails Values(‘C1’, ‘B1’)

INSERT INTO BankDetails Values(‘C1’, ‘B2’)

INSERT INTO BankDetails Values(‘C1’, ‘B3’)

INSERT INTO BankDetails Values(‘C2’, ‘B1’)

INSERT INTO BankDetails Values(‘C2’, ‘B2’)

INSERT INTO BankDetails Values(‘C2’, ‘B3’)

-In this case all the statements are valid because a composite unique constraint checks the uniqueness on the combination of columns, but not on a single column.

The drawback with Unique Constraint is even if it restricts duplicate values it will allow a single null value in to the column. If we want to restricted duplicate values as well as null values we need to use Primary Key Constraint.


 <<< Previous                                                                                                                                Next >>>

Sql Server material 4

Updating data present in the tables: if we want to Update the data existing in the table we use 

Update Statement:

Syntax:

            -UPDATE <TNAME> SET <CNAME>=<VALUE> [, …..] [CONDITIONS]

Note: We can modify a single column or multiple columns using the update statement all the rows that satisfy the condition gets affected.

            -UPDATE BANK SET CNAME=’RAMESH’ WHERE CUSTID=104

            -UPDATE BANK SET CNAME=’RAJESH’, BAL=3000 WHERE CUSTID=105

Deleting data present in the tables: if we want to delete rows of data present in the table we use

Delete Statement:

Syntax:

            -DELETE FROM <TNAME> [CONDITIONS]

            -DELETE FROM BANK WHERE CUSTID=105

            -DELETE FROM BANK


<<< Previous                                                                                                                          Next >>>

Sql Server material 3

Populating Data into Tables: after the table gets created to populate the data into it we use

Insert Statement:

Syntax for Insert statement:

            -INSERT INTO <table_name> [(col1, col2, ……..coln)]

             VALUES (val1, val2, …….valn)

Examples:

            INSERT INTO BANK VALUES (101, ‘VENKAT’, 4500)

 -In this case we need to provide the values for all the columns in the same order they are present in the table.

-String and Date values have to be enclosed in single quotes.

INSERT INTO BANK (CUSTID, CNAME, BAL) VALUES (102, ‘SUBASH’, 5600)

-This statement is same as above statement.

-If we want to change the order of columns while inserting:

INSERT INTO BANK (CNAME, CUSTID, BAL) VALUES (‘SURESH’, 103, 6500)

-If we want to insert data only into required columns then:

            INSERT INTO BANK (CUSTID, BAL) VALUES (104, 3600)

-In this case the columns into which values are not supplied are filled with Null value.

-We can also insert Null’s explicitly into the column in the following way:

INSERT INTO BANK VALUES (105, NULL, 5400)

Retrieving the data from Tables: if we want to retrieve the information from the table use

Select Statement:

Basic Syntax for Select statement:

            SELECT < * | COLLIST > FROM <TNAME> [CONDITIONS]

-          ‘*’ Represents all the columns of the table in the same order.

-          COLLIST is used for specifying the required no of columns and in required order.

-          CONDITIONS are optional which can be used for retrieving the required rows.

-SELECT * FROM BANK

-SELECT CUSTID, CNAME, BAL FROM BANK

-SELECT CNAME, BAL, CUSTID FROM BANK

-SELECT CUSTID AS ACCNO, CNAME, BAL FROM BANK

-We can specify an alias name for any required column while retrieving known as Column Alias.

-If we want to retrieve required rows then we use a conditional statement where:

            -SELECT * FROM BANK WHERE CUSTID=104

            -SELECT CUSTID, BAL FROM BANK WHERE CNAME=’SURESH’

-SQL Server does not have any case restrictions while writing the conditions.

Handling Null Values: The value NULL means the data value for the column is unknown or not available, so we cannot use equality condition while getting the data based on null values.

            SELECT * FROM EMP WHERE CNAME=NULL

-The above statement will not get any result because no 2 null values can be compared so to get the data based on Null values we should use the IS NULL operator as following:

            SELECT * FROM EMP WHERE CNAME IS NULL 


<<< Previous                                                                                                                       Next >>>