Database (DBMS) interview questions and answers | Real time questions and answers

Database (DBMS) interview questions and answers are below

Questions 1: What is database or database management systems (DBMS)? and - What’s the

difference between file and database? Can files qualify as a database?

Answers 1 :Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information.Secondly the information has to be persistent, that means even after the application is closed the information should be persisted.

Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information.Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. 

Questions 2: What is SQL ?

Answers 2 :SQL stands for Structured Query Language.SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.

Questions 3: What’s difference between DBMS and RDBMS ?

Answers 3:DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity. So in short we can say

RDBMS = DBMS + REFERENTIAL INTEGRITY These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies claimed there DBMS product was a RDBMS compliant, but according to industry rules and regulations if the DBMS fulfills the twelve CODD rules it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc) fulfills all the twelve CODD rules and are considered as truly RDBMS.

Questions 4: What are CODD rules?

Answers 4:In 1969 Dr. E. F. Codd laid down some 12 rules which a DBMS should adhere in order to get the logo of a true RDBMS.

Rule 1: Information Rule."All information in a relational data base is represented explicitly at the logical level and inexactly one way - by values in tables."

Rule 2: Guaranteed access Rule."Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name." In flat files we have to parse and know exact location of field values. But if a DBMS is truly RDBMS you can access the value by specifying the table name, field name, for instance Customers.Fields [‘Customer Name’].

Rule 3: Systematic treatment of null values."Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.".

Rule 4: Dynamic on-line catalog based on the relational model."The data base description is represented at the logical level in the same way as ordinary data,so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure of the database.

Rule 5: Comprehensive data sub-language Rule. "A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items 

Data Definition

View Definition

Data Manipulation (Interactive and by program).

Integrity Constraints

Authorization.

Transaction boundaries ( Begin , commit and rollback)

Rule 6: .View updating Rule "All views that are theoretically updatable are also updatable by the system." Rule 7: High-level insert, update and delete."The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data."

Rule 8: Physical data independence."Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods."

Rule 9: Logical data independence."Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit un-impairment are made to the base tables."

Rule 10: Integrity independence."Integrity constraints specific to a particular relational data base must be definable in the relational data sub-language and storable in the catalog, not in the application programs." 

Rule 11: Distribution independence."A relational DBMS has distribution independence."

Rule 12: Non-subversion Rule."If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time)."

Questions 5: What are E-R diagrams?

Answers 5:E-R diagram also termed as Entity-Relationship diagram shows relationship between various tables in the database. .

Questions 6: How many types of relationship exist in database designing?

Answers 6:There are three major relationship models:-

One-to-one

One-to-many

Many-to-many

Questions 7:What is normalization? What are different type of normalization?

Answers 7:There is set of rules that has been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of Normalizing your database include:

=>Avoiding repetitive entries

=>Reducing required storage space

=>Preventing the need to restructure existing tables to accommodate new data.

=>Increased speed and flexibility of queries, sorts, and summaries.

Following are the three normal forms :-

First Normal Form

For a table to be in first normal form, data must be broken up into the smallest un possible.In

addition to breaking data up into the smallest meaningful values, tables first normal form should not contain repetitions groups of fields.

Second Normal form

The second normal form states that each field in a multiple field primary keytable must be

directly related to the entire primary key. Or in other words,each non-key field should be a fact

about all the fields in the primary key.

Third normal form

A non-key field should not depend on other Non-key field.

Questions 8: What is denormalization ?

Answers 8:Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance.The sacrifice to performance is that you increase redundancy in database.

Questions 9: Can you explain Fourth Normal Form and Fifth Normal Form ?

Answers 9:In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy “Third Normal form”.Fifth normal form deals with reconstructing information from smaller pieces of information.These smaller pieces of information can be maintained with less redundancy.

Questions 10: Have you heard about sixth normal form?

Answers 10:If we want relational system in conjunction with time we use sixth normal form. At this moment SQL Server does not supports it directly.

Questions 11: What are DML and DDL statements?

Answers 11:DML stands for Data Manipulation Statements. They update data values in table. Below are the most important DDL statements:-

=>SELECT - gets data from a database table

=> UPDATE - updates data in a table

=> DELETE - deletes data from a database table

=> INSERT INTO - inserts new data into a database table

DDL stands for Data definition Language. They change structure of the database objects like

table, index etc. Most important DDL statements are as shown below:-

=>CREATE TABLE - creates a new table in the database.

=>ALTER TABLE – changes table structure in database.

=>DROP TABLE - deletes a table from database

=> CREATE INDEX - creates an index

=> DROP INDEX - deletes an index

Questions 12: How do we select distinct values from a table?

Answers 12:DISTINCT keyword is used to return only distinct values. Below is syntax:- Column age and Table pcdsEmp

SELECT DISTINCT age FROM pcdsEmp

Questions 13: What is Like operator for and what are wild cards?

Answers 13:LIKE operator is used to match patterns. A "%" sign is used to define the pattern.

Below SQL statement will return all words with letter "S"

SELECT * FROM pcdsEmployee WHERE EmpName LIKE 'S%'

Below SQL statement will return all words which end with letter "S"

SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S'

Below SQL statement will return all words having letter "S" in between

SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S%'

"_" operator (we can read as “Underscore Operator”). “_” operator is the character defined at

