AWS Solution Architect Associate - Tricky Questions and Analysis

 This post is created for discussing tricky questions for AWS Solution Architect Associate . We will discuss the solution throughly here. Please put your questions in comment , will try to answer asap. 

Let's Learn together and get certified as AWS Solution Architect Associate.

Note: I will keep adding new questions here.

1. The disk configuration for an Amazon EC2 instance must be finalized. The instance will be running an application that requires heavy read/write IOPS. A single volume is required that is 500 GiB in size and needs to support 30,000 IOPS.

What EBS volume type should be selected?

A) EBS Throughput Optimized HDD  

B) EBS General Puprose SSD

c) EBS Provisioned IOPS SSD

d) EBS General Purpose SSD in Raid 2 config    

51 PYTHON INTERVIEW QUESTIONS - (Level 0)

1) What is Python? What are the benefits of using Python?

 Python is a programming language with objects, modules, threads, exceptions and automatic memory management. The benefits of pythons are that it is simple and easy, portable, extensible, build-in data structure and it is an open source. 

2) What is PEP 8? 

 PEP 8 is a coding convention, a set of recommendation, about how to write your Python code more readable.

 3) What is pickling and unpickling? 

 Pickle module accepts any Python object and converts it into a string representation and dumps it into a file by using dump function, this process is called pickling. While the process of retrieving original Python objects from the stored string representation is called unpickling. 

 4) How Python is interpreted?


 Python language is an interpreted language. Python program runs directly from the source code. It converts the source code that is written by the programmer into an intermediate language, which is again translated into machine language that has to be executed. 

 5) How memory is managed in Python?


 Python memory is managed by Python private heap space. All Python objects and data structures are located in a private heap. The programmer does not have an access to this private heap and interpreter takes care of this Python private heap.


 The allocation of Python heap space for Python objects is done by Python memory manager. The core API gives access to some tools for the programmer to code.


 Python also have an inbuilt garbage collector, which recycle all the unused memory and frees the memory and makes it available to the heap space.

6) What are the tools that help to find bugs or perform static analysis?


 PyChecker is a static analysis tool that detects the bugs in Python source code and warns about the style and complexity of the bug. Pylint is another tool that verifies whether the module meets the coding standard.

 7) What are Python decorators?


 A Python decorator is a specific change that we make in Python syntax to alter functions easily.

 8) What is the difference between list and tuple?


 The difference between list and tuple is that list is mutable while tuple is not. Tuple can be hashed for e.g as a key for dictionaries. 

 9) How are arguments passed by value or by reference?


 Everything in Python is an object and all variables hold references to the objects. The references values are according to the functions; as a result you cannot change the value of the references. However, you can change the objects if it is mutable. 

 10) What is Dict and List comprehensions are?


 They are syntax constructions to ease the creation of a Dictionary or List based on existing iterable. 

 11) What are the built-in type does python provides?


 There are mutable and Immutable types of Pythons built in types 

Mutable built-in types
 List
 Sets
 Dictionaries


 Immutable built-in types
 Strings
 Tuples
 Numbers 

 12) What is namespace in Python?


 In Python, every name introduced has a place where it lives and can be hooked for. This is known as namespace. It is like a box where a variable name is mapped to the object placed. Whenever the variable is searched out, this box will be searched, to get corresponding object. 

 13) What is lambda in Python?


 It is a single expression anonymous function often used as inline function. 

 14) Why lambda forms in python does not have statements?


 A lambda form in python does not have statements as it is used to make new function object and then return them at runtime. 

 15) What is pass in Python?


 Pass means, no-operation Python statement, or in other words it is a place holder in compound statement, where there should be a blank left and nothing has to be written there.

 16) In Python what are iterators?


 In Python, iterators are used to iterate a group of elements, containers like list.

 17) What is unittest in Python?


 A unit testing framework in Python is known as unittest. It supports sharing of setups, automation testing, shutdown code for tests, aggregation of tests into collections etc. 

 18) In Python what is slicing?


 A mechanism to select a range of items from sequence types like list, tuple, strings etc. is known as slicing. 

 19) What are generators in Python?


 The way of implementing iterators are known as generators. It is a normal function except that it yields expression in the function. 

 20) What is docstring in Python?


 A Python documentation string is known as docstring, it is a way of documenting Python functions, modules and classes.

 21) How can you copy an object in Python?


 To copy an object in Python, you can try copy.copy () or copy.deepcopy() for the general case. You cannot copy all objects but most of them.

 22) What is negative index in Python?


 Python sequences can be index in positive and negative numbers. For positive index, 0 is the first index, 1 is the second index and so forth. For negative index, (-1) is the last index and (-2) is the second last index and so forth. 

 23) How you can convert a number to a string?


 In order to convert a number into a string, use the inbuilt function str(). If you want a octal or hexadecimal representation, use the inbuilt function oct() or hex(). 

 24) What is the difference between Xrange and range?


 Xrange returns the xrange object while range returns the list, and uses the same memory and no matter what the range size is. 

 25) What is module and package in Python?


 In Python, module is the way to structure program. Each Python program file is a module, which imports other modules like objects and attributes.


 The folder of Python program is a package of modules. A package can have modules or subfolders. 

 26) Mention what are the rules for local and global variables in Python?


 Local variables: If a variable is assigned a new value anywhere within the function’s body, it’s assumed to be local.


 Global variables: Those variables that are only referenced inside a function are implicitly global.

 27) How can you share global variables across modules?


 To share global variables across modules within a single program, create a special module. Import the config module in all modules of your application. The module will be available as a global variable across modules. 

 28) Explain how can you make a Python Script executable on Unix?


 To make a Python Script executable on Unix, you need to do two things,


 Script file’s mode must be executable and


 the first line must begin with # ( #!/usr/local/bin/python) 

 29) Explain how to delete a file in Python?


 By using a command os.remove (filename) or os.unlink(filename) 

