sc - Task 1

all-clients-more-than-2.sql

SELECT client.id, client.name, count(user.clientId) as total_users
from client
left join user
on (client.id = user.clientId)
group by
    client.id
having total_users > 2

all-clients-statement.sql

SELECT client.id, client.name, count(user.clientId) as total_users
from client
left join user
on (client.id = user.clientId)
group by
    client.id

insert-statements.sql

INSERT INTO `client` (`id`, `name`, `active`) VALUES
(1, 'la caixa', 1),
(2, 'bbva', 1),
(3, 'triodos', 1),
(4, 'bankia', 0),
(5, 'sabadell', 1),
(6, 'lloyds bank', 0);

INSERT INTO `user` (`id`, `name`, `active`, `password`, `clientId`) VALUES
(1, 'Loydie', 1, '55143ac3dfaa9f0395adc146cac58b3451fee1fec1b8c315d32a674381e2889e', 1),
(2, 'Othilia', 1, 'a741b5cca53050461b06ae539ba962c6b96abce21ec653acbf3eff7c6c4fe688', 1),
(3, 'Vin', 0, '8deaaadf03a18c3e94c78d73efa70019105a73546332279636f3e3e7aa6e8d45', 1),
(4, 'Igor', 1, 'fddcfd9de0af6aef13ef9cf6f4581a1c6cb2677c9e9fa9d79dc5515c45a47b97', 1),
(5, 'Farah', 1, 'fddcfd9de0af6aef13ef9cf6f4581a1c6cb2677c9e9fa9d79dc5515c45a47b97', 1),
(6, 'Chloette', 1, 'fddcfd9de0af6aef13ef9cf6f4581a1c6cb2677c9e9fa9d79dc5515c45a47b97', 2),
(7, 'Duane', 1, 'fddcfd9de0af6aef13ef9cf6f4581a1c6cb2677c9e9fa9d79dc5515c45a47b97', 2),
(8, 'Doloritas', 0, 'fddcfd9de0af6aef13ef9cf6f4581a1c6cb2677c9e9fa9d79dc5515c45a47b97', 2),
(9, 'Tudor', 1, 'fddcfd9de0af6aef13ef9cf6f4581a1c6cb2677c9e9fa9d79dc5515c45a47b97', 3);

table-statements.sql

CREATE TABLE `client` (
  `id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `active` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `client`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `client`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `password` varchar(100) NOT NULL,
  `clientId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_clientId` (`clientId`);
  
ALTER TABLE `user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
  
ALTER TABLE `user`
  ADD CONSTRAINT `fk_clientId` FOREIGN KEY (`clientId`) REFERENCES `client` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;