-- create
CREATE TABLE book(isbn int PRIMARY KEY,
                 title varchar (20),
                 publication varchar (20)
                 );

INSERT INTO book VALUES (01,'Taming Flame','Pearson');
INSERT INTO book values (02,'Mocking bird','Tata Mcgrew');
INSERT INTO book VALUES (08,'Milestones','Tata Mcgrew');
INSERT INTO book VALUES (13,'Vow of thieves','Pearson');
INSERT INTO book VALUES (15,'Belief','Macmillan');


                               
CREATE TABLE author (aname varchar (20));
                     
INSERT INTO author VALUES ('Therisa');
INSERT INTO author VALUES ('Ved');
INSERT INTO author VALUES ('Jhonson');
INSERT INTO author VALUES ('Neil');
INSERT INTO author VALUES ('Jhonny');


CREATE TABLE wrote (isbn int,
                    aname varchar (20));
                    
INSERT INTO wrote VALUES (01,'Therisa');
INSERT INTO wrote VALUES (02,'Ved');
INSERT INTO wrote VALUES (08,'Jhonson');
INSERT INTO wrote VALUES (13,'Naveen');
INSERT INTO wrote VALUES (15,'Jhonny');

a>Find the title published by "Pearson"?                            
SELECT title FROM book
WHERE publication = 'Pearson';

Output
title
Taming Flame
Vow of thieves

b>Find all authors of book with isbn = 13?
SELECT aname FROM wrote
WHERE isbn = 13;

Output
aname
Naveen

c>Find all the authors who published at least one book with "Tata Mcgrew"?
SELECT DISTINCT aname FROM book b, wrote w
WHERE b.isbn = w.isbn
AND publication = 'Tata Mcgrew';

Output
aname
Ved
Jhonson

d>Find all authors who never published a book with "Tata Mcgrew"?
SELECT aname FROM book b, wrote w
WHERE b.isbn = w.isbn
AND publication != 'Tata Mcgrew';

Output
aname
Therisa
Naveen
Jhonny
 
e>Find the author name whose name start from J and name having 6 characters long?
SELECT aname FROM author
WHERE aname LIKE 'J_____'
And length(aname) = 6;


f>Find the name of author  whose name start with N and name contain v in third position
SELECT aname FROM author
WHERE aname LIKE 'N_v%';