2015: Intw question 4- SQL

Difference b/w delete, truncate and drop

Delete

• The Delete command is used to delete rows from table
• After Delete operation we need to perform COMMIT or ROLLBACK to make the changes permanent or undo
• Delete operations will cause all the delete triggers on the table to fire
• DML command

Truncate

• The Truncate command is used to delete rows from table
• After Truncate operation data can't be rolled back
• No Delete triggers will be fired 
• Truncate is faster that Delete
• DDL command

Drop

• The Drop command is used to delete table
• All table rows, indexes, privileges will also be removed
• Can't be roll back
• No Delete triggers will be fired 
• DDL Command

DDL Command- Create, Alter, Truncate, Rename, Drop

DML Command- Select, Insert, Delete, Update

DCL Command- Grant, Revoke

                                                   Difference b/w Primary, Unique & Foreign Key

Primary Key

• The PRIMARY KEY constraint uniquely identifies each record in a database table.
• Primary keys must contain unique values.
• A primary key column cannot contain NULL values.
• Most tables should have a primary key, and each table can have only ONE primary key.
• A Primary Key can be comprised of more than one column of a table

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Unique Key

• The UNIQUE constraint uniquely identifies each record in a database table.
• The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
• Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
• A unique key can be comprised of more than one column of a table
• In MS SQL Server, at most a single row for that column can have NULL

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

Foreign Key

• A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

 CREATE TABLE Orders

