面试题与答案

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. So it�s a matter of debate if some interviewers think this as a main difference between files and database accept it� going in to debate is probably loosing a job.

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.

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.

E-R diagram also termed as Entity-Relationship diagram shows relationship between various

tables in the database.

There are three major relationship models:-

One-to-one

One-to-many

Many-to-many

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

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

Table wbEmp

SELECT DISTINCT age FROM wbEmp

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.

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 wbEmployee SET name='maxwell',age='22';

UPDATE wbEmployee SET age='22' where name='maxwell';

DELETE FROM wbEmployee WHERE name = 'david';

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

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 'David' or 'Craig' SELECT * FROM wbEmployee WHERE name IN ('David','Craig') Also you can specify a not clause with the same. SELECT * FROM wbEmployee WHERE age NOT IN (30,25)

Below SQL selects employees born between '01/01/1995' AND '01/01/1978' as per mysql

SELECT * FROM wbEmployee WHERE DOB BETWEEN '1995-01-01' AND '2011-09-28'

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.

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.

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)

A collection of conceptual tools for describing data, data relationships data semantics and constraints.

An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set. 

It is a 'thing' in the real world with an independent existence.

It is an association among two or more entities.

The domain of attribute must include only atomic (simple, indivisible) values.

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

A foreign key is used to establish relationships among relations in the relational model. Technically, a foreign key is a column (or columns) appearing in one relation that is (are) the primary key of another table. Although there may be exceptions, the values in the foreign key columns usually must correspond to values existing in the set of primary key values. This correspondence requirement is created in a database using a referential integrity constraint on the foreign key.