|
+
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:
- SQL - Structured Query Language.
- DDL - Data Definition Language.
- DML - Data Manipulation Language.
- Database design and data normalization.
- Creating tables and indexes
- MySql and Oracle data Dictionaries
- Oracle PL/SQL
- MySql Procedural Language
BOOKS - (Required / Suggested)
Required Books -
- Teach Yourself SQL in One Hour a Day (Fifth Edition)
- Authors - Ryan Stephens, Ronald Plew & Arie Jones
- Publisher - SAMS
- SQL Bible (Second Edition)
- Author - Alex Kriegel and Boris Trukhnov
- Publisher - Wiley
GRADES AND GRADING POLICY
- Attendance - 10%
- Class Participation - 10%
- Homework - 20%
- Final Exam - 60%
Details of Assignment and Evaluation.
- 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.
- Homework: Homeworks must be submitted on time within 1 week of date assigned.
Late submission will severely impact your homework grade, or may not be accepted altogether at instructor discretion.
- Class Project Presentation: There will be a group/team class presentation.
The presentation will be a culmination of verbal, visual and presentation skills. It will also be the culmination of topics, concepts and competencies learned in this class.
- 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.
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.
To receive your final grade at the end of the semester,
follow these steps:
- click on http://www.sps.nyu.edu/academics/noncredit-offerings/academic-noncredit-policies-and-procedures.html#Obtaining_Grades
- Log into Albert using your net id, at: https://admin.portal.nyu.edu/psp/paprod/EMPLOYEE/EMPL/h/?tab
- Click on "Student Center"
- Within your student center, in the "academics" section click on the dropdown: "other academic"
- From the dropdown select "grades"
- 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