Advanced SQL Programming

INFO1-CE5150

Professor:Sam Sultan [sam.sultan@nyu.edu]
Class website: [workshop.sps.nyu.edu/~sultans/sql2] (or) [samsultan.com/sql2]
Course Days: Saturdays
Course Hours: 1:30pm - 5:30pm

Announcement(s):

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

Session - Prereq1   Prereq2   1   2   3   4   5   6   7   8   9   10   11   12  


SQL - MySql Server / GUI Workbench (downloads) - Oracle Server / GUI SQLdeveloper (downloads) - MS SQL Server (download) - SQL*Tester © - SQL*Chart©
Site Helpful?

COURSE OBJECTIVE:

The objective of this course is to teach you the fundamentals and more advanced concepts in the SQL language. 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.

Some of the more advanced topics covered will include database design, data normalization, SQL subqueries, creating and using views, understanding and working with the MySql and Oracle data dictionaries, loading and unloading databases. In addition, we will cover Oracle's procedural language PL/SQL, as well as MySQL procedural language

This course will teach you all relevant SQL concepts more from an ANSI SQL 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 a MySql and an Oracle point of view.

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


BOOKS - (Required / Suggested)

Required Books -

Suggested Books -

GRADES AND GRADING POLICY


Details of Assignment and Evaluation.

How to Submit Homeworks.

- Homework assignments are always due the next session we meet.
- Print out your homework code and output and bring with you to class the next time we meet.
- I will either collect, or will ask students to discuss their solutions in class.
- I will not accept homework via email unless you are not able to attend the class.
- Multiple pages should be stapled together (paper clips are not accepted).

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.

If you are not interested in a grade, or you do not submit your homeworks/project or take the exams, then you will receive a grade of an "NE" (Non-Evaluative). A grade of NE is final, and cannot be changed. A grade of NE cannot be applied as partial fulfillment for any NYU certificate program.

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: http://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. click on http://www.sps.nyu.edu/academics/noncredit-offerings/academic-noncredit-policies-and-procedures.html#Obtaining_Grades
  2. Log into Albert using your net id, at: https://admin.portal.nyu.edu/psp/paprod/EMPLOYEE/EMPL/h/?tab
  3. Click on "Student Center"
  4. Within your student center, in the "academics" section click on the dropdown: "other academic"
  5. From the dropdown select "grades"
  6. For complete instructions click here


COURSE OUTLINE

DATE SESSION TOPIC[s] COVERED
 
[Prereq] Pre 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 1] 1 SQL built-in Functions
Numeric functions - CEIL, FLOOR, ROUND, POWER
String functions - CONCAT, LENGTH, SUBSTR, INSTR, IF
Date functions - Current date, date manipulation, date formatting
The CASE expression, 2 flavors
REGEX functions and expressions

Reading: Chapter 7
 
  2 The SQL SELECT Clauses
The FROM clause
The WHERE clause
The GROUP BY clause
Aggregating and Grouping
The HAVING clause
Finding Duplicate Records
The ROLLUP feature
The ORDER BY clause

Reading: Chapter 4
 
[Week 2] 3 Select Sub-Queries
Using Subqueries for filtering
Using Subqueries as inline views
Using Subqueries as additional derived columns
Correlated Subqueries

Reading: Chapter 6
 
  4 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
Entiry relationships
One-to-one relationship
One-to-many relationship
Many-to-many relationship

Reading: Chapter 8
 
[Week 3] 5 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
Oracle Sequences

Reading: Chapters 9, 10, 15
 
  6 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 4] 7 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
 
  8 What are Stored Procedures
Stored Procedures, Functions & Triggers
Creating Stored Procedures
Calling Stored Procedures
Creating Variables
Global Variables
Creating code blocks
Conditional Testing- IF, CASE
Loops and iterations - LOOP, WHILE, REPEAT
Procedures with non-SELECT statements
Using Unbounded SELECT statements
Using SELECT INTO statements
Using SELECT with cursor statements
Creating Stored Functions

Reading: https://dev.mysql.com/doc/refman/5.7/en/stored-programs-views.html
 
[Week 5]
9 Introduction to PL/SQL
The PL/SQL code block
Creating Named and Anonymous Blocks
Creating Variables
Using %TYPE and %ROWTYPE
Assigning values to variables using SELECT INTO
Conditional Statements - IF, CASE
Loops and Iterations - LOOP, FOR, WHILE
Using Nested Loops
Using DBMS_OUTPUT and other DBMS_ packages

Reading: Chapter 26
 
  10 PL/SQL Functions and Procedures
Calling Functions and Procedures
Nesting Functions and Procedures
Saving and Retrieving Functions and Procedures
Obtaining Compilation Error messages
Creating and Working with Cursors
Implicit and explicit cursors
Declare, Open, Fetch and Close a cursor
Looping through cursors
The cursor FOR loop
Using cursors with Parameters
Nested Loops with nested Cursors

Reading: Chapter 26
 
[Week 6] 11 PL/SQL Exception Processing and Handling
List of named exceptions
Creating and raising your own exceptions
Creating and Using Triggers
Statement-level Triggers, Row-level Triggers
INSTEAD OF Triggers for Views
Collection Types - VARRAY, and Nested TABLE
Loading data into a Collection
Creating Packages

Reading: Chapter 26
 
  12 - Final Exam -



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