Wednesday, 29 August 2018

JAVA FA1


Fundamentals

#JDK consists of development tools required for developing a Java application.
It also consists of Java runtime environment for executing a Java application.

#.java files are in src folder of the project
.class files are in bin folder of the project

#A single .java file can have multiple classes, but they are compiled into their own separate .class files.
It is a good practice to have a single Java class in a .java file.

#JIT compiler - converts reusable byte code to machine language.

#There are 50 keywords in Java 8.
Apart from keywords, there are other words which are used as names to identify components in a program, e.g. HelloWorld, main. These words are called Identifiers.

#Identifiers should start with a letter, $ or _
It should not be a Java keyword or a literal

#Where there is functionality, there is data. And for implementing the functionality, we need to store the data.

#char datatype is of 2 bytes and the values range is \u0000 to \uffff

#String is one of the non-primitive types.

#Implicit conversion happens when a value of smaller data type needs to be assigned to a variable of larger compatible data type. Also called widening conversion, it is done automatically by Java.

#In narrowing conversions, char data type can be converted to byte or short

#Does the code below require type casting?
long rollNo  = 88843L;
float rollNoFloat = rollNo;

Ans - No
Explanaton: there are two types of losses, loss of magnitude and loss of precision.
int to byte is loss of magnitude because you cant store 500 in a byte.
long to float is loss of precision because the value range of float is larger than that of long.
eg. - 23500000000 is stored as 2.35e10 in float
Rule is: loss of magnitude - explicit cast required
         loss of precision - no cast required
 
#Control stucture:
1] Decision Making
2] Looping
3] Branching

# ARRAY can be used to store data of primitive as well as reference data type

#// Another way of creating a 2-D array
int[][] dayWiseTemperature = new int[][] {
    {30, 35, 28},
    {29, 32, 0},
    .
    .
    .
};

#For-Each loop:
for (int score : marks) {
    System.out.println(score);
}

#String args[] is an array of strings.
It is an array of command line arguments. Such arguments are passed as inputs to your program either through the command prompt of your system, or through the run configuration of your IDE.

#Valid Array Declaration:
1] int myArray2[];
2] int myArray3[]=new int[5];
3] int []myArray5=new int[5];
4] int myArray7[]=null;
5] int score[][] = new int[2][];
6] int score[][] = new int[2][2];
7] int score[][] = new int[2][];
   score[0] = new int[2];
   
#Class:
Instance variables are automatically initialized to their default values during object creation.
Local variables (declared in methods) have to be initialized before they can be used.


#When a method has a local variable with the same name as an instance variable, the local variable gets higher preference inside that method. This is called shadowing of a field. In such a case, "this" keyword can be used to refer to the instance variables.

"this" can also be used to invoke one constructor or method  from another constructor or method.


#local variables are not assigned to default variable.

#Memory:
-All local variables and method invocations are stored in the stack.
-Earlier languages like C/C++ made the programmer responsible for freeing the memory occupied by such resources. Java, on the other hand, has a garbage collector which automatically deallocates the memory used by these resources.
-When a reference is local to some method then it will be removed from the stack as soon as the method has finished executing.


#Encapsulation:
private members (model, color, mileage variables of Car class below) are hidden and are accessible only through public methods.

Abstraction:
Invoking methods (start or displayDetails method of Car class) without knowing their details.


#Relationships:
1] Association: When an object utilizes another object in order to perform its activities, a relationship of association is established between them. This is termed as 'uses-a' relationship.
Example: A driver uses a car to travel. (Uses objects as parameters)
Association (uses-a) is denoted by a line.

2] Aggregation: When an object contains another object as its attribute, we have an aggregation between them. It is termed as "has-a" relationship.
Example: A car has an engine (here, Car is an aggregate class with full ownership on the Engine object)
Aggregation (has-a) is denoted by a link with a diamond head.

3] Inheritance:


#Types of Inheritance:
1] Single Inheritance
2] Hierarchical Inheritance
3] Multi level Inheritance
4] Multiple Inheritance (Not Supported in JAVA due to diamond problem, but by Interfaces) 


#The ability of an object to behave differently to a behavior invocation is known as Polymorphism.

#Method Overriding:
-When we override a method in the child class, it should have the same signature as that of the parent class.
-It should not have a weaker access privilege.
-Private methods are not overridden.

#super keyword:
- to call parent call methods
- to invoke the parent class constructor
- to access the instance variable of parent class

#static blocks:
static blocks are used to initialize static variables when it cannot be done in a single line. They can be used to add preprocessing if required. Static blocks get executed when a class gets loaded.
e.g.-
static {
    int randomNumber = 30;
    counter = (int) Math.pow(randomNumber, 3);
}


#NOTE: A static context, i.e. static blocks and static methods, cannot access non-static (instance) members directly.
However, non-static methods can access static members.


#Final keyword:
-Final field can not be changed
-Final class can not be subclassed
-Final method can not be overridden

#Abstract Methods:
A class can be abstract even without any abstract methods.
e.g.-
abstract class Automobile {
    public abstract void start();    // Notice the semicolons in the method declarations
    public abstract void stop();
}


#Abstract classes enforce inheritance
and
Abstract methods enforce overriding
Hence,we achieve dynamic binding

#Interfaces:
An interface is used to define a generic template which can be implemented by various classes.
It contains method signatures and constant declarations. Since Java 8, it can also have default and static methods.
The methods declared in an interface are implicitly public and abstract, and the data fields are implicitly public, static and final, i.e. constants.
The classes implementing an interface must implement all the specified methods. Otherwise, they should be made abstract.


#default and static in Interfaces:
"Default" methods can be used to introduce new functionalities into interfaces without breaking the existing classes which implement those interfaces. The default methods provide their own definitions, which the implementing classes need not override.
"Static" methods in interfaces allow us to easily organize the related helper and utility methods in our libraries.

Note: If a class implements multiple interfaces having the same default methods, it has to override them.
From the overridden methods, the default methods of a specified interface can be called using the super keyword.
E.g. PassengerCar.super.calculateFare();


#"SavingsAccount extends Account" is correct if both Account and SavingsAccount are both either interfaces or classes.


#Access specifiers:
1] Public: Accessible everywhere.
2] Private: Accessible only inside the same class.
3] Protected: Accessible in the same packages and to sub classes of the same class.
4] Default: Accessible inside the same packages (Members without any access specifiers)

Note: A class can have only public or default access.
Note: Protected methods of a class can be inherited by subclasses, and can become public in the subclasses.


#PMD is a source code analyzer, which checks code against a predefined set of rules.
It detects inefficient code like unused variables, empty switch/if/while, unnecessary object creation, etc.
The rules are defined in XML format (.exsd)

#PMD rules we are going to follow are:
1] Unused imports should not be present
2] System.out.println statements should not be present
3] Variable naming convention should be followed
variable names should be in camel case
4] Method naming convention should be followed
method names should be in camel case
5] Class naming convention should be followed
class names should be in pascal case
6] Package naming convention should be followed
package names should be in lowercase
7] printStackTrace() should not be present


#In Java, the Object class is the implicit super class of all classes.
A method that takes an Object type parameter can accept any object as the parameter.

#equals() compares objects to check for equality.
By default, it uses memory address to compare objects for equality (just like ==).
To make it work for different requirements, it needs to be overridden.

#hashCode() uses an object's data to generate a hash value, which should be a 32 bit integer.
By default, it derives the hash value based on the memory address of the object being used.
In Java, hash tables and other such data structures make use of hash values for better performance.
To make it work for different requirements, it needs to be overridden.


#Equal objects must have equal hash codes
In other words, if two objects are equal according to the equals() method, then the hashCode() method on each of the two objects must produce the same integer result.
To maintain this contract both the methods should be overridden in tandem
It is important to understand that if the hash codes of two objects are same, it doesn't prove that the objects are equal, i.e. it is possible for two unequal objects to have the same hash codes.


#toString() returns a textual representation of the object.
The returned text should be concise, easy to read, and informative.
By default, it returns a string consisting of the name of the object's class, the '@' character, and the unsigned hexadecimal representation of the hash code of the object.
It should be overridden to provide a meaningful textual representation.


#The Comparable interface imposes an ordering among the objects of a class. This is called the class' natural ordering.
It has only one method, compareTo()
When overridden, it should return:
--a negative integer if this object is less than the specified object
--zero if this object is equal to the specified object
--a positive integer if this object is greater than the specified object
The wrapper classes and the String class implement the Comparable interface.

#While overriding the equals() method, hashCode() method "should" be overridden to maintain the hashCode-equals contract.


#Strings are immutable (final), i.e. they are constants, and their values cannot change after creation. This improves memory usage and makes them thread-safe*.

#the String class maintains a literal pool, which is like a cache in the heap.
--The pool keeps a reference for every string literal object.
--If the same string literal value is used again, the reference to the existing object is used from the pool.
--The intern() method is implicitly used to add and use string object references from the pool. This method can also be used explicitly.

e.g. - 
String name1 = "Emily";
Initially the string pool is empty. When a string literal value is used, intern() is invoked implicitly.
A String object is created, and a reference to it is added to the pool. The same reference is then returned to 'name1' for use.

NOTE: If the String object is explicitly created using the "new" keyword, the literal pool is not used.
While creating String objects using the new keyword, the intern() method can be explicitly invoked to use the literal pool. This works the same way as directly creating strings using literals.
String name4 = new String("Emily").intern();
#the equal() and hashCode() methods are already overridden in the String class.


#The StringBuffer class represents thread-safe and mutable strings, whose capacity can be dynamically increased.

