Scribd Logo
Upload

Welcome to Scribd!

  • 23 views

    Dbms Assignment 2: 1. Perform Constraints On Particular Field of Tables (Unique, Not Null, In

    Uploaded by

    Abhishek Mishra
    This document contains information about various SQL concepts including constraints, triggers, functions, operators and DDL commands. It discusses constraint types like unique, not null and default values. It describes the different types of triggers - DML, DDL and logon triggers. It explains functions like NVL, COUNT, AVG and SUM. It discusses operators LIKE and BETWEEN. It provides information on packages, grants and revokes in SQL. It also discusses committing and rolling back transactions.

    Copyright:

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

    Dbms Assignment 2: 1. Perform Constraints On Particular Field of Tables (Unique, Not Null, In

    Uploaded by

    Abhishek Mishra
    23 views8 pages
    This document contains information about various SQL concepts including constraints, triggers, functions, operators and DDL commands. It discusses constraint types like unique, not null and default values. It describes the different types of triggers - DML, DDL and logon triggers. It explains functions like NVL, COUNT, AVG and SUM. It discusses operators LIKE and BETWEEN. It provides information on packages, grants and revokes in SQL. It also discusses committing and rolling back transactions.

    Original Title

    DBMS Assignment2

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains information about various SQL concepts including constraints, triggers, functions, operators and DDL commands. It discusses constraint types like unique, not null and default values. It describes the different types of triggers - DML, DDL and logon triggers. It explains functions like NVL, COUNT, AVG and SUM. It discusses operators LIKE and BETWEEN. It provides information on packages, grants and revokes in SQL. It also discusses committing and rolling back transactions.

    Copyright:

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

    Dbms Assignment 2: 1. Perform Constraints On Particular Field of Tables (Unique, Not Null, In

    Uploaded by

    Abhishek Mishra
    This document contains information about various SQL concepts including constraints, triggers, functions, operators and DDL commands. It discusses constraint types like unique, not null and default values. It describes the different types of triggers - DML, DDL and logon triggers. It explains functions like NVL, COUNT, AVG and SUM. It discusses operators LIKE and BETWEEN. It provides information on packages, grants and revokes in SQL. It also discusses committing and rolling back transactions.

    Copyright:

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

    DBMS ASSIGNMENT 2

    1. Perform Constraints on particular field of tables(unique,not null, in,


    Default value)
    ANS. UNIQUE :-

    NOT NULL :-

    DEFAULT VALUE :-

    2. Check constraint
    3. How to use on delete cascade at the time of creating table?
    Ans.

    4. Oracle functions;( nvl() , count(),avg(), sum())


    ANS. Nvl() :-

    Count() :-
    Avg() :-

    Sum() :-

    5.How to apply triggers and types of triggers?


    Ans. Triggers :-

    A trigger is a stored procedure in database which automatically invokes


    whenever a special event in the database occurs.
    For example, a trigger can be invoked when a row is inserted into a specified
    table or when certain table columns are being updated.

    How to apply triggers :-

    Triggers supplement the standard capabilities of oracle to provide a highly


    customized database management system.
    For example, a trigger can restrict DML operation against a table to those
    issued during regular business hours.

    Types of SQL triggers :-

    In SQL Server, There are 3 groups of triggers:

     DML (data manipulation language) triggers – We’ve already


    mentioned them, and they react to DML commands. These are –
    INSERT, UPDATE, and DELETE
     DDL (data definition language) triggers – As expected, triggers of
    this type shall react to DDL commands like – CREATE, ALTER, and
    DROP
     Logon triggers – The name says it all. This type reacts to LOGON
    events

    6 like (% , _) between and operator with example?


    ANS.

    LIKE OPERATOR :-

    BETWEEN OPERATOR :-
    10. Explain Pragma Exception.

    Ans. The PRAGMA is a keyword used to signify that the remainder


    of the pl/sql statement is a pragma, or directive, to the compiler. Tell
    the pl/sql runtime engine to commit or roll back any changes made to
    the database inside the current block without affecting the main or
    outer transaction.

    The pragma exception associates an exception name with an oracle


    error number. You can intercept any ORA- error and write a specific
    handler for it instead of using the others handler.

    11. Explain the package, grant and revoke.


    Ans. PACKAGE :-

    A package is a kind of schema object that groups logically related


    pl/sql type, variables, constants, subprograms, cursors, and
    exceptions. A package is compiled and stored in the database, where
    many application can share its contents.

    GRANT : -

    Grant command is specifically used to provide privileges to database


    object for a user. This command also allows users to grant
    permissions to other users too.
    It assigns access rights to users.

    REVOKE :-

    Revoke command withdraw user privileges on database objects if any


    granted. It does operations opposite to the grant command. When a
    privilege is revoked from a particular user U, then privilege granted to
    all other users by user U will be revoked.

    12. Create the following tables.


    Ans.
    1. Write the query to display all employees working in ‘XYZ’ company.

    2.Write the query to display name of employees whose salary is largest.


    3. Perform Alter and delete, truncate, drop, command.
    Ans.

    Alter and delete :-

    Truncate :-

    Drop :-

    4.Commit and roll back


    14.Delete duplicate row from the table.

    You might also like