andrelashley/Lab5 part 1

## Lab5 part 1
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
	<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
	<title>Results</title>
</head>
<body>
    <?php
    /*
    Lab 5
    part1.php
    Andre Lashley (andre.lashley@gmail.com)
    February 5, 2012
    5 hours estimated completion time
    4 hours actual completion time
    This program connects to a database, runs queries against it and presents the results to the user.
    Visit http://deepblue.cs.camosun.bc.ca/~cst020/comp170/lab5/part1.php to use it
    Query #2 does not work in SQL/92 because the (+) used for the left outer join is not valid in MySQL,
    all the other queries worked correctly.
    */

    // include our database credentials, it is above the public_html directory
    // so that the public cannot get our credentials and compromise the security
    // of the database.
    require('../../../comp170/db.php');

    // choose the db and run a query.
    mysql_select_db("comp170", $conn);

    // create a  query to display the last name, job, department number, and department name for all employees who work in Southlake, Texas in Oracle SQL.
    $q1oracle = "SELECT e.last_name, e.job_id, e.department_id, d.department_name, l.city
    FROM employees e INNER JOIN departments d
    ON (e.department_id = d.department_id)
    INNER JOIN locations l ON(d.location_id = l.location_id)
    WHERE lower(l.city) = 'southlake'";

    // run the query
    $q1result1 = mysql_query($q1oracle, $conn);

    // Display the results.
    echo "<h2>Query 1: Employees who work in Southlake, Texas -- Oracle SQL</h2>";
    if ($q1result1) {
        echo "<table border='1'>";
        echo "<thead>";
        echo "<tr>";
        echo "<th>Employee Last Name</th>";
        echo "<th>Employee Job ID</th>";
        echo "<th>Department ID</th>";
        echo "<th>Department Name</th>";
        echo "</tr>";
        echo "</thead>";
        echo "<tbody>";
        while ($row=mysql_fetch_row($q1result1)) {
            echo "<tr>";
            echo "<td>$row[0]</td>";
            echo "<td>$row[1]</td>";
            echo "<td>$row[2]</td>";
            echo "<td>$row[3]</td>";
            echo "</tr>";
        }
        echo "</tbody>";
        echo "</table>";
    }

    // create a  query to display the last name, job, department number, and department name for all employees who work in Southlake, Texas in SQL 92.
    $q1sql92 = "SELECT last_name, job_id, employees.department_id, departments.department_name, city
    FROM employees, departments, locations
    WHERE employees.department_id = departments.department_id
    AND departments.location_id = locations.location_id
    AND lower(locations.city) = 'southlake'";

    // run the query
    $q1result2 = mysql_query($q1sql92, $conn);

    // Display the results.
    echo "<h2>Query 1: Employees who work in Southlake, Texas Results -- SQL/92</h2>";
    if ($q1result2) {
        echo "<table border='1'>";
        echo "<tr>";
        echo "<th>Employee Last Name</th>";
        echo "<th>Employee Job ID</th>";
        echo "<th>Department ID</th>";
        echo "<th>Department Name</th>";
        echo "</tr>";
        while ($row=mysql_fetch_row($q1result2)) {
            echo "<tr>";
            echo "<td>$row[0]</td>";
            echo "<td>$row[1]</td>";
            echo "<td>$row[2]</td>";
            echo "<td>$row[3]</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

    // create a query that will list all of the employees (last names), whose last name starts with 'G' in SQL/92
    $q2sql92 = "SELECT e.last_name, d.department_name
    FROM employees e, departments d
    WHERE e.department_id (+) = d.department_id
    AND UPPER(e.last_name) LIKE 'G%'";

    // run the query
    $q2result1 = mysql_query($q2sql92, $conn);

    // Display the results.
    // echo "<h2>Query 2: Employees whose last names start with 'G' -- Oracle SQL</h2>";
    if ($q2result1) {
        while ($row=mysql_fetch_row($q2result1)) {
            echo "$row[0], $row[1]<br>\n";
        }
    }


    // create a query that will list all of the employees (last names), whose last name starts with 'G' in SQL/92
    $q2oracle = "SELECT e.last_name, d.department_name
    FROM employees e
    LEFT OUTER JOIN departments d ON (e.department_id = d.department_id)
    WHERE UPPER(e.last_name) LIKE 'G%'";

    // run the query
    $q2result2 = mysql_query($q2oracle, $conn);

    // Display the results.
    echo "<h2>Query 2: Employees whose last names start with 'G' -- Oracle SQL</h2>";
    if ($q2result2) {
        echo "<table border='1'>";
        echo "<tr>";
        echo "<th>Employee Last Name</th>";
        echo "<th>Department Name</th>";
        echo "</tr>";
        while ($row=mysql_fetch_row($q2result2)) {
            echo "<tr>";
            echo "<td>$row[0]</td>";
            echo "<td>$row[1]</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

    // create a query to display the employee last name and employee number along with their manager’s last name and manager number
    $q3oracle = "SELECT w.last_name \"Employee\", w.employee_id \"Emp#\", m.last_name \"Manager\", m.manager_id \"Mgr#\"
    FROM employees w INNER JOIN employees m ON (w.manager_id = m.employee_id)
    WHERE lower(w.last_name) LIKE 'g%'";

    // run the query
    $q3result1 = mysql_query($q3oracle, $conn);

    // Display the results.
    echo "<h2>Query 3: Employees last name and employee number with their manager's last name and number -- Oracle SQL</h2>";
    if ($q3result1) {
        echo "<table border='1'>";
        echo "<tr>";
        echo "<th>Employee Last Name</th>";
        echo "<th>Employee ID</th>";
        echo "<th>Manager's Last Name</th>";
        echo "<th>Manager ID</th>";
        echo "</tr>";
        while ($row=mysql_fetch_row($q3result1)) {
            echo "<tr>";
            echo "<td>$row[0]</td>";
            echo "<td>$row[1]</td>";
            echo "<td>$row[2]</td>";
            echo "<td>$row[3]</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

    // create a query to display the employee last name and employee number along with their manager’s last name and manager number
    $q3sql92 = "SELECT w.last_name \"Employee\", w.employee_id \"Emp#\" , m.last_name \"Manager\", m.manager_id \"Mgr#\"
    FROM employees w, employees m
    WHERE w.manager_id = m.employee_id AND lower(w.last_name) LIKE 'g%'";

    // run the query
    $q3result2 = mysql_query($q3sql92, $conn);

    // Display the results.
    echo "<h2>Query 3: Employees last name and employee number with their manager's last name and number -- SQL/92</h2>";
    if ($q3result2) {
        echo "<table border='1'>";
        echo "<tr>";
        echo "<th>Employee Last Name</th>";
        echo "<th>Employee ID</th>";
        echo "<th>Manager's Last Name</th>";
        echo "<th>Manager ID</th>";
        echo "</tr>";
        while ($row=mysql_fetch_row($q3result2)) {
            echo "<tr>";
            echo "<td>$row[0]</td>";
            echo "<td>$row[1]</td>";
            echo "<td>$row[2]</td>";
            echo "<td>$row[3]</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    ?>
</body>
</html>
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
	<head>
	<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
	<title>Results</title>
	</head>
	<body>
	<?php
	/*
	Lab 5
	part1.php
	Andre Lashley (andre.lashley@gmail.com)
	February 5, 2012
	5 hours estimated completion time
	4 hours actual completion time
	This program connects to a database, runs queries against it and presents the results to the user.
	Visit http://deepblue.cs.camosun.bc.ca/~cst020/comp170/lab5/part1.php to use it
	Query #2 does not work in SQL/92 because the (+) used for the left outer join is not valid in MySQL,
	all the other queries worked correctly.
	*/

	// include our database credentials, it is above the public_html directory
	// so that the public cannot get our credentials and compromise the security
	// of the database.
	require('../../../comp170/db.php');

	// choose the db and run a query.
	mysql_select_db("comp170", $conn);

	// create a query to display the last name, job, department number, and department name for all employees who work in Southlake, Texas in Oracle SQL.
	$q1oracle = "SELECT e.last_name, e.job_id, e.department_id, d.department_name, l.city
	FROM employees e INNER JOIN departments d
	ON (e.department_id = d.department_id)
	INNER JOIN locations l ON(d.location_id = l.location_id)
	WHERE lower(l.city) = 'southlake'";

	// run the query
	$q1result1 = mysql_query($q1oracle, $conn);

	// Display the results.
	echo "<h2>Query 1: Employees who work in Southlake, Texas -- Oracle SQL</h2>";
	if ($q1result1) {
	echo "<table border='1'>";
	echo "<thead>";
	echo "<tr>";
	echo "<th>Employee Last Name</th>";
	echo "<th>Employee Job ID</th>";
	echo "<th>Department ID</th>";
	echo "<th>Department Name</th>";
	echo "</tr>";
	echo "</thead>";
	echo "<tbody>";
	while ($row=mysql_fetch_row($q1result1)) {
	echo "<tr>";
	echo "<td>$row[0]</td>";
	echo "<td>$row[1]</td>";
	echo "<td>$row[2]</td>";
	echo "<td>$row[3]</td>";
	echo "</tr>";
	}
	echo "</tbody>";
	echo "</table>";
	}

	// create a query to display the last name, job, department number, and department name for all employees who work in Southlake, Texas in SQL 92.
	$q1sql92 = "SELECT last_name, job_id, employees.department_id, departments.department_name, city
	FROM employees, departments, locations
	WHERE employees.department_id = departments.department_id
	AND departments.location_id = locations.location_id
	AND lower(locations.city) = 'southlake'";

	// run the query
	$q1result2 = mysql_query($q1sql92, $conn);

	// Display the results.
	echo "<h2>Query 1: Employees who work in Southlake, Texas Results -- SQL/92</h2>";
	if ($q1result2) {
	echo "<table border='1'>";
	echo "<tr>";
	echo "<th>Employee Last Name</th>";
	echo "<th>Employee Job ID</th>";
	echo "<th>Department ID</th>";
	echo "<th>Department Name</th>";
	echo "</tr>";
	while ($row=mysql_fetch_row($q1result2)) {
	echo "<tr>";
	echo "<td>$row[0]</td>";
	echo "<td>$row[1]</td>";
	echo "<td>$row[2]</td>";
	echo "<td>$row[3]</td>";
	echo "</tr>";
	}
	echo "</table>";
	}

	// create a query that will list all of the employees (last names), whose last name starts with 'G' in SQL/92
	$q2sql92 = "SELECT e.last_name, d.department_name
	FROM employees e, departments d
	WHERE e.department_id (+) = d.department_id
	AND UPPER(e.last_name) LIKE 'G%'";

	// run the query
	$q2result1 = mysql_query($q2sql92, $conn);

	// Display the results.
	// echo "<h2>Query 2: Employees whose last names start with 'G' -- Oracle SQL</h2>";
	if ($q2result1) {
	while ($row=mysql_fetch_row($q2result1)) {
	echo "$row[0], $row[1]<br>\n";
	}
	}


	// create a query that will list all of the employees (last names), whose last name starts with 'G' in SQL/92
	$q2oracle = "SELECT e.last_name, d.department_name
	FROM employees e
	LEFT OUTER JOIN departments d ON (e.department_id = d.department_id)
	WHERE UPPER(e.last_name) LIKE 'G%'";

	// run the query
	$q2result2 = mysql_query($q2oracle, $conn);

	// Display the results.
	echo "<h2>Query 2: Employees whose last names start with 'G' -- Oracle SQL</h2>";
	if ($q2result2) {
	echo "<table border='1'>";
	echo "<tr>";
	echo "<th>Employee Last Name</th>";
	echo "<th>Department Name</th>";
	echo "</tr>";
	while ($row=mysql_fetch_row($q2result2)) {
	echo "<tr>";
	echo "<td>$row[0]</td>";
	echo "<td>$row[1]</td>";
	echo "</tr>";
	}
	echo "</table>";
	}

	// create a query to display the employee last name and employee number along with their manager’s last name and manager number
	$q3oracle = "SELECT w.last_name \"Employee\", w.employee_id \"Emp#\", m.last_name \"Manager\", m.manager_id \"Mgr#\"
	FROM employees w INNER JOIN employees m ON (w.manager_id = m.employee_id)
	WHERE lower(w.last_name) LIKE 'g%'";

	// run the query
	$q3result1 = mysql_query($q3oracle, $conn);

	// Display the results.
	echo "<h2>Query 3: Employees last name and employee number with their manager's last name and number -- Oracle SQL</h2>";
	if ($q3result1) {
	echo "<table border='1'>";
	echo "<tr>";
	echo "<th>Employee Last Name</th>";
	echo "<th>Employee ID</th>";
	echo "<th>Manager's Last Name</th>";
	echo "<th>Manager ID</th>";
	echo "</tr>";
	while ($row=mysql_fetch_row($q3result1)) {
	echo "<tr>";
	echo "<td>$row[0]</td>";
	echo "<td>$row[1]</td>";
	echo "<td>$row[2]</td>";
	echo "<td>$row[3]</td>";
	echo "</tr>";
	}
	echo "</table>";
	}

	// create a query to display the employee last name and employee number along with their manager’s last name and manager number
	$q3sql92 = "SELECT w.last_name \"Employee\", w.employee_id \"Emp#\" , m.last_name \"Manager\", m.manager_id \"Mgr#\"
	FROM employees w, employees m
	WHERE w.manager_id = m.employee_id AND lower(w.last_name) LIKE 'g%'";

	// run the query
	$q3result2 = mysql_query($q3sql92, $conn);

	// Display the results.
	echo "<h2>Query 3: Employees last name and employee number with their manager's last name and number -- SQL/92</h2>";
	if ($q3result2) {
	echo "<table border='1'>";
	echo "<tr>";
	echo "<th>Employee Last Name</th>";
	echo "<th>Employee ID</th>";
	echo "<th>Manager's Last Name</th>";
	echo "<th>Manager ID</th>";
	echo "</tr>";
	while ($row=mysql_fetch_row($q3result2)) {
	echo "<tr>";
	echo "<td>$row[0]</td>";
	echo "<td>$row[1]</td>";
	echo "<td>$row[2]</td>";
	echo "<td>$row[3]</td>";
	echo "</tr>";
	}
	echo "</table>";
	}
	?>
	</body>
	</html>