SQL Query related question:

Firstly I have tables (shown below) with data already filled into them, my task is:

Find full names (first name, last name) and an average skill level of all applicants whose average skill level is below an average skill level of all skills possessed.

-

The specific way im supposed to do it is as follows:

(1) First, create a relational view that contains information about an average skill level of all skills possessed by at least one applicant. A name of relational view is up to you.

(2) Next, create a relational view that for each applicant who possesses at least one skill contains information about an applicant number and an average level of all his/her skills. Remember, that the view must contain information about the applicants who possess at least one skill. A name of relational view is up to you.

(3) Next, create a relational view that for each applicant who possesses no skills contains information about an applicant number and 0 as his/her an average level of all skills. A name of relational view is up to you.

(4) Next, create a relational view that is union of the views created in step (2) and step (3). A name of relational view is up to you.

(5) Next, create a relational view that contains information about applicant numbers whose average skill level is below an average skill level computed in step (1). The new relational view created in this step must use information included in the views created in the steps (1) and (4). A name of relational view is up to you.

(6) Finally, implement SELECT statement that uses a relational view created in a step (5) and a relational table APPLICANT to find full names (first name, last name) and an average skill level of all applicants whose average skill level is below an average skill level of all skills possessed by at least one applicant.

-

-

Any help would be greatly appreciated thanks guys

-

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

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

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE SREQUIRED( sname VARCHAR(30) NOT NULL, /* Skill name */ requires VARCHAR(30) NOT NULL, /* Skill required */ slevel DECIMAL(2) NOT NULL, /* Level required */ CONSTRAINT SREQUIRED_pkey PRIMARY KEY ( sname, requires ), CONSTRAINT SREQUIRED_fkey1 FOREIGN KEY ( sname) REFERENCES SKILL( sname ), CONSTRAINT SREQUIRED_fkey2 FOREIGN KEY ( requires ) REFERENCES SKILL( sname ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ /* Relational tables implementing classes of objects */ /* */ CREATE TABLE APPLICANT( /* Applicants */ anumber DECIMAL(6) NOT NULL, /* Applicant number */ fname VARCHAR(20) NOT NULL, /* First name */ lname VARCHAR(30) NOT NULL, /* Last name */ dob DATE NOT NULL, /* Date of birth */ city VARCHAR(30) NOT NULL, /* City */ state VARCHAR(20) NOT NULL, /* State */ phone DECIMAL(10) NOT NULL, /* Phone number */ fax DECIMAL(10) , /* Fax number */ email VARCHAR(50) , /* E-mail address */ CONSTRAINT APPLICANT_pkey PRIMARY KEY ( anumber ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE EMPLOYER( /* Employers */ ename VARCHAR(100) NOT NULL, /* Employer name */ city VARCHAR(30) NOT NULL, /* City */ state VARCHAR(20) NOT NULL, /* State */ phone DECIMAL(10) NOT NULL, /* Phone number */ fax DECIMAL(10) , /* Fax number */ email VARCHAR(50) , /* E-mail address */ web VARCHAR(50) , /* Web site address */ CONSTRAINT EMPLOYER_pkey PRIMARY KEY ( ename ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE POSITIONS( /* Advertised positions */ pnumber DECIMAL(8) NOT NULL, /* Position number */ title VARCHAR(30) NOT NULL, /* Position title */ salary DECIMAL(9,2) NOT NULL, /* Salary */ extras VARCHAR(50) , /* Extras */ bonus DECIMAL(9,2) , /* End of year bonus */ specification VARCHAR(2000) NOT NULL, /* Specification */ ename VARCHAR(100) NOT NULL, /* Employer name */ CONSTRAINT POSITION_pkey PRIMARY KEY ( pnumber ), CONSTRAINT POSITION_fkey FOREIGN KEY ( ename) REFERENCES EMPLOYER( ename ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE EMPLBY( /* Former employers */ anumber DECIMAL(6) NOT NULL, /* Applicant number */ ename VARCHAR(100) NOT NULL, /* Employer name */ fromdate DATE NOT NULL, /* Employed from */ todate DATE , /* Employed to */ CONSTRAINT EMPLBY_pkey PRIMARY KEY ( anumber, ename, fromdate ), CONSTRAINT EMPLBY_fkey1 FOREIGN KEY ( anumber ) REFERENCES APPLICANT( anumber ), CONSTRAINT EMPLBY_fkey2 FOREIGN KEY ( ename ) REFERENCES EMPLOYER( ename ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ /* Relational tables implementing associations */ /* */ CREATE TABLE SPOSSESSED( anumber DECIMAL(6) NOT NULL, /* Applicant number */ sname VARCHAR(30) NOT NULL, /* Skill name */ slevel DECIMAL(2) NOT NULL, /* Skill level */ 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 ) ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE SNEEDED( pnumber DECIMAL(8) NOT NULL, /* Position number */ sname VARCHAR(30) NOT NULL, /* Skill name */ slevel DECIMAL(2) NOT NULL, /* Skill level */ CONSTRAINT SNEEDED_pkey PRIMARY KEY ( pnumber, sname ), CONSTRAINT SNEEDED_fkey1 FOREIGN KEY ( pnumber ) REFERENCES POSITIONS ( pnumber ) ON DELETE CASCADE, CONSTRAINT SNEEDED_fkey2 FOREIGN KEY ( sname ) REFERENCES SKILL ( sname ), CONSTRAINT SNEEDED_check1 CHECK ( slevel IN ( 1,2,3,4,5,6,7,8,9,10 ) ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE APPLIES( anumber DECIMAL(6) NOT NULL, /* Applicant number */ pnumber DECIMAL(8) NOT NULL, /* Position number */ appdate DATE NOT NULL, /* Application date */ CONSTRAINT APPLIES_pkey PRIMARY KEY ( anumber, pnumber ), CONSTRAINT APPLIES_fkey1 FOREIGN KEY ( anumber ) REFERENCES APPLICANT ( anumber ) ON DELETE CASCADE, CONSTRAINT APPLIES_fkey2 FOREIGN KEY ( pnumber ) REFERENCES POSITIONS ( pnumber ) ON DELETE CASCADE);

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */