Important Questions: Unit 4 – PHP Database Connectivity

Short Answer Questions (2-5 Marks)

1. What is a Database? Differentiate between DBMS and RDBMS.
2. What is MySQL? Why is it used with PHP?
3. What is a Primary Key? How is it different from a Foreign Key?
4. Differentiate between MySQLi and PDO with at least four points.
5. What is SQL? Name the four categories of SQL commands (DDL, DML, DCL, TCL) with one example each.
6. What are Table Constraints? Explain NOT NULL, PRIMARY KEY, UNIQUE, and DEFAULT.
7. What are Aggregate Functions in MySQL? Name five with examples.
8. What is the difference between WHERE and HAVING clauses?
9. Explain INNER JOIN, LEFT JOIN, and RIGHT JOIN with syntax.
10. What is a Prepared Statement? Why is it used?

Long Answer Questions (10-15 Marks)

1. Explain how to connect PHP to a MySQL database using MySQLi (Object-Oriented style). Include: creating a connection, checking for errors, executing a query, and closing the connection. Provide complete code.
2. Explain CRUD operations (INSERT, UPDATE, DELETE, SELECT) in PHP with MySQLi. Write complete code examples for each operation on a students table.
3. Explain SQL data types in MySQL. Cover numeric types (INT, DECIMAL), string types (VARCHAR, TEXT, CHAR), and date/time types (DATE, DATETIME, TIMESTAMP). When should DECIMAL be used instead of FLOAT for money values?
4. Describe the process of creating a database and table in MySQL via PHP. Include: CREATE DATABASE, CREATE TABLE with appropriate data types and constraints (NOT NULL, AUTO_INCREMENT, UNIQUE, DEFAULT, FOREIGN KEY). Explain ALTER TABLE.
5. Explain SQL JOINs in detail. Define INNER JOIN, LEFT JOIN, and RIGHT JOIN with examples. Write a PHP script that executes a JOIN query and displays the results in an HTML table.
6. What is SQL Injection? Explain how Prepared Statements prevent SQL Injection in MySQLi. Show a vulnerable code example and its safe equivalent using prepare(), bind_param(), and execute().
7. Explain GROUP BY and HAVING clauses in MySQL with examples. Write a PHP script that uses GROUP BY to display the number of students and average marks per course, filtering to show only courses with an average above 60.
8. Explain the ACID properties of a database transaction with real-world examples for each property.

Programming Questions

1. Write a PHP script to connect to MySQL and insert a student record into a students table using a prepared statement.
2. Write a PHP script to display all students from a database in an HTML table, sorted by marks in descending order.
3. Write a PHP script to update a student's marks by their ID using a prepared statement.
4. Write a PHP script to delete a student record by ID and display the number of affected rows.
5. Write a PHP script that uses GROUP BY and COUNT(*) to display how many students are enrolled in each course.
6. Write a PHP script to execute an INNER JOIN between a students table and a departments table and display the results.
7. Write a PHP script using PDO to connect to a MySQL database with try-catch error handling.
8. Write a PHP script to search students by course name using a prepared SELECT statement with user input from a GET parameter.