#StringBuilder is the non-synchronized version of StringBuffer. It is used when thread safety is not required.
It is faster than String and StringBuffer.
Note: The equals() and hashCode() methods in the StringBuffer and StringBuilder classes are not overridden.

#Integer wrappedInt = 2;         --autoboxing.
int primitiveInt = wrappedInt;   --unboxing

#Note: Similar to the String pool, Byte, Short, Integer and Long classes cache values in the range -128 to 127.
Wrapper classes are commonly used when we need to convert numeric strings into other data types. For this purpose, we have methods like parseDouble(), parseInt(), etc.

Also, type casting cannot be used to convert any wrapper type to another. We can make use of methods like intValue(), byteValue(), floatValue(), etc. to perform such conversions. Consider the example below
e.g. - 
Integer phoneNo = 44281234;
Long phoneNo = phoneNo.longValue();
// Converts Integer into a Long value

#string pool + (compile time reference)    creates a new object.


#An annotation is a meta-data that provides data about the program and is not part of the program itself.
--Its purpose is to give additional information to the compiler, or for some processing during compile-time or runtime.
--It can be added to classes, methods, variables, parameters and packages.
e.g.- 
@SuppressWarnings(value="unused")
Here, SuppressWarnings is the annotation name, value is the element and 'unused' is its value.
This annotation prevents warnings related to unused local variables and unused private methods.

A few other built-in annotations:
@Override is used on a method. It ensures that the method is actually overriding a parent's method. Otherwise, a compilation error occurs.
@Deprecated denotes a method as obsolete. A warning occurs when such a method is used.


#Java provides varargs to make things easier. It is a construct that allows methods to accept zero or more arguments.
--It internally uses arrays to process the variable number of arguments.
Limitations:
--There can be only one varargs argument in a method.
--If present, varargs must be the last parameter.


#Regular Expression (Regex) :
String regex = "Hello.*";
here, Hello = literals
  . = Meta Character (Reserved character)
  * = Quantifiers (frequency of occurance of characters)
This regex will match with any string that starts with "Hello"

The regex API (java.util.regex) provides classes and interfaces to work with regular expressions.
The String class uses this API to support regex in four methods:
matches(), split(), replaceFirst(), replaceAll()

Note:
-->If meta characters are needed to be used as literals, they have to be escaped with a double backslash.
-->To refer to captured groups in the replacement methods, $<group no.> is used. E.g. str.replaceAll(regex, "$1");
-->"\w" is a meta character. But in Java, '\' is an escape character. Hence, we have to escape it using another backslash. E.g. "[\\w]+"


#<<2 Uppercase Characters>>-<<9 digits>>:<<2 Uppercase Characters>>
[A-Z]{2}-[\\d]{9}:[A-Z]{2}

password regex - password.matches(".{6,}") && password.matches("[\\w]*[\\d][\\w]*")

\\w+@infy\\.(com|in)
\\w+\\d+@\\w+\\.com
[\\w&&[^\\s]]+@infy\\.com
[a-zA-Z0-9@.]+


#An enum is a data type which contains a fixed set of constants. For example,
days of a week (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday and Saturday)
directions (North, South, East and West)
The enum fields are static and final implicitly, making them compile time constants.

Enums can be defined inside a interface, a class and a static context.

A few things about enums:
-->Enums in Java are considered to be reference types like classes and interfaces, and thus, a programmer can define constructors, methods and variables, inside an enum.
-->The Java compiler automatically generates a static method called values() for each enum. This method returns an array of all the constants defined inside the enum.
-->The constructor for an enum type must have default or private access. And it cannot be invoked using the new operator.
-->Values can be provided to enum constants at the time of their creation. These values are passed to the constructor when the constant is created.

We know the instance Direction.SOUTH gets initialized automatically when used for the first time. And since it is static and final by default, only one copy of SOUTH is created.
This feature of enums can be used as a solution in situations where a single copy of an object is required for the whole application lifecycle.
Such recurring situations are called "design problems", and their solutions are called "design patterns".
The above pattern is called the "Singleton Design Pattern".

Some benefits of using design patterns are:
They provide a standard approach to solve a recurring problem and save time.
They promote reusability, which leads to more robust and highly maintainable code.
They make our code easy to understand and debug as they are already defined.
They lead to faster development.

A design pattern is a proven and time tested solution to a recurring design problem.
Types of design patterns:
1] Creational : Offers flexibility to decide who(how,which,when) is responsible for the creation of the object. e.g.- Builder, factory, singleton
2] Structural : focus on how related classes or objects are composed together to form a larger structure. e.g. - adaptor, composite, decorator, bridge
3] Behavioral : defines communicaation of the objects and controls the flow within the participating objects. e.g.- Command, Iterator, Observer


#Singleton pattern is a design solution where an application needs to have one and only one instance of any class, in all possible scenarios without any exceptional condition.
There are many ways of implementing singleton design pattern, such as using private constructors and static methods, enum, etc.
The simplest approach in Java for implementing singleton is through enum.

e.g.-
class Registrar {
      private static Registrar reg;
      private Registrar() {
            System.out.println("Registrar created!");
      }
      public static Registrar getRegistrar() {
            if(reg == null) reg = new Registrar();
            return reg; 
      }
}

// Using enum
public enum Registrar {
    REGISTRAR("R1001");
    String value;
    private Registrar(String value) {
            this.value = value;
    }
}

public enum Day {
SUNDAY(1), MONDAY(2), TUESDAY(3), WEDNESDAY(4), THURSDAY(5), FRIDAY(6), SATURDAY(
7);
private int value;

private Day(int value) { this.value = value; }

public int getValue() { return this.value; }
public static void main(String[] args) {
//Printing all constants of an enum.
for(Day day: Day.values())
System.out.println("Day:"+day.name()+"   Value:"+day.getValue());

}

}

enum enumDemo {
INSTANCE;
public void display() {
System.out.println("Display called");
}
}

public class UserInterface {
public static void main(String[] args) {
enumDemo.INSTANCE.display();
}
}

#Builder pattern separates the construction logic for an object from its representation so that the same construction process can create different representations.
It provides a step-by-step way to build the object and provide a method that will return the final object.
For example,
StringBuilder.append()
It is an overloaded method which appends string representation of the argument of any data type to the current object. E.g. append(boolean b), append(Object obj), etc.


#DATE :
Date class allows us to represent date in terms of year, month, day, hour, minute, second and millisecond values.

Date today = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
String todayString = sdf.format(today);

NOTE: Some methods of the Date class are now deprecated, and it is recommended to use the Calendar class instead.

java.util.Calendar is an abstract class.
Its object is created using the getInstance() method.
e.g. - Calendar today = Calendar.getInstance();
int currentYear = today.get(Calendar.YEAR);                    // Here Calendar.YEAR is a field constant
System.out.println("Current Year: " + currentYear);
This method follows the Factory Design Pattern, and actually returns a GregorianCalendar (a child class of Calendar) object with the current system date, time, time-zone and locale.

Note:
While checking Calendar objects for equality, all the field values are compared.
In the Date and Calendar classes, the month index starts from zero.
The Date and Calendar objects are not thread-safe.

java.time.LocalDate is an immutable date-time object that represents a date. It does not store time and time-zone.
e.g. - LocalDate today = LocalDate.now(); 

java.time.LocalTime is an immutable date-time object that represents time up to nanoseconds of precision. It does not store the time-zone.
e.g. - LocalTime rightNow = LocalTime.now();

java.time.LocalDateTime is an immutable date-time object that represents date and time. It does not store the time-zone.
e.g. - LocalDateTime rightNow = LocalDateTime.now();

The ChronoField enum is used to provide field-based access to date and time components. 
And the ChronoUnit enum can be used to specify the desired unit of a field.
e.g. - 
LocalDate today = LocalDate.now();
LocalDate anotherDay = LocalDate.of(2010, 2, 16);
int thisYear = today.get(ChronoField.YEAR);
long durationInYears = anotherDay.until(today, ChronoUnit.YEARS);

String date = anotherDay.format(DateTimeFormatter.ofPattern("eeee, dd MMMM, yyyy"));
// Output: Tuesday, 16 February, 2010

Note:
In the LocalDate and LocalDateTime classes, the month index starts from 1.
The LocalDate, LocalTime and LocalDateTime objects are thread-safe.

the Calendar class solves a design problem… the problem of creating its objects based on parameters.
The Factory design pattern is used when there is a super class with multiple sub-classes, and we need an object of one of the sub-classes based on the input.
This pattern uses a factory class to create objects of different sub-classes based on the user requirement.
e.g. -
1] Calendar.getInstance() implements factory pattern as it returns a different instance of Calendar based on the parameters.
2] Calendar.getInstance() returns an object of GregorianCalendar with the default time zone and locale.
3] Calendar.getInstance(Locale locale) returns an object of BuddhistCalendar if the locale is Thai. It returns an object of JapaneseImperialCalendar if the locale is Japanese. Otherwise it returns an object of GregorianCalendar.

#enterprise applications have non-functional requirements (NFRs) like:
1] Usability
2] Performance
3] Availability
4] Security
5] Scalability
6] Maintainability
7] Reliability

#n-tier structure:
client tier
presentation tier
business tier
persistence tier

#client-server architecture:
if the client request for a static resource through URL, it is retrieved from a set of static content.
Otherwise, the web container component of the server generates the dynamic content


#The Application servers (App Servers) should be able to take care of the load, availability, security, performance monitoring, etc.
Examples:
WebSphere Application Server (WAS) by IBM
WildFly (JBoss) Application Server by Red Hat
Geronimo by Apache
WebLogic Application Server by Oracle
GlassFish by Oracle


