sqlplus using of sub query

Question

I have 3 tables, and have to find out the applicant number, name and total number of position each applicant applied for.

CREATE TABLE    APP (
appNum      varchar2(10)    not null,
appName     varchar2(70),
constraint applicant_Pkey primary key (appNum)
);

CREATE TABLE    POS (
posNum          varchar2(10)    not null,
posStartOfferDt     date        not null,
constraint pos_Pkey primary key (posNum, posStartOfferDt)
);



CREATE TABLE    APPLICATION (
appcnPosNum     varchar2(10)    not null,
appcnPosStOffrDt    date        not null,
appcnAppNum     varchar2(10)    not null,
appcnDt         date,
constraint application_Pkey primary key (appcnPosNum, appcnPosStOffrDt, appcnAppNum),
constraint application_Fkey1 foreign key (appcnPosNum, appcnPosStOffrDt) references     POSITION(posNum, posStartOfferDt),
constraint application_Fkey2 foreign key (appcnAppNum) references APPLICANT(appNum)
);

I have tried using sub query, natural join but all not working out for me.

Which sql version are you using? mySql, MSSQL/T-SQL, Oracle, etc? — Anudeep Bulla
– Anudeep Bulla, Commented Nov 1, 2013 at 15:25
SELECT a.appNum, a.appName, COUNT(ap.appcnAppNum) FROM applicant a left JOIN application ap ON a.appNum = ap.appcnAppNum GROUP BY a.appNum, a.appName — Gayathri
– Gayathri, Commented Nov 1, 2013 at 15:35

Mureinik · Accepted Answer · 2013-11-01 15:26:11Z

1

SELECT   appNum, appName, COUNT(*)
FROM     applicant
JOIN     application ON applicant.appNum = application.appcnAppNum
GROUP BY appNum, appName

answered Nov 1, 2013 at 15:26

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlplus using of sub query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related