SUBCONSULTATIONS IN SQL?

Question

SUBCONSULTATIONS IN SQL?

Navigation

#1 by (3 votes)

1

Using the DB below, how would you return the name of the person you rented on the date '2016/11/17'. Name is in the person table and the date in the loan table.

CREATE DATABASE escola;
  USE escola;

CREATE TABLE IF NOT EXISTS acervo (
  id int(11) NOT NULL PRIMARY KEY,
  cod_barra int(15) DEFAULT NULL,
  edicao tinyint(3) DEFAULT NULL,
  categoria_id int(50) DEFAULT NULL,
  genero_id varchar(50) DEFAULT NULL,
  editora_id varchar(50) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS pessoa (
  id int(11) NOT NULL PRIMARY KEY,
  nome varchar(45) DEFAULT NULL,
  endereco varchar(45) DEFAULT NULL,
  num varchar(5) DEFAULT NULL,
  bairro varchar(20) DEFAULT NULL,
  cidade varchar(20) DEFAULT NULL,
  estado varchar(15) DEFAULT NULL,
  cpf varchar(14) DEFAULT NULL,
  aluno tinyint(1) DEFAULT NULL,
  funcionario tinyint(1) DEFAULT NULL,
  professor tinyint(1) DEFAULT NULL,
  data_nac date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 ;

create table emprestimo(
  id int auto_increment primary key,
  data_do_aluguel date,
  hora time,
  devolvido tinyint(1),
  acervo_id int,
  pessoa_id int,
  foreign key(acervo_id) references acervo(id),
  foreign key(pessoa_id) references pessoa(id)
);

mysql sql query

asked by anonymous 18.11.2016 / 13:24

1 answer

Mysqli with access denied even with correct settings How do I hide URL IDs?

score 3 · Answer 1

I would like to use INNER JOIN to find the person, but will only return the name and date:

SELECT pes.nome as pessoa,
       emp.data_do_aluguel
  FROM emprestimo emp
       INNER JOIN pessoa pes ON pes.id = emp.pessoa_id
 WHERE emp.data_do_aluguel = '2016-11-17'