(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

Indexes, Views & Triggers

Indexes

• Indexes are special lookup tables that the database search engine can use to speed up data retrieval. 
• An index is a pointer to data in a table
• The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

• A composite index is an index on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (column1, column2);

• Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Views

• A view is a virtual table.
• A view is a virtual table based on the result-set of an SQL statement.
• A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
• A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
• Syntax to create view 

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Triggers

• Triggers are stored programs, which are automatically executed or fired when some events occur. 
• Triggers are, in fact, written to be executed in response to any of the following events:
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• The syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

Difference between Stored Procedure and Functions

• SPs are pre-compiled objects where they are compiled first time and it's compiled format is executed whenever it is called. But Function is compiled and executed every time when it is called
• Function must return a value but in SP it is optional
• Functions can be called from SPs but SPs can't be called from Function

2015: Intw question 3- Basic Xpath

XPath

• XPath is used to navigate through elements and attributes in an XML document.
• XPath is a language for finding information in an XML document.
• In XPath, there are seven kinds of nodes: 
• element
• attribute
• text
• namespace
• processing-instruction
• comment
• document
• Look at the following XML document:

<?xml version="1.0" encoding="UTF-8"?>

<bookstore>
  <book>
    <title lang="en">Harry Potter</title>
    <author>J K. Rowling</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
</bookstore>

Example of nodes in the XML document above:

<bookstore> (root element node)

<author>J K. Rowling</author> (element node)

lang="en" (attribute node)

• Atomic values/Items/Node- Atomic values are nodes with no children or parent. e.g. 

J K. Rowling

"en"

• Relationship of Nodes

Refer following example

<book>
  <title>Harry Potter</title>
  <author>J K. Rowling</author>
  <year>2005</year>
  <price>29.99</price>
</book>

• Parent- Each element and attribute has one parent. e.g. book is parent is of title/author/year/price
• Children- Element nodes may have zero, one or more children. e.g. title/author/year/price are children of book
• Siblings- Nodes that have same parent e.g. title/author/year/price
• Ancestors- Node's parent, parent's parent e.g. book is ancestor of title/author/year/price
• Descendants- Node's children, children's children e.g. title/author/year/price are descendants of book

• Basic XPath Syntax- Refer following article -

       http://www.w3schools.com/xpath/xpath_syntax.asp

2015: Interview Que2- Java related

How to call a method of a class without creating it's instance

• create a method in inherit class in which you can use base keyword to call the base class method....

public class Person
    {
        protected string ssn = "444-55-6666";
        protected string name = "John L. Malgraine";

        public virtual void GetInfo()
        {
            Console.WriteLine("Name: {0}", name);
            Console.WriteLine("SSN: {0}", ssn);
        }
    }
    class Employee : Person
    {
        public string id = "ABC567EFG";
        public override void GetInfo()
        {
            // Calling the base class GetInfo method: 
            base.GetInfo();
            Console.WriteLine("Employee ID: {0}", id);
        }
    }

    class TestClass
    {
        static void Main()
        {
            Employee E = new Employee();
            E.GetInfo();
        }
    }

Output

Name: John L. Malgraine

SSN: 444-55-6666 

Employee ID: ABC567EFG

• We can make the method static, a static method belongs to class rather than object of a class. We can invoke static method without creating instance of a class. Please note there are following restrictions on static method- 
• static method can access static data member and can change the value of it.
• The static method can not use non static data member or call non-static method directly
• this and super cannot be used in static context.

Example of static Method

1. class Student9{  
2.      int rollno;  
3.      String name;  
4.      static String college = "ITS";  
5.        
6.      static void change(){  
7.      college = "BBDIT";  
8.      }  
9.   
10.      Student9(int r, String n){  
11.      rollno = r;  
12.      name = n;  
13.      }  
14.   
15.      void display (){System.out.println(rollno+" "+name+" "+college);}  
16.   
17.     public static void main(String args[]){  
18.     Student9.change();  
19.   
20.     Student9 s1 = new Student9 (111,"Karan");  
21.     Student9 s2 = new Student9 (222,"Aryan");  
22.     Student9 s3 = new Student9 (333,"Sonoo");  
23.   
24.     s1.display();  
25.     s2.display();  
26.     s3.display();  
27.     }  
28. }  

Output

111 Karan BBDIT

222 Aryan BBDIT
333 Sonoo BBDIT

Difference between class and structure

• In structure have a by default public. In class have a by default private.
• There is no data hiding features comes with structures. Classes do, private, protected and public.
• A structure can't be abstract, a class can.
• Structure cannot be inherited. But class can be inherit.

Structure :

struct struct-name
{
data-type member1;
data-type member2;
data-type membern;
};
struct struct-name v1,v2...vn;

Class :

class class-name
{
private :

data members;
member functions;

public :

data members;
member functions;

protected :

data members;
member functions;

};
class class-name obj1,obj2...objn;

Java.util.Scanner Class

• The java.util.Scanner class is a simple text scanner which can parse primitive types and strings using regular expressions.
• A Scanner breaks its input into tokens using a delimiter pattern, which by default matches whitespac
• A scanning operation may block waiting for input.
• This class inherits methods from the following classes: java.util.Object

Popular class constructors

• Scanner(InputStream source) 

This constructs a new Scanner that produces values scanned from the specified input stream..

e.g. Scanner sc=new Scanner(System.in);

• Scanner(File source) 

This constructs a new Scanner that produces values scanned from the specified file.

• Scanner(String source) 

This constructs a new Scanner that produces values scanned from the specified string.

Popular class methods

boolean hasNext() 
This method returns true if this scanner has another token in its input.

int nextInt() 
This method scans the next token of the input as an int.

String next() 
This method finds and returns the next complete token from this scanner..

String nextLine() 
This method advances this scanner past the current line and returns the input that was skipped.

Example to differentiate between next() and nextLine()

String str, str1;
Scanner sc= new Scanner(System.in);
str=sc.next();
        System.out.println(str);
        str1=sc.nextLine();

        System.out.println(str1);

Input#0
www.test.com
www.test1.com
Output#0
www.test.com


String str, str1;
Scanner sc= new Scanner(System.in);
str=sc.nextLine();
        System.out.println(str);
        str1=sc.next();

        System.out.println(str1);

Input#1
my name is test
my name is test1
Output#1
my name is test
my

System.in/System.out/System.err

• System.in is an InputStream which is typically connected to keyboard input of console programs. 
• System.out is a PrintStream. System.out normally outputs the data you write to it to the console. 
• System.err is a PrintStream. System.err works like System.out except it is normally only used to output error texts.

Here is a simple example that uses System.out and System.err:

try {
  InputStream input = new FileInputStream("c:\\data\\...");
  System.out.println("File opened...");

} catch (IOException e){
  System.err.println("File opening failed:");
  e.printStackTrace();
}

2015: Intw Que 1- Constructors and Destructors

Difference between Constructors & Descturctors

Constructors:

• Constructor is Used to Initialize the Object.

• Constructor can takes arguments.

• Constructor overloading can be possible means more than one constructors can be defined insame class.

• Constructor has same name as class name.

• Syntax of constructor:

                class class_name
                  {
                     class_name(){}
                     class_name(argulist){}
                 } ;

• Constructor are of following:

               1)Default Constructor.
               2)Parameterized Constructor.
               3)Copy Constructor.