#Use Case Diagrams are used in UML (Unified Modeling Language to visualize a system design) to depict Use Cases. They are used to identify, clarify and organize the system requirements as well as to capture the functional requirements during the development stage.
A Use Case is a list of steps which define the interactions between an entity and a system.

#Note: DAO stands for Data Access Object

#sequence diagrams. They are used to represent the dynamic behaviour of the objects in a system by using interactions. They describe interactions by focusing on the sequence of messages that are exchanged, without giving information about the object structure.

#The details representing a real world user can be encapsulated into an object. Such objects are called beans.

#A factory class provides the objects of various classes as and when required. This ensures that all object-creation code remains in a single place. A factory contains static methods to create and return objects of other classes.
e.g. - 
public static UserService createPremiumUserService() {
    return new PremiumUserService();
}
public static UserService createRegularUserService() {
    return new RegularUserService();
}
// Similarly for other such classes

using the interface reference and getting the required object from the factory has made the code completely independent of the implementation while following the business standards.
This introduces more abstraction and flexibility into our code, and also makes it resilient to changes.
Such a convention is called the Factory Design Pattern.


#"Generics" are used to create classes, interfaces and methods in which the type of the object on which they operate is specified as a parameter.
--Type checking is done at compile time
--Eliminates the need for casting
--Generic algorithms can be implemented

class class-name<type-parameter-list> { }
Here, the type-parameter-list specifies the type parameters. By convention, the type parameter is denoted by a single capital letter and is commonly one among E (element), T (type), K (key), N (number) and V (value)
e.g. - 
class Stack<E> {
    private E[] elements;
    public E push(E element) {
        // Code to push element
    }
    public E pop() {
        // Code to pop element
    }
}
Stack<String> strStack = new Stack<String>();
Stack<Integer> integerStack = new Stack<>();    // Java 7 or higher versions
//Generic types can also be used without type parameters. Such a type is called raw type.
Stack stack = new Stack();    // Raw type
//There can be more than one type parameter for a class or interface.
class MyClass<K,V> { }

Among all the flexibility and security, Generics however, possess some restrictions:

A type parameter cannot be instantiated
E e1 = new E();    // Compilation error
A generic class cannot extend the java.lang.Throwable * class.
A generic type cannot be used with a primitive type. It must always be a reference type.


public class UserInterface {
    public static <E> void display(E[] list) {    // A generic method to display an array of objects
        for (int i = 0; i < list.length; i++)
            System.out.println(list[i] + ", ");
    }
}
UserInterface.<String>display(cities);

NOTE: A generic method can return a generic type value.
Also, a generic method can be declared inside a non-generic class.

#Arrays
Advantages:
1] Compile time type checking
2] Can hold primitive type data as well as objects
Disadvantages:
1] It cannot grow and shrink dynamically
2] It does not have any built-in algorithm for searching or sorting

The "collections framework" provides a set of interfaces and classes for representing and manipulating collections. Introduced as part of J2SE 1.2, it standardizes the way we store and access data from collections. It is a part of the java.util package.

Accessing the elements of a collection:
1] The for loop can be used for ordered collections
2] The enhanced for loop (for-each) can be used for ordered and unordered collections
3] The Iterator interface can be used for collections implementing the Collection interface

The Iterator interface provides the following methods:
1] hasNext()
2] next()
3] remove()
Iterator interface follows the Iterator Design Pattern. 

#ArrayList and LinkedList classes implements List interface. The equals() method works across all the implementations of the List interface and returns true if and only if they contain the same elements in the same order. We can traverse the elements of a list using the for-loop, the enhanced for-loop, the iterator or the list iterator.

ArrayList is used for more frequent access.
LinkedList is used for more frequent updating and deletion.

class ListTester {
    public static void main(String args[]) {
        LinkedList obj = new LinkedList();
        obj.add("A");
        obj.add(10);
        obj.add("C");
        obj.addFirst("D");
        System.out.println(obj);             //[D, A, 10, C]
    }
}


#Set uses the methods from the Collection interface and does not declare any new method of its own.
Being unordered, sets cannot be accessed using indexes. The enhanced for-loop and the iterator are two ways of traversing and accessing the elements of a set.

Implementation of Set Interface:
1] HashSet   (uses hash table)
2] LinkedHashSet   (uses hash table and linked list) (ordered by insertion)
3] TreeSet        (use tree based data structure) (natural ordered)


public class Test {
public static void main(String args[]) {
Set s = new TreeSet();
s.add("4");
s.add(8);                          //Runtime Exception coz of compareTo()
Iterator itr = s.iterator();
while (itr.hasNext())
System.out.print(itr.next() + " ");
}
}


#Map does not extend the Collection interface. So there is no iterator for maps. Moreover, map values cannot be retrieved without knowing the keys. Hence, there is no direct way of traversing a map.

Working with the set of keys:   
Set setOfKeys = map.keySet();

Working with the Collection of values:  
Collection valueCollection = map.values();

Working with the set of entries:   
Set<Entry> setOfEntries = map.entrySet();

Implementation of Map Interface:
1] Hash Map      (hash table implementation)(unordered)
2] TreeMap   (tree based implementation)(natural ordering)
3] LinkedHashMAp (order of insertion)
4] ConcurrentHashMap serialized(thread safe) version of hash map

Note: TreeMap and ConcurrentHashMap do not allow null keys.

Map<String, Double> map = new HashMap<String, Double>();
 map.add("pi", 3.14159); //wrong it has put() method
 map.add("log(1)", new Double(0.0)); //wrong
 map.add("e", 2.71828D); //wrong
 map.add('x', new Double(123.4)); //wrong

public static Iterator getIterator(List list) {
Collections.reverse(list);
return list.iterator();
}
public static void main(String[] args) {
List list = new ArrayList();
list.add("1"); list.add("2"); list.add("3");
Iterator iter = getIterator(list);
while(iter.hasNext())
System.out.print(iter.next() + ", "); //3,2,1
}

public class Sorter {
    public static void main(String[] args) {
        ArrayList<Integer> collection = new ArrayList<Integer>();
        collection.add(1); collection.add(5); collection.add(3);
        Collections.sort(collection);
        collection.add(2);
        Collections.reverse(collection);
        System.out.println(collection); //2,5,3,1
    }
}


Exception
#In Java, all exceptions are objects of the java.lang.Exception class. These objects carry the information related to the exception, including the stack trace.

#Whenever an exceptional event occurs, the runtime environment (JRE) generates the exception object and throws it.

#Two kinds of Exception:
1] Checked Exception
2] Unchecked Exception

#A try block is immediately followed by one or more catch blocks or a finally block. When an exception is caught and handled by a catch block, the execution continues from immediately after the try-catch block.

e.g. - catch(Exception1 | Exception2 | Exception3 e) {
          // Code for handling exceptions
}

#The finally block will be disrupted if an exception occurs inside it, or if System.exit() is invoked before it.

#All the exceptions belong to the Exception class, which is a child of the Throwable class.
Note: Exceptions are either handled or declared to be thrown.
If there is a checked exception which the method doesn't handle, it has to be declared using the throws clause.

#We can create a user-defined exception class by extending the Exception class:
We can also set messages by using the parameterized constructor:
public class DivisionException extends Exception { 
public DivisionException(String message) {
       super(message);
}
}

Note: Extending RuntimeException instead of Exception will make the custom exception unchecked.


XML
#Enterprise applications consist of modules built on different platforms. These modules may have to interact with each other and share data. So, we need a proper structure for representing data, irrespective of the platform. 
XML is one such language used to represent data in our applications in a structured manner. (It is extensible as it allows users to define tags)

#XML is said to be a well-formed XML as it follows the below "syntax rules".
--It has a root element that contains all the other elements
--Tags are case sensitive and are properly nested
--The attribute values are enclosed within quotes


#This can be prevented by setting rules for our XML files. These rules can specify elements and attributes that are allowed. We can also add rules for constraints on data types , for validating data, etc.

All such rules are defined in a document called Document Type Definition (DTD) or Schema file.

DTD is an old specification with its own syntax and has certain limitations, e.g. DTD cannot specify the data type of the contents in a tag. Schema, on the other hand, is a newer specification which uses the XML syntax itself. It also overcomes the limitations of DTD.

Logging

#In large applications, it becomes essential to keep track of all the events which are generated during the execution of an Enterprise Application. And keeping a log of the associated information helps in quick problem diagnosis, debugging, and maintenance.

Logging is the process of tracking the execution of a program where
Any event that is of interest to the programmer or the administrator can be logged
Relevant messages can be recorded in the event of exceptions and errors
The logs can be analyzed by the administrators later

Some of the popular logging APIs are
1] JDK Logging API
2] Apache Log4j
3] Commons Logging API

Log4j is written in Java and is open source. It provides mechanisms to directly log information to a variety of destinations such as database, file, console, etc. It also logs information in various formatting styles such as HTML, XML, etc.
Components:
1] The Logger : Accepts all the information to be logged during the execution of an application and redirects them to a specified Destination i.e., Appender
2] Appender : Specifies the output destination for logging. (FileAppender, ConsoleAppender, WriteAppender, JDBCAppender)
3] Layout : Specifies the format of the logged information (SimpleLayout, PatternLayout, HTMLLayout, XMLLayout)

Log4j can be configured programmatically or by using a configuration file.
log4j.xml and log4j.properties are the log4j configuration files.

A project needs the log4j-1.2.15.jar file in the build path to perform logging.

