SQL Coding Standards - Revision history

Ganskop: Update standards to require underscores instead of camel case

2025-08-11T15:58:14Z

Update standards to require underscores instead of camel case

← Older revision		Revision as of 11:58, 11 August 2025
Line 6:		Line 6:
	=== Relational Notation Standards: ===		=== Relational Notation Standards: ===

	* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore<br>Examples:<br>&emsp;&emsp;<code>STUDENT(…)</code><br>&emsp;&emsp;<code>STUDENT_ADVISOR(…)</code><br><br>		* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore.<br>Examples:<br>&emsp;&emsp;<code>STUDENT(…)</code><br>&emsp;&emsp;<code>STUDENT_ADVISOR(…)</code><br><br>
	* Relation names are singular <br>Example:<br>&emsp;&emsp;<code>STUDENT(…)</code>, not <code>STUDENTS(…)</code><br><br>		* Relation names are singular.<br>Example:<br>&emsp;&emsp;<code>STUDENT(…)</code>, not <code>STUDENTS(…)</code><br><br>
	* Primary keys are underlined <br>Example:<br>&emsp;&emsp;<code>STUDENT(~~univID~~, …)</code><br><br>		* Primary keys are underlined.<br>Example:<br>&emsp;&emsp;<code>STUDENT(<u>univ_id</u>, …)</code><br><br>
	* Foreign key are italicized when typed, dash-underlined when written <br>Example:<br>&emsp;&emsp;<code>STUDENT(~~univID~~, ''~~collegeID~~'', …)</code><br><br>		* Foreign key are italicized when typed, dash-underlined when written <br>Example:<br>&emsp;&emsp;<code>STUDENT(<u>univ_id</u>, ''college_id'', …)</code><br><br>
	* Attribute names that would otherwise include a space ~~or an underscore~~ as a separator ~~are to be in camelCase ID exception: <code>ID</code> is commonly used as~~ an ~~abbreviation for ‘identifier’~~. ~~Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code>~~<br>Example:<br>&emsp;&emsp;<code>STUDENT(~~univID~~, ''~~collegeID~~'', ~~firstName~~, ~~lastName~~, ~~expectedGradYear~~, …)</code>		* Attribute names that would otherwise include a space as a separator must include an underscore instead.<br>Example:<br>&emsp;&emsp;<code>STUDENT(univ_id, ''college_id'', first_name, last_name, expected_grad_year, …)</code>

	=== Implementation (Coding) Standards: ===		=== Implementation (Coding) Standards: ===

	* One clause per line (<code>SELECT</code>, <code>FROM</code>, <code>WHERE</code>, etc.)<br>Example:		* One clause per line (<code>SELECT</code>, <code>FROM</code>, <code>WHERE</code>, etc.)<br>Example:
	SELECT ~~univID~~, ~~firstName~~, ~~lastName~~		SELECT univ_id, first_name, last_name
	FROM student		FROM student
	WHERE city = 'Rochester' AND state = 'NY';		WHERE city = 'Rochester' AND state = 'NY';
	* Keywords and data types must be in UPPERCASE <br>Example:		* Keywords and data types must be in UPPERCASE <br>Example:
	CREATE TABLE test (		CREATE TABLE test (
	~~testID~~ CHAR(9),		test_id CHAR(9),
	~~testCount~~ INT,		test_count INT,
	~~testDesc~~ VARCHAR(255),		test_desc VARCHAR(255),
	CONSTRAINT test_pk PRIMARY KEY(~~testID~~)		CONSTRAINT test_pk PRIMARY KEY(test_id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	* Table names and attribute names that would otherwise include as space ~~or an underscore~~ as a separator must be in ~~camelCase ID exception: <code>ID<~~/~~code> is commonly used as~~ an ~~abbreviation for ‘identifier’. Attributes~~ that ~~include ‘ID’ can~~ be ~~stated as <code>attrID</code>~~, ~~instead of~~ the ~~strict camelCase of <code>attrId</code>~~ <br>Example:		* Table names and attribute names that would otherwise include a space as a separator must include an underscore instead. Don't use camel case to separate words, since capital letters in identifiers cause problems with Postgres, SQLite, and Oracle (even though they're fine in MySQL/MariaDB). E.g., in Postgres, if an identifier contains a capital letter, any time that identifier is referenced, it must be written in double quotes. In Oracle, even if identifiers are created with camel case, the database reports them as all caps.<br>Example:
	SELECT ~~univID~~, ~~collegeID~~, ~~firstName~~, ~~lastName~~		SELECT univ_id, college_id, first_name, last_name
	* Single quotes for a string literal Double quotes do not work for string literals in all DBMSs <br>Example:		* Single quotes for a string literal. Double quotes do not work for string literals in all DBMSs.<br>Example:
	WHERE city = 'Rochester' AND state = 'NY'		WHERE city = 'Rochester' AND state = 'NY'
	* Double quotes for any alias that includes a space <br>Example:		* Double quotes for any alias that includes a space <br>Example:
	SELECT ~~univID~~, ~~firstName~~ "First Name", ~~lastName~~ "Last Name"		SELECT univ_id, first_name "First Name", last_name "Last Name"
	* Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number Comment options include:		* Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number. Comment options include:
	-- single line comment (a space MUST be included after --)		-- single line comment (a space MUST be included after --)
	# single line comment		# single line comment
Line 43:		Line 43:

	* Include whitespace around operators <br>Example:<br>&emsp;&emsp;<code>city = 'Rochester'</code>, not <code>city='Rochester'</code>		* Include whitespace around operators <br>Example:<br>&emsp;&emsp;<code>city = 'Rochester'</code>, not <code>city='Rochester'</code>
	* Prefix column names with a table identifier<br>Example, in table student:<br>&emsp;&emsp;<code>~~stuUnivID~~</code> <br>&emsp;&emsp;<code>~~stuFirstName~~</code> <br>&emsp;&emsp;<code>~~stuLastName~~</code> <br>&emsp;&emsp;<code>~~stuMajID~~</code>		* Prefix column names with a table identifier<br>Example, in table student:<br>&emsp;&emsp;<code>stu_univ_id</code> <br>&emsp;&emsp;<code>stu_first_name</code> <br>&emsp;&emsp;<code>stu_last_name</code> <br>&emsp;&emsp;<code>stu_maj_id</code>
	* Indent each hierarchical level <br>Example:		* Indent each hierarchical level <br>Example:
	SELECT ~~stuUnivID~~, ~~stuFirstName~~, ~~stuLastName~~, ~~majName~~		SELECT stu_univ_id, stu_first_name, stu_last_name, stu_maj_id
	FROM student		FROM student
	JOIN major		JOIN major
	ON ~~stuMajorID~~ = ~~majID~~		ON stu_maj_id = maj_id
	AND ~~majActive~~ = TRUE		AND maj_active = TRUE
	WHERE ~~stuCity~~ = 'Rochester'		WHERE stu_city = 'Rochester'
	AND ~~stuState~~ = 'NY'		AND stu_state = 'NY'
	AND ~~stuYearLvl~~ >= 3;		AND stu_year_lvl >= 3;

Ganskop: /* Relational Notation Standards: */

2025-01-14T19:06:03Z

Relational Notation Standards:

← Older revision		Revision as of 15:06, 14 January 2025
Line 6:		Line 6:
	=== Relational Notation Standards: ===		=== Relational Notation Standards: ===

	* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore<br>Examples:<br>&emsp;&emsp;<code>STUDENT(…)</code><br>&emsp;&emsp;<code>STUDENT_ADVISOR(…)</code>		* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore<br>Examples:<br>&emsp;&emsp;<code>STUDENT(…)</code><br>&emsp;&emsp;<code>STUDENT_ADVISOR(…)</code><br><br>
	* Relation names are singular <br>Example:<br>&emsp;&emsp;<code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>		* Relation names are singular <br>Example:<br>&emsp;&emsp;<code>STUDENT(…)</code>, not <code>STUDENTS(…)</code><br><br>
	* Primary keys are underlined <br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, …)</code>		* Primary keys are underlined <br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, …)</code><br><br>
	* Foreign key are italicized when typed, dash-underlined when written <br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, ''collegeID'', …)</code>		* Foreign key are italicized when typed, dash-underlined when written <br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, ''collegeID'', …)</code><br><br>
	* Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code><br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, ''collegeID'', firstName, lastName, expectedGradYear, …)</code>		* Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code><br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, ''collegeID'', firstName, lastName, expectedGradYear, …)</code>

