Set - 1

Question 6 :

You wrote a search engine that should retrieve 10 results at a time, but at the same time you'd like to know how many rows there're total. How do you display that to the user?

Answer :

SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there're total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.


Question 7 :

How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

Answer :

SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)


Question 8 :

How would you select all the users, whose phone number is null?

Answer :

SELECT user_name FROM users WHERE ISNULL(user_phonenumber);


Question 9 :

What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) ?

Answer :

It's equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id


Question 10 :

How do you find out which auto increment was assigned on the last insert?

Answer :

SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don't have to specify the table name.