Destructors:

• ‘Destructors’ are used to destruct instances of classes.
• A class can only have one destructor.
• Destructors cannot be inherited or overloaded.
• Destructors cannot be called. They are invoked automatically.
• A destructor does not take modifiers or have parameters.

The following is a declaration of a destructor for the class MyClass:

~ MyClass() 
{
   // Cleaning up code goes here
}

• The programmer has no control on when the destructor is going to be executed because this is determined by the Garbage Collector. The garbage collector checks for objects that are no longer being used by the application. It considers these objects eligible for destruction and reclaims their memory. 
• Destructors are also called when the program exits. When a destructor executes what is happening behind the scenes is that the destructor implicitly calls the Object.Finalize method on the object's base class. Therefore, the preceding destructor code is implicitly translated to:

protected override void Finalize()
{
   try
   {
      // Cleaning up .
   }
   finally
   {
      base.Finalize();
   }
}

Now, let us look at an example of how destructors are called. We have three classes A, B and C. B is derived from A, and C is derived from B. Each class has their own constructors and destructors. In the main of the class App, we create an object of C

class A
{
 public A()
 {
  Console.WriteLine("Creating A");
 }
 ~A()
 {
  Console.WriteLine("Destroying A");
 }
}

class B:A
{
 public B()
 {
  Console.WriteLine("Creating B");
 }
 ~B()
 {
  Console.WriteLine("Destroying B");
 }

}
class C:B
{
 public C()
 {
  Console.WriteLine("Creating C");
 }

 ~C()
 {
  Console.WriteLine("Destroying C");
 }
}
class App
{
 public static void Main()
 {
  C c=new C();
  Console.WriteLine("Object Created ");
  Console.WriteLine("Press enter to Destroy it");
  Console.ReadLine();
  c=null;
  //GC.Collect();
  Console.Read();
 }

}


As we expect, the constructors of base classes will be executed and program will wait for the user to press 'enter'. When this occurs, we set the object of class C to null. But the destructors are not executing ..!!?? As we already said, the programmer has no control on when the destructor is going to be executed because the Garbage Collector determines this. But the destructors are called when the program exits. You can check this by redirecting the o/p of the program to a text file. I have the output here. Notice that the destructors of the base classes are called because behind the scenes base.Finalize() is called.


Creating A
Creating B
Creating C
Object Created 
Press enter to Destroy it
Destroying C
Destroying B
Destroying A




So, what do you do if you want to call the destructors once you are finished using the object? There are two ways:

Call the Garbage collector to clean up: You can force the garbage collector to do clean up by calling the GC.Collect method, but in most cases, this should be avoided because it may result in performance issues. In the above program, remove the comment onGC.Collect(). Compile and run it. Now, you can see the destructors being executed in the console itself.
Implement Dispose method of IDisposable interface: The IDisposable interface contains only one public method with signature void Dispose(). We can implement this method to close or release unmanaged resources such as files, streams, and handles held by an instance of the class that implements this interface.


class MyClass:IDisposable
{
    public void Dispose()
 {
  //implementation
 }
}