Multiple Choice questions
Working with Databases - Part 2
Here are 100 multiple-choice questions (MCQs) related to CRUD operations with MySQL and PHP, focusing on database concepts, SQL commands, and PHP scripting. Each question has four options, with the correct answer indicated.
Multiple-Choice Questions (MCQs)
- What does CRUD stand for?
a) Create, Read, Update, Delete
b) Create, Retrieve, Update, Delete
c) Create, Read, Undo, Delete
d) Create, Read, Update, Dump
Answer: a) Create, Read, Update, Delete - Which of the following SQL commands is used to insert new records into a database?
a) UPDATE
b) INSERT
c) ADD
d) MODIFY
Answer: b) INSERT - What is the purpose of the SELECT statement in SQL?
a) To insert new records
b) To delete records
c) To retrieve data from a database
d) To update existing records
Answer: c) To retrieve data from a database - Which SQL command is used to remove records from a table?
a) REMOVE
b) DELETE
c) DROP
d) TRUNCATE
Answer: b) DELETE - In PHP, which function is used to connect to a MySQL database?
a) connect_db()
b) mysql_connect()
c) mysqli_connect()
d) db_connect()
Answer: c) mysqli_connect() - What keyword is used to update records in an SQL database?
a) MODIFY
b) CHANGE
c) UPDATE
d) EDIT
Answer: c) UPDATE - Which PHP function fetches a result row as an associative array?
a) fetch_array()
b) fetch_assoc()
c) mysqli_fetch()
d) mysqli_fetch_assoc()
Answer: d) mysqli_fetch_assoc() - What is the correct syntax to create a database in MySQL?
a) CREATE DATABASE dbname;
b) CREATE TABLE dbname;
c) MAKE DATABASE dbname;
d) NEW DATABASE dbname;
Answer: a) CREATE DATABASE dbname; - Which SQL command is used to change existing records in a table?
a) CHANGE
b) EDIT
c) UPDATE
d) MODIFY
Answer: c) UPDATE - What is the purpose of the WHERE clause in an SQL statement?
a) To sort the results
b) To specify which records to retrieve or update
c) To join tables
d) To limit the number of records returned
Answer: b) To specify which records to retrieve or update - Which of the following is a valid SQL statement to delete all records from a table?
a) DELETE FROM table_name;
b) DELETE * FROM table_name;
c) REMOVE ALL FROM table_name;
d) TRUNCATE table_name;
Answer: a) DELETE FROM table_name; - What does the COUNT() function do in SQL?
a) Counts the number of columns
b) Counts the number of rows
c) Counts the total number of records
d) Counts the number of distinct values
Answer: b) Counts the number of rows - Which function is used to close a MySQL database connection in PHP?
a) close_db()
b) mysql_close()
c) mysqli_close()
d) db_disconnect()
Answer: c) mysqli_close() - In SQL, what does the GROUP BY clause do?
a) Groups records based on specified criteria
b) Deletes records
c) Updates records
d) Joins tables
Answer: a) Groups records based on specified criteria - Which SQL statement is used to update a column in a table?
a) MODIFY table_name SET column_name = value;
b) CHANGE table_name SET column_name = value;
c) UPDATE table_name SET column_name = value;
d) ALTER table_name SET column_name = value;
Answer: c) UPDATE table_name SET column_name = value; - What is the result of executing the following SQL command? SELECT * FROM students WHERE age > 18;
a) All records from students table
b) Records of students with age less than 18
c) Records of students with age greater than 18
d) Records of students with age exactly 18
Answer: c) Records of students with age greater than 18 - In PHP, which method is commonly used to prepare an SQL statement for execution?
a) prepare()
b) query()
c) execute()
d) mysqli()
Answer: a) prepare() - Which of the following statements will return the maximum age of students?
a) SELECT MAX(age) FROM students;
b) SELECT age MAX() FROM students;
c) SELECT TOP age FROM students;
d) SELECT age FROM students MAX;
Answer: a) SELECT MAX(age) FROM students; - What is the default port number for MySQL?
a) 3306
b) 8080
c) 5432
d) 1521
Answer: a) 3306 - Which PHP function retrieves the number of rows in a result set?
a) row_count()
b) num_rows()
c) mysqli_num_rows()
d) count_rows()
Answer: c) mysqli_num_rows() - What does the DISTINCT keyword do in an SQL query?
a) Returns all records
b) Returns unique records only
c) Returns duplicate records
d) Sorts the results
Answer: b) Returns unique records only - What will the following SQL query return? SELECT COUNT(*) FROM students;
a) Total number of columns
b) Total number of students
c) Total number of unique names
d) Total number of classes
Answer: b) Total number of students - Which operator is used to specify a pattern to match in SQL?
a) LIKE
b) MATCH
c) IN
d) EQUALS
Answer: a) LIKE - What is the result of this SQL query? SELECT name FROM students WHERE name LIKE 'A%';
a) Names starting with 'A'
b) Names containing 'A'
c) Names ending with 'A'
d) Names equal to 'A'
Answer: a) Names starting with 'A' - In a MySQL database, which of the following commands is used to create a new table?
a) NEW TABLE table_name;
b) CREATE TABLE table_name;
c) ADD TABLE table_name;
d) INSERT TABLE table_name;
Answer: b) CREATE TABLE table_name; - Which SQL statement is used to create a table?
a) CREATE RECORD table_name;
b) CREATE TABLE table_name;
c) NEW TABLE table_name;
d) ADD TABLE table_name;
Answer: b) CREATE TABLE table_name; - What does the following SQL command do? ALTER TABLE students ADD COLUMN email VARCHAR(50);
a) Deletes the email column
b) Modifies the email column
c) Adds a new email column
d) Renames the email column
Answer: c) Adds a new email column - In SQL, what is the purpose of the LIMIT clause?
a) To specify the maximum number of records to return
b) To specify the minimum number of records to return
c) To sort the records
d) To group records
Answer: a) To specify the maximum number of records to return - What type of join returns all records from the left table and matched records from the right table?
a) INNER JOIN
b) OUTER JOIN
c) LEFT JOIN
d) RIGHT JOIN
Answer: c) LEFT JOIN - Which of the following functions is used to execute a prepared statement in PHP?
a) execute()
b) run()
c) run_query()
d) start()
Answer: a) execute() - Which SQL command is used to change the structure of an existing table?
a) CHANGE TABLE
b) MODIFY TABLE
c) ALTER TABLE
d) UPDATE TABLE
Answer: c) ALTER TABLE - What does the INNER JOIN keyword do in SQL?
a) Combines rows from two or more tables based on a related column
b) Combines all rows from both tables
c) Returns only unmatched rows
d) Filters rows based on a condition
Answer: a) Combines rows from two or more tables based on a related column - Which PHP function is used to sanitize user inputs to prevent SQL injection?
a) htmlspecialchars()
b) sanitize_input()
c) mysqli_real_escape_string()
d) escape_input()
Answer: c) mysqli_real_escape_string() - Which SQL command is used to rename a column in an existing table?
a) RENAME COLUMN
b) ALTER TABLE ... RENAME TO
c) MODIFY COLUMN
d) ALTER TABLE ... CHANGE COLUMN
Answer: d) ALTER TABLE ... CHANGE COLUMN - What is the purpose of the HAVING clause in SQL?
a) To filter records before aggregation
b) To filter records after aggregation
c) To sort the results
d) To join tables
Answer: b) To filter records after aggregation - What does the SQL command TRUNCATE TABLE table_name; do?
a) Deletes the table structure
b) Deletes all rows in a table but keeps the structure
c) Deletes the table and all its data
d) Deletes specific rows in a table
Answer: b) Deletes all rows in a table but keeps the structure - In PHP, which function is used to check for errors during a database connection?
a) is_error()
b) error_check()
c) mysqli_connect_error()
d) connection_error()
Answer: c) mysqli_connect_error() - What type of data does VARCHAR store in a MySQL database?
a) Numeric data
b) Textual data
c) Date and time data
d) Boolean data
Answer: b) Textual data - What SQL command is used to modify existing records in a table?
a) CHANGE
b) UPDATE
c) EDIT
d) ALTER
Answer: b) UPDATE - Which function is used to select data from a MySQL database in PHP?
a) mysqli_query()
b) mysql_select()
c) fetch_data()
d) get_data()
Answer: a) mysqli_query() - Which SQL statement will select all columns from the employees table?
a) SELECT employees;
b) SELECT * FROM employees;
c) SELECT ALL FROM employees;
d) SELECT employees.*;
Answer: b) SELECT * FROM employees; - What will the following SQL query return? SELECT DISTINCT department FROM employees;
a) All departments, including duplicates
b) Unique departments only
c) All employees in a department
d) The first department only
Answer: b) Unique departments only - In MySQL, which of the following data types is used to store date values?
a) DATE
b) STRING
c) CHAR
d) TEXT
Answer: a) DATE - What does the acronym SQL stand for?
a) Structured Query Language
b) Standard Query Language
c) Simple Query Language
d) Scripted Query Language
Answer: a) Structured Query Language - Which of the following is not a type of SQL constraint?
a) PRIMARY KEY
b) UNIQUE
c) DEFAULT
d) SELECT
Answer: d) SELECT - What is the correct way to add a primary key to a table in SQL?
a) ADD PRIMARY KEY (column_name);
b) SET PRIMARY KEY (column_name);
c) CREATE PRIMARY KEY (column_name);
d) ALTER TABLE table_name ADD PRIMARY KEY (column_name);
Answer: d) ALTER TABLE table_name ADD PRIMARY KEY (column_name); - In PHP, which function is used to fetch a single row from a result set?
a) fetch_row()
b) mysqli_fetch_row()
c) fetch_one()
d) get_row()
Answer: b) mysqli_fetch_row() - Which of the following SQL statements will return all records from the orders table where the amount is greater than 100?
a) SELECT * FROM orders WHERE amount >= 100;
b) SELECT * FROM orders WHERE amount > 100;
c) SELECT * FROM orders WHERE amount = 100;
d) SELECT * FROM orders WHERE amount < 100;
Answer: b) SELECT * FROM orders WHERE amount > 100; - Which of the following statements will update the status column for records in the tasks table?
a) UPDATE tasks SET status = 'completed';
b) MODIFY tasks SET status = 'completed';
c) CHANGE tasks SET status = 'completed';
d) ALTER tasks SET status = 'completed';
Answer: a) UPDATE tasks SET status = 'completed'; - What is the result of executing this SQL command? SELECT AVG(salary) FROM employees;
a) The highest salary
b) The total salary
c) The average salary of all employees
d) The minimum salary
Answer: c) The average salary of all employees - What is the function of the SQL JOIN clause?
a) To combine rows from two or more tables
b) To delete rows
c) To create new tables
d) To modify rows
Answer: a) To combine rows from two or more tables - In MySQL, what does the AUTO_INCREMENT attribute do?
a) Automatically assigns a unique value for new records
b) Automatically updates existing records
c) Automatically deletes records
d) Automatically sorts records
Answer: a) Automatically assigns a unique value for new records - What will the following SQL query return? SELECT * FROM students ORDER BY name DESC;
a) Students ordered by name in ascending order
b) Students ordered by name in descending order
c) Students ordered by age
d) Students in random order
Answer: b) Students ordered by name in descending order - Which SQL function returns the number of unique values in a column?
a) UNIQUE()
b) COUNT()
c) DISTINCT()
d) COUNT(DISTINCT column_name)
Answer: d) COUNT(DISTINCT column_name) - Which PHP function is used to fetch all rows from a result set?
a) mysqli_fetch_all()
b) fetch_all()
c) mysqli_get_all()
d) fetch_rows()
Answer: a) mysqli_fetch_all() - What does the SQL command DROP TABLE table_name; do?
a) Deletes the table structure and all its data
b) Deletes all data in the table but keeps the structure
c) Deletes only the structure of the table
d) Deletes specific records in the table
Answer: a) Deletes the table structure and all its data - Which of the following SQL statements will create an index on a column?
a) CREATE INDEX index_name ON table_name(column_name);
b) ADD INDEX index_name ON table_name(column_name);
c) INDEX index_name ON table_name(column_name);
d) NEW INDEX index_name ON table_name(column_name);
Answer: a) CREATE INDEX index_name ON table_name(column_name); - What is the purpose of the SQL command ALTER TABLE students DROP COLUMN age;?
a) To delete the age column from the students table
b) To rename the age column
c) To modify the age column
d) To change the data type of the age column
Answer: a) To delete the age column from the students table - In PHP, which function is used to get the last inserted ID?
a) mysqli_insert_id()
b) get_last_id()
c) last_id()
d) mysqli_get_last_id()
Answer: a) mysqli_insert_id() - Which of the following SQL statements will select records from the users table where the email contains "gmail"?
a) SELECT * FROM users WHERE email = '%gmail%';
b) SELECT * FROM users WHERE email LIKE '%gmail%';
c) SELECT * FROM users WHERE email LIKE 'gmail%';
d) SELECT * FROM users WHERE email IN ('gmail');
Answer: b) SELECT * FROM users WHERE email LIKE '%gmail%'; - Which of the following SQL commands will return the lowest value in a column?
a) SELECT MIN(column_name) FROM table_name;
b) SELECT LOWEST(column_name) FROM table_name;
c) SELECT SMALL(column_name) FROM table_name;
d) SELECT MINIMUM(column_name) FROM table_name;
Answer: a) SELECT MIN(column_name) FROM table_name; - What does the following SQL statement do? CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
a) Creates a new table with an auto-incrementing primary key
b) Modifies an existing table to add a primary key
c) Deletes the students table
d) Adds a new column to the students table
Answer: a) Creates a new table with an auto-incrementing primary key - Which SQL keyword is used to prevent duplicate records in a column?
a) UNIQUE
b) DIFFERENT
c) NO_DUPLICATES
d) DISTINCT
Answer: a) UNIQUE - What does the command INSERT INTO table_name (column1, column2) VALUES (value1, value2); do?
a) Updates existing records
b) Creates a new table
c) Adds a new record to the table
d) Deletes a record from the table
Answer: c) Adds a new record to the table - In PHP, which function is used to execute a query on the database?
a) mysqli_execute()
b) execute_query()
c) mysqli_query()
d) run_query()
Answer: c) mysqli_query() - Which of the following is a valid SQL statement for creating a table?
a) CREATE TABLE table_name;
b) CREATE table_name;
c) CREATE TABLE table_name (column1 datatype, column2 datatype);
d) CREATE TABLE table_name column1 datatype, column2 datatype;
Answer: c) CREATE TABLE table_name (column1 datatype, column2 datatype); - What does the SQL command SELECT COUNT(*) FROM employees; return?
a) The total number of employees
b) The highest employee ID
c) The average number of employees
d) The total salary of all employees
Answer: a) The total number of employees - In MySQL, which clause is used to filter results based on a condition?
a) WHERE
b) ORDER BY
c) HAVING
d) GROUP BY
Answer: a) WHERE - Which SQL command is used to delete all records from a table without deleting the table itself?
a) DELETE ALL FROM table_name;
b) DELETE FROM table_name;
c) TRUNCATE table_name;
d) DROP table_name;
Answer: c) TRUNCATE table_name; - What is the purpose of the SQL LIMIT clause?
a) To filter records based on a condition
b) To specify the number of records to return
c) To sort the results
d) To group records
Answer: b) To specify the number of records to return - In PHP, how do you close a MySQL database connection?
a) mysqli_close(connection);
b) close(connection);
c) disconnect(connection);
d) end_connection(connection);
Answer: a) mysqli_close(connection); - Which SQL command will update the salary for an employee with id 1?
a) UPDATE employees SET salary = 50000 WHERE id = 1;
b) MODIFY employees SET salary = 50000 WHERE id = 1;
c) CHANGE employees SET salary = 50000 WHERE id = 1;
d) ALTER employees SET salary = 50000 WHERE id = 1;
Answer: a) UPDATE employees SET salary = 50000 WHERE id = 1; - Which function is used in PHP to connect to a MySQL database?
a) connect_to_mysql()
b) mysqli_connect()
c) db_connect()
d) mysql_connect()
Answer: b) mysqli_connect() - What is the default port number for MySQL?
a) 80
b) 8080
c) 3306
d) 5432
Answer: c) 3306 - Which SQL clause is used to group rows that have the same values in specified columns?
a) GROUP BY
b) ORDER BY
c) JOIN
d) HAVING
Answer: a) GROUP BY - What is the purpose of the SQL IN operator?
a) To check for a value within a set of values
b) To combine multiple tables
c) To filter results
d) To sort results
Answer: a) To check for a value within a set of values - In PHP, how do you escape special characters in a string for SQL queries?
a) escape_string()
b) mysqli_real_escape_string()
c) sanitize_string()
d) sql_escape()
Answer: b) mysqli_real_escape_string() - Which SQL command will add a new column to an existing table?
a) ADD COLUMN column_name TO table_name;
b) ALTER TABLE table_name ADD column_name datatype;
c) CREATE COLUMN column_name IN table_name;
d) MODIFY TABLE table_name ADD column_name datatype;
Answer: b) ALTER TABLE table_name ADD column_name datatype; - What does the SQL command SELECT * FROM table_name LIMIT 10; do?
a) Selects all records from the table
b) Selects the first 10 records from the table
c) Selects records from the table in random order
d) Selects all records except the first 10
Answer: b) Selects the first 10 records from the table - Which SQL command is used to create a foreign key relationship?
a) ADD FOREIGN KEY
b) CONSTRAINT FOREIGN KEY
c) CREATE FOREIGN KEY
d) ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);
Answer: d) ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(column_name); - What is the function of the SQL LIKE operator?
a) To search for a specified pattern in a column
b) To join two tables
c) To filter records
d) To sort results
Answer: a) To search for a specified pattern in a column - In PHP, what is the correct way to handle a database connection error?
a) if (connection_error) { echo "Error"; }
b) if (!$connection) { echo "Error"; }
c) if (mysqli_error()) { echo "Error"; }
d) if (db_error()) { echo "Error"; }
Answer: b) if (!$connection) { echo "Error"; } - What will the SQL command SELECT name FROM employees WHERE age BETWEEN 25 AND 30; return?
a) Employees older than 25
b) Employees younger than 30
c) Employees aged between 25 and 30, inclusive
d) Employees with an age not between 25 and 30
Answer: c) Employees aged between 25 and 30, inclusive - Which of the following SQL functions is used to count the total number of rows in a table?
a) TOTAL()
b) COUNT()
c) SUM()
d) ROWS()
Answer: b) COUNT() - In MySQL, which function is used to retrieve the current date and time?
a) CURRENT_DATE()
b) NOW()
c) GET_DATE()
d) TODAY()
Answer: b) NOW() - What does the SQL command ALTER TABLE table_name DROP PRIMARY KEY; do?
a) Deletes the entire table
b) Deletes the primary key constraint from the table
c) Modifies the primary key
d) Renames the primary key
Answer: b) Deletes the primary key constraint from the table - Which SQL statement will select the second highest salary from the employees table?
a) SELECT MAX(salary) FROM employees;
b) SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
c) SELECT SECOND_MAX(salary) FROM employees;
d) SELECT salary FROM employees ORDER BY salary ASC LIMIT 1;
Answer: b) SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; - What does the SQL command SET FOREIGN_KEY_CHECKS = 0; do?
a) Enables foreign key checks
b) Disables foreign key checks
c) Resets foreign key checks
d) Deletes foreign keys
Answer: b) Disables foreign key checks - Which of the following is a valid way to comment in SQL?
a) // This is a comment
b) /* This is a comment */
c) -- This is a comment
d) Both b and c
Answer: d) Both b and c - In PHP, what does the function mysqli_select_db() do?
a) Connects to a database
b) Creates a new database
c) Selects a database for the connection
d) Lists all databases
Answer: c) Selects a database for the connection - Which SQL command will remove a column from a table?
a) REMOVE COLUMN column_name FROM table_name;
b) DELETE COLUMN column_name FROM table_name;
c) DROP COLUMN column_name FROM table_name;
d) ALTER TABLE table_name DROP COLUMN column_name;
Answer: d) ALTER TABLE table_name DROP COLUMN column_name; - What is the purpose of the SQL UNION operator?
a) To combine the results of two or more SELECT statements
b) To join two tables
c) To filter results
d) To sort results
Answer: a) To combine the results of two or more SELECT statements - In MySQL, which statement is used to create an index on a table?
a) CREATE INDEX index_name ON table_name(column_name);
b) ADD INDEX index_name ON table_name(column_name);
c) INDEX table_name(column_name);
d) CREATE TABLE table_name ADD INDEX index_name;
Answer: a) CREATE INDEX index_name ON table_name(column_name); - Which SQL command is used to change the data type of a column?
a) MODIFY COLUMN column_name datatype;
b) CHANGE COLUMN column_name datatype;
c) ALTER TABLE table_name MODIFY column_name datatype;
d) ALTER TABLE table_name CHANGE column_name datatype;
Answer: c) ALTER TABLE table_name MODIFY column_name datatype; - In PHP, how do you retrieve the number of rows returned by a query?
a) mysqli_num_rows(result);
b) get_num_rows(result);
c) count_rows(result);
d) num_rows(result);
Answer: a) mysqli_num_rows(result); - What does the SQL command SELECT DISTINCT column_name FROM table_name; do?
a) Selects all records
b) Selects unique values from the specified column
c) Selects all columns
d) Selects records with duplicates
Answer: b) Selects unique values from the specified column - Which function in PHP is used to fetch a result row as an associative array?
a) mysqli_fetch_array()
b) mysqli_fetch_assoc()
c) mysqli_fetch_row()
d) mysqli_fetch_result()
Answer: b) mysqli_fetch_assoc() - What does the SQL command CREATE TABLE IF NOT EXISTS table_name; do?
a) Creates a table only if it already exists
b) Creates a table if it does not exist
c) Deletes a table if it exists
d) Modifies a table if it exists
Answer: b) Creates a table if it does not exist - Which SQL function is used to calculate the average value of a numeric column?
a) AVG()
b) MEAN()
c) AVERAGE()
d) MEDIAN()
Answer: a) AVG() - What does the SQL command DROP TABLE table_name; do?
a) Deletes the specified table and all its data
b) Deletes all records from the table but keeps the table
c) Removes all constraints from the table
d) Renames the table
Answer: a) Deletes the specified table and all its data