Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    Case Study 3

    Uploaded by

    rohit rajput

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Case Study 3

    Uploaded by

    rohit rajput
    1 views5 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    1 views5 pages

    Case Study 3

    Uploaded by

    rohit rajput

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 5

    select * from [dbo].

    [Continent]
    select * from [dbo].[Customers]
    select * from [dbo].[Transactions]

    1 Display the count of customer in each region who has done the transaction in year
    2020

    select con.region_name, count(distinct cus.customer_id) as count from


    [dbo].[Continent] con
    inner join [dbo].[Customers] cus
    on con.region_id = cus.region_id
    inner join [dbo].[Transactions] t
    on t.customer_id = cus.customer_id
    where year(t.txn_date) = 2020
    group by con.region_name

    2 Display the maximum, minimum of transaction amount of each transaction type

    select txn_type, max(txn_amount) as max,


    min(txn_amount) as min from [dbo].[Transactions] group by txn_type

    3 Display customer id, region name and transaction amountwhere transaction type is
    deposit and transaction amount > 500

    select cus.customer_id, con.region_name, t.txn_amount from


    [dbo].[Continent] con
    inner join [dbo].[Customers] cus
    on con.region_id = cus.region_id
    inner join [dbo].[Transactions] t
    on t.customer_id = cus.customer_id
    where t.txn_type = 'deposit' and t.txn_amount > 500

    4 Find duplicate records in a customer table.

    SELECT customer_id, count(*)


    FROM Customers
    GROUP BY customer_id
    HAVING count(*) > 1;

    5 Display the detail of customer id, region name, transaction type and transaction
    amount for the minimum transaction amount in deposit

    select distinct cus.customer_id, con.region_name, t.txn_type from


    [dbo].[Continent] con
    inner join [dbo].[Customers] cus
    on con.region_id = cus.region_id
    inner join [dbo].[Transactions] t
    on t.customer_id = cus.customer_id
    WHERE txn_amount = (SELECT MIN(txn_amount) FROM
    [dbo].[Transactions] )
    6 Create a stored procedure to display details of customer and transaction table where
    transaction date is greater than Jun 2020

    CREATE PROCEDURE GetCustomersTransactions (@startDate date)


    AS
    BEGIN
    SELECT c.customer_id, r.region_name, t.txn_date, t.txn_type,
    t.txn_amount
    FROM customers c
    INNER JOIN continent r ON c.region_id = r.region_id
    INNER JOIN [dbo].[Transactions] t ON c.customer_id = t.customer_id
    WHERE t.txn_date > @startDate
    END
    exec GetCustomersTransactions '2010-05-01'

    7 Create a stored procedure to insert a record in the region table

    CREATE PROCEDURE insert_region (@region_id INT, @region_name


    VARCHAR(50))
    AS
    BEGIN
    INSERT INTO Continent(region_id, region_name)
    VALUES (@region_id, @region_name)
    END
    exec insert_region 6,'India'
    select * from Continent

    8 Create a stored procedure to display the details of transaction happened in specific


    day

    CREATE PROCEDURE sp_display_transactions (@transactionDate


    DATE)
    AS
    BEGIN
    SELECT *
    FROM [dbo].[Transactions]
    WHERE txn_date = @transactionDate
    END
    exec sp_display_transactions '2020-01-01'

    9 Create a udf to add 10% of transaction amount in a table

    CREATE FUNCTION add_10_percent (@amount decimal(10,2))


    RETURNS decimal(10,2)
    AS
    BEGIN
    DECLARE @new_amount decimal(10,2)
    SET @new_amount = @amount + (@amount * 0.10)
    RETURN @new_amount
    END
    select *, dbo.add_10_percent(txn_amount) as updated_amount from
    [dbo].[Transactions]

    10 Create a udf to find total of transaction amount for given transaction type

    create FUNCTION TotalAmount (@txn_type VARCHAR(20), @txn_date


    date)
    RETURNS DECIMAL(18,2)
    AS
    BEGIN
    DECLARE @result DECIMAL(18,2)
    SELECT @result = SUM(txn_amount)
    FROM [dbo].[Transactions]
    WHERE txn_type = @txn_type and txn_date=@txn_date
    RETURN @result
    END
    select dbo.TotalAmount ('deposit','2020-01-01') as total_amount
    select * from Customers
    select * from Transactions

    11 Create a table values function which comprises of the following columns


    customer_id, region_id , txn_date , txn_type v, txn_amount int which retrieve data from
    the respective table*/

    create FUNCTION MVF(@type varchar(30))


    RETURNS @mvf TABLE
    (
    customer_id int,
    region_id int,
    txn_date date,
    txn_type varchar(30),
    txn_amount int
    )
    AS
    BEGIN
    INSERT INTO @mvf
    select c.customer_id, c.region_id, t.txn_date,t.txn_type, t.txn_amount
    from Customers c
    inner join Transactions t
    on c.customer_id = t.customer_id
    where t.txn_type = @type
    return
    end
    select * from mvf('deposit')
    12 Create a try catch block to print a region id and region name in a single column

    BEGIN TRY
    SELECT region_id + region_name from Continent
    END TRY
    BEGIN CATCH
    SELECT ERROR_STATE() AS ErrorState , ERROR_MESSAGE()
    ErrorMsg ;
    END CATCH;
    GO

    13 Create a try catch block to insert a value in continent table

    BEGIN TRY
    INSERT INTO Continent values(null,'India')
    END TRY
    BEGIN CATCH
    PRINT 'Error: Insert failed.'
    END CATCH
    --14 Create a trigger to prevent deleting of a table in a database
    CREATE TRIGGER tr_prevent_table_drop
    ON DATABASE
    FOR DROP_TABLE
    AS
    BEGIN
    RAISERROR ('Table drop is not allowed.', 16, 1)
    ROLLBACK;
    END;
    drop table Continent

    15 Create a trigger for auditing of data in a table

    create table customer_audit(id int identity(1,1), AuditData varchar(50))


    select * from customer_audit
    CREATE TRIGGER trg_audit
    ON customers
    FOR INSERT
    as begin
    Declare @id int
    select @id = customer_id from inserted
    insert into customer_audit values
    ('New customer with ID = ' + cast(@id as varchar(5)) + ' ' +'is added at')
    end
    insert into Customers values(2003,2,'2001-11-14','2001-11-14')
    drop trigger trg_audit
    select * from customer_audit
    select * from Customers
    16 Create a trigger to prevent login of same user id in multiple page

    select * from sys.dm_exec_sessions order by is_user_process desc


    select is_user_process, original_login_name from
    sys.dm_exec_sessions
    order by is_user_process desc
    create trigger trg_logon
    on all server
    for logon
    as begin
    declare @LoginName varchar(50)
    set @LoginName = ORIGINAL_LOGIN()
    if(select count(*) from sys.dm_exec_sessions where
    is_user_process = 1 and original_login_name = @LoginName) > 3
    begin
    print 'Fourth connection attempt by ' +@loginName + 'Blocked'
    rollback;
    end
    end
    drop trigger trg_logon on all server

    17 Display top n customer on the basis of transaction type

    SELECT * FROM (
    SELECT *,
    DENSE_RANK () OVER (
    PARTITION BY txn_type
    ORDER BY txn_amount DESC
    ) amount_rank
    FROM [dbo].[Transactions]
    )t
    WHERE amount_rank < 5;

    18 Create a pivot table to display the total purchase, withdrawal and deposit for all the
    customers.

    select customer_id, purchase, withdrawal, deposit from


    [dbo].[Transactions]
    PIVOT
    (
    SUM(txn_amount)
    for
    txn_type in ([deposit],[withdrawal],[purchase])
    )
    as PivotTable

    You might also like