30) Explain how can you generate random numbers in Python?


 To generate random numbers in Python, you need to import command as import random random.random()


 This returns a random floating point number in the range [0,1) 

 31) Explain how can you access a module written in Python from C?


 You can access a module written in Python from C by following method,


 Module = =PyImport_ImportModule(“”); 

 32) Mention the use of // operator in Python?


 It is a Floor Divisionoperator , which is used for dividing two operands with the result as quotient showing only digits before the decimal point. For instance, 10//5 = 2 and 10.0//5.0 = 2.0. 

 33) Mention five benefits of using Python?


 Python comprises of a huge standard library for most Internet platforms like Email, HTML, etc.


 Python does not require explicit memory management as the interpreter itself allocates the memory to new variables and free them automatically
 Provide easy readability due to use of square brackets


 Easy-to-learn for beginners


 Having the built-in data types saves programming time and effort from declaring variables

 34) Mention the use of the split function in Python?


 The use of the split function in Python is that it breaks a string into shorter strings using the defined separator. It gives a list of all words present in the string. 

 35) Is python the right choice for Web based Programming?


 Python is another open source programming that has become popular for creating web-related applications and large programs. Scripts written in Python are often very clear to read; the language is also known for its flexibility. Whether you are looking for database tools, image manipulation scripts, or something else entirely, if it is written in Python, you will find it here. 

 36) What are uses of lambda?


 It used to create small anonymous functions at run time. 

Like e.g.
 def fun1(x):

                  
 return x**2
 

                   print fun1(2)
 

it gives you answer 4
 

the same thing can be done using
 

sq=lambda x: x**2
 print sq(2)
 it gives the answer 4 

 37) When you need ordered container of things, which will be manipulated, use lists.


 Dictionary is key, value pair container and hence is not ordered. Use it when you need fast access to elements, not in ordered fashion. Lists are indexed and index of the list cannot be “string” e.g. list [‘myelement’] is not a valid statement in python. 

 38) When do you use list vs. tuple vs. dictionary vs. set?


 List and Tuple are both ordered containers. If you want an ordered container of constant elements use tuple as tuples are immutable objects. 

 39) Do they know a tuple/list/dict when they see it?


 Dictionaries are consisting of pair of keys and values.like {’key’:’value’}.


 book={’cprog’:’1024′,’c++’:’4512′}


 Keys are unique but values can be same. 

The main difference between list and tuple is you can change the list but you cannot change the tuple. Tuple can be used as keys in mapping where list is not. 

 40) What is used to represent Strings in Python? Is double quotes used for String representation or single quotes used for String representation in Python?


 Using Single Quotes (‘)


 You can specify strings using single quotes such as ‘Quote me on this’ . All white space i.e. spaces and tabs are preserved as-is.


 Using Double Quotes (“)


 Strings in double quotes work exactly the same way as strings in single quotes. An example is “What’s your name?”


 Using Triple Quotes (”’ or “””)


 You can specify multi-line strings using triple quotes. You can use single quotes and double quotes freely within the triple quotes. An example is


 ”’This is a multi-line string. This is the first line.


 This is the second line.
 “What’s your name?,” 

I asked.
 He said “Bond, James Bond.” 

 41) Who created the Python programming language?


 Python programming language was created by Guido van Rossum.

 42) Why cannot lambda forms in Python contain statements?


 A lambda statement is used to create new function objects and then return them at runtime that is why lambda forms in Python did not contain statement. 

 43) How is memory managed in Python?


 Memory is managed through private heaps. Private heap is managed by python memory manager. 

 44) Which of the languages does Python resemble in its class syntax?


 C++ is the appropriate language that Python resemble in its class syntax. 

 45) Why was the language called as Python?


 At the same time he began implementing Python, Guido van Rossum was also reading the published scripts from “Monty Python’s Flying Circus” (a BBC comedy series from the seventies, in the unlikely case you didn’t know). It occurred to him that he needed a name that was short, unique, and slightly mysterious, so he decided to call the language Python.

 46) Does Python support strongly for regular expressions? What are the other languages that support strongly for regular expressions?


 Yes, python strongly support regular expression. Other languages supporting regular expressions are: Delphi, Java, Java script, .NET, Perl, Php, Posix, python, Ruby, Tcl, Visual Basic, XML schema, VB script, Visual Basic 6. 

 47) Why is not all memory freed when Python exits?


 Objects referenced from the global namespaces of Python modules are not always de-allocated when Python exits. This may happen if there are circular references. There are also certain bits of memory that are allocated by the C library that are impossible to free (e.g. a tool like the one Purify will complain about these). Python is, however, aggressive about cleaning up memory on exit and does try to destroy every single object. If you want to force Python to delete certain things on de- allocation, you can use the at exit module to register one or more exit functions to handle those deletions. 

 48) What are the disadvantages of the Python programming language?


 One of the disadvantages of the Python programming language is it is not suited for fast and memory intensive tasks. 

 49) What is the language from which Python has got its features or derived its features?


 Most of the object oriented programming languages to name a few are C++, CLISP and Java is the language from which Python has got its features or derived its features. 

 50) Does python support switch or case statement in Python? If not what is the reason for the same?


 No. You can use multiple if-else, as there is no need for this.

