SQL basics- complete reference guide - part4 - Aggregate Functions in SQL

Part4: Aggregate Functions in SQL- complete reference sheet

Command/FunctionDescriptionSYNTAXExample
AVGThe average (mean) value.AVG ( [ DISTINCT ] { int | long | decimal | double } )
BOOL_ANDReturns true if all expressions are true.BOOL_AND(boolean)
BOOL_ORReturns true if any expression is true.BOOL_OR(boolean)
COUNTThe count of all row, or of the non-null values.COUNT( { * | { [ DISTINCT ] expression } } )
GROUP_CONCATConcatenates strings with a separator.GROUP_CONCAT ( [ DISTINCT ] string
[ ORDER BY { expression [ ASC | DESC ] } [,...] ]
[ SEPARATOR expression ] )
MAXThe highest value.MAX(value)
MINThe lowest value.MIN(value)
SUMThe sum of all values.SUM( [ DISTINCT ] { int | long | decimal | double } )
SELECTIVITYEstimates the selectivity (0-100) of a value.SELECTIVITY(value)
STDDEV_POPThe population standard deviation.STDDEV_POP( [ DISTINCT ] double )
STDDEV_SAMPThe sample standard deviation.STDDEV_SAMP( [ DISTINCT ] double )
VAR_POPThe population variance (square of the population standard deviation).VAR_POP( [ DISTINCT ] double )
VAR_SAMPThe sample variance (square of the sample standard deviation).VAR_SAMP( [ DISTINCT ] double )

Call one constructor from another in Java

Is this possible to call one constructor from another in Java ?

Yes, it is possible:

public class Foo
{
    private int x;

    public Foo()
    {
        this(1);//calling constructor -->> public Foo(int x)
    }

    public Foo(int x)
    {
        this.x = x;
    }
}

SQL basics- complete reference guide - part3 - Data Types in SQL

Part3: SQL Data Types Reference

TOPICTEXTSYNTAXExample
INT TypePossible values: -2147483648 to 2147483647.INT | INTEGER | MEDIUMINT | INT4 | SIGNED
BOOLEAN TypePossible values: TRUE and FALSE.BOOLEAN | BIT | BOOL
TINYINT TypePossible values are: -128 to 127.TINYINT
SMALLINT TypePossible values: -32768 to 32767.SMALLINT | INT2 | YEAR
BIGINT TypePossible values: -9223372036854775808 to 9223372036854775807.BIGINT | INT8
IDENTITY TypeAuto-Increment value.IDENTITY
DECIMAL TypeData type with fixed precision and scale.{ DECIMAL | NUMBER | DEC | NUMERIC } ( precisionInt [ , scaleInt ] )
DOUBLE TypeFloating point number.{ DOUBLE [ PRECISION ] | FLOAT | FLOAT4 | FLOAT8 }
REAL TypeSingle precision floating point number.REAL
TIME TypeThe format is hh:mm:ss.TIME
DATE TypeThe format is yyyy-MM-dd.DATE
TIMESTAMP TypeThe format is yyyy-MM-dd hh:mm:ss[.{ TIMESTAMP | DATETIME | SMALLDATETIME }
BINARY TypeRepresents a byte array.{ BINARY | VARBINARY | LONGVARBINARY | RAW | BYTEA } [ ( precisionInt ) ]
OTHER TypeThis type allows storing serialized Java objects.OTHER
VARCHAR TypeUnicode String.{ VARCHAR | LONGVARCHAR | VARCHAR2 | NVARCHAR
    | NVARCHAR2 | VARCHAR_CASESENSITIVE}  [ ( precisionInt ) ]
VARCHAR_IGNORECASE TypeSame as VARCHAR, but not case sensitive when comparing.VARCHAR_IGNORECASE [ ( precisionInt ) ]
CHAR TypeThis type is supported for compatibility with other databases and older
applications.
{ CHAR | CHARACTER | NCHAR } [ ( precisionInt ) ]
BLOB TypeLike BINARY, but intended for very large values such as files or images.{ BLOB | TINYBLOB | MEDIUMBLOB | LONGBLOB | IMAGE | OID } [ ( precisionInt ) ]
CLOB TypeCLOB is like VARCHAR, but intended for very large values.{ CLOB | TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | NTEXT | NCLOB } [ ( precisionInt ) ]
UUID TypeUniversally unique identifier.UUID
ARRAY TypeAn array of values.ARRAY

SQL basics- complete reference guide - part2 - DDL

Part2: DDL -Data Definition Language Reference

CommandDescriptionSYNTAXExample
ALTER INDEX RENAMERenames an index.ALTER INDEX indexName RENAME TO newIndexName
ALTER SCHEMA RENAMERenames a schema.ALTER SCHEMA schema RENAME TO newSchemaName
ALTER SEQUENCEChanges the next value and the increment of a sequence.ALTER SEQUENCE sequenceName [ RESTART WITH long ] [ INCREMENT BY long ]
ALTER TABLE ADDAdds a new column to a table.ALTER TABLE tableName ADD name dataType [ DEFAULT expression ]
[ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] [ BEFORE columnName ]
ALTER TABLE ADD CONSTRAINTAdds a constraint to a table.ALTER TABLE tableName ADD constraint [ CHECK | NOCHECK ]
ALTER TABLE ALTERChanges the data type of a column, rename a column,
change the identity value, or change the selectivity.
ALTER TABLE tableName ALTER COLUMN columnName
{ { dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] }
    | { RENAME TO name }
    | { RESTART WITH long }
    | { SELECTIVITY int }
    | { SET DEFAULT expression }
    | { SET NULL }
    | { SET NOT NULL } }