that point. In the below sample fired a query Select name from pcdsEmployee where name like

'_s%' So all name where second letter is “s” is returned.

Questions 14: Can you explain Insert, Update and Delete query?

Answers 14:Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :-

INSERT INTO pcdsEmployee SET name='rohit',age='24';

UPDATE pcdsEmployee SET age='25' where name='rohit';

DELETE FROM pcdsEmployee WHERE name = 'sonia';

Questions 15: What is order by clause?

Answers 15:ORDER BY clause helps to sort the data in either ascending order to descending order.Ascending order sort query

SELECT name,age FROM pcdsEmployee ORDER BY age ASC

Descending order sort query

SELECT name FROM pcdsEmployee ORDER BY age DESC

Questions 16: What is the SQL " IN " clause?

Answers 16:SQL IN operator is used to see if the value exists in a group of values. For instance the below SQL checks if the Name is either 'rohit' or 'Anuradha' 

SELECT * FROM pcdsEmployee WHERE name IN ('Rohit','Anuradha') 

Also you can specify a not clause with the same. 

SELECT * FROM pcdsEmployee WHERE age NOT IN (17,16)

Questions 17: Can you explain the between clause?

Answers 17:Below SQL selects employees born between '01/01/1975' AND '01/01/1978' as per mysql

SELECT * FROM pcdsEmployee WHERE DOB BETWEEN '1975-01-01' AND '2011-09-28'

Questions 18: we have an employee salary table how do we find the second highest from it?

Answers 18:below Sql Query find the second highest salary

SELECT * FROM pcdsEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM pcdsEmployeeSalary b WHERE b.salary>=a.salary))

Questions 19: What are different types of joins in SQL?

Answers 19: INNER JOIN:Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers.Customerid and Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID LEFT OUTER JOIN

Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON

Customers.CustomerID =Orders.CustomerID RIGHT OUTER JOIN

Right join will display all records in right table of the SQL statement. In SQL below all orders

with or without matching customer records will be displayed. Customer data for orders without

customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON

Customers.CustomerID =Orders.CustomerID

Questions 20: What is “CROSS JOIN”? or What is Cartesian product?

Answers 20:“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.

Questions 21: How to select the first record in a given set of rows?

Answers 21: Select top 1 * from sales.salesperson

Questions 22: What is the default “-SORT ” order for a SQL?

Answers 22: ASCENDING

Questions  23: What is a self-join?

Answers 23: If we want to join two instances of the same table we can use self-join.

Questions 24: What’s the difference between DELETE and TRUNCATE ?

Answers 24:Following are difference between them:

=>>DELETE TABLE syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.

=>>DELETE table can have criteria while TRUNCATE can not.

=>> TRUNCATE table can not have triggers.

Questions 25: What’s the difference between “UNION” and “UNION ALL” ?

Answers 25:UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.

Questions 26: What are cursors and what are the situations you will use them?

Answers 26:SQL statements are good for set at a time operation. So it is good at handling set of data. But there are scenarios where we want to update row depending on certain criteria. we will loop through all rows and update data accordingly. There’s where cursors come in to picture.

Questions 27: What is " Group by " clause?

Answers 27: “Group by” clause group similar data so that aggregate values can be derived.

Questions 28: What is the difference between “HAVING” and “WHERE” clause?

Answers 28:“HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.

Questions 29: What is a Sub-Query?

Answers 29:A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you have to check for the existence of records.For example, to retrieve all EmployeeID and CustomerID records from the ORDERS table that have the EmployeeID greater than the average of the EmployeeID field, you can create a nested query, as shown:

SELECT DISTINCT EmployeeID, CustomerID FROM ORDERS WHERE EmployeeID > (SELECT AVG(EmployeeID) FROM ORDERS) 

Questions 30: What are Aggregate and Scalar Functions?

Answers 30:Aggregate and Scalar functions are in built function for counting and calculations.

Aggregate functions operate against a group of values but returns only one value.

AVG(column) :- Returns the average value of a column

COUNT(column) :- Returns the number of rows (without a NULL value) of a column

COUNT(*) :- Returns the number of selected rows

MAX(column) :- Returns the highest value of a column

MIN(column) :- Returns the lowest value of a column

Scalar functions operate against a single value and return value on basis of the single value.

UCASE(c) :- Converts a field to upper case

LCASE(c) :- Converts a field to lower case

MID(c,start[,end]) :- Extract characters from a text field

LEN(c) :- Returns the length of a text

Questions 31: Can you explain the SELECT INTO Statement?

Answers 31:SELECT INTO statement is used mostly to create backups. The below SQL backsup the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of pcdsEmployeeBackup and pcdsEmployee table should be same. 

SELECT * INTO pcdsEmployeeBackup FROM pcdsEmployee

Questions 32: What is a View?

Answers 32:View is a virtual table which is created on the basis of the result set returned by the select statement.

CREATE VIEW [MyView] AS SELECT * from pcdsEmployee where LastName = 'singh'

In order to query the view

SELECT * FROM [MyView]

Questions 33 : What is SQl injection ?

Answers 33:It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database.SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation.As name suggest we inject SQL which can be relatively dangerous for the database. Example this is a simple SQL

SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'

Now somebody does not put “x” as the input but puts “x ; DROP TABLE members;”.

