I am tyring to learn composite Primary Key and Foregin Key. Wherein I am trying to have many to many mappings. Have three tables here.
CREATE TABLE authors(
authorID INT PRIMARY KEY ,
firstName VARCHAR(20),
lastName VARCHAR(20),
gender VARCHAR(20)
);
CREATE TABLE books(
bookId INT PRIMARY KEY ,
bookTitle VARCHAR(20),
price INT,
publisher VARCHAR(20)
);
/*Junction table*/
CREATE table authors_Books(
authorId INT NOT NULL,
bookId INT NOT NULL ,
FOREIGN KEY (authorID)
REFERENCES authors(authorID),
FOREIGN KEY (bookId)
REFERENCES books(bookId)
);
The records inserted are
Authors Table:
insert into authors values (1,'Mark','Dunn', 'Male');
insert into authors values (2,'Sara','Longhorn', 'Female');
insert into authors values (3,'Jessica','Dale', 'Female');
insert into authors values (4,'Steve','Wicht', 'Male');
Books Table
insert into books values(1,'Learn SQL', 10,1);
insert into books values(2,'Learn C#', 20,1);
insert into books values(3,'Learn CSS', 15,1);
insert into books values(4,'Learn HTML', 20,0);
Authors books Table
insert into authors_Books values(1,1);
insert into authors_Books values(1,2);
insert into authors_Books values(2,1);
We can observe that the author Mark Dunn has written two books (Lean SQL and Learn C#). Also, the book Learn SQL is written by two authors Mark Dunn and Sara Longhorn. How do I write an SQL query to get the books written by Mark? I assume that we can get results without using joins right?
I have tried this query but it's not correct
select
books.bookTitle,
books.price,
books.publisher,
authors.firstName,
authors.lastName
from
books,
authors,
authors_Books
where
authors_Books.authorId=1 ;