Oracle EMP,DEPT tables with data

CREATE TABLE EMP
    (
        EMPNO        INT            NOT NULL    ,
        ENAME        VARCHAR(10)                ,
        JOB            VARCHAR(9)                ,
        MGR            INT                        ,
        SAL            NUMERIC(7,2)                ,
        DEPTNO        INT
    );
   
CREATE TABLE DEPT
        (
        DEPTNO    INT,
        DNAME    VARCHAR(14),
        LOC        VARCHAR(13)
        )
   

CREATE TABLE BONUS
(
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    SAL NUMERIC(8,2),
    COMM NUMERIC(8,2)
)

CREATE TABLE SALGRADE
(
    GRADE INT,
    LOSAL NUMERIC(8,2),
    HISAL NUMERIC(8,2)
)


        INSERT INTO emp VALUES
            (7369, 'SMITH', 'CLERK', 7902, 800, 20);
        INSERT INTO EMP VALUES
            (7499, 'ALLEN', 'SALESMAN', 7698, 1600, 30);
        INSERT INTO EMP VALUES
            (7521, 'WARD', 'SALESMAN', 7698, 1250, 30);
        INSERT INTO EMP VALUES
            (7566, 'JONES', 'MANAGER', 7839, 2975, 20);
        INSERT INTO EMP VALUES
            (7654, 'MARTIN', 'SALESMAN', 7698, 1250, 30);
        INSERT INTO EMP VALUES
            (7698, 'BLAKE', 'MANAGER', 7839, 2850, 30);
        INSERT INTO EMP VALUES
            (7782, 'CLARK', 'MANAGER', 7839, 2450, 10);
        INSERT INTO EMP VALUES
            (7788, 'SCOTT', 'ANALYST', 7566, 3000, 20);
        INSERT INTO EMP VALUES
            (7839, 'KING', 'PRESIDENT', NULL, 5000, 10);
        INSERT INTO EMP VALUES
            (7844, 'TURNER', 'SALESMAN', 7698, 1500, 30);
        INSERT INTO EMP VALUES
            (7876, 'ADAMS', 'CLERK', 7788, 1100, 20);
        INSERT INTO EMP VALUES
            (7900, 'JAMES', 'CLERK', 7698, 950, 30);
        INSERT INTO EMP VALUES
            (7902, 'FORD', 'ANALYST', 7566, 3000, 60);
        INSERT INTO EMP VALUES
            (7934, 'MILLER', 'CLERK', 7782, 1300, 10);
           




        INSERT INTO DEPT
            VALUES (10, 'ACCOUNTING', 'NEW YORK')
        INSERT INTO DEPT
            VALUES (20, 'RESEARCH', 'DALLAS')
        INSERT INTO DEPT
            VALUES (30, 'SALES', 'CHICAGO')
        INSERT INTO DEPT
            VALUES (40, 'OPERATIONS', 'BOSTON')




        INSERT INTO SALGRADE
                VALUES (1, 700,  1200)
        INSERT INTO SALGRADE
                VALUES (2, 1201, 1400)
        INSERT INTO SALGRADE
                VALUES (3, 1401, 2000)
        INSERT INTO SALGRADE
                VALUES (4, 2001, 3000)
        INSERT INTO SALGRADE
                VALUES (5, 3001, 9999)

1.List out all details of employees

2.List out all details of employees who earn more than 2000

3.List out employee names and their manager ids of all employees

4.List out all the employee ids and their department nos who are working in department 20

5.List out all details of ALLEN

6.List out all details of MARTIN and LUTHER

List out all employee details who"s name starts with S

List out employee details who"s name ends with G

List out employee details who"s name contains LL

List out employee details who"s name starts with A and ends with N

7.List out department 10 and depatment 20 employee details

8.List out all the employees who work in SCOTT"s department

9.List out all details of employees who earn more than MARTIN

10.Details of Employees Under KING"S manager

11.List out all details of employees and theire managers

12.List out all the employee details who earn more than his manager

13.List out departments and their employee count

14.List out each departments avg salary

15.List out Designation wise employee count

16.List out managers and total employees under each manager

17.List out departments and their maximum salaries

