Sunday 11 June 2017

DBMS Tutorial-3


To Perform various data manipulation commands, aggregate functions and sorting concept on all created tables in previous tutorial.

Queries

  1. List total deposit from deposit.

    • select sum(amount)from deposit_gtu;
  2. List total loan from karolbagh branch

    • select sum(amount) from deposit_gtu where bname='Andheri';
  3. Give maximum loan from branch vrce.

    • select max(amount) from borrow_gtu where bname='VRCE';
  4. Count total number of customers.

    • select count(cname) from customers;
  5. Count total number of customer’s cities.

    • select count(DISTINCT city) from customers;
  6. Create table supplier from employee with all the columns.

    • create table supplier as select * from employee;
  7. Create table sup1 from employee with first two columns.

    • create table sup1 as (select * from employee WHERE EMP_NO<103 );
  8. Create table sup2 from employee with no data.

    • create table sup2 as select * from employee WHERE 1=2;
  9. Insert the data into sup2 from employee whose second character should be ‘n’ and string should be 5 characters long in employee name field.

    • INSERT INTO sup2 SELECT * FROM employee where emp_name like '_n___';
  10. Delete all the rows from sup1.

    • delete from sup1;
  11. Delete the detail of supplier whose sup_no is 103.

    • delete from supplier where emp_no=103;
Share:

DBMS Tutorial-2

  1. create table Job ( job_id varchar2 (15), job_title varchar2 (30) , min_sal number (7,2) , max_sal number (7,2));

    • insert into Job values('&job_id','&job_title','&min_sal','&max_sal');
  2. create table Employee (emp_no number (3), emp_name varchar2 (30), emp_sal number (8,2), emp_comm number (6,1), dept_no number (3));

    • insert into Employee values ('01','&emp_name','&emp_sal','&emp_comm','&dept_no');
  3. Create table deposit_gtu(a_no varchar2 (5),cname varchar2 (15),bname varchar2 (10),amount number (7,2), a_date date);

    • insert into deposit_gtu values('&a_no','&cname','&bname','&amount','&a_date');
  4. Create table borrow_gtu(loanno varchar2 (5),cname varchar2 (15),bname varchar2 (10),amount number (7,2));

    • insert into borrow_gtu values('&loanno','&cname','&bname','&amount');

Queries

  1. Retrieve all data from employee, jobs and deposit.

    • select * from Employee;
    • select * from Job;
    • select * from deposit_gtu;
  2. Give details of account no. and deposited rupees of customers having account opened between dates 01-01-06 and 25-07-06.

    • select ACTNO,AMOUNT from DEPOSIT where ADATE BETWEEN '01-JAN-06' and '25-JUL-03';
  3. Display all jobs with minimum salary is greater than 4000.

    • select * from Job where min_sal>4000;
  4. Display name and salary of employee whose department no is 20. Give alias name to name of employee.

    • select emp_name "name of employee",emp_sal "salary of employee" from employee where dept_no=20;
  5. Display employee no,name and department details of those employee whose department lies in(10,20)

    • select EMP_NO,EMP_NAME,DEPT_NO from EMPLOYEE where DEPT_NO BETWEEN 10 and 20;
Share:

Saturday 10 June 2017

DBMS Tutorial-1

  1. CREATE TABLE DEPOSIT (ACTNO VARCHAR2(5),CNAME VARCHAR2(18),BNAME VARCHAR2(18),AMOUNT NUMBER(8,2),ADATE DATE);

    • INSERT INTO DEPOSIT VALUES('&ACTNO','&CNAME','&BNAME','&AMOUNT','&ADATE');
  2. CREATE TABLE BRANCH(BNAME VARCHAR2(18),CITY VARCHAR2(18));

    • INSERT INTO BRANCH VALUES('&BNAME','&CITY');
  3. CREATE TABLE CUSTOMERS(CNAME VARCHAR2(19) ,CITY VARCHAR2(18));

    • INSERT INTO CUSTOMERS VALUES('&CNAME','&CITY');
  4. CREATE TABLE BORROW(LOANNO VARCHAR2(5), CNAME VARCHAR2(18), BNAME VARCHAR2(18), AMOUNT NUMBER (8,2));

    • INSERT INTO BORROW VALUES('&LOANNO','&CNAME','&BNAME','&AMOUNT');

