DBMS perguntas e respostas de entrevista

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

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

wbEmployeeBackup and wbEmployee table should be same. SELECT * INTO

wbEmployeeBackup FROM wbEmployee

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]

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.

Data independence means that "the application is independent of the storage structure and access strategy of data". In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level. 

Two types of Data Independence: 

1. Physical Data Independence: Modification in physical level should not affect the logical level.

2. Logical Data Independence: Modification in logical level should affect the view level.