Levels in logger specify the severity of an event to be logged.
The org.apache.log4j.Level class defines all the levels. Each level has a unique integer associated with it.
The levels provided by Log4j are:
1] ALL 
2] TRACE
3] DEBUG
4] INFO
5] WARN
6] ERROR
7] FATAL
8] OFF

The logging level can be decided based on the requirement. For example, trace during development and error during deployment.
public static Logger getRootLogger()    // to get the root logger object.
public static Logger getLogger(String name)    // to get the logger with a specific name.

JAR is a file containing packaged class files and associated resource file, usually used to distribute application and library.

DOMConfigurator.configure("src/com/infy/infyretail/resources/log4j.xml");
Logger logger = Logger.getLogger(this.getClass());

logger.error(e.getMessage(), e);
throw e;

PROPERTIES

#As enterprise applications contain several modules, it is important to ensure consistency in the communication with the user. Generally, communication happens in the form of messages. These messages are used across modules and may require to be modified from time-to-time.
Whenever the messages are modified, the application needs to be rebuilt.
It would be better to have an external file for all such needs, and read them at runtime.
A "properties" file can help us with this.
It is a text file used to store any kind of textual information in the form of key-value pairs.
It can be easily understood by a Java application with the help of the java.util.Properties class.

#Properties files are usually used for
--Standardization of messages (user friendly error messages)
--Configuration of enterprise applications
--Internationalization or localization

#Some methods of properties class:
getProperty(key)
getProperty(key,defaultValue)
load(inputStream)

#A utility class with name 'AppConfig.java' will be available in the 'resources' package of the demos. This class has the code necessary for reading & loading key-value pairs from a properties file.

In AppConfig.java, the following line opens the file for reading
inputStream = new FileInputStream("configuration.properties");

And the following loads the content into the static properties object
PROPERTIES.load(inputStream);

Now we can get a property by specifying its key
AppConfig.PROPERTIES.getProperty("LOGIN_SUCCESS");
AppConfig.PROPERTIES.getProperty("PropertiesTester.TRANSACTION_SUCCESS", "Transaction successful");


MULTITHREADING

#In enterprise application environments, multithreading is used by App servers for maintaining thread pools to serve multiple users simultaneously. A thread pool is a group of ready-made threads with no overhead of creating new ones.

#ways to create thread:
1] extending thread class   (overridding run() method)
MyThread myThread = new MyThread();
2] implementing Runnable interface    (implementing run() method)     
Thread threadObj = new Thread(myRunnable);

In the previous example, observe how the thread object (threadObj) knows how to invoke the operation, the run() method, without knowing how the operation is performed. This behavior follows a design pattern called the Command Design Pattern.

This pattern is used to encapsulate a request as an object and pass to an invoker, wherein the invoker does not know how to service the request but uses the encapsulated command to perform an action.

In our example, the Runnable object (myRunnable) is the request object which is invoked by threadObj. Eventually, the encapsulated run() method is executed.

Sometimes there are objects which need to be accessed by multiple threads. Since threads share the memory of their parent process, they can access those "shared resource" together.

Every object has a built-in mutually exclusive lock mechanism called monitor. It can be used to achieve synchronization among threads. At a time, only one thread can acquire the monitor on an object.

Methods or code blocks that should be accessed by only one thread at a time should be marked as "synchronized" keyword.

#synchronized method:
public synchronized void withdraw(Double amount) {
       // Check balance
       // Withdraw amount
}

#synchronized block:
public void withdraw(Double amount) {
        synchronized(this) {
            // Check balance
            // Withdraw amount
        }
}

Such methods or blocks thus become thread-safe.

Inter-thread Communication methods:
wait()
notify()
notifyAll()

NOTE: These methods can be called from within a synchronized context only.

--If a thread needs to wait for an object's state to change while executing a synchronized method, it may call wait()
--The thread calling wait() will release the lock on the particular object and will wait for a notification
--It will wait till it is notified by another thread holding the lock on the same object
--If there are multiple threads waiting on the same object, the notify() method will notify any one among them. All the waiting threads can be notified using notifyAll()

The methods notify(), notifyAll(), wait() belong to Object class.


#While working with multi-threaded applications, a developer needs to take care of several aspects of threads like
--creating and supervising a number of threads
--synchronizing them
--handling their communication
--organizing their individual results 
All of it takes a great deal of effort. Moreover, they do not contribute to the business functionality.

Some important packages of the concurrency API are as follows:
java.util.concurrent
java.util.concurrent.locks
java.util.concurrent.atomic
Also, new concurrent data structures have been added in the Java Collections Framework.


An Executor initiates and controls the execution of threads, thereby improving the maintainability of programs.
The java.util.concurrent.Executor interface provides the execute() method, which executes a specified thread.
void execute(Runnable thread)

The java.util.concurrent.ExecutorService interface extends Executor and provides methods to manage threads. It also has methods for terminating them, and for tracking asynchronous tasks.
It defines the shutdown() method for an orderly shutdown. During the shutdown, the previously submitted tasks are executed, but new tasks will not be accepted.


Since Java 5, we can make use of java.util.concurrent.Callable<V> which is an interface similar to the Runnable interface but with an added capability to return an object or throw an exception. When a callable thread finishes execution, it returns a Future object, which is the representation of its result.

The Callable interface declares only one method - call()
This method represents a task that needs to be completed by a thread before returning a value. The callable thread class has to implement the Callable interface and override the call() method.

ExecutorService provides the submit() method to submit callable objects for execution and return their Future objects.

A Future object represents the value that will be returned by a thread in the future. This value can be retrieved using the get() method of the Future object.

If the result is ready, it will be returned
If not, the calling thread will be blocked

The java.util.concurrent.locks package provides support for locks as an alternative for using synchronized keyword to control access to a shared resource.

The Lock interface defines a lock, whose implementations provide more extensive locking operations compared to synchronized methods and blocks.

The important methods declared in Lock are:
lock()
tryLock()
unlock()

One of the implementation of the Lock interface is the Reentrant Lock.

A reentrant lock is the one in which if a thread tries to acquire a lock that it already holds for a different block, the request succeeds, i.e. a thread can acquire the lock multiple times without blocking on itself.
It means that locks are acquired on a per thread basis rather than per invocation basis. It is implemented by associating with each lock a counter and an owning thread.

When the counter is zero, the lock is unheld
When a thread acquires a previously unheld lock, the counter is set to one
If the same thread acquires the lock again, the counter is incremented by one
When the owning thread exits the synchronized block, the counter is decremented by one
When the counter reaches zero, the lock is released


The need for Fork/Join becomes clear in case of divide-and-conquer or recursive task-processing problems, where a work can be broken into smaller tasks, and their results can be combined to get a final result.

Fork means splitting a task into smaller subtasks which can be executed concurrently. Each subtask can be executed in parallel by different CPUs, or different threads on the same CPU

Join means merging the results of all the subtasks into a single result

The goal is to use all the available processing power to enhance the performance of an application. The core Fork/Join framework class is java.util.concurrent.ForkJoinPool which implements work-stealing algorithm.

In Java SE 8, java.util.Arrays implements Fork/Join framework in its parallelSort() method for sorting large arrays in a multiprocessor system.

While going through the concurrency API, it will be useful to know that the java.util.concurrent package provides a new set of collections for concurrent programming.

Some interfaces and classes in the java.util.concurrent package are:
BlockingQueue
ConcurrentHashMap


TESTING

#Testing of single small units of code such as a method or a class and asserting certain behavior is called unit testing. It is done by developers.

JUnit is an open-source unit testing framework for Java. It provides classes to write and run automated tests. It provides
--annotations to create and customize tests
--test fixtures (to fix state) for setting up each test
--assertions to test for expected results
--test suites for grouping tests

The org.junit.Test annotation turns a public method into a test method.
@Test
public void isValidUsernameValidTest() { }

Once a class has a test method, it is called a test class or a test case, and can be run as a JUnit test:
Right click on the class ---> Run As ---> JUnit Test

The JUnit Runner is responsible for constructing the instances of the test classes before running the tests. It is also responsible for making them available for garbage collection after the tests.

@Test
public void isValidUsernameValidTest() {
       Validator val = new Validator();
       Assert.assertTrue(val.isValidUsername("EMAIL"));
}

The above test asserts that the isValidUsername() method will return true for the given input. If the method has been properly implemented, it will return true, and the test will pass.


The Assert class provides static methods for testing a variety of conditions.
Here are some useful methods in the Assert class:
assertEqual()
assertTrue()
assertFalse()
assertNull()
assertNotNull()

NOTE: if an input is expected to cause an exception, assertions cannot be made.

JUnit allows us to create rules for adding a new behavior or redefining the behavior of each test method in a test class. The @Rule annotation is used for this purpose. It marks the public fields of the type TestRule which is an abstract class, and allows developers to create custom rules.

TestRule has a lot of implementation classes, out of which ExpectedException is an important one. It is used to verify expected exception types and messages.

Here is an example of using ExpectedException class along with @Rule annotation:
public class MyTest {
@Rule
    public ExpectedException ee = ExpectedException.none();
    
    @Test
    public void isValidUsernameInvalidTest() throws Exception {
           ee.expect(Exception.class);
           ee.expectMessage("VALIDATOR.Invalid_Username");
           Validator val = new Validator();
           val.isValidUsername("EMAIL"));
    }
}

Useful methods of ExpectedException:
none() -  return object of ExpectedException that expect no exception i.e., empty rule
expect()  - verifies that a code throws the specified exception
expectMessage()  - verifies the exception message of that exception