Queries

  1. Describe deposit, branch.

    • desc deposit;
    • desc branch;
  2. Describe borrow, customers.

    • desc borrow;
    • desc customers;
  3. List all data from table DEPOSIT.

    • select * from deposit;
  4. List all data from table BORROW.

    • select * from borrow;
  5. List all data from table CUSTOMERS.

    • select * from customers;
  6. List all data from table BRANCH.

    • select * from branch;
  7. Give account no and amount of depositors.

    • select accno,amount from deposit;
  8. Give name of depositors having amount greater than 4000.

    • select cname from deposit where amount>4000;
  9. Give name of customers who opened account after date '1-12-96'

    • select cname from deposit where adate>'1-12-96';
Share:

Friday 9 June 2017

Sunday 4 June 2017

JAVA Tutorial-7

1. Write a Main method that takes the name of a text file as a command line argument and prints every line in lower case.

 
import java.io.*;
class Fileread
{
 public static void main(String s[])throws Exception
 {
  FileInputStream in =new FileInputStream(s[0]);
  int size=in.available();
  int i;
  char c;
  for(i=0;i<size;i++)
  {
   c=(char)in.read();
   System.out.print(Character.toLowerCase(c));
  }
  in.close();
 }
}

Output

2. Write a main() method that counts the number of words in a text file whose name is accepted from standard input. Also print the size of a file.

 
import java.util.*;
import java.io.*;
class Filecount
{
 public static void main(String s[])throws Exception
 {
  FileInputStream in =new FileInputStream(s[0]);
  Scanner sc=new Scanner(in);
  int size=in.available();
  int count=0;
  while(sc.hasNext())
  { 
   sc.next();
   count++;
  }
  System.out.println("Size of File: "+size);
  System.out.println("There are "+count+" Word in file");
  in.close();
 }
}

Output

3. Write a program using BufferedInputStream, FileInputStream, BufferedOutputStream, FileOutputStream to copy Content of one file File1.txt into another file File2.txt.


import java.io.*;
class Filecopy
{
 public static void main(String s[])throws Exception
 {
  FileInputStream in = new FileInputStream("File1.txt");
  BufferedInputStream bin = new BufferedInputStream(in);
  FileOutputStream ou = new FileOutputStream("File2.txt");
  BufferedOutputStream bou = new BufferedOutputStream(ou);
  int size=in.available();
  int i;
  for(i=0;i<size;i++)
  {
   bou.write((char)bin.read());
  }
  System.out.println("Content of one file is copied to another fle");
  bou.close();
  ou.close();
  bin.close();
  in.close();
 }
 
}

Output

Share:

Saturday 3 June 2017

JAVA Tutorial-6

1. Write a program to insert values into an array.
The user will enter a sequence of index and value to be stored at that index on the command line.
Define your exception handlers for the following cases:
Index is out of range.
Value < 0 and value > 5000.
Given index has already value stored.

 
class Exceptiondemo
{ 
 public static void main(String ar[])
 {
  int a[]=new int[Integer.parseInt(ar[0])];
  int i,j=2,k;
  try{
  
   for(i=1,k=0;i<ar.length;i+=2,k++)
   {
    a[Integer.parseInt(ar[i])]=Integer.parseInt(ar[j]);
    if(a[k]<0 || a[k]>5000)
    {
     throw new InvalidValueException();
    }
    j+=2;
   }
   for(i=0;i<a.length;i++)
   {
    System.out.println(a[i]);
   }
   
  }
  catch(ArrayIndexOutOfBoundsException e)
  {
   System.out.println("Array Index Out Of Bound Exception");
  }
  catch(InvalidValueException ex)
  {
   System.out.println(ex.toString());
  }
 }
} 
class InvalidValueException extends Exception
{
   public String toString()
   {
 return ("Invalid Value Exception / 0 value is already there in array");
   }  
}

Output

2. Write a program to get the date in form of DD MM YYYY from command line argument.
Raise and handle following custom exceptions to check the validity of the entered date.
 YearException: to check for valid four digit year.
 MonthException: to check whether the month is between 01 to 12 or not.
 DateException: to check wether date is between 01 to 31 or not.