51) If you have module imported in your code, still your program fails with error - "Imported module not found" ?


 Please check your python path if it's pointing to the correct path and set the path as below

EXPORT PYTHONPATH='<location to imported python path>'

Please like, comment and share if you find this useful.

51 ORACLE SQL PL/SQL INTERVIEW QUESTIONS

Hi Guys, I have come up with my new post to put Oracle interview questions which I came across during my interview with various companies. I believe that this post will be helpful to freshers as well as experience candidate in interview. These interview questions are mostly straight forward. I will come up with more tricky questions later. Please post your comments and suggestions ,which will make learning better for all. Some questions are having answer and some are not in below list of questions .You can post queries and doubts related with questions below, so I can answer them. Please let me know if you like the post, so you can see more post on Oracle!

Keep Sharing,Keep Learning guys :)

51 Oracle Interview Questions and Answers -

1. Reference Cursor ?

A reference cursor is a pointer to a memory location that can be passed 
between different PL/SQL clients, thus allowing query result sets to be 
passed back and forth between clients.

A reference cursor is a variable type defined using the PL/SQL TYPE 
statement within an Oracle package, much like a PL/SQL table:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

Here, ref_type_name is the name given to the type and return_type represents 
a record in the database. You do not have to specify the return type as this 
could be used as a general catch-all reference cursor. Such non-restrictive 
types are known as weak, whereas specifying the return type is restrictive, 
or strong. The following example uses %ROWTYPE to define a strong return 
type that represents the record structure of the emp table:

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;



================================================================================
2. Anonymous Block ?
An unnamed PL/SQL block is called an anonymous block.

A PL/SQL block contains three parts or sections. They are:
• The optional Declaration section.
• The mandatory Execution section.
• The optional Exception (or Error) Handling
section.

================================================================================
3. Explain how cursor is implemented in PL/SQL



A cursor is a variable that runs through the rows of some table or answer to 
some query.

CURSOR employee_cur IS SELECT * FROM employee;

Once I have declared the cursor, I can open it:
OPEN employee_cur;

And then I can fetch rows from it:
FETCH employee_cur INTO employee_rec;

and, finally, I can close the cursor:
CLOSE employee_cur;

***********
DECLARE
   CURSOR joke_feedback_cur
   IS
      SELECT J.name, R.laugh_volume, C.name
        FROM joke J, response R, comedian C
       WHERE J.joke_id = R.joke_id
         AND J.joker_id = C.joker_id;
BEGIN
   ...
END;

================================================================================
4. Constraints ?


Integrity constraints are used to enforce Business logic.
Enforcing rules with integrity constraints is less costly than enforcing the 
equivalent rules by issuing SQL statements in the application.

Only define NOT NULL constraints for columns of a table that absolutely 
require values at all times.

Use the combination of NOT NULL and UNIQUE key integrity constraints to 
force the input of values in the UNIQUE key; this combination of data 
integrity rules eliminates the possibility that any new row's data will ever 
attempt to conflict with an existing row's data.

Choosing a Table's Primary Key :
Each table can have one primary key. A primary key allows each row in a 
table to be uniquely identified and ensures that no duplicate rows exist. 
Use the following guidelines when selecting a primary key:

Choose a column whose data values are unique.
The purpose of a table's primary key is to uniquely identify each row of the 
table. Therefore, the column or set of columns in the primary key must 
contain unique values for each row.

Choose a column whose data values are never changed.
A primary key value is only used to identify a row in the table; primary key 
values should never contain any data that is used for any other purpose. 
Therefore, primary key values should rarely need to be changed.

Choose a column that does not contain any nulls.
A PRIMARY KEY constraint, by definition, does not allow the input of any row 
with a null in any column that is part of the primary key.

Choose a column that is short and numeric.
Short primary keys are easy to type. You can use sequence numbers to easily 
generate numeric primary keys.

Avoid choosing composite primary keys.
Although composite primary keys are allowed, they do not satisfy the 
previous recommendations. For example, composite primary key values are long 
and cannot be assigned by sequence numbers.

