Wednesday 13 July 2016

postgreSQL - PSQL MATA Commands


PSQL META CommandUsage
\dtList the table
\dnList the sachems
\lList the databases
\diList of Indexes
\d <table-name>Describe table(Columns and their type)
\dbList of table spaces(In which places tables are storing)
\duList of users
\dsList of system table
\dvsList of system views
\l+List of databases with size
\dt+List of tables with size
\!present working directory
\dpTable space privileges 
\qQuit from psql shell
select current_userTo view current user
How to switch to database/user?
\c <db> <user>

how to create database outside?
createdb -p 5444 <db-name> -u <user>

Note: we can refer for port in postgres.conf

How to write Query results in to file?

\o <path>
select * from <table-name>;
\o --->switching off

How to importing script?
\i <path of .sql file>

How to print time taken by query?
\timing
-->time take to run particular query
Query
\timtig -->turn off

How to write history of commands into file?
\s <path>

Alternate for \dt+

select pg_relation_size('<table-name>')
    output in bytes

select pg_size_pretty(pg_relation_size('<table-name>'))
    output in MBs or in GB

select pg_size_pretty(pg_database_size('<database-name>'))
    output in MBs or in GB

select pid from pg_stat_activity;

History of PostgreSQL

History of PostgreSQL

The world’s most advanced open source database
Designed for extensibility and customization .
Actively developed for more than 20 years
- University Postgres (1986-1993)
- Postgres95 (1994-1995)
- PostgreSQL (1996-current)

Features of PostgreSQL

Portable:

Written in ANSI C
Supports Windows, Linux, Mac OS/X and major UNIX platforms

Reliable:

  •  ACID Compliant
  •  Supports Transactions
  •  Supports Save points
  •  Uses Write Ahead Logging (WAL)

Secure:

  • Uses Multi-version Concurrency Control
  • Supports Table Partitioning
  • Supports Tablespaces

Recovery and Availability: 

  •  Streaming Replication
  •  Replication Slots
  •  Supports Hot-Backup, pg_basebackup
  •  Point-in-Time Recovery

Advanced:

  • Supports Triggers and Functions
  • Supports Custom Procedural Languages PL/pgSQL, PL/Perl, PL/TCL, PL/PHP, PL/Java - Upgrade using pg_upgrade
  • Unlogged Tables - Materialized Views


Why postgresSQL?

Why postgresSQL?


PostgreSQL is capable of processing big data and which has better capabilities as follows.

LimitValue
Maximum Database SizeUnlimited
Maximum Table Size32 TB
Maximum Row Size1.6 TB
Maximum Field Size1 GB
Maximum Rows per Table Unlimited
Maximum Columns per Table250-1600 (Depending on Column types)
Maximum Indexes per Table?Unlimited

what is postgreSQL

What is postgreSQL?


PostgreSQL is a Relational Database Management System(RDBMS) is a named collection of SQL objects. It is a collection of schemas and the schemas contain the tables, functions, etc.
  • Databases are created with the CREATE DATABASE command and
  • Databases are destroyed with the DROP DATABASE command.
To determine the set of existing databases:
-SQL: SELECT datname FROM pg_database;
Above command will list all the databases
-psql META COMMAND: \l(backslash lowercase L)
Using \l also we can list all the databases

Tuesday 12 July 2016

How to check disk space in linux

Example:
[pgdb@localhost home]$ df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/vda3              106G    12G    89G  12% /
tmpfs                   22G    78k    22G   1% /dev/shm
/dev/vda1              204M    34M   160M  18% /boot
/dev/vda2              201G   136G    55G  72% /data1
/dev/vda6              201G   402M   191G   1% /data2
172.19.1.25:/data/pub
                       316G   216G    84G  73% /nfs

How to check disk space of a folder?
Example:
[pgdb@localhost home]$ df -H /home
Filesystem             Size   Used  Avail Use% Mounted on
/dev/vda3              106G    12G    89G  12% /

Get more about df command using below?
df --help
or
man df

Thursday 7 July 2016

PostgreSQL Tutorial

Java Interview questions and answers part3

What is the result of below code?

Given the proper import statement(s), and
PriorityQueue<String> pq = new PriorityQueue<String>();
pq.add("2");
pq.add("4");
System.out.print(pq.peek() + " ");
pq.offer("1");
pq.add("3");
pq.remove("1");
System.out.print(pq.poll() + " ");
if(pq.remove("2")) System.out.print(pq.poll() + " ");
System.out.println(pq.poll() + " " + pq.peek());

A. 2 2 3 3
B. 2 2 3 4
C. 4 3 3 4
D. 2 2 3 3 3
E. 4 3 3 3 3
F. 2 2 3 3 4
G. Compilation fails
H. An exception is thrown at runtime
Answer:
B is correct. For the sake of the exam, add() and offer() both add to (in this case),
naturally sorted queues. The calls to poll() both return and then remove the first item
from the queue, so the if test fails.