So the actual SQL which will execute is :-

SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' ; DROP TABLE members;Think what will happen to your database.

Questions 34: What is Data Warehousing ?

Answers 34:Data Warehousing is a process in which the data is stored and accessed from central location and is meant to support some strategic decisions. Data Warehousing is not a requirement for Data mining. But just makes your Data mining process more efficient.

Data warehouse is a collection of integrated, subject-oriented databases designed to support the decision-support functions (DSF), where each unit of data is relevant to some moment in time.

Questions 35: What are Data Marts?

Answers 35:Data Marts are smaller section of Data Warehouses. They help data warehouses collect data. For example your company has lot of branches which are spanned across the globe. Head-office of the company decides to collect data from all these branches for anticipating market. So to achieve this IT department can setup data mart in all branch offices and a central data warehouse where all data will finally reside.

Questions 36:What are Fact tables and Dimension Tables ? What is Dimensional Modeling and Star Schema Design

Answers 36:When we design transactional database we always think in terms of normalizing design to its least form. But when it comes to designing for Data warehouse we think more in terms of denormalizing the database. Data warehousing databases are designed using Dimensional Modeling. Dimensional Modeling uses the existing relational database structure and builds on that. There are two basic tables in dimensional modeling:-

Fact Tables. 

Dimension Tables.

Fact tables are central tables in data warehousing. Fact tables have the actual aggregate values which will be needed in a business process. While dimension tables revolve around fact tables.They describe the attributes of the fact tables.

Questions 37:What is Snow Flake Schema design in database? What’s the difference between Star and Snow flake schema?

Answers 37:Star schema is good when you do not have big tables in data warehousing. But when tables start becoming really huge it is better to denormalize. When you denormalize star schema it is nothing but snow flake design. For instance below customeraddress table is been normalized and is a child table of Customer table. Same holds true for Salesperson table.

Questions 38:What is ETL process in Data warehousing? What are the different stages in “Data warehousing”?

Answers 38:ETL (Extraction, Transformation and Loading) are different stages in Data warehousing. Like when we do software development we follow different stages like requirement gathering,designing, coding and testing. In the similar fashion we have for data warehousing.

Extraction:-In this process we extract data from the source. In actual scenarios data source can be in many forms EXCEL, ACCESS, Delimited text, CSV (Comma Separated Files) etc. So extraction process handle’s the complexity of understanding the data source and loading it in a structure of data warehouse.

Transformation:-This process can also be called as cleaning up process. It’s not necessary that after the extraction process data is clean and valid. For instance all the financial figures have NULL values but you want it to be ZERO for better analysis. So you can have some kind of stored procedure which runs through all extracted records and sets the value to zero.

Loading:-After transformation you are ready to load the information in to your final data warehouse database.

Questions 39: What is Data mining ?

Answers 39:Data mining is a concept by which we can analyze the current data from different perspectives and summarize the information in more useful manner. It’s mostly used either to derive some valuable information from the existing data or to predict sales to increase customer market.

There are two basic aims of Data mining:-

Prediction: -From the given data we can focus on how the customer or market will perform. For instance we are having a sale of 40000 $ per month in India, if the same product is to be sold with a discount how much sales can the company expect.

Summarization: -To derive important information to analyze the current business scenario. For example a weekly sales report will give a picture to the top management how we are performing on a weekly basis?

Questions 40: Compare Data mining and Data Warehousing ?

Answers 40:“Data Warehousing” is technical process where we are making our data centralized while “Data mining” is more of business activity which will analyze how good your business is doing or predict how it will do in the future coming times using the current data. As said before “Data Warehousing” is not a need for “Data mining”. It’s good if you are doing “Data mining” on a “Data Warehouse” rather than on an actual production database. “Data Warehousing” is essential when we want to consolidate data from different sources, so it’s like a cleaner and matured data which sits in between the various data sources and brings then in to one format.“Data Warehouses” are normally physical entities which are meant to improve accuracy of “Data mining” process. For example you have 10 companies sending data in different format, so you create one physical database for consolidating all the data from different company sources,while “Data mining” can be a physical model or logical model. You can create a database in “Data mining” which gives you reports of net sales for this year for all companies. This need not be a physical database as such but a simple query.

Questions 41: What are indexes? What are B-Trees?

Answers 41:Index makes your search faster. So defining indexes to your database will make your search faster.Most of the indexing fundamentals use “B-Tree” or “Balanced-Tree” principle. It’s not a principle that is something is created by SQL Server or ORACLE but is a mathematical derived

fundamental.In order that “B-tree” fundamental work properly both of the sides should be balanced.

Questions 42:I have a table which has lot of inserts, is it a good database design to create indexes on that table?Insert’s are slower on tables which have indexes, justify it?or Why do page splitting happen?

Answers 42:All indexing fundamentals in database use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance.Creates a new page to balance the tree.Shuffle and move the data to pages.So if your table is having heavy inserts that means it’s transactional, then you can visualize the amount of splits it will be doing. This will not only increase insert time but will also upset the end-user who is sitting on the screen. So when you forecast that a table has lot of inserts it’s not a good idea to create indexes.

Questions 43:What are the two types of indexes and explain them in detail? or What’s the

difference between clustered and non-clustered indexes?

