SQL

SQL Server Connections :

               Here get the data from Sql server databases, so we are trying to connect sql server database using java code,then it il connected and getting the value from databases. If you need database connection using this below scripts.        

Java code for connecting MS SQL Server by using SQL Server Authentication 

First of all, You will need to add a jar file to your project library as SQL Server 2000 Driver for JDBC Service. My target is SQL Server 2000, it will require the jar file called "sqljdbc4.jar". This is not supported on Microsoft website now, you can download it here. For other versions of SQL Server, here is the link of SQL Server 2000 Driver for JDBC Service.

The following is the code for connection MS SQL Server and select some records from a testing table.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class Main {
 
 public static void main(String[] args) throws SQLException 

 {
  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
  Connection conn = DriverManager.getConnection(

 "jdbc:sqlserver://severname;user="";password="";database=""");
  System.out.println("test");
  Statement sta = conn.createStatement();
  String Sql = "select * from testing_table";
  ResultSet rs = sta.executeQuery(Sql);
  while (rs.next()) {
   System.out.println(rs.getString("txt_title"));
  }
 }
}

              

          

Complex Queries in SQL ( Oracle )

These questions are the most frequently asked in interviews.

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal); 
5. Select FIRST n records from a table.
   select * from emp where rownum <= &n;
6. Select LAST n records from a table
   select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no employees in the department.
   select * from dept where deptno not in (select deptno from emp);
   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
   altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
8. How to get 3 Max salaries ?
   select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. How to get 3 Min salaries ?
   select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
10. How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Select DISTINCT RECORDS from emp table.
    select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
12. How to delete duplicate rows in a table?
    delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
13. Count of number of employees in  department  wise.
    select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
14.  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
    select ename,sal/12 as monthlysal from emp;
    
15. Select all record from emp table where deptno =10 or 40.
    select * from emp where deptno=30 or deptno=10;
    
16. Select all record from emp table where deptno=30 and sal>1500.
    select * from emp where deptno=30 and sal>1500;
    
17. Select  all record  from emp where job not in SALESMAN  or CLERK.
    select * from emp where job not in ('SALESMAN','CLERK');
    
18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
    select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
    
19. Select all records where ename starts with ‘S’ and its lenth is 6 char.
    select * from emp where ename like'S____';
    
20. Select all records where ename may be any no of  character but it should end with ‘R’.
    select * from emp where ename like'%R';
    
21. Count  MGR and their salary in emp table.
    select count(MGR),count(sal) from emp;
    
22. In emp table add comm+sal as total sal  .
    select ename,(sal+nvl(comm,0)) as totalsal from emp;
    
23. Select  any salary <3000 from emp table. 
    select * from emp  where sal> any(select sal from emp where sal<3000);
    
24. Select  all salary <3000 from emp table. 
    select * from emp  where sal> all(select sal from emp where sal<3000);
    
25. Select all the employee  group by deptno and sal in descending order.
    select ename,deptno,sal from emp order by deptno,sal desc;
    
26. How can I create an empty table emp1 with same structure as emp?
    Create table emp1 as select * from emp where 1=2;
    
27. How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
    
28. Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
    
29. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
    
30. How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
    
31.  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
    
32. Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2

Connect to a Database Using Java Code 

 

In a later section, you'll create a Java form that loads information from a database. The form will have Next and Previous to scroll through the data. Individual records will then be displayed in text fields. We'll also add button to Update a record, Delete a record, and create a new record in the database.To get started, and for simplicity's sake, we'll use a terminal/console window to output the results from a database.So start a new project for this by clicking File > New Project from the NetBeans menu. Create a Java Application. Call the package database_console, and the Main class DBConnect:

When you click Finish, your code should look like this:

Connecting to the Database

To connect to a database you need a Connection object. The Connection object uses a DriverManager. The DriverManager passes in your database username, your password, and the location of the database.
Add these three import statements to the top of your code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

To set up a connection to a database, the code is this:

Connection con = DriverManager.getConnection( host, username, password );

So the DriverManager has a method called getConnection. This needs a host name (which is the location of your database), a username, and a password. If a connection is successful, a Connection object is created, which we've called con.

You can get the host address by looking at the Services tab on the left of NetBeans:

The address of the highlighted database above is:

jdbc:derby://localhost:1527/Employees

The first part, jdbc:derby://localhost, is the database type and server that you're using. The 1527 is the port number. The database is Employees. This can all go in a String variable:

String host = "jdbc:derby://localhost:1527/Employees";

Two more strings can be added for the username and password:

String uName = "Your_Username_Here";
String uPass= " Your_Password_Here ";

Add these three string before the connection object and your code would look like this:

As you can see in the image above, there is a wavy underline for the Connection code. The reason for this is because we haven't trapped a specific error that will be thrown up when connecting to a database - the SQLException error.

It's the DriverManager that attempts to connect to the database. If it fails (incorrect host address, for example) then it will hand you back a SQLException error. You need to write code to deal with this potential error. In the code below, we're trapping the error in catch part of the try … catch statement:

try {

}
catch ( SQLException err ) {
System.out.println( err.getMessage( ) );
}

In between the round brackets of catch, we've set up a SQLException object called err. We can then use the getMessage method of this err object.

Add the above try …catch block to your own code, and move your four connection lines of code to the try part. Your code will then look like this:

Try running your code and see what happens.
You may get this error message in the console window:

"java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect."

If you do, it means you haven't connected to your database server. In which case, right click on Java DB in the Services window. From the menu that appears, click Start Server:

You need to make sure that any firewall you may have is not blocking the connection to the server. A good firewall will immediately display a message alerting you that something is trying to get through, and asking if you want to allow or deny it. When you allow the connection, your NetBeans output window should print the following message:

"Apache Derby Network Server - 10.4.1.3 - (648739) started and ready to accept connections on port 1527 at DATE_AND_TIME_HERE"

Once your server is started, run the programme again. There's a very good chance you'll get another error message:

"No suitable driver found for jdbc:derby://localhost:1527/Employees"

The reason for this error is that the DriverManager needs a Driver in order to connect to the database. Examples of drivers are Client Drivers and Embedded Drivers. You can import one of these so that the DriverManager can do its job.

Click on the Projects tab to the left of the Services window in NetBeans. (If you can't see a Projects tab, click Window > Projects from the menu bar at the top of NetBeans.)

Locate your project and expand the entry. Right-click Libraries. From the menu that appears, select Add Jar/Folder:

When you click on Add Jar/Folder a dialogue box appears. What you're doing here is adding a Java Archive file to your project. But the JAR file you're adding is for the derby Client Drivers. So you need to locate this folder. On a computer running Windows this will be in the following location:

C:\Program Files\Sun\JavaDB\lib

The file you're looking for is called derbyclient.jar. If you can't find it, or are using an operating system other than Windows, then do a search for this file. Note the location of the file.
In the dialogue box, select the derbyclient.jar file:

Click Open and the file will be added to your project library:

Now that you have a Client driver added to your project, run your programme again. You should now be error free. (The Output window will just say Run, and Build Successful.)
In the next lesson, we'll continue with this Java database tutorial.