18.List out department details which is giving the maximum salary

19.List out the department details where there are more employees than all

20.List out department details where there are no employees in it

21.List out all employees and thier salgrades

22.List out Grade wise total employees

SP to Compare two databases

DROP PROCEDURE dbo.USP_CompareDB
GO
CREATE PROCEDURE [dbo].[USP_CompareDB]
                            @dbName_Main NVARCHAR(100) ,
                            @dbName_Sub NVARCHAR(100) ,
                            @table_list NVARCHAR(MAX) = NULL
AS BEGIN

--  exec USP_CompareDB    'DBName','DBName_Prod_v','Table_1,Table_2'
--    ====================================================Header=======================================================
--    Name        :    [USP_CompareDB]
--    Purpose        :    Procedure USP_CompareDB is useful for comparing two Similar databases
--                                ** The first database is refered to as the Main Database and the
--                                        second database is refered to as the Sub Database
-- ------------------------------------------------------------------------------------------------------------------
--    Orig Auth    :    Vasudeva Varma Revu
--    Orig Date    :    Tue, May 25 2010
-- ------------------------------------------------------------------------------------------------------------------
--    Arguments      :
--            @dbName_Main    -- Name of the main database with reference to which the comparision should go on
--            @dbName_Sub        -- Name of the sub database with respect to
--            @table_list        -- List of tables if any or else NULL
--                         ( if given NULL all the tables in the specified database is considered)
-- ------------------------------------------------------------------------------------------------------------------
--    Return Value   
--                Results a table with the info of a table_name,column_name and
--                            the change regarding that column as reason
-- ------------------------------------------------------------------------------------------------------------------

       
    -- Initially verifying the existence of databases  and then proceeding furthur
    IF EXISTS
        (
            SELECT        1        FROM    sys.databases
            WHERE   
                Name like @dbName_Main
        )
    BEGIN
        IF EXISTS
            (
                SELECT        1        FROM    sys.databases
                WHERE   
                    Name like @dbName_Sub
            )
        BEGIN
            DECLARE @mainDB_columnsInfo TABLE(Table_Name NVARCHAR(200),
                                              Column_Name NVARCHAR(200),
                                              Is_Nullable NVARCHAR(50),
                                              Data_Type NVARCHAR(100),
                                              VStatus INT)

            DECLARE @subDB_columnsInfo TABLE(Table_Name NVARCHAR(200),
                                            Column_Name NVARCHAR(200),
                                            Is_Nullable NVARCHAR(50),
                                            Data_Type NVARCHAR(100),
                                            VStatus INT)

            DECLARE @dbMain_constraint_list TABLE(Table_Name NVARCHAR(200),
                                              Column_Name NVARCHAR(200),
                                              Constraint_Name NVARCHAR(300),
                                              Constraint_Type NVARCHAR(100))

            DECLARE @dbSub_constraint_list TABLE(Table_Name NVARCHAR(200),
                                              Column_Name NVARCHAR(200),
                                              Constraint_Name NVARCHAR(300),
                                              Constraint_Type NVARCHAR(100))

            DECLARE @resTB TABLE(Table_Name NVARCHAR(200),
                                 Column_Name NVARCHAR(200),Reason NVARCHAR(300))

            DECLARE @tablename NVARCHAR(200),
                    @columnname NVARCHAR(200),
                    @isnullable NVARCHAR(50),
                    @datatype NVARCHAR(100),
                    @db_constraint_type1 NVARCHAR(100) ,
                    @db_constraint_type2 NVARCHAR(100),
                    @db_constraint_name NVARCHAR(500)


            IF @table_list is null
            BEGIN
                -- Verifying the table list if table list is not given then performing the operation for  all the tables
                SELECT @table_list =  COALESCE(
                    @table_list + ',''' +
                    CAST(Name AS VARCHAR) + '''', '''' +
                    CAST(Name AS VARCHAR)+ '''' )
                FROM sys.tables
            END
            ELSE
            BEGIN
                SET @table_list = REPLACE(QUOTENAME(REPLACE(@table_list,',',''','''),CHAR(39)),'''''','''')
            END

            SET @table_list = ' and table_name in ('+@table_list +')'           

            -- Extracting the databases info all columns info including the nullable field and datatype

            -- Main database column information
            INSERT INTO @mainDB_columnsInfo
            EXEC('SELECT    TABLE_NAME,        COLUMN_NAME,    IS_NULLABLE,        DATA_TYPE,        0
                  FROM        '+@dbName_Main+'.INFORMATION_SCHEMA.COLUMNS
                  WHERE            TABLE_NAME NOT LIKE ''SYSDIAGRAMS''
                            ' + @table_list)

            -- Sub database columns information
            INSERT INTO @subDB_columnsInfo
            EXEC('SELECT    TABLE_NAME,        COLUMN_NAME,    IS_NULLABLE,         DATA_TYPE,        0
                  FROM        '+@dbName_Sub+'.INFORMATION_SCHEMA.COLUMNS
                  WHERE        TABLE_NAME NOT LIKE ''SYSDIAGRAMS''
                            ' + @table_list )

            --    Main database constraints and their details  (on what column and for what table they are defined )
            INSERT INTO @dbmain_constraint_list 
            EXEC(' SELECT    CUSG.TABLE_NAME,    CUSG.COLUMN_NAME,    TCON.CONSTRAINT_NAME,     TCON.CONSTRAINT_TYPE
                   FROM        '+@dbName_Main+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS    TCON    INNER JOIN
                              '+@dbName_Main+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE        CUSG
                            ON        TCON.CONSTRAINT_NAME = CUSG.CONSTRAINT_NAME ')

            --    Sub database constraints and their details  (on what column and for what table they are defined )           
            INSERT INTO @dbsub_constraint_list
            EXEC(' SELECT    CUSG.TABLE_NAME,    CUSG.COLUMN_NAME,    TCON.CONSTRAINT_NAME,    TCON.CONSTRAINT_TYPE
                   FROM     '+@dbName_Sub+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS    TCON    INNER JOIN
                            '+@dbName_Sub+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE     CUSG
                            ON        TCON.CONSTRAINT_NAME = CUSG.CONSTRAINT_NAME    ')

            -- Traversing each and every column of an individual table from the main database
            DECLARE TC CURSOR FOR SELECT    Table_Name,        Column_Name,    Is_Nullable,    Data_Type
                                  FROM        @mainDB_columnsInfo
            OPEN TC
            FETCH TC INTO @tablename ,@columnname ,@isnullable ,@datatype
            WHILE @@FETCH_STATUS = 0
            BEGIN

                SET @db_constraint_type1 = NULL
                SET @db_constraint_type2 = NULL
               
                --    Verifying the existance of table and a column combination in the sub database
                IF(NOT EXISTS(
                            SELECT        *            FROM    @subDB_columnsInfo
                            WHERE TABLE_NAME = @tablename AND      COLUMN_NAME = @columnname))
                BEGIN
                        -- If the column for that table does not exists then inserting a record into the resTB with below reason
                        INSERT INTO @resTB 
                        (Table_Name,    Column_Name,    Reason)                 VALUES
                        (@tablename,    @columnname,    ' COLUMN for the specified TABLE doesn''t EXISTS in SUB_DATABASE')
                END
                ELSE
                    BEGIN
                        -- The column for that table exists so verifying the datatype specifications
                        IF(NOT EXISTS(
                                    SELECT        *        FROM         @subDB_columnsInfo
                                    WHERE TABLE_NAME = @tablename        AND
                                          COLUMN_NAME = @columnname AND DATA_TYPE = @datatype))
                        BEGIN
                            -- if datatype mismatches for that table-column then inserting into resTB with the below reason
                            INSERT INTO @resTB
                            (Table_Name,    Column_Name,    Reason )        VALUES
                            (@tablename,    @columnname,    ' DATATYPE MISMATCH for the COLUMN of this TABLE in SUB_DATABASE ')
                        END
                       
                        -- Consequently verifying the IS_NULLABLE field for the table-column
                        IF(NOT EXISTS(
                                    SELECT        *        FROM        @subDB_columnsInfo
                                    WHERE TABLE_NAME = @tablename AND
                                          COLUMN_NAME = @columnname AND IS_NULLABLE = @isnullable))
                        BEGIN
                            -- If IS_NULLABLE property mis matches then inserting a record into the resTB with below reason
                            INSERT INTO @resTB
                            (Table_Name,    Column_Name,    Reason)        VALUES
                            (@tablename,    @columnname,    'IS_NULLABLE PROPERTY MISMATCH for the specified TABLE-COLUMN combination')
                        END
                            -- Updating the subDB columns information so that they are visited once
                            UPDATE        @subDB_columnsInfo   
                            SET            VSTATUS = 1
                            WHERE        TABLE_NAME = @tablename AND
                                        COLUMN_NAME = @columnname
                    END       

               
                -- Listing out the constraints list of individual table and verifying them

                DECLARE CC CURSOR FOR SELECT    CONSTRAINT_NAME,    CONSTRAINT_TYPE           
                                      FROM        @dbmain_constraint_list
                                      WHERE        COLUMN_NAME = @columnname    AND
                                                    TABLE_NAME = @tablename
                OPEN CC
                FETCH NEXT FROM CC INTO @db_constraint_name,@db_constraint_type1
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    -- Verifying the constraint for the same table and column combination
                    IF     NOT EXISTS(   
                                SELECT        *        FROM        @dbSub_constraint_list
                                WHERE    COLUMN_NAME = @columnname        AND
                                        TABLE_NAME = @tablename            AND
                                        CONSTRAINT_TYPE = @db_constraint_type1  )
                    -- if the specified constraint does not exists for the same table and column combination then
                    -- inserting into the resTB a record with the
                    BEGIN
                        INSERT INTO @resTB
                        (Table_Name,    Column_Name,    Reason )     VALUES
                        (@tablename,    @columnname,    'CONSTRAINT "'+@db_constraint_name + '"  not defined on COLUMN in SUB DATABASE ')
                    END

                FETCH NEXT FROM CC INTO @db_constraint_name,@db_constraint_type1
                END
                CLOSE CC
                DEALLOCATE CC

            FETCH TC INTO @tablename ,@columnname,@isnullable,@datatype
            END
            CLOSE TC
            DEALLOCATE TC

            -- Inserting those list of records from the sub database which have not been touched
            -- That implies the columns list is explicitly in sub database but not in main database 
            INSERT INTO @resTB
                  (Table_Name,    Column_Name,    Reason )   
            SELECT TABLE_NAME,    COLUMN_NAME,    'COLUMN not present in MAIN DATABASE but present in SUB DATABASE'
            FROM @subDB_columnsInfo
            WHERE VSTATUS = 0

            -- Resultant table with contains all the defects and their reasons after comparing those two database
            IF EXISTS (        SELECT        *        FROM    @resTB        )
                BEGIN
                                SELECT        Table_Name,        Column_Name,        Reason
                                FROM        @resTB
                                ORDER BY    TABLE_NAME,        REASON
                END
            ELSE
                BEGIN
                    SELECT ' Both the databases Match at every instance '    as Result_Message
                END           

        END -- END OF DB_SUB CHECK IF BLOCK

    ELSE
        BEGIN
            SELECT        'Database " ' + @dbName_Sub +'"  does not Exists ' AS ErrorMessage
        END       
    END -- END OF DB_MAIN CHECK IF BLOCK
    ELSE
            SELECT        'Database  "' + @dbName_Main +'"  does not Exists' AS ErrorMessage

END

Search database objects for a string literal

        /*************************************************************** */
                --                    GOOGLE Jr..                --
        /***************************************************************  *//*
    Description :: Script is mend to search a string literal given and give the
                    dependencies in the database
    Author        ::    R.V.D.Varma

    Scripted Date :: 20 May 2010 Wed
    Inputs ::        iString_Literal         ---------->     Literal which is to be searched
                    iObjects_type         ---------->     Type of Objects in which String Literal is to be
                                                            searched as specified
                                1..... Stored Procedures / Functions
                                2..... Views
                                3..... Tables/Columns   
                                4..... Object Not Known
*/

SET NOCOUNT ON

    DECLARE @iString_Literal NVARCHAR(1000)
    DECLARE @iObjects_type INT
    DECLARE @iSearch_Objects NVARCHAR(1000)
    DECLARE @vObject_type NVARCHAR(500)
    DECLARE @vObject_Name NVARCHAR(1000)

    DECLARE @iResTable TABLE (Attribute_Type NVARCHAR(200),
                         Attribute_Description NVARCHAR(MAX))

    DECLARE @vObjectInfo TABLE([ObjID] INT IDENTITY(1000,1),
                                Object_Text NVARCHAR(MAX))

    SET @iString_Literal = 'TWITTER_COST_DATA'
    SET @iObjects_type = 4

    IF @iObjects_type = 1 
        BEGIN
            DECLARE tc CURSOR FOR SELECT TYPE_DESC,NAME
                                    FROM sys.OBJECTS
                                    WHERE TYPE_DESC IN ('SQL_STORED_PROCEDURE','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
            OPEN tc
            FETCH NEXT FROM tc INTO @vObject_type,@vObject_Name
            WHILE @@FETCH_STATUS = 0
            BEGIN
                    INSERT INTO @vObjectInfo
                    EXEC sp_helptext @vObject_Name
                   
                    IF EXISTS(SELECT * FROM @vObjectInfo WHERE CHARINDEX(@iString_Literal,Object_Text,1)>0 )
                            INSERT INTO @iResTable (Attribute_Type,Attribute_Description)
                                    VALUES (@vObject_type,@vObject_Name)
   
                DELETE FROM @vObjectInfo
                FETCH NEXT FROM tc INTO @vObject_type,@vObject_Name

            END
            CLOSE tc
            DEALLOCATE tc
        END
    ELSE IF @iObjects_type = 2
        BEGIN
            DECLARE tc CURSOR FOR SELECT TYPE_DESC,NAME FROM sys.VIEWS
            OPEN tc
            FETCH NEXT FROM tc INTO @vObject_type,@vObject_Name
            WHILE @@FETCH_STATUS = 0
            BEGIN
                    INSERT INTO @vObjectInfo
                    EXEC sp_helptext @vObject_Name
                   
                    IF EXISTS(SELECT * FROM @vObjectInfo WHERE CHARINDEX(@iString_Literal,Object_Text,1)>0 )
                            INSERT INTO @iResTable (Attribute_Type,Attribute_Description)
                                    VALUES (@vObject_type,@vObject_Name)
   
                DELETE FROM @vObjectInfo
                FETCH NEXT FROM tc INTO @vObject_type,@vObject_Name
            END   
            CLOSE tc
            DEALLOCATE tc
        END
    ELSE IF @iObjects_type = 3
        BEGIN
            INSERT INTO @iResTable
            SELECT DISTINCT 'USER_TABLE',OBJECT_NAME(OBJECT_ID) FROM sys.columns
                                        WHERE NAME LIKE @iString_Literal
        END    
    ELSE IF @iObjects_type = 4
        BEGIN
            DECLARE tc CURSOR FOR SELECT TYPE_DESC,NAME FROM sys.OBJECTS WHERE TYPE_DESC IN ('SQL_STORED_PROCEDURE','VIEW','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
            OPEN tc
            FETCH NEXT FROM tc INTO @vObject_type,@vObject_Name
            WHILE @@FETCH_STATUS = 0
            BEGIN
                    INSERT INTO @vObjectInfo
                    EXEC sp_helptext @vObject_Name
                   
                    IF EXISTS(SELECT * FROM @vObjectInfo WHERE CHARINDEX(@iString_Literal,Object_Text,1)>0 )
                            INSERT INTO @iResTable (Attribute_Type,Attribute_Description)
                                    VALUES (@vObject_type,@vObject_Name)
   
                DELETE FROM @vObjectInfo
                FETCH NEXT FROM tc INTO @vObject_type,@vObject_Name
            END
            CLOSE tc
            DEALLOCATE tc   

            INSERT INTO @iResTable
            SELECT DISTINCT 'USER_TABLE',OBJECT_NAME(OBJECT_ID) FROM sys.columns
                                        WHERE NAME LIKE @iString_Literal
        END
SELECT * FROM @iResTable

SET NOCOUNT OFF