Question

Using Oracle database

Need help getting the JAVA code for the queries and the rest of the instructions. Need the table provided converted and fulfil  the requirements.

.

For this project - you will be writing a program (you may choose whatever programming language you want) to read in your database tables from Lab 5, and then print out the contents of each of the tables. Additionally, you must print at least one query each with the following clauses:
*JOIN (any version)
*WHERE
*ORDER BY

You must submit the following:
*Your program's source code
*The SQL commands you used
*Screenshots of the program's output

Lab 5 Table:

Create table Guest
(
GuestId int not null primary key,
GuestFirstName varchar(20),
GuestLastName varchar(20),
GuestAddress varchar(30),
GuestCity varchar (20),
GuestPhone varchar(20),
GuestEmail varchar(30),
StayId int
);

Create table Volunteer
(
VolunteerID int not null primary key,
VolunteerFirstName varchar(30),
VolunteerLastName varchar(20),
VolunteerAddress varchar (30),
VolunteerCity varchar(20),
VolunteerPhone varchar(20),
VolunteerEmail varchar(30),
ShiftID int
);

Create table Shift
(
ShiftID int not null primary key,
ShiftIN Date,
ShiftOut Date
);

Create table Inventory
(
InventorySku int not null primary key,
InventoryName varchar(30),
InventoryPrice dec,
InventoryStocked int
);

Create table Schedule
(
GuestCard int not null primary key,
CheckIn date,
CheckOut date,
room int
);

Create table Donor
(
DonorID int not null primary key,
DonorFirstName Varchar(30),
DonorLastName varchar(20),
DonorAddress varchar(30),
DonorCity varchar (20),
DonorPhone varchar(30),
DonorEmail varchar(30),
DonorAmount int
);

insert into Guest values (1,'Bob','Dylan','607Maine','Greensville','6904931104','[email protected]',635783);
insert into Guest values (2,'Sam','Porter','901Fordza','Drive','79630698836','[email protected]',9924573);
insert into Guest values (3,'Chuck','Barter','Slugaa','Molkvia','3215789033','RoxoN@xxx',63267326);
insert into Guest values (4,'Emma','Stone','411Holly','WoodBlvd','7246793226','CelebInfo@axxxx',1112244778);
insert into Guest values (5,'Paige','Maye','254Looms','Ipelka','35974324780','RoboSwa@xxxx',66447837);

insert into Volunteer values (90,'Mike','Dickeson','32Chicago','Bears','69021356332','MickeyD@xxxx',2019121);
insert into Volunteer values (77,'Coach','Carter','Ludvale','Atlanta','9035467789','CCoach@xxxx',20191090);
insert into Volunteer values (23,'Larry','Ludo','30Mega','Watkinsville','11156267252','RoadHog@xxx',2019315);
insert into Volunteer values (10,'Taylor','Ireka','911Gotham','City','6342139902','TyGod@xxxx',2019923);
insert into Volunteer values (68,'Elvis','Elton','4Rocky Rd','Athens','89513631244','Starway@xxxxxx',20191012);

insert into Shift values (20190207,TO_Date('20190207','YYYYMMDD'),TO_Date('20190213','YYYYMMDD'));
insert into Shift values (20190821,TO_Date('20190821','YYYYMMDD'),TO_Date('20190916','YYYYMMDD'));
insert into Shift values (20190113,TO_Date('20190113','YYYYMMDD'),TO_Date('20190130','YYYYMMDD'));
insert into Shift values (20190420,TO_Date('20190420','YYYYMMDD'),TO_Date('20190501','YYYYMMDD'));
insert into Shift values (20191008,TO_Date('20191008','YYYYMMDD'),TO_Date('20191220','YYYYMMDD'));

Insert into Inventory values (912109,'Clothes',13.43,5);
insert into Inventory values (789567,'Remotes',9.50,3);
insert into Inventory values (605932,'Furniture',26.37,2);
insert into Inventory values (411235,'Sandwhich',3.99,7);
insert into Inventory values (009576,'Bags',0.99,99);

