SQL SELECT STATEMENT CODING STANDARDS - Updated Summer 2024

SQL Single Table Query SELECT STATEMENT CODING STANDARDS

Hi all, I want to share with the class some of the expected standards that students in the course will be assessed and graded by during the semester when writing Single Table or Multiple Table SELECT Statements during the semester.

During the semester, several SQL SELECT Statement assignments will get published. The SELECT Statements will be in varying degrees of difficulty throughout the semester. There are some coding standards that I will demand for these types of SQL assignments. Points will be deducted from assignments when these standards are not followed.

I also added this material to the Introduction Module in Canvas as a PDF link and listed the document download link in the Announcements for week 1.

Like the last Directed Announcement, because of the importance of students understanding the coding standards that will be in place for SQL SELECT Statement Assignments this semester, I am also sending the information through a Special Directed Class Announcement.

This announcement concerns some preliminary coding standards that must be adhered to during the semester when writing SQL Select Statements. Currently, the class is focusing on Single Table Select Statements during the first half of the semester. As the semester progresses, a few additional items will be added to the coding standards listed in this document. These additional standards will be introduced when the class transitions from Single Table SELECT Queries to Multiple Table Select Queries.

There was a document listed in the Week 1 Class Announcements. The name of the document was “Using a Database Management System”. This reading material that was assigned for week 1 describes and illustrates an International Coding Standard for SQL. It described and illustrated some of the following standards listed below in this announcement. This announcement reinforces the need to follow these listed standards in the course. Once again, student assessment and grading on Single Table Queries will be based on these standards. Points will be deducted when these standards are not followed.

List of SQL Single Table Query Coding Standards

Numeric Literals Compared to Numeric Attributes

Numeric literals comparing to numeric attributes must not be enclosed in quotes. This includes both Integer Numbers that do not contain decimal places and Floating-Point Numbers that contain decimal places.

Examples

totalPrice > “1000.00” - This is an Incorrectly Formatted Boolean Condition for comparing a Floating-Point Attribute to a Floating Point Literal

totalPrice > 1000.00 - This is a Correctly Formatted Boolean Condition for comparing a Floating Point Attribute to a Floating Point Literal

Text Literal Comparing to Text or String Attributes

It also described and illustrated that text literal compared to text or string attributes must be enclosed in quotes. Students must examine the associated metadata to determine the data types of attributes. Students should examine the metadata for some numeric text attributes such as phone numbers, zip codes, and identification numbers. Typically, numbers that will not be involved in calculations will be defined as text data types.

Examples

firstName = “John” - This is a Correctly Formatted Boolean Condition for comparing a String Data Type Attribute to a String Literal

firstName = John - This is an Incorrectly Formatted Boolean Condition for comparing a String Data Type Attribute to a String Literal

String literal must have quotes around them unless they will be considered Reserved Words or Identifier Names

Comparing Date Literals to Attributes of Data Type Date

When comparing DATE Data Types in MySQL or MariaDB, the date literal must be enclosed in quotes. Additionally, when comparing 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 Year, Month, Day in this format “YYYY/MM/DD”.

Examples

WHERE hireDate = “2021/05/22” - This is a correctly formatted Boolean Condition for comparing to a Date Attribute to a Date Literal

WHERE hireDate = 2021/05/22 - This is an Incorrectly Formatted Boolean Condition for comparing to a Date Attribute to a Date Literal

A date value without quotes may be interpreted as an arithmetic expression in some Database Management System (DBMS) environments.

Capitalize Reserved Words

Reserve words such as SELECT, FROM, WHERE, AND, and OR should be Capitalized in all Caps.

Attribute names should be in lowercase. Camel Case can be used.
Table Names should have the first letter in the Table Name Capitalized

Reserved Words must not be in Quotes.

In addition to this, SQL has reserved words compared to Boolean Data Types. These reserve words are TRUE and FALSE. The reserved words TRUE and FALSE should also be capitalized. 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 reserved words TRUE and FALSE. The reserve words TRUE and FALSE must not have Quotes around them.

Examples

WHERE Citizenship = “TRUE” - This is an incorrectly formatted Boolean Condition

WHERE Citizenship = TRUE - This is a correctly formatted Boolean Condition

The Integer Values 1 and 0 can represent TRUE or FALSE

The Integer Values 1 and 0 must not have quotes around them when compared to Boolean Attributes.
The Integer value 1 represents TRUE when compared to a Boolean attribute.
The Integer value 0 represents FALSE when compared to a Boolean Attribute.
The reserve words TRUE and FALSE expand the group of people that will be able to understand the code when reading it.

Examples

WHERE citizen = 1 - This Boolean Expression is Correctly Formatted.

WHERE citizen = “1” - This Boolean Expression is Incorrectly Formatted.

Integers comparing to Boolean Attributes in Boolean Expressions cannot have quotes around Integer Value.

YES and NO are not Reserved Words

The Words “Yes” and “No” cannot be used to represent the Boolean values of TRUE and FALSE in most DBMS products. Typically, only Microsoft Access permits the use of the words YES and NO to also represent the Boolean values TRUE and FALSE.

The Words “Yes” and “No” cannot be used to compare Boolean Attributes in WHERE Clauses. YES and NO are not reserved words in Standard SQL

Examples

Citizenship = “YES” - This is an incorrectly formatted Boolean Condition

Citizenship = YES - This is also an incorrectly formatted Boolean Condition

Boolean Expressions must be Complete.

Comparison of 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 and logical errors in your SELECT Statements.

Examples

This Boolean Expression is Correctly Formatted.

WHERE city = “New York” or city = “Philadelphia”-

This Boolean Expression is Incorrectly Formatted.

WHERE city = “New York”, “Philadelphia”

Never Place Quotes Around Reserve Words

Never place quotes around Reserve Words in SQL. When Quotes are placed around reserved words and numeric literals, there is a high probability of ambiguity errors in many Database Management Systems (DBMS). They may be interpreted as regular string values as opposed to reserved words. That is why in this class, students must adhere to the International Standard, not the expanded standards for a specific DBMS application.

Examples

WHERE Citizenship = “TRUE” - This is an incorrectly formatted Boolean Condition

WHERE Citizenship = TRUE - This is a correctly formatted Boolean Condition

Here are some typical comparative operators used for comparisons in the WHERE Clauses appended to SELECT Statements

Comparative Operators Available to Use in SQL

Typically, comparative operators are used when comparing the following; comparing two table attributes, comparing a table attribute to a literal value, or comparing two literal values.

The symbol” >” represents Greater Than
The symbol” <” represents Less Than
The symbol” =” represents Equality.
The symbol”!=” represents Inequality
The symbol” >=” represents Greater Than or Equal to
The symbol” <=” represents Less Than or Equal to

As you can see, there are six basic comparative operators that one can use when filtering the returned rows or tuples of a result set in a SELECT Query Statement

Review the METADATA Before Writing the Query Statement

METADATA – Metadata is information about information. Metadata describes the characteristics of the data and information in the database tables.

Students must review the Metadata included with the Single Table Query Assignments. When a database user is not familiar with a database, written metadata can be helpful for the user in understanding the database structure and required table and attribute identifiers needed to correctly interact with the Database Management System (DBMS) when writing Query Statements.

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.

Test the Query Statement before Submitting it for Grading.

Testing is an important component in Software Development.

Each Single Table Query Assignment contains a Sample Database to test the SQL Script for Syntax Errors and Logical Errors. Students should test their SQL Script before submitting the script for assessment and grading. A script containing syntax errors can be debugged and corrected before submitting the final version of the assignment.

There Are Two Categories of Testing Tools Recommended in The Course

Client Sided Testing Tool
Cloud Side Internet Server Testing Tool

Client Sided Testing Tools

Client Sided Testing Tools require downloading and installing the software on personal computers. Some students have experienced difficulties downloading and installing this category of SQL Testing Tool. Particularly, students with Apple Operating Systems (OS) have had difficulties. Students with limited experience using an Apple OS in Terminal Mode should consider using one of the Cloud Side Internet Server Testing Tools

Client Sided Testing Tool

MariaDB
MySQL

Cloud Side Internet Server Testing Tools

Cloud Side Internet Server Testing Tools are internet based. They do not require downloading and installation. You can use a Web Browser to access the service to test the SQL Statements.

