Q61
Q61 What is a stored procedure in MySQL?
A collection of SQL queries executed as a single query
A user-defined function that stores data
A method to backup databases
A protocol for MySQL communication
Q62
Q62 What is the primary advantage of using stored procedures?
Increased security
Reduced network traffic and higher performance
Automatic data backup
Simplified syntax
Q63
Q63 In MySQL, how can you pass a parameter to a stored procedure?
By using the IN keyword
By declaring it outside the procedure
By using the OUT keyword
By using the DECLARE keyword
Q64
Q64 Which of the following is NOT a characteristic of functions in MySQL?
Can return only one value
Can be used in SQL expressions
Can perform updates on tables
Can be called from within SQL
Q65
Q65 What is the purpose of the OUT parameter in a stored procedure?
To send data into a procedure
To return data from the procedure to the caller
To declare variable types
To specify optional parameters
Q66
Q66 How do stored procedures contribute to database security?
By restricting direct access to data
By encrypting data automatically
By logging user actions
By validating user inputs
Q67
Q67 What differentiates a deterministic function from a non-deterministic function in MySQL?
Deterministic functions return the same result any time they are called with a specific set of input values
Deterministic functions perform better
Non-deterministic functions cannot be indexed
Non-deterministic functions are faster
Q68
Q68 What does this SQL command do:
CREATE FUNCTION GetCustomerLevel(p_credit DOUBLE) RETURNS VARCHAR(20) RETURN CASE WHEN p_credit > 50000 THEN 'Platinum' WHEN p_credit > 20000 THEN 'Gold' ELSE 'Silver' END;?
Creates a function that returns a customer's level based on credit
Deletes a function
Modifies an existing function
None of the above
Q69
Q69 How do you execute a stored procedure named 'CalculateDiscount' that takes two parameters in MySQL?
EXECUTE CalculateDiscount;
CALL CalculateDiscount(100, 20);
RUN CalculateDiscount(100, 20);
LAUNCH CalculateDiscount(100, 20);
Q70
Q70 What is the impact of declaring a variable with the same name as a column in a stored procedure?
It leads to an error due to name conflict
It hides the column for the duration of the procedure
It automatically updates the column
It has no effect
Q71
Q71 Identify the error in this stored procedure creation:
CREATE PROCEDURE UpdateUser() BEGIN UPDATE Users SET age = age + 1; END;
Missing parameters for user identification
Syntax is correct
Unnecessary semicolon at END
Should use a function instead
Q72
Q72 What is wrong with this SQL command?
CREATE PROCEDURE ResetLog() DELETE FROM LogEntries;
Incorrect procedure syntax
DELETE statement should include a WHERE clause
Should be a function
Syntax is correct
Q73
Q73 What is a trigger in MySQL?
A command to start a transaction
A predefined action executed in response to an event
A scheduled database backup
A user privilege setting
Q74
Q74 When does a BEFORE INSERT trigger execute?
After the data is inserted into the table
Before the data is inserted into the table
During the data insertion
After the transaction commits
Q75
Q75 Which of the following is NOT a valid event for a trigger in MySQL?
BEFORE UPDATE
AFTER DELETE
ON SELECT
AFTER INSERT
Q76
Q76 What is the limitation of a trigger in MySQL related to transaction control statements?
Triggers cannot execute SELECT statements
Triggers cannot include transaction control statements
Triggers can rollback transactions only
Triggers can commit transactions only
Q77
Q77 What does the following SQL trigger do?
CREATE TRIGGER CheckAge BEFORE INSERT ON Employees FOR EACH ROW BEGIN IF NEW.Age < 18 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employee too young'; END IF; END;
Prevents insertion of employees under 18
Updates age of new employees
Deletes records of young employees
Calculates average age of employees
Q78
Q78 What action is performed by this SQL statement?
CREATE TRIGGER UpdateLog AFTER UPDATE ON Documents FOR EACH ROW BEGIN INSERT INTO ChangeLog (DocID, ChangedOn) VALUES (OLD.DocID, NOW()); END;
Logs changes to documents
Deletes old document records
Creates a backup of documents
None of the above
Q79
Q79 How does this trigger function?
CREATE TRIGGER SyncAudit BEFORE UPDATE ON Accounts FOR EACH ROW BEGIN UPDATE Audit SET Balance = NEW.Balance WHERE AccountID = OLD.AccountID; END;
Synchronizes balances in the Audit table when Accounts are updated
Creates a new account
Deletes old audit records
None of the above
Q80
Q80 Identify the mistake in this trigger definition:
CREATE TRIGGER AuditInsert AFTER INSERT INTO AuditRecords FOR EACH ROW BEGIN INSERT INTO AuditLog VALUES (NEW.RecordID, NOW()); END;
Syntax error in the INSERT statement
Incorrect trigger event specification
Missing END keyword
All are correct
Q81
Q81 What's wrong with this trigger?
CREATE TRIGGER ValidateCredit BEFORE INSERT ON Orders FOR EACH ROW BEGIN IF NEW.Credit > 10000 THEN SET NEW.Credit = 10000; END IF; END;
It modifies data during a BEFORE trigger which is not allowed
It should use the AFTER keyword
The IF condition is incorrectly formulated
Syntax is correct
Q82
Q82 What is a view in MySQL?
A physical table in the database
A saved SQL query that can be treated as a table
A user interface for databases
A tool for database design
Q83
Q83 Which statement is true about updatable views in MySQL?
All views are updatable
Only views created with the WITH CHECK OPTION are updatable
Views based on multiple tables cannot be updated
Views cannot be updated
Q84
Q84 How do you restrict access to specific rows of a table through a view?
Using the WITH RESTRICT clause
By setting permissions on the base table
Using a WHERE clause in the view definition
Views cannot restrict row access
Q85
Q85 What does the following SQL command do?
CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE Status = 'Active';
Creates a new table ActiveUsers with data from Users
Deletes inactive users from Users
Creates a view showing only active users from Users
Updates the status of all users in Users
Q86
Q86 What does this command do?
CREATE OR REPLACE VIEW CustomerInfo AS SELECT Name, Email FROM Customers WHERE Active = 1;
Replaces an existing view or creates a new one displaying certain customer info
Deletes the old CustomerInfo view and creates a new one
Alters the Customers table structure
None of the above
Q87
Q87 What is the purpose of the SQL command SHOW FULL TABLES WHERE Table_type = 'VIEW';?
To list all tables in the database
To display only the views in the database
To modify the type of tables to views
To delete views from the database
Q88
Q88 How does the INFORMATION_SCHEMA.VIEWS table help users?
It shows the SQL statements for all views in the database
It displays metadata about each view in the database
It changes the definitions of views
It creates new views
Q89
Q89 Identify the error in this view creation:
CREATE VIEW TotalOrders AS SELECT COUNT(*) FROM Orders;
Missing alias for COUNT(*)
Syntax is correct
Should use SUM instead of COUNT
View cannot contain aggregate functions
Q90
Q90 What's wrong with this SQL command?
CREATE VIEW CustomerContacts AS SELECT CustomerID, ContactName, ContactEmail FROM Customers WHERE ContactEmail LIKE '%@%';
The WHERE clause is invalid for a view
The LIKE operator is used incorrectly
There is no error
The view includes too many columns
