Discussion
Home ‣ Interview ‣ Technology Comments
- Question
What are aggregate functions in SQL? What are those functions?
- Correct Answer
- Aggregate functions in SQL are used to perform calculation on data These functions are inbuilt in SQL and return a single value SUM( ) SUM function returns the sum or addition of all NOT NULL values of a column For eg I have a Table employee with the fields id, name, salary and I want the sum of all salaries, I can use SUM function as shown SELECT SUM(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 63,000 AVG( ) AVG function returns the average of all NOT NULL values of a column For eg I have a Table employee with the fields id, name, salary and I want the average of all salaries, I can use AVG function as shown SELECT AVG(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 21,000 COUNT( ) COUNT function returns the number of rows or values of a table For eg I have a Table employee with the fields id, name, salary and I want the count of all rows, I can use COUNT function as shown SELECT COUNT(*) from employee; Max ( ) and Min ( ) MAX function returns the largest value of a column in a table For eg I have a Table employee with the fields id, name, salary and I want the maximum salary of an employee, I can use MAX function as shown SELECT MAX(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 22,000 MIN function returns the smallest value of a column in a table For eg I have a Table employee with the fields id, name, salary and I want the minimun salary of an employee, I can use MIN function as shown SELECT MIN(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 20,000
- 1. Explain Row level and statement level trigger. Discuss
- 2. Explain the difference between trigger and stored procedure. Discuss
- 3. What do you mean by Redo Log file mirroring ? Discuss
- 4. What are the varoius components of physical database structure of Oracle database? Discuss
- 5. What is a sub query? What are its various types? Discuss
- 6. What are Number Functions in SQL? Discuss
- 7. What are the basic SQL*Plus commands? Discuss
- 8. What is AFIEDT.BUF? Discuss
- 9. How to Add a new column to an existing table? Discuss
- 10. How to recover a dropped table? Discuss
Technology problems
Search Results
Correct Answer: Row Level Trigger : Row Level Trigger is fired each time row is affected by Insert, Update or Delete command If statement doesn?t affect any row, no trigger action happens Statement Level Trigger : This kind of trigger fires when a SQL statement affects the rows of the table The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement
Correct Answer: - A stored procedure can accept parameters while a trigger cannot - A trigger can?t return any value while stored procedures can - A trigger is executed automatically on some event while a stored procedure needs to be explicitly called - Triggers are used for insertions, update and deletions on tables while stored procedures are often using independently in the database - A trigger cannot be written in a stored procedure However, the reverse is not possible
Correct Answer: - The process of having a copy of redo log files is called mirroring - It is done by creating group of log files together This ensures that LGWR automatically writes them to all the members of the current on-line redo log group - In case a group fails, the database automatically switches over to the next group It diminishes the performance
Correct Answer: Oracle database comprises of three kinds of files: - Datafiles - Redo log files - Control files
Correct Answer: - Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables - A sub query is added in the where clause of the main query There can be two types of subqueries: a) Correlated sub query : - It can reference column in a table listed in the from list of the outer query but is not as independent as a query b) Non-Correlated sub query : - Results of this sub query are submitted to the main query or parent query - It is independent like a query
Correct Answer: - ABS(number) Returns the absolute positive value of an expression Syntax: ABS(expression) Example: SELECT ABS(-10), ABS(00), ABS(10) Output: 10 0 10 - CEIL(number) Returns the smallest integer greater than, or equal to, the specified numeric expression Syntax: CEILING(expression) Example: SELECT CEILING($22345), CEILING($-22345), CEILING($00) Output: 22400 -22300 000 - FLOOR(number) Returns the largest integer less than, or equal to, the specified numeric expression Syntax: FLOOR(expression) Example: SELECT FLOOR($22345), CEILING($-22345), CEILING($00) Output: 22300 -22400 000 - MOD(number, divisor) Returns the remainder of the division from 2 integer values Syntax: MOD(dividend, divisor) Example: SELECT MOD(20,3) Output: 2 - POWER(number, power) Returns the exponential value for the numeric expression Syntax: POWER(number, power) Example: SELECT POWER(20, 30) Output: 80 - SIGN(number) Returns the sign ie positive or negative value for the numeric expression It returns -1 for negative expressions, a value of 0 for zero Syntax: SIGN(number) Example: SELECT SIGN(4) Output: 1 - ROUND(number, precision) Returns the numeric value rounded off to the next value specified Syntax: ROUND(number, number of places) Example: SELECT ROUND(13456, 2) - SQRT(number) Returns the square root value of the expression Syntax: SQRT(number) Example: SELECT SQRT(40) Output: 20 - TRUNC(number, precision) Returns a numeric value that truncate to the specific places Syntax: TRUNCATE(number,places) Example: SELECT TRUNCATE(13456, 2) Output: 134
Correct Answer: 1) START- Used to run a SQL script 2) ACCEPT- Accepts input from user 3) GET- Gets the sql file from user to place in buffer 4) LIST- Displays the last command executed 5) RUN- Used to list and run the command in buffer 6) SHOW- Shows the environment settings Example: SHOW PAGESIZE
Correct Answer: When any file is attempted to edit without any arguments (example ed or edit), the last SQL command is saved in AFIEDTBUF, It acts like a default buffer file for edit commands
Correct Answer: New column can be added using the ALTER TABLE command as shown below: Example: ALTER TABLE employee ADD DateOfBirth date
Correct Answer: Dropped tables can be recovered using DROP TABLE flashback It works the way recycle bin works Example: FLASHBACK TABLE EMPLOYEE TO BEFORE DROP; The most recently dropped table with that original name is retrieved from the recycle bin, with its original name
Comments
There are no comments.More in Interview:
Programming
Copyright ©CuriousTab. All rights reserved.