The SQL Programming Language

INFO1-CE9272

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:

• The concepts and advantages of databases.
• The relational database model RDBMS.
• SQL - Structured Query Language.
• DDL - Data Definition Language.
• DML - Data Manipulation Language.
• Database design and data normalization.

2. Student Learning Outcomes:

• Learn what databases are and the difference between the various database models
• Appreciate the power and flexibility of the relational database model
• Understand the various terms used in conjunction with SQL
• Learn how to work with SQL as it related to both Oracle and MySql
• Master how to join tables to access data from multiple database tables
• Differentiate between inner and outer joins and when to use each
• Obtain a working knowledge of the various SQL functions
• Become proficient with queries and sub-queries
• Learn how to design databases and how to build databases tables and indexes
• Learn how to insert, update and delete data from database tables
• Become familiar with both Oracle and MySql data dictionary tables

BOOKS - (Required / Suggested)

Required Books -

• Teach Yourself SQL in 24 Hours (7th Edition)
  • Authors - Ryan Stephens
  • Publisher - SAMS

GRADE ASSIGNMENT AND EVALUATION:

• Attendance & Class Participation - 10%
• Homework - 10%
• Final Exam - 80%

• Class Attendance and Participation: To receive full credit for the course, you must attend all classes since much of the learning occurs during class presentation and discussions. Please contact the instructor if you anticipate missing any part of the class. Grades will be based on:
  • Involvement in class activities
  • Participation which demonstrates integration of reading, class work, relevance and application.
  • Willingness to learn by accepting feedback, trying new skills and approaches, etc.
  • Quality/quantity of providing effective and balanced feedback.
  • You must ask at least one question, or respond to professor or student inquiry at least once per session
  
  
• Homework: Homework is always due one week from assignment date.
  • Student should complete the assignment and bring their answers to class the following week.
  • Instructor will ask students to present and discuss their answers prior to start of lecture of the following week.
  • Incompleted homeworks will severly impact your learning experience and overall grade.
  
  
• Final Exam: There will be a final exam. The exam will be an open book, open notes style exam. The exam will test the student's acquisition of topics, concepts and competencies learned in this class.

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.

• Non-Evaluative Grade Request
• Pass/Fail Grade Request

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 -