Question

Database query problem

the questions 2,5 and 6 of this sample paper

question 2 is not written properly, it should be product name , supplier name which supplies maximum quantity of each product

Product Supplier prid prname price (int) (int) vc(10) PK 101 Scale 104 Sketch 110 105Tape 107 Pad 110 Pad 112Pin 124 Pin 135 Paper supid (int) PK vc(10) supname suploc vc(10) Delhi Crown TopperDelhi Avon Swan Rally Buddy 30 7 Mumbai Hyderabad Chennai Chennai 15 160 25 13 15 120 110 1.Get the prod ID, prod Name, Qty for those products which are supplied at least 200 quantities by all partner supplier. Supply 2. Get the product Name, and supplier name which supplying maximum quantities pid (PK1 and FK to Sid( PK2 and FK Qty (int) prid of Product) to supid Not greater than 500 420 380 350 500 200 10 Supplier) 3. Get supplier name those supplying at least 400 quantities of products having cost above 100 104 104 105 107 107 107 110 110 110 110 124 124 124 13 13 4. Get the prod Name, and number of supplier. 5. Write a function Fun_<your ID> that takes the product id as input and return supplied quantities 35 150 20 300 34 12 13 15 6. Write a Procedure PROC1_<your ID> that takes the product ID as the input and call the function Fun_<your ID> to calculate the supplied quantities. This Procedure wil prints-<Product name> has supplied: quantities>quantities by <number of supplier> numbers of supplier.' If no product supplied, will print- "Sorry Not supplied". 13

Answer 1

Qo.

ANS:

Let's Create DB first and their structure and then join tables them by INNER JOIN.

Product Databse1 Structure: IMAGE:

Server: 127.0.0.1>Database: stationaryTable: product 目Browse И Structure gSQL → Search Insert -Export -Import na Privileges / Operations ⑤ Tracking 2e Triggers Table structure 唱Relation view # Name Type Collatio Attributes Null Default Comments Extra Action Drop Primary Lu Unique Index E Spatial En Fulltext ▼ More Drop Primary u Unique诅Index E Spatial T Fulltext ▼ More Drop de Primaryj Unique圃IndexE Spatial Tj Fulltext ▼More dp Change Change / Change prid int(11) 2 prname varchar(10) latin1_swedish_ci 3 price nt 11) No None No None No None P Primary Check all With selected: E Browse ノChange O Drop Index Add to central columns Remove from central columns u Unique Move the columns by dragging them up and down

DATA IMAGE:

HT- prid prnameprice Edit CopyDelete 101 Scale 30 110 Edit i Copy Delete 105 Tape 15 160 25 5 120 110 Edit e Copy Delete 104 Sketch Edit e Copy Delete 107 Pad Edit Copy Delete 110 Pad Edit i CopyDelete 112 Pin Edit i Copy Delete 124 Pin Edit i CopyDelete 135 Paper

Supplier Databse2 Structure: IMAGE:

Server: 127.0.0.1> Database: stationaryTable: supplier Browse И structure!目SQL Search Insert -Export | 몬 Import .n Privileges / Operations ® Tracking as Triggers Table structureRelation view Name Type Collation 1 Attributes Null Default Comments Extra Actio up Change e Drop P Primary IU Unique Index Spatial ▼ More up Change Drop rp Primary U Unique妇Index匤Spatial ▼ More ChangeroPrimary u UniqueIndex I SpatialMore 0 supid. int(11) No None No None No None supname varchar(10) latin1_swedish_ci 3 suploc varchar(10) latin1_swedish_ci t- o check all With selected: Browse-, Change Drop Primary E] Index Add to central columns 욱 Remove from central columns E iu Unique

DATA IMAGE:

Server: 127.0.0.1 » Database: stationaryTable: supplier 目Browse yt Structure LjSQL 、Search Insert Show BLOB conte supid supname suploc Edit CopyDelete 3 Crown Delhi Edit 3 CopyDelete Avon Edit 3 CopyDelete 3 Rally Edit İ.Ề Copy Delete Delhi Mumbai Hyderabad Chennai lopper Edit恥Copy Delete 11 Swan Edit Copy Delete 15 Buddy Chennai t-o check all With selected: Edit Copy Delete曇E

Supply Databse3 Structure: IMAGE:

Server 1270 01 ® Database stationary 》圜Table supply ■ Browse Bt Structure □SQL → Search Insert -Export H Import nl Privileges / Operations ⑤ Tracking 2 Triggers Table structure Relation view Name Type Collation Attributes Null Default Comments Extra Action 1 pidint(11) 2 sidint(11) 3 qty int(11) up Change Drop Primary .U Unique 1ndex Spatial m Fulltext ▼ More Change Drop de Primary .U Unique归Index匤Spatial-ti Fulltext ▼ More Changeo Primary Unique Index s Spatial FulltextMore No None No None No None tCheck all With selected: BrowseChangeDropPrimary u UniqueIndex Add to centralomnsRemove from central colum

DATA IMAGE:

