CREATE TABLE authors (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(30)
) ENGINE=InnoDB;

CREATE TABLE books (
  isbn VARCHAR(10) NOT NULL UNIQUE,
  title VARCHAR(50) NOT NULL,
  summary VARCHAR(255),
  author_id INT UNSIGNED NOT NULL,
  CONSTRAINT FOREIGN KEY (`author_id`) REFERENCES authors(`id`)
) ENGINE=InnoDB;

`authors`
=========
id name
=========
1  mido
2  hitchcock
3  einstein
4  newton

`books`
=====================================
isbn   title                 summary            author_id
=====================================
0093  'amazing grace'  'i am a summary'  1
0012  'another title'     'summary no#2'   1
0123  'same author'     'small summary'    1
0141  'different author' 'erkjvakejrhvjk'    4

SELECT isbn,title 
FROM authors a
JOIN books b ON a.ID=b.author_id
WHERE a.Name = 'mido'

CREATE TABLE cars(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  registration VARCHAR(8) NOT NULL UNIQUE,
  make VARCHAR(20) NOT NULL,
  model VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE drivers(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  license_number VARCHAR(40) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE drivers_cars_map(
  driver_id INT UNSIGNED NOT NULL,
  car_id INT UNSIGNED NOT NULL,
  CONSTRAINT FOREIGN KEY (`driver_id`) REFERENCES drivers(`id`),
  CONSTRAINT FOREIGN KEY (`car_id`) REFERENCES cars(`id`),
  PRIMARY KEY(driver_id,car_id)
) ENGINE=InnoDB;

INSERT INTO drivers(name,license_number)
VALUES
('Brian','JHsk3923ZX'),('Garry','Jalke933ds'),
('Phil','KJERejejd23'),('James','Xhheje552X');

INSERT INTO cars(registration,make,model)
VALUES
('XJS 235','vauxhall','nova'),('AKS 23KS','vauxhall','astra'),
('ZKA 775A','ford','focus'),('J321 JJA','ford','focus'),
('FJA 421K','chrysler','proton'),('LK82 JAA','chrysler','charger');

/* Brian is allowed to driver all cars */
INSERT INTO drivers_cars_map 
SELECT d.id,c.id 
FROM drivers d,cars c 
WHERE d.Name='Brian';

/* Garry is allowed to driver all vauxhalls */
INSERT INTO drivers_car_map 
SELECT d.id,c.id 
FROM drivers d,cars c 
WHERE d.Name='Garry' AND c.Make='vauxhall';

/* Phil can drive all vauxhalls and fords */
INSERT INTO drivers_cars_map
SELECT d.id,c.id
FROM drivers d,cars c
WHERE d.Name='Phil' AND (c.Make='vauxhall' OR c.Make='ford');

/* James can drive cars with particular registation numbers */
INSERT INTO drivers_cars_map
SELECT d.id,c.id
FROM drivers d,cars c
WHERE d.Name='James' AND 
(
c.registration='XJS 235' 
OR c.registration='ZKA 775A' 
OR c.registration='LK82 JAA'
);

SELECT name FROM cars c
JOIN drivers_cars_map dcm ON dcm.car_id=c.id
JOIN drivers d ON dcm.driver_id=d.id
WHERE make='vauxhall'
GROUP BY name;

SELECT name FROM drivers d
WHERE d.id IN
(SELECT driver_id
 FROM drivers_cars_map dcm
 JOIN cars c ON dcm.car_id=c.id
 WHERE c.make='vauxhall' AND c.model='astra'
)