You can see all indexes defined for a table using following query.
SHOW INDEX FROM table_name;
When would you use ORDER BY in DELETE statement?
When you are not deleting by row ID. Such as in DELETE FROM questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table questions.
Executing the DELETE statement throw an error about foreign key constraint failing. Why ?
It means the data that you’re trying to delete is still present in another table. Like if you have a table for colleges and a table for students, which contains the ID of the college they go to, running a DELETE on a college table will fail if the students table still contains people enrolled at that college.
To delete the offending data first, and then delete the college. Quick way would involve running SET foreign_key_checks = 0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.
What does –i-am-a-dummy flag to do when starting MySQL?
–i-am-a-dummy flag makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.
How do you find out which auto increment was assigned on the last insert ?
SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function.
Note : You don’t have to specify the table name.
How would you select all the members, whose phone number is null?
SELECT * FROM members WHERE ISNULL(phonenumber);
How would you write a query to select all players that won either 1, 5, 9 or 14 games?
SELECT player_name FROM games WHERE player_won IN (1, 5, 9, 14)