SQL SELECT STATEMENT CODING STANDARDS-2
SQL SELECT STATEMENT CODING STANDARDS
This announcement concerns coding standards that must be adhered to during the semester. The reading material that was assigned for week 1 describes and illustrates an International Coding Standard for SQL. It illustrated some of the following standards
- Numeric literals comparing to numeric attributes must not be enclosed in quotes.
- It also described and illustrated that text literals comparing to text or string attributes must be enclosed in quotes.
- When comparing to DATE Data Types in MySQL or MariaDB, the date literal must be enclosed in quotes. Additionally, when comparing to DATE Data Types in MySQL or MariaDB, the date literal must be in the recognized date format for MySQL or MariaDB. The recognized date format for MySQL and MariaDB is “YYYY/MM/DD”.
- Reserve words should be Capitalized in all Caps.
- Attribute names should be in lower case.
- Table Names should have the first letter in the Table Name Capitalized
- In addition to this SQL has reserve words when comparing to Boolean Data Types. These reserve words are TRUE and FALSE. SQL also recognizes the integers 1 and 0 as comparative operators to Boolean values. The Integers 1 and 0 are accepted for assignment submission. However, I encourage students to use the reserve words TRUE and FALSE. The reserve words TRUE and FALSE must not have Quotes around them. The Integer Values 1 and 0 must not have quotes around them. The reserve words TRUE and FALSE expand the group of people that will be able to understand the code when reading it.
- Comparison Boolean Expressions in WHERE Clauses must be complete. This means that there should be an attribute or literal value on both sides of the comparison operator. This will avoid possible syntax errors in your SELECT Statements.
Students must review the Metadata included with the Single Table Query Assignments.
- The Metadata Document illustrates and describes the data types of attributes of the data in the table.
- The Metadata Document illustrates and describes the constraints of attributes in the table.
- The Metadata Document illustrates and describes the Primary Key of the attributes in the table.
- The Metadata Document illustrates and describes the Foreign Keys of attributes in a table.
- The Metadata Document illustrates and describes the context of attributes in the table.
Never place quotes around Reserve Words in SQL. When Quotes are placed around reserve words and numeric literals, there is a high probability of ambiguity errors in many Database Management Systems (DBMS). That is why in this class, students must adhere to the International Standard, not the expanded standards for a specific DBMS application.
Each Single Table Query Assignment contains a Sample Database to test the SQL Script for Syntax Errors. Students should test their SQL Script before submitting the script for assessment and grading. A script containing syntax errors can be debugged prior to submitting the final version of the assignment. Students can get assistance debugging Syntax errors through email interaction with the professor or interacting with a CCP Tutor in the Learning Lab. When communicating with your professor, students must include a description of the problem and the line number in the script causing the syntax problem.