Consider the following
MySQL tables :
|
CREATE TABLE `clients` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
) ENGINE = MYISAM
|
|
CREATE TABLE `calls` (
`client_from` int(10) unsigned NOT NULL,
`client_to` int(10) unsigned NOT NULL,
`duration` int(10) unsigned NOT NULL,
`cost` decimal(6,1) unsigned NOT NULL,
`date` timestamp NOT NULL default '0000-00-00
00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
|
|
The « clients »
table stores the different clients of a company providing mobile phone
services.
Each client has a
unique identifier (the « id » field) and other information (only a
name to keep it simple).
The
« calls » table stores each call between clients of the company. The
« client_from » is the id of the calling client and client_to is the
id of the callee. These ids exist in the clients table. Duration is stored in
seconds and cost in ruppees. The data field store the starting date of the
call.
- All the SQL statements must be written as single query and for a MySQL 5 database.
- Answers are not unique. There exist several manners to get same results. Please give the most optimized choice and/or the most simple way if you can. Please privilegiate solutions using EXPLICIT JOIN synthax (INNER JOIN, LEFT JOIN …)
- If you don’t know how to answer or have any doubts, please answer by explaining your misunderstanding or issues faced.
Please answer the
following questions:
- Write a SQL statement to only get the « name » of all the company’s client.
Answer: SELECT `name`
FROM `clients` ;
- Write a SQL statement adding the « Mittal » client to the « clients »’s table
Answer: INSERT INTO
`clients`(`name`) VALUES('Mittal');
- Write a SQL statement to get the name and id of the clients who called at least one time. There must be unique results. Please give two possible answers if possible.
Answer :
1.
SELECT
a.`name`, a.`id` from `clients` a JOIN `calls` b on a.`id` = b.`client_from`
group by a.`id`;
2.
SELECT
`name`, `id` from `clients` WHERE `id` IN (SELECT `client_from` FROM `calls`
GROUP BY `client_from`);
- Write a SQL statement to get the name and id of the clients who received at least one call. There must be unique results. Please give two possible answers if possible.
Answer :
1.
SELECT
a.`name`, a.`id` from `clients` a JOIN `calls` b on a.`id` = b.`client_to`
group by a.`id`;
2.
SELECT
`name`, `id` from `clients` WHERE `id` IN (SELECT `client_to` FROM `calls`
GROUP BY `client_to`);
- Write a SQL statement to get the name and id of the clients who have no received a called yet. There must be unique results.
Answer : SELECT a.`name`, a.`id` from `clients` a LEFT JOIN `calls` b on a.`id`
= b.`client_to` AND b.`client_to` IS NULL;
- Write a SQL statement to get the name and id of the clients who have not called someone yet. There must be unique results.
Answer : SELECT a.`name`, a.`id` from `clients` a LEFT JOIN `calls` b on a.`id`
= b.`client_from` AND b.`client_from` IS NULL;
- Write the most simple SQL statement to get all the calls starting from the 24/01/2009 and the 03/03/2009 (start date and end date included).
Answer: SELECT
`client_from`, `client_to`, `duration`, `cost`, `date` FROM `calls` WHERE `date`
BETWEEN '2009-01-24' AND '2009-03-03';
- Write a SQL statement to get the total cost of all calls made by the client « JOHN » with the id 42. Only this total cost must be returned and it must be named as « total_cost »
Answer: SELECT
SUM(`cost`) AS total_cost FROM `calls` WHERE `client_from` = '42';
- Write a SQL statement to get the total cost associated to each caller.
Answer: SELECT
SUM(`cost`) AS total_cost FROM `calls` GROUP BY `client_from`;
- Write a SQL statement to insert a new call in the « calls » table. This call was made by the client id 42 to the client id 56 and lasted 40 minutes. The communication cost is 1 ruppee/minute. The call began at 10pm on the 21/05/2009.
Answer: INSERT INTO
`calls`(`client_from`, `client_to`, `duration`, `cost`, `date`) VALUES('42',
'56', '40', '40', '2009-05-21 22:00:00');
- What is not optimized in the structure of the « calls » table regarding the previous statments you wrote (Q. 3, 4, 5, 6) ? Are there something else not optimized for this table ?
Answer : Not setup foreign key in calls table `client_from` and `client_to` to
`clients` table `id` column. Because the `calls` table `clients_from` and
`clients_to` based on the clients table.
0 comments:
Post a Comment