JUnit provides test suites to create groups of test classes which can be executed together.
The following annotations are used for creating test suites:

@RunWith(Suite.class)
@Suite.SuiteClasses
e.g.-
import org.junit.runner.RunWith;
import org.junit.runners.Suite;

@RunWith(Suite.class)
@Suite.SuiteClasses({UserTest.class, ProductTest.class, OrderTest.class})
public class TestSuiteDemo {  }

Code coverage aims at determining the extent to which code is tested during unit testing.
Some commonly used coverage tools are as follows:
--JCov
--JaCoCo
--Cobertura
--EclEmma
--Emma
We will be using EclEmma in the course.




EXTRAS:
Lambda function in java
Comsumer Interface in java

Tuesday, 7 August 2018

DBMS NOTES

Software Engineering


#software development activities required a systematic and disciplined approach i.e. Engineering Approach.

#Quality is not a feature which can be added to software after creating it! Quality must be built into software from the first day of development.

#SDLC models ---
Waterfall model
Incremental/Iterative model
Spiral model

#Requirement analysis
Activities:

Define the scope of the work
Understand processes
Focus on what needs to be automated
Understand functional and non-functional requirements
Document and Verify Requirements

Deliverables:

Software Requirement Specification(SRS)
Acceptance Test Plan
System Test Plan


#Functional Requirements:

Number of passengers booked per transaction should be max 6
SMS confirmation has to be sent to the registered mobile number on completing a successful transaction

Non Functional Requirements:

Time taken to complete a transaction should not be more than 3 minutes
1000 users should be able to use the application simultaneously


#waterfall model - design
Activities:

Create blueprint of the software
Define structure and behaviour of modules

Deliverables:

High Level Design
Detailed Design
Integration Test Plan
Unit Test Plan

#Coding
Activities:

Build the software
Unit test

Deliverables:

Unit tested code

#Configuration Management is the task of tracking and controlling changes in the software. It is achieved through version control tools like git, svn etc.
Configuration Management ensures:

We are using the right version
Deliverables are consistent with each other

Activity -
Version COntrol
Simultaneous USage
Change COntrol
Backup


# A class without any instance variable also follows Abstraction design principle

#Testing -
Activities:

Ensure that requirements are met

Deliverables:

Integrated and System tested software

#
Unit testing = developers
Integration testing = Development team and testing team
System Testing = Test team
Acceptance Testing = Customer

#We write "Unit A Driver" to test "Unit B" and "Unit C"
We write "Unit B stub" to test "Unit A"

#Regression testing =
While doing changes programmers might end up introducing some new defects in the software. Testing and fixing such defects is known as Regression testing.
In the maintenance phase the product must be tested against previous test cases.

#Performance Testing =
1] Load testing  == Maximum operating capacity
2] Endurance testing  == sustain continuous expected load
3] Stress Testing  == determine a breaking point of system (stability)  (expected situation)
4] Spike testing  == ability to handle dramatic changes of load  (unexpected situation)


#Deployment -
Activities:

Assemble
Install
End-user Training and
Sign-off

Deliverables:

Implemented Software
User manual


#Waterfall model is not suitable for:

Small projects
Complex projects
Accommodating changes (Correct Answer)
Maintenance Projects

#TOp down testing uses Stubs

#Agile software development is a group of software development methods in which requirements and solutions evolve through collaboration between self-organizing, cross-functional teams. It promotes adaptive planning, evolutionary development, early delivery, continuous improvement, and encourages rapid and flexible response to change.

#Sacrum Cycle ==

Product Backlog: The product backlog comprises an ordered list of requirements that a scrum team maintains for a product. It consists of features, bug fixes, functional, non functional requirements, change requests, enhancements etc.

Sprint Backlog: The sprint backlog is the list of tasks the development team must address during the upcoming sprint.

Sprint: A sprint (or iteration) is the basic unit of development in scrum. The sprint is a timeboxed effort; that is, it is restricted to a specific duration.

Working increment of software: The increment (or potentially shippable increment, PSI) is the sum of all the product backlog items completed during a sprint and all previous sprints.

#Sprint is time boxed development period in Scrum.
Each sprint starts with a sprint planning: event that identify the work for the sprint, and make an estimated commitment for the sprint goal.
Each sprint produces the planned increment of the product: outcome could be a working code.
Inspect: the client performs the acceptance testing during inspection and provides feedback.
Adapt: the team incorporates the feedback provided by the client to improve the work during subsequent sprint.
CYCLE = PLAN > PRODUCE > INSPECT > ADAPT

#Agile adoption results in frequent delivery of features to end users. Thus testing has to be performed more often. It also increases the probability of defects through manual errors. Most often there is no time to manually test the entire application as releases could often be only weeks apart.

Test automation is a key strategy to achieve faster delivery cycles. Agile teams use tools like xUnit, QTP, Selenium etc. to automate Unit testing, Functional testing, Regression testing etc. Test cases are written as code using these frameworks and can be executed at every build to detect bugs.

Another practice usually employed in agile projects is Continuous Integration. It essentially means that developers integrate their code to a common region frequently (often daily or multiple times a day). It's main aim is to detect and fix integration problems at an early stage.


#
Traditional Methods                                                                Agile Methods
Predictive Adaptive
Requirement changes are resisted                                                   Changes are welcome
Limited Interaction with Business/ Product and Testing                              Collaboration between various teams
Single Delivery Multiple Short / Small Deliveries
Process and Documentation Focus Communication Focus


#Advantages of Agile ==
1] Time to Market: Early and regular releases
2] Revenue: Early Return On Investment (ROI)
3] High Quality and Productivity: Testing is integrated throughout the cycle
4] Business Engagement/Customer Satisfaction: Active involvement of a business owner and the high visibility of the product and progress
5] Motivated Teams: Active involvement & collaboration make Agile development more enjoyable


#Which is more important, the Product or the Process?
--Process decides the Quality of the Product. Therefore process is important.

#Quality Control

Focus is on the Product
QC measures a product against the existence of a required attribute
Major QC activity is identifying defects and correcting them (Rework)
Inspections, Reviews and Testing activities

Quality Assurance

Focus is on the Process rather than Product
QA ensures "Fitness for Purpose"
Auditing and Reporting functions
Building process guidelines, checklists, templates, Training activities

#Cost of Quality (CoQ) is a measure of costs (effort) incurred in ensuring quality. These costs include:

Prevention cost such as training, process improvement etc.
Appraisal cost such as reviews, testing etc.
Correction/Rework/Failure cost such as rework to fix defects after testing etc.
CoQ is typically measured as percentage of effort spent to ensure quality compared to total project effort. Thus CoQ of 31.38% means that compared to overall effort, the team has spent 31.38% of effort to ensure quality.

CoQ = ((Prevention cost + Appraisal cost + Failure cost) x 100) / Total Project effort
Appraisal Cost, Failure Cost, Prevention Cost and Total Project Effort are all measured in person months.


#Ensures all requirements are implemented in the software. Requirement Traceability Matrix (RTM) maintains reference between: (Eg - page 60)

Requirement Number
Requirement Description
HLD Reference and DD Reference
Unit Test Plan Reference, Integration Test Plan Reference
System Test Plan Reference
Horizontal traceability ensures all requirements are incorporated into product.

Vertical traceability ensures no unnecessary functionality is included unless specifically called for by a requirement.


#defect classification -
1] logical
2] User Interface
3] Maintainability
4] Standards

#If a requirement defect slips from requirements stage unnoticed, and gets caught during later phases, it becomes much more costlier to fix them.

#Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes. In software development context 80% of the defects are due to 20% of the causes.

#Basic MAtrics at Infosys:
1] Efforts
2] Defects
3] Schedule
4] Size

#Other derived Metrics used at Infosys

Defect Injection Rate
Defect Removal Efficiency
Review Effectiveness
Turn Around Time

#Process Infrastructure:
1] PRIDE
2] IPM+
3] ENCORE



DataBase


#Reference =
http://ilp/fp2/Generic/DBMS/page/78
http://ilp/fp2/Generic/DBMS/page/99

