MySQL list the values of an applicant 000002 first name, last name, and the tota
ID: 3705830 • Letter: M
Question
MySQL
list the values of an applicant 000002 first name, last name, and the total number of positions the applicant applies for. How can Implement the total number of positions the applicant applies for.
CREATE TABLE Applies(
a# NUMBER(6) NOT NULL, /* Applicant number */
p# NUMBER(8) NOT NULL, /* Position number */
appdate DATE NOT NULL, /* Application date */
CONSTRAINT Applies_pkey PRIMARY KEY ( a#, p# ),
CONSTRAINT Applies_fkey1 FOREIGN KEY ( a# )
REFERENCES Applicant ( a# )
ON DELETE CASCADE,
CONSTRAINT Applies_fkey2 FOREIGN KEY ( p# )
REFERENCES Position ( p# )
ON DELETE CASCADE
);
CREATE TABLE Applicant(
a# NUMBER(6) NOT NULL, /* Staff number */
fname VARCHAR(20) NOT NULL, /* First name */
lname VARCHAR(30) NOT NULL, /* Last name */
address VARCHAR(50) NOT NULL, /* Street, home number, etc. */
city VARCHAR(30) NOT NULL, /* City */
state VARCHAR(20) NOT NULL, /* State */
phone# NUMBER(10) NOT NULL, /* Phone number */
fax# NUMBER(10), /* Fax number */
email VARCHAR(50), /* E-mail address */
acomment VARCHAR2(1000), /* Interesting comments from interviews */
CONSTRAINT Applicant_pkey PRIMARY KEY ( a# ),
CONSTRAINT Applicant_fkey1 FOREIGN KEY ( state )
REFERENCES LState ( state )
);
Explanation / Answer
Query: list first name, last name, and the total number of positions applicant applies for
SELECT fname, lname, count(Applies.p#)
FROM Applicant, Applies
WHERE Applicant.a# = Applies.a#;
The above query compares the number of applications present in the table appiles with same refernece to the application id's present in the two tabls and different position ids in the table applies
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.