Vivekanand Mathapati: May 2015

Monday 25 May 2015

A PHP to read a text file and display it as HTML

This PHP program is to read a text file and display in HTML

This is my text file i have saved it in the same folder where my php program is. you can save it at any place but you have to give the complete path for the file

SOURCE CODE :

<!DOCTYPE html>
<html>
<body>

<?php
//vivek.txt is my file name replace it with your file name
$myfile = fopen("vivek.txt", "r") or die("Unable to open file!");
// Output one line until end-of-file
while(!feof($myfile)) {
echo fgets($myfile) . "<br>";
}
fclose($myfile);
?>

</body>
</html>

OUTPUT :

Saturday 23 May 2015

A simple java Servlet program

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;

public class Demo extends HttpServlet
{
public void doGet(HttpServletResponse res, HttpServletRequest req)throws IOException, ServletException
{
res.setContentType("text/html");
PrintWriter pw = res.getWriter();
pw.println(" Welcome ");
}
}

java applet program to create database connection using JDBC

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
/*
<applet code="JtextFieldDemo3" width=300 height=200>
</applet>
*/
public class JtextFieldDemo3 extends JApplet
{
JLabel jlb1,jlb2,jlb3,jlb4,jlb5;
JTextField jtf1,jtf2,jtf3,jtf4,jtf5;
JButton jb;
String text1,text2,text3,text4,text5;
public void init()
{
try
{
SwingUtilities.invokeAndWait(new Runnable(){
public void run()
{

makeGUI();
}
});
}catch(Exception e)
{ System.out.println(e);
}
}
private void makeGUI()
{
setLayout(new FlowLayout());
jlb1=new JLabel("regester_no");
jtf1=new JTextField(20);
jlb2=new JLabel("student name");
jtf2=new JTextField(20);
jlb3=new JLabel("father name");
jtf3=new JTextField(20);
jlb4=new JLabel("mother name");
jtf4=new JTextField(20);
jlb5=new JLabel("address");
jtf5=new JTextField(20);

jb=new JButton("click");

add(jlb1);
add(jtf1);
add(jlb2);
add(jtf2);
add(jlb3);
add(jtf3);
add(jlb4);
add(jtf4);
add(jlb5);
add(jtf5);
add(jb);

jb.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent ae)
{
text1=jtf1.getText();
text2=jtf2.getText();
text3=jtf3.getText();
text4=jtf4.getText();
text5=jtf5.getText();
showStatus(text1);
try
{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:student_DSN");
Statement st=con.createStatement();
st.executeUpdate("insert into student_tb values('" + text1 + "','" + text2 + "','" + text3 + "','" + text4 + "','" + text5 + "')");

con.commit();
showStatus("data inserted");

}catch(Exception e)
{
System.out.println(e);
}

}
});
}
}

JAVA database connection to oracle ( Oracle JDBC connection )

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

public class OracleJDBC {

public static void main(String[] argv) {

System.out.println("-------- Oracle JDBC Connection Testing ------");

try {

Class.forName("oracle.jdbc.driver.OracleDriver" );

} catch (ClassNotFoundException e) {

System.out.println("Where is your Oracle JDBC Driver?" +e);
e.printStackTrace();
return;

}

System.out.println("Oracle JDBC Driver Registered!");

Connection connection = null;

try {

connection = DriverManager.getConnection("jdbc:oracle:thin:@//127.0.0.1:8080/XE", "system", "gcc");

} catch (SQLException e) {

System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;

}

if (connection != null) {
System.out.println("You made it, take control your database now!");
} else {
System.out.println("Failed to make connection!");
}
}

}

PHP database connection ,insertion & extraction of data into and from database

There are 5 simple steps we should follow while dealing with PHP database connection
1)create connection to database & test connection
2)perform database query & test for query errors
3)use returned data(if any)
4)release returned data
5)close database connection

example :
<?php
//1.create connnection to database
$dbhost="localhost";
$dbuser="root";
$dbpass="";
$dbname="mydb";
$connection=mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);
//test connnection
if(mysqli_connect_errno())
{
die("database connection failed: " . mysqli_connect_error().
" (".mysqli_connect_errno(). ")"
);
}

?>
<?php
//2.perform database query
$query ="SELECT * ";
$query .="FROM subjects ";
$result=mysqli_query($connection,$query);
//test for query errors
if(!$result)
{
die("database query failed");
}
?>
<html>
<head>
<title>database connection</title>
</head>
<body>
<table border="1">
<tr><th>sub1</th><th>sub2</th></tr>
<?php
//3.use returned data(if any)