Answers 43:There are basically two types of indexes:-

Clustered Indexes.

Non-Clustered Indexes.

In clustered index the non-leaf level actually points to the actual data.In Non-Clustered index

the leaf nodes point to pointers (they are rowid’s) which then point to actual data.

Database Interview Questions and Answers | Real time questions and Answers

Database Interview Questions and Answers | Real time questions and Answers

Q) DML - insert, update, delete

     DDL - create, alter, drop, truncate, rename.

     DQL - select

     DCL - grant, revoke.

     TCL - commit, rollback, savepoint.

  Q) Normalization

            Normalization is the process of simplifying the relationship between data elements in a record.

 (i) 1^st normal form: - 1^st N.F is achieved when all repeating groups are removed, and P.K should be defined. big table is broken into many small tables, such that each table has a primary key.

(ii) 2^nd normal form: - Eliminate any non-full dependence of data item on record keys. I.e. The columns in a table which is not completely dependant on the primary key are taken to a separate table.

(iii) 3^rd normal form: - Eliminate any transitive dependence of data items on P.K’s. i.e. Removes Transitive dependency. Ie If X is the primary key in a table. Y & Z are columns in the same table. Suppose Z depends only on Y and Y depends on X. Then Z does not depend directly on primary key. So remove Z from the table to a look up table. 

Q) Diff Primary key and a Unique key? What is foreign key?

A) Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only. 

Foreign key constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

 Q) Diff Delete & Truncate?

A) Rollback is possible after DELETE but TRUNCATE remove the table permanently and can’t rollback. Truncate will remove the data permanently we cannot rollback the deleted data.

 Dropping   :  (Table structure  + Data are deleted), Invalidates the dependent objects, Drops the indexes

Truncating :  (Data alone deleted), Performs an automatic commit, Faster than delete

Delete        : (Data alone deleted), Doesn’t perform automatic commit

Q) Diff Varchar and Varchar2?

A) The difference between Varchar and Varchar2 is both are variable length but only 2000 bytes of character of data can be store in varchar where as 4000 bytes of character of data can be store in varchar2.

 Q) Diff LONG & LONG RAW?

A) You use the LONG datatype to store variable-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 32760 bytes.

     You use the LONG RAW datatype to store binary data (or) byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. The maximum length of a LONG RAW value is 32760 bytes.

 Q) Diff Function & Procedure

            Function is a self-contained program segment, function will return a value but procedure not.

            Procedure is sub program will perform some specific actions.

 Q) How to find out duplicate rows & delete duplicate rows in a table?

A)         MPID EMPNAME    EMPSSN

   -----   ----------     -----------

         1 Jack       555-55-5555

         2 Mike       555-58-5555

         3 Jack       555-55-5555

         4 Mike       555-58-5555

SQL> select count (empssn), empssn from employee group by empssn

      having count (empssn) > 1;

 COUNT (EMPSSN) EMPSSN

-------------            -----------

            2 555-55-5555

            2 555-58-5555

SQL> delete from employee where (empid, empssn)

    not in (select min (empid), empssn from employee group by empssn);

 Q) Select the nth highest rank from the table?

A) Select * from tab t1 where 2=(select count (distinct (t2.sal)) from tab t2 where t1.sal<=t2.sal)

 Q) a) Emp table where fields empName, empId, address

   b) Salary table where fields EmpId, month, Amount

these 2 tables he wants EmpId, empName and salary for month November?

A) Select emp.empId, empName, Amount from emp, salary where emp.empId=salary.empId and month=November;

 Q) Oracle/PLSQL: Synonyms?

A) A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects

 Syntax: -

Create [or replace]  [public] synonym [schema.] synonym_name for [schema.] object_name;

 or replace -- allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

Public -- means that the synonym is a public synonym and is accessible to all users. 

Schema -- is the appropriate schema.  If this phrase is omitted, Oracle assumes that you are referring to your own schema.

object_name -- is the name of the object for which you are creating the synonym. It can be one of the following:

Table	Package
View	materialized view
sequence	java class schema object
stored procedure	user-defined object
Function	Synonym

 example:

Create public synonym suppliers for app. suppliers;

Example demonstrates how to create a synonym called suppliers.  Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app.  For example:

Select * from suppliers; 

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

Create or replace public synonym suppliers for app. suppliers;

Dropping a synonym

It is also possible to drop a synonym.

drop  [public]  synonym  [schema .]  Synonym_name [force];

public -- phrase allows you to drop a public synonym.  If you have specified public, then you don't specify a schema.

Force -- phrase will force Oracle to drop the synonym even if it has dependencies.  It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects. 

Example:

Drop public synonym suppliers;

This drop statement would drop the synonym called suppliers that we defined earlier.

 Q) What is an alias and how does it differ from a synonym?

A) An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view.  The alias is not dropped when the table is dropped.

 Q) What are joins? Inner join & outer join?

A) By using joins, you can retrieve data from two or more tables based on logical relationships between the tables

Inner Join: - returns all rows from both tables where there is a match.

Outer Join: - outer join includes rows from tables when there are no matching values in the tables.

 • LEFT JOIN or LEFT OUTER JOIN 

The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
• RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
• FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Q. Diff join and a Union?

A) A join selects columns from 2 or more tables. A union selects rows. 

