UMass College of Information and Computer Sciences

COMPSCI 197Q Introduction to SQL (1 credit)

Instructor:  Gordon Anderson

gordon@cs.umass.edu

 

Course Description: This course provides a comprehensive introduction to the language of relational databases: Structured Query Language (SQL). Topics covered include: Entity-Relationship modeling, the Relational Model, the SQL language: data retrieval statements, data manipulation and data definition statements. All interactive reading problems involve the use of "live" SQL. Homework will be done using databases running in MySQL which students install on their machines. Students develop a real-world database project using MySQL during the course.

 

Prerequisites: This course is designed for students who desire an intermediate level of proficiency with the Structured Query Language (SQL). Prerequisite: Basic computer literacy including ability to create and manipulate files and install software.

 

Justification: This course will provide a solid preparation in SQL for students in the Computer Science and Informatics majors who plan to take database or data science courses. A knowledge of this material is also an important skill for anyone planning to do software engineering and development. It is also a service course for students outside of the CS major who work with or plan to work with relational databases and data science such as students studying social science, business, biological and physical sciences, among others.

 

Course Objectives: 

 

Text and Learning Management System:

 

Interactive SQL an online text delivered via the OWL (Online Web-based Learning) system. No software other than a web browser required for this material.

 

OWLlogo

iSQLlogo2

 

           Course administration and communications via Moodle.

          moodleLogo

                       

Software for Students to Install (open source- do not have to buy):  

            https://dev.mysql.com/downloads/mysql/

Methods of Instruction:  This course is designed as a self-paced learning experience. The course is delivered in a series of assignments to provide a structure for students to work through the material. Students may work through the material at their own pace. All text and homework problems are available online. Instructor support is offered in an on-line format via chat sessions and forum postings. A final project will be developed and submitted by the end of the course.

 

 

Attendance Policy: 

Attendance is not necessary as the material is delivered, assessed, and collected online. Assignments must be completed by their due dates. Late work will not be accepted without clearance from the instructor. Students are expected to manage their time so that they submit the assignments on or before the due dates.

 

Communication with the instructor is via online forums and chat sessions.

 

Disabilities: 

We encourage students who have any disabilities which may impact their learning in this class to get in touch with the instructor as soon as possible. Our goal is to create a great learning experience for every student.

 

Methods of Evaluation: Your grade will be based on the following:

 

Homework will be assigned in a series of assignments. Homework will be submitted via the interactive SQL material on the OWL website. A grade and feedback will be provided on each homework assignment.

 

Project: A project will be developed by students during the course. The project is an opportunity to exercise the knowledge and skills learned in the text and homework in a creative manner.

 

Grade Breakdown:

Homework

75%

Project

25%

  

Assignment Schedule:

This schedule is subject to change at the discretion of the instructor. The chapters refer to the textbook.

 

Module

Reading and Lecture Topic

Assessment

1

Entity-Relationship (ER) modeling

and the Relational Data Model

interactive SQL Ch1

 

Hwk 1

2

  interactive SQL Ch2

  2.0 DataRetrievalOverview

  2.1 SelectQueryIntro 2.2 Distinct

  2.3 Alias

  2.4 OrderBy

  2.5 WildcardInSelectList

  2.6 ComputedColumn

 

Hwk 2

3

  2.7 WhereClauseIntro

  2.8 WhereClauseComparisonOp

  2.9 Like Operator

  2.10 Between Operator

  2.11 PropositionalLogic

  2.12 WhereClauseLogicalOpI

 

Hwk 3

4

  2.13 JoinQueries

  2.14 JoinOperator

  2.15 MultiTableJoins

  2.16 OuterJoins

  2.17 Grouping

  2.18 GroupingHavingClause

 

Hwk 4

5

  2.19 Subqueries

  2.20 SubqueriesInClauses

  2.21 CorrelatedSubqueries

  2.22 SpecialFunctions

  2.23 UnionIntersectMinus

 

Hwk 5

6

SQL Data manipulation statements

            interactive SQL Ch 3

 

Hwk 6

7

SQL Data definition statements

interactive SQL Ch 4

 

Hwk 7

8

Final Project Due

 

Late Submissions: No late work will be accepted unless you have made an agreement with the instructor. Extensions are given only for extenuating circumstances. Medical events must be documented with a physician's note stating that the student could not engage in the work for medical reasons. Technical issues with network and client machine are the student's responsibility. Adjustments will be made in the case of server downtime which make the course content unavailable.

 

Communication: Students are required to log on to the course website(s) on a regular basis. Students are required to communicate in a timely and professional manner regarding any issues related to this course. If you have any difficulties getting work in on time you must let the instructor know, otherwise late work will not be accepted.

 

Academic Integrity Policy: "Honesty in all academic work is expected of every student. This means giving one's own answers in all class-work without help from any outside source not approved by the instructor. Written material is to be the student's original composition. Appropriate credit must be given for outside sources from which ideas, language, or quotations are derived." As members of the academic community, students are expected to be responsible for all of their own academic work without dishonesty or deception; joint work is legitimate only when assigned or approved by the instructor. Violations of academic integrity include, but are not limited to, the following general categories:

Online Classroom Civility: There are certain basic standards of classroom civility that should be adhered to in order to make the online classroom a pleasant place for all involved. In an online setting, "classroom" means any public forums or chat sessions. It also applies to student to student and student to instructor communication. Civility does not eliminate appropriate humor, enjoyment, or other features of a comfortable and pleasant classroom community. Classroom civility examples include: displaying respect for all members of the classroom community, both your instructor and fellow students; attentiveness to and participation in chat sessions, forums, and other classroom exercises. Those students who do not behave in a civil fashion may be asked to leave the class.