ALTER TABLE DROP COLUMNRemoves a column from a table.ALTER TABLE tableName DROP COLUMN columnName
ALTER TABLE DROP CONSTRAINTRemoves a constraint or a primary key from a table.ALTER TABLE tableName DROP { CONSTRAINT [ IF EXISTS ] constraintName | PRIMARY KEY }
ALTER TABLE SETDisables or enables referential integrity checking for a table.ALTER TABLE tableName SET REFERENTIAL_INTEGRITY
    { FALSE | TRUE [ CHECK | NOCHECK ] }
ALTER TABLE RENAMERenames a table.ALTER TABLE tableName RENAME TO newName
ALTER USER ADMINSwitches the admin flag of a user on or off.ALTER USER userName ADMIN { TRUE | FALSE }
ALTER USER RENAMERenames a user.ALTER USER userName RENAME TO newUserName
ALTER USER SET PASSWORDChanges the password of a user.ALTER USER userName SET { PASSWORD string | SALT bytes HASH bytes }
ALTER VIEWRecompiles a view after the underlying tables have been changed or created.ALTER VIEW viewName RECOMPILE
ANALYZEUpdates the selectivity statistics of all tables.ANALYZE [ SAMPLE_SIZE rowCountInt ]
COMMENTSets the comment of a database object.COMMENT ON
{ { COLUMN [ schemaName. ] tableName.columnName }
    | { { TABLE | VIEW | CONSTANT | CONSTRAINT | ALIAS | INDEX | ROLE
    | SCHEMA | SEQUENCE | TRIGGER | USER | DOMAIN } [ schemaName. ] objectName } }
IS expression
CREATE AGGREGATECreates a new user-defined aggregate function.CREATE AGGREGATE [ IF NOT EXISTS ] newAggregateName FOR className
CREATE ALIASCreates a new function alias.CREATE ALIAS [ IF NOT EXISTS ] newFunctionAliasName [ DETERMINISTIC ]
{ FOR classAndMethodName | AS sourceCodeString }
CREATE CONSTANTCreates a new constant.CREATE CONSTANT [ IF NOT EXISTS ] newConstantName VALUE expression
CREATE DOMAINCreates a new data type (domain).CREATE DOMAIN [ IF NOT EXISTS ] newDomainName AS dataType
[ DEFAULT expression ] [ [ NOT ] NULL ] [ SELECTIVITY selectivity ]
[ CHECK condition ]
CREATE INDEXCreates a new index.CREATE { [ UNIQUE ] [ HASH ] INDEX [ IF NOT EXISTS ] newIndexName
    | PRIMARY KEY [ HASH ] }
