INTERNET AND WEB PROGRAMMING - DA-2

NAME: D POOJIT SAI

Reg.No:21BCE1325
PROGRAMME: BTech (CSE – CORE)
SEMESTER: WINTER 2023-24
COURSE CODE: BCSE404L
FACULTY: Dr. Vignesh U
SLOT: D2 Slot

I have done this assignment in my friends Macbook as my laptop didn’t support xampp software.But I have
created my own database and codes.
 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)

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 = "poojit@1325"; // MySQL password
➢ $dbname = "Poojit_1325"; // 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 Poojit_21BCE1325 (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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 Poojit_21BCE1325 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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 Poojit_21BCE1325 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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 Poojit_21BCE1325 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 distinct department names

<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 Poojit_21BCE1325";

// 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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 Poojit_21BCE1325 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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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
Poojit_21BCE1325";

// 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 listing employee getting minimum bonus points
<?php
// Define MySQL connection parameters
$servername = "localhost"; // or the IP address of your MySQL server
$username = "root"; // MySQL username
$password = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 Poojit_21BCE1325 WHERE BonusPoints = (SELECT MIN(BonusPoints) FROM Poojit_21BCE1325)";

// 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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 = "poojit@1325"; // MySQL password
$dbname = "Poojit_1325"; // 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 Poojit_21BCE1325 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

Hence, the given task is completed connecting a database to php.