SQL & TSQL

SQL & T-SQL
------------------------------------------------------------

 Overview of Database:

Database (DB)

  1. It is a logical container which contains related object, object includes table, Views, Synonyms, Indexes , Stored procedure, Stored functions, Stored Trigger.
Database Management System (DBMS)


  1. It is a collection of software programs used to stores the data, Managed data, retrieve the data
  2. Data retrieval is based on three points

  • ACCURACY: Data free form error.
  • TIMELINESS: With a stipulated time
  • RELEVANCY:  Related data
Types of Database according to to different models
  1. ER Models
  2. Object oriented Models
  3. Record based logical model
    • HDBMS
    • NDBMS
    • RDBMS
    • ORDBMS
Relational Database management System -(RDBMS)

 R- Referential Integrity Constraints

 Referential means Relationship 

  • One-one - Direct implementation
  • One-many - Direct implementation
  • Many- many - Indirect implementation
In the year 1970 by mathematician Dr. EF Codd  defined in a seminar paper (Relational model of data for large shared data banks)


 Collection of characters is called columns or identifiers or fields or attribute
Collection of column's is called as rows or records or tuple.
Collections of cols or rows is called table or object, entity or relation.

 Basic meaning of Normalization .

  • It is a process of splitting single table to 2 or more sub table.
  • To avoid redundancy
  • To promote integrity.
Components of RDBMS
  1. Collection of objects or relations to store data either in the form of rows or Columns
  2. Set of operations applied on or more relations that produced new relations .i.e. JOIN.
  3. Data integrity-  
  • Always you will get accuracy
  • Data consistency.
SQL- Structured Query Language

It is a language which is used to communicate with database server.
How SQL came?
Earlier SQL it was named as SQUARE( Specification of query as relational expression)
Then its named as SEQUEL- (Structured English query language)
Then its simplified with name SQL

SQL is a product of IBM

SQL used by different company and they and made available SQL according to their standards
  • SQL Server
  • My Sql
  • DB2
  • Teradata
  • Sybase
  • Access
  • Oracle
SQL is a common database language since it can get understand by every RDBMS
SQL is a command based language
SQL is insensitive language(Lower case, Upper case)
SQL is a non-procedural language

SQL SERVER

Sql server consists of following sub language

DQL: Data query or Relational Language 
  • Select
DML: Data manipulation language
  • Insert
  • Update
  • Delete
  • Merge
TCL: Transaction Control Language
  • Commit
  • Roll Back
  • Save
DDL: Data Definition Language
  • Create
  • Drop
  • Alter
  • Truncate
DCL: Data Control Language
  • Grant
  • Revoke
  • Deny
SCL: Session Control Language


DML Statement and Examples

Insertion of new rows
Modification of existing rows
removing unwanted rows 
is known as DML statement

INSERT: 

  • This DML Command is used to insert new rows into existing table.
  • Inserting 1 row into 1 table
  • Inserting multiple rows into 1 table
  • Inserting multiple rows into multiple table
  • Single insertion is possible is SQL server 2005, but multiple insertion is possible in SQL server 2008 onward.
  • Null value can be insertion
Syntax and example:
Insert into Dept values(50,'HR','HYD')  
Insert into Dept values(50,'HR','HYD'),(60,'NR','HYD')
Insert into Dept values(50,'HR',NULL)
  • Also insert like this manner.
  • Insert  tableName1 select * from Tablename2 ---> All column will insert
  • Insert  tableName1 (EmpName,EmpNo,Sal) select EmpName,EmpNo,Sal from Tablename2 ---> Partial column will insert


UPDATE: 


This DML Command is used to modify existing data into 
  • Single column single rows,
  • Single column multiple rows
  • Multiple column multiple rows.
  • Null value can be insertion