Server: 127.0.0.1 » Database: stationaryTable: supply Br O Full texts O Display column for relationships Structure !」SQL Search SearchnserExport Show BLOB contents pid sid qty O"PEdit Copy > Delete 104 7420 Edit e Copy Delete 104 13 380 Edit ẻ Copy Delete 105 13 350 Edite Copy Delete 1075 500 Edit e Copy Delete 107 7 10 Edit e Copy Delete 107 11 200 Edit ẻ Copy Delete 110 3 22 u? Edit恥Copy() Delete 110 5 35 Edit ẻ Copy Delete 110 13 150 Edit恥Copy Delete 110 15 20 Edit ẻ Copy Delete 124 7300 Edit Copy Delete 124 11 34 O "PEdit Copy > Delete 124 13 12

Now comes to query(Questions)

NOTE: I am using PHP MySQL DB, but queries are same in SQL DB, So focus on the query rather than medium).

Qo2: get the product name, supplier name which supplying maximum quantities of each product.

ANS:
QUERY IS: "SELECT prid,prname,supname,qty FROM product INNER JOIN supply on product.prid=supply.pid INNER JOIN supplier on supplier.supid=supply.sid order by qty desc";

PHP CODE IS:

<html>
<head>
   <title>Show Data</title>
   <style>
       table{
           border-collapse: collapse;
           width: 100%;
       }
       tr,td{
           padding: 15px;
           text-align: center;
       }
   </style>
</head>
<?php
   $product_db=mysqli_connect("localhost","root","","stationary") or die("Can't connect to db");
   $product_query="SELECT prid,prname,supname,qty FROM product INNER JOIN supply on product.prid=supply.pid INNER JOIN supplier on supplier.supid=supply.sid order by qty desc";
   $product_result=mysqli_query($product_db,$product_query);
   echo "<table border='1'>";
   echo "<tr>
           <th>Product ID</th>
           <th>Product Name</th>
           <th>Supplier Name</th>
           <th>Quantity(s)</th>
       </tr>";
   while($product_ans=mysqli_fetch_assoc($product_result))
   {
       echo "<tr>
               <td>".$product_ans['prid']."</td>
               <td>".$product_ans['prname']."</td>
               <td>".$product_ans['supname']."</td>
               <td>".$product_ans['qty']."</td>
           </tr>";
      
   }
   echo "</table>";
?>

you can find max using max(qty).

OUTPUT IMAGE:

Product ID Product Name Supplier Name Quantity(s) 107 Pad Topper 104 Sketch 420 Rally 104 Sketch 380 105 Tape 350 124 107 Pad Swan Rally 150 110 Pad 110 Pad Topper 35 124 Swan Pad 110 Buddy 110 Pad 124 Rally 107 10 Pad von

Q5) write a function that takes the product id as input and return supplied quantity.

ANS: Function is:

function getQuantity($prod_id)
   {
       $product_db=mysqli_connect("localhost","root","","stationary") or die("Can't connect to db");
       $product_query="SELECT * FROM product INNER JOIN supply on product.prid=supply.pid where prid=$prod_id";
       $product_result=mysqli_query($product_db,$product_query);
       $product_ans=mysqli_fetch_assoc($product_result);
       $totalQty=$product_ans['qty'];
       return $totalQty;
   }
  
   echo "Quantity is: ".getQuantity(104).'<br/>';
   echo "Quantity is: ".getQuantity(105).'<br/>';

Qo6)

ANS: Function with PHP Code is:

<html>
<head>
   <title>Show Data</title>
   <style>
       table{
           border-collapse: collapse;
           width: 100%;
       }
       tr,td{
           padding: 15px;
           text-align: center;
       }
   </style>
</head>
<?php
  
   function getQuantity($prod_id)
   {
       $product_db=mysqli_connect("localhost","root","","stationary") or die("Can't connect to db");
       $product_query="SELECT * FROM product INNER JOIN supply on product.prid=supply.pid INNER JOIN supplier on supplier.supid=supply.sid where prid=$prod_id ";
       $product_result=mysqli_query($product_db,$product_query);
      
       if(mysqli_num_rows($product_result)>0)
       {
           echo "<table border='1'>";
               echo "<tr>
                   <th>Product ID</th>
                   <th>Product Name</th>
                   <th>Supplier Name</th>
                   <th>Quantity(s)</th>
               </tr>";
           while($product_ans=mysqli_fetch_assoc($product_result))
           {
               echo "<tr>
                       <td>".$product_ans['prid']."</td>
                       <td>".$product_ans['prname']."</td>
                       <td>".$product_ans['supname']."</td>
                       <td>".$product_ans['qty']."</td>
                   </tr>";
              
           }
           echo "</table>";
       }
       else{
           echo "No Product Supplied";
       }
   }
       getQuantity(104);
       getQuantity(105);
?>

OUTPUT IMAGE:

Supplier Name Product ID Product Name Quantity(s) ID 104 Sketch Avon 420 104 Product ID 105 Sketch Product Name Tape Rall Supplier Name ealiy 380 Quantity(s) 350
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

THANKS!!