<?php
$servername ="localhost";
$username="root";
$password="jit123";
$dbname="1jt14cs007db5";

$conn= new mysqli($servername,$username,$password,$dbname);

if($conn->connect_error)
    {
        die("CONN failed :" . $conn->connect_error);
    }
else
    {

        $sqlquery="select d.customername
                   from depositor d, account a
                   where d.accno=a.accno
                   group by d.customername having count(d.customername)>=2;";

        $result = $conn->query($sqlquery);

        if($result->num_rows>0)
        {
            echo "<p align=\"center\"><b>1. Find all the customers who have at least two accounts at the Main branch</b><br>";
            echo "<br>select d.customername<br>
                   from depositor d, account a<br>
                   where d.accno=a.accno<br>
                   group by d.customername having count(d.customername)>=2;</p>";
            echo "<table align=\"center\"border=1> <tr> <th> Customer Name </th> </tr>";
            while($row=$result->fetch_assoc())
            {

                echo "<tr><td>$row[customername]</td></tr>";

            }
            echo "</table>";
        }
        else echo "Empty Set";


        $sqlquery="select d.customername , count(distinct b.branchname) as cnt
        from account a, depositor d, branch b
        where a.accno=d.accno and b.branchname =a.branchname and b.branchcity='bangalore'
        group by d.customername having count(distinct b.branchname) = (select count(distinct b.branchname)
        from branch b where b.branchcity='bangalore');";

        $result = $conn->query($sqlquery);

        if($result->num_rows>0)
        {
            echo "<p align=\"center\"><b>2. Find all the customers who have an account at all the branches located in a specific city</b><br>";
            echo "<br>select d.customername , count(distinct b.branchname) as cnt<br>
            from account a, depositor d, branch b<br>
            where a.accno=d.accno and b.branchname =a.branchname and b.branchcity=\"bangalore\"<br>
            group by d.customername having count(distinct b.branchname) = (select count(distinct b.branchname)<br>
            from branch b where b.branchcity=\"bangalore\");</p>";

            echo "<table align=\"center\"border=1> <tr> <th> Customer Name </th> <th> Count </th> </tr>";
            while($row=$result->fetch_assoc())
            {

                echo "<tr><td>$row[customername]</td><td>$row[cnt]</td></tr>";

            }
            echo "</table>";
        }
        else echo "Empty Set";

        $sqlquery="delete from account where branchname in ( select branchname from branch where branchcity=‘mumbai‘);";
        $result = $conn->query($sqlquery);

        $sqlquery="select * from account";
        $result = $conn->query($sqlquery);

        if($result->num_rows>0)
        {
            echo "<p align=\"center\"><b>3. Demonstrate how you delete all account tuples at every branch located in a specific city</b><br>";
            echo "<br>delete from account where branchname in ( select branchname from branch where branchcity=‘mumbai‘);</p>";

            echo "<table align=\"center\"border=1> <tr> <th> Account Number </th> <th> Branch Name </th> <th> Balance </th></tr>";
            while($row=$result->fetch_assoc())
            {

                echo "<tr><td>$row[accno]</td><td>$row[branchname]</td><td>$row[balance]</td</tr>";

            }
            echo "</table>";
        }
        else echo "Empty Set";

    }


$conn->close();
?>