Tolal:174 Click:
1
2 3 4 5 6 7 8 9
SQL Interview Questions And Answers
Page 1
Ques: 1 What are the difference between DDL, DML and DCL commands?
Ans:
SQL can be divided into two parts:
> The Data Manipulation Language (DML)
> The Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
> SELECT - extracts data from a database
> UPDATE - updates data in a database
> DELETE - deletes data from a database
> INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:
> CREATE DATABASE - creates a new database
> ALTER DATABASE - modifies a database
> CREATE TABLE - creates a new table
> ALTER TABLE - modifies a table
> DROP TABLE - deletes a table
> CREATE INDEX - creates an index (search key)
> DROP INDEX - deletes an index
Data Control Language (DCL) statements. Some examples:
> GRANT - gives user's access privileges to database
> REVOKE - withdraw access privileges given with the GRANT command
Ques: 2 Which one is faster DELETE/TRUNCATE?
Ans:
Truncante is more faster than delete because when we delete the records from the database, database has to perform 2 actions.
> Delete from the database
> Write the deleted records into "rollback"
segments. But incase of "Truncate" the second activity is not required.It is faster than becouse truncate is a ddl command so it does not produce any rollback information and the storage space is released while the delete command is a dml command and it produces rollback information too and space is not deallocated using delete command.
Ques: 3 What is RDBMS?
Ans:
RDBMS stands for Relational Database Management
System. RDBMS is the basis for SQL, and for all
modern database systems like MS SQL Server, IBM
DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects
called tables. A table is a collections of
related data entries and it consists of columns
and rows.Relational Data Base Management Systems
(RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
Ques: 4 What are different normalization forms?
Ans:
normalisation have a se veral forms that a database structure can be subject to, each with rules that constrain the database further and each creating what is called a Normal Form. These are, in order:
> First Normal Form (1NF)
> Second Normal Form (2NF)
> Third Normal Form (3NF)
> Boyce Codd Normal Form (BCNF)
> Fourth Normal Form (4NF)
> Fifth Normal Form (5NF)
> Optimal Normal Form (ONF)
> Domain-Key Normal Form (DKNF)
Theses are defined as :
> 1NF: Eliminate Repeating Groups : Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
> 2NF: Eliminate Redundant Data : If an attribute depends on only part of a multi-valued key, remove it to a separate table.
> 3NF: Eliminate Columns Not Dependent On Key : If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
> BCNF: Boyce-Codd Normal Form : If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
> 4NF: Isolate Independent Multiple Relationships : No table may contain two or more 1:n or n:m relationships that are not directly related.
> 5NF: Isolate Semantically Related Multiple : Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships.
> ONF: Optimal Normal Form : A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
> DKNF: Domain-Key Normal Form : A model free from all modification anomalies.
Ques: 5 What is normalization?
Ans:
Normalisation is a process whish is the tables in a database are optimised to remove the potential for redundancy. Two main problems may arise if this is not done:
> Repeated data makes a database bigger.
> Multiple instances of the same values make
maintaining the data more difficult and can
create anomalies.
Ques: 6 What is Stored Procedure
Ans:
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc
Ques: 7 What is Trigger?
Ans:
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
Ques: 8 What is View?
Ans:
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
Ques: 9 What is Index?
Ans:
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application
. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
Ques: 10 What is SQL?
Ans:
SQL is a bassically standard language for accessing and manipulating databases. Its is defines as :
> SQL stands for Structured Query Language
> SQL lets you access and manipulate databases
> SQL is an ANSI (American National Standards Institute) standard.
SQL have a many properties, It can do :
> SQL can execute queries against a database.
> SQL can retrieve data from a database.
> SQL can insert records in a database.
> SQL can update records in a database.
> SQL can delete records from a database.
> SQL can create new databases.
> SQL can create new tables in a database.
> SQL can create stored procedures in a database.
> SQL can create views in a database.
> SQL can set permissions on tables, procedures,
and views.
Ques: 11 What are the components of physical database structure of Oracle database?
Ans:
Physical components of oracle database are:
> Control files,
> Redo log files and
> Datafiles.
> Control file : control file is basicaly a type of file .Its read in the mount state of database. control file is a small binary file which records the physical structure of database which includes
* Database name
* names and locations of datafiles and online
redo log files.
* timestamp of database creation
* check point information
* current log sequence number.
Redo log files : Redo log files usually use for the saving to the files. This files saves all the changes that are made to the database as they occur. This plays a great role in the database recovery.
Datafiles : Datafiles are the physicalfiles which stores data of all logical structure.
Ques: 12 What are the components of logical database structure of Oracle database?
Ans:
The Logical Database Structure of an Oracle include :
> The schema objects,
> Data blocks,
> Extents,
> Segments and
> Tablespaces.
> schema objects : means table, index, synonyms, sequences etc.
> Data blocks : are the smallest part of the oracle database defined by DB_BLOCK_SIZE parameter.
> Extent : A group of data blocks forms an extent.
> Segments : An extents groups tends to segments,
> Table space : lastly a group of segment forms a tablespace.
Ques: 13 What is a tablespace?
Ans:
A Tablespace is bassically a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one data file which are physically located in the file system of the server. b/w datafile belongs to exactly one tablespace. Each table index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored. There are three types of tablespaces in Oracle:
> Permanent tablespaces
> Undo tablespaces
> temporary tablespaces
A tablespace is created with the create tablespace sql command.
Ques: 14 What is SYSTEM tablespace and when is it created?
Ans:
Every ORACLE database have a table space which name is SYSTEM . when the database is created taht a time its also automatically created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
Ques: 15 Explain the relationship among database, tablespace and data file.
Ans:
Database : Database is the Collection of data is called database.
Table Space : The table space ismany use for storing the data in the database. When a database is created two table spaces are created.
i) System Table Space : This data file stores
all the tables related to the system and dba tables.
ii) User Table Space : This data file stores all the user related tables. We should have separate table spaces for storing the tables and indexes so that the access is fast.
Data Files : Every Oracle Data Base has one or more physical data files. They store the data for the database. Every data-file is associated with only one database. Once the Data file is created the size cannot change. To increase the size of the database to store more data we have to add data file.
Ques: 16 What is schema?
Ans:
Schema bassically Pronounce skee-ma, It is the structure of a database system,It described in a formal language supported by the database management system (DBMS). In the RDBMS (Relational database System) , the schema defines the tables, the fields in each table, and the Schemas are generally stored in a data dictionary . Even a schema is defined in text database language , the term is often used to refer to a graphical depiction of the database structure.
Ques: 17 What are Schema Objects?
Ans:
Schema is bassically a Associated with each database user . It is a collection of schema objects. Examples of schema objects include tables, views, sequences, synonyms, indexes, clusters, database links, snapshots, procedures, functions, and packages. Schema objects are logical data storage structures. Schema objects don't have a one-to-one correspondence to physical files on disk that store their information. Even Oracle stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles.
Ques: 18 Can objects of the same schema reside in different table spaces?
Ans:
Yes we can objects of the same schema reside in different table space. Schema objects can stored in different tablespace and a tablespace can contained one or more schema objects data.
Ques: 19 Can a tablespace hold objects from different schemes?
Ans:
Yes we can a template hold objects from different schemes.
Ques: 20 What is Oracle table?
Ans:
The Collection of informations stored in the strutrued format which is in rows and columns formate that is called a table.
Goto Page:
1
2 3 4 5 6 7 8 9
SQL Objective
SQL Objective Questions And Answers
SQL Interview Questions And Answers
SQL Interview Questions And Answers
R4R,SQL Objective, SQL Subjective, SQL Interview Questions And Answers,SQL,SQL Interview,SQL Questions ,SQL Answers