Ganskop at 15:45, 14 January 2025

2025-01-14T15:45:56Z

← Older revision		Revision as of 11:45, 14 January 2025
Line 6:		Line 6:
	=== Relational Notation Standards: ===		=== Relational Notation Standards: ===

	* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore<br>		* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore<br>Examples:<br>&emsp;&emsp;<code>STUDENT(…)</code><br>&emsp;&emsp;<code>STUDENT_ADVISOR(…)</code>
	Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>		* Relation names are singular <br>Example:<br>&emsp;&emsp;<code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>
	* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>		* Primary keys are underlined <br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, …)</code>
	* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>		* Foreign key are italicized when typed, dash-underlined when written <br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, ''collegeID'', …)</code>
	* Foreign key are italicized when typed, dash-underlined when written Example: <code>STUDENT(univID, ''collegeID'', …)</code>		* Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code><br>Example:<br>&emsp;&emsp;<code>STUDENT(univID, ''collegeID'', firstName, lastName, expectedGradYear, …)</code>
	* Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example: <code>STUDENT(univID, ''collegeID'', firstName, lastName, expectedGradYear, …)</code>

	=== Implementation (Coding) Standards: ===		=== Implementation (Coding) Standards: ===

	* One clause per line (<code>SELECT</code>, <code>FROM</code>, <code>WHERE</code>, etc.) Example:		* One clause per line (<code>SELECT</code>, <code>FROM</code>, <code>WHERE</code>, etc.)<br>Example:
	SELECT univID, firstName, lastName		SELECT univID, firstName, lastName
	FROM student		FROM student
	WHERE city = 'Rochester' AND state = 'NY';		WHERE city = 'Rochester' AND state = 'NY';
	* Keywords and data types must be in UPPERCASE Example:		* Keywords and data types must be in UPPERCASE <br>Example:
	CREATE TABLE test (		CREATE TABLE test (
	testID CHAR(9),		testID CHAR(9),
Line 26:		Line 25:
	CONSTRAINT test_pk PRIMARY KEY(testID)		CONSTRAINT test_pk PRIMARY KEY(testID)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	* Table names and attribute names that would otherwise include as space or an underscore as a separator must be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example:		* Table names and attribute names that would otherwise include as space or an underscore as a separator must be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> <br>Example:
	SELECT univID, collegeID, firstName, lastName		SELECT univID, collegeID, firstName, lastName
	* Single quotes for a string literal Double quotes do not work for string literals in all DBMSs Example:		* Single quotes for a string literal Double quotes do not work for string literals in all DBMSs <br>Example:
	WHERE city = 'Rochester' AND state = 'NY'		WHERE city = 'Rochester' AND state = 'NY'
	* Double quotes for any alias that includes a space Example:		* Double quotes for any alias that includes a space <br>Example:
	SELECT univID, firstName "First Name", lastName "Last Name"		SELECT univID, firstName "First Name", lastName "Last Name"
	* Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number Comment options include:		* Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number Comment options include:
	-- single line comment (a space MUST be included after --)		-- single line comment (a space MUST be included after --)

	# single line comment		# single line comment

	/* block comment (can span multiple lines) */		/* block comment (can span multiple lines) */
	* Script submission must be an executable script file (i.e. only comments and SQL statements). Common violations:		* Script submission must be an executable script file (i.e. only comments and SQL statements). Common violations:
Line 45:		Line 42:
	=== Suggestions: ===		=== Suggestions: ===

	* Include whitespace around operators Example: <code>city = 'Rochester'</code>, not <code>city='Rochester'</code>		* Include whitespace around operators <br>Example:<br>&emsp;&emsp;<code>city = 'Rochester'</code>, not <code>city='Rochester'</code>
	* Prefix column names with a table identifier Example, in table student: <code>stuUnivID</code> <code>stuFirstName</code> <code>stuLastName</code> <code>stuMajID</code>		* Prefix column names with a table identifier<br>Example, in table student:<br>&emsp;&emsp;<code>stuUnivID</code> <br>&emsp;&emsp;<code>stuFirstName</code> <br>&emsp;&emsp;<code>stuLastName</code> <br>&emsp;&emsp;<code>stuMajID</code>
	* Indent each hierarchical level Example:		* Indent each hierarchical level <br>Example:
	SELECT stuUnivID, stuFirstName, stuLastName, majName		SELECT stuUnivID, stuFirstName, stuLastName, majName
	FROM student		FROM student

Ganskop at 15:37, 14 January 2025

2025-01-14T15:37:48Z

← Older revision		Revision as of 11:37, 14 January 2025
Line 6:		Line 6:
	=== Relational Notation Standards: ===		=== Relational Notation Standards: ===

	* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore~~<nowiki>~~<br>~~</nowiki>~~ Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>		* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore<br>
			Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>
	* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>		* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>
	* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>		* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>

Ganskop at 15:37, 14 January 2025

2025-01-14T15:37:28Z

← Older revision		Revision as of 11:37, 14 January 2025
Line 6:		Line 6:
	=== Relational Notation Standards: ===		=== Relational Notation Standards: ===

	* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>		* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore<nowiki><br></nowiki> Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>
	* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>		* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>
	* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>		* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>

Ganskop at 15:36, 14 January 2025

2025-01-14T15:36:37Z

← Older revision		Revision as of 11:36, 14 January 2025
Line 6:		Line 6:
	=== Relational Notation Standards: ===		=== Relational Notation Standards: ===

	* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>		* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>
	* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>		* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>
	* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>		* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>

Ganskop: Formatted "SQL Coding Standards" page

2025-01-14T15:30:13Z

Formatted "SQL Coding Standards" page

← Older revision		Revision as of 11:30, 14 January 2025
Line 6:		Line 6:
	=== Relational Notation Standards: ===		=== Relational Notation Standards: ===

	* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>		* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>
	* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>		* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>
	* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>		* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>
	* Foreign key are italicized when typed, dash-underlined when written Example: <code>STUDENT(univID, ''collegeID'', …)</code>		* Foreign key are italicized when typed, dash-underlined when written Example: <code>STUDENT(univID, ''collegeID'', …)</code>
	* Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example: <code>STUDENT(univID, ''collegeID'', firstName, lastName, expectedGradYear, …)</code>		* Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example: <code>STUDENT(univID, ''collegeID'', firstName, lastName, expectedGradYear, …)</code>

	=== Implementation (Coding) Standards: ===		=== Implementation (Coding) Standards: ===

	* One clause per line (<code>SELECT</code>, <code>FROM</code>, <code>WHERE</code>, etc.) Example: ~~<code>~~ SELECT univID, firstName, ~~lastName</code><code>~~ FROM student~~</code><code>~~ WHERE city = 'Rochester' AND state = 'NY';~~</code>~~		* One clause per line (<code>SELECT</code>, <code>FROM</code>, <code>WHERE</code>, etc.) Example:
	* Keywords and data types must be in UPPERCASE Example: ~~<code>~~ CREATE TABLE test (~~</code><code>~~ testID CHAR(9),~~</code><code>~~ testCount INT,~~</code><code>~~ testDesc VARCHAR(255),~~</code><code>~~ CONSTRAINT test_pk PRIMARY KEY(testID)~~</code><code>~~ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;~~</code>~~		SELECT univID, firstName, lastName
	* Table names and attribute names that would otherwise include as space or an underscore as a separator must be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example: ~~<code>~~SELECT univID, collegeID, firstName, lastName~~</code>~~		FROM student
	* Single quotes for a string literal Double quotes do not work for string literals in all DBMSs Example: ~~<code>~~WHERE city = 'Rochester' AND state = 'NY'~~</code>~~		WHERE city = 'Rochester' AND state = 'NY';
	* Double quotes for any alias that includes a space Example: ~~<code>~~SELECT univID, firstName "First Name", lastName "Last Name"~~</code>~~		* Keywords and data types must be in UPPERCASE Example:
	* Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number Comment options include: ~~<code>~~-- single line comment (a space MUST be included after --)~~</code> <code>~~# single line comment~~</code> <code>~~/* block comment (can span multiple lines) */~~</code>~~		CREATE TABLE test (
			testID CHAR(9),
			testCount INT,
			testDesc VARCHAR(255),
			CONSTRAINT test_pk PRIMARY KEY(testID)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
			* Table names and attribute names that would otherwise include as space or an underscore as a separator must be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example:
			SELECT univID, collegeID, firstName, lastName
			* Single quotes for a string literal Double quotes do not work for string literals in all DBMSs Example:
			WHERE city = 'Rochester' AND state = 'NY'
			* Double quotes for any alias that includes a space Example:
			SELECT univID, firstName "First Name", lastName "Last Name"
			* Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number Comment options include:
			-- single line comment (a space MUST be included after --)

			# single line comment

			/* block comment (can span multiple lines) */
	* Script submission must be an executable script file (i.e. only comments and SQL statements). Common violations:		* Script submission must be an executable script file (i.e. only comments and SQL statements). Common violations:
	** Submitting a log file		** Submitting a log file
Line 27:		Line 44:
	=== Suggestions: ===		=== Suggestions: ===

	* Include whitespace around operators Example: <code>city = 'Rochester'</code>, not <code>city='Rochester'</code>		* Include whitespace around operators Example: <code>city = 'Rochester'</code>, not <code>city='Rochester'</code>
	* Prefix column names with a table identifier Example, in table student: <code>stuUnivID</code> <code>stuFirstName</code> <code>stuLastName</code> <code>stuMajID</code>		* Prefix column names with a table identifier Example, in table student: <code>stuUnivID</code> <code>stuFirstName</code> <code>stuLastName</code> <code>stuMajID</code>
	* Indent each hierarchical level Example: ~~<code>~~ SELECT stuUnivID, stuFirstName, stuLastName, majName~~</code><code>~~ FROM student~~</code><code>~~ JOIN major~~</code><code>~~ ON stuMajorID = majID~~</code><code>~~ AND majActive = TRUE~~</code><code>~~ WHERE stuCity = 'Rochester'~~</code><code>~~ AND stuState = 'NY'~~</code><code>~~ AND stuYearLvl >= 3;~~</code>~~		* Indent each hierarchical level Example:
			SELECT stuUnivID, stuFirstName, stuLastName, majName
			FROM student
			JOIN major
			ON stuMajorID = majID
			AND majActive = TRUE
			WHERE stuCity = 'Rochester'
			AND stuState = 'NY'
			AND stuYearLvl >= 3;

Ganskop: Added page for "SQL Coding Standards"

2025-01-14T14:51:13Z

Added page for "SQL Coding Standards"

New page

Any place that you work in the technology industry typically has standards for how code is written, which you are expected to follow. This course is no different.

We will be requiring (and enforcing) the following standards on homework assignments (HWs), practice exercises (PEs), zyLabs, and examinations. In all cases, these are not part of the total points, but rather result in additional deductions (1 point for any occurrence per each item, with up to 7 points deducted if none are followed).

=== Relational Notation Standards: ===

* Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore Examples: <code>STUDENT(…)</code> <code>STUDENT_ADVISOR(…)</code>
* Relation names are singular Example: <code>STUDENT(…)</code>, not <code>STUDENTS(…)</code>
* Primary keys are underlined Example: <code>STUDENT(univID, …)</code>
* Foreign key are italicized when typed, dash-underlined when written Example: <code>STUDENT(univID, ''collegeID'', …)</code>
* Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example: <code>STUDENT(univID, ''collegeID'', firstName, lastName, expectedGradYear, …)</code>

=== Implementation (Coding) Standards: ===

* One clause per line (<code>SELECT</code>, <code>FROM</code>, <code>WHERE</code>, etc.) Example: <code> SELECT univID, firstName, lastName</code><code> FROM student</code><code> WHERE city = 'Rochester' AND state = 'NY';</code>
* Keywords and data types must be in UPPERCASE Example: <code> CREATE TABLE test (</code><code> testID CHAR(9),</code><code> testCount INT,</code><code> testDesc VARCHAR(255),</code><code> CONSTRAINT test_pk PRIMARY KEY(testID)</code><code> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>
* Table names and attribute names that would otherwise include as space or an underscore as a separator must be in camelCase ID exception: <code>ID</code> is commonly used as an abbreviation for ‘identifier’. Attributes that include ‘ID’ can be stated as <code>attrID</code>, instead of the strict camelCase of <code>attrId</code> Example: <code>SELECT univID, collegeID, firstName, lastName</code>
* Single quotes for a string literal Double quotes do not work for string literals in all DBMSs Example: <code>WHERE city = 'Rochester' AND state = 'NY'</code>
* Double quotes for any alias that includes a space Example: <code>SELECT univID, firstName "First Name", lastName "Last Name"</code>
* Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number Comment options include: <code>-- single line comment (a space MUST be included after --)</code> <code># single line comment</code> <code>/* block comment (can span multiple lines) */</code>
* Script submission must be an executable script file (i.e. only comments and SQL statements). Common violations:
** Submitting a log file
** Including the MySQL prompt along with the statement
** Including an uncommented result set

=== Suggestions: ===

* Include whitespace around operators Example: <code>city = 'Rochester'</code>, not <code>city='Rochester'</code>
* Prefix column names with a table identifier Example, in table student: <code>stuUnivID</code> <code>stuFirstName</code> <code>stuLastName</code> <code>stuMajID</code>
* Indent each hierarchical level Example: <code> SELECT stuUnivID, stuFirstName, stuLastName, majName</code><code> FROM student</code><code> JOIN major</code><code> ON stuMajorID = majID</code><code> AND majActive = TRUE</code><code> WHERE stuCity = 'Rochester'</code><code> AND stuState = 'NY'</code><code> AND stuYearLvl >= 3;</code>