when using the UNION command all selected columns need to be of the same data type. The UNION command eliminate duplicate values.

 Q. Union & Union All?

A) The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It cannot eliminate duplicate values.

 > SELECT E_Name FROM Employees_Norway

UNION ALL

    SELECT E_Name FROM Employees_USA

 Q) Is the foreign key is unique in the primary table?

A) Not necessary

 Q) Table mentioned below named employee

ID	NAME	MID
1	CEO	Null
2	VP	CEO
3	Director	VP

 Asked to write a query to obtain the following output

CEO	Null
VP	CEO
Director	VP

 A) SQL> Select a.name, b.name from employee a, employee b where a.mid=b.id(+).

 Q) Explain a scenario when you don’t go for normalization?

A) If we r sure that there wont be much data redundancy then don’t go for normalization.

 Q) What is Referential integrity?

A) R.I refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

 Q) What techniques are used to retrieve data from more than one table in a single SQL statement?

A) Joins, unions and nested selects are used to retrieve data.

 Q) What is a view? Why use it?

A) A view is a virtual table made up of data from base tables and other views, but not stored separately.

 Q) SELECT statement syntax?

A) SELECT [ DISTINCT | ALL ]  column_expression1, column_expression2, ....

  [ FROM from_clause ]

  [ WHERE where_expression ]

  [ GROUP BY expression1, expression2, .... ]

  [ HAVING having_expression ]

  [ ORDER BY order_column_expr1, order_column_expr2, .... ]

 column_expression ::= expression [ AS ] [ column_alias ]

from_clause ::= select_table1, select_table2, ...

from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr  ...

from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr  ...

from_clause ::= select_table1 [INNER] JOIN select_table2  ...

select_table ::= table_name [ AS ] [ table_alias ]

select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]

order_column_expr ::= expression [ ASC | DESC ]

 Q) DISTINCT clause?

A) The DISTINCT clause allows you to remove duplicates from the result set.

      > SELECT DISTINCT city FROM supplier;

 Q) COUNT function?

A) The COUNT function returns the number of rows in a query

       > SELECT COUNT (*) as "No of emps" FROM employees WHERE salary > 25000;

 Q) Diff HAVING CLAUSE & WHERE CLAUSE? 

A) Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

 Q) Diff GROUP BY & ORDER BY?

A) Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

 > SELECT "col_nam1", SUM("col_nam2") FROM "tab_name" GROUP BY "col_nam1"

> SELECT "col_nam" FROM "tab_nam" [WHERE "condition"] ORDER BY "col_nam" [ASC, DESC]

 Q) What keyword does an SQL SELECT statement use for a string search?

A) The LIKE keyword allows for string searches.  The % sign is used as a wildcard.

 Q) What is a NULL value?  What are the pros and cons of using NULLS?

A) NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value. A NULL in a column means no entry has been made in that column. A data value for the column is "unknown" or "not available."

 Q) Index? Types of indexes?

A) 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. 

 Unique Index : -

A unique index means that two rows cannot have the same index value.

   >CREATE UNIQUE INDEX index_name ON table_name (column_name)

 When the UNIQUE keyword is omitted, duplicate values are allowed. If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:

   >CREATE INDEX PersonIndex ON Person (LastName DESC)

 If you want to index more than one column you can list the column names within the parentheses.

   >CREATE INDEX PersonIndex ON Person (LastName, FirstName)

 Q) Diff subqueries & Correlated subqueries?

A)subqueries are self-contained. None of them have used a reference from outside the subquery.

correlated subquery cannot be evaluated as an independent query, but can reference columns in a table listed in the from list of the outer query. 

Q) Predicates IN, ANY, ALL, EXISTS?

A) Sub query can return a subset of zero to n values. According to the conditions which one 

IN:The comparison operator is the equality and the logical operation between values is OR.wants to express, one can use the predicates IN, ANY, ALL or EXISTS. 

 ANY:Allows to check if at least a value of the list satisfies condition.

ALL:Allows to check if condition is realized for all the values of the list.

EXISTS :If the subquery returns a result, the value returned is True otherwise the value returned is False.

Q) What are some sql Aggregates and other Built-in functions?

A) AVG, SUM, MIN, MAX, COUNT and DISTINCT.

Struts Framework Interview questions and answers | Real time Questions and Answers

Struts Framework Interview questions and answers | Real time Questions and Answers

Q) Framework?

A) A framework is a reusable, ``semi-complete'' application that can be specialized to produce custom applications

 Q) Why do we need Struts?

A) Struts combines Java Servlets, Java ServerPages, custom tags, and message resources into a unified framework. The end result is a cooperative, synergistic platform, suitable for development teams, independent developers, and everyone in between.

 Q) Diff Struts1.0 & 1.1?

1.RequestProcessor class, 2.Method perform() replaced by execute() in Struts base Action Class

3. Changes to web.xml and struts-config.xml, 4. Declarative exception handling, 5.Dynamic ActionForms, 6.Plug-ins, 7.Multiple Application Modules, 8.Nested Tags, 9.The Struts Validator Change to the ORO package, 10.Change to Commons logging, 11. Removal of Admin actions, 12.Deprecation of the GenericDataSource

 Q) What is the difference between Model2 and MVC models?

