1. Write a
PHP
program to display “Hello World” Message on
Screen.
Practical-1.php
<html>
<head>
<title>Practical-1</title>
</head>
<?php
echo "<b>Hello</b>
<i>World</i>";
?>
<body>
</body>
</html>
Output:
Hello World
2.
Write a PHP program
to display the today’s date and
current time.
Practical-2.php
<?php
print strftime('%c');
echo "<br />";
print strftime('%d/%m/%Y');
echo "<br />";
print strftime('%A,
%d %B - %Y');
echo "<br />";
echo "<b>Current Day, Date and Time
is:</b> " . date("D M d, Y G:i A");
?>
Output:
01/19/14 11:31:16
19/01/2014
Sunday, 19 January - 2014
Current Day, Date and Time is: Sun Jan 19, 2014 11:31 AM
3.
Write a PHP program to calculate sum of given number
using function.
Practical-3.php
<?php
$val1=10;
$val2=10;
function sum($val1,$val2)
{
$total=$val1+$val2;
return $total;
}
echo "<b>Sum using Function : </b>" . sum($val1,$val2);
$sum=$val1
+ $val2;
echo "<br />";
echo "<b>Sum is :</b> $sum";
?>
Output:
Sum using Function
: 20
Sum is : 20
4. Write a PHP Program that will
use the concept form.
Practical-4.php
<html>
<head>
<title>Practical-4
Form Concept</title>
</head>
<body>
<form name="frmdemo"
action="practical-4a.php" method="post" >
<fieldset>
<legend>Enter Your Details</legend>
<table width="250px" border="2"
align="center">
<tr>
<td align="right">Name</td>
<td><input type="text" name="txtname"></td>
</tr>
<tr>
<td
align="right">Contact No</td>
<td><input
type="text" name="txtcno"></td>
</tr>
<tr>
<td
colspan="2" align="center">
<input type="submit" name="submit"
value="Submit">
</td>
</tr>
</table>
</fieldset>
</form>
</body>
</html>
Practical-4a.php
<?php
if(isset($_REQUEST['submit']))
{
$name=$_REQUEST['txtname'];
$cno=$_REQUEST['txtcno'];
echo "<b>Your Name is: </b>" . $name . "<br>";
echo "<b>Contact No: </b>" . $cno;
}
else
{
echo
"Go Back and Press Submit
button";
}
?>
Output:
5. Write a PHP program to prepare
student Mark sheet using Switch statement.
Practical-5.php
<html>
<head>
<title>Practical-5
Marksheet</title>
</head>
<body>
<form name="frmdemo"
action="practical-5a.php" method="post" >
<fieldset>
<legend
align="center">Enter Your Name with Marks Detail</legend>
<table width="250px" border="2"
align="center">
<tr>
<td align="right">Name</td>
<td><input
type="text" name="txtname"></td>
</tr>
<tr>
<td align="right">Subject-1</td>
<td><input
type="text" name="txtsub1"></td>
</tr>
<tr>
<td
align="right">Subject-2</td>
<td><input
type="text" name="txtsub2"></td>
</tr>
<tr>
<td
align="right">Subject-3</td>
<td><input
type="text" name="txtsub3"></td>
</tr>
<tr>
<td
align="right">Subject-4</td>
<td><input
type="text" name="txtsub4"></td>
</tr>
<tr>
<td
colspan="2" align="center">
<input type="submit" name="submit" value="Submit"></td>
</tr>
</table>
</fieldset>
</form>
</body>
</html>
Output:
Practical
5a.php
<?php
if(isset($_REQUEST['submit']))
{
$name=$_REQUEST['txtname'];
$sub1=$_REQUEST['txtsub1'];
$sub2=$_REQUEST['txtsub2'];
$sub3=$_REQUEST['txtsub3'];
$sub4=$_REQUEST['txtsub4'];
echo "<b>Your Name
is: </b>" . $name . "<br>";
echo "<b>Your Marks Detail: </b> <br>";
echo "Subject-1:
" . $sub1 . "<br>"; echo "Subject-2: " . $sub2 .
"<br>"; echo "Subject-3: " . $sub3 .
"<br>"; echo "Subject-4: " . $sub4 .
"<br>";
$total=$sub1+$sub2+$sub3+$sub4;
echo "Total Marks:
" . $total . "<br>";
$per=$total/4;
echo "Percentage: "
. $per . "%<br>";
switch($per)
{
case $per<35:
echo "Grade: F" .
"<br>";
break;
case $per>=35
&& $per<=50: echo "Grade: D" . "<br>";
break;
case $per>50 && $per<=60:
echo "Grade: C" .
"<br>";
break;
case $per>60 && $per<=70:
echo "Grade: B" .
"<br>";
break;
case $per>70 && $per<100:
echo "Grade: A" .
"<br>";
break;
default:
echo "Invalid.... or out of limit";
break;
}
}
else
{
echo "Go Back and
Press Submit button";
}
?>
Output:
6. Write a PHP program to
generate the multiplication of
matrix. Practical-6.php
<?php
$p = array();
$p[] = array(1,3,-4);
$p[] = array(1,1,-2);
$p[] = array(-1,-2,5);
$q = array();
$q[] = array(8,3,0);
$q[] = array(3,10,2);
$q[] = array(0,2,6);
echo "matrix 1<br/>"; echoMatrix(3, $p); echo "<br/>";
echo "matrix
2<br/>"; echoMatrix(3, $q); echo "<br/>";
$r = matrixMultiply(3,
$p, $q);
echo "result
of matrix
multiply<br/>";
echoMatrix(3, $r);
function echoMatrix($N, $r)
{
for($i=0; $i<$N; $i++)
{
for($j=0; $j<=$N; $j++)
{ if ($j==$N)
{
echo "<br>";
}
else
{
echo $r[$i][$j];
}
if ($j<($N-1))
{
echo ", ";
}
}
}
}
function matrixMultiply($N, $p, $q)
{
$r =
array();
for($i=0; $i<$N; $i++)
{
$t = array();
for($j=0; $j<$N; $j++)
{
$t[] = 0;
}
$r[] = $t;
}
for($i=0; $i<$N;
$i++)
{
for($j=0; $j<$N; $j++)
{
$t = 0;
for($k=0; $k<$N; $k++)
{
$t += $p[$i][$k] * $q[$k][$j];
}
$r[$i][$j] = $t;
}
}
return $r;
}
?>
Output:
7. Write a PHP
application to add new Rows in a Table.
1. Open Xampp control panel
(StartàAll ProgramsàXampp control panel).
2. After opening Xampp
control panel, select Start button
behind the Apache and Start button
behind the MYSQL.
3. Select Admin button as
shown in fig.
4.
Click on User Accounts tab. Click on Add User Account button.
5.
Select
Databases tab and create a Database as ShaikDB
and click Create button.
6.
Create table Student as shown below.
7.
After selecting
Go button, Add the field name as
shown below.
8.
Give
the filed name and its values as follows
Name Type Length Attributes null Index
id INT 6
UNSIGNED AUTO_INCREMENT PRIMARY KEY
firstname VARCHAR 30 NOT NULL
firstname VARCHAR 30 NOT NULL
lastname VARCHAR 30 NOT NULL
email VARCHAR 50
email VARCHAR 50
reg_date TIMESTAMP.
9.
After
typing the information, select save button.
10. Now minimize and then type the program in
notepad and save it as practical-7.php
Practical-7.php
<?php
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO Student (id,firstname, lastname, email)
VALUES (1,'John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO Student (id,firstname, lastname, email)
VALUES (1,'John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
8. Write a PHP
application to modify the Rows in a Table.
1. Open Xampp control panel
(StartàAll ProgramsàXampp control panel).
2. After opening Xampp
control panel, select Start button
behind the Apache and Start button
behind the MYSQL.
3. Select Admin button as
shown in fig.
3.
Click on User Accounts tab. Click on Add User Account button.
4.
Select
Databases tab and create a Database as ShaikDB
and click Create button.
5.
Create table Student as shown below.
6.
After selecting
Go button, Add the field name as
shown below.
7.
Give
the filed name and its values as follows
Name Type Length Attributes null Index
id INT 6
UNSIGNED AUTO_INCREMENT PRIMARY KEY
firstname VARCHAR 30 NOT NULL
firstname VARCHAR 30 NOT NULL
lastname VARCHAR 30 NOT NULL
email VARCHAR 50
email VARCHAR 50
reg_date TIMESTAMP.
After typing the
information, select save button.
8.
Select
Insert tab.
9.
Insert values
in particular fields and click on save button.
10.
Minimize the Xampp control panel and type the
following code.
Practical-8.php
<?php
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
9.
Write a PHP application to delete the Rows from a Table.
1. Open Xampp control panel
(StartàAll ProgramsàXampp control panel).
2. After opening Xampp
control panel, select Start button
behind the Apache and Start button
behind the MYSQL.
3. Select Admin button as
shown in fig.
3.
Click on User Accounts tab. Click on Add User Account button.
4.
Select
Databases tab and create a Database as ShaikDB
and click Create button.
5.
Create table Student as shown below.
6.
After selecting
Go button, Add the field name as
shown below.
7.
Give
the filed name and its values as follows
Name Type Length Attributes null Index
id INT 6
UNSIGNED AUTO_INCREMENT PRIMARY KEY
firstname VARCHAR 30 NOT NULL
firstname VARCHAR 30 NOT NULL
lastname VARCHAR 30 NOT NULL
email VARCHAR 50
email VARCHAR 50
reg_date TIMESTAMP.
After typing the
information, select save button.
8.
Select
Insert tab.
9.
Insert values
in particular fields and click on save button.
10.
Minimize the Xampp control panel and type the
following code.
Practical-9.php
<?php
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
// Create connection
$conn = new mysqli($servername, $root, $, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// sql to delete a record
$sql = "DELETE FROM Student WHERE id=3";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>
10.
Write a PHP application to fetch the Rows in a Table
1. Open Xampp control panel
(StartàAll ProgramsàXampp control panel).
2. After opening Xampp
control panel, select Start button
behind the Apache and Start button
behind the MYSQL.
3. Select Admin button as
shown in fig.
3.
Click on User Accounts tab. Click on Add User Account button.
4.
Select
Databases tab and create a Database as ShaikDB
and click Create button.
5.
Create table Student as shown below.
6.
After selecting
Go button, Add the field name as
shown below.
7.
Give
the filed name and its values as follows
Name Type Length Attributes null Index
id INT 6
UNSIGNED AUTO_INCREMENT PRIMARY KEY
firstname VARCHAR 30 NOT NULL
firstname VARCHAR 30 NOT NULL
lastname VARCHAR 30 NOT NULL
email VARCHAR 50
email VARCHAR 50
reg_date TIMESTAMP.
After typing the
information, select save button.
8.
Select
Insert tab.
9.
Insert values
in particular fields and click on save button.
10.
Minimize the Xampp control panel and type the
following code.
Practical-10.php
<?php
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
// Create connection
$conn = new mysqli($servername, $root, $, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM Student";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
$servername = "localhost";
$username = "shaik";
$password = "Ayaan2013#";
$dbname = "shaikDB";
// Create connection
$conn = new mysqli($servername, $root, $, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM Student";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
MYSQL
LAB
Q1) An Enterprise wishes to maintain the details about his
suppliers and other corresponding details. For that he uses the following
details.
Suppliers (sid: Integer, sname: string, address:
string)
Parts (pid: Integer, pname: string, color:
string)
Catalog (sid: integer, pid: integer, cost: real)
The catalog relation lists the prices charged for parts by
suppliers. Write the following queries in SQL:
1. Find
the pnames of parts for which there is some supplier.
2. Find
the snames of suppliers who supply every part.
3. Find
the snames of supplier who supply every red part.
4. Find the pnames of parts
supplied by London Supplier and by noone else.
5. Find
the sid‘s of suppliers who charge more for some part than the average cost of
that part.
6. For
each part, find the sname of the supplier who charges the most for that part.
7. Find
the sid‘s of suppliers who supply only red parts.
8. Find
the sid‘s of suppliers who supply a red and a green part.
9. Find
the sid‘s of suppliers who supply a red or green part.
10. Find
the total amount has to pay for that suppler by part located from London.
Ans:
1. Create
Database with name as “MYSQLLAB1”.
2. Now
Create Tables called Suppliers, Parts and Catalog using the following
dialogbox.
3.
After Creating tables Create Primary Keys for
the tables.
4.
Select Designer under More Option, to create
relationship between tables
5.
Insert the values/records in the tables with the
following data.
6. Now
select SQL tab and execute the following commands.
1. Find
the pnames of parts for which there is some supplier.
SELECT DISTINCT P.pname
FROM Parts P, Catalog C WHERE P.pid = C.pid
2. Find
the snames of suppliers who supply every part.
SELECT S.sname FROM Suppliers S WHERE NOT EXISTS(( SELECT P.pid FROM Parts P )
EXCEPT ( SELECT C.pid FROM Catalog C WHERE C.sid = S.sid ))
3. Find
the snames of supplier who supply every red part.
SELECT S.sname FROM Suppliers S WHERE NOT EXISTS (( SELECT P.pid FROM Parts P
WHERE P.color = ‘Red’ ) EXCEPT ( SELECT C.pid FROM Catalog C, Parts P WHERE
C.sid
= S.sid AND C.pid
= P.pid AND P.color
= ‘Red’ ))
4. Find the pnames of parts
supplied by London Supplier and by noone else.
SELECT P.pname FROM Parts P, Catalog C,
Suppliers S WHERE P.pid
= C.pid AND C.sid
= S.sid
AND S.sname = ‘Acme Widget
Suppliers’ AND NOT EXISTS ( SELECT * FROM Catalog C1, Suppliers S1 WHERE P.pid = C1.pid AND C1.sid = S1.sid AND S1.sname <> ‘Acme Widget Suppliers’ )
5. Find
the sid‘s of suppliers who charge more for some part than the average cost of
that part.
SELECT DISTINCT C.sid
FROM Catalog C WHERE C.cost > ( SELECT AVG (C1.cost) FROM
Catalog
C1 WHERE C1.pid
= C.pid )
6. For
each part, find the sname of the supplier who charges the most for that part.
SELECT P.pid, S.sname FROM Parts P, Suppliers S,
Catalog C WHERE C.pid
= P.pid
AND C.sid = S.sid AND C.cost = ( SELECT MAX (C1.cost) FROM Catalog C1
WHERE C1.pid = P.pid)
7. Find
the sid‘s of suppliers who supply only red parts.
SELECT DISTINCT C.sid
FROM Catalog C WHERE NOT EXISTS ( SELECT * FROM
Parts
P WHERE P.pid
= C.pid AND P.color
<> ‘Red’ )
8. Find
the sid‘s of suppliers who supply a red and a green part.
SELECT DISTINCT C.sid
FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = ‘Red’
INTERSECT
SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1 WHERE C1.pid = P1.pid AND P1.color = ‘Green’
9. Find
the sid‘s of suppliers who supply a red or green part.
SELECT DISTINCT C.sid
FROM Catalog C, Parts P WHERE C.pid = P.pid
AND P.color = ‘Red’ UNION SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘Green’
10. Find
the total amount has to pay for that suppler by part located from London.
SELECT S.sname, COUNT(*) as
PartCount FROM Suppliers
S, Parts P, Catalog C WHERE
P.pid
= C.pid AND C.sid
= S.sid GROUP BY S.sname,
S.sid HAVING EVERY (P.color=’Green’)
Q2) An organisation wishes to maintain the status about the
working hours made by his employees. For that he uses the following tables.
Emp (eid: integer, ename: string, age: integer,
salary: real)
Works (eid: integer, did: integer, pct_time:
integer)
Dept (did: integer, budget: real, managerid:
integer)
An employee can work in more than one department; the
pct_time field of the works relation shows the percentage of time that a given
employee works in a given department. Resolve the following queries.
1. Print
the names and ages of each employee who works in both Hardware and Software
departments.
SELECT E.ename, E.age FROM Emp E, Works W1, Works W2,
Dept D1, Dept D2
WHERE E.eid = W1.eid AND W1.did = D1.did AND D1.dname = ‘Hardware’
AND E.eid = W2.eid AND W2.did = D2.did AND D2.dname = ‘Software’
2. For
each department with more than 20 full time equivalent employees (i.e., where
the part-time and full-time employees add up to at least that many full-time
employees), print the did‘s together with the number of employees that work in
that department.
SELECT W.did, COUNT (W.eid) FROM Works W GROUP BY W.did HAVING 2000 < ( SELECT SUM (W1.pct time) FROM Works W1 WHERE W1.did = W.did )
3. Print
the name of each employee whose salary exceeds the budget of all of the
departments that he or she work in.
SELECT E. ename FROM Emp E WHERE E.salary > ALL ( SELECT D.budget FROM
Dept
D, Works W WHERE E.eid = W.eid AND D.did = W.did)
4. Find
the managerid‘s of managers who manage only departments with budgets greater
than 1,000,000.
SELECT DISTINCT D.managerid FROM Dept D WHERE 1000000 < ALL (
SELECT
D2.budget FROM Dept D2 WHERE
D2.managerid = D.managerid )
5. Find
the enames of managers who manage the departments with largest budget.
SELECT E.ename FROM Emp E WHERE E.eid IN ( SELECT D.managerid FROM
Dept
D WHERE D.budget = ( SELECT MAX (D2.budget) FROM Dept D2))
6. If a
manager manages more than one department, he or she controls the sum of all the
budgets for those departments. Find the managerid‘s of managers who control
more than 5,000,000.
SELECT D.managerid FROM Dept D WHERE 5000000 < ( SELECT SUM (D2.budget) FROM
Dept
D2 WHERE D2.managerid
= D.managerid )
7. Find
the managerid‘s of managers who control the highest amount.
SELECT DISTINCT tempD.managerid
FROM ( SELECT DISTINCT D.managerid,
SUM
(D.budget)
AS tempBudget FROM Dept D GROUP BY D.managerid ) AS tempD WHERE
tempD.tempBudget
= (SELECT MAX (tempD.tempBudget)
FROM tempD)
No comments:
Post a Comment