November 30, 2015 at 8:54 am
CREATE TABLE JOB
(ID INTEGER PRIMARY KEY, NAME CHAR(20), SURNAME CHAR(60), SALARY REAL);
INSERT INTO JOB VALUES (44, ‘William’, ‘Simpson’, 6387.01);
INSERT INTO JOB VALUES (11, ‘John’, ‘geolo’, 3045.78);
INSERT INTO JOB VALUES (22, ‘Betran’, ‘sullivan’, 4046.79);
INSERT INTO JOB VALUES (33, ‘Paul’, ‘donn’, 13040.78);
CREATE TABLE COMISSION
(ID INTEGER REFERENCES JOB(ID), MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),
VALUE_COMISS REAL, PRIMARY KEY (ID, MONTH));
INSERT INTO COMISSION VALUES (22,1,1001.67);
INSERT INTO COMISSION VALUES (22,6,1001.67);
INSERT INTO COMISSION VALUES (44,5,2338.67);
INSERT INTO COMISSION VALUES (11,1,400.67);
INSERT INTO COMISSION VALUES (33,9,2340.00);
INSERT INTO COMISSION VALUES (44,12,2940.67);
What is the result from:
SELECT NAME FROM JOB WHERE 2340.00 < (SELECT AVG(VALUE_COMISS) FROM
COMISSION WHERE JOB.ID = COMISSION.ID);
Isn't PRIMARY KEY (ID, MONTH) in the wrong position???What does (ID, MONTH) mean??
What is JOB.ID and COMISSION.ID??
Could you explain the logic that leads to the result??If possible could you suggest a good SQL course so I can get good at it soon????
Thanks
November 30, 2015 at 9:04 am
zicado (11/30/2015)
What is the result from:SELECT NAME FROM JOB WHERE 2340.00 < (SELECT AVG(VALUE_COMISS) FROM
COMISSION WHERE JOB.ID = COMISSION.ID);
You've gone to the trouble of posting DDL and sample data - all you need to do now is run the query and your question is answered.
What does (ID, MONTH) mean??
This means that the combination of ID and MONTH uniquely identifies the rows in the table. In other words, you can't have more than one row with the same values of (ID, MONTH).
What is JOB.ID and COMISSION.ID??
This is the ID column from the JOB table and the ID column from the COMISSION table respectively.
Please don't use the real data type for sums of money - it's not a precise type. Use money instead. Also, I recommend that you don't use SQL keywords (eg MONTH) for column names.
John
November 30, 2015 at 9:20 am
I still don't understand (SELECT AVG(VALUE_COMISS) FROM
COMISSION WHERE JOB.ID = COMISSION.ID)...Could someone explain this to a noob??
November 30, 2015 at 9:31 am
It's a correlated subquery. So for each value of ID in the JOB table, it calculates the average commission for that ID in the COMISSION table. If that average is greater than 2340, the value of NAME for that ID from JOB is included in the result set.
John
November 30, 2015 at 9:32 am
It is getting the average commission for each Job ID
November 30, 2015 at 9:33 am
zicado (11/30/2015)
I still don't understand (SELECT AVG(VALUE_COMISS) FROMCOMISSION WHERE JOB.ID = COMISSION.ID)...Could someone explain this to a noob??
It's called a correlated subquery. Basically, for each ID in the JOB table it will get the average for VALUE_COMISS. Then it will compare it to 2340.00.
This condition will return only jobs which average commission is greater than 2340.
Isn't PRIMARY KEY (ID, MONTH) in the wrong position???What does (ID, MONTH) mean??
This isn't in the wrong place, it needs to be included after both columns are defined and the common practice is to put it at the end so you don't have to look for it between columns. This is called a compound key and it means that ID or MONTH can be repeated, but when combined they will give a unique value that will identify the row.
What is JOB.ID and COMISSION.ID??
JOB.ID is the Primary key for the table JOB. COMISSION.ID is a Foreign key that references the primary key in JOB.
If possible could you suggest a good SQL course so I can get good at it soon?
Try reading the stairways on T-SQL and Data in this site. http://www.sqlservercentral.com/stairway/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply