Please read any book on basic data modeling or SQL. By definition, a table must have a key. A key is a particular subset of attributes in that table that are unique. Since an identifier exist on a nominal scale, it cannot be a numeric value; you do not get do calculations on it. A proper column name consist of <Attribute name>_< Attribute property>. I’m going to assume that the last three columns in this table declaration are counts of some kind. I also don’t see any need to make them NULL. Yes, I voted for Nels when I was in ANSI X3 H2 and I show how to use them in my books, but I really think they should be minimized in the DDL.
People who believe in a universal “id” on a table are called idiots in SQL slang :-). An identifier must identify something in particular and cannot be a general, vague Kabbalah number.
Let me try and clean up your code.
CREATE TABLE Assessments
(assessment_id CHAR(5) NOT NULL PRIMARY KEY,
experience_level INTEGER NOT NULL,
sql_stmt_cnt INTEGER NOT NULL DEFAULT 0,
algo_cnt INTEGER NOT NULL DEFAULT 0,
bug_fix_cnt INTEGER NOT NULL DEFAULT 0);
Did you know that for the past several years you been able to use a table constructor. The values clause of an insertion statement? The old row–at–a-time Is how we used to have to do it with punchcards over 50 years ago. The table constructor can be optimized.
INSERT INTO Assessment
VALUES(1, 3, 100, NULL, 50),
(2, 5, NULL, 100, 100),
(3, 1, 100, 100, 100),
(4, 5, 100, 50, NULL),
(5, 5, 100, 100, 100);
>> I need the query to return..<<
There is nothing named “XP” in the table. Max () and count () are aggregate functions in SQL so they need a parameter. Your request makes no sense. Would you like to try again?
Please post DDL and follow ANSI/ISO standards when asking for help.