Workshop

The quiz and exercises are provided to help you solidify your understanding of the material covered today. Try to understand the quiz and exercise answers before continuing to tomorrow's lesson.

Quiz

1:

True or False: SELECT can only get data out of tables.

A1: False. You can use it like the “equals button” on a calculator. It can evaluate any kind of expression, including comparisons, functions, and constants.
2:

True or False: You can't have ORDER BY and GROUP BY in the same SELECT statement.

A2: False. You can have both, but GROUP BY must precede ORDER BY.
3:

True or False: A HAVING clause is processed after a WHERE clause.

A3: True. But don't put anything in a HAVING clause that you could put in a WHERE clause because it won't be optimized by the query optimizer.

Exercises

1:Write a SELECT query that extracts data (all columns) from a table of cities, ordering by city name and showing result numbers 31 through 40.
A1: You should have this: 
mysql> SELECT * FROM cities ORDER BY city_name LIMIT 30,10;

2:Write a SELECT query that extracts the city name from a table of cities, listing each unique city name only once.
A2: You should have this: 
mysql> SELECT DISTINCT name FROM cities;

3:Write a SELECT query that does a full-text search for the words “Learn some SQL” in the headline and text_body columns of a table articles in a content management system. It should return the headline of relevant articles.
A3: You should have this: 
										SELECT headline FROM articles WHERE
										MATCH (headline, text_body) AGAINST ("Learn some SQL");

For full-text searching in default mode (not Boolean), you will need an index on at least the headline and text_body columns. You can apply such an index like this:

										ALTER TABLE articles ADD FULLTEXT(headline,text_body);

4:Say why the search in Exercise 3 would be inadequate in the default full-text mode and suggest how you could improve it.
A4: Default full-text mode may be unsatisfactory on the text “Learn some SQL” because:

  • “SQL” is less than four letters in length, so it will be ignored in the search string.

  • “some” is a common English word and is in the stop-word list, so it will also be ignored.

You can improve the search by doing a full-text search in Boolean mode. For example, the following will return headlines of articles in which all words are present:

SELECT headline FROM articles WHERE
MATCH (headline, text_body) AGAINST ("+Learn +some +SQL" IN BOOLEAN MODE);

However, you will need to edit the stop-word list and remove “some” and change the ft_min_word_len variable to be less than 4.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.136.22.179