DBMS using connections(Client-Data sever, two tier) Oracle/MySQL (ODBC/JDBC), SQL prompt to create data base tables insert, update data values, delete table, use table, select queries with/without where clause.

Problem Statement: 

DBMS using connections(Client-Data sever, two tier) Oracle/MySQL (ODBC/JDBC), SQL prompt to create data base tables insert, update data values, delete table, use table, select queries with/without where clause.

Relation Schema:

Employees (Employee_id , first_name , last_name , email, ph_no , hire_date, job_id, salary, department_id )

Works(Employee_id , manager_id )

Departments (Department_id , dept_name , location_id)

Jobs (Job_id, job_title , min_salary , max_salary)

Locations (Location_id , street, city, state , country)

Job_history(Employee_id , hire_date, leaving_date, salary, job_id, department_id)

Execute following queries.

1. Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority.
2. List name of all departments in location 20,30 and 50
3. Display the full name of all employees whose first_name or last_name contains ‘a’
4. update the salary of employees for specific department_id
5. delete employee details from job_history whose salary is less than 50,000
6. Show all data for clerks hired after the year 1999.

Required Softwares:
1. mysql-connector-java-5.x.x
2. Eclipse

PROGRAM:

import java.io.*;

import java.sql.*;

public class Obiwan {

public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException

{

BufferedReader buf= new BufferedReader(new InputStreamReader(System.in));

String url="jdbc:mysql://localhost/*Database Name*;

String driver="com.mysql.jdbc.Driver";

String user="*Username*", password="*Password*";

Connection con=null;

Statement st=null;

Class.forName(driver);

con=DriverManager.getConnection(url,user,password);

st=con.createStatement();

int ch=0;

do

{

System.out.println("\n1.Create tables");

System.out.println("2.Insert values");

System.out.println("3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority");

System.out.println("4.List name of all departments in location 20 or 30");

System.out.println("5.Display the full name of all employees whose first_name or last_name contains ‘a’");

System.out.println("6.Update the salary of employees for specific department_id");

System.out.println("7.Delete employee details from job_history whose salary is less than 50,000");

System.out.println("8.Show all data for clerks hired after the year 1999");

System.out.println("9.Delete tables");

System.out.println("10.Exit");

System.out.println("Enter Choice");

ch=Integer.parseInt(buf.readLine());

String sql,sql1,sql2,sql3,sql4,sql5,sql6,sql7;

switch(ch)

{

case 1: sql="create table Jobs(job_id int,job_title VARCHAR(20), min_sal int, max_sal int,primary key(job_id))";

st.executeUpdate(sql);

System.out.println("Table Jobs successfully created.");

sql="create table Locations(loc_id int,city VARCHAR(20), country VARCHAR(20), primary key(loc_id))";

st.executeUpdate(sql);

System.out.println("Table Locations successfully created.");

sql="create table Departments(dep_id int,dep_nm VARCHAR(20), loc_id int,primary key(dep_id),foreign key(loc_id) references Locations(loc_id))";

st.executeUpdate(sql);

System.out.println("Table Departments successfully created.");

sql="create table Employees(emp_id int,first_nm VARCHAR(20), last_nm VARCHAR(20), email VARCHAR(20), ph_no int, hire_date int, job_id int, sal int, dep_id int, primary key(emp_id),foreign key(job_id) references Jobs(job_id),foreign key(dep_id) references Departments(dep_id))";

st.executeUpdate(sql);

System.out.println("Table Employees successfully created.");

sql="create table Works(emp_id int, mang_id int,primary key(emp_id,mang_id),foreign key(emp_id) references Employees(emp_id))";

st.executeUpdate(sql);

System.out.println("Table Works successfully created.");

sql="create table Job_history(emp_id int, leaving_date int, sal int, job_id int, dep_id int,primary key(emp_id,leaving_date),foreign key(emp_id) references Employees(emp_id), foreign key(job_id) references Jobs(job_id), foreign key(dep_id) references Departments(dep_id))";

st.executeUpdate(sql);

System.out.println("Table Job_history successfully created.");

break;

case 2: sql1="insert into Jobs values(1,'Clerk',5000,10000)";

st.executeUpdate(sql1);

sql1="insert into Jobs values(2,'Manager',20000,50000)";

st.executeUpdate(sql1);

sql1="insert into Jobs values(3,'President',70000,90000)";

st.executeUpdate(sql1);

sql1="select * from Jobs";

ResultSet rs1=st.executeQuery(sql1);

while(rs1.next())

{

System.out.println("\tJob ID: "+rs1.getInt("job_id"));

System.out.println("\tJob Title: "+rs1.getString("job_title"));

System.out.println("\tMin Salary: "+rs1.getInt("min_sal"));

System.out.println("\tMax Salary: "+rs1.getInt("max_sal")+"\n");

}

rs1.close();

System.out.println("Values inserted in Table Jobs");

sql1="insert into Locations values(10,'Pune','India')";

st.executeUpdate(sql1);

sql1="insert into Locations values(20,'Bombay','India')";

st.executeUpdate(sql1);

sql1="insert into Locations values(30,'New Delhi','India')";

st.executeUpdate(sql1);

sql1="select * from Locations";

ResultSet rs2=st.executeQuery(sql1);

while(rs2.next())

{

System.out.println("\tLocation ID: "+rs2.getInt("loc_id"));

System.out.println("\tCity: "+rs2.getString("city"));

System.out.println("\tCountry: "+rs2.getString("country")+"\n");

}

rs2.close();

System.out.println("Values inserted in Table Locations");

sql1="insert into Departments values(1,'Accounting',30)";

st.executeUpdate(sql1);

sql1="insert into Departments values(2,'Sales',20)";

st.executeUpdate(sql1);

sql1="insert into Departments values(3,'Marketing',10)";

st.executeUpdate(sql1);

sql1="select * from Departments";

ResultSet rs3=st.executeQuery(sql1);

while(rs3.next())

{

System.out.println("\tDepartment ID: "+rs3.getInt("dep_id"));

System.out.println("\tDepartment Name: "+rs3.getString("dep_nm"));

System.out.println("\tLocation ID: "+rs3.getInt("loc_id")+"\n");

}

rs3.close();

System.out.println("Values inserted in Table Departments");

sql1="insert into Employees values(1,'Clark','Kent','ck@',123,2006,2,30000,3)";

st.executeUpdate(sql1);

sql1="insert into Employees values(2,'James','Hunt','jh@',234,1998,1,7000,1)";

st.executeUpdate(sql1);

sql1="insert into Employees values(3,'Denzil','Mirks','dm@',345,2007,3,80000,2)";

st.executeUpdate(sql1);

sql1="insert into Employees values(4,'Nikki','Lauda','nl@',456,2000,1,8000,3)";

st.executeUpdate(sql1);

sql1="select * from Employees";

ResultSet rs4=st.executeQuery(sql1);

while(rs4.next())

{

System.out.println("\tEmployee ID: "+rs4.getInt("emp_id"));

System.out.println("\tFirst Name: "+rs4.getString("first_nm"));

System.out.println("\tLast Name: "+rs4.getString("last_nm"));

System.out.println("\tEmail: "+rs4.getString("email"));

System.out.println("\tPhone No: "+rs4.getInt("ph_no"));

System.out.println("\tHire Date: "+rs4.getInt("hire_date"));

System.out.println("\tJob ID: "+rs4.getInt("job_id"));

System.out.println("\tSalary: "+rs4.getInt("sal"));

System.out.println("\tDepartment ID: "+rs4.getInt("dep_id")+"\n");

}

rs4.close();

System.out.println("Values inserted in Table Employees");

sql1="insert into Works values(1,3)";

st.executeUpdate(sql1);

sql1="insert into Works values(2,3)";

st.executeUpdate(sql1);

sql1="insert into Works values(3,3)";

st.executeUpdate(sql1);

sql1="insert into Works values(4,3)";

st.executeUpdate(sql1);

sql1="select * from Works";

ResultSet rs5=st.executeQuery(sql1);

while(rs5.next())

{

System.out.println("\tEmployee ID: "+rs5.getInt("emp_id"));

System.out.println("\tManager ID: "+rs5.getInt("mang_id")+"\n");

}

rs5.close();

System.out.println("Values inserted in Table Works");

sql1="insert into Job_history values(1,1997,25000,2,3)";

st.executeUpdate(sql1);

sql1="insert into Job_history values(2,1997,6500,1,2)";

st.executeUpdate(sql1);

sql1="insert into Job_history values(3,1997,75000,2,1)";

st.executeUpdate(sql1);

sql1="insert into Job_history values(4,1997,7000,3,1)";

st.executeUpdate(sql1);

sql1="select * from Job_history";

ResultSet rs6=st.executeQuery(sql1);

while(rs6.next())

{

System.out.println("\tEmployee ID: "+rs6.getInt("emp_id"));

System.out.println("\tLeaving Date: "+rs6.getInt("leaving_date"));

System.out.println("\tPrevious Salary: "+rs6.getInt("sal"));

System.out.println("\tJob ID: "+rs6.getInt("job_id"));

System.out.println("\tDepartment ID: "+rs6.getInt("dep_id")+"\n");

}

rs6.close();

System.out.println("Values inserted in Table Job_history");

break;

case 3: sql2="select emp_id,first_nm,last_nm,sal from Employees natural join Jobs where hire_date=2006 order by Jobs.job_id desc";

ResultSet rs7=st.executeQuery(sql2);

while(rs7.next())

{

System.out.println("\tEmployee ID: "+rs7.getInt("emp_id"));

System.out.println("\tName :"+rs7.getString("first_nm")+" "+rs7.getString("last_nm"));

System.out.println("\tSalary: "+rs7.getInt("sal")+"\n");

}

rs7.close();

System.out.println("Query Successfully executed");

break;

case 4: sql3="select dep_id,dep_nm from Departments where loc_id=20 or loc_id=30";

ResultSet rs8=st.executeQuery(sql3);

while(rs8.next())

{

System.out.println("\tDepartment ID: "+rs8.getInt("dep_id"));

System.out.println("\tDepartment Name :"+rs8.getString("dep_nm")+"\n");

}

rs8.close();

System.out.println("Query Successfully executed");

break;

case 5: sql4="select first_nm,last_nm from Employees where first_nm like '%a%' or last_nm like '%a%'";

ResultSet rs9=st.executeQuery(sql4);

while(rs9.next())

{

System.out.println("\tName :"+rs9.getString("first_nm")+" "+rs9.getString("last_nm")+"\n");

}

rs9.close();

System.out.println("Query Successfully executed");

break;

case 6: System.out.println("Enter new salary: ");

String val1=buf.readLine();

System.out.println("Enter Department ID: ");

String val2=buf.readLine();

System.out.println("Enter Employee ID: ");

String val3=buf.readLine();

sql5="Update Employees set sal="+val1+" where dep_id="+val2+" and emp_id="+val3;

st.executeUpdate(sql5);

sql5="select * from Employees";

ResultSet rs10=st.executeQuery(sql5);

while(rs10.next())

{

System.out.println("\tEmployee ID: "+rs10.getInt("emp_id"));

System.out.println("\tFirst Name: "+rs10.getString("first_nm"));

System.out.println("\tLast Name: "+rs10.getString("last_nm"));

System.out.println("\tEmail: "+rs10.getString("email"));

System.out.println("\tPhone No: "+rs10.getInt("ph_no"));

System.out.println("\tHire Date: "+rs10.getInt("hire_date"));

System.out.println("\tJob ID: "+rs10.getInt("job_id"));

System.out.println("\tSalary: "+rs10.getInt("sal"));

System.out.println("\tDepartment ID: "+rs10.getInt("dep_id")+"\n");

}

rs10.close();

System.out.println("Query Successfully executed");

break;

case 7: sql5="Delete from Job_history where sal<=50000";

st.executeUpdate(sql5);

sql5="select * from Job_history";

ResultSet rs11=st.executeQuery(sql5);

while(rs11.next())

{

System.out.println("\tEmployee ID: "+rs11.getInt("emp_id"));

System.out.println("\tLeaving Date: "+rs11.getInt("leaving_date"));

System.out.println("\tPrevious Salary: "+rs11.getInt("sal"));

System.out.println("\tJob ID: "+rs11.getInt("job_id"));

System.out.println("\tDepartment ID: "+rs11.getInt("dep_id")+"\n");

}

rs11.close();

System.out.println("Query Successfully executed");

break;

case 8: sql6="select * from Employees where hire_date>1999 and job_id=1";

ResultSet rs12=st.executeQuery(sql6);

while(rs12.next())

{

System.out.println("\tEmployee ID: "+rs12.getInt("emp_id"));

System.out.println("\tFirst Name: "+rs12.getString("first_nm"));

System.out.println("\tLast Name: "+rs12.getString("last_nm"));

System.out.println("\tEmail: "+rs12.getString("email"));

System.out.println("\tPhone No: "+rs12.getInt("ph_no"));

System.out.println("\tHire Date: "+rs12.getInt("hire_date"));

System.out.println("\tJob ID: "+rs12.getInt("job_id"));

System.out.println("\tSalary: "+rs12.getInt("sal"));

System.out.println("\tDepartment ID: "+rs12.getInt("dep_id")+"\n");

}

rs12.close();

break;

case 9: sql7="Drop table Job_history";

st.executeUpdate(sql7);

System.out.println("Table Job_history deleted.");

sql7="Drop table Works";

st.executeUpdate(sql7);

System.out.println("Table Works deleted.");

sql7="Drop table Employees";

st.executeUpdate(sql7);

System.out.println("Table Employees deleted.");

sql7="Drop table Departments";

st.executeUpdate(sql7);

System.out.println("Table Departments deleted.");

sql7="Drop table Locations";

st.executeUpdate(sql7);

System.out.println("Table Locations deleted.");

sql7="Drop table Jobs";

st.executeUpdate(sql7);

System.out.println("Table Jobs deleted.");

break;

case 10: System.out.println("Exiting...");

break;

}

}while(ch!=10);

}

}

