PL SQL Interview questions

1.Can we have a commit statement inside a trigger? if no why cant we?
2.How to sum negative and positive numbers from a single column ?
3.select strings containing % in a column.
4.Why is self-join required ?
5.differentiate between Where and Having ?
6.Find the Nth largest value in a column
8.Select alternate records from a table
9.select distinct records from a table,based on one column
10.delete duplicate rows in a table
11.What is a sequence?
12.How to access a sequence ?
13.What is a SYNONYM? in which cases do we generally use synonyms?
14.Advantages of synonym?
15.Will the synonym work when the parent table name is changed or table is dropped and created again??

16.difference between PK and Unique Key

17.differentiate between char and varchar

18.procedures are give better performance ??
    do we need to compile procedures ?

19.diff between ,
  select count(*) from table
  select count(column1) from table

20.Can we use commit inside the trigger? If not then how can we save the transaction made by the trigger?

21. What is the difference between "IS" and "AS" while creating procedure. Ex:- create procedure IS or AS

22. what is the difference between stored procedures and stored functions in ORACLE.How are they invoked.

23. How can we call stored procedures inside store procedures?

24. What is stored procedure?How to invoke them ?can we use them in dml statement ??

25. Explain about the process which takes place to execute a Stored routine?

26.Where the procedures are stored in database?

28. Explain about the difficulties faced by the database developer in implementing pre compiled statements?

29. how many types of stored procedure?

30. What is the difference between stored procedures and stored functions in ORACLE

31. Explain the benefits of running stored procedure on a database engine?

32. How one calls DDL statement using stored procedures in Oracle? Why can't we write ddl statement directly into the PL/SQL block ?

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    truncate table table_name; // error
END test;
/
But,

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    execute immediate 'truncate table table_name'; // works fine
END test;
/

Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE can be executed within PL/SQL program units.

When using EXECUTE IMMEDIATE, remember that any DDL operations you execute will implicitly COMMIT the current transaction.

33. Are triggers fired during DDL statements?Say you have a "on delete" trigger. What will happen when you fire a delete?

Trigger will be fired and trigger body will be executed
34. What are external procedures ? Why and when they are used?
35. 
State the different extensions for stored procedures?
Does storing of data in stored procedures increase the access time? Explain?
What is cursors?
What are the uses of stored procedure?

Explain about recursive stored procedures?

PL/SQL allows sub procedures or functions to be called recursively. The tutorial example below shows you how to calculate factorial values with a recursive sub function:

CREATE OR REPLACE PROCEDURE FACTORIAL_TEST AS              
  FUNCTION FACTORIAL(N NUMBER)                             
    RETURN NUMBER AS                                       
  BEGIN            
    IF N <= 1 THEN 
      RETURN 1;    
    ELSE           
      RETURN N*FACTORIAL(N-1);                             
    END IF;        
  END;             
BEGIN              
  DBMS_OUTPUT.PUT_LINE('3! = ' ||    TO_CHAR(FACTORIAL(3)));
  END;

/  


What Is the Scope of a Local Variable?
The scope of a variable can be described with these rules:
A variable is valid within the procedure or function where it is defined.
A variable is also valid inside a sub procedure or function defined.
If a variable name is collided with another variable in a sub procedure or function, this variable becomes not visible in that sub procedure or function.

CREATE OR REPLACE PROCEDURE PARENT AS
   X CHAR(10) := 'FYI';
   Y NUMBER := 999999.00;
   PROCEDURE CHILD AS
     Y CHAR(10) := 'CENTER';
     Z NUMBER := -1;
   BEGIN
     DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
     DBMS_OUTPUT.PUT_LINE('Y = ' || Y); -- Y from CHILD
     DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
   END;
 BEGIN
   DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
   DBMS_OUTPUT.PUT_LINE('Y = ' || TO_CHAR(Y));
   -- DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
   CHILD;
 END;

 /


what is the Difference between View and Stored Procedure?
Explain about the implementation of stored procedures?

C Interview Questions

1.Difference between malloc and calloc
2.Write your own strcpy,strcmp,atoi function


   char *my_strcpy(char dest[], const char source[])
  {
          int i = 0;
        while (source[i] != '\0')
        {
               dest[i] = source[i];
               i++;
       }
     dest[i] = '\0';
      return(dest);
}

2.1 Program to Check if a Given String is Palindrome

// A function to check if a string str is palindrome
void isPalindrome(char str[])
{
    // Start from leftmost and rightmost corners of str
    int l = 0;
    int h = strlen(str) - 1;

    // Keep comparing characters while they are same
    while (h > l)
    {
        if (str[l++] != str[h--])
        {
            printf("%s is Not Palindrome\n", str);
            return;
        }
    }
    printf("%s is palindrome\n", str);
}

