Internet And Web Programming
Digital Assignment –
2
PHP
Name : P Revanth Kumar
Registration Number : 21BCE5214
Creating The Table
Create table “Employee” and insert at least 10-20 records. Set Empid as primary
key.
Empid:
Attribute Description/Data Type/Constraint
EmpId Employee’s unique ID. Max. 4 characters should be numeric (Primary Key)
Name Employee’s first name. Max. 15 characters. (Not Null)
Department Max. 15 characters (Not Null) (Only HR, Testing, Development and
Accounts)
Grade Max. 3 characters (Only Grades A , B ,C allowed)
Programme : BTech (CSE – CORE) Semester : WINTER 2023-24
Course Title : INTERNET AND WEB PROGRAMMING Code : BCSE404L
Faculty : Dr. Vignesh U Slot : D2 Slot
Basic Pay Decimal point number length 10
Salary Pay Decimal point number length 10
Bonus Points Decimal point number length 5
HireDate Date on which employee was hired. Date data type.
Sol:
Ø PHP Script
Ø IN this script we will create a table named Revanth_21BCE5214 and will
insert some random regards for testing further questions
Ø How To run the PHP
Ø <?php
Ø // Define MySQL connection parameters
Ø $servername = "localhost"; // or the IP address of your MySQL server
Ø $username = "root"; // MySQL username
Ø $password = "Sai@201022"; // MySQL password
Ø $dbname = "Revanth_5214"; // Your database name
Ø
Ø // Create connection
Ø $conn = mysqli_connect($servername, $username, $password, $dbname);
Ø
Ø // Check connection
Ø if (!$conn) {
Ø die("Connection failed: " . mysqli_connect_error());
Ø }
Ø
Ø // SQL to create Employee table
Ø $sql = "CREATE TABLE IF NOT EXISTS Revanth_21BCE5214 (
Ø EmpId INT(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Ø Name VARCHAR(15) NOT NULL,
Ø Department VARCHAR(15) NOT NULL CHECK (Department IN ('HR', 'Testing',
'Development', 'Accounts')),
Ø Grade VARCHAR(3) CHECK (Grade IN ('A', 'B', 'C')),
Ø BasicPay DECIMAL(10,2),
Ø SalaryPay DECIMAL(10,2),
Ø BonusPoints DECIMAL(5,2),
Ø HireDate DATE
Ø )";
Ø
Ø if (mysqli_query($conn, $sql)) {
Ø echo "Table Employee created successfully\n";
Ø } else {
Ø echo "Error creating table: " . mysqli_error($conn) . "\n";
Ø }
Ø
Ø // Insert records into Employee table
Ø $insert_sql = "INSERT INTO Revanth_21BCE5214 (Name, Department, Grade,
BasicPay, SalaryPay, BonusPoints, HireDate) VALUES ";
Ø
Ø // Sample records (you can add more records as needed)
Ø $insert_sql .= "('John Doe', 'HR', 'A', 5000.00, 6000.00, 100.00, '2023-01-
15'), ";
Ø $insert_sql .= "('Jane Smith', 'Testing', 'B', 4500.00, 5500.00, 80.00,
'2023-02-20'), ";
Ø $insert_sql .= "('Michael Johnson', 'Development', 'C', 4800.00, 5800.00,
120.00, '2023-03-10'), ";
Ø $insert_sql .= "('Emily Brown', 'Accounts', 'A', 5200.00, 6200.00, 90.00,
'2023-04-05')";
Ø
Ø // Execute insert query
Ø if (mysqli_query($conn, $insert_sql)) {
Ø echo "Records inserted successfully\n";
Ø } else {
Ø echo "Error inserting records: " . mysqli_error($conn) . "\n";
Ø }
Ø
Ø // Close connection
Ø mysqli_close($conn);
Ø ?>
Ø
Ø We use DBeaver To view the results that we created
1Q. Display the employee details in Grade is not “B”
Sol:
Ø Php Script
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to select employee details where Grade is not "B"
$sql = "SELECT * FROM Revanth_21BCE5214 WHERE Grade <> 'B'";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {
echo "EmpId: " . $row["EmpId"] . " | Name: " . $row["Name"] . " |
Department: " . $row["Department"] . " | Grade: " . $row["Grade"] . "<br>";
}
} else {
echo "No records found";
}
// Close connection
mysqli_close($conn);
?>
Ø Output Results
2Q. List the number of employees in each department. Only include department
with more than 3 employees. (Use having clause)
Ø Php Script to retrive departments with more than 3 employees
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to list the number of employees in each department with more than 3
employees
$sql = "SELECT Department, COUNT(*) AS NumEmployees FROM Revanth_21BCE5214 GROUP BY
Department HAVING COUNT(*) > 3";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {
echo "Department: " . $row["Department"] . " | Number of Employees: " .
$row["NumEmployees"] . "<br>";
}
} else {
echo "No departments with more than 3 employees found";
}
// Close connection
mysqli_close($conn);
?>
Ø Running the Script And the results of the script
3Q. Lists the number of employees in each department and group by their
departments (Use Group by)
Ø PHP script to see all the employees grouped by department
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to list the number of employees in each department and group by department
$sql = "SELECT Department, COUNT(*) AS NumEmployees FROM Revanth_21BCE5214 GROUP BY
Department";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {
echo "Department: " . $row["Department"] . " | Number of Employees: " .
$row["NumEmployees"] . "<br>";
}
} else {
echo "No departments found";
}
// Close connection
mysqli_close($conn);
?>
Ø Running the script and the results of the script
4Q. List the distinct department names
Ø Php to retrive the dis>nct department names
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to list distinct department names
$sql = "SELECT DISTINCT Department FROM Revanth_21BCE5214";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (!$result) {
die("Error executing query: " . mysqli_error($conn));
}
// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {
echo "Department: " . $row["Department"] . "<br>";
}
} else {
echo "No departments found";
}
// Close connection
mysqli_close($conn);
?>
Ø Running and results
5Q. How many employees earn salary in the range between 30k and 40k
Ø PHP script to retrieve number of employees between 30k to
40 k
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to count employees earning salary in the range between 30k and 40k
$sql = "SELECT COUNT(*) AS NumEmployees FROM Revanth_21BCE5214 WHERE SalaryPay
BETWEEN 30000 AND 40000";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (!$result) {
die("Error executing query: " . mysqli_error($conn));
}
// Fetch the result
$row = mysqli_fetch_assoc($result);
// Output the result
echo "Number of employees earning salary in the range between 30k and 40k: " .
$row["NumEmployees"];
// Close connection
mysqli_close($conn);
?>
Ø Results of php
6Q. Find the rounded value of the bonus points. (Differentiate using CEIL,
FLOOR, TRUNC, ROUND)
Ø Php
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to find the rounded value of the bonus points using different rounding
methods
$sql = "SELECT
BonusPoints AS OriginalBonusPoints,
CEIL(BonusPoints) AS RoundedUpBonusPoints,
FLOOR(BonusPoints) AS RoundedDownBonusPoints,
TRUNCATE(BonusPoints, 0) AS TruncatedBonusPoints,
ROUND(BonusPoints) AS RoundedBonusPoints
FROM
Revanth_21BCE5214";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (!$result) {
die("Error executing query: " . mysqli_error($conn));
}
// Output the result
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {
echo "Original Bonus Points: " . $row["OriginalBonusPoints"] . " | ";
echo "CEIL: " . $row["RoundedUpBonusPoints"] . " | ";
echo "FLOOR: " . $row["RoundedDownBonusPoints"] . " | ";
echo "TRUNC: " . $row["TruncatedBonusPoints"] . " | ";
echo "ROUND: " . $row["RoundedBonusPoints"] . "<br>";
}
} else {
echo "No bonus points found";
}
// Close connection
mysqli_close($conn);
?>
Ø Runnig the php and reults
7Q. List the employee details who got the minimum bonus points
Ø PHP Script for lis>ng employee geBng minimum bonus points
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to find the employee details with the minimum bonus points
$sql = "SELECT * FROM Revanth_21BCE5214 WHERE BonusPoints = (SELECT
MIN(BonusPoints) FROM Revanth_21BCE5214)";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (!$result) {
die("Error executing query: " . mysqli_error($conn));
}
// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {
echo "EmpId: " . $row["EmpId"] . " | ";
echo "Name: " . $row["Name"] . " | ";
echo "Department: " . $row["Department"] . " | ";
echo "Grade: " . $row["Grade"] . " | ";
echo "BasicPay: " . $row["BasicPay"] . " | ";
echo "SalaryPay: " . $row["SalaryPay"] . " | ";
echo "BonusPoints: " . $row["BonusPoints"] . " | ";
echo "HireDate: " . $row["HireDate"] . "<br>";
}
} else {
echo "No employees found with the minimum bonus points";
}
// Close connection
mysqli_close($conn);
?>
Ø Running the php scripts and results
8Q. Calculate the total salary of all employees
Ø Php script
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to calculate the total salary of all employees
$sql = "SELECT SUM(SalaryPay) AS TotalSalary FROM Revanth_21BCE5214";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (!$result) {
die("Error executing query: " . mysqli_error($conn));
}
// Fetch the result
$row = mysqli_fetch_assoc($result);
// Output the total salary
echo "Total salary of all employees: " . $row["TotalSalary"];
// Close connection
mysqli_close($conn);
?>
Ø Running the php and results
9Q. List the employee details in “Testing” department.
Ø PHP scripts to List the employee details in “Testing” department.
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to list the employee details in the "Testing" department
$sql = "SELECT * FROM Revanth_21BCE5214 WHERE Department = 'Testing'";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (!$result) {
die("Error executing query: " . mysqli_error($conn));
}
// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while ($row = mysqli_fetch_assoc($result)) {
echo "EmpId: " . $row["EmpId"] . " | ";
echo "Name: " . $row["Name"] . " | ";
echo "Department: " . $row["Department"] . " | ";
echo "Grade: " . $row["Grade"] . " | ";
echo "BasicPay: " . $row["BasicPay"] . " | ";
echo "SalaryPay: " . $row["SalaryPay"] . " | ";
echo "BonusPoints: " . $row["BonusPoints"] . " | ";
echo "HireDate: " . $row["HireDate"] . "<br>";
}
} else {
echo "No employees found in the Testing department";
}
// Close connection
mysqli_close($conn);
?>
Ø Running the php script and results
10Q. Calculate the average salary of all employees in “HR” department
Ø PHP script
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "Sai@201022"; // MySQL password
$dbname = "Revanth_5214"; // Your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Query to calculate the average salary of employees in the "HR" department
$sql = "SELECT AVG(SalaryPay) AS AverageSalary FROM Revanth_21BCE5214 WHERE
Department = 'HR'";
// Execute the query
$result = mysqli_query($conn, $sql);
// Check if any rows were returned
if (!$result) {
die("Error executing query: " . mysqli_error($conn));
}
// Fetch the result
$row = mysqli_fetch_assoc($result);
// Output the average salary
echo "Average salary of employees in the HR department: " . $row["AverageSalary"];
// Close connection
mysqli_close($conn);
?>
Ø Running the scripts and results