In model2, we have client tier as jsp, controller is servlet, and business logic is java bean. Controller and business logic beans are tightly coupled. And controller receives the UI tier parameters. But in MVC, Controller and business logic are loosely coupled and controller has nothing to do with the project/ business logic as such. Client tier parameters are automatically transmitted to the business logic bean, commonly called as ActionForm.

So Model2 is a project specific model and MVC is project independent.

 Q) How to Configure Struts?

Before being able to use Struts, you must set up your JSP container so that it knows to map all appropriate requests with a certain file extension to the Struts action servlet. This is done in the web.xml file that is read when the JSP container starts. When the control is initialized, it reads a configuration file (struts-config.xml) that specifies action mappings for the application while it's possible to define multiple controllers in the web.xml file, one for each application should suffice

 Q) ActionServlet (controller)

The class org.apache.struts.action.ActionServlet is the called the ActionServlet. In Struts Framework this class plays the role of controller. All the requests to the server goes through the controller. Controller is responsible for handling all the requests. The Controller receives the request from the browser, invoke a business operation and coordinating the view to return to the client.

 Q) Action Class

The Action Class is part of the Model and is a wrapper around the business logic. The purpose of Action Class is to translate the HttpServletRequest to the business logic. To use the Action, we need to Subclass and overwrite the execute() method. In the Action Class all the database/business processing are done. It is advisable to perform all the database related stuffs in the Action Class. The ActionServlet (commad) passes the parameterized class to Action Form using the execute() method. The return type of the execute method is ActionForward which is used by the Struts Framework to forward the request to the file as per the value of the returned ActionForward object.

 Ex: -

package com.odccom.struts.action;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class BillingAdviceAction extends Action {

     public ActionForward execute(ActionMapping mapping,

                                                    ActionForm form,

                                                  HttpServletRequest request, HttpServletResponse response)

        throws Exception {

        BillingAdviceVO bavo = new BillingAdviceVO();

        BillingAdviceForm  baform = (BillingAdviceForm)form;

        DAOFactory factory = new MySqlDAOFactory();

        BillingAdviceDAO badao = new MySqlBillingAdviceDAO();   

        ArrayList projects = badao.getProjects();

        request.setAttribute("projects",projects);

        return (mapping.findForward("success"));

    }

}

 Q) Action Form

An ActionForm is a JavaBean that extends org.apache.struts.action.ActionForm. ActionForm maintains the session state for web application and the ActionForm object is automatically populated on the server side with data entered from a form on the client side.

 Ex: -

package com.odccom.struts.form;

 public class BillingAdviceForm extends ActionForm {

               private String projectid;

               private String projectname;   

               public String getProjectid() {  return projectid;  }

               public void setProjectid(String projectid) {  this.projectid = projectid;  }    

public ActionErrors validate(ActionMapping mapping, HttpServletRequest request) {

        ActionErrors errors = new ActionErrors();

        if(getProjectName() == null || getProjectName().length() < 1){

               errors.add(“name”, new ActionError(“error.name.required”));

        }         

        return errors;

    }

public void reset(ActionMapping mapping, HttpServletRequest request){

            this.roledesc = "";

            this.currid      = "";

}

}

 Q) Message Resource Definition file

 M.R.D file are simple “.properties” file these files contains the messages that can be used in struts project. The M.R.D can be added in struts-config.xml file through <message-resource> tag

ex: <message-resource parameter=”MessageResource”>

 Q) Reset :-

This is called by the struts framework with each request, purpose of this method is to reset all of the forms data members and allow the object to be pooled for rescue.

 public class TestAction extends ActionForm{

  public void reset(ActionMapping mapping, HttpServletRequest request request)

}

 Q) execute

Is called by the controller when a request is received from a client. The controller creates an instance of the Action class if one does not already exist. The frame work will create only a single instance of each Action class.

  public ActionForward execute( ActionMapping mapping, ActionForm form, HttpServletRequest request,    HttpServletResponse response)

 Q) Validate: -

This method is called by the controller after the values from the request has been inserted into the ActionForm. The ActionForm should perform any input validation that can be done and return any detected errors to the controller.

 Public ActionErrors validate(ActionMapping mapping, HttpServletRequest request)

 Q) What is Validator? Why will you go for Validator?

Validator is an independent framework, struts still comes packaged with it.

            Instead of coding validation logic in each form bean’s validate() method, with validator you use an xml configuration file to declare the validations that should be applied to each form bean. Validator supports both “server-side and client-side” validations where form beans only provide “server-side” validations.

 Flowing steps: -

1. Enabling the Validator plug-in: This makes the Validator available to the system.

2. Create Message Resources for the displaying the error message to the user.

3. Developing the Validation rules We have to define the validation rules in the validation.xml for the address form. Struts Validator Framework uses this rule for generating the JavaScript for validation.

4. Applying the rules: We are required to add the appropriate tag to the JSP for generation of JavaScript.

 Using Validator Framework

                Using the Validator framework involves enabling the Validator plug-in, configuring Validator's two configuration files, and creating Form Beans that extend the Validator's ActionForm subclasses.

 Enabling the Validator Plug-in

Validator framework comes packaged with Struts; Validator is not enabled by default. To enable Validator, add the following plug-in definition to your application's struts-config.xml file.

 <plug-in className="org.apache.struts.validator.ValidatorPlugIn">

