[DRAFT]

Planning SQL Course

This list helps students to plan their SQL self-training. Introduction and good external links are also available here for each topic .

"adv:" means advanced sub-topics

database operations (create, alter, delete, drop, truncate, select, insert ...........)

Basic Topics

Database objects::::::::::::
0) database basics

introduction to various db objects: database objects (Database, Tables , Views Stored procedures, Triggers, Functions, Constraints (PK, FK, UK, DF, NULL...), Indexes, Statistics...)

DDL DML

Variables (declaring, assigning value, scope)
Constants http://www.sqlservercentral.com/articles/T-SQL/61244/
Data types
1) Tables (create, alter, drop, insert, update, delete, truncate operations)
identity property basics
adv: identity property advanced topics (various related functions, the behaviour of identity column on delete, truncate, insert, update operations)
adv: identity_insert
) select statement
from clause
where clause
order by clause
Aliases
Group by clause
Having clause
Operators used in where clause:
AND
OR
In
Between
=, <, >, <>
IS, NOT, EXISTS,
IN
) Insert statement
with list of values
with results from a select statement
adv: complex insert having joins
) Update statement
simple update
updating multiple columns in a single query
adv: complex insert having joins
) Delete

simple delete

adv: complex delete having joins
) Truncate
truncate vs delete
truncate vs delete vs drop

6) Constraints (PK, FK, UK, DF, NULL.......)
types of constraints

significance of constraints while operating on parent-child tables.

8) system functions and exercises on system functions (to test if the student is able to pick the right function for the right purpose)

String Functions

Date Functions

Aggrigate Functions

Exercises on system functions

* print the statements like this (given the DOB as input and Name)
My Birthday is on <date>

My Birthday falls on <week day>

My Birthday is on <day>th of <month>

This year my birthday is on <week day>

My age is <age>

My first name is (using sub string)

My last name is (using sub string)

My middle name is (using sub string)

Length of my name is <length>

9) Sub-queries

exercises on Aggregate Functions: running total and a running count using sub-queries

10) JOINS (self, cross, inner, left outer, right outer)

Various types of joins and their applications

Alternative queries to various types of joins

JOIN vs IN vs EXISTS: http://mrdee.blogspot.com/2007/05/sql-server-join-vs-in-vs-exists-logical.html

2) Views
create view
adv: various operations on view and their effect on the underlying tables
adv: schema binding
adv: cascading property

adv: system views

Exercises on System Views:

1) write a query to list all tables in the database which are having column name starting with 'emp'
2) write a query to list all tables and their columns in the database which are having int datatype column

3) Write a SP that generates the insert

3) Stored procedures

4) Triggers
trigger basics

types of events which can be triggered

virtual tables

Types of triggers

disabling and enabling a trigger

adv: trigger settings (db level and server level)

5) Functions
basic system functions (string functions, aggregate functions, ...)

functions basics

Write simple functions

* to add 2 values

* to find the reminder of the division

* to find the quotient of the division

Write moderate complex functions

* to return 'N' Perfect Numbers

Intermediate topics:

) Basics topics that are marked as "Adv:"

) user defined datatypes
) Union, Union ALL
) dynamic SQL
) Select Into vs Insert into
) Database (create, drop ... operations)

Assignment: design database for telephone bill loading system (example use my reliance mobile bill)

Question on Database Design: How can we keep track of inserts and updates of records in the database... like who inserted the record, who updated the record, when is this record inserted, when is this record
) cross db queries
) Corelated subqueries
) delete vs drop vs truncate

) DISTINCT vs GROUP BY

* www.sqlmag.com/Article/ArticleID/24282/sql_server_24282.html
* http://blog.sqlauthority.com/2007/03/29/sql-server-difference-between-distinct-and-group-by-distinct-vs-group-by/

) Admin: Backup & Resore

) Error handling exercises:
modify the following previous code to include error handling
generate Prime numbers
generate Perfect numbers
generate maths tables

Advanced topics:
1) Indexes

2) Performance tuning
3) Statistics
4) cascaded views and other advanced topics related to views
5) linked server

6) COLLATE