Assignments for Class 15: Working with Databases

Assignments for Class 15:

Working with Databases - Part 1

Here are some assignments based on the topic "Working with Databases - Part 1," including solutions and detailed explanations.

Assignment 1: Create a Database and Table

Objective: Create a database named employee_db and a table named employees with the following fields:

id (INT, Primary Key, Auto Increment)
name (VARCHAR(50))
department (VARCHAR(50))
salary (DECIMAL(10, 2))
created_at (TIMESTAMP)

Solution:

Create Database:

sql

Copy code

CREATE DATABASE employee_db;

Create Table:

sql

Copy code

USE employee_db;

CREATE TABLE employees (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

department VARCHAR(50) NOT NULL,

salary DECIMAL(10, 2) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

Explanation:

CREATE DATABASE employee_db;: This command creates a new database called employee_db.
USE employee_db;: Switches the context to the newly created database so that subsequent commands operate on it.
CREATE TABLE employees (...): This creates a new table called employees with the specified fields. The id field is set to auto-increment, meaning it will automatically generate a unique value for each new record.

Assignment 2: Insert Records into the Employees Table

Objective: Write a PHP script to insert three employee records into the employees table.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// Insert records

$sql1 = "INSERT INTO employees (name, department, salary) VALUES ('Alice Johnson', 'HR', 60000.00)";

$sql2 = "INSERT INTO employees (name, department, salary) VALUES ('Bob Smith', 'IT', 75000.00)";

$sql3 = "INSERT INTO employees (name, department, salary) VALUES ('Charlie Brown', 'Finance', 80000.00)";

if ($conn->query($sql1) === TRUE) {

echo "Record 1 created successfully ";

} else {

echo "Error: " . $sql1 . " " . $conn->error;

}

if ($conn->query($sql2) === TRUE) {

echo "Record 2 created successfully ";

} else {

echo "Error: " . $sql2 . " " . $conn->error;

}

if ($conn->query($sql3) === TRUE) {

echo "Record 3 created successfully ";

} else {

echo "Error: " . $sql3 . " " . $conn->error;

}

$conn->close();

Explanation:

This script connects to the employee_db database.
Three SQL INSERT statements are executed to add records for Alice Johnson, Bob Smith, and Charlie Brown.
After each INSERT operation, it checks if the execution was successful and displays an appropriate message.
Finally, it closes the database connection.

Assignment 3: Retrieve Records from the Employees Table

Objective: Write a PHP script to retrieve and display all records from the employees table.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// SQL query to retrieve records

$sql = "SELECT * FROM employees";

$result = $conn->query($sql);

// Check if records exist

if ($result->num_rows > 0) {

// Output data for each row

while ($row = $result->fetch_assoc()) {

echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Department: " . $row["department"] . " - Salary: " . $row["salary"] . " - Created At: " . $row["created_at"] . " ";

}

} else {

echo "0 results";

}

$conn->close();

Explanation:

This script retrieves all records from the employees table using a SELECT statement.
It checks if any records are returned and iterates through each row, displaying the values for id, name, department, salary, and created_at.
If no records exist, it prints "0 results."

Assignment 4: Update a Record

Objective: Write a PHP script to update the salary of an employee with id 2 to $80,000.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// SQL query to update salary

$sql = "UPDATE employees SET salary = 80000.00 WHERE id = 2";

if ($conn->query($sql) === TRUE) {

echo "Record updated successfully";

} else {

echo "Error updating record: " . $conn->error;

}

$conn->close();

Explanation:

This script updates the salary of the employee with id 2 to $80,000.
It checks if the update was successful and displays a message accordingly.
Finally, it closes the database connection.

Assignment 5: Delete a Record

Objective: Write a PHP script to delete the employee record with id 3.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// SQL query to delete record

$sql = "DELETE FROM employees WHERE id = 3";

if ($conn->query($sql) === TRUE) {

echo "Record deleted successfully";

} else {

echo "Error deleting record: " . $conn->error;

}

$conn->close();

Explanation:

This script deletes the employee record with id 3 from the employees table.
It checks if the deletion was successful and provides feedback accordingly.
The connection is closed after the operation.

Summary

These assignments cover the fundamental operations of creating, reading, updating, and deleting (CRUD) records in a MySQL database using PHP. Students will gain hands-on experience in managing data with databases, which is crucial for developing dynamic web applications. Encourage students to experiment with different data and queries to reinforce their learning!

4o mini

You said:

give some more

ChatGPT said:

ChatGPT

Here are additional assignments on the topic "Working with Databases - Part 1," complete with solutions and explanations.

Assignment 6: Count the Number of Employees in Each Department

Objective: Write a PHP script to count the number of employees in each department and display the results.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// SQL query to count employees in each department

$sql = "SELECT department, COUNT(*) as count FROM employees GROUP BY department";

$result = $conn->query($sql);

// Check if records exist

if ($result->num_rows > 0) {

// Output data for each department

while ($row = $result->fetch_assoc()) {

echo "Department: " . $row["department"] . " - Count: " . $row["count"] . " ";

}

} else {

echo "0 results";

}

$conn->close();

Explanation:

This script counts the number of employees in each department using the COUNT() function and groups the results by the department field.
It checks if any results are returned and displays the department name along with the count of employees.
If no records are found, it prints "0 results."

Assignment 7: Search for Employees by Name

Objective: Write a PHP script that allows users to search for employees by name (case-insensitive).

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// User input for searching (for demonstration, use a static value)

$searchName = 'Alice';

// SQL query to search for employees by name

$sql = "SELECT * FROM employees WHERE name LIKE '%$searchName%'";

$result = $conn->query($sql);

// Check if records exist

if ($result->num_rows > 0) {

// Output data for each matching employee

while ($row = $result->fetch_assoc()) {

echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Department: " . $row["department"] . " - Salary: " . $row["salary"] . " ";

}

} else {

echo "No results found for '$searchName'";

}

$conn->close();

Explanation:

This script allows users to search for employees based on a provided name using the LIKE operator.
The % wildcard is used to match any characters before or after the search term, making the search case-insensitive.
It checks if any matching records are found and displays them or shows a "No results found" message if none exist.

Assignment 8: Update Multiple Records

Objective: Write a PHP script to increase the salary of all employees in the 'IT' department by 10%.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// SQL query to update salary for IT department

$sql = "UPDATE employees SET salary = salary * 1.10 WHERE department = 'IT'";

if ($conn->query($sql) === TRUE) {

echo "Salaries updated successfully for IT department.";

} else {

echo "Error updating salaries: " . $conn->error;

}

$conn->close();

Explanation:

This script increases the salary of all employees in the 'IT' department by 10% using an UPDATE statement.
It checks if the operation was successful and provides feedback to the user.
Finally, it closes the database connection.

Assignment 9: List Employees with a Salary Above a Certain Amount

Objective: Write a PHP script that lists all employees whose salary is greater than $70,000.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// SQL query to select employees with salary > 70000

$sql = "SELECT * FROM employees WHERE salary > 70000";

$result = $conn->query($sql);

// Check if records exist

if ($result->num_rows > 0) {

// Output data for each matching employee

while ($row = $result->fetch_assoc()) {

echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Department: " . $row["department"] . " - Salary: " . $row["salary"] . " ";

}

} else {

echo "No employees found with a salary greater than $70,000.";

}

$conn->close();

Explanation:

This script retrieves all employees whose salary exceeds $70,000 using a WHERE clause in the SELECT statement.
It checks if any records meet the criteria and displays them; otherwise, it shows a message indicating that no such employees exist.

Assignment 10: Display Employee Details in a Table Format

Objective: Write a PHP script that retrieves all employee records and displays them in an HTML table.

Solution:

php

Copy code

<?php

// Database configuration

$host = 'localhost';

$username = 'root';

$password = '';

$database = 'employee_db';

// Create connection

$conn = new mysqli($host, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// SQL query to retrieve all employees

$sql = "SELECT * FROM employees";

$result = $conn->query($sql);

// Start HTML table

echo "<table border='1'>";

echo "<tr><th>ID</th><th>Name</th><th>Department</th><th>Salary</th><th>Created At</th></tr>";

// Check if records exist

if ($result->num_rows > 0) {

// Output data for each employee

while ($row = $result->fetch_assoc()) {

echo "<tr>";

echo "<td>" . $row["id"] . "</td>";

echo "<td>" . $row["name"] . "</td>";

echo "<td>" . $row["department"] . "</td>";

echo "<td>" . $row["salary"] . "</td>";

echo "<td>" . $row["created_at"] . "</td>";

echo "</tr>";

}

} else {

echo "<tr><td colspan='5'>No results found</td></tr>";

}

// End HTML table

echo "</table>";

$conn->close();

Explanation:

This script retrieves all employee records and displays them in an HTML table format.
It starts by creating an HTML table with headers for each field.
It checks if any records exist; if so, it iterates through each row and adds it to the table.
If no records are found, it displays a message within the table.
Finally, it closes the database connection.

Summary

These additional assignments reinforce the fundamental operations of working with databases and introduce students to more complex queries and output formatting. They help students practice essential database management skills, which are vital for developing dynamic applications.

Assignments for Class 15: Working with Databases - Part 1

Post a Comment

Practical Test Of MCA 2023-2025(A)

Contact form

Assignments for Class 15: Working with Databases - Part 1

You Might Like

Post a Comment

Contact form