Insert into Schedule values (02072019,TO_Date('20190207','YYYYMMDD'),TO_Date('20190128','YYYYMMDD'),12);
insert into Schedule values (08212019,TO_Date('20190821','YYYYMMDD'),TO_Date('20191108','YYYYMMDD'),14);
insert into Schedule values (01132019,TO_Date('20190113','YYYYMMDD'),TO_Date('20190319','YYYYMMDD'),1);
insert into Schedule values (04202019,TO_Date('20190420','YYYYMMDD'),TO_Date('20190919','YYYYMMDD'),7);
insert into Schedule values (10082019,TO_Date('20191008','YYYYMMDD'),TO_Date('20191028','YYYYMMDD'),6);

Insert into Donor values (003,'Carl','Carly','45Alley','Atlanta',9102108443,'carlCar@xxxxx',76);
insert into Donor values (001,'Chuck','Berry','87Pipernickle','Athens',6782680012,'[email protected]',334);
insert into Donor values (007,'Tom','Jerry','01Which','Watkinsville',6325896541,'[email protected]',600);
insert into Donor values (004,'Todd','Gurley','Football','Stadium',7869367833,'FootB@xxxx',2300);
insert into Donor values (006,'Kanye','West','North','West',6983859205,'Yeezus@xxxx',5000);
insert into Donor values (009,'Kenny','Gee','Worlds','Ends',8374823859,'KenG@xxxxx',34);

Answer 1

solution: I have used php programming language with html

// ---------------- Connect.php ---------------

<?php
date_default_timezone_set('Asia/Calcutta');

function compressImage($source_url, $destination_url, $quality) {

$info = getimagesize($source_url);

if ($info['mime'] == 'image/jpeg')
$image = imagecreatefromjpeg($source_url);

elseif ($info['mime'] == 'image/gif')
$image = imagecreatefromgif($source_url);

elseif ($info['mime'] == 'image/png')
$image = imagecreatefrompng($source_url);

imagejpeg($image, $destination_url, $quality);
return $destination_url;
}

function base64_to_jpeg($base64_string, $output_file) {
$data = explode( ',', $base64_string );
$data1 = imagejpeg(imagecreatefromstring(base64_decode($data[1])),$output_file,50);
return $output_file;
}

function ago( $time )
{
$time_difference = time() - $time;

if( $time_difference < 1 ) { return 'less than 1 second ago'; }
$condition = array( 12 * 30 * 24 * 60 * 60 => 'year',
30 * 24 * 60 * 60 => 'month',
24 * 60 * 60 => 'day',
60 * 60 => 'hour',
60 => 'minute',
1 => 'second'
);

foreach( $condition as $secs => $str )
{
$d = $time_difference / $secs;

if( $d >= 1 )
{
$t = round( $d );
return '' . $t . ' ' . $str . ( $t > 1 ? 's' : '' ) . ' ago';
}
}
}

function ConvertToK($num) {

if($num>1000) {

$x = round($num);
$x_number_format = number_format($x);
$x_array = explode(',', $x_number_format);
$x_parts = array('k', 'm', 'b', 't');
$x_count_parts = count($x_array) - 1;
$x_display = $x;
$x_display = $x_array[0] . ((int) $x_array[1][0] !== 0 ? '.' . $x_array[1][0] : '');
$x_display .= $x_parts[$x_count_parts - 1];

return $x_display;

}

return $num;
}

function rand_color() {
return '#' . str_pad(dechex(mt_rand(0, 0xFFFFFF)), 6, '0', STR_PAD_LEFT);
}

$servername = "localhost";
$uname = "root";
$pword = "";
$dbname = "lab 5";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $uname, $pword);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>

//----------------- END OF CONNECT FILE ------------------------------

