Tags
SQL is code that can be generated based on natural language prompts. SELECT statements, which are reading statements, can be tested and improved. A powerful way to use ChatGPT is to ask for a statement to be explained. So any sample SQL posted on a github repository or in the community posts, can be used as a detailed learning experience.
This process can be used as a way to get started, debug your code, or to get help to work through errors.
Sample SQL queries can be found here:
https://github.com/blackboard/BBDN-BlackboardData-Queries
In this example, we provide a structure of the COURSE table, and then ask to have SQL prepared:
https://chat.openai.com/share/b7d701b5-914c-4f6f-9046-3dceecf687fe
A more complex example for creating a query based on multiple tables. Additional business rules can be also provided.
Start of prompt:
cdm_lms.course_tool (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
COURSE_ID NUMBER(38,0)
TOOL_ID NUMBER(38,0)
COURSE_SOURCE_ID VARCHAR(16777216)
TOOL_SOURCE_ID VARCHAR(16777216)
AVAILABLE_IND BOOLEAN
TOC_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
)
cdm_lms.course (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
COURSE_PARENT_ID NUMBER(38,0)
TERM_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
NAME VARCHAR(16777216)
DESCRIPTION VARCHAR(16777216)
START_DATE DATE
END_DATE DATE
COURSE_NUMBER VARCHAR(16777216)
DESIGN_MODE VARCHAR(16777216)
DESIGN_MODE_SOURCE_CODE VARCHAR(16777216)
DESIGN_MODE_SOURCE_DESC VARCHAR(16777216)
ENROLLMENT_METHOD VARCHAR(16777216)
ENROLLMENT_METHOD_SOURCE_CODE VARCHAR(16777216)
ENROLLMENT_METHOD_SOURCE_DESC VARCHAR(16777216)
AVAILABLE_IND BOOLEAN
AVAILABLE_TO_STUDENTS_IND BOOLEAN
ENABLED_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
MODIFIED_TIME TIMESTAMP_LTZ(3)
CREATED_TIME TIMESTAMP_LTZ(3)
START_TIME TIMESTAMP_LTZ(3)
END_TIME TIMESTAMP_LTZ(3)
COPY_FROM_COURSES VARCHAR(16777216)
)
cdm_lms.tool (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
NAME VARCHAR(16777216)
PLUGIN_DESC VARCHAR(16777216)
PLUGIN_VENDOR VARCHAR(16777216)
TOOL_TYPE VARCHAR(16777216)
DESIGN_MODE VARCHAR(16777216)
DESIGN_MODE_SOURCE_CODE VARCHAR(16777216)
DESIGN_MODE_SOURCE_DESC VARCHAR(16777216)
BUILT_IN_IND BOOLEAN
VISIBLE_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
PLUGIN_HANDLE VARCHAR(16777216)
)
cdm_lms.course_tool_activity (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
COURSE_ID NUMBER(38,0)
PERSON_COURSE_ID NUMBER(38,0)
PERSON_ID NUMBER(38,0)
COURSE_TOOL_ID NUMBER(38,0)
SESSION_ACTIVITY_ID NUMBER(38,0)
TOOL_ID NUMBER(38,0)
COURSE_SOURCE_ID VARCHAR(16777216)
LOGIN_SOURCE_ID VARCHAR(16777216)
PERSON_SOURCE_ID VARCHAR(16777216)
TOOL_SOURCE_ID VARCHAR(16777216)
DURATION_SUM NUMBER(38,0)
INTERACTION_CNT NUMBER(38,0)
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
FIRST_ACCESSED_TIME TIMESTAMP_LTZ(3)
LAST_ACCESSED_TIME TIMESTAMP_LTZ(3)
)
cdm_lms.person (
ID NUMBER(38,0)
GLOBAL_PERSON_ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
FIRST_NAME VARCHAR(16777216)
LAST_NAME VARCHAR(16777216)
AVATAR_URL VARCHAR(16777216)
BIRTH_DATE DATE
EMAIL VARCHAR(16777216)
POSTAL_CODE VARCHAR(16777216)
INSTITUTION_ROLE VARCHAR(16777216)
INSTITUTION_ROLE_SOURCE_CODE VARCHAR(16777216)
INSTITUTION_ROLE_SOURCE_DESC VARCHAR(16777216)
SYSTEM_ROLE VARCHAR(16777216)
SYSTEM_ROLE_SOURCE_CODE VARCHAR(16777216)
SYSTEM_ROLE_SOURCE_DESC VARCHAR(16777216)
AVAILABLE_IND BOOLEAN
ENABLED_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
CREATED_TIME TIMESTAMP_LTZ(3)
MODIFIED_TIME TIMESTAMP_LTZ(3)
)
cdm_lms.person_course (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
COURSE_ID NUMBER(38,0)
PERSON_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
COURSE_ROLE VARCHAR(16777216)
COURSE_ROLE_SOURCE_CODE VARCHAR(16777216)
COURSE_ROLE_SOURCE_DESC VARCHAR(16777216)
AVAILABLE_IND BOOLEAN
ENABLED_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
MODIFIED_TIME TIMESTAMP_LTZ(3)
ACT_AS_INSTRUCTOR_IND BOOLEAN
COURSE_ROLE_DESC VARCHAR(16777216)
DUE_DATE_EXCEPTION NUMBER(38,0)
TIME_LIMIT_EXCEPTION NUMBER(38,0)
ENROLLMENT_TIME TIMESTAMP_LTZ(3)
PRIMARY_INSTRUCTOR_IND BOOLEAN
)
cdm_lms.course_activity (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
COURSE_ID NUMBER(38,0)
PERSON_COURSE_ID NUMBER(38,0)
PERSON_ID NUMBER(38,0)
SESSION_ACTIVITY_ID NUMBER(38,0)
COURSE_SOURCE_ID VARCHAR(16777216)
LOGIN_SOURCE_ID VARCHAR(16777216)
PERSON_SOURCE_ID VARCHAR(16777216)
DURATION_SUM NUMBER(38,0)
INTERACTION_CNT NUMBER(38,0)
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
MOBILE_IND BOOLEAN
FIRST_ACCESSED_TIME TIMESTAMP_LTZ(3)
LAST_ACCESSED_TIME TIMESTAMP_LTZ(3)
)
cdm_lms.activity (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
PERSON_ID NUMBER(38,0)
COURSE_ID NUMBER(38,0)
COURSE_ITEM_ID NUMBER(38,0)
TOOL_ID NUMBER(38,0)
COURSE_SOURCE_ID VARCHAR(16777216)
COURSE_ITEM_SOURCE_ID VARCHAR(16777216)
LOGIN_SOURCE_ID VARCHAR(16777216)
PERSON_SOURCE_ID VARCHAR(16777216)
TOOL_SOURCE_ID VARCHAR(16777216)
DURATION_SUM NUMBER(38,0)
GRADE_VIEW_CNT NUMBER(38,0)
INTERACTION_CNT NUMBER(38,0)
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
FIRST_ACCESSED_TIME TIMESTAMP_LTZ(3)
LAST_ACCESSED_TIME TIMESTAMP_LTZ(3)
ACCESSED_TIME TIMESTAMP_LTZ(0)
)
cdm_lms.session_activity (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
PERSON_ID NUMBER(38,0)
LOGIN_SOURCE_ID VARCHAR(16777216)
PERSON_SOURCE_ID VARCHAR(16777216)
DURATION_SUM NUMBER(38,0)
INTERACTION_CNT NUMBER(38,0)
MOBILE_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
FIRST_ACCESSED_TIME TIMESTAMP_LTZ(3)
LAST_ACCESSED_TIME TIMESTAMP_LTZ(3)
)
cdm_lms.grade (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
GRADEBOOK_ID NUMBER(38,0)
PERSON_COURSE_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
NAME VARCHAR(16777216)
DESCRIPTION VARCHAR(16777216)
COMMENT VARCHAR(16777216)
FEEDBACK VARCHAR(16777216)
SCORE FLOAT
POSSIBLE_SCORE FLOAT
NORMALIZED_SCORE FLOAT
ATTEMPTED_CNT NUMBER(38,0)
GRADED_CNT NUMBER(38,0)
MANUAL_IND BOOLEAN
VALID_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
LAST_GRADED_TIME TIMESTAMP_LTZ(3)
GRADED_TIME TIMESTAMP_LTZ(3)
FIRST_ATTEMPTED_TIME TIMESTAMP_LTZ(3)
MODIFIED_TIME TIMESTAMP_LTZ(3)
LAST_ATTEMPTED_TIME TIMESTAMP_LTZ(3)
CREATED_TIME TIMESTAMP_LTZ(3)
FIRST_GRADED_TIME TIMESTAMP_LTZ(3)
ATTEMPTED_TIME TIMESTAMP_LTZ(3)
MODIFIER_ROLE VARCHAR(16777216)
MODIFIER_PERSON_ID NUMBER(38,0)
)
cdm_lms.gradebook (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
COURSE_ID NUMBER(38,0)
COURSE_ITEM_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
NAME VARCHAR(16777216)
DESCRIPTION VARCHAR(16777216)
GRADEBOOK_TYPE VARCHAR(16777216)
ALLOWED_ATTEMPTS_CNT NUMBER(38,0)
POSSIBLE_SCORE FLOAT
AGGREGATION_MODEL VARCHAR(16777216)
AGGREGATION_MODEL_SOURCE_CODE NUMBER(38,0)
AGGREGATION_MODEL_SOURCE_DESC NUMBER(38,0)
CALCULATION_TYPE VARCHAR(16777216)
CALCULATION_TYPE_SOURCE_CODE VARCHAR(16777216)
CALCULATION_TYPE_SOURCE_DESC VARCHAR(16777216)
DELETED_IND BOOLEAN
FINAL_GRADE_IND BOOLEAN
MULTIPLE_ATTEMPTS_IND BOOLEAN
USED_IN_CALCULATIONS_IND BOOLEAN
VISIBLE_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
MODIFIED_TIME TIMESTAMP_LTZ(3)
CREATED_TIME TIMESTAMP_LTZ(3)
DUE_TIME TIMESTAMP_LTZ(3)
GRADES_RELEASED_IND BOOLEAN
)
cdm_lms.evaluation (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
EVALUABLE_ITEM_ID NUMBER(38,0)
EVALUABLE_COURSE_ITEM_ID NUMBER(38,0)
PERSON_COURSE_ID NUMBER(38,0)
EVALUATION_PARENT_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
NAME VARCHAR(16777216)
DESCRIPTION VARCHAR(16777216)
TYPE VARCHAR(16777216)
FEEDBACK VARCHAR(16777216)
SCORE FLOAT
POSSIBLE_SCORE FLOAT
NORMALIZED_SCORE FLOAT
PRIMARY_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
CREATED_TIME TIMESTAMP_LTZ(3)
MODIFIED_TIME TIMESTAMP_LTZ(3)
)
cdm_lms.evaluable_item (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
COURSE_ID NUMBER(38,0)
COURSE_ITEM_ID NUMBER(38,0)
EVALUABLE_ITEM_PARENT_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
NAME VARCHAR(16777216)
DESCRIPTION VARCHAR(16777216)
TYPE VARCHAR(16777216)
DELETED_IND BOOLEAN
CHILD_CNT NUMBER(38,0)
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
CREATED_TIME TIMESTAMP_LTZ(3)
MODIFIED_TIME TIMESTAMP_LTZ(3)
)
cdm_lms.evaluable_course_item (
ID NUMBER(38,0)
INSTANCE_ID NUMBER(38,0)
EVALUABLE_ITEM_ID NUMBER(38,0)
COURSE_ITEM_ID NUMBER(38,0)
SOURCE_ID VARCHAR(16777216)
PRIMARY_IND BOOLEAN
STAGE OBJECT
ROW_INSERTED_TIME TIMESTAMP_LTZ(3)
ROW_UPDATED_TIME TIMESTAMP_LTZ(3)
ROW_DELETED_TIME TIMESTAMP_LTZ(3)
CREATED_TIME TIMESTAMP_LTZ(3)
)
create SQL to display final grade as indicated in gradebook table and student email. Do it in course called IDS 273 Business Online 2022 Fall.
Grade is stored in column normalized_score as a percentage so use default grade letter schema.
Join gradebook table with grade on the id field.
Join grade on PERSON_COURSE_ID with person_course table.
Make sure to remove deleted rows from grade table with ROW_DELETED_TIME.
Output:
SELECT
c.NAME AS COURSE_NAME,
p.EMAIL AS STUDENT_EMAIL,
gb.NAME AS GRADEBOOK_NAME,
g.NORMALIZED_SCORE,
CASE
WHEN g.NORMALIZED_SCORE >= 90 THEN ‘A’
WHEN g.NORMALIZED_SCORE >= 80 THEN ‘B’
WHEN g.NORMALIZED_SCORE >= 70 THEN ‘C’
WHEN g.NORMALIZED_SCORE >= 60 THEN ‘D’
ELSE ‘F’
END AS GRADE_LETTER
FROM
cdm_lms.course c
JOIN
cdm_lms.gradebook gb ON c.ID = gb.COURSE_ID
JOIN
cdm_lms.grade g ON gb.ID = g.GRADEBOOK_ID
JOIN
cdm_lms.person_course pc ON g.PERSON_COURSE_ID = pc.ID
JOIN
cdm_lms.person p ON pc.PERSON_ID = p.ID
WHERE
c.NAME = ‘IDS 273 Business Online 2022 Fall’
AND g.ROW_DELETED_TIME IS NULL;
Experience Design for Learning 