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

Part3: SQL Data Types Reference

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

SQL basics- complete reference guide - part2 - DDL

Part2: DDL -Data Definition Language Reference

Command	Description	SYNTAX	Example
ALTER INDEX RENAME	Renames an index.	ALTER INDEX indexName RENAME TO newIndexName
ALTER SCHEMA RENAME	Renames a schema.	ALTER SCHEMA schema RENAME TO newSchemaName
ALTER SEQUENCE	Changes the next value and the increment of a sequence.	ALTER SEQUENCE sequenceName [ RESTART WITH long ] [ INCREMENT BY long ]
ALTER TABLE ADD	Adds a new column to a table.	ALTER TABLE tableName ADD name dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] [ BEFORE columnName ]
ALTER TABLE ADD CONSTRAINT	Adds a constraint to a table.	ALTER TABLE tableName ADD constraint [ CHECK | NOCHECK ]
ALTER TABLE ALTER	Changes 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 COLUMN	Removes a column from a table.	ALTER TABLE tableName DROP COLUMN columnName
ALTER TABLE DROP CONSTRAINT	Removes a constraint or a primary key from a table.	ALTER TABLE tableName DROP { CONSTRAINT [ IF EXISTS ] constraintName | PRIMARY KEY }
ALTER TABLE SET	Disables or enables referential integrity checking for a table.	ALTER TABLE tableName SET REFERENTIAL_INTEGRITY     { FALSE | TRUE [ CHECK | NOCHECK ] }
ALTER TABLE RENAME	Renames a table.	ALTER TABLE tableName RENAME TO newName
ALTER USER ADMIN	Switches the admin flag of a user on or off.	ALTER USER userName ADMIN { TRUE | FALSE }
ALTER USER RENAME	Renames a user.	ALTER USER userName RENAME TO newUserName
ALTER USER SET PASSWORD	Changes the password of a user.	ALTER USER userName SET { PASSWORD string | SALT bytes HASH bytes }
ALTER VIEW	Recompiles a view after the underlying tables have been changed or created.	ALTER VIEW viewName RECOMPILE
ANALYZE	Updates the selectivity statistics of all tables.	ANALYZE [ SAMPLE_SIZE rowCountInt ]
COMMENT	Sets 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 AGGREGATE	Creates a new user-defined aggregate function.	CREATE AGGREGATE [ IF NOT EXISTS ] newAggregateName FOR className
CREATE ALIAS	Creates a new function alias.	CREATE ALIAS [ IF NOT EXISTS ] newFunctionAliasName [ DETERMINISTIC ] { FOR classAndMethodName | AS sourceCodeString }
CREATE CONSTANT	Creates a new constant.	CREATE CONSTANT [ IF NOT EXISTS ] newConstantName VALUE expression
CREATE DOMAIN	Creates a new data type (domain).	CREATE DOMAIN [ IF NOT EXISTS ] newDomainName AS dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ SELECTIVITY selectivity ] [ CHECK condition ]
CREATE INDEX	Creates a new index.	CREATE { [ UNIQUE ] [ HASH ] INDEX [ IF NOT EXISTS ] newIndexName     | PRIMARY KEY [ HASH ] } ON tableName ( indexColumn [,...] )
CREATE LINKED TABLE	Creates 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 ROLE	Creates a new role.	CREATE ROLE [ IF NOT EXISTS ] newRoleName
CREATE SCHEMA	Creates a new schema.	CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ]
CREATE SEQUENCE	Creates a new sequence.	CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ] [ INCREMENT BY long ] [ CACHE long ]
CREATE TABLE	Creates 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 TRIGGER	Creates 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 USER	Creates a new user.	CREATE USER [ IF NOT EXISTS ] newUserName { PASSWORD string | SALT bytes HASH bytes } [ ADMIN ]
CREATE VIEW	Creates a new view.	CREATE [ OR REPLACE ] [ FORCE ] VIEW [ IF NOT EXISTS ] newViewName [ ( columnName [,...] ) ] AS select
DROP AGGREGATE	Drops an existing user-defined aggregate function.	DROP AGGREGATE [ IF EXISTS ] aggregateName
DROP ALIAS	Drops an existing function alias.	DROP ALIAS [ IF EXISTS ] existingFunctionAliasName
DROP ALL OBJECTS	Drops 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 CONSTANT	Drops a constant.	DROP CONSTANT [ IF EXISTS ] constantName
DROP DOMAIN	Drops a data type (domain).	DROP DOMAIN [ IF EXISTS ] domainName
DROP INDEX	Drops an index.	DROP INDEX [ IF EXISTS ] indexName
DROP ROLE	Drops a role.	DROP ROLE [ IF EXISTS ] roleName
DROP SCHEMA	Drops a schema.	DROP SCHEMA [ IF EXISTS ] schemaName
DROP SEQUENCE	Drops a sequence.	DROP SEQUENCE [ IF EXISTS ] sequenceName
DROP TABLE	Drops an existing table, or a list of tables.	DROP TABLE [ IF EXISTS ] tableName [,...] [ CASCADE | RESTRICT ]
DROP TRIGGER	Drops an existing trigger.	DROP TRIGGER [ IF EXISTS ] triggerName
DROP USER	Drops a user.	DROP USER [ IF EXISTS ] userName
DROP VIEW	Drops an existing view.	DROP VIEW [ IF EXISTS ] viewName [ RESTRICT | CASCADE ]
TRUNCATE TABLE	Removes all rows from a table.	TRUNCATE TABLE tableName