It also checks the validity if date of month February when there is a leap year.

 
class YearException extends Exception
{
 YearException()
 {
  System.out.println("Year Exception Generated");
 }
 public String toString()
 {
  return "Invalid Year";
 }
}
class MonthException extends Exception
{
 MonthException()
 {
  System.out.println("Month Exception Generated");
 }
 public String toString()
 {
  return "Invalid Month";
 }
}
class DateException extends Exception
{
 DateException()
 {
  System.out.println("Date Exception Generated");
 }
 public String toString()
 {
  return "Invalid Date";
 }
}
class ddmmyyyy
{
   public static void main(String a[])
   {
 int yyyy=Integer.parseInt(a[2]);
 int mm=Integer.parseInt(a[1]);
 int dd=Integer.parseInt(a[0]);
 try
 {
  if(a.length==3)
  {
   //YEAR
   if(a[2].length()!=4 || yyyy<1000 || yyyy>9999)
   {
    throw new YearException();
   }
   else
   {
    System.out.println("Year: "+yyyy);
   }
   //MONTH
   if(a[1].length()!=2 || mm<01 || mm>12)
   {
    throw new MonthException();
   }
   else
   {
    System.out.println("Month: "+mm);
   }
   //DATE
   if(a[0].length()!=2)
   {
    throw new DateException();
   }
   else if(mm==01 || mm==03 || mm==05 || mm==07 || mm==8 || mm==10 || mm==12)
   {
    if(dd<01 || dd>31)
    {
     throw new DateException();
    }
    else
    {
     System.out.println("Date: "+dd);
    }
   }
   else if(mm==04 || mm==06 || mm==9 || mm==11)
   {
    if(dd<01 || dd>30)
    {
     throw new DateException();
    }
    else
    {
     System.out.println("Date: "+dd);
    }
   }
   else if(mm==02)
   {
    if((yyyy%4==0) && (yyyy%100!=0) || (yyyy%400==0))
    {
     if(dd<01 || dd>29)
     {
      throw new DateException();
     }
     else
     {
      System.out.println("Date: "+dd);
     }
    }
    else
    {
     if(dd<01 || dd>28)
     {
      throw new DateException();
     }
     else
     {
      System.out.println("Date: "+dd);
     }
    }
   }
  }
 }
 catch(YearException y)
 {
  System.out.println(y.toString());
 }
 catch(MonthException m)
 {
  System.out.println(m.toString());
 }
 catch(DateException d)
 {
  System.out.println(d.toString());
 }
   }
}

Output

3. Write a method for computing x & y by doing repetitive multiplication. x and y are of type integer and are to be given as command line arguments.
Raise and handle exception(s) for invalid values of x and y. Also define method main.


class InvalidNumberException extends Exception
{
 InvalidNumberException()
 {
  System.out.println("Power Is Negative ");
 }
}
class power
{
   public static void main(String a[])
   {
 int x=Integer.parseInt(a[0]);
 int y=Integer.parseInt(a[1]);
 int temp;
 try
 {
  if(y>=0)
  {
   if(y==0)
   {
    System.out.println(+x+" Power "+y+" is: "+1);
   }
   else
   {
    temp=x; 
    int i;
    for(i=1;i<y;i++)
    {
     temp=temp*x;
    }
    System.out.println(+x+" Power "+y+" is: "+temp);
   }
  }
  else if(y<0)
  {
   throw new InvalidNumberException();
  }
 }
 catch(InvalidNumberException i)
 {
  double temp1;
  int y1=(-y);
  temp1=1/(double)x;
  for(int j=1;j<y1;j++)
  {
   temp1=temp1*(1/(double)x);
  }
  System.out.println(+(double)x+" Power "+y+" is: "+temp1);
 }
   }
}

Output

4. Declare a class called coordinate to represent 3 dimensional Cartesian coordinates( x, y and z). Define following methods:
Constructors.
display method, to print values of members
add_coordinates method, to add three such coordinate objects to produce a resultant coordinate object.
Generate and handle exception if x, y and z coordinates of the result are zero.
main method, to show use of above methods.


class zerovalue extends Exception{}
class coordinate
{
 float x,y,z;
 coordinate()
 {
  x=0;
  y=0;
  z=0;
 }
 coordinate(float x,float y,float z)
 {
  this.x=x;
  this.y=y;
  this.z=z;
 }
 void display()
 {
  System.out.println("X: "+x);
  System.out.println("Y: "+y);
  System.out.println("Z: "+z);
 }
 void add_coordinates(coordinate obj1,coordinate obj2)
 {
  this.x=this.x+obj1.x+obj2.x;
  this.y=this.y+obj1.y+obj2.y;
  this.z=this.z+obj1.z+obj2.z;
 }
 public static void main(String args[])
 {
  coordinate c1=new coordinate(1,1,1);
  coordinate c2=new coordinate(2,2,2);
  coordinate c3=new coordinate(-3,3,3);
  c1.add_coordinates(c2,c3);
  coordinate c4=new coordinate();
  c4=c1;
  c4.display();
  try
  {
   if(c4.x==0||c4.y==0||c4.z==0)
   {
    throw new zerovalue();
   }
  }
  catch(zerovalue r)
  {
   System.out.println("Exception.No Co-Ordinate of Resultant Object Can Be Zero");
  }
 } 
}

Output

Share: