Question

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)?

Answer 1

Hi
1.
select count(*), SUBSTRING(last_name,1,1) from actor group by SUBSTRING(last_name,1,1);
in the above query, first we group the results based on the first letter of the last name which is what is needed in question
2.

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

this query is wrong, first the date is wrong and next its printing values with count <1 the correct one is 

SELECT first_name, count(actor_id) FROM actor
GROUP BY first_name
HAVING count(actor_id) > 1  -- this will give same first names 
WHERE YEAR(last_update) >= 2017
AND MONTH(last_update) >= 1
AND DAY(last_update) >= 1

Thumbs up if this was helpful, otherwise let me know in comments