while($row=mysqli_fetch_assoc($result))
{
?>
<tr><td><?php echo $row["sub1"] ; ?></td><td><?php echo $row["sub2"] ;?></td></tr>

<?php
}
?>
</table>
<?php
//4. release returned data
mysqli_free_result($result);
?>
</body>
</html>

<?php
//5.close database connection
mysqli_close($connection);
?>

A PHP program to create database connection & check the connection

<?php
$servername = "localhost";
$username = 'root'; // your database system username
$password = 'pas123'; //your database system password

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Thursday 21 May 2015

A SQL database design to simulate the followings #DBMS1

#PROBLEM STATEMENT

Notown Records has decided to store information about musicians who perform on its albums. Each musician that records at Notown has an SSN, a name, an address, and a phone number. No musician has more than one phone. Each instrument used in songs recorded at Notown has a unique identification number, a name (e.g., guitar, synthesizer, flute) and a musical key (e.g., C, B-flat, E-flat). Each album recorded on the Notown label has a unique identification number, a title, a copyright date and a format (e.g., CD or MC). Each song recorded at Notown has a title and an author. Each musician may play several instruments, and a given instrument may be played by several musicians. Each album has a number of songs on it, but no song may appear on more than one album. Each song is performed by one or more musicians, and a musician may perform a number of songs. Each album has exactly one musician who acts as its producer. A musician may produce several albums. Queries

a) List musician name, title of the song which he has played, the album in which song has occulted.

b) List the details of songs which are performed by more than 3 musicians.

c) List the different instruments played by the musicians and the average number of musicians who play the instrument.

d) Retrieve album title produced by the producer who plays guitar as well as flute and has produced no of songs greater than the average songs produced by all producers.

e) List the details of musicians who can play all the instruments present.

...........................CREATE TABLES.............................

create table projects

(

pno number,

sponsor varchar(15),

st_date number,

end_date number,

budget number,

primary key(pno)

);

create table professor

(

prof_id number primary key,

name varchar(15),

age number(5),

rank varchar(10),

speciality varchar(15),

pno number references projects

);

create table students

(

usn varchar(5),

name varchar(15),

program varchar(15),

primary key(usn)

);

create table stud_workson_proj

(

prof_id number references professor,

pno number references projects,

usn varchar(20) references students

);

create table prof_workson_proj

(

prof_id number references professor,

pno number references projects

);

...............................INSERT INTO TABLE.........................

insert into projects values('101','UGC','2006','2028','70000');

insert into projects values('102','UG','1992','2026','60000');

insert into projects values('103','UG','2003','2024','50000');

insert into projects values('104','COLL','2008','2022','000');

insert into projects values('105','UFC','2010','2020','30000');

insert into projects values('106','ULC','2001','2018','20000');

insert into projects values('107','UML','2000','2016','65000');

insert into projects values('108','FLV','1999','2014','75000');

insert into projects values('109','DOC','2006','2012','78000');

insert into projects values('110','UGC','2004','2010','85000');

insert into professor values('201','Abhishek','20','1200','database mgt','101');

insert into professor values('202','Hrishikesh','21','1201','database','102');

insert into professor values('203','Tejraj','22','1202','D.S','101');

insert into professor values('204','Kokitkar','23','1203','Unix','103');

insert into professor values('205','Kotrappa','24','1204','mysql','104');

insert into professor values('206','Govind','25','1205','Clang','105');

insert into professor values('207','Swaroopa','26','1206','cpp','106');

insert into professor values('208','Venogopal','27','1207','java','102');

insert into professor values('209','Mithari','28','1208','O.S','105');

insert into professor values('210','Manoj','29','1209','system s/w','103');

insert into professor values('211','Poonam','30','1300','visual basic','101');

insert into professor values('212','Amol','31','1301','fco','106');

insert into professor values('213','Raghvendra','32','1302','DMS','108');

insert into professor values('214','Narendra','33','1303','WEB','108');

insert into professor values('215','Sonia','34','1304','computer n/w','105');

insert into students values('s01','Abhijeet','MCA');

insert into students values('s02','Abid','BCA');

insert into students values('s03','Akshata','BE');

insert into students values('s04','Ankita','MCA');

insert into students values('s05','Apurva','MCA');

insert into students values('s06','Anoop','MSC');

insert into students values('s07','Aparna','MS');