Foreign Key(Referential Integrity): Whenever two tables are related by a 
common column (or set of columns), define a PRIMARY or UNIQUE key constraint 
on the column in the parent table, and define a FOREIGN KEY constraint on 
the column in the child table, to maintain the relationship between the two 
tables.

Using CHECK Integrity Constraints
Use CHECK constraints when you need to enforce integrity rules that can be 
evaluated based on logical expressions. Never use CHECK constraints when any 
of the other types of integrity constraints can provide the necessary 
checking.

*The condition must be a Boolean expression that can be evaluated using the 
values in the row being inserted or updated.
*The condition cannot contain subqueries or sequences.
*The condition cannot include the SYSDATE, UID, USER, or USERENV SQL 
functions.
*The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM;
*The condition cannot contain a user-defined SQL function.

Eg.
CREATE TABLE dept (

deptno  NUMBER(3) PRIMARY KEY,
dname   VARCHAR2(15),
loc     VARCHAR2(15),
        CONSTRAINT dname_ukey UNIQUE (dname, loc),
        CONSTRAINT loc_check1
                CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));

================================================================================
5. Difference between Primary Key and Unique key ?

*  Primary keys are used to identify each row of the table uniquely. Unique 
keys should not have the purpose of identifying rows in the table.
* A primary key field cannot be Null, whereas a Unique column can have a 
Null value.
* There could be only 1 Primary Key per table, but there could be any number 
of Unique Key columns.
* A primary key should be a Unique column, but all Unique Key column need 
not be a PRimary Key
================================================================================
6. what are diffenent Joins explain . outer ? how would you write outer 
join(=+)
   if specify + to the right of equal to sign, which table data will be in 
full.

A join is a query that combines rows from two or more tables.
An equijoin is a join with a join condition containing an equality operator. 
( where A.b = C.d)
A self join is a join of a table to itself.
Cartesian Products
If two tables in a join query have no join condition, then Oracle returns 
their Cartesian product. Oracle combines each row of one table with each row 
of the other. A Cartesian product always generates many rows and is rarely 
useful. For example, the Cartesian product of two tables, each with 100 
rows, has 10,000 rows. Always include a join condition unless you 
specifically need a Cartesian product. If a query joins three or more tables 
and you do not specify a join condition for a specific pair, then the 
optimizer may choose a join order that avoids producing an intermediate 
Cartesian product.

An inner join (sometimes called a "simple join") is a join of two or more 
tables that returns only those rows that satisfy the join condition.

An outer join returns all rows that satisfy the join condition and also 
returns some or all of those rows from one table for which no rows from the 
other satisfy the join condition.

To write a query that performs an outer join of tables A and B and returns 
all rows from A( left outer join), apply the outer join operator (+) to all 
columns of B in the join condition.
For example,
SELECT ename, job, dept.deptno, dname
    FROM emp, dept
    WHERE emp.deptno (+) = dept.deptno;

will select all departments even if there is no employee for a particular 
dept.

Similarly to get all rows from B form a join of A & B , apply the outer join 
operator(+) to A.
Example,
    SELECT ename, job, dept.deptno, dname
    FROM emp, dept
    WHERE emp.deptno = dept.deptno (+)

================================================================================
7. Is a function in SELECT possible ?
Yes. FOr example, Select ltrim(price) from Item.
User defined functions work only from Oracle 8i onwards.
================================================================================
8. EXCEPTION handling in PL/SQL

An exception is a runtime error or warning condition, which can be 
predefined or user-defined. Predefined exceptions are raised implicitly 
(automatically) by the runtime system. User-defined exceptions must be 
raised explicitly by RAISE statements. To handle raised exceptions, you 
write separate routines called exception handlers.

Example of an exception handler:
DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';  -- might cause division-by-zero error
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
   COMMIT;
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
      COMMIT;
   ...
   WHEN OTHERS THEN  -- handles all other errors
      ROLLBACK;
END;  -- exception handlers and block end here


Unlike predefined exceptions, user-defined exceptions must be declared and 
must be raised explicitly by RAISE statements.
Example:
DECLARE
   past_due EXCEPTION;
   acct_num NUMBER;
BEGIN
     ...
     ...
     IF ... THEN
         RAISE past_due;  -- this is not handled
      END IF;

EXCEPTION
   WHEN past_due THEN  -- does not handle RAISEd exception
   WHEN OTHERS THEN    -- handle all other errors

END;


The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error 
messages from stored subprograms. That way, you can report errors to your 
application and avoid returning unhandled exceptions.

To call RAISE_APPLICATION_ERROR, use the syntax

raise_application_error(error_number, message[, {TRUE | FALSE}]);


where error_number is a negative integer in the range -20000 .. -20999 and 
message is a character string up to 2048 bytes long. If the optional third 
parameter is TRUE, the error is placed on the stack of previous errors. If 
the parameter is FALSE (the default), the error replaces all previous 
errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as 
with package STANDARD, you do not need to qualify references to it.

An application can call raise_application_error only from an executing 
stored subprogram (or method). When called, raise_application_error ends the 
subprogram and returns a user-defined error number and message to the 
application. The error number and message can be trapped like any Oracle 
error.

Another way to handle exception is to use DECODE statements. For example:
INSERT INTO stats (symbol, ratio)
   SELECT symbol, DECODE(earnings, 0, NULL, price / earnings)
   FROM stocks WHERE symbol = 'XYZ';

Here the DECODE function checks whether the earnings is 0 and if it is zero, 
then returns earnings, else price/earnings.
================================================================================
9. When an exception is triggered in a loop, how you
continue to next itereration ?
By having an exception andling block within the loop.

================================================================================
10. Decode ? Using Decode map this logic,

If A>B Display 'A is Big', If A=B Display 'A equals B'
Else 'B is Big'

DECODE(Greatest(A,B), A, 'A is big',
                      B, 'B is Big',
                         'A equals B')

================================================================================
11. What is an index? what are diff type of indices? what is Clustered and 
Non Clustered Indeces ?

Indexing is typically listing of keywords alongwith its location and is done 
to speed up the Data base.

Create index WORKERSKILL_NAME_SKILL on WORKERSKILL(Name,Skill);

A clustered index is a special type of index that reorders the way records 
in the table are physically stored. Therefore table can have only one 
clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order 
of the index does not match the physical stored order of the rows on disk. 
The leaf node of a nonclustered index does not consist of the data pages. 
Instead, the leaf nodes contain index rows.
================================================================================
12. Mutating Error ?
When we use a row level triger on a table, and at the same time if we 
query/insert/delete/update on the same table, it will give the mutating 
error.

================================================================================
13. How to avoid mutating error?
Two possible solutions.
1. Change the design of the table so as to avoid querying or any update on 
the table on which the row level trigger is holding on.
2. Create a package which holds a PL/SQL table with the RowID of the rows 
need to insert/update and a counter of the rows
a. In BEFORE STATEMENT trigger set the counter to zero.
b. In every ROW trigger call a procedure from the package which will 
register the RowId and modify the counter
c. In AFTER STATEMENT trigger call a procedure which will make the needed 
checks and update the particular rows of the table.
If you are not familiar with PL/SQL tables alternatively you can use 
temporary Oracle tables. It is also a good idea.

================================================================================
14. Difference between Delete and Truncate.
Truncate will permenantly delete the record and no rollback capability will 
exist.
The delete command remove records from a table. Delete moves records to the 
rollback segment enabling rollback capability.
Excessive use of triggers can result in complex interdependencies, which can 
be difficult to maintain in a large application.


================================================================================
15. How many types of triggers are there and what are ? (Before Insert, 
after Insert, update,
delete etc..)

Triggers are similar to stored procedures. A trigger stored in the database 
can include SQL and PL/SQL or Java statements to run as a unit and can 
invoke stored procedures. However, procedures and triggers differ in the way 
that they are invoked. A procedure is explicitly run by a user, application, 
or trigger. Triggers are implicitly fired by Oracle when a triggering event 
occurs, no matter which user is connected or which application is being 
used.
Different Types
---------------
Row Triggers and Statement Triggers
BEFORE and AFTER Triggers
INSTEAD OF Triggers ( ON views,for INSERT etc, instaed of Inserting, it will 
carry out the trigger statements)
Triggers on System Events(shut down) and User Events ( log on)
================================================================================
16. What all composite datatypes in oracle
Composite Data types:

Table

1. Is similar but not the same as a database table
2. Must contain only one column of any scalar datatype
3. Is like a one-dimensional array of any size
4. Has its elements indexed with a binary integer column called the primary 
key of the table

Record

1. Contains uniquely defined columns of different data types,
2. Enables us to treat dissimilar columns that are logically related as a 
single unit.

================================================================================
17. what is PL/SQL tables?


================================================================================
18. What are the default packages provided by oracle
The ones with "DBMS_" prefix. Eg. DBMS_Output, DBMS_ALERT

================================================================================
19. What is the diff between Where and Having

Having clause is used where group by clasue is used to restrict the groups 
of returned rows to those groups for which the specified condition is TRUE.
The HAVING condition cannot contain a scalar subquery expression
eg:
SELECT department_id, MIN(salary), MAX (salary)
   FROM employees
   GROUP BY department_id
   HAVING MIN(salary) < 5000;

================================================================================
20. How is error Handling done?


================================================================================
21. I have table that contains state codes,
I might have more than 1 rows with the same state code, how can I find out?
select count(*), state_code from table. IF this is > 1 , then we can assure 
that there are duplicates.


================================================================================
22. How do you copy rows from Schema a , table p to
table p in schema b ?
To copy between tables on a remote database, include the same username, 
password, and service name in the FROM and TO clauses:

COPY FROM HR/your_password@SchemaA -
TO HR/your_password@SchemaB -
INSERT tableP-
USING SELECT * FROM tableP


================================================================================
23. What is %Rowtype and %Type

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)
..Means the INput Parameter Dept_num should have the same Data Type as 
Emp_tab.DeptNo.

