JkpNAAz8

1CREATE TABLE IF NOT EXISTS `categoria` (
2  `id` INT NOT NULL AUTO_INCREMENT,
3  `nome` VARCHAR(25) NOT NULL,
4  `descricao` VARCHAR(100) NULL,
5  PRIMARY KEY (`id`))
6ENGINE = InnoDB;
7
8CREATE TABLE IF NOT EXISTS `veiculo` (
9  `id` INT NOT NULL AUTO_INCREMENT,
10  `cat_id` INT NOT NULL,
11  `modelo` VARCHAR(100) NULL,
12  `placa` VARCHAR(7) NULL,
13  PRIMARY KEY (`id`),
14  INDEX `cat_id` (`cat_id` ASC),
15  CONSTRAINT `cat_id`
16    FOREIGN KEY (`cat_id`)
17    REFERENCES `categoria` (`id`))
18ENGINE = InnoDB;
19
20CREATE TABLE IF NOT EXISTS `locacao` (
21  `id` INT NOT NULL AUTO_INCREMENT,
22  `veiculo_id` INT NOT NULL,
23  `dt_inicio` DATETIME NOT NULL,
24  `dt_fim` DATETIME NOT NULL,
25  PRIMARY KEY (`id`),
26  INDEX `veiculo_id` (`veiculo_id` ASC),
27  CONSTRAINT `veiculo_id`
28    FOREIGN KEY (`veiculo_id`)
29    REFERENCES `veiculo` (`id`))
30ENGINE = InnoDB; 
31     
32SELECT c.nome, count(c.id) AS disponiveis
33FROM categoria AS c
34INNER JOIN veiculo AS v
35ON v.cat_id = c.id
36GROUP BY c.id; 
37     
38nome, disponiveis
39A - ECONÔMICO, 5
40B - ECONÔMICO COM AR, 4
41C - SEDAN COM AR, 2 
42     
43SELECT v.*
44  FROM veiculo v
45 WHERE NOT EXISTS(SELECT l.id
46                    FROM locacao l
47                   WHERE l.veiculo_id = v.veiculo_id
48                     AND '2016-11-22' BETWEEN l.dt_inicio AND l.dt_fim) 
49     
50SELECT c.nome,
51       (SELECT count(v.id)
52          FROM veiculo AS v
53         WHERE v.cat_id = c.id
54           AND NOT EXISTS(SELECT l.id
55                            FROM locacao AS l
56                           WHERE l.veiculo_id = v.veiculo_id
57                             AND '2016-11-22' BETWEEN l.dt_inicio AND l.dt_fim)) AS disponiveis
58  FROM categoria AS c 
59     
60SELECT c.nome,
61       count(c.id) AS disponiveis
62  FROM categoria AS c
63       LEFT JOIN veiculo AS v ON v.cat_id = c.id
64 WHERE NOT EXISTS(SELECT l.id
65                    FROM locacao l
66                   WHERE l.veiculo_id = v.veiculo_id
67                     AND '2016-11-22' BETWEEN l.dt_inicio AND l.dt_fim) 
68     
69SELECT c.nome, count(c.id) AS disponiveis
70    FROM categoria AS c
71    INNER JOIN veiculo AS v
72    ON v.cat_id = c.id
73    where exists (select 1 from locacao l
74    where suadata BETWEEN l.dt_inicio and l.dt_fim
75    and l.veiculo_id = v.id)
76    GROUP BY c.id;