OUTPUT:

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

1

Table Jobs successfully created.

Table Locations successfully created.

Table Departments successfully created.

Table Employees successfully created.

Table Works successfully created.

Table Job_history successfully created.

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

2

Job ID: 1

Job Title: Clerk

Min Salary: 5000

Max Salary: 10000

Job ID: 2

Job Title: Manager

Min Salary: 20000

Max Salary: 50000

Job ID: 3

Job Title: President

Min Salary: 70000

Max Salary: 90000

Values inserted in Table Jobs

Location ID: 10

City: Pune

Country: India

Location ID: 20

City: Bombay

Country: India

Location ID: 30

City: New Delhi

Country: India

Values inserted in Table Locations

Department ID: 1

Department Name: Accounting

Location ID: 30

Department ID: 2

Department Name: Sales

Location ID: 20

Department ID: 3

Department Name: Marketing

Location ID: 10

Values inserted in Table Departments

Employee ID: 1

First Name: Clark

Last Name: Kent

Email: ck@

Phone No: 123

Hire Date: 2006

Job ID: 2

Salary: 30000

Department ID: 3

Employee ID: 2

First Name: James

Last Name: Hunt

Email: jh@

Phone No: 234

Hire Date: 1998

Job ID: 1

Salary: 7000

Department ID: 1

