#import "tpl.typ": *
#import "@preview/tablex:0.0.6": tablex, vlinex, hlinex, colspanx
#show: body => apply(body)
#let semibold(body) = text(weight: 600, body)
#text(size: 1.3em, align(center)[#semibold[Practical Assignments] \ _for_ \ #semibold[RDBMS Lab] \ _using_ \ #semibold[MySQL and PHP]])
#set enum(full: true, numbering: (..args) => {
let patterns = ("1.", "(a)")
let pattern = patterns.at(calc.min(args.pos().len(), patterns.len()) - 1)
numbering(pattern, args.pos().last())
})
#set raw(lang: "php")
+ Create a table `Student` containing the fields `name`, `roll`, `city`, `email` and `date_of_birth`. Then,
- Create a form to insert records into this table (and insert at least 5 of them),
- Display all of the records,
- Display records of those students who were born in between `01/01/2000` to `31/12/2005`, and
- Take a valid E-mail address from the user, search for the student with the E-mail address and display the details of the student.
+ Design a `Login` table to take `username` and `password`, and
- Show all records of the `Student` table for an authorized user, whose username and password exists in the `Login` table.
- Allow the authenticated users to change their username and password.
+ Suppose a table `Food_Details` containing the fields `Food_items` and `Price_per_item`. Create another table `Customer_details` containing the fields `customer_name`, `total_amount_paid`, `date_of_payment`. Design a menu to select different food items and input the quantity ordered by customer. Generate a bill containing Customer name, Food items, quantity, and Total price. Also insert the record i.e. customer name, total amount paid and date of payment in `Customer_details` table. Net price should be the total price plus 15% GST of total price.
+ Suppose a table `Salary` contained the fields `EmpName`, `BasicPay`, `HRA`, `DA` and `Professional_tax`. Record should exists only for `EmpName` and `BasicPay`. Design a HTML form to take input for HRA percentage, DA percentage, and Professional tax. Now write a PHP script to generate the DA, HRA and Professional tax of every employee and store them into Salary table with respect to every employee. Now display the records.
+ Design a HTML form to take inputs from user and store the records into a table `Employee` using PHP script containing the following fields.
#align(center, tablex(
columns: 2,
auto-lines: false,
hlinex(),
vlinex(), vlinex(), vlinex(),
[*Fields*], [*Form input control*],
hlinex(),
[`Ename`], [textbox],
[`Address`], [textarea],
[`Phno`], [textbox],
[`Salary`], [textbox],
[`Category` \ (out of GEN, SC, ST, OBC)], [radio buttons],
[`Language`], [checkbox],
hlinex(),
))
(Multiple languages have to be concatenate into a string separated by commas and then store into database like `Bengali, English, Hindi`.)
+ Write a PHP script to display the employee records of the above `Employee` table order by user’s choice field. The field have to be choices through drop down menu containing all fields of the table.
+ Create a table `Sports` in MySQL database containing the fields `Players`, `TestRuns`, `ODIRuns`, `T20IRuns`. Insert and display the record with total individual runs of every player (like `TestRun + ODIRun + T20IRun`) using PHP.
+ Display the record of highest run scorer in Test or T20I or ODI according to user’s choice through a drop down menu for the table in Question 13. Drop down menu contains the fields name `TestRuns`, `ODIRuns`, `T20IRuns`.
+ Design a HTML form to take records from user for the above table `Sports`. Now write PHP script to insert, update and delete records through PHP script.
+ Design a form to take input from user and store into a table containing the fields `name`, `city`, `phno`, `email`, `password`. Write a PHP script to take input and store into the table such that Phno and mail id of every person must be unique.
+ Create a MySQL database table `Employee` with fields `ename`, `eid`, `salary`, `dept` and `dob`. Perform all database operations like select, insert, delete and update through PHP.
+ Create a table `Marks` with the fields `stdname`, `roll`, `CompScMarks`, `PhysicsMarks`, `ChemistryMarks`. Design a form to insert records into database and display records with grade using PHP script.
#align(center, tablex(
columns: (8em, 4em),
auto-lines: false,
align: center + horizon,
colspanx(2)[$ m = ""$average marks of all subjects.],
hlinex(),
vlinex(start: 1), vlinex(start: 1), vlinex(start: 1),
[*Marks*], [*Grade*],
hlinex(),
[$80 <= m <= 100$], [A],
[$60 <= m < 80$], [B],
[$40 <= m < 60$], [C],
[$0 <= m < 40$], [D],
hlinex(),
))
+ Create MySQL database table `Dept` with field `dno`, `dname` where `dno` is the primary key. Create another table `Employee` with field `eno`, `ename`, `city`, `salary`, `join_date`, `dno` where `eno` is the primary key and `dno` is a foreign key reference to the `Dept` table. Include other constraints like `NOT NULL`, `CHECK` etc. Perform all database operation like select, insert, update, and delete using PHP. (Query may include `group by`, `having`, `order by`, aggregate functions, `like` operator.)