insert into students values('s08','Adavesh','MCA');

insert into students values('s10','Rajnikanth','BBA');

insert into students values('s11','Amit','MBA');

insert into students values('s12','Soumya','MCA');

insert into students values('s13','Amita','MCA');

insert into students values('s14','Apeksha','MCA');

insert into students values('s15','Akshay','BE');

insert into students values('s16','Chetana','BE');

insert into students values('s17','Maya','MCA');

insert into students values('s18','dhanraj','ARCH');

insert into students values('s19','Anant','MCA');

insert into students values('s20','kiran','BE');

insert into stud_workson_proj values('201','101','s01');

insert into stud_workson_proj values('201','102','s02');

insert into stud_workson_proj values('202','103','s03');

insert into stud_workson_proj values('203','101','s01');

insert into stud_workson_proj values('204','104','s01');

insert into stud_workson_proj values('205','103','s03');

insert into stud_workson_proj values('205','105','s04');

insert into stud_workson_proj values('206','104','s05');

insert into stud_workson_proj values('207','101','s06');

insert into stud_workson_proj values('209','102','s04');

insert into stud_workson_proj values('201','105','s03');

insert into stud_workson_proj values('201','106','s05');

insert into stud_workson_proj values('202','107','s07');

insert into stud_workson_proj values('203','103','s05');

insert into stud_workson_proj values('204','103','s05');

insert into stud_workson_proj values('206','105','s04');

insert into stud_workson_proj values('205','105','s06');

insert into stud_workson_proj values('207','101','s08');

insert into stud_workson_proj values('208','107','s14');

insert into stud_workson_proj values('208','103','s11');

insert into stud_workson_proj values('201','107','s11');

insert into stud_workson_proj values('201','103','s11');

insert into stud_workson_proj values('201','104','s11');

insert into stud_workson_proj values('201','108','s11');

insert into stud_workson_proj values('201','110','s11');

insert into prof_workson_proj values('201','101');

insert into prof_workson_proj values('202','101');

insert into prof_workson_proj values('203','101');

insert into prof_workson_proj values('204','102');

insert into prof_workson_proj values('205','103');

insert into prof_workson_proj values('206','103');

insert into prof_workson_proj values('207','102');

insert into prof_workson_proj values('208','108');

insert into prof_workson_proj values('209','108');

insert into prof_workson_proj values('210','102');

insert into prof_workson_proj values('211','104');

insert into prof_workson_proj values('212','101');

insert into prof_workson_proj values('215','106');

insert into prof_workson_proj values('214','106');

insert into prof_workson_proj values('215','108');

insert into prof_workson_proj values('204','108');

insert into prof_workson_proj values('205','104');

insert into prof_workson_proj values('206','108');

insert into prof_workson_proj values('203','109');

insert into prof_workson_proj values('206','110');

insert into prof_workson_proj values('207','101');

insert into prof_workson_proj values('201','109');

insert into prof_workson_proj values('201','102');

insert into prof_workson_proj values('201','103');

insert into prof_workson_proj values('201','104');

insert into prof_workson_proj values('201','105');

insert into prof_workson_proj values('201','106');

insert into prof_workson_proj values('201','107');

insert into prof_workson_proj values('201','108');

insert into prof_workson_proj values('201','110');

insert into prof_workson_proj values('202','102');

insert into prof_workson_proj values('202','103');

insert into prof_workson_proj values('202','104');

insert into prof_workson_proj values('202','105');

insert into prof_workson_proj values('202','106');

insert into prof_workson_proj values('202','107');

insert into prof_workson_proj values('202','108');

insert into prof_workson_proj values('202','109');

insert into prof_workson_proj values('202','110');

QUERIES:

select p.name

from professor p, projects pr

where p.pno=pr.pno AND budget<100000;

NAME

--------------------

Abhishek

Hrishikesh

Tejraj

Kokitkar

Kotrappa

Govind

Swaroopa

Venogopal

Mithare

Manoj

Poonam

NAME

--------------------

Amol

Raghvendra

Narendra

Sonia

15 rows selected.

select s.name,p.name,a.sponsor

from students s, professor p, projects a, stud_workson_proj w

where s.usn=w.usn AND p.prof_id=w.prof_id AND a.pno=w.pno;

NAME NAME SPONSOR

-------------------- -------------------- --------------------

Akhilesh Abhishek UGC

Abid Abhishek UG

Akshata Hrishikesh UG

Akhilesh Tejraj UGC

