How to Return paper title and number of authors for each paper using sql

I try to return paper title and number of authors for each paper,names of authors who have at least two papers and List names of authors who have co-authored with Dr. Chen. but i don’t find any way.

Author

CREATE TABLE author(
    aid int,
    aname  VARCHAR(50),
    aemail  VARCHAR(50) NOT NULL,
    affliation VARCHAR(50) NOT NULL,
    PRIMARY KEY(aid)
);

Editor

CREATE TABLE Editor (
    eid  int,
    ename   VARCHAR(50) NOT NULL,
    eemail   VARCHAR(50) NOT NULL,
    PRIMARY KEY(eid)
);

Paper

CREATE TABLE paper (
    pid int NOT NULL,
    title VARCHAR(50) NOT NULL,
    eid int,
    submit_date date,
    status int NOT NULL,
    PRIMARY KEY (pid),
    FOREIGN KEY (eid) REFERENCES Editor(eid)
);

Paper_author

create table paper_author (
pid int,
aid int, 
primary key(pid,aid),
foreign key(pid) references paper(pid),
foreign key(aid) references author(aid)

)

paper_review

create table paper_review (
      prid int,
      pid int, 
      rid int,
      due_date date,
      receive_date date,  
      round int,
      decision int,
      rcomment varchar(200), 
      primary key(prid),
      foreign key(pid) references paper(pid),
      foreign key(rid) references reviewer(rid)
    )

Reviewer

create table Reviewer ( 
rid int, 
rname varchar(50), 
remail varchar(50),
raffiliation varchar(50),
primary key(rid)
)

Sample Inputs

Author

INSERT INTO dbo.author (aid,aname,aemail,affliation) VALUES(1,'Dr. Chen','[e-mail]','UMBC');
INSERT INTO dbo.author (aid,aname,aemail,affliation)values(2,'Susan','[e-mail]','UMBC');

INSERT INTO dbo.author (aid,aname,aemail,affliation)values(3,'Steve','[e-mail]','UMB');

INSERT INTO dbo.author (aid,aname,aemail,affliation) values(4,'Carole','[e-mail]','UMB');

Editor

insert into editor (eid,ename,eemail) values(1,'Claire','[e-mail]');    
insert into editor (eid,ename,eemail) values(2,'David','[e-mail]');

Paper

INSERT INTO paper (pid,title,eid,submit_date,status) values(1,'Comparing big data systems',2, '2020-1-10',3);
INSERT INTO paper (pid,title,eid,submit_date,status) values(2,'A novel approach of mining EHR data',2, '2020-2-10',2);
INSERT INTO paper (pid,title,eid,submit_date,status) values(3, 'A new SQL Benchmark', 1,  '2020-1-1',1);

paper_author

INSERT INTO paper_author(pid,aid) values(1,1);
INSERT INTO paper_author(pid,aid) values(1,2);
INSERT INTO paper_author(pid,aid) values(2,3);
INSERT INTO paper_author(pid,aid) values(2,4);
INSERT INTO paper_author(pid,aid) values(3,1);
INSERT INTO paper_author(pid,aid) values(3,2);

paper_review

INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(2, 1, 3, '2020-2-10', '2020-2-10',1,2,'Good paper fix a few typo');
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(1, 1, 1, '2020-2-10', '2020-2-9',1,3,'Good paper but needs to improve writing');
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(3, 1, 4, '2020-2-10', '2020-2-12',1,3,'Please add more experiments');
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(4, 2, 2, '2020-3-10',null,1,null,null);
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(5, 2, 1, '2020-3-10',null,1,null,null);
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(6, 2, 3, '2020-3-10',null,1,null,null);
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(7, 3, 4, '2020-2-1', '2020-1-15',1,2,'good paper, fix a few typo');
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(8, 3, 2, '2020-2-1', '2020-1-11',1,3,'good paper, but please add more related work');
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(9, 3, 4, '2020-3-1', '2020-2-15',2,1,'all comments addressed');
INSERT INTO paper_review   (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(10, 3, 2,'2020-3-1', '2020-2-18',2,1,'all comments addressed');

Reviewer

INSERT INTO reviewer(rid,rname,remail,raffiliation) values(1,'Ellen','[e-mail]','Johns Hopkins');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(2,'Cathy','[e-mail]','Johns Hopkins');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(3,'Grace','[e-mail]','Stanford');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(4,'Eric','[e-mail]','Stanford');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(5,'Ethan','[e-mail]','UMBC');

Answer

What I understood is you are looking or three different answers.

paper title and number of authors for each paper:

select p.Title as Paper_Title, count(pa.aid) No_Of_Authors from paper p inner join paper_author pa on p.pid=pa.pid 
group by p.title;

Output:

Names of authors who have at least two papers:

select AName from author a inner join paper_author pa on a.aid=pa.aid
group by AName
having count(*)>=2;

Output:

List names of authors who have co-authored with Dr. Chen:

select Distinct a.*  from author a inner join paper_author pa on a.aid=pa.aid
where exists (select 1 from Author aa inner join  paper_author paa on aa.aid=paa.aid 
where aname='Dr. Chen' and paa.pid=pa.pid and aa.aid<>a.aid);

Output: