SQL Plus Questions

ORACLE QUESTIONS & ANSWERS
Questions	Answers
What is a Database ?	A Database can be one of the two definitions: ·         A set of dictionary tables and user tables that are treated as a unit. ·         One or more operating system files in which ORACLE stores the tables,views,and other objects:also, the set of database objects used by  a given application. ·         A database is a collection of interrelated data that are to be stored in a single location. It enables sharing of data among various users as and when required.
What is a Database system ?	A Database system is a combination of an Instance and a Database. If the instance is started and connected to an open database, then the database is available for access by the users. A DBMS is a software system with capabilities to organise, manipulate and manage the data. Note:-           A DBMS  must be able to reliably manage a large amount     of  data  in  a  multi-user  environment  so  that  many users can concurrently access the same data.            A DBMS must also be secure from unauthorised access and provides eficient solutions for failure recovery.
What is an RDBMS ?	A relational database Mangement System (RDBMS) is a computer program for general purpose data storage and retrieval that organizes data into tables consisting of one or more units of information (rows), each containing the same set of data items  (columns). ORACLE is a relational database management system.
What are the differnt Database models ?	·         Hierarchial. ·         Networking. ·         Relational.
What is SQL ?	·         S.Q.L - Structured Query Language.SQL is the ANSI industry standard language, used to manipulate information in a relational  database and used in ORACLE and IBM DB2 relational database management systems. SQL is formally pronounced “sequel”, although common usage also pronounces it “S.Q.L.” ·         SQL is a set of commands that all programmers must use to access data within the tables of Database.
What are the benefits of SQL ?	1. It is flexible, Powerful and easy to learn. 2. It is a non-procedural language. It     a] Processes set of records rather than just one at a time         and     b] Provides automatic navigation to the data. 3. It provides commands for a variety of tasks including :     a] Querying data     b] Creating,Updating and Replacing objects and   Inserting,         Updating and Deleting rows. 4. All RDBMS supports SQL      Thus one can transfer the skills gained with SQL from one      RDBMS to another. 5. Programs written in SQL are portable, they can often be     moved from one database to another with little modification.
What is SQL*PLUS ?	SQL*PLUS is the ORACLE  database language which includes ANSI standard SQL commands plus additional commands for accessing data in ORACLE database. SQL*PLUS is a Structured Query Language supported by Oracle. Through this only, we store, retrieve, edit, enter & run SQL commands and PL/SQL blocks.             We can perform calculations , list column definitions, format query results in the form of a query.
What is PL/SQL ?	It is a Procedural Language extension of SQL. It can contain any no of SQL statements integrated with flow of control statements. Thus it combine the Data Manipulating power of SQL with data processing power of Procedural language.
What are the different types of SQL commands ?	DDL ( Data definition language ) DML ( Data manipulation language ) TCL  ( Transact control language) Session Control Statements. ( ALTER SESSION, ROLE ) System Control Statements. ( ALTER SYSTEM )
What is A DDL statements?	DDL statements are one category of SQL statements. DDL statements define (create) or delete (drop) database objects.Examples are ceate view, create table, create index,drop table and rename tabl. The other catagories are DML statements and DCL statements.
What is a DML statements ?	DML statements are one catagory of SQL statements. DML statements, such as select, insert, delete and update, query and update the actual data. The other catagories are DDL statements and DCL statements.
What are DCL statements ?	DML statements are one catagory of SQL statements. DCLstatments such as, connect, grant select,grant update and revoke dba, control access to the data and to the database. The other catagories are DDL and DML statements.
What is a Transaction ?	·         It can be defined as a logical unit of work. ·         A transaction is a sequence of SQL statements that ORACLE treats as a single unit. The set of statements is made permanent  with the  COMMIT statement. Part or all of a transaction can de undone with the ROLLBACK statement. ·         All changes to the database between successive COMMITS and / or ROLLBACK operations are called a transaction.
What is a Commit ?	·         COMMIT commits any changes made to the database since the last COMMIT was executed implicitly or explicitly. WORK is optional and has no effect on usage. ·         To COMMIT means to make changes to data (inserts,updates and deletes) permanent. before changes are stored both the old and new data exists so that changes can be made, or so that the data can be restored  to its prior state.(“rollback”). When a user enters the ORACLE SQL Command COMMIT, all changes from that transaction are made permanent. ·         To end a transaction and make permanent all changes performed in the transaction. This command also erases all Savepoints in the transaction and release the transaction locks
What is a Rollback ?	·         A ROLLBACK discards part or all of the work you have done in the current transaction, since the last COMMIT or SAVEPOINT. ·         To undo work done in current tranaction.
What is a DEAD LOCK ?	A DEAD lock  is  a  rare  situation  in  which  two  or  more user processes of a database cannot complete theirtransactions.This occurs because each process is holding  a resource that the other process requires  (such as a row in a table)  in order to complete.Although these situations occur rarely, ORACLE detects and resolves deadlocks by rolling back the work of one of the processes.
What are INTEGRITY CONSTRAINTS ?	INTEGRITY CONSTRAINT is a rule that restricts the range of valid values for a column, it is placed on a column when the table is created.
What is REFERENTIAL INTEGRITY ?	REFERENTIAL INTEGRITY is the property that guarantees that values from one column depend on values from another column. This property is enforced through integrity constraints.
What is a PRIMARY KEY ?	The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.
What is a FOREIGN KEY ?	A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.
What is a UNIQUE KEY ?	A UNIQUE KEY is one or more columns that must be unique for each row of the table.
What is the difference between UNIQUE and PRIMARY KEY ?	The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as  NULL value is restricted.
What is a SEQUENCE ?	A SEQUENCE is a database object used to generate UNIQUE INTEGERS  for use as PRIMARY KEYS.
What is a VIEW ?	A View is a database object  that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.
What is a SYNONYM ?	A SYNONYM  is a name assigned to a table or view that may thereafter be used to refer it. If you access to another user’s table, you may create a synonym for it and refer to it by the synonym alone, without entering the user’s name as a qualifier.
What is INDEX ?	INDEX is a general term for an ORACLE / SQL feature used primarily to speed execution an impose UNIQUENESS  upon certain data. INDEX provides a faster access method to one table’s data than doing a full table scan. There are several types of Indexes : UNIQUE INDEX, COMPRESSED INDEX, CONCATENATED  INDEX. An Index has an entry for each value found in the table’s Indexed field(s) ( except those with a NULL value ) and pointer(s) to the rows having that value.
What is an UNIQUE INDEX ?	An UNIQUE INDEX  is an index that imposes uniqueness on each value in indexes. The index may be one column or concatenated columns.
What is a COMPRESSED INDEX ?	A COMPRESSED INDEX is an index for which only enough index information is stored to identify unique entries; information that an index stores with the previous or following key is “compressed” (truncated)  and not stored to reduce the storage overhead required by an index.
What is CONCATENATED INDEX  or KEY?	A CONCATENATED INDEX  is one that is created on more than one column of a table. It can be used to guarantee that those columns  are unique for every row in the table and to speed access to rows via those columns
What are CLUSTERS ?	A CLUSTER is a means of storing together data from multiple tables, when the data in those tables contains information and is likely to be accessed concurrently.
What is CLUSTER KEY  or CLUSTER COLUMNS ?	A CLUSTER KEY is the column or columns that cluster tables have in common, and which is chosen as the storage / access key. For example two tables, WORKER and WORKERSKILL, might be clustered on the column name. A cluster key is the same thing as a cluster column.
What is CLUSTER INDEX ?	A CLUSTER INDEX  is one manually created after a cluster has been created and before any DML ( that is SELECT, INSERT, UPDATE AND DELETE )statements can operate on the cluster. This index is created on the CLUSTER KEY columns with the SQL statement CREATE INDEX. In ORACLE 7, you can define a hash cluster to index on the primary key.
What are EXCEPTIONS ?	Exceptions are the error handling routines of PL/SQL.The EXCEPTION section of a PL/SQL block is where program control is transfered whenever an exception flag is raised. Exception flags are either user-defined or system exceptions raised automatically by PL/SQL.
What are CURSORS ?	Cursor has two definitions : ·         A cursor is a marker such as a blinking square or line, that marks your current position on a CRT screen. ·         Cursor is also a synonym for context area - a work area in memory where ORACLE stores the current SQL statement. For a query , the area in memory also includes column headings and one row retrieved by the SELECT statement.
What is NULL  ?	A NULL value is one that is unknown, irrelevant, or not meaningful. Any ORACLE data type can be NULL. NULL in a number data type is not the same as zero. The default value for a field in ORACLE is NULL.
What is EXPRESSION ?	An expression is any form of a column. This could be a literal, a variable, a mathematical computation, a function, or virtually any combination of functions and columns whose final result is a single value, such as a string, a number, or a value.
What is a CONDITION ?	A Condition is an expression whose value evaluates to either TRUE or FALSE, such as AGE > 16.
CLIENT	A Client or Front End database application acts as an interface between the user and the Database. It also checks for validation against the data entered by the user. CLIENT is a general term for a user , software application, or computer that requires the services, data, or processing of another application or computer.
SERVER	A Database server or Back End is used to manage the Database tables optimally among multiple clients who concurrently request the server for the same data. It also enforces data integrity across all client applications and controls database access and other security requirements. SERVER system is the configuration of the ORACLE when a remote user accesses ORACLE via SQL*NET.
What is a SESSION ?	A SESSION is a sequence of events that happens between the time a  user connects to SQL and the time  he or she disconnects.
What is an INSTANCE ?	An INSTANCE is everything required for ORACLE to run: backround processes (programs), memory, and so on. An INSTANCE  is the means of accessing a database.
What is SYSTEM USERID ? What does it have ?	SYSTEM is one of the DBA  users that is created when the database system is installed and initialized ( the other is SYS ). While SYS owns most of the data dictionary tables, SYSTEM owns the views created on those base tables.
What is SYS USERID ? What does it have ?	SYS is one of the DBA  users that is created when the database system is installed and initialized ( the other is SYSTEM ). SYS owns most of the data dictionary tables, SYSTEM owns the views created on those base tables.
What is a Datadictionary  in ORACLE ?	The DATA DICTIONARY is a comprehensive set of tables and views owned by the DBA users SYS and SYSTEM, which activates when ORACLE is initially installed, and is a cental source of information for the ORACLE RDBMS  itself and for all users of ORACLE. The tables are automatically maintained by ORACLE, and holds a set of views and tables containing information about the database  objects, users, privileges, events, and use.
What is Sqldba ?	SQL * DBA  is an ORACLE utility used by DBAs while performing database maintenance and monitoring.
What are Database files ?	A DATABASE file is simply any file used in a database. A database is made up of one or more tablespaces, which in turn are made up of one or more database files.
What does a INSERT statement do ?	INSERT adds one or more new rows to the table or view.
What does an UPDATE statement do ?	Updates (changes) the values in the listed columns in the specified table.
What does a DELETE statement do ?	DELETE  deletes all rows that satisfy condition from table.
What does a SELECT  statement do ?	SELECT retrieves rows from one or more tables  ( or views or snapshots ), either as a command, or as a subquery in another SQL command (with limitations), including SELECT,INSERT,UPDATE and DELETE. ALL means that all rows satisfying the conditions will be returned ( this is the default ). DISTINCT means that only rows that are unique will be returned: any duplicates will be weeded out first.
What is  Startup and Shutdown ?	STARTUP is the process of starting an instance, presumably with the intent of mounting and opening a database in order to make a database system available for use. To SHUTDOWN  is to disconnect an instance from the database and terminate the instance.
What is Mounting of database ?	To MOUNT  a database is to make it available to the database administrator.
What is Two Phase - Commit ?	ORACLE7 manages distributed transactions with a special feature called TWO PHASE - COMMIT. TWO PHASE - COMMIT  guarantees that a transaction is valid at all sites by the time it commits or roll back. All sites either commit or rollback together, no matter what errors occur in the network or on the machines tied together by the network. You don’t need to do anything special to have your applications use a TWO PHASE - COMMIT.
What are Triggers ?	A DATABASE TRIGGER is a stored procedure associated with a table that ORACLE7 automatically executes on one or more specified events (BEFORE or AFTER an INSERT,UPDATE or DELETE) affecting the table. Triggers can execute for the table as a whole or for each affected row in the table.
What are Packages ?	A PACKAGE is a PL/SQL object that groups PL/SQL  types, variables, SQL cursors, exceptions,procedures, and functions.Each package has a specification and a body. The specification shows the object you can access when you use the package. The body fully defines all the objects and can contain additional objects used only for the internal workings. You can change the body (for example, by adding procedures to the packages) without invalidating any object that uses the package.
What are Pseudo Columns in ORACLE ?	A PSEUDO COLUMN is a “column”  that yields a value when selected, but which is not an actual column of the table. An example is ROWID or SYSDATE.
What is a Schema  ?	A SCHEMA is a collection of objects. SCHEMA objects are logical structures that directly refer to the database’s data. SCHEMA objects include structures such as tables, views, synonyms, sequences, indexes, clusters, stored procedures and data links.
What are the major aspects of the Relational Database Management System ?	The Relational model has three major aspects: Structures : Structures are well-defined objects that store the data of the database. Structures and the data contained within them can be manipulated by operations. Operations : Operations are clearly defined actions that allow the user to manipulate the data and structure of the database. The operation on a database must adhere to a pre-defined set of integrity rules. Integrity rules : Integrity rules are the laws that govern which operations are allowed on the data and structure of  a database. Integrity rules protect the data and the structures of a database.
What are the benefits of Relational Database Management System ?	RDBMS offers benefits such as : 1] Independence of physical data storage and logical     database structure. 2] variable and easy access to all data. 3] Complete flexibility in database design. 4] Reduced data storage and redundancy.
What is a Database Structure ?	An ORACLE  database structure has both a physical and logical structure. Physical database structure : An ORACLE  database physical structure is determined by the operating system files that constitute the database. Each ORACLE database is comprised of three types of files: one or more data files, two or more redolog files, and one or more control files. The files of  a database provide the actual physical storage of the database information. Logical database structure: An ORACLE  database’s logical structure is determined by ·         One or more tablespaces. ·         The database’s schema objects (e.g. tables, views, indexes, clusters, sequences,and stored procedures ) The logical storage structures,  including tablespaces, segments, and extents, dictate how the physical space of a database is used. the schema objects and the relationships among them form the relational design of the database.