Question:
Given:
import java.util.*;
class Turtle {
int size;
public Turtle(int s) { size = s; }
public boolean equals(Object o) { return (this.size == ((Turtle)o).size); }
// insert code here
}
public class TurtleTest {
public static void main(String[] args) {
LinkedHashSet<Turtle> t = new LinkedHashSet<Turtle>();
t.add(new Turtle(1)); t.add(new Turtle(2)); t.add(new Turtle(1));
System.out.println(t.size());
}
}
And these two fragments:
I. public int hashCode() { return size/5; }
II. // no hashCode method declared
If fragment I or II is inserted, independently, at line 8, which are true? (Choose all that apply.)
A. If fragment I is inserted, the output is 2
B. If fragment I is inserted, the output is 3
C. If fragment II is inserted, the output is 2
D. If fragment II is inserted, the output is 3
E. If fragment I is inserted, compilation fails
F. If fragment II is inserted, compilation fails
Answer:
A and D are correct. While fragment II wouldn't fulfill the hashCode() contract (as you
can see by the results), it is legal Java. For the purpose of the exam, if you don’t override
hashCode(), every object will have a unique hashcode.

Which are true? (Choose all that apply.) Given the proper import statement(s), and:

TreeSet<String> s = new TreeSet<String>();
TreeSet<String> subs = new TreeSet<String>();
s.add("a"); s.add("b"); s.add("c"); s.add("d"); s.add("e");
subs = (TreeSet)s.subSet("b", true, "d", true);
s.add("g");
s.pollFirst();
s.pollFirst();
s.add("c2");
System.out.println(s.size() +" "+ subs.size());

A. The size of s is 4
B. The size of s is 5
C. The size of s is 7
D. The size of subs is 1
E. The size of subs is 2
F. The size of subs is 3
G. The size of subs is 4
H. An exception is thrown at runtime
Answer:
B and F are correct. After "g" is added, TreeSet s contains six elements and TreeSet subs
contains three (b, c, d), because "g" is out of the range of subs. The first pollFirst()
finds and removes only the "a". The second pollFirst() finds and removes the "b" from
both TreeSets (remember they are backed). The final add() is in range of both TreeSets.
The final contents are [c,c2,d,e,g] and [c,c2,d].

What is the output of below program? 
class Magellan {
public static void main(String[] args) {
TreeMap<String, String> myMap = new TreeMap<String, String>();
myMap.put("a", "apple"); myMap.put("d", "date");
myMap.put("f", "fig"); myMap.put("p", "pear");
System.out.println("1st after mango: " +myMap.higherKey("f"));
System.out.println("1st after mango: " + myMap.ceilingKey("g"));
System.out.println("1st after mango: " +myMap.floorKey("g"));
SortedMap<String, String> sub = new TreeMap<String, String>();
sub = myMap.tailMap("f");
System.out.println("1st after mango: " +sub.firstKey());
System.out.println("1st after mango: " + sub.lastKey());
System.out.println(sub.size());
}
}
Answer:
1st after mango: p
1st after mango: p
1st after mango: f
1st after mango: f
1st after mango: p
2

Which, inserted independently at line 9, will compile? (Choose all that apply.) 
import java.util.*;
class Business { }
class Hotel extends Business { }
class Inn extends Hotel { }
public class Travel {
ArrayList<Hotel> go() {
// insert code here
}
}

A. return new ArrayList<Inn>();
B. return new ArrayList<Hotel>();
C. return new ArrayList<Object>();
D. return new ArrayList<Business>();
Answer:
B is correct.
A is incorrect because polymorphic assignments don't apply to generic type parameters. C
and D are incorrect because they don't follow basic polymorphism rules.

What is the result? 
import java.util.*;
class Dog { int size; Dog(int s) { size = s; } }
public class FirstGrade {
public static void main(String[] args) {
TreeSet<Integer> i = new TreeSet<Integer>();
TreeSet<Dog> d = new TreeSet<Dog>();

d.add(new Dog(1)); d.add(new Dog(2)); d.add(new Dog(1));
i.add(1); i.add(2); i.add(1);
System.out.println(d.size() + " " + i.size());
}
}
A. 1 2
B. 2 2
C. 2 3
D. 3 2
E. 3 3
F. Compilation fails
G. An exception is thrown at runtime
Answer:
G is correct. Class Dog needs to implement Comparable in order for a TreeSet (which
keeps its elements sorted) to be able to contain Dog objects.

What is output of below program? 
class GeoCache {
public static void main(String[] args) {
String[] s = {"map", "pen", "marble", "key"};
Othello o = new Othello();
Arrays.sort(s,o);//perameters here String[] and class obj which should impliment Comparator
for(String s2: s) System.out.print(s2 + " ");
System.out.println(Arrays.binarySearch(s, "map"));//this method meake use of compareTo of Othello
}
static class Othello implements Comparator<String> {
public int compare(String a, String b) { return a.compareTo(b); }
}
}
Answer:
output:key map marble pen 1

