How to install postgres in mac

Install postgres in mac by using brew

• brew install postgres

Want to view info about postgres

•    brew info postgresql

After installing postgres , better restart your host which will automatically start postgres or you can use following commands to start postgres..

•     pg_ctl -D /usr/local/var/postgres start
•     pg_ctl -D /usr/local/var/postgres stop -m fast

Modify config file to set password , if you don’t want to set password then leave it as trust . 

• file : /usr/local/var/postgres/pg_hba.conf

• host    all             all             127.0.0.1/32            trust

Just logging with psql and check it 

•   psql -U postgres

If its thrown error as “Postgres user doesn’t exist” then clean up postgres data by using following commands 

•   Navigate to file directory :   cd /usr/local/var/
•   clean up everything : rm -rf *
•   initdb -U postgres  -D postgres

Read More

Store procedure to simulate dummy data

BEGIN
  FOR i IN 100000..300000 LOOP
  insert into table_name (column1,2,....)
  values(2013,234,i,5951,'01-JAN-2013',20.2,0,20.2,'31-DEC-2013') ;
  COMMIT ;
  END LOOP;
  END;

Read More

Postgres dump and restore commands

Postgres pg dump :

 pg_dump -Upostgres --no-owner --format=tar db_name > db_name_local.tar.dump

Postgres pg restore :

pg_restore -Upostgres --format=tar -d db_name db_name_with_pl.dump.tar

Read More

Connecting Excel as Database in java

User can connect excel as database by using excel drivers and perform insert , read operations.
import java.io.*;
import java.sql.*;

public class ExcelReadTest{

public static void main(String[] args){

Connection connection = null;

try{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection( "jdbc:odbc:exceltest" );

Statement st = con.createStatement();

ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

while (rs.next()) {

for (int i = 1; i <= numberOfColumns; i++) {

if (i > 1) System.out.print(", ");

String columnValue = rs.getString(i);

System.out.print(columnValue);

}

System.out.println("");

}
st.close();

con.close();

} catch(Exception ex) {

System.err.print("Exception: ");

System.err.println(ex.getMessage());

}

}

}
To insert data :
"insert into [Sheet1$] (ColumnName1,ColumnName2) values ('"+sValue1+"','"sValue2"')"

Read More

Triggers

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Examples:

CREATE TRIGGER schema.trigger_name
    BEFORE
    DELETE OR INSERT OR UPDATE
    ON schema.table_name
       pl/sql_block

Read More

SQL: Set operators

Types of set Operators in SQL :
    1. Union
    2. Union ALL
    3. Intersect.

Read More

Find out second larget by using the Max..

Using this Query , U can find the 2nd larget value...

select Max (t2.column1) from table t2
where t2. column1 < (select max(column1) from table ) ;

Read More

Find out nth Highest salary from Emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); Output : n=2 , it will display 2nd highest salary from table. Otherwise We can use rank statement to find second largest:
select column1
from
(
select column1 rank() over (order by column1 desc) rank from
(select distinct(column1) from table1)
)
where rank 2;

Simple Solution:
-----------------
 Select max(Column1) from table1 where Column1 < (select max(Column1) from table1)

Read More

Change the column name of table in oracle?

alter table tablename rename column column1 to column2;

Read More

Stored Functions in SQL Server?

A function always return a value,and it is called inside the sql statements like normal functions.

Examples:

CREATE FUNCTION CALC_AVERAGE (n1 INT, n2 INT)
  RETURNS INT
   DETERMINISTIC
    BEGIN
     DECLARE avg INT;
     SET avg = (n1+n2*2+n4*4)/8;
     RETURN avg;
    END|

Read More

What is Stored Procedure in SQL Server?

Stored Procedures:

Stored procedures are extremely similar to the constructs seen in other programming languages.
They accept data in the form of input parameters that are specified at execution time.

Benifits of Stored Procedures:
1. PreComplied execution
2. Efficient reuse
3. Enhanced security controls

Example:
 CREATE PROCEDURE sp_GetQuantity
 @Name varchar(10)
 AS
 SELECT Product, Quantity
 FROM Product_Table
 WHERE Product = @Name

For executing the above Procedure:
  EXECUTE  sp_GetQuantity 'A7'

Read More

What is Index in the databases?

Index is a set pointers reference to the specific row in the table.It will make Sql search as much more fast.
 
  Example:
  CREATE INDEX IndexName
  ON TableName (ColumnName1,ColumnName2).

For More information:
http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

Read More

Difference between Subquery and Joins?

Sub query is also query which returns one or more values,we
can retrieve the data based on the condition...

Join is used to join the two tables and used to retrieve the
data from one or more tables.

Read More

What is Primary and Foreign key in databases?

Primary key:

   Each table can have one primary key, which uniquely identifies each row in a table and ensures that no duplicate rows exist.
 

   Foreign Key:

   On whichever column you put FOREIGN KEY constraint then the values in that column must refer to existing values in the other table. A foreign key column can refer to primary key or unique key column of other tables.

Read More

Explain about the following things in Databases?

1. What is foreign key and Primary key , how do you differentiate?
2. What is Index in the table?
3. How do we differentiate producer and function in Database?
4. What is trigger in Databases?

Read More

what is view in SQL Server?

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables


syntax:
CREATE VIEW vwCustomer
AS
SELECT CustomerId, Company Name, Phone
FROM Customers


For more information, see the below link..



http://www.sql-server-performance.com/articles/dev/views_in_sql_server_p1.aspx

Read More

How to take backup in oracle?

 For Exporting the Database :
exp file=RPM62B13.dmp userid=devusr/Usrdev1234@RPM62B13.symphonysv.com fully=y
log=AgileExpBackup.log

For Exporting the tables :
exp  file=empdept.expdat  userid=devusr/Usrdev1234@RPM62B13.symphonysv.com tables=(EMP,DEPT) log=empdept.log

Read More

Drop and truncate in Database?

drop table tablename;
  It will  delete per-mentally the table and data.
 truncate table tablename;
  it will delete the data from the table.

Read More

How to copy the table in oracle?

•  Copy the table structure with data

        create table emp_copy as
          select *
            from Employee;

•   Copy the table structure with out data  

            CREATE TABLE Table2 AS 
               SELECT * FROM Table1 WHERE 1=0;
       Note: if condition is not validating then it wont copy the data. 

•  Copying selected columns from another table

                   CREATE TABLE newTable
                        AS (SELECT empno, ename
                                    FROM emp);

Read More

constraints in Oracle

They are five integrity constraints are available in Oracle.

1. Not Null
2. Primary key
3. Foreign key
4. Check
5. unique    

For more information , Refer the following URLs
 http://www.oracle-dba-online.com/sql/integrity_constraints.htm

Read More