Employee ID: 3

First Name: Denzil

Last Name: Mirks

Email: dm@

Phone No: 345

Hire Date: 2007

Job ID: 3

Salary: 80000

Department ID: 2

Employee ID: 4

First Name: Nikki

Last Name: Lauda

Email: nl@

Phone No: 456

Hire Date: 2000

Job ID: 1

Salary: 8000

Department ID: 3

Values inserted in Table Employees

Employee ID: 1

Manager ID: 3

Employee ID: 2

Manager ID: 3

Employee ID: 3

Manager ID: 3

Employee ID: 4

Manager ID: 3

Values inserted in Table Works

Employee ID: 1

Leaving Date: 1997

Previous Salary: 25000

Job ID: 2

Department ID: 3

Employee ID: 2

Leaving Date: 1997

Previous Salary: 6500

Job ID: 1

Department ID: 2

Employee ID: 3

Leaving Date: 1997

Previous Salary: 75000

Job ID: 2

Department ID: 1

Employee ID: 4

Leaving Date: 1997

Previous Salary: 7000

Job ID: 3

Department ID: 1

Values inserted in Table Job_history

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

3

Employee ID: 1

Name :Clark Kent

Salary: 30000

Query Successfully executed

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

4

Department ID: 1

Department Name :Accounting

Department ID: 2

Department Name :Sales

Query Successfully executed

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

5

Name :Clark Kent

Name :James Hunt

Name :Nikki Lauda

Query Successfully executed

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

8

Employee ID: 4

First Name: Nikki

Last Name: Lauda

Email: nl@

Phone No: 456

Hire Date: 2000

Job ID: 1

Salary: 8000

Department ID: 3

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

9

Table Job_history deleted.

Table Works deleted.

Table Employees deleted.

Table Departments deleted.

Table Locations deleted.

Table Jobs deleted.

1.Create tables

2.Insert values

3.Display employee_id, full name and salary of all employees who have joined in year 2006 according to their seniority

4.List name of all departments in location 20 or 30

5.Display the full name of all employees whose first_name or last_name contains ‘a’

6.Update the salary of employees for specific department_id

7.Delete employee details from job_history whose salary is less than 50,000

8.Show all data for clerks hired after the year 1999

9.Delete tables

10.Exit

Enter Choice

10

Exiting...