To list out both attribute without mentioning one of them in one table

December 12, 2021

Below are the following codes that I have created and succesfully displayed the correct output.

CREATE TABLE SKILLS (
 skID CHAR(14) primary key,
 skName VARCHAR2(100));

INSERT INTO SKILLS VALUES ('SK1', 'Time Management');
INSERT INTO SKILLS VALUES ('SK2', 'Emotional Management');
INSERT INTO SKILLS VALUES ('SK3', 'Communication');
INSERT INTO SKILLS VALUES ('SK4', 'Relapse Prevention');

CREATE TABLE SKILLCENTRE (
  cID CHAR(14),
  skID CHAR(14),
  FOREIGN KEY (cID) REFERENCES CENTRE(cID) ON DELETE CASCADE,
  FOREIGN KEY (skID) REFERENCES SKILLS(skID) ON DELETE CASCADE);

INSERT INTO SKILLCENTRE VALUES ('CT1', 'SK1');
INSERT INTO SKILLCENTRE VALUES ('CT1', 'SK2');
INSERT INTO SKILLCENTRE VALUES ('CT1', 'SK3');
INSERT INTO SKILLCENTRE VALUES ('CT2', 'SK2');
INSERT INTO SKILLCENTRE VALUES ('CT2', 'SK3');
INSERT INTO SKILLCENTRE VALUES ('CT3', 'SK1');
INSERT INTO SKILLCENTRE VALUES ('CT3', 'SK3');
INSERT INTO SKILLCENTRE VALUES ('CT4', 'SK1');
INSERT INTO SKILLCENTRE VALUES ('CT4', 'SK2');
INSERT INTO SKILLCENTRE VALUES ('CT4', 'SK3');
INSERT INTO SKILLCENTRE VALUES ('CT5', 'SK1');
INSERT INTO SKILLCENTRE VALUES ('CT5', 'SK3');

However, how can I list both skID and skName present in SKILLCENTRE by using MINUS? I know from table SKILLS I did created a column for name but not for SKILLCENTRE. Can someone help me? Thank you.

>Solution :

Using MINUS? What good would it do? Isn’t that just a simple join?

SQL> select c.cid, s.skid, s.skname
  2  from skills s join skillcentre c on c.skid = s.skid;

CID            SKID           SKNAME
-------------- -------------- ------------------------------
CT1            SK1            Time Management
CT1            SK2            Emotional Management
CT1            SK3            Communication
CT2            SK2            Emotional Management
CT2            SK3            Communication
CT3            SK1            Time Management
CT3            SK3            Communication
CT4            SK1            Time Management
CT4            SK2            Emotional Management
CT4            SK3            Communication
CT5            SK1            Time Management
CT5            SK3            Communication

12 rows selected.

SQL>

If that’s not what you wanted, please, explain it once again and provide desired output.