#
--CREATE TABLE Department(D_id number(3))
--SELECT * FROM user_tables
--SELECT * FROM TAB
--ALTER TABLE department ADD d_name varchar2(30)
--DROP TABLE department
--DESC department
--alter table Department add constraint Dept_UK Unique(Dept_Name)
--alter table Department rename constraint <old_name> to <new_name>
--SELECT EName, Salary * 2 AS Double_Salary FROM Employee
--SELECT EName, 30 AS Value FROM Employee
--SELECT DISTINCT Dept FROM Employee                         (NULL is also distinct)
--SELECT DISTINCT Dept, Manager FROM EMPLOYEE                (for multiple columns distinct)
--SELECT ID, ENAME FROM Employee WHERE SALARY BETWEEN 30000 and 50000
--UPDATE Employee SET SALARY = SALARY * 1.3, BONUS = SALARY * 0.30 WHERE ID = 1
--TRUNCATE table Employee
--DELETE FROM Employee
--DELETE FROM Employee WHERE Id = 5
--select empid, salary AS "Current Salary", (salary * 1.1) AS "New Salary", (salary * 0.1) AS "Incremented Amount" from empdetails
--SELECT City, Country, CONCAT(City, Country) "CONCAT", City || Country "ConcatByOperator", CONCAT(CONCAT(City, ', '), Country) "NestedConcat" FROM Weather;
--SELECT RecordDate, SUBSTR(RecordDate,1,2) "DAY", SUBSTR(RecordDate,4,3) "MONTH", SUBSTR(RecordDate,8) "YEAR" FROM Weather;
--SELECT MinTemp, TO_CHAR(MinTemp) DEF_FORMAT, TO_CHAR(MinTemp, '999.99') "FIXED_DIGITS", TO_CHAR(MinTemp, '9,9.99') "COMMA" FROM Weather;
--SELECT '1000.98' "ORIG_NOFORMAT", TO_NUMBER('1000.98') "CONV_NOFORMAT", '1,000.98' "ORIG_FORMAT", TO_NUMBER('1,000.98', '9,999.99') "CONV_FORMAT" FROM DUAL;
--SELECT RecordDate, TO_CHAR(RecordDate, 'MON') "MONTH", TO_CHAR(RecordDate, 'Month') "FULL_MONTH", TO_CHAR(RecordDate, 'Dy') "DAY", TO_CHAR(RecordDate, 'Day') "FULL_DAY" FROM Weather;
--SELECT TO_CHAR(RecordDate) DEF_FORMAT, TO_CHAR(RecordDate, 'DD/MM/CCYY') INDIAN, TO_CHAR(RecordDate, 'MM/DD/YY') AMERICAN FROM Weather;
--SELECT '01-Jan-2014' DATE_STRING, TO_DATE('01-Jan-2014') CONV_NOFORMAT, TO_DATE('01-Jan-2014', 'DD-Mon-YYYY') CONV_FORMAT FROM DUAL
--SELECT 'Jan-01-2014' DATE_STRING, TO_DATE('Jan-01-2014', 'Mon-DD-YYYY') CONV_FORMAT FROM DUAL
--SELECT COUNT(Dept) Count1, COUNT(DISTINCT Dept) Count2 FROM Employee
--SELECT CITY, NVL(CITY, 'Not Available') NVL_CITY, MINTEMP, NVL(MINTEMP, 0.0) NVL_MINTEMP, NVL(TO_CHAR(MINTEMP), 'Not Available') NVL_MINTEMP2 from Weather;
--select count(*) sale_count from sale where add_months(sldate, 40) > sysdate       #query to display the number of sales that were made in the last 40 months
--insert all
into Student values(20, 'Amit')
into Student values(21, 'Nik')
into Student values(22, 'Raj')
--SELECT Id, EName, Designation, Salary,
CASE Designation
    WHEN 'SE' THEN Salary * 1.2
    WHEN 'SSE' THEN Salary * 1.1
    ELSE Salary * 1.05
END New_Salary
FROM Employee;
--SELECT EName, Designation, Bonus,
CASE
    WHEN Designation = 'SE' THEN Bonus + 500
    WHEN Designation = 'SSE' THEN Bonus + 1000
    WHEN Designation = 'PM' THEN Bonus + 2000
    ELSE  Bonus
END AS NewBonus
FROM Employee;
--SELECT Id, EName, Designation, Salary,
CASE
    WHEN Designation = 'SE' OR Designation = 'SSE' THEN TO_CHAR(Salary * 1.2)
    WHEN Designation = 'PM' AND Salary >= 90000 THEN 'No hike'
    ELSE TO_CHAR(Salary * 1.05 )
END New_Salary
FROM Employee;
--Select ID, ENAME, DOJ, SALARY, DEPT, DESIGNATION FROM Employee ORDER BY DEPT ASC, DESIGNATION DESC
--select to_char(sldate, 'Month') MONTH, count(*) NUmber_sale from sale group by to_char(sldate, 'Month') order by 2 desc
--select prodid, sum(quantity) qty_sold from saledetail where quantity > 1 group by prodid having count(*) > 1
--select distinct Itemtype,
case
 when price between 0 and 499 then 'Cheap'
 when price between 500 and 1999 then 'Affordable'
 when price between 2000 and 4999 then 'Expensive'
 when price >= 5000 then 'Very Expensive'
end as classification
from item
order by 1,2
--select to_char(to_date('Jan/10/2015', 'Mon/DD/YYYY'), 'Month') month, to_number('2,50,000.00', '9,99,999.99') amount from dual
--select pymtmode, count(*) pymtcount from orders where to_number(to_char(pymtdate, 'YY'),'99') < 15 group by pymtmode having count(*) > 1
--select to_char(sysdate, 'Mon/DD/YYYY Day') currentdate from dual
--select orderid, round(abs(months_between(orderdate, pymtdate)* 30)) noofdays from orders
--select count(distinct itemtype) noofitemtypes from item


#A file-based approach suffers following problems:
1] Dependency of program on physical structure of data
2] Complex process to retrieve data
3] Loss of data on concurrent access
4] Inability to give access based on record (Security)
5] Data redundancy

#Database is a shared collection of logically related data
Database Management System is a software system that enables users to define, create, maintain, and control access to the database.
Application Program interacts with a database by issuing an appropriate request

#DBMS offers -
Data Management, Integrity, Transaction, Concurrency, Security, Recovery and Utilities

#Types of DataBase System:
1] Hierarchical
2] Network
3] Relational
4] NoSQL

#Tables = Relations
Attributes = Columns = Fields
Rows = Records = Tuples
Number of Columns = Degree of Relation
Number of Records = Cardinality of Relation

#A relationship among a set of values in a table is represented by a "ROW"

#Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle.
Types of Integrity Constraints:
1] Entity Integrity  (Primary Key)   (between rows)
2] Domain Integrity  (DATA TYPES, CHECK CONSTRAINT)   (between columns)
3] Referential Integrity  (Foreign Key)   (between tables)

#When two or more columns together identify the unique row then it's referred to as Composite Primary Key.

#Terms of Foreign KEys:
Parent Table = Master table = Referenced table
Child table = Referencing table

#ER model is a graphical representation of entities and their relationships which helps in understanding data independent of the actual database implementation.
Entity = Real world Object
Attribute = Property of Entity

#Crow foot notation is one of the ways to represent cardinality of relationship in an ER Model.

#In 1 : N relationships, the foreign key and relationship attributes are always added to the many (N) side of the relationship.

#An attribute in a table/relation can reference another attribute in the same table/relation and this is called Self Referencing Foreign Key

#CHAR = CHARACTER
VARCHAR2 = CHARACTER VARYING

#SQL supports SMALLINT, INTEGER and INT data types that are used for storing whole numbers.
SQL, unlike programming languages, does not provide support for arbitrary length numbers i.e. numbers not bound by size limits. For e.g. Python supports "bignum" and Java supports "BigInteger" data types.

#Nonintegral data types have an integer part and a fractional part. Either NUMERIC, DECIMAL or NUMBER data types can be used to store nonintegral numbers.

#SQL supports following data types for representing date and large objects:
1] DATE    (default format = DD-MON-YY )
2] TIMESTAMP  (Storing date data with precision up-to 1 billionth (9 digits) of a second.)
3] CLOB    (Character Large Object) (More than 4000 bytes data)
4] BLOB    (Binary Large Object)  (movies, images with size up to 4GB)

#Not equal to   =  <>

#Various constraints that can be created on database tables are:

NOT NULL
PRIMARY KEY
CHECK
UNIQUE
FOREIGN KEY

We can also specify DEFAULT value for a column. Oracle database does not consider DEFAULT as a constraint.

#CREATE TABLE Student (
    StudentId INTEGER CONSTRAINT stud_sid_pk PRIMARY KEY,
    FName VARCHAR2(10) CONSTRAINT Stud_Fname_nn NOT NULL,
    LName VARCHAR2(10),
Gender CHAR(1) CONSTRAINT Stud_gender_ck1 CHECK(Gender IN('M', 'F')),
ContactNo NUMBER(10) CONSTRAINT Stud_cno_uk UNIQUE,
DOJ DATE DEFAULT SYSDATE);
--CONSTRAINT Stud_gender_ck1 CHECK(Gender IN('M', 'F')));

#Column level constraints :
CREATE TABLE Student (
    StudentId INTEGER CONSTRAINT stud_sid_pk PRIMARY KEY,
    FName VARCHAR2(10),
    ContactNo NUMBER(10));

Table level constraints :
CREATE TABLE Student (
    StudentId INTEGER,
    FName VARCHAR2(10),
    ContactNo NUMBER(10),
    CONSTRAINT stud_sid_pk PRIMARY KEY (StudentId));

#NOT NULL can be defined only at the column level
Unlike Primary Key, UNIQUE constraint allows NULL values. A table can have many UNIQUE constraints.
Referential integrity constraint allows NULL value

#CREATE TABLE Marks(
    CourseId INTEGER,
    StudentId INTEGER CONSTRAINT marks_sid_fk REFERENCES Student(StudentId),
    MarksScored DECIMAL(5,2));

CREATE TABLE Marks(
    CourseId INTEGER,
    StudentId INTEGER,
    MarksScored DECIMAL(5,2),
    CONSTRAINT marks_sid_fk FOREIGN KEY (StudentId) REFERENCES Student(StudentId));

#The data type of the column can be modified with the MODIFY clause. The size of the data type can be increased or decreased. The column should be empty for decreasing the size or for changing the data type from one type to another.

#The column dropped cannot be recovered back. At least one column should be present in the table after dropping the columns.

#Multiple rows can be inserted through a single INSERT statement only when it is used with SELECT statement.

#DISTINCT =
DISTINCT removes duplicates based on all the columns in the SELECT clause
Usage of DISTINCT should be avoided as far as possible due to performance issues

#Equal to operator cannot be used to check for NULL values    (eg. - where bonus = NULL)
Trailing spaces are ignored for CHAR data type.
Leading spaces are not ignored for CHAR data type.
Trailing spaces are not ignored for VARCHAR2 data type.
Leading spaces are not ignored for VARCHAR2 data type.

#varchar will reserve space for null values while varchar2 will not reserve any space for null values.

#"Frank John's Wicked Grave Haunts Several Dull Owls" mnemonics for execution

#(date1 < date2) find answer? (past is smaller)

#Which of the following columns in a table cannot be updated?
A primary key column which also serves as foreign key reference in another table.

#TRUNCATE statement deletes all rows from the table as it does not support WHERE clause. TRUNCATE statement is a faster option compared to DELETE when you have to delete all rows from the table.

#Status Code:
ORA-00000 Successful Completion
ORA-00001 Unique constraint violation
ORA-00904 Invalid Identifier
ORA-0913 Too Many Values

#BETWEEN operator can be replaced by AND.
IN operator can be replaced by OR.

#There are two columns in every table  -  Rownum and Sysdate

#single row function produces one row of output for each row of input
multi row function produces just one row of output, irrespective of the number of rows in input

Numeric functions are single row functions that accept a numeric value and return numeric output. -
ABS, ROUND, FLOOR, CEIL
Character functions work on character strings and can return a character string or a numeric value. -
UPPER, LOWER, CONCAT, LENGTH
Substring function is used to extract part of a string. It has the following syntax
SUBSTR(value, start_position, length)
Use conversion functions to convert data from one format to another. -
TO_CHAR(value, format) ; TO_DATE (value, format) ; TO_NUMBER (value, format)
DATE Functions - SYSDATE, SYSTIMESTAMP, ADD_MONTHS(date, no_of_months), MONTHS_BETWEEN(date1, date2)

Aggregate functions operate on multiple rows to return a single row. Some aggregate functions like SUM (total), AVG (average) operates only on numeric columns while others like MIN (lowest value), MAX (highest value) and COUNT (number of rows) operate on all data types. All aggregate functions ignore NULL values except COUNT(*).
NVL(value1, value2) : Substitutes value1 by value2 if value1 is NULL. The data type of value1 and value2 must be same.
USER : Returns the current logged in user
NVL2(v1,v2,v3)
NULLIF(v1,v2)

#MIN, MAX, COUNT can be applied on both number and character typed columns.

#CASE Statement can also be used with WHERE, GROUP BY etc. CASE statement has two different syntax styles: Simple CASE expression and Searched CASE expression.
Simple CASE expression can be used when all conditions check for equality against a single column.
Searched CASE expression is used to compare different conditions.
NOTE: ELSE is optional. If ELSE is omitted and no condition is true, then NULL is returned by CASE.

#Column position in the query can be used as an alternative to column name in ORDER BY clause
Select ID, ENAME, DOJ, SALARY, DEPT, DESIGNATION FROM Employee ORDER BY 2

#Having allows aggregate functions to be used as filter criteria which cannot be done using WHERE clause.

#GROUP BY must always be after the WHERE clause otherwise aggregate functions will be calculated wrongly.
Having must always be after Group by as it filters records based on aggregate functions calculated during GROUP BY evaluation

#Nested aggregate function cannot be used in SELECT clause without GROUP BY clause.
Columns specified in SELECT clause must be part of the GROUP BY clause.
a GROUP BY clause is mandatory in SELECT query having one aggregate function along with other columns
 A query that has a nested aggregate function must have a GROUP BY clause

#Use UNION and UNION ALL clause to combine results from two or more SELECT statements. The select statements may be from same or different tables.They must have same number of columns and their data types at same position in both the query must be compatible (either same or convertible through automatic conversion).


JOINS

--SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL FROM Employee E INNER JOIN Computer C ON E.COMPID = C.COMPID
--SELECT E.Id, E.ENAME FROM Employee E INNER JOIN Employee J ON E.Dept = J.Dept AND E.Id <> J.Id AND J.ENAME='Jack Abraham';
--select e.id empid, e.ename empname, m.id mgrid, m.ename mgrname, make from employee e  inner join employee m on e.manager = m.id inner join computer c on e.compid = c.compid
--select s1.sid, s1.sname, s1.location from salesman s1 cross join salesman s2 where s1.location = s2.location and s1.sid <> s2.sid
--select s1.sid, s1.sname, sum((p.price * s2.quantity)) tamount, sum((p.price * s2.quantity * p.discount * 0.01)) tdiscount from salesman s1 inner join sale s3 on s1.sid = s3.sid inner join saledetail s2 on s3.saleid = s2.saleid inner join product p on s2.prodid = p.prodid group by s1.sid, s1.sname order by 3 desc, 4 desc
--select q.sname, q.quotationid from quotation q inner join orders o on q.quotationid = o.quotationid where abs(o.orderdate - o.delivereddate) <= 5

#For optimally, use filter condition Combining with the join condition using AND operator.

#LEFT OUTER JOIN:  However care must be taken that the filter condition is using an attribute from the main table. Any attempt to filter (except check for NULL) using attribute from lookup table will result in wrong output as all NULL rows will get filtered and the purpose of using OUTER join will get defeated.
If we want to conditionally fetch values from the lookup table then the additional criteria must be combined with the join condition using AND operator.

#FULL OUTER JOIN: For two tables with p and q rows, a 1:1 relationship and m matched rows the total number of rows in the resultset is m + (p - m) + (q - m) = p + q - m.

ALTERNATE SYNTAX:
CROSS JOIN:       SELECT E.ID, E.ENAME, E.COMPID AS E_COMPID, C.COMPID, C.Model FROM Employee E, Computer C
INNER JOIN:       SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL FROM Employee E, Computer C WHERE E.COMPID = C.COMPID
LEFT OUTER JOIN:  SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL FROM Employee E, Computer C WHERE E.COMPID = C.COMPID (+)
RIGHT OUTER JOIN: SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL FROM Employee E, Computer C WHERE E.COMPID (+) = C.COMPID


SUBQUERY

--SELECT EName,Dept FROM Employee WHERE Salary>(SELECT AVG(Salary) FROM Employee);
--Interesting Question - http://ilp/fp2/Generic/DBMS/page/420
SELECT ename FROM emp WHERE ename = (SELECT MIN(ename) FROM emp);
--SELECT DEPT FROM Employee GROUP BY DEPT HAVING SUM(Salary) = (SELECT MAX(SUM(Salary)) FROM Employee GROUP BY Dept);
--SELECT CompId, Make, Model FROM Computer C WHERE EXISTS (SELECT 1 FROM Employee E WHERE E.CompId = C.CompId);
--SELECT CompId, Make, Model FROM Computer C WHERE NOT EXISTS (SELECT 1 FROM Employee E WHERE E.CompId = C.CompId);


#A subquery must be enclosed in brackets and can be used in SELECT, FROM, WHERE and HAVING clauses.
Subquery in SELECT and FROM clause are rarely used. Subqueries in WHERE and HAVING clauses are classified into ""Independent and Correlated subqueries"".
In an independent subquery, the inner and outer query are independent (the inner query has no reference to the outer query) of each other.
Subquery can be used in -
1] Field names in the SELECT statement
2] The FROM clause in the SELECT statement
3] The HAVING clause in the SELECT statement
4] The WHERE clause in SELECT as well as all DML statements

#A Correlated subquery is one in which the inner query that depends upon the outer query for it's execution. Specifically it uses a column from one of the tables in the outer query. The inner query is executed iteratively for each selected row of the outer query. In case of independent subquery, the inner query just executes once.
eg - SELECT EName, Dept, Salary FROM Employee E1 WHERE Salary > (SELECT AVG(Salary) FROM Employee E2 WHERE E2.Dept = E1.Dept)

#EXISTS keyword is used to check presence of rows in the subquery. The main query returns the row only if at least one row exists in the subquery. EXISTS clause follows short circuit logic i.e. the query calculation is terminated as soon as criteria is met. As a result it is generally faster than equivalent join statements.

#NOT EXISTS is opposite of EXISTS i.e. it is used to check absence of rows in the subquery. The main query returns the row only if at least no row exists in the subquery. It also uses short circuit logic and is hence faster.

#A subquery can appear on either side of a comparison operator

#Oracle database poses no limit on the number of subquery levels in the from clause of the top level query.
you can nest upto 255 levels of subqueries in the where clause.

#Inner queries in WHERE clause can not contain ORDER BY clause.


    TRANSACTIONS

#Database provides three statements for transactions
1] SET Transaction
2] COMMIT
3] ROLLBACK

#AUTOCOMMIT property:
1] SET AUTOCOMMIT ON: Changes the mode of connection to ON. In this mode COMMIT command is automatically issued after every SQL statement that alters the state of a database.
2] SET AUTOCOMMIT OFF: Changes the mode of connection to OFF. In this mode user is expected to provide an explicit COMMIT or ROLLBACK command to complete the transaction.
3] SHOW AUTOCOMMIT

set aautocommit on will commit previous statement only if there is no commit/rollback in the next statement.

#All transactions exhibit ACID properties:
1] Acidity
2] Consistency
3] Isolation : Transactions execute in isolation of each other. In other words partial execution of one transaction is not visible to other transactions. Only committed data is visible to other transactions.
4] Durability : Once a transaction is committed, it is permanently saved, the data is preserved even in the case of power failure, hardware failure etc.

SELECT, DESCRIBE does not change the state of database.



NORMALIZATION



#An attribute A is said to functionally determine attribute B if each value of A is associated with only one value of B. A is called the Determinant while B is called the Dependent.

#Three types of FD:
1] Full FD
2] Partial FD
3] Transitive FD

#Normalization is the process of reorganizing data in a database so that data redundancy is reduced.