Syntax and example:
Update table_name set Col1=Var/expression, Col2=Var/expression [where conditions]
Update emptable set sal_column=200 where ename='ALLEN'
Update emptable set sal_column=200,ename='JEMS' where ename='ALLEN'
Update emp set sal=sal+sal*12/100 where deptno=10

    DELETE: 


    This DML Command is used to one or more rows 

    Syntax and example:
    Delete from table_name [Where condition]
    Delete from emp where deptNo in (10,30) 

    Update emp set job=null (Delete the data of a column)

    MERGE: 
    • It works only Sql server 2008
    • It will terminated by (; )
    • This command is used to merge two table into single table
    • It is often called as 'UPSERT' i.e UPDATE+ INSERT
    • This command will work with a support of 4 clauses
    • Using clause
    • On clause
    • When Matched clasuse
    • When not matched clause
    Syntax and example:
    MERGE INTO emp USING (SELECT empno,empname,Sal from emp) c2 
    ON (C1.empno=c2.empno)
    WHERE MATCHED THEN
    UPDATE SET c1.sal=c2.sal
    WHEN  NOTMATCHED THEN
    INSERT VALUES (c2 empno,c2 empname,c2 sal)


    JOINS

    • It is a process of joining columns from two or more table and always produces a new table
    • Joining condition is must
    • To join nth table n-1 joining condition one required.
    • Any number of filtering condition can be provided after joining conditions
    Standard Sql Syntax (Operators)
    • Equi join 
    • Non Equi join
    • Left outer join
    • Right outer join
    • Cartesian join
    • Self join
    ANSI syntax (keywords)
    • Inner Join - Eque, Non Eque and self Join)
    • Cross Join - (Similar to Cartesian join)
    • Outer Join
    Keywords-
    • Left Outer Join
    • Right Outer Join
    • Full outer Join

    Syntax: 
    SELECT--- 
    FROM Table1 [Table1 Alias]
    <Join Type>
    Table2 [Table2 Alias]
    ON (Joining Condition)

    <Join Type>
    Table3 [Table3 Alias]
    ON (Joining Condition) 




    Stored Procedure.
    1. It is a stored block database object which contain SQL Statements and Procedural features.
    2. Stored Procedure can contain Multiple Queries.
    3. Stored Procedure can be created with or without argument.
    4. There are 3 Types of Stored Procedure
    i) User defined Stored Procedure .
    ii) Built-in Stored Procedure. Ex: sp_HelpText, sp_HelpDb
    iii) Extended Stored Procedure --Ex.: sp_cmdcell, sp_Readmail

    SYNTAX:

    Create Procedure ProcedureName
    [(With Argument)]
    [(With Encryption)]
    As
    Begin
    Declaration Part
    Execution Part
    Exception Part
    End

    To ececute Procedure EXEC procedure name Value1,Value2 

    To Display Code of Procedure -sp_HelpText ProcedureName

    Execution Process

    1 St Time It Compiled
    2nd Execution Plan Generated
    3rd Get Executed

    EXAMPLE

    Create Procedure InsertEmp(@eno int,@ename varchar(10))
    AS
    BEGIN
    insert into emp(@eno,@ename)
    END


    ADVANTAGES
    1. User will get Quick Response
    2. Code Re-usability
    3. Sql injunction are avoided.
    4. Application performance will be improves.



    Trigger & Types Of Trigger.?

    1. Trigger is a T-sql Program Unit which associate with Table.
    2. Trigger is automatically fired when any DML operation performed on a table.
    3. Trigger does not take any argument like procedure and Function.
    4. There are 3 types of Trigger. (i) For/ After Trigger (ii) Logon Trigger (iii) Insted of Trigger
    5. Insted of Triggers are executed before the primary key and Foreign Key constraint checked.
    6. After Trigger  are executed After the primary key and foreign key are checked.

    SYNTAX

    Create  Trigger trigger_name on Table_name
    FOR/AFTER TRIGGER
    AS
    BEGIN
    DECLARATION
    TRIGGERING CONSTRAINT
    TRIGGERING EVENTS
    END
    EXAMPLE

    CREATE TRIGGER TR1 ON EMP
    FOR INSERT,DELETE
    AS
    BEGIN
    IF DateName(DW,Getdate())=="sunday"
    BEGIN
    ROLLBACK;
    RAISERROR(""Cant Delete or Insert "15,16)
    END
    END

    What is View ?

    View is a window or virtual table which is allows the related user to view and update data
    View is a database object which is resides in the database server and stores only selected queries in a compiled format.
    Any modification made through view will reflected on base table or viceversa.
    Types of view
    (i) Simple View
    (ii) Complex View
    (iii) Index View
    (iv) Checked Option View

    ADVANTAGES

    1. Its Provide Security
    2. Performance Gets Increase.
    3. Network Traffic reduces




    What is Cursor ?

    What is Index & Types of Index.
    Diff Bet Delete & Tuncate.
    Unique Key Primary Key, Foreign Key
    Join & types of Join.
    What is Merged Command.
    Diff between procedure and Function.
    Aggregate Function.
    Set Join & Set operator.
    What is SqlInjuction.
    Diff between Having Clause & where clause.
    Temp Variable Vs Table variable.
    Diff between clustered index & Non-clustered index.
    Table Value Function & Scalared Function.

    SQL QUERIES
    INSERT /UPDATE/DELETE in one Procedure

    ALTER PROC [dbo].[SP_sp_MST]
    (
    @TYP NVARCHAR(2),
    @sp_CODE INT=NULL,
    @sp_ID NVARCHAR(10)=NULL,
    @sp_NM NVARCHAR(50)=NULL,
    @sp_ONR_NM NVARCHAR(50)=NULL,
    @sp_DL_NO NVARCHAR(50)=NULL,
    @sp_TIN_NO NVARCHAR(50)=NULL,
    @sp_CNT_NO NVARCHAR(20)=NULL,
    @sp_MAIL_ID NVARCHAR(50)=NULL,
    @sp_ADDR NVARCHAR(1000)= NULL,
    @sp_BNK_NM  NVARCHAR(50)=NULL,
    @sp_AC_NO  NVARCHAR(30)=NULL,
    @sp_STS BIT=NULL
    )
    AS
    BEGIN TRY
    IF @TYP='I'
    BEGIN
    INSERT INTO sp_MST( sp_ID,sp_NM,sp_ONR_NM,sp_DL_NO,sp_TIN_NO,sp_CNT_NO,sp_MAIL_ID,sp_ADDR,sp_BNK_NM,sp_AC_NO,sp_STS)
    VALUES(@sp_ID,@sp_NM,@sp_ONR_NM,@sp_DL_NO,@sp_TIN_NO,@sp_CNT_NO,@sp_MAIL_ID,@sp_ADDR,@sp_BNK_NM,@sp_AC_NO,@sp_STS)
    END
    IF @TYP='U'
    BEGIN
    UPDATE  sp_MST SET 
    --sp_ID=@sp_ID,
    sp_NM=@sp_NM,
    sp_ONR_NM=@sp_ONR_NM,
    sp_DL_NO=@sp_DL_NO,
    sp_TIN_NO=@sp_TIN_NO,
    sp_CNT_NO=@sp_CNT_NO,
    sp_MAIL_ID=@sp_MAIL_ID,
    sp_ADDR=@sp_ADDR,
    sp_BNK_NM=@sp_BNK_NM,
    sp_AC_NO=@sp_AC_NO,
    sp_STS=@sp_STS
    WHERE sp_CODE=@sp_CODE
    END
    END TRY
    BEGIN CATCH
    END CATCH


    ------------

    Computed Column Using Pivot & CTE

    CREATE TABLE [dbo].[TEMPTBL](
    [2012] [int] NULL,
    [2013] [int] NULL,
    [2014] [int] NULL,
    [2015] [int] NULL,
    [2016] [int] NULL,
    [2017] [int] NULL,
    [total] [int] NULL,
    [category] [varchar](50) NULL
    ) ON [PRIMARY]
    GO


    ---
    CREATE TABLE [dbo].[Heading](
    [SNo] [bigint] IDENTITY(1,1) NOT NULL,
    [ActualHeading] [float] NULL,
    [NewHeading] [varchar](50) NULL,
    [category] [varchar](50) NULL,
    [total] [float] NULL
    ) ON [PRIMARY]
    GO
    ---
    CREATE TABLE [dbo].[ProjectsTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Year] [varchar](50) NOT NULL,
    [Category] [varchar](100) NOT NULL,
    [Amount] [money] NULL,
     CONSTRAINT [PK_ProjectsTable_1] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ----
    create function [dbo].[GetTotal](@category varchar(50))
      returns int
      as 
      begin
      declare @tot int
     select @tot=  sum(amount) from dbo.ProjectsTable 
      where year in(2012,2013,2014,2015,2016,2017) and category=@category
      group by category
      return @tot
      end
    GO
    ----

    CREATE procedure [dbo].[GetData]  @startyear int,@endyear int
    as
    begin
    declare @prior  varchar(10)
    declare @future  varchar(10)

    --set @styear=2012
    --set @endyear=2017
    select @prior=convert(varchar,sum(amount)) from projectstable where year between (select min(year)
     from projectstable) and (@startyear-1)


    select @future=convert(varchar,sum(amount)) from projectstable where year 
    between (@endyear+1) and (select max(year) from projectstable) ;




    delete dbo.heading
    insert into dbo.heading  select amount,year,category,sum(amount) 
    from projectstable where year between @startyear and @endyear 
    group by amount,year,category 

    DECLARE @SQL AS VARCHAR(MAX)
    DECLARE @Columns AS VARCHAR (MAX)
    DECLARE @ColumnsRollup AS VARCHAR (MAX)

    SELECT @Columns =
    COALESCE(@Columns + ', ','')+ QUOTENAME(newheading)
    FROM
    (
        SELECT distinct newheading 
        FROM dbo.Heading
    ) AS B
    ORDER BY B.newheading

    SELECT @ColumnsRollup =
    COALESCE(@ColumnsRollup + ', Sum(','Sum(')+ QUOTENAME(cast(newheading as varchar(10)))+')  YEAR'+cast(newheading as varchar(10))
    FROM
    (
        SELECT distinct newheading 
        FROM heading
    ) AS B
    ORDER BY B.newheading


               SET @SQL = 'WITH PivotData AS
                (
                    SELECT newheading,ActualHeading , category
                    FROM heading
                )
                SELECT case when category is not null then cast(category as varchar(10))
                    else ''Total'' end category
                    ,case when category is null then ' +@prior+ '   else null end as Prior

                    , ' + @ColumnsRollup+ ',sum(dbo.GetTotal(category)) as total
                
                   ,case when category is null then ' +@future+ ' else null end as Future 
                FROM
                (
                    SELECT category, ' + @Columns + '
                    FROM PivotData
                    PIVOT
                    (
                        SUM(ActualHeading)
                        FOR newheading IN (' + @Columns + ')
                    ) AS PivotResult
                ) src
                GROUP BY category with ROLLUP'

    EXEC (@SQL)
    end

    --exec getdata 2012,2017
    GO
    /****** Object:  StoredProcedure [dbo].[SP_INS_ProjectsTable]    Script Date: 07/19/2013 10:19:04 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[SP_INS_ProjectsTable]
    (
    @Year varchar(50),
    @Category varchar(100),
    @Amount money,
    @message varchar(100) output
    )
    AS
    declare @row_count int
    BEGIN
    select @row_count = count(*) from ProjectsTable where Year=@Year and Category=@Category and Amount = @Amount;
    if @row_count = 0 
    begin
    insert into ProjectsTable(Year,Category,Amount)
    values(@Year,@Category,@Amount);
      set @message = '';
    end
    else
    set @message = 'The Records already exists in the database';

    END
    GO

    No comments:

    Post a Comment

    Subscribe to: Comments (Atom)

    Search This Blog