SQL basics- complete reference guide - part1 - DML

Part1: DML-Data Manipulation Language

Command	Description	SYNTAX	Example
SELECT	Selects 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 ]
INSERT	Inserts a new row / new rows into a table.	INSERT INTO tableName [ ( columnName [,...] ) ] { VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | [ DIRECT ] [ SORTED ] select }
UPDATE	Updates data in a table.	UPDATE tableName [ [ AS ] newTableAlias ] SET { columnName= { DEFAULT | expression } } [,...] [ WHERE expression ]
DELETE	Deletes rows form a table.	DELETE FROM tableName [ WHERE expression ]
BACKUP	Backs up the database files to a .	BACKUP TO fileNameString
CALL	Calculates a simple expression.	CALL expression
EXPLAIN	Shows the execution plan for a statement.	EXPLAIN { [ PLAN FOR ] | ANALYZE } { select | insert | update | delete | merge }
MERGE	Updates existing rows, and insert rows that don't exist.	MERGE INTO tableName [ ( columnName [,...] ) ] [ KEY ( columnName [,...] ) ] { VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select }
RUNSCRIPT	Runs a SQL script from a file.	RUNSCRIPT FROM fileNameString [ scriptCompression ] [ CIPHER cipher PASSWORD string ] [ CHARSET charsetString ]
SCRIPT	Creates a SQL script from the database.	SCRIPT [ SIMPLE ] [ NODATA ] [ NOPASSWORDS ] [ NOSETTINGS ] [ DROP ] [ BLOCKSIZE blockSizeInt ] [ TO fileNameString [ scriptCompression ]     [ CIPHER cipher PASSWORD string ] ] [ CHARSET charsetString ]
SHOW	Lists 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.

maximize a JFrame window in java

How to Maximize a JFrame :

 
    JFrame myFrame = new JFrame();
    myFrame.setVisible(true);
    myFrame.setExtendedState(myFrame.getExtendedState() | JFrame.MAXIMIZED_BOTH);
    

The usage of Java packages.

Explain the usage of Java packages.

A. This is a way to organize files when a project consists of multiple modules. It also helps resolve naming conflicts when different packages have classes with the same names. 

Packages access level also allows you to protect data from being used by the non-authorized classes.