What is output of below program? 
class GeoCache {
public static void main(String[] args) {
String[] s = {"map", "pen", "marble", "key"};
Othello o = new Othello();
Arrays.sort(s,o);//perameters here String[] and class obj which should impliment Comparator
for(String s2: s) System.out.print(s2 + " ");
System.out.println(Arrays.binarySearch(s, "map"));//this method meake use of compareTo of Othello
}
static class Othello implements Comparator<String> {
public int compare(String a, String b) { return b.compareTo(a); }//check difference here with previous question.
}
}
Answer:
output:pen marble map key -1

Which are true? (Choose all that apply.)
import java.util.*
public class GeoCache {
public static void main(String[] args) {
String[] s = {"map", "pen", "marble", "key"};
Othello o = new Othello();
Arrays.sort(s,o);
for(String s2: s) System.out.print(s2 + " ");
System.out.println(Arrays.binarySearch(s, "map"));
}
static class Othello implements Comparator<String> {
public int compare(String a, String b) { return b.compareTo(a); }
}
}

A. Compilation fails
B. The output will contain a 1
C. The output will contain a 2
D. The output will contain a –1
E. An exception is thrown at runtime
F. The output will contain "key map marble pen"
G. The output will contain "pen marble map key"
Answer:
D and G are correct. First, the compareTo() method will reverse the normal sort.
Second, the sort() is valid. Third, the binarySearch() gives –1 because it needs to be
invoked using the same Comparator (o), as was used to sort the array. Note that when the
binarySearch() returns an "undefined result" it doesn’t officially have to be a -1, but it
usually is, so if you selected only G, you get full credit!

Assume you have a class that holds two private variables: a and b. Which of the following pairs can prevent concurrent access problems in that class? (Choose all that apply.) 
A. public int read(){return a+b;}
public void set(int a, int b){this.a=a;this.b=b;}
B. public synchronized int read(){return a+b;}
public synchronized void set(int a, int b){this.a=a;this.b=b;}
C. public int read(){synchronized(a){return a+b;}}
public void set(int a, int b){synchronized(a){this.a=a;this.b=b;}}
D. public int read(){synchronized(a){return a+b;}}
public void set(int a, int b){synchronized(b){this.a=a;this.b=b;}}
E. public synchronized(this) int read(){return a+b;}
public synchronized(this) void set(int a, int b){this.a=a;this.b=b;}
F. public int read(){synchronized(this){return a+b;}}
public void set(int a, int b){synchronized(this){this.a=a;this.b=b;}}
Answer:
B and F are correct. By marking the methods as synchronized, the threads will get the
lock of the this object before proceeding. Only one thread will be setting or reading at any
given moment, thereby assuring that read() always returns the addition of a valid pair.
Wrong:
A is incorrect because it is not synchronized; therefore, there is no guarantee that the values
added by the read() method belong to the same pair. C and D are incorrect; only objects
can be used to synchronize on. E fails —it is not possible to select other objects (even this)
to synchronize on when declaring a method as synchronized.

Choose Appropriate option? Given:
public class TwoThreads {
static Thread laurel, hardy;
public static void main(String[] args) {
laurel = new Thread() {
public void run() {
System.out.println("A");
try {
hardy.sleep(1000);
} catch (Exception e) {
System.out.println("B");
}
System.out.println("C");
}
};
hardy = new Thread() {
public void run() {
System.out.println("D");
try {
laurel.wait();
} catch (Exception e) {
System.out.println("E");
}
System.out.println("F");
}
};
laurel.start();
hardy.start();
}
}
Which letters will eventually appear somewhere in the output? (Choose all that apply.)
A. A
B. B
C. C
D. D
E. E
F. F
G. The answer cannot be reliably determined
H. The code does not compile
Answer:
A, C, D, E, and F are correct. This may look like laurel and hardy are battling to cause
the other to sleep() or wait()—but that's not the case. Since sleep() is a static
method, it affects the current thread, which is laurel (even though the method is invoked
using a reference to hardy). That's misleading but perfectly legal, and the Thread laurel
is able to sleep with no exception, printing A and C (after at least a 1-second delay). Meanwhile
hardy tries to call laurel.wait()—but hardy has not synchronized on laurel,
so calling laurel.wait() immediately causes an IllegalMonitorStateException, and
so hardy prints D, E, and F. Although the order of the output is somewhat indeterminate
(we have no way of knowing whether A is printed before D, for example) it is guaranteed
that A, C, D, E, and F will all be printed in some order, eventually—so G is incorrect.

Will below code compiles?  
class A1 {
int a=10;
static int  add(int i, int j){
return i+j;
}
  }
class B1 extends A1{
int a=10;
public static void main(String argv[]){
short s = 9;
System.out.println(add(s,6)+" "+super.a);//error becoz of super key word.
}
   }
Answer:
error bcoz of "super" key word.We can not use "this" and "super" key word in side static methods and blocks(Cannot use this(or)super in a static context)