2.2 Program t check if number is palindrome
http://www.geeksforgeeks.org/check-if-a-number-is-palindrome/
3. Write a program to delete a node from Linked List
4.Write a program to reverse a linked List with recursion and w/o recurssion.
5. Program to find the Nth Node from end in single traversal
6.Write a function to accept variable number of arguments
7.different storage classes in c
8.Program to write a daemon in c
9.What is structure padding in C,size of a structire variable.
10. Advantage of function pointers
11.How to find linked list has loop
12.find the middle element of a linked list
13.write a function to check if a string/number is palindrome
14.Difference between reference variable and pointer variable
15.Difference between string a character arrays
16.Constant pointer and pointer to a constant
17.describe memory layout of a c/c++ program.i.e. heap/stack/static storage etc
18.Difference between character array and character pointer
      char arr[]="hello";
      char *s="hello";
19. memset vs memcpy.
20.What is a dangling pointer.give an example.Example of cases when memory leak might hppen
21.Mutable vs Volatile variables
22.Why is volatile needed in c
23. C Program to Check whether two Strings are Anagrams.(with sort and without sorting them)
24What is stack unwinding,context switching,Segmentation Fault
   http://www.bogotobogo.com/cplusplus/stackunwinding.php
   http://www.geeksforgeeks.org/stack-unwinding-in-c/
25. Sorting Algorithms along with time complexities- Selection,Insertion,Bubble,Quick,Merge
26.How to use realloc function as free and malloc or inplace of free and malloc
27.Programm or macro for - setting a bit,toggling a bit,clear and checking a bit using bitwise operators
28. Strlen vs sizeof op in c
29. implement strlen function in C
30.Write a program to check whether the given number is a prime.
31.Write a program to generate the Fibonacci series
32.Difference in reading a text file and a Binary file ?
33.Break vs continue
34.Why is it safer to use fgets() over gets()
35.difference between printf ,fprintf,sprintf
36.prorag to check liitle endian vs big endian
37.What is the difference between NULL, '\0' and 0
http://stackoverflow.com/questions/1296843/what-is-the-difference-between-null-0-and-0

38.write a variable argument function
   http://www.tutorialspoint.com/cprogramming/c_variable_arguments.htm

40.What does the below codes do ??
   a. void my_strcat(char *dest, char *src)

{

  (*dest)? my_strcat(++dest, src): (*dest++ = *src++)? my_strcat(dest, src): 0 ;

}

b. int my_strcmp(char *a, char *b)

{

  return (*a == *b && *b == '\0')? 0 : (*a == *b)? my_strcmp(++a, ++b): 1;

}

41.Dynamically allocate array of strings in c

 
    int num,len,i;
    char temp[50],ch;
    char **ptr;
    printf("enter num of strings u want\n");
    scanf("%d",&num);
    ptr=(char **)malloc(sizeof(char *) * num);

    for(i=0;i <num;i++)
    {

       printf("enter the string\n");
       scanf("%s",temp);
       len=strlen(temp)+1;
       ptr[i]=(char*)malloc(len);
       strcpy(ptr[i],temp);

    }

42. Difference between global static variable vs normal global variable

    static int var1;
           int var2;
    int main(){}

   var2 has external linkage while var1 has internal linkage (visible to that file only)

43. Multi threading c (pthreads)

44. System programming(IPC) - sockets,sharedmem etc

45. what is the difference between popen() and system() in C ?

46. in C Language,which is better(faster n safer) : Better sprintf ,strcpy or memcpy???

47. How to debug a program running in remote machine ?

     gdbserver

48.What will be the output of below code snippet?

   int x=0; 
  while (x<3) {
    cout<<x<<std::endl;
    x = x++;
  }

 ANS: Infinite loop

49. What is heap corruption ?

50. which layout the command line arguments stored in memory ? heap , stack or somewhere else ?

51. describe how heap is designed ? 

52.  socket programming -- Listen socket call
   https://stackoverflow.com/questions/60181148/what-is-the-purpose-of-listen-in-socket-programming
   https://www.gnu.org/software/libc/manual/html_node/Accepting-Connections.html

53. System calls in c ==> 
   A system call is a request for service that a program makes of the kernel
   System calls are sometimes called kernel calls.

    https://www.gnu.org/software/libc/manual/html_node/System-Calls.html
  https://www.geeksforgeeks.org/input-output-system-calls-c-create-open-close-read-write/