#2NF -
We decompose the relation by moving all the attributes that are dependent upon the partial candidate key (StudentId) into a new relation called Student. The partial candidate key (StudentId) becomes the primary key of this relation.

# Online Transaction Processing vs Online Analytical Processing
Data Dynamic (day to day transactional/Operational data) Static (historical data)
Purpose of data To control and run fundamental business task Assist in planning, problem solving and decision making
Data Atomicity Data is stored at the microscopic level Data is aggregated or summarized and stored at a higher level
Normalization Normalized databases to facilitate insertion, update, and deletion De-normalized databases to facilitate queries and analysis
Operations Simple operations that use small amounts of data Complex queries using large amounts of data
Updates Updates are frequent Updates are infrequent
Response time Fast response time is important. Transactions are slow. Queries consume a lot of bandwidth
Data must be up-to-date, consistent at all times
Joins queries Joins are more and complex as tables are normalized Joins are few and simple as tables are de-normalized

#Index is a database object that allows us to search data in tables quickly and efficiently.
Indexes are also used to enforce data integrity. An index is automatically created for constraint enforcement when you create Primary Key and Unique constraint.

SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'EMPLOYEE';

QUICK FACTS:
->Index for PRIMARY KEY and UNIQUE constraints are automatically created and dropped during table creation and deletion respectively.
->All other indexes have to be explicitly maintained by DBAs by issuing CREATE INDEX, ALTER INDEX or DROP INDEX commands. These indexes facilitate faster search and are created on columns that are used in search criteria i.e. the WHERE clause.
->Index is maintained automatically by the database server whenever data gets added, deleted or modified in the database.
->Size of index is directly proportional to size of data in primary table.
->Index can be created on multiple attributes as well. These are called composite index.
->Each table can have only one clustered index where data in the primary table is physically sorted in that order. Usually this is created on the primary key columns.
->There is no limit of number of non-clustered indexes that can be created on a database table.
->It increases the performance of SELECT statement but, decrease the performance of other query type.
->Indexes should be created on attributes used in the "Where" clause.
->A table can have only one clustered index and any number of non clustered indexes


#Data is retrieved from tables using Table Scan, Index Unique Scan or Index Range Scan. While the first process does not use an index at all, the other two leverage index created in database. Index is used only if sort order of data in the index can be leveraged for faster search.

#A full table scan is selected when:
--A large portion of the rows in the table must be accessed
--Index does not exist on the column being searched
--Index exist but cannot be used

#Index Unique Scan: It always retrieves one row from the database.
It is used when there is an equality predicate on a unique index or an index created as a result of a primary key constraint.

#Index Range Scan: It is used when SQL statement has an equality or range predicate (>, <, between) on a non-unique index key, or a range predicate on a unique index key. data is returned in the ascending order of index columns.

#Indexes cannot be used for negative criteria. Imagine you being asked to find all topics other than Union using the index of database book. A more sensible option is to do a table scan i.e. go page by page and omit any topic if it has UNION. E.g. - SELECT * FROM Employee WHERE Id <> 5

#Index cannot be used if expression is used with a column even if that column has an index present. This is because index has original values from table while here database has to look up calculated values which will not be present in the index. A table scan is performed.

#Index will be used if there is a partial match on a composite index. However the match must happen from the first column otherwise sorting cannot be leveraged.

#Index cannot be used if functions are used with a column even if that column has an index present. This is because index has original values from table while here database has to look up calculated values which will not be present in the index. A table scan is performed. You could create index on functions but for that the function must be deterministic.

#Index cannot be used if wildcard in LIKE operator is the first character.

#IF there is multiple index search then the database determines which one to choose based on which one results in faster filtering. This determination is made using database statistics.

#BEST PRACTICE:
Do not use Select ‘*’, instead fetch only attributes that are required. It improves code maintainability as clients invoking this query will not get impacted if new columns are added to the table in future.


BIG DATA

#Defining characteristics of data are:
1] Variety
2] Volume
3] Velocity

All the data put together is BIG DATA.
More than 85% of the world's data is unstructured (Facebook, Twitter, YouTube, NetFlix, Flickr etc.) and categorized as big data.

#NoSQL servers usually run on low cost hardware which can fail anytime. Therefore, the data is replicated and stored on multiple servers.
NoSQL databases are Partition Tolerant

#All databases should ideally provide the following features (CAP):
1] Consistency – An end user must be able to see the latest data at all times.
2] Availability – Every database request must be responded by the server.
3] Partition Tolerance – When two systems cannot talk to each other in a network, it is called network partition. Our DB system should continue to function even if there is a network partition.

#Eric Brewer’s CAP Theorem states that it is impossible for a distributed system to guarantee all three (Consistency, Availability and Partition Tolerance).
NoSQL databases are partition tolerant. In case of a network partition, there is a tradeoff between consistency and availability – some NoSQL systems give importance to Consistency while others give importance to Availability.

#NoSQL databases guarantee that eventually all replicas will be identical. This is called eventual consistency.
In contrast to NoSQL’s eventual consistency, SQL databases always provide consistent data. This is called strong consistency.

#NoSQL databases store data in four ways.
1] Key-Value Store (e.g. Riak, Redis, MemcacheDB)
2] Column Family Store (e.g. HBase, Cassandra)
3] Document-Oriented Database (e.g. MongoDB, CouchDB)
4] Graph Database (e.g. Neo4J, HyperGraphDB, InfoGrid)


#Operations on a Key-Value store
1] Get(key)
2] Put(key, value)
3] Multi-get(key1, key2, .., keyN)
4] Delete(key)


#Column Family Store characteristics:
1] Column Family databases can handle hundreds of terabytes of data easily.
2] Updates are performed without reading the row that contains it. Hence writes are very quick.
3] Column Family databases support real-time insertion of huge amount of data e.g. one million writes per second.
e.g. -
-Real-time weather data (min. temperature, max. temperature, air pressure etc.) collected through sensors at multiple locations.
-Log files of web servers for data analysis.


#Document Oriented databases have following characteristics:  (MongoDB - a popular document-oriented database, open source)
1] Supports flexible schema, i.e. each document can have different attributes.
2] Provides a rich query language for storing, fetching, modifying and deleting data. This allows search and filter by any attribute value.
3] Ensures rapid application development e.g. In a fast changing retail store scenario, where products and their attributes are frequently changing.
4] Are suitable for storing data that does not require frequent updates, but is read many times.


#Graph Database characteristics:
1] Graph databases are suited for data that are heavily interconnected through relationships.
2] Graphs do not need joins for querying.
3] Graph databases use graph theory for traversal. It improves performance by keeping track of and thereby skipping nodes already visited.
4] Graph databases provide Atomicity, Consistency, Isolation and Durability (similar to SQL).

Cypher Query Language provided by Neo4j is used to query Graph Database:
e.g. - CREATE (u: Users { userid : 1, username : "Priya" });
MATCH (u: Users) WHERE u.username = "Priya" RETURN u;

Different NoSQL databases and relational databases complement each other as they are suitable for different scenarios. Increasingly applications are being designed to use multiple types of databases leveraging each for their strength in specific areas. This is called "polyglot persistence" (Using different databases for different purposes in a same project).


#MongoDB Document Oriented
Cassandra Column Family
Neo4J Graph Database
Oracle RDBMS
Riak Key Value Store


#MongoDB:
->CRUD operations are performed through insert, find, update and remove operations on Collection object, NOT NULL, UNIQUE, FOREIGN KEY and CHECK constraints are not supported
->Joins and Subquery are not supported
->INSERT :  db.emp.insert([ {_id: 2, ename: "Ethan", designation: "Manager", salary: 90000},
{_id: 3, ename: "Emily", designation: "Analyst", salary: 25000} ]);
->NOTE: If _id field is not specified then it is automatically generated with a unique value.

->Retrive data:
db.emp.find();
db.emp.find({designation: "Analyst"});
db.emp.find({}, {ename: 1, salary: 1});
db.emp.find({}, {ename: 1, salary: 1, _id: 0});                    #In order not to display _id field
db.emp.find({salary: {$in: [30000, 90000]}});
db.emp.find({$or: [{designation:"Analyst"}, {ename:"Ethan"}]}, {ename: 1, salary: 1});

->Update:
db.emp.update({_id: 1}, {$set: {salary: 35000}});
db.emp.update({_id: 2}, {$set: {dept: "HR"}});                     #Adding new Fields
db.emp.update({_id: 4}, {$set: {designation: "Analyst", dept: "ETA"}});
db.emp.update({}, {$set: {salary: 10000}}, {multi: true});
db.emp.update({_id: 3}, {ename: "Mark", designation: "Manager"});

->Delete:
db.emp.remove({_id: 4});
db.emp.remove({salary: {$lt: 30000}});
db.emp.remove({});

#Which of the following pairs of queries are equivalent under all circumstances?
1]SELECT DeptNo FROM Emp GROUP BY DeptNo;
  SELECT DISTINCT DeptNo FROM Emp;

2]SELECT D.* FROM Emp E, Dept D WHERE D.DeptNo = E.DeptNo
  SELECT * FROM Dept WHERE DeptNo IN (SELECT DeptNo from Emp)

3]SELECT AVG(DeptNo) As AVG FROM Emp;
  SELECT SUM(DeptNo) / COUNT(*) AS AVG FROM Emp;

4]SELECT COUNT(*) - COUNT(DeptNo) FROM Emp;
  SELECT COUNT(CASE WHEN DeptNo IS NULL THEN 0 ELSE NULL END) FROM Emp;

ANS - 1,4