DBMS VIVA QUESTIONS
FRESHERS INTERVIEW QUESTIONS
1. What is Database?
- A Database is an organized collection of data.
- Database Management System is a collection of interrelated data and a set of programs to access those data.
- It is of two types: a) Logical data Independence: Is is the capacity to change the conceptual schema without having to change external schemas or application programs.
4. What is Data Abstraction?
- Hiding the complexity of data from real world is called as Data Abstraction.
- Physical Level : This is the lowest level of abstraction which describes how data are actually stored.
View Level: View provides security mechanism to prevent user from accessing certain parts of database.
6. What is RDBMS?
- It stands for Relational Database Management System, which stores the data into the collection of tables, which is related by common fields between the columns of the table. example: Oracle, SQL Server.
- SQL stands for Structured Query Language, and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.
- There are two Integrity rules.
- Entity Integrity: States that "Primary key cannot have Null values",
- Referential Integrity: States that "Foreign Key can be either a Null or should be primary key values of other relation.
- A Primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit Not Null constraint. which mean primary key values cannot be null.
- A Unique key constraint uniquely identifies each record in the database.
- A Foreign key in one table is related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.
- This is a keyword used to query data from more tables based on the relationship between the fields of the tables.
a. Inner join: It returns rows when there is at least one match of tows between the tables.
b. Right join: It returns tows which are common between the tables and all rows of tight hand side table. Simply it returns all the rows from left hand side table even though there are no matches in the right hand side table.
c. Full join: It returns tows when there are matching tows in any one of the tables. This means, it returns all the tows from the left hand side table and all the rows from the right hand side table.
14. What is Normalization?
- Normalization is the process of minimizing redundancy and dependency by organizing fields and table of database. The main aim of Normalization is to add delete r modify field that can be made in a single table.
- Denormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.
- First Normal Form : This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
- Second Normal Form : Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
- Third Normal Form: This should meet all requirements of 2NF, removing the columns which are not dependent on primary key constraints.
- A View is a virtual table which consists of subset of data contained in a table. View can have data of one or more tables combined.
- An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.
- A database cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.
- Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, they are:
- one to one relationship
- one to many relationship
- many to many relationship
- It is a code written in order to get the information back from the database.
- There are two types of sub - queries - correlated and non - correlated.
- A Correlated sub-query cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.
- A Non - Correlated sub query can be considered as independent query and the output of sub-query are substituted in the main query.
- Stored procedure is a function consists of many sql statement to access the database system.
- It is a code or program that automatically execute with response to some event on a table or view in a database. Mainly , trigger helps to maintain the integrity of the database.
25. What is the difference between Delete, Truncate and Drop command?
- DROP and TRUNCATE are DDL Commands, whereas DELETE is a DML Command.
- Delete: It is used to remove rows from a table.
- Truncate: It removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use much undo space as DELETE.
- DROP: This command removes a table from the database.
- Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement.
- NOT NULL
- CHECK
- DEFAULT
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- Data integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.
- Data Warehouse is a central repository of data from multiple sources of Information. Those data are consolidated, transformed and made available for the mining and online processing.
- Self Join: Self join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS can be used for the same table comparision.
- Cross Join: Cross Join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, Where clause is used in cross join the query wil work like an INNER Join.
- UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
- MINUS operator is used to return rows from the first query but not from the second query.
- INTEREST Operator is used to return rows returned by both the queries.
- ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.
- DATA DEFINITION LANGUAGE (DDL) Statements are used to define the database structure or schema,
- CREATE - to create objects in the database
- ALTER - Alters the structure of the database
- DROP - Delete objects from the database
- TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
- RENAME - Rename an object
- DATA MANIPULATION LANGUAGE(DML) statements are used for managing data within schema objects.
- SELECT - retrieve data from the database
- INSERT - Insert data into a table
- UPDATE _ Updates existing data within a table
- DELETE - Deletes all records from a table, the space for the records remains.
- GRANT : gives users access privileges to database
- REVOKE : Withdraw access privileges given with the GRANT Command
- Transaction Control Statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
- COMMIT
- SAVEPOINT
- ROLLBACK
- SQL Data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type used while creating or altering the table.
- Number: for number
- Boolean: either true/false and null.
- Char: string of fixed length maximum char size is 255 characters.
- Varchar(size):
- Date:
- It is used to sort the data in ascending or descending order, based on one or more columns.
- It enables us to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by GROUP BY clause.
- A WHERE clause is used to filter the records and fetch only necessary records based on condition. The WHERE clause not only used in SELECT statement, but it is also used in UPDATE, DELETE statement.
- An Operator is a reserved word or a character used primarily in an SQL statements Where clause to perform operations, such as comparison and arithmetic operations. Operators are used to specify conditions in an SQL Statement and to serve as conjunctions for multiple conditions in a statement.
- Arithemetic Operators(+,-,*,/,mod)
- Comparision Operators
- Logical Operators(and,or,not)
41. Explain about built in function in SQL
- Group - by functions: They are used to retrieve multiple tows/columns using group by clause.
- Single Tow Functions: They return a value for every row that is processed in a query. There are four types of single row functions.
- Mathematical function
- Character or Text functions
- Date function
- Conversion function.
42. What is DUAL Table in Oracle?
- This is a single row and single column dummy table provide by oracle, used to perform mathematical/functional calculations without using a table.
44. What are Procedures?
- A stored procedure or in a simple proc is a named pl/sql block which performs one or more specific task. This is similar to a procedure in other programming languages.
45. What is a Sequence?
Sequence is a feature supported by some database systems to produce unique values on demand. 46. Explain Cursor.
- A cursor is a temporary work are created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.
- There are two types of cursors in PL/SQL:
- Implicit cursor
- Explicit cursor
- Index in SQL is created on existing table to retrieve the rows quickly.
Syntax : Create INDEX index_name on table_name;
48. Explain about Alias.
- Alias are defined for columns and tables. Basically aliases is created to make the column selected more readable.
Syntax: select column_name AS alias_name from table_name; ---- alias for columns
select column_name(s) from table_name AS alias_name; -----alias for tables
49. What are views?
- A View is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a Select statement in the database.
Syntax : Create view view_name
AS
select column_list from table_name[where condition];
Early off value natural cut. Opportunity economic call field.education
ReplyDelete