Use the %ROWTYPE attribute to create a record that contains all the columns 
of the specified table. The following example defines the Get_emp_rec 
procedure, which returns all the columns of the Emp_tab table in a PL/SQL 
record for the given empno:

PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                       Emp_ret     OUT Emp_tab%ROWTYPE) IS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;

You could call this procedure from a PL/SQL block as follows:

DECLARE
   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp_tab table
BEGIN
   Get_emp_rec(7499, Emp_row);   -- call for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || 
Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);
   DBMS_OUTPUT.NEW_LINE;
END;



================================================================================
24. How to use the cursor with using open, fetch and close.?

================================================================================
25. using select Statement how you will retrieve the user who is logged in?

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
FROM DUAL;

================================================================================
26. When Exception occurs, i want to see the error generated by Oracle. How 
to see it?
SQLERRM
================================================================================
27. how to generate the out of the select statement in a file?
SPOOL

================================================================================
28. how to find the duplicate records?

select count(*), job from emp group by job having count(*) > 1

then there are duplicates.
================================================================================
29. What is Procedure, function, package and anonymous block?

Procedures are named groups of SQl statements.
Functions are like Procedures, but can return a value.
Packages are groups of procedures, functions, variables and SQL statements.
An unnamed PL/SQL block is called an anonymous block.
================================================================================
30. What is group by funcion and where it is used.?
Specify the GROUP BY clause if you want Oracle to group the selected rows 
based on the value of expr(s) for each row and return a single row of 
summary information for each group.
Expressions in the GROUP BY clause can contain any columns of the tables, 
views, or materialized views in the FROM clause, regardless of whether the 
columns appear in the select list.


It is used for aggraegate functions.

================================================================================
31. What is the difference between RDBMS and DBMS? Sol: Chords 12 rules.



================================================================================
32. What is normalised and denormalised data?
process of removing redundancy in data by separating the data into multiple 
tables.


================================================================================
33. What is a VIEW?
A view is a custom-tailored presentation of the data in one or more tables. 
A view can also be thought of as a "stored query." Views do not actually 
contain or store data; they derive their data from the tables on which they 
are based.

================================================================================
34. Can a view update a table?

Can, but with restrictions.
Like tables, views can be queried, updated, inserted into, and deleted from, 
with some restrictions.
All operations performed on a view affect the base tables of the view.
================================================================================
35. What happens if there is an exception in the cursor?
How do we ensure that the execution for other records in the cursor does not 
stop.



================================================================================
36. What are cursors ? After retrieving the records into the cursor can we 
update
the record in the table for the retrieved record. What effect will it
have on the cursor?

Cursors
Oracle uses work areas to execute SQL statements and store processing 
information. A PL/SQL construct called a cursor lets you name a work area 
and access its stored information. There are two kinds of cursors: implicit 
and explicit. PL/SQL implicitly declares a cursor for all SQL data 
manipulation statements, including queries that return only one row. For 
queries that return more than one row, you can explicitly declare a cursor 
to process the rows individually. An example follows:

DECLARE
   CURSOR c1 IS
      SELECT empno, ename, job FROM emp WHERE deptno = 20;


The set of rows returned by a multi-row query is called the result set. Its 
size is the number of rows that meet your search criteria
Multi-row query processing is somewhat like file processing. For example, a 
COBOL program opens a file, processes records, then closes the file. 
Likewise, a PL/SQL program opens a cursor, processes rows returned by a 
query, then closes the cursor. Just as a file pointer marks the current 
position in an open file, a cursor marks the current position in a result 
set.

You use the OPEN, FETCH, and CLOSE statements to control a cursor. The OPEN 
statement executes the query associated with the cursor, identifies the 
result set, and positions the cursor before the first row. The FETCH 
statement retrieves the current row and advances the cursor to the next row. 
When the last row has been processed, the CLOSE statement disables the 
cursor.


================================================================================
37. What are user defined data types ?

SUBTYPE subtype_name IS base_type;
Eg. SUBTYPE EmpDate IS DATE;           -- based on DATE type
SUBTYPE ID_Num IS emp.empno%TYPE;  -- based on column type

   CURSOR c1 IS SELECT * FROM dept;
   SUBTYPE DeptFile IS c1%ROWTYPE;    -- based on cursor rowtype


To specify base_type, you can use %TYPE, which provides the datatype of a 
variable or database column. Also, you can use %ROWTYPE, which provides the 
rowtype of a cursor, cursor variable, or database table.

A subtype does not introduce a new type; it merely places an optional 
constraint on its base type.

================================================================================
38. How to copy the Structure and data from one table to another in one SQL 
stmt?
COPY {FROM database | TO database | FROM database TO database} 
{APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, 
...)]
USING query

where database has the following syntax:

username[/password]@connect_identifier
================================================================================