Akhilesh Kokitkar COLL

Akshata Kotrappa UG

Ankita Kotrappa UFC

Apurva Govind COLL

Anoop Swaroopa UGC

Ankita Mithare UG

Akshata Abhishek UFC

Apurva Abhishek ULC

Aparna Hrishikesh UML

Apurva Tejraj UG

Apurva Kokitkar UG

Ankita Govind UFC

Anoop Kotrappa UFC

Adavesh Swaroopa UGC

Amit Venogopal UG

Apeksha Venogopal UML

20 rows selected.

select p.name, sum(budget)

from professor p, projects a

where p.pno=a.pno AND a.st_date>2000 AND a.end_date=2018

group by p.name;

NAME SUM(BUDGET)

-------------------- -----------

Amol 20000

Swaroopa 20000

select p.name

from professor p, projects a

where p.pno=a.pno AND

BUDGET>(select avg(budget)

from projects);

NAME

--------------------

Abhishek

Hrishikesh

Tejraj

Venogopal

Poonam

Raghvendra

Narendra

7 rows selected.select *

select p.name

from professor p

where p.prof_id in(select prof_id

from prof_workson_proj

group by prof_id

having count(*)=(select count(*)

from projects));

NAME

--------------------

Hrishikesh

A SQL database to design movie database and simulate the followings #DBMS5

#PROBLEM STATEMENT

Data requirements of movie industry are captured. Each movie is identified by title and year of release. Each movie has length in minutes and classified under one genres (like action, horror etc.). Each movie has a plot outline. Production companies are identified by name and each has an address. A production company produces one or more movies. Actors are identified by id. Other details like name and date of birth of actors are also stored. Each actor acts in one or more movies. Each actor has a role in movie. Directors are identified by id. Other details like name and date of birth of directors are also stored. Each director directs one or more movies. Each movie has one or more actors and one or more directors and is produced by a production company. Queries:

a) List the details of horror movies released in 2012 and directed by more than 2 directors.

b) List the details of actors who acted in movies having same titles but released before 2000 and after 2010.

c) List the details of production companies producing maximum movies.

d) List the details of movies where director and actor have same date of birth.

e) Retrieve the names of directors directed all the movies produced by any one production company.

………………………………………..CREATE TABLES………………………….

create table production

(

pname varchar(20) primary key,

address varchar(20)

);

create table actor

(

aid varchar(5) primary key,

aname varchar(20),

a_dob date

);

create table director

(

did varchar(5) primary key,

dname varchar(20),

d_dob date

);

create table movie

(

mtitle varchar(20),

year number(4),

primary key(mtitle,year),

length number(6),

genre varchar(20),

plot varchar(10),

pname varchar(20),

aid varchar(5),

did varchar(5),

foreign key(pname) references production(pname),

foreign key(aid) references actor(aid),

foreign key(did) references director(did)

);

create table directed_by

(

mtitle varchar(20),

year number(4),

did varchar(5),

primary key(mtitle,year,did),

foreign key(mtitle,year) references movie(mtitle,year),

foreign key(did) references director(did)

);

create table acts_in

(

aid varchar(5),

mtitle varchar(20),

foreign key(aid) references actor(aid)

);

create table produced_by

(

pname varchar(20),

mtitle varchar(20),

foreign key(pname) references production(pname)

);

…………………………………………INSERT INTO TABLE……………………

insert into production values('Dharma','Mumbai');

insert into production values('Universal','Mumbai');

insert into production values('Crouching Tiger','Mumbai');

insert into production values('Amir Khan','Mumbai');

insert into actor values ('a1','Shahrukh','01-jan-70');

insert into actor values ('a2','Ajay','01-feb-70');

insert into actor values ('a3','Hrithik','03-mar-80');

insert into actor values ('a4','Akshay','01-jan-90');

insert into actor values ('a5','Jackie','03-mar-60');

insert into actor values ('a6','Yash','02-jan-85');

insert into actor values ('a7','Vivek','02-jan-85');

insert into actor values ('a8','Govinda','02-jan-85');

insert into actor values ('a9','Akhtar','02-jan-85');

insert into actor values ('a10','Mohan','02-jan-85');

insert into director values('d1','Allu Arvind','01-jan-70');

insert into director values('d2','Sanjay Bhansali','02-jan-60');

insert into director values('d3','David Dhavan','03-mar-80');

insert into director values('d4','Farah Khan','01-oct-70');

insert into director values('d5','Gouri Khan','02-nov-80');