ON tableName ( indexColumn [,...] )
CREATE LINKED TABLECreates a table link to an external table.CREATE [ [ GLOBAL | LOCAL ] TEMPORARY ] LINKED TABLE [ IF NOT EXISTS ]
name ( driverString, urlString, userString, passwordString,
[ originalSchemaString, ] originalTableString ) [ EMIT UPDATES | READONLY ]
CREATE ROLECreates a new role.CREATE ROLE [ IF NOT EXISTS ] newRoleName
CREATE SCHEMACreates a new schema.CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ]
CREATE SEQUENCECreates a new sequence.CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ]
[ INCREMENT BY long ] [ CACHE long ]
CREATE TABLECreates a new table.CREATE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ]
TABLE [ IF NOT EXISTS ] name
{ { ( { columnDefinition | constraint } [,...] ) [ AS select ] }
    | { AS select } }
[ ENGINE tableEngineName ] [ TRANSACTIONAL ] [ NOT PERSISTENT ]
CREATE TRIGGERCreates a new trigger.CREATE TRIGGER [ IF NOT EXISTS ] newTriggerName { BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE | SELECT | ROLLBACK } [,...] ON tableName [ FOR EACH ROW ]
[ QUEUE int ] [ NOWAIT ] CALL triggeredClassName
CREATE USERCreates a new user.CREATE USER [ IF NOT EXISTS ] newUserName
{ PASSWORD string | SALT bytes HASH bytes } [ ADMIN ]
CREATE VIEWCreates a new view.CREATE [ OR REPLACE ] [ FORCE ] VIEW [ IF NOT EXISTS ] newViewName
[ ( columnName [,...] ) ] AS select
DROP AGGREGATEDrops an existing user-defined aggregate function.DROP AGGREGATE [ IF EXISTS ] aggregateName
DROP ALIASDrops an existing function alias.DROP ALIAS [ IF EXISTS ] existingFunctionAliasName
DROP ALL OBJECTSDrops all existing views, tables, sequences, schemas, function aliases, roles,
user-defined aggregate functions, domains, and users (except the current user).
DROP ALL OBJECTS [ DELETE FILES ]
DROP CONSTANTDrops a constant.DROP CONSTANT [ IF EXISTS ] constantName
DROP DOMAINDrops a data type (domain).DROP DOMAIN [ IF EXISTS ] domainName
DROP INDEXDrops an index.DROP INDEX [ IF EXISTS ] indexName
DROP ROLEDrops a role.DROP ROLE [ IF EXISTS ] roleName
DROP SCHEMADrops a schema.DROP SCHEMA [ IF EXISTS ] schemaName
DROP SEQUENCEDrops a sequence.DROP SEQUENCE [ IF EXISTS ] sequenceName
DROP TABLEDrops an existing table, or a list of tables.DROP TABLE [ IF EXISTS ] tableName [,...] [ CASCADE | RESTRICT ]
DROP TRIGGERDrops an existing trigger.DROP TRIGGER [ IF EXISTS ] triggerName
DROP USERDrops a user.DROP USER [ IF EXISTS ] userName
DROP VIEWDrops an existing view.DROP VIEW [ IF EXISTS ] viewName [ RESTRICT | CASCADE ]
TRUNCATE TABLERemoves all rows from a table.TRUNCATE TABLE tableName

SQL basics- complete reference guide - part1 - DML

Part1: DML-Data Manipulation Language

