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: Mondays 10/3 - 12/12
Course Hours: 6:00pm - 9:00pm
This course will be delivered online via Zoom software

Announcement(s):
  • 11/6/2022 - No class Monday 11/14

  • + syllabus
    + course outline
    + grades policy
    + student listing
    + examples & demos
    + homework submission
    + 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) - 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, SQLite, or PostgreSQL.


    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 -

    Suggested Books -

    GRADES AND GRADING POLICY


    Details of Assignment and 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

    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: 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. Log into Albert using your net id, at: https://admin.portal.nyu.edu/psp/paprod/EMPLOYEE/EMPL/h/?tab
    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"
    5. For complete instructions click http://www.sps.nyu.edu/academics/noncredit-offerings/academic-noncredit-policies-and-procedures.html#Obtaining_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