SQL Notes For Database,Table,Constraints and Functions

SQL Notes For Database,Table,Constraints and Functions

DATABASE:

1        It is a Collection of  Tables

2        System Databases          

               These system Databases are “Brain’s of SQL Server” Then the SQL server can not operate without them.

 DATABASE CREATION:

Syntax:

CREATE DATABASE database_name

TABLE CREATION:

Syntax:

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)

CREATE TABLE [dbo].[DEPARTMENT](

            [DEPID] [int] NOT NULL,

            [EMPID] [int] NULL,

            [DOJ] [datetime] NULL,

            [DEPNAME] [varchar](30) 

 )

            --TO ADD NEW COLUMN IN TABLE:

                  ALTER TABLE Products ADD Average INT

--TO DROP THE COLUMN IN TABLE:

ALTER TABLE Products DROP COLUMN Average

--TO DROP THE COLUMN:

                  DROP TABLE Products

CORE OF SQL:

DML: (Data Manipulation Language)

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL: (Data Definition Language)

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL: (Data Control Language)

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL: (Transaction Control Language)

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

CONSTRAINTS:

Primary Key:

                    It is a column which is uniquely identifies each row of the table.

                    Must contain unique value.

                   Cannot contain null value.

                   

     E.g:       

            CREATE TABLE Persons

           (

           P_Id int NOT NULL PRIMARY KEY,

           LastName varchar(255) NOT NULL,

           FirstName varchar(255),

           Address varchar(255),

           City varchar(255)

           )

           When the table is already created means,

          

           ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)

         

           To drop a PRIMARY KEY constraint,

                      ALTER TABLE Persons DROP CONSTRAINT pk_PersonID 

Foreign Key:

A foreign key in one table points to a primary key in another table.

When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.

    Create Table Statement  to create Foreign Key

                   a.       Column Level

USE AdventureWorks2008

 GO

CREATE TABLEProductSales(SalesIDINT CONSTRAINTpk_productSales_sidPRIMARY KEY,

ProductIDINT CONSTRAINTfk_productSales_pidFOREIGN KEY REFERENCESProducts(ProductID),
SalesPersonVARCHAR(25)
);

GO

                   b.      Table Level

CREATE TABLEProductSales
(
SalesIDINT,
ProductIDINT,
SalesPersonVARCHAR(25)
CONSTRAINTpk_productSales_sidPRIMARY KEY(SalesID),
CONSTRAINTfk_productSales_pidFOREIGN KEY(ProductID)REFERENCESProducts(ProductID)
);
GO

                             1)      Alter Table Statement to create Foreign Key

ALTER TABLEProductSales
ADD CONSTRAINTfk_productSales_pidFOREIGN KEY(ProductID)REFERENCESProducts(ProductID)
GO

                            2)      Alter Table Statement to Drop Foreign Key

ALTER TABLEProductSales
DROP CONSTRAINTfk_productSales_pid;
GO

                        

                     It is one (or) more columns whose values are based on the primary key.

    E.g:           CREATE TABLE Orders

           (

           O_Id int ,

           OrderNo int NOT NULL,

           p_Id int,

           CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)

           REFERENCES Persons(P_Id)

           )

                      Table is already created means,

                      ALTER TABLE Orders                        

           ADD CONSTRAINT fk_PerOrders                       

           FOREIGN KEY (P_Id)                      

           REFERENCES Persons(P_Id)  

           To drop a FOREIGN KEY constraint,

           ALTER TABLE Orders                       

           DROP CONSTRAINT fk_PerOrders

Unique Key:

                         The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

                     It is one (or) more column that must be unique for each row of the table.

  E.g:             CREATE TABLE Persons

           (

           P_Id int NOT NULL,

           LastName varchar(255) NOT NULL,

           FirstName varchar(255),

           Address varchar(255),

           City varchar(255),

           CONSTRAINT uc_PersonID UNIQUE (P_Id)

           )     

         

           Table is already created means,

             

           ALTER TABLE Person

           ADD CONSTRAINT uc_PersonID  UNIQUE (P_Id)

           To drop a UNIQUE constraint,

                      ALTER TABLE Persons

           DROP CONSTRAINT uc_PersonID

Check Constraint:

                    The CHECK constraint is used to limit the value range that can be placed in a column.

                     

  E.g

               CREATE TABLE Persons
               (
               P_Id int NOT NULL CHECK (P_Id>0),
                LastName varchar(255) NOT NULL,
                FirstName varchar(255),
                Address varchar(255),
                City varchar(255)
)

          When the table is already created means,

     ALTER TABLE Persons

     ADD CONSTRAINT chk_Person CHECK (P_Id>0)

          To drop a CHECK constraint,

                      ALTER TABLE Persons

           DROP CONSTRAINT chk_Person

Not Null Constraint:

                    The NOT NULL constraint enforces a column to NOT accept NULL values.

  E.g:           CREATE TABLE Persons

          (

          P_Id int NOT NULL,

          LastName varchar(255) NOT NULL,

          FirstName varchar(255),

          City varchar(255)

          )

Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.

FUNCTIONS IN SQL SERVER:

SYSTEM FUNCTIONS:

   SQL String Functions:

                         These scalar functions perform an operation on a string input value and return a string or numeric value.

1        Char index ()

2        Replace ()

3        Reverse ()

4        Substring ()

5        Length ()

       E.g.:             --REPLACE:

             SELECT REPLACE ('abcdefghicde','cde','yyy')

              --REVERSE:

                SELECT REVERSE(1234) AS Reversed

              --SUBSTRING:

                SELECT SUBSTRING('sakthivel',1,6)

             --CHARINDEX:

               SELECT CHARINDEX('th','sakthi')

                   

                           --LENGTH:

                SELECT LEN(Coursename) FROM Course

       

SQL Aggregate Functions:

                         SQL aggregate functions return a single value, calculated from values in a column.

                         Useful aggregate functions:

ü AVG() - Returns the average value

ü COUNT() - Returns the number of rows 

ü MAX() - Returns the largest value

ü MIN() - Returns the smallest value

ü SUM() - Returns the sum

   E.g.:             --TO FIND THE AVG VALUE OF NUMERIC COLUMN:

  

                SELECT AVG(orderprice) AS Orderaverage FROM orders

             --TO FIND THE NO OF ROWS IN THE TABLE:

    SELECT COUNT(*) FROM orders

 --TO FIND THE NO OF DISTINCT ROWS IN THE TABLE:

    SELECT COUNT(DISTINCT ordercode) FROM  orders

             --TO FIND THE LARGEST & SMALLEST VALUE OF THE SELECTED COLUMN:

    SELECT MAX(orderprice) AS Largestorderprice FROM orders

    SELECT MIN(orderprice) AS Smallestorderprice FROM orders

--TO FIND THE TOTAL SUM OF SELECTED COLUMN:

    SELECT SUM(orderprice) AS Oredrtotal FROM orders

--TO FIND THE GROUP THE SELECTED COLUMN:

    SELECT customername,SUM(orderprice) FROM orders GROUP BY customername

Mathematical Functions:

These scalar functions perform a calculation, usually based on input values provided as arguments, and return a numeric value.

1        Round ()

2        Floor ()

3        Sqrt ()

   E.g.:    --FLOOR:

               SELECT FLOOR(234.56)

           --ROUND:

               SELECT ROUND(150.75, 0);

SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

           --SQRT:

               SELECT SQRT(100)