SQL script does not return the correct table

Question

I have tables as follows:

CREATE TABLE SKILL(
sname         VARCHAR(30)     NOT NULL,
    CONSTRAINT SKILL_pkey PRIMARY KEY ( sname ) );


CREATE TABLE APPLICANT(
anumber         DECIMAL(6)      NOT NULL,
fname           VARCHAR(20)     NOT NULL,
lname       VARCHAR(30) NOT NULL,
dob             DATE        NOT NULL,
city        VARCHAR(30) NOT NULL,
state       VARCHAR(20) NOT NULL,
phone       DECIMAL(10) NOT NULL,
fax     DECIMAL(10)     ,
email       VARCHAR(50)     ,
    CONSTRAINT APPLICANT_pkey PRIMARY KEY ( anumber ) );


CREATE TABLE SPOSSESSED(
anumber         DECIMAL(6)  NOT NULL,
sname       VARCHAR(30) NOT NULL,
slevel      DECIMAL(2)  NOT NULL,
    CONSTRAINT SPOSSESSED_pkey PRIMARY KEY ( anumber, sname ), 
    CONSTRAINT SPOSSESSED_fkey1 FOREIGN KEY ( anumber )
                REFERENCES APPLICANT ( anumber )
                ON DELETE CASCADE,
    CONSTRAINT SPOSSESSED_fkey2 FOREIGN KEY ( sname )
                REFERENCES SKILL ( sname ),
    CONSTRAINT SPOSSESSED_check1 CHECK ( slevel IN 
                    ( 1,2,3,4,5,6,7,8,9,10 ) ) );

I was ordered to:

Create a relational table that contains information about the names of all skills and the largest skill level possessed by an applicant and a number of applicant who possesses a skill at the highest level. Ignore the skills not possessed by any applicant. All data must be loaded into the table by the same SQL statement that creates the table. Enforce the appropriate primary key and referential integrity constraints (if any) after data is loaded.

From what I understood, I came up with 2 separate scripts to achieve the selection first:

SELECT * FROM SKILL;

SELECT MAX(SPOSSESSED.slevel), APPLICANT.anumber
FROM APPLICANT RIGHT OUTER JOIN SPOSSESSED
ON APPLICANT.anumber = SPOSSESSED.anumber
GROUP BY anumber;

I don't know if I did the selection right or not? Can anyone help? The more I read the requirement the more I feel confused.

EDIT #1:

Sample Data SKILL:

INSERT INTO SKILL VALUES ( 'C++ programming' );
INSERT INTO SKILL VALUES ( 'C programming' );
INSERT INTO SKILL VALUES ( 'Java programming' );
INSERT INTO SKILL VALUES ( 'SQL programming' );
INSERT INTO SKILL VALUES ( 'driving' );
INSERT INTO SKILL VALUES ( 'painting' );
INSERT INTO SKILL VALUES ( 'cooking' );

APPLICANT:

INSERT INTO APPLICANT VALUES ( 000001, 'Harry', 'Potter', '1980-12-12',  'Perth', 'Western Australia', 645278453, NULL, '[email protected]' );
INSERT INTO APPLICANT VALUES ( 000002, 'Johnny', 'Walker', '1990-02-13',  'Geelong', 'Victoria', 63569784, 63569785, '[email protected]' );
INSERT INTO APPLICANT VALUES ( 000003, 'Mary', 'Poppins', '1950-01-01',  'Melbourne', 'Victoria', 62389541, NULL, NULL );
INSERT INTO APPLICANT VALUES ( 000004, 'Michael', 'Collins', '1960-05-25',  'Brisbane', 'Queensland', 63336666, NULL, '[email protected]');
INSERT INTO APPLICANT VALUES ( 000005, 'Margaret', 'Finch', '1953-12-07',  'Sydney','New South Wales', 64573489, NULL, '[email protected]');
INSERT INTO APPLICANT VALUES ( 000006, 'Claudia', 'Kowalewski', '1959-05-03',  'Hobart', 'Tasmania', 64577744, NULL, '[email protected]');
INSERT INTO APPLICANT VALUES ( 000007, 'James', 'Bond', '1960-06-01','Perth', 'Western Australia', 645278434, NULL, '[email protected]');

SPOSSESSED:

INSERT INTO SPOSSESSED VALUES ( 000001, 'Java programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000001, 'C programming', 4 );
INSERT INTO SPOSSESSED VALUES ( 000001, 'cooking', 9 );
INSERT INTO SPOSSESSED VALUES ( 000002, 'Java programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000002, 'driving', 9 );
INSERT INTO SPOSSESSED VALUES ( 000003, 'C++ programming', 10 );
INSERT INTO SPOSSESSED VALUES ( 000003, 'Java programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000003, 'painting', 5 );
INSERT INTO SPOSSESSED VALUES ( 000005, 'SQL programming', 6 );
INSERT INTO SPOSSESSED VALUES ( 000006, 'SQL programming', 8 );
INSERT INTO SPOSSESSED VALUES ( 000007, 'SQL programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000007, 'cooking', 10 );

I don't really understand what I was required to do so I ask you to see how do you interpret it.

But from my understanding, it should show a table with sname, highest slevel for that sname and anumber with the highest slevel in that sname.

This is as specific as I can go...

I just did! Can you please look through it for me? The requirements gave me a lot of confusion — Huy Anh Dinh
– Huy Anh Dinh, Commented May 10, 2018 at 4:59
udpate your desired output also that you want from this data — Ankit Agrawal
– Ankit Agrawal, Commented May 10, 2018 at 5:00
Like I said, I don't really understand the requirements, that's why I posted the question up here ... Also, in the last few sentences, I briefly went through what I think that table should have — Huy Anh Dinh
– Huy Anh Dinh, Commented May 10, 2018 at 5:03
@AnkitAgrawal: I think that is the problem the OP is describing - he doesn't think he understands the requirement. — Sloan Thrasher
– Sloan Thrasher, Commented May 10, 2018 at 5:04

Sloan Thrasher · Accepted Answer · 2018-05-10 05:28:21Z

The following query will create a list of all skills, and the applicants with the highest skill level for each skill.

SELECT
    c.`anumber`,
    a.`sname`
FROM `SPOSSESSED` a
JOIN (
    SELECT 
        `sname`
        MAX(`slevel`) as `slevel`
    FROM `SPOSSESSED`
    GROUP BY `sname`
    ) b
    ON b.`sname` = a.`sname` AND b.`slevel` = a.`slevel`
JOIN `APPLICANT` c
    ON c.`anumber` = a.`anumber`
GROUP BY a.`sname`, c.`anumber`

If instead of a list of applicants with the highest skill level for each skill, you want a count of applicants with the highest skill level for each skill, you might try this:

SELECT
    a.`sname`,
    a.`slevel`,
    count(DISTINCT c.`anumber`) as `numapplicants`
FROM `SPOSSESSED` a
JOIN (
    SELECT 
        `sname`
        MAX(`slevel`) as `slevel`
    FROM `SPOSSESSED`
    GROUP BY `sname`
    ) b
    ON b.`sname` = a.`sname` AND b.`slevel` = a.`slevel`
JOIN `APPLICANT` c
    ON c.`anumber` = a.`anumber`
GROUP BY a.`sname`, a.`slevel`

Not tested, and may have a typo.

Collectives™ on Stack Overflow

SQL script does not return the correct table

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