insert into director values ('d6','Karan Johar','01-feb-70');

insert into director values ('d7','Rohit Shetty','02-jan-85');

insert into movie values('Raaz','2012','70','Horror','Horror','Dharma','a1','d1');

insert into movie values('DDLJ','2012','70','Love','Love','Amir Khan','a2','d2');

insert into movie values('Titanic','2012','70','Love','Love','Dharma','a3','d3');

insert into movie values('Rebel','2012','70','Action','Action','Crouching Tiger','a4','d1');

insert into movie values('Aarya','1999','70','Love','Action','Dharma','a5','d2');

insert into movie values('RHTDM','2012','70','Love','Love','Amir Khan','a1','d4');

insert into movie values('Elaan','2014','70','Action','Action','Universal','a1','d1');

insert into movie values('Kaal','2012','70','Thriller','Action','Dharma','a4','d1');

insert into movie values('Aashiqui','2012','70','Romantic','Love','Amir Khan','a1','d2');

insert into movie values('Dhoom','2013','70','Action','Action','Dharma','a1','d3');

insert into directed_by values('Raaz','2012','d1');

insert into directed_by values('DDLJ','2012','d2');

insert into directed_by values('Titanic','2012','d1');

insert into directed_by values('Rebel','2012','d2');

insert into directed_by values('Aarya','1999','d1');

insert into directed_by values('Elaan','2012','d2');

insert into directed_by values('Raaz','2012','d2');

insert into directed_by values('Raaz','2012','d3');

insert into produced_by values('Dharma','Raaz');

insert into produced_by values('Universal','Elaan');

insert into produced_by values('Amir Khan','Aashiqui');

insert into produced_by values('Dharma','Dhoom');

insert into produced_by values('Dharma','Titanic');

insert into produced_by values('Crouching Tiger','Rebel');

…………………………………..QUERIES……………………………………….

1.List the details of the horror movies released in 2012 and directed by more than 2 directors.

select m.mtitle

from movie m

where m.year='2012' and

m.genre='Horror' and

m.mtitle in

(

select d.mtitle from directed_by d

where d.year='2012'

group by d.mtitle

having count(d.mtitle)>1

);

SQL> select m.mtitle

2 from movie m

3 where m.year='2012' and

4 m.genre='Horror' and

5 m.mtitle in

6 (

7 select d.mtitle from directed_by d

8 where d.year='2012'

9 group by d.mtitle

10 having count(d.mtitle)>1

11 );

MTITLE

--------------------

Raaz

2.List the details of actors who acted in movies having same titles but released before 2000 and after 2010.

select a.aid,a.aname

from actor a,movie m

where m.aid=a.aid and

m.year not between 2000 and 2010

group by a.aid,aname

having count(a.aid)>1;

SQL> select a.aid,a.aname

2 from actor a,movie m

3 where m.aid=a.aid and

4 m.year not between 2000 and 2010

5 group by a.aid,aname

6 having count(a.aid)>1;

AID ANAME

----- --------------------

a1 Shahrukh

a4 Akshay

3.List the details of production companies producing maximum movies.

select max(pname)

from movie m;

select m.pname

from movie m

group by m.pname

having count(m.pname)>=ALL(select count(*)

from movie

group by pname);

SQL> select m.pname

2 from movie m

3 group by m.pname

4 having count(m.pname)>=ALL(select count(*)

5 from movie

6 group by pname);

PNAME

--------------------

Dharma

4.List the details of movies where director and actor have same date of birth.

select mtitle,year

from movie m,actor a, director d

where d.d_dob=a.a_dob and

m.aid=a.aid and

m.did=d.did;

SQL> select mtitle,year

2 from movie m,actor a, director d

3 where d.d_dob=a.a_dob and

4 m.aid=a.aid and

5 m.did=d.did;

MTITLE YEAR

-------------------- ----------

Elaan 2014

Raaz 2012

Titanic 2012

5.Retreive the names of directors directed all the movies produced by any one production company.

select dname

from director d, movie m

where d.did=m.did and

pname='&pname';

SQL> select dname

2 from director d, movie m

3 where d.did=m.did and

4 pname='&pname';

Enter value for pname: Dharma

old 4: pname='&pname'

new 4: pname='Dharma'

DNAME

--------------------

Allu Arvind

Sanjay Bhansali

David Dhavan

select d.dname

from director d, movie m

where d.did=m.did and

pname='&pname'

group by(d.dname)