Consider the table actor which already exists in our database, with 200 rows.

Upon executing SHOW CREATE TABLE `actor`, we get the following:

CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(10) NOT NULL, `last_name` varchar(10) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

You will need this table for any questions below which refer to an `actor` table.

1.

Using the actor table described before, write a query to find out how many last_name start with each letter of the English alphabet. In the output, we want the number of actors whose name start with that letter, and the letter. You may omit those letters for which we don't have any last_name.

The function SUBSTRING might be useful to you. It's syntax is:

SUBSTRING(string,position,length)

It return a substring length characters long from string string, starting at position position.

Example:

substring('databases',1,4) will give you 'data'.

2.

From the actor table referred above, we want to find out the duplicate values of first_name among the actors, along with the count of how many actors share that first_name. We want only those records which were updated since the beginning of 2017.

The query that a student came up with is:

SELECT first_name, count(actor_id) FROM actor GROUP BY first_name HAVING count(actor_id) < 1 WHERE YEAR(last_update) >= 2006 AND MONTH(last_update) >= 1 AND DAY(last_update) >= 1

Will this query get us the results that we want?

If not, then what would be the correct query (provide the complete version)?