CommandDescriptionSYNTAXExample
SELECTSelects data from a table or multiple tables.SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...]
FROM tableExpression [,...] [ WHERE expression ]
[ GROUP BY expression [,...] ] [ HAVING expression ]
[ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,...] ]
[ LIMIT expression [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ]
[ FOR UPDATE ]
INSERTInserts a new row / new rows into a table.INSERT INTO tableName [ ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | [ DIRECT ] [ SORTED ] select }
UPDATEUpdates data in a table.UPDATE tableName [ [ AS ] newTableAlias ]
SET { columnName= { DEFAULT | expression } } [,...]
[ WHERE expression ]
DELETEDeletes rows form a table.DELETE FROM tableName [ WHERE expression ]
BACKUPBacks up the database files to a .BACKUP TO fileNameString
CALLCalculates a simple expression.CALL expression
EXPLAINShows the execution plan for a statement.EXPLAIN { [ PLAN FOR ] | ANALYZE } { select | insert | update | delete | merge }
MERGEUpdates existing rows, and insert rows that don't exist.MERGE INTO tableName [ ( columnName [,...] ) ]
[ KEY ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select }
RUNSCRIPTRuns a SQL script from a file.RUNSCRIPT FROM fileNameString [ scriptCompression ]
[ CIPHER cipher PASSWORD string ] [ CHARSET charsetString ]
SCRIPTCreates a SQL script from the database.SCRIPT [ SIMPLE ] [ NODATA ] [ NOPASSWORDS ] [ NOSETTINGS ]
[ DROP ] [ BLOCKSIZE blockSizeInt ]
[ TO fileNameString [ scriptCompression ]
    [ CIPHER cipher PASSWORD string ] ]
[ CHARSET charsetString ]
SHOWLists the schemas, tables, or the columns of a table.SHOW { SCHEMAS | TABLES [ FROM schemaName ] |
    COLUMNS FROM tableName [ FROM schemaName ] }

Scenarios in which Serialization cannot happen

What are the special cases in which serialization cannot happen?
-> There are following scenarios in which serialization cannot happen:
a. Variables are transient.
b. Variables are static.
c. Base class variables are serialized if class itself is serializable.

java prevent sql injection - using PreparedStatement


PreparedStatement is the best way to prevent sql injection in java, rather than escaping strings. 
Here's a simple example taking the user's input as the parameters:
public insertUser(String name, String email) {
   Connection conn = null;
   PreparedStatement stmt = null;
   try {
      conn = setupTheDatabaseConnectionSomehow();
      stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
      stmt.setString(1, name);
      stmt.setString(2, email);
      stmt.executeUpdate();
   }
   finally {
      try {
         if (stmt != null) { stmt.close(); }
      }
      catch (Exception e) {
         // log this error
      }
      try {
         if (conn != null) { conn.close(); }
      }
      catch (Exception e) {
         // log this error
      }
   }
}
No matter what characters are in name and email, those characters will be placed directly in the database. They won't affect the INSERT statement in any way.
There are different set methods for different data types -- which one you use depends on what your database fields are. For example, if you have an INTEGER column in the database, you should use asetInt method. The PreparedStatement documentation lists all the different methods available for setting and getting data.

redirect message to IO stream

How could Java classes direct program messages to the system console, but error messages, say to a file?
The class System has a variable out that represents the standard output, and the variable err that represents the standard error device. By default, they both point at the system console. This how the standard output could be re-directed:

Stream st = new Stream(new FileOutputStream("output.txt")); 
System.setErr(st);
System.setOut(st);

java socket connect read string

Java - create socket connection to HOST:PORT and read message from there
import java.io.*;
import java.net.*;
import java.util.*;

public class Time_Server_Socket_Test_Java {
    public static void main(String[] args) {
        try {
            Socket s = new Socket(HOST, PORT);//use your own HOST:PORT
            try {
                InputStream inStream = s.getInputStream();
                Scanner in = new Scanner(inStream);

                while (in.hasNextLine()) {
                    String line = in.nextLine();
                    System.out.println(line);
                }
            } finally {
                s.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}



How to serialize variables selectively

In a Java class, one has 10 variables. One wants to serialize only 3 variables,how can this be achieved?
->Make variables as 'transient' which are not to be serialized.