<set-property property="pathnames" value="/technology/WEB-INF/validator-rules.xml,

 /WEB-INF/validation.xml"/>

</plug-in>

This definition tells Struts to load and initialize the Validator plug-in for your application. Upon initialization, the plug-in loads the comma-delimited list of Validator config files specified by the pathnames property.

 Validator-rules.xml

<form-validation>

  <global>

    <Validator name="required" classname="org.apache.struts.validator.FieldChecks" method="validateRequired"

       methodParams="java.lang.Object,

                     org.apache.commons.validator.ValidatorAction,

                     org.apache.commons.validator.Field,

                     org.apache.struts.action.ActionErrors,

                     javax.servlet.http.HttpServletRequest"

                msg="errors.required">

      <javascript>

        <![CDATA[

                    function validateRequired(form) {

        ]]>

      </javascript>

    </validator>

  </global>

</form-validation>

 Q) How struts validation is working?

Validator uses the XML file to pickup the validation rules to be applied to a form. In XML validation requirements are defined applied to a form. In case we need special validation rules not provided by the validator framework, we can plug in our own custom validations into Validator.

 The Validator Framework uses two XML configuration files validator-rules.xml & validation.xml. The validator-rules.xml defines the standard validation routines; such as Required, Minimum Length, Maximum length, Date Validation, Email Address validation and more. These are reusable and used in validation.xml. To define the form specific validations. The validation.xml defines the validations applied to a form bean.

 Q) What is Tiles?

A) Tiles is a framework for the development user interface, Tiles is enables the developers to develop the web applications by assembling the reusable tiles.

1. Add the Tiles Tag Library Descriptor (TLD) file to the web.xml.
2. Create layout JSPs.
3. Develop the web pages using layouts.

Q) How to call ejb from Struts?

    1…use the Service Locator patter to look up the ejbs.

    2…Or you can use InitialContext and get the home interface.

 Q) What are the various Struts tag libraries?

Struts-html tag library -> used for creating dynamic HTML user interfaces and forms.

Struts-bean tag library -> provides substantial enhancements to the basic capability provided by .

Struts-logic tag library -> can manage conditional generation of output text, looping over object collections for repetitive generation of output text, and application flow management.

Struts-template tag library -> contains tags that are useful in creating dynamic JSP templates for pages which share a common format.

Struts-tiles tag library -> This will allow you to define layouts and reuse those layouts with in our site.

Struts-nested tag library ->

 Q) How you will handle errors & exceptions using Struts?

-To handle “errors” server side validation can be used using ActionErrors classes can be used. 
-The “exceptions” can be wrapped across different layers to show a user showable exception. 
- Using validators

 Q) What are the core classes of struts?

A) ActionForm, Action, ActionMapping, ActionForward etc.

 Q) How you will save the data across different pages for a particular client request using Struts?

A) If the request has a Form object, the data may be passed on through the Form object across pages. Or within the Action class, call request.getSession and use session.setAttribute(), though that will persist through the life of the session until altered.

(Or) Create an appropriate instance of ActionForm that is form bean and store that form bean in session scope. So that it is available to all the pages that for a part of the request

 Q) How would struts handle “messages” required for the application?

A) Messages are defined in a “.properties” file as name value pairs. To make these messages available to the application, You need to place the .properties file in WEB-INF/classes folder and define in struts-config.xml

 <message-resource parameter=”title.empname”/>

 and in order to display a message in a jsp would use this

 <bean:message key=”title.empname”/>

 Q) What is the difference between ActionForm and DynaActionForm?

A) 1. In struts 1.0, action form is used to populate the html tags in jsp using struts custom tag.when the java code changes, the change in action class is needed. To avoid the chages in struts 1.1 dyna action form is introduced.This can be used to develop using xml. The dyna action form bloats up with the struts-config.xml based definetion.

2. There is no need to write actionform class for the DynaActionForm and all the variables related to the actionform class will be specified in the struts-config.xml. Where as we have to create Actionform class with getter and setter methods which are to be populated from the form

3.if the formbean is a subclass of ActionForm, we can provide reset(),validate(),setters(to hold the values),gettters whereas if the formbean is a subclass to DynaActionForm we need not provide setters, getters but in struts-config.xml we have to configure the properties in using .basically this simplifies coding

 DynaActionForm which allows you to configure the bean in the struts-config.xml file. We are going to use a subclass of DynaActionForm called a DynaValidatorForm which provides greater functionality when used with the validation framework.

 <struts-config>

    <form-beans>

        <form-bean name="employeeForm" type="org.apache.struts.validator.DynaValidatorForm">

            <form-property name="name" type="java.lang.String"/>

            <form-property name="age" type="java.lang.String"/>

            <form-property name="department" type="java.lang.String" initial="2" />

            <form-property name="flavorIDs" type="java.lang.String[]"/>

            <form-property name="methodToCall" type="java.lang.String"/>

        </form-bean>

    </form-beans>

</struts-config>

 This DynaValidatorForm is used just like a normal ActionForm when it comes to how we define its use in our action mappings. The only 'tricky' thing is that standard getter and setters are not made since the DynaActionForms are backed by a HashMap. In order to get fields out of the DynaActionForm you would do: 

String age = (String)formBean.get("age");
Similarly, if you need to define a property:
formBean.set("age","33");