39. Describe UNION and UNION ALL.  UNION returns distinct rows selected by 
both queries while UNION ALL returns all the rows. Therefore, if the table 
has duplicates, UNION will remove them. If the table has no duplicates, 
UNION will force a sort and cause performance degradation as compared to 
UNION ALL.
================================================================================
40. What is 1st normal form?  Each cell must be one and only one value, and 
that value must be atomic: there can be no repeating groups in a table that 
satisfies first normal form.
================================================================================
41. What is 2nd normal form?  Every nonkey column must depend on the entire 
primary key.
================================================================================
42. What is 3rd normal form? (another explanation than #1)  No nonkey column 
depends on another nonkey column.
================================================================================

43. What is 4th normal form?  Fourth normal form forbids independent 
one-to-many relationships between primary key columns and nonkey columns.
================================================================================
44. What is 5th normal form?  Fifth normal form breaks tables into the 
smallest possible pieces in order to eliminate all redundancy within a 
table. Tables normalized to this extent consist of little more than the 
primary key.

45. How to find out top n salary from a employee table?
================================================================================

46.If we will modify a function , which is defined in package even though stored package will be valid ?

================================================================================

47.How do you define public and private procedure in oracle?

================================================================================

48.If same kind of logic we put in function as well as procedure then which one will be faster?

================================================================================

49.If we deleted any rows in table and commited then is it possible to retrieve the deleted data?

================================================================================

50. While handiling excecption if we have written “When others then” first before other exception handler then what will happen?

================================================================================
51. What is sql injection attack???

<form method="post" action="http://testasp.vulnweb.com/login.asp">
<input name="tfUName" type="text" id="tfUName">
<input name="tfUPass" type="password" id="tfUPass">
</form>
The easiest way for the login.asp to work is by building a database query that looks like this:
SELECT id
FROM logins
WHERE username = '$username'
AND password = '$password’
If the variables $username and $password are requested directly from the user's input, this can easily be compromised. Suppose that we gave "Joe" as a username and that the following string was provided as a password: anything' OR 'x'='x
SELECT id
FROM logins
WHERE username = 'Joe'
AND password = 'anything' OR 'x'='x'
As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause.
The 'x'='x' part guarantees to be true regardless of what the first part contains.
This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!


                                                            COPYRIGHT © 2013 TECH BRAINS

ETL Testing Interview Questions & Answers - Part 4

Advanced ETL Testing Interview Questions & Answers 

Q1. How can you create BPT test scripts?

A1. Please follow the below steps to create BPT:

First you have to go Business Components section of ALM,

1. create a new component there
2. create component steps with parameters
3. put parameter variables in bracket like <<< input1 >>>
4. check it’s reflecting in parameters
5. add details for business component
6. Make status ready once all the steps are written.

Now go to the Test Plan in ALM,

1. Create a new test script; type will be Business Process for the same.
2. Put all the required details for script
3. In test script tab, you have to pull component now which you have created (you can pull more than one component as required and integrate them in particular test script)
4. After that you can see the parameters which you defined earlier for components here (suppose you have defined 4 parameters for the component then you can see 4 i/o parameters , iteration will be default 1 )
5. If you want to check the same parameters for different iterations then you can increase the iteration here
6. Now, BPT is ready (you can see green box)

Q2. How can you run BPT test scripts?

A2. Please follow the below steps to run BPT test scripts:

First go to Test Plan in ALM,

1. Go to test script tab for BPT test scripts, click on iterations
2. Default 1 iterations is mentioned there, after clicking this, you get the parameters list
3. Define the variable value which you want to pass for all the parameters
4. If you want to add one more iteration then, click on add iteration above
5. Now you can define parameter values for added iterations as well.
6. After defining all the values, we can pull this script in test lab

Now go to Test Lab in ALM,

1. Go to the particular BPT which you want to run, click on run with manual runner
2. Now all the defined parameter values will be substituted
3. You need to follow the steps now
4. check all the iterations are checked for mentioned steps and pass it
5. BPT will be passed.

Q3. How can we link the existing defects with particular test case?

A3. Go to particular test case in Test Lab in ALM, double click on it, windows will appear. Click on Linked Defects tab, then click on Link Existing Defects then we can put defect id and click on Link. That defect will be linked with that particular test case.

Q4. Let’s suppose for making table loading faster, index will be disabled as pre-load activity, and then duplicates got loaded. Again after loading, in pos load activity index will be enabled that time it will fetch error. How to resolve this issue?

A4. You can follow the below steps to mitigate this issue –

1. First we have to disable primary constraint
2. drop the index after that
3. Remove the duplicates using rowid
4. Now create the index on the table
5. At last we can enable the primary constraint

Q5.How can we pass the variable value through command box in teradata?

A5. We can use the below query for this –

SELECT COUNT(*)

FROM <TABLE_NAME>

WHERE <date_field> = CAST('?yyyymmdd' AS DATE FORMAT 'yyyymmdd')

Q6.How can we search particular job in control M?

A6.you can follow the below steps to find out the job in control m –

1. Login to control m
2. Click customizable filter in view point tab of control m
3. Filter based on folder name and server name ( we can give wild characters here *)
4. After filtering we can see all the instances for jobs coming under that folder
5. In particular folder, all the jobs coming under that will be shown

Q7. How can we check the roles assigned to particular tables/view in teradata?

A7. We can use dbc.allrolerights table to find out the roles assigned to table/view

Select * from dbc.allrolerights where tablename= <table_name>

We can see the fields for role names, access rights, and grantor names here.

Q8. In control m, how can we run the job instantly which is in waiting due to dependencies or schedule?

A8. Do right click on that particular job, free that job and click run now.

Q9. In control m, how can we order a new job which is not scheduled?

A9.Click order button on home tab, select control m server, folder, particular job (if all jobs selected then all the jobs for particular folder will be submitted to run). Also check boxes ignore scheduling criteria and order as independent flow.

Q10. How can we modify command for particular job in control m?

A10. Do right click on particular job, go to properties, click on modify job, now we can change the commands and other things like schedule for that job.

Q11. In Tivoli, some job is stuck due to dependencies and resource constraints then how can we run that job?

A11. Right click on job stream, increase the priority to 101, release all dependencies, and remove time restrictions, then job should run. If still it’s stuck then do the same thing at job level; it will definitely run.

Q12. In Tivoli, how can we submit the jobstream which are not scheduled?

A12. Go to default database jobstream , search that particular jobstream , you can submit that jobstream from there. It will be reflected in scheduled job stream.

Note : If jobstream is in draft mode, then we can't submit that job. Jobstream should be active then only we can submit to plan. 

Basic ETL Testing Interview Questions and Answers - Part 3

Basic Data Warehouse Testing Interview Questions and Answers - Part 3

Let's Continue discussing basic Conceptual ETL or Data Warehouse Interview Questions and answers...

=================================================================





Q43. How to delete one year older file in Unix?









A43. Find ./your_dir  -mtime +365  -type  f  -delete









Note : f-> file only, -mtime +365 -> 365 days older 


=================================================================



Q44.  What's the difference between delete and truncate command?



A44 : Difference are given below -



a) Delete is dml statement (which requires commit after that) while Truncate is ddl statement (which is autocommit) 





