sqlexercise 0.3.0

This project generates SQL assignments based on common mistakes made by learners.

sql-assignment-generation

Automated generation of SQL assignments based on misconceptions.

Misconceptions - To Do List

Misconceptions are based on Taipalus' error categorization.

SQL Errors TODO List

Syntax Errors

ID	Category	Name	Description	Implemented
1	SYN-1	Ambiguous database object	Omitting correlation names	Not planned
2	SYN-1	Ambiguous database object	Ambiguous column
3	SYN-1	Ambiguous database object	Ambiguous function	Not planned
4	SYN-2	Undefined database object	Undefined column
5	SYN-2	Undefined database object	Undefined function	Not planned
6	SYN-2	Undefined database object	Undefined parameter	Not planned
7	SYN-2	Undefined database object	Undefined object
8	SYN-2	Undefined database object	Invalid schema name
9	SYN-2	Undefined database object	Misspellings
10	SYN-2	Undefined database object	Synonyms
11	SYN-2	Undefined database object	Omitting quotes around character data
12	SYN-3	Data type mismatch	Failure to specify column name twice
13	SYN-3	Data type mismatch	Data type mismatch	Not planned
14	SYN-4	Illegal aggregate function placement	Using aggregate function outside SELECT or HAVING	Not planned
15	SYN-4	Illegal aggregate function placement	Grouping error: aggregate functions cannot be nested
16	SYN-5	Illegal or insufficient grouping	Grouping error: extraneous or omitted grouping column	Not planned
17	SYN-5	Illegal or insufficient grouping	Strange HAVING: HAVING without GROUP BY	Not planned
18	SYN-6	Common syntax error	Confusing function with function	Not planned
19	SYN-6	Common syntax error	Using WHERE twice
20	SYN-6	Common syntax error	Omitting the FROM clause	Not planned
21	SYN-6	Common syntax error	Comparison with NULL
22	SYN-6	Common syntax error	Omitting the semicolon	Not planned
23	SYN-6	Common syntax error	Date time field overflow	Not planned
24	SYN-6	Common syntax error	Duplicate clause	Not planned
25	SYN-6	Common syntax error	Using an undefined correlation name	Not planned
26	SYN-6	Common syntax error	Too many columns in subquery
27	SYN-6	Common syntax error	Confusing table names with column names	Not planned
28	SYN-6	Common syntax error	Restriction in SELECT clause (e.g., SELECT fee > 10)	Not planned
29	SYN-6	Common syntax error	Projection in WHERE clause (e.g., WHERE firstname, surname)	Not planned
30	SYN-6	Common syntax error	Confusing the order of keywords (e.g., FROM customer SELECT fee)	Not planned
31	SYN-6	Common syntax error	Confusing the logic of keywords (e.g., grouping instead of ordering)	Not planned
32	SYN-6	Common syntax error	Confusing the syntax of keywords (e.g., LIKE (‘A’, ‘B’))	Not planned
33	SYN-6	Common syntax error	Omitting commas	Not planned
34	SYN-6	Common syntax error	Curly, square or unmatched brackets	Not planned
35	SYN-6	Common syntax error	IS where not applicable
36	SYN-6	Common syntax error	Nonstandard keywords or standard keywords in wrong context	Not planned
37	SYN-6	Common syntax error	Nonstandard operators (e.g., &&, || or ==)	Not planned
38	SYN-6	Common syntax error	Additional semicolon	Not planned

Semantic Errors

ID	Category	Name	Description	Implemented
39	SEM-1	Inconsistent expression	AND instead of OR (empty result table)
40	SEM-1	Inconsistent expression	Implied, tautological or inconsistent expression
41	SEM-1	Inconsistent expression	DISTINCT in SUM or AVG
42	SEM-1	Inconsistent expression	DISTINCT that might remove important duplicates
43	SEM-1	Inconsistent expression	Wildcards without LIKE
44	SEM-1	Inconsistent expression	Incorrect wildcard: using _ instead of % or using, e.g., *
45	SEM-1	Inconsistent expression	Mixing a > 0 with IS NOT NULL or empty string with NULL
46	SEM-2	Inconsistent join	NULL in IN/ANY/ALL subquery
47	SEM-2	Inconsistent join	Join on incorrect column (matches impossible)	Not planned
48	SEM-3	Missing join	Omitting a join	Not planned
49	SEM-4	Duplicate rows	Many duplicates
50	SEM-5	Redundant column output	Constant column output
51	SEM-5	Redundant column output	Duplicate column output	Not planned