Q) Struts how many “Controllers” can we have?

A) You can have multiple controllers, but usually only one is needed

Q) Can I have more than one struts-config.xml?

A) Yes you can I have more than one.

<servlet>

    <servlet-name>action</servlet-name>

    <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>

    <init-param>

            <param-name>config</param-name>

<param-value>/WEB-INF/struts-config.xml</param-value>

    </init-param>

   <! — module configurations -- >

   <init-param>

            <param-name>config/exercise</param-name>

<param-value>/WEB-INF/exercise/struts-config.xml</param-value>

    </init-param>

  </servlet>

Q) Can you write your own methods in Action class other than execute() and call the user method directly?

A) Yes, we can create any number of methods in Action class and instruct the action tag in struts-config.xml file to call that user methods.

Public class StudentAction extends DispatchAction

{

      public ActionForward read(ActionMapping mapping, ActionForm form,

                                                  HttpServletRequest request, HttpServletResponse response) throws Exceptio

      {

            Return some thing;

      }

      public ActionForward update(ActionMapping mapping, ActionForm form,

                                                  HttpServletRequest request, HttpServletResponse response) throws Exceptio

      {

            Return some thing;

      }

If the user want to call any methods, he would do something in struts-config.xml file.

<action path="/somepage"

            type="com.odccom.struts.action.StudentAction"

            name="StudentForm"

            <b>parameter=”methodToCall”</b>

            scope="request"

            validate="true">                                   

<forward name="success" path="/Home.jsp" />

</action>

 Q) Struts-config.xml

 <struts-config>

     <data-sources>

        <data-source>

               <set-property property=”key” value=”” url=”” maxcount=”” mincount=””  user=”” pwd=”” >                 

        </data-source>

    <data-sources>

     <!— describe the instance of the form bean-- >

    <form-beans>

        <form-bean name="employeeForm" type="net.reumann.EmployeeForm"/>

    </form-beans>

     <!— to identify the target of an action class when it returns results -- >

    <global-forwards>

        <forward name="error" path="/error.jsp"/>

    </global-forwards>

     <!— describe an action instance to the action servlet-- >

    <action-mappings>

        <action path="/setUpEmployeeForm" type="net.reumann.SetUpEmployeeAction"

            name="employeeForm" scope="request" validate="false">

          <forward name="continue" path="/employeeForm.jsp"/>

        </action> 

        <action path="/insertEmployee" type="net.reumann.InsertEmployeeAction"

            name="employeeForm" scope="request" validate="true"

            input="/employeeForm.jsp">

         <forward name="success" path="/confirmation.jsp"/>

        </action>

    </action-mappings>

        <!— to modify the default behaviour of the struts controller-- >

    <controller processorClass=”” bufferSize=” ” contentType=””  noCache=””   maxFileSize=””/>

       <!— message resource properties -- >

    <message-resources parameter="ApplicationResources" null="false" />

    <!—Validator plugin à

   <plug-in className="org.apache.struts.validator.ValidatorPlugIn">
          <set-property property="pathnames" value="/WEB-INF/validator-rules.xml, /WEB-INF/validation.xml"/>
   </plug-in>

    <!—Tiles plugin à

   <plug-in className="org.apache.struts.tiles.TilesPlugIn">
          <set-property property="definitions-config" value="/WEB-INF/tiles-defs.xml "/>
   </plug-in>

 </struts-config>

 Q) Web.xml: -   Is a configuration file describe the deployment elements.

<web-app>

    <servlet>

            <servlet-name>action</servlet-name>

<servlet-class>org.apache.struts.action.ActionServlet</servlet-class>

      <!—Resource Properties -->

      <init-param>

            <param-name>application</param-name>

            <param-value>ApplicationResources</param-value>

      </init-param>

                  <init-param>

         <param-name>config</param-name>

         <param-value>/WEB-INF/struts-config.xml</param-value>

      </init-param>

    <load-on-startup>1</load-on-startup>

  </servlet>

   <!-- Action Servlet Mapping -->

  <servlet-mapping>

            <servlet-name>action</servlet-name>

<url-pattern>/do/*</url-pattern>

  </servlet-mapping>

   <!-- The Welcome File List -->

  <welcome-file-list>

            <welcome-file>index.jsp</welcome-file>

  </welcome-file-list>

   <!-- tag libs -->

  <taglib>

<taglib-uri>struts/bean-el</taglib-uri>

<taglib-location>/WEB-INF/struts-bean-el.tld</taglib-location>

  </taglib>

   <taglib>

            <taglib-uri>struts/html-el</taglib-uri>

            <taglib-location>/WEB-INF/struts-html-el.tld</taglib-location>

  </taglib>

   <!—Tiles tag libs -->

  <taglib>
            <taglib-uri>/tags/struts-tiles</taglib-uri>
            <taglib-location>/WEB-INF/struts-tiles.tld</taglib-location>
  </taglib>

 <!—Using Filters -->

  <filter>

<filter-name>HelloWorldFilter</filter-name>

            <filter-class>HelloWorldFilter</filter-class>

  </filter>

   <filter-mapping>

            <filter-name>HelloWorldFilter</filter-name>

            <url-pattern>ResponseServlet</ url-pattern >

  </filter-mapping> 

</web-app>