Cloud Side Internet Server Testing Tool

In the Course Introduction Module, there is a link to a Guided Video Tutorial on using the Paiza.io SQL Statement Testing Tool. The framework of using the Paiza.io testing tool can be easily transferred to using the OneCompiler.com Testing Tool.

Getting Help with Syntax Errors

Sometimes a person may need a little help problem solving an issue such as a syntax error in a SQL Statement. There are various pathways for students to receive assistance.

Categories or Types of Assistance

Direct Face-To-Face Assistance with the professor
Asynchronous Email Interaction with the professor
Synchronous Zoom Meeting with the professor
Synchronous Zoom Meeting with a tutor in the CCP Learning Lab
Direct Face-To-Face Assistance with a tutor in the CCP Learning Lab

Direct Face-To-Face Assistance

There can be direct face-to-face assistance. This is when a student can meet in person with a person knowledgeable in the subject area. This is a person such as your professor or a CCP Tutor in the CCP Learning Lab.

Asynchronous Email Interaction

Asynchronous Communication is when there is delay in time between sending a message and receiving a reply. Students can send an email message seeking assistance with a syntax error in a SQL Statement. The student must attach a copy of the current version of the SQL Text File with the email. The message in the email should include a brief description of the problem. After receiving the email, I will examine the issue and create a response. The response will contain a description of the problem. The response will also contain description of how to correct the problem.

Synchronous Zoom Meeting

Synchronous Communication is when there is instantaneous back and forth communication between two parties. Another pathway to receive assistance is to schedule a Zoom Meeting. A Zoom Meeting is a Synchronous form of communication. To schedule a Zoom Meeting, a student must send an email to me requesting the Zoom Meeting. The email should contain several possible days and related times that the student is available to meet.

I will respond to the email with a reply containing a link to join a Zoom Meeting if I have availability during any of the available times that you the student has listed in the previous message.

Getting Assistance in Face-To-Face Sections

In sections of the course that meet face-to-face, students can get help correcting syntax errors in queries during scheduled lab times during class.
Students can get assistance debugging Syntax errors through asynchronous email interaction with the professor.
Students can get assistance debugging Syntax errors through a scheduled Zoom Meeting.
Students can get assistance by scheduling a face-to-face appointment with a CCP Tutor in the CCP Learning Lab
Students can get assistance by scheduling an online Zoom appointment with a CCP Tutor in the CCP Learning Lab

Getting Assistance in Online Sections

Students can get assistance debugging Syntax errors through asynchronous email interaction with the professor.
Students can get assistance debugging Syntax errors through a scheduled Zoom Meeting interaction with the professor.
Students can get assistance by scheduling an online Zoom appointment with a CCP Tutor in the CCP Learning Lab

How To Schedule A Meeting with A CCP Tutor In the CCP Learning Lab.

Students can get assistance by interacting with a CCP Tutor in the CCP Learning Lab. I provided instructions in Canvas describing and illustrating how to schedule a meeting with a CCP Tutor in the CCP Learning Lab. The name of the link is “Instructions for Scheduling a CCP Learning Lab Tutor”. It is in the Course Introduction Module in Canvas.

Information I Need to Provide Support with Syntax Errors

When communicating with their professor, students must include the SQL Text File containing the SQL Script for the assignment, a description of the problem, and the line number in the script causing the syntax problem.

Non-Syntax Related Issues

I can only provide support when there is a syntax error in the SQL script.

Logical errors in the Result set of a SQL Query are part of the problem-solving portion of the homework assignment that students must solve on their own. After the assignment is graded and feedback has been provided to the class, I can provide guidance on solving individual logical errors in student solutions. Depending on the issue and assignment, this may require scheduling a meeting with me during my scheduled office hours.

Students that have questions on any of these standards should send an email requesting additional clarification on these coding standards. The email must contain a specific standard that needs clarification. Optionally, students can schedule a Zoom meeting. When scheduling a Zoom meeting, I recommend that students send multiple days and times that they would like to meet that overlap my scheduled office hours listed in the course syllabus. I will then send a reply containing a link to a scheduled Zoom Meeting when our availability overlaps.