//------------------------------- index.php --------------------

<?php
include 'connect.php';

?>

<!DOCTYPE html>
<html>
<head>
<title></title>

<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
</style>
</head>
<body>

<!-- Display data from volunteer and shift table using join -->

<center>
<h1> Display data from volunteer and shift table using join</h1>
</center>

<table style="width:100%;">
<tr>
<th>Volunteer First Name</th>
<th>Volunteer Last Name</th>
<th>Volunteer Address</th>
<th>Volunteer City</th>
<th>Volunteer Phone</th>
<th>Volunteer Email</th>
<th>Shift IN </th>
<th>Shift Out </th>
</tr>
  

<?php

$stmt = $conn->prepare("SELECT v.VolunteerFirstName as vfn, v.VolunteerLastName as vln, v.VolunteerAddress as va, v.VolunteerCity as vc, v.VolunteerPhone as vp, v.VolunteerEmail as ve, s.ShiftIN as sin, s.ShiftOut as sout from volunteer as v join shift as s on s.ShiftID=v.ShiftID ");
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
if($stmt->rowCount()>0){
foreach(($stmt->fetchAll()) as $k=>$row) {
echo '<tr>';
echo '<td>' .$row['vfn']. '</td>';
echo '<td>' .$row['vln']. '</td>';

echo '<td>' .$row['va']. '</td>';
echo '<td>' .$row['vc']. '</td>';

echo '<td>' .$row['vp']. '</td>';
echo '<td>' .$row['ve']. '</td>';
echo '<td>' .$row['sin']. '</td>';
echo '<td>' .$row['sout']. '</td>';

echo ' </tr>';

}
}

?>

</table>

<br><br><br>

<!-- Display guest using where clause -->
<center>
<h1>Display guest using where clause</h1>
</center>
<table style="width:100%;">
<tr>
<th>Guest First Name</th>
<th>Guest Last Name</th>
<th>Guest Address</th>
<th>Guest City</th>
<th>Guest Phone</th>
<th>Guest Email</th>

</tr>
  

<?php

$stmt = $conn->prepare("SELECT * from guest where GuestId=1 ");
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
if($stmt->rowCount()>0){
foreach(($stmt->fetchAll()) as $k=>$row) {
echo '<tr>';
echo '<td>' .$row['GuestFirstName']. '</td>';
echo '<td>' .$row['GuestLastName']. '</td>';

echo '<td>' .$row['GuestAddress']. '</td>';
echo '<td>' .$row['GuestCity']. '</td>';

echo '<td>' .$row['GuestPhone']. '</td>';
echo '<td>' .$row['GuestEmail']. '</td>';

echo ' </tr>';

}
}

?>

</table>

<br><br><br>

<!-- Display guest using order by clause -->
<center>
<h1>Display guest using order by clause</h1>
</center>

<table style="width:100%;">
<tr>
<th>Guest Id's</th>
<th>Guest First Name</th>
<th>Guest Last Name</th>
<th>Guest Address</th>
<th>Guest City</th>
<th>Guest Phone</th>
<th>Guest Email</th>

</tr>
  

<?php

$stmt = $conn->prepare("SELECT * from guest ORDER BY GuestId desc ");
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
if($stmt->rowCount()>0){
foreach(($stmt->fetchAll()) as $k=>$row) {
echo '<tr>';
echo '<td>' .$row['GuestId']. '</td>';

echo '<td>' .$row['GuestFirstName']. '</td>';
echo '<td>' .$row['GuestLastName']. '</td>';

echo '<td>' .$row['GuestAddress']. '</td>';
echo '<td>' .$row['GuestCity']. '</td>';

echo '<td>' .$row['GuestPhone']. '</td>';
echo '<td>' .$row['GuestEmail']. '</td>';

echo ' </tr>';

}
}

?>

</table>

</body>
</html>

//------------------------ end of index.php file -------------------