The SQL Programming Language

INFO1-CE9272

Professor:Sam Sultan   [sam.sultan@nyu.edu]
Class website: [workshop.sps.nyu.edu/~sultans/sql]   (or)   [samsultan.com/sql/]
Course Days: Thursdays, September 19 - November 21
Course Hours: 6:30pm - 9:30pm
This course will be delivered online via Zoom software

Announcement(s):

+syllabus
+course outline
+grades & evaluation
+student listing
+examples & demos
 
+student feedback
+student evaluation & comments

Session - 1   2   3   4   5   6   7   8a   8b   9   10  


SQL Tutorial - MySql Server (download) / MySql Workbench - Oracle Server (download) / SQL Developer - MS SQL Server (download) - MongoDB© - SQL*Tester ©
Site Helpful?

COURSE DESCRIPTION:

Structured Query Language (SQL) is the language used to manipulate data in relational databases. Learn to use SQL to select, update, insert, and delete data from database tables, and acquire hands-on experience with both Oracle and MySQL. Learn how to select data from multiple tables using joins and unions, understand how to create sub-queries to develop more complex retrieval capabilities, and use DDL to create your own database and to populate tables. In addition, learn about database design, table relationships, and normalization techniques. This course prepares you to work with any relational database, such as Oracle, MySQL, SQL Server, PostgreSQL, SQLite, and even the relational aspect of Snowflake.


COURSE LEARNING GOALS:

1. Course Objectives:

The objective of this course is to teach you the fundamentals behind the SQL language. We will discuss the concepts of databases in general, and more specifically the relational database model. We'll examine the various database engines that support this model. We will learn about SQL (Structured Query language) both as an ANSI standard language and more some vendor specific extensions. We will also use DDL (Data Definition Language) to create and delete database objects, and DML (Data Manipulation Language) to access and manipulate those objects.

This course will teach you all relevant SQL concepts more from an ANSI SQL2 standard point of view. Some topics, and examples however will need to be discussed with a specific database engine in mind. This course will cover SQL from both an Oracle and a MySQL point of view

The focus of the course will be on the following topics:

2. Student Learning Outcomes:


BOOKS - (Required / Suggested)

Required Books -


GRADE ASSIGNMENT AND EVALUATION:


Details of Assignment, Exam and Grade Evaluation.
[Exercise/Homework Answers]

Non-Evaluative and Pass/Fail Grades
If you are not interested in receiving a grade, and you simply require an NE (non-evaluative) or P/F (pass/fail) grade, please fill out the appropriate form listed. Neither an NE nor a P/F grade will count toward an NYU certificate.

NYU SPS Grading Scale and Policies https://www.sps.nyu.edu/homepage/student-experience/policies-and-procedures.html>


Grades are FINAL.

Please do not negotiate for a better grade. If you are expecting to receive a grade of an "A" at the end of the semester, then I expect you to attend all sessions (unless I am notified ahead of time), to participate in these sessions, to keep up with the class reading material, and to complete your homework assigments. This will ensure that you stay current with the class content, and will ensure that you get a good grade on your test(s), project as well as your final grade.

Please Note: The Office of the University Registrar maintains individual records of students enrolled in NYU and is the only department authorized to record an official grade. Final grades are reported on NYU-Albert.
For more information: https://www.sps.nyu.edu/academics/academic-policies-and-procedures/graduate-academic-policies-and-procedures.html


To receive your final grade at the end of the semester, follow these steps:

  1. Log into Albert using your net id
  2. Click on "Student Center"
  3. Within your student center, in the "academics" section click on the dropdown: "other academic"
  4. From the dropdown select "grades"


COURSE OUTLINE:

DATE SESSION TOPIC[s] COVERED
 
[Week 1] 1 Introduction to databases
What is a database?
The History of databases
The various database models
Hierarchical databases
Network databases
Relational databases
Object & Object relational databases
NoSQL and Big Data databases

Reading:   Chapter 1
 
[Week 2] 2 Introduction to SQL
Flavors of SQL
DDL - Data Definition Language
DML - Data Manipulation Language
The SELECT statement
Choosing distinct values
The WHERE clause
Comparison operators
Comparing with LIKE
Logical operators, AND, OR, NOT
Numeric operators
Creating computational columns

Reading: Chapters 2, 3
 
[Week 3] 3 Selecting data from multiple tables
The join construct
Old vs. new join syntax
Normal or Inner join
Cross join - Cartesian product
Outer join vs. Inner join
What is a Self Join
Set operators, UNION, INTERSECT, MINUS
Combining Join with UNION
Performance considerations

Reading: Chapter 5
 
[Week 4] 4 SQL built-in Functions
Numeric functions - CEIL, FLOOR, ROUND, TRUNCATE, etc.
String functions - CONCAT, LENGTH, SUBSTR, REPLACE, etc.
The CASE expression, 2 flavors
Date functions - MySQL and Oracle
Current date, date manipulation, date formatting

Reading: Chapter 7 (Skip Aggregate functions), 12
 
[Week 5] 5 Aggregating and Grouping
Aggregate functions - SUM, COUNT, AVG, MIN, MAX
The GROUP BY clause
The HAVING clause
Finding Duplicate Records
GROUP BY with ROLLUP feature
The ORDER BY clause Pivoting rows into columns

Reading: Chapters 7 (Aggregate functions), 4
 
[Week 6] 6 Using SELECT Sub-Queries
Subqueries as filters
Subqueries as inline views
Subqueries as additional derived columns
Correlated Subqueries
Where [NOT} EXISTS in Subquery
Finding the last record from a set
Pivoting rows into columns

Reading: Chapter 6
 
[Week 7] 7 Database Design
The Logical and Physical Model
Understanding data normalization
First normal form
Second normal form
Third normal form
Pros & cons of data normalization
Denormalizing data
Entity relationships
One-to-one relationship
One-to-many relationship
Many-to-many relationship
Designing Self-join relationship
Designing for an ODS (Reporting Database)
Designing for a Data Warehouse

Reading: Chapter 8
 
[Week 8] 8a Creating database objects
What is a primary key?
What is a foreign key?
What is an index?
Creating tables
SQL data types
Adding a primary key
Adding constraints
Creating Indexes
Altering table definition
Droping tables
MySql Auto Increment
Oracle Sequences

Reading: Chapters 9, 10, 15
 
  8b Manipulating data in tables
Adding data with the INSERT statement
INSERT with a SELECT statement
Changing data with the UPDATE statement
UPDATE with a SELECT statement
Removing data with the DELETE statement
DELETE with a SELECT statement
The TRUNCATE statement
The REPLACE statement (MySql)
The MERGE statement (Oracle)

Reading: Chapter 11
 
[Week 9] 9 Advanced Topics
Creating and using views
Using the Data Dictionary - MySql
Using the Data Dictionary - Oracle
The show statement (MySql)
Loading data from a file
Unloading data into a file
Importing a database or table(s)
Exporting a database of table(s)

Reading: Chapters 13, 18
 
[Week 10] 10 - Final Exam -


All contents © Sam Sultan.
For more information, send e-mail to: sam.sultan@nyu.edu