Logical Errors

ID	Category	Name	Description	Implemented
52	LOG-1	Operator error	OR instead of AND
53	LOG-1	Operator error	Extraneous NOT operator
54	LOG-1	Operator error	Missing NOT operator
55	LOG-1	Operator error	Substituting existence negation with <>
56	LOG-1	Operator error	Putting NOT in front of incorrect IN/EXISTS
57	LOG-1	Operator error	Incorrect comparison operator or incorrect value compared
58	LOG-2	Join error	Join on incorrect table
59	LOG-2	Join error	Join when join needs to be omitted
60	LOG-2	Join error	Join on incorrect column (matches possible)
61	LOG-2	Join error	Join with incorrect comparison operator	Not planned
62	LOG-2	Join error	Missing join
63	LOG-3	Nesting error	Improper nesting of expressions
64	LOG-3	Nesting error	Improper nesting of subqueries
65	LOG-4	Expression error	Extraneous quotes	Not planned
66	LOG-4	Expression error	Missing expression
67	LOG-4	Expression error	Expression on incorrect column
68	LOG-4	Expression error	Extraneous expression
69	LOG-4	Expression error	Expression in incorrect clause
70	LOG-5	Projection error	Extraneous column in SELECT
71	LOG-5	Projection error	Missing column from SELECT
72	LOG-5	Projection error	Missing DISTINCT from SELECT
73	LOG-5	Projection error	Missing AS from SELECT
74	LOG-5	Projection error	Missing column from ORDER BY clause
75	LOG-5	Projection error	Incorrect column in ORDER BY clause
76	LOG-5	Projection error	Extraneous ORDER BY clause
77	LOG-5	Projection error	Incorrect ordering of rows
78	LOG-6	Function error	DISTINCT as function parameter where not applicable
79	LOG-6	Function error	Missing DISTINCT from function parameter
80	LOG-6	Function error	Incorrect function
81	LOG-6	Function error	Incorrect column as function parameter

Complications

ID	Category	Name	Description	Implemented
82	COM	Complication	Unnecessary complication
83	COM	Complication	Unnecessary DISTINCT in SELECT clause	Not planned
84	COM	Complication	Unnecessary join
85	COM	Complication	Unused correlation name	Not planned
86	COM	Complication	Correlation names are always identical
87	COM	Complication	Unnecessarily general comparison operator	Not planned
88	COM	Complication	LIKE without wildcards
89	COM	Complication	Unnecessarily complicated SELECT in EXISTS subquery
90	COM	Complication	IN/EXISTS can be replaced by comparison	Not planned
91	COM	Complication	Unnecessary aggregate function
92	COM	Complication	Unnecessary DISTINCT in aggregate function	Not planned
93	COM	Complication	Unnecessary argument of COUNT
94	COM	Complication	Unnecessary GROUP BY in EXISTS subquery	Not planned
95	COM	Complication	GROUP BY with singleton groups
96	COM	Complication	GROUP BY with only a single group
97	COM	Complication	GROUP BY can be replaced with DISTINCT
98	COM	Complication	UNION can be replaced by OR
99	COM	Complication	Unnecessary column in ORDER BY clause
100	COM	Complication	ORDER BY in subquery
101	COM	Complication	Inefficient HAVING	Not planned
102	COM	Complication	Inefficient UNION	Not planned
103	COM	Complication	Condition in the subquery can be moved up
104	COM	Complication	Condition on left table in LEFT OUTER JOIN	Not planned
105	COM	Complication	OUTER JOIN can be replaced by INNER JOIN