SQL Advanced updated on Dec 2019

SQL Advanced

|   62471

1.What is REDO in database? 

A. Opposite of UNDO 
B. Re-does the previous operation on database again. 
C. REDO is used for ROLLBACK. 
D. None of the above. 
Answer: C
The most important point to remember is REDO is not the opposite of UNDO. Whenever a DML transaction happens in database, the data to be updated goes to the DATABASE BUFFER CACHE. From here the data is written to REDO BUFFER and then to REDO Logs. These logs are saved for future use. Future ROLLBACK and DATA RECOVERY operations require these logs. Without these logs it is impossible to do DATA RECOVERY. If ARCHIVING is enabled then these logs are bundled or archived and stored. 

 



2. COMMIT takes more time than ROLLBACK .

A. True 
B. False 
Answer: B 
COMMIT simply confirms the transaction and writes the committed data to disk and clears UNDO file. While ROLLBACK does the opposite transaction. ROLLBACK also clears UNDO file. ROLLBACK takes much longer time because it has to execute one full transaction (opposite) and COMMIT it. Hence COMMIT is faster than ROLLBACK. 

 



3. What is the difference between ORDERBY and GROUPBY? 

A. ORDERBY performs sorting while GROUPBY AGGREGATES Data 

B. GROUPBY sorts data while ORDERBY puts data in order 
C. Both perform sorting. 
D. None of the above 
Answer: A 
The ORDER BY performs a sort operation. So think of a telephone phone directory. 
SELECT NAME FROM DIRECTORY ORDER BY NAME 
This would ensure that the result set would be sorted in (by default) ascending order. 
The GROUP BY operation aggregates data in your result set. Continuing the example of the telephone directory 
SELECT CITY, COUNT(CITY) FROM DIRECTORY GROUP BY CITY 
This would ensure that the result set would be grouped according to the city where the individual lives. The COUNT and GROUP BY works in conjunction. 

 

 


4. Which of the following records all modifications to data? 

A. UNDO file 
B. Alert Log file 
C. Archive file 
D. Both A & B 
Answer: C
Alert log file records all modifications to the database but modifications to data alone is recorded by Archive files. UNDO file stores UNDO tables which have opposite transactions recorded. Archive files also help in recovery of data. 

 

 


5. Which is better ? 

A. SQL 
B. Procedures 
Answer: SQL

• SQL is often much shorter to write - you can do an update or summary procedure in one line of code that would take you several lines of procedural.
• For set-based problems - SQL is much faster processor-wise and IO wise too because all the underlining looping iteration is delegated to a database server process that does it in a very low level way and uses IO/processor more efficiently and knows the current state of the data - e.g. what other processes are asking for the data
If you were to update say a sales person of all customers in a particular region - your procedural way would look something like this 
do until eof 
if rs("state") = "NH" then 
rs("salesperson") = "Mike" 
end if 
rs.next 
loop 
The SQL way would be: UPDATE customers SET salesperson = "Mike" WHERE state = "NH"
If you had, say 2 or 3 tables you need to check, your procedural quickly becomes difficult to manage as you pile on nested loop after loop.

feedback