Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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="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>3. Demonstrate how you delete all account tuples at every branch located in a specific city</b><br>";
- echo "<br></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";*/
- }
- $conn->close();
- ?>
Add Comment
Please, Sign In to add comment