b) In Delete statement we can put conditions, so we can delete whatever required; while Truncate deletes all the data in tables ( we can't put conditions here)




c) Delete  activates a trigger as the individual row deletions are logged, while truncate never activates a trigger (no logs maintained) that's why rollback is possible in Delete but not in Truncate.




d) Truncate tables always locks the table while Delete Statement uses rowlock before deleting the row.




e)Truncate is faster than delete as it doesn't keep the logs. 




=================================================================




Q45.Write a query for the below -  

           To Find out the name of the employees whose salary is greater than their

           manager salary.



A45 Query-



Select EMP.name from Employee Emp, Employee Mgr

where EMP.managerid = Mgr.empid

and Emp.sal > Mgr.sal ;





================================================================= 

Q46. What is BPT (Business Process testing) ?





A46. BPT stands for business process testing. It enables SMEs to design test script early in the development cycle. It consist of reusable business components which got converted into business process test.





=================================================================



Q47. How can we add partitions in Teradata? Please tell me the query.



A47. Query -



Alter table table_name

modify primary index

Add range between date '2014-04-01' and date '2015-06-01'  Each interval '1' Month;



=================================================================



Q48. What is multi-file system (MFS) ? How can we copy a mfs file?



A48. Multi-file system(MFS) is a file which is divided into different files . Using MFS file we can process a big input file in efficient manner as files are divided. So, we can process the divided files parallel and make the processing faster.



We can use the below commands to copy MFS file -



m_cp file1 file2



=================================================================



Q48. You worked on Informatica then tell me the difference between FILTER  and ROUTER ?



A48. There are many differences between FILTER and Router, which are given below -



 a) Filter transformation has a single i/p and single o/p while Router has single i/p and multiple o/p

 b) Router is like Case statement in database while filter is like where clause in database

 c) Router transformation doesn't block any row i.e no. of rows equal in i/p and o/p while filter block rows i.e no. of rows in o/p <= no. of rows in i/p



=================================================================



Q49. In Abinitio graph , how can we check DML and XFR for the transformation?



A49. Right Click on Transformation Properties -> go to file layout -> check path for dml

        (or embedded DML)



       Right Click on Transformation Properties -> check transformation logic -> check path for       XFR(or embedded logic)



=================================================================





Q50. Are you aware of IBM Tivoli Scheduler..How can get the Job stream in plan  ? How can we execute that job stream even though dependencies are there?





A50. IBM TIVOLI is used to schedule the job streams which calls the wrapper script/graph to perform the job.

Go to database of job stream and then submit the particular job stream from there; it will appear in the Plan. We can increase the priority to high(101) and release all the dependencies to run that stream.



=================================================================



Q51. What do you understand by full load and incremental load? 



A52. Full load is basically dumping of entire data from source table to target table . Every time when full load is happening then we are truncating the table and loading the entire data again.

      

        Incremental load is basically loading of delta data from source to target table in

        regular interval so that source and target can be synchronized. while applying the delta 

        into target table, Normally we do capture data change (CDC) i.e what is getting inserted,

        updated or deleted.





   

********************************************************************************************************