Course: Learn Databases from Developer Perspective

admin

Scripts used in Videos are available here on github

1- Introduction to Databases

  • What is a data store
  • What is a database
  • Basic data types
  • What is DBMS
  • Examples of RDMS

2- Data Anomalies

  • Issues because of anomalies (redundancy, inconsistency)
  • Insert anomaly
  • Update anomaly
  • Delete anomaly

3- Candidate Keys + Entity Relationships

In this video we’ll learn

  • What is a key?
  • What is candidate key?
  • What is primary key?
  • What is unique key?
  • Foreign Key
  • What is entity?
  • Relationships between entities
    • one to one
    • one to many
    • many to many

4- Normalization

To avoid “redundancy” and “inconsistency” in data, we do “normalization” of our DB schema. In normalization, we may break our one table into more tables. To to use “reference” keys instead of actual data. Normalization has bad impact on performance as when we retrieve data, we may have to use joins to get data from multiple tables. You don’t need to memorize these normal forms. Just understand try to practice (in excel sheet). Just try to visualize what issue may occur if you have specific column in specific tables etc.

5- Introduction to SQL Server

  • Introduction to SQL Server
  • Introduction to SQL Server Engine
  • Introduction to SQL Server Management Studio
  • Introduction to SQL Server Authentication Modes
  • How to enable Mix authentication mode
  • How to enable and set “sa” password

SQL Server Installation

How to Install SQL Server: Download PDF tutorial from following link

6- Tables creation using Management Studio

  • How to create a DB using SQL Server Management Studio
  • How to create a table using SQL Server Management Studio
  • How to add data in a table using SQL Server Management Studio

7- Data Types in SQL Server

  • Data Types in SQL Server
  • Main categories
  • Uni Code data types vs. Non-Uni Code
  • Fixed vs. dynamic data types
  • Precision vs Scale

8- Introduction to SELECT statement

  • Introduction to SQL (Standard Query Language)
  • Understanding of SELECT statement
  • Columns (Projection), Columns Alias
  • Where Clause
  • Different mathematical Operators
  • IN Operator
  • Between Operator
  • NOT, AND, OR Keywords
  • LIKE Operator

9- SQL Statements Syntax

  • SQL Statements (DDL, DML, DCL, TCL)
  • Syntax of DDL Statements( CREATE, ALTER, DROP, TRUNCATE)
  • Syntax of DML Statements (SELECT, INSERT, UPDATE, DELETE)

10- DDL/DML Examples

In this video, we’ll learn DDL + DML statements with examples in SQL Server.

11- Group Functions

  • USE Statement
  • Identity Column
  • Order by ASC, DESC
  • Group Functions (Sum, Min, Max, AVG, Count)
  • Group By clause
  • Having clause

12- Introduction to Joins

  • Introduction to JOINS
    • INNER JOIN
    • OUTER JOIN (LEFT, RIGHT, FULL)
    • SELF JOIN

13- Examples of Joins

  • Introduction to JOINS
  • – INNER JOIN
  • – OUTER JOIN (LEFT, RIGHT, FULL)
  • – SELF JOIN
  • – CROSS JOIN
  • – Multiple Joins
  • – Joins with group by clause in query

14- Introduction to T-SQL (Transact SQL)

In this video, we’ll explore following concepts

  • Variable Declaration
  • If else
  • Table type variable
  • While Loop
  • getdate() function
  • cast() function
  • convert() function

15- Stored Procedures

In this video, we’ll learn about stored procedures. How can you create/alter/drop/execute a procedure. Procedures are useful in encapsulating a logic in a block and increase re-usability. Procedures are pre-compiled and most of the time provides better performance as compared to executing direct queries.

16- Functions in SQL Server

In SQL Server, we’ve another construct like ‘Stored Procedure’ and that is Function. There are differences between function & stored procedures. In this video, we’ll explore how to create a function in SQL server and their different types.

17- Temporary Tables in SQL Server

– Introduction to Temp Tables
— Local Temp Tables (#)
— Global Temp Tables (##)
— Table Variables (@)
– User Defined Types
– Passing Table valued Parameter
– sp_help Stored Procedure
– GO statement

18- Views

– CREATE, UPDATE, DROP View
– Indexed (Materialized) Views
– System Defined Views
— Information_Schema
— sys

19- Constraints

Constraints:
——————————–
ISNULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
INDEX

20- Triggers

Triggers
– DML Triggers
– DDL Triggers
– LOGON Triggers
Computed Column

21- Clustered Indexes vs. Non-Clustered Indexes

– Indexes in Books
– Indexed data in Dictionary, Telephone Directory
– Indexes in databases (Clustered Index, Non-Clustered Index)
– Heap table vs. Index Table
– CREATE/DROP Clustered Index
– CREATE/DROP non-Clustered Index

22- Sub Query Derived Table CTE

In this video, we’ll learn following concepts

  • Sub Query
  • Derived Table
  • CTE (Common Table Expression)
  • CTE Recursive

23- Row Number, Rank, Dense Rank, Partition By

In this video, we’ll learn following concepts

— Row_Number, Over
— Rank, Over
— dense_Rank, Over
— Row_Number, Partition
— Row_Number, Partition with CTE
— OFFSET, FETCH
— sp_who

24- Miscellaneous

In this video, we’ll learn following concepts

— Exists
— Dynamic Query
— TOP, DISTINCT
— UNION
— UNION ALL
— INTERSECT
— EXCEPT
— Introduction to full text search

25- Workflow System Example – Part 1

In this (2 part video series), we’ll see a full example of designing & programming a work flow system. We’ll start with understanding the “Workflow System” requirements. Then we’ll design a DB with some initial data. Then we’ll do programming in stored procedures to handle following three cases

  1. Creating a New Request
  2. Approve or Reject a request (In next video)
  3. Search Requests (In next video)

You will see the usage of different DB constructs we learnt in previous videos. If you feel any confusion, Please check relevant video again to revise the concept. Don’t forget to share your questions or feedback on this video.

26- Workflow System Example – Part 2

This is 2nd part of (2-part video series) on a real time example. In this video we’ll work on following 2 cases

  • Approve or Reject a request
  • Search Requests

27- Schedule Management System Example

For employees, We want to manage their working schedules (9-6) + activities (Lunch, Break, Training etc.) in database. So we’ll learn how we can design a DB for this.
Then in other task, we’ll see an external database which has data in different format. We’ll learn how to move that data in our database.

28- Analysis of Elections 2013 data – Example

General Elections of Pakistan happened in 2013. This data is available on election commission of Pakistan web site. There is “National Assembly” of Pakistan and then there are four “Provincial Assemblies”. Whole Pakistan is divided into different constituencies (total 272 NA seats/areas). Each Province is also divided into different provincial constituencies.

  • Punjab (PP) = 297 seats
  • Sindh (PS) = 130 seats
  • Khaybar Bakhut Khawan = 99 seats
  • Baluchistan = 51 seats

In this video, we’ll do different analysis on votes data polled in 2013 elections to see usage of different DB constructs.

Script files are available here. Execute the script file in order (0, 1, 2, 3, 4). 28_ file contains the queries for analysis.

29- Introduction to Transactions

Whenever you execute an SQL query (statement), it is executed as a (single statement) Transaction. In this video, we’ll learn

  • What are DB Transactions
  • What are ACID Properties
  • What are Isolation Levels

Excel file can be found here.

30- Transaction Isolation Levels in SQL Server

In this video, we’ll revise our learning regarding isolation levels and will learn them with examples in SQL Server.

31- SQL Server Management Tips

In this video, we’ll look quickly some features of SQL Server Management Studio

  • How to take backup
  • How to restore a backup
  • How to generate scripts for database objects
  • How to Import or Export Data

32- Tips for DB Design (Performance)

In this video, we’ll see some tips regarding how to design database to achieve better performance. We’ll see following points

  • Use of data types carefully
    • For Example (smallint, int, bigint, char vs nchar, varchar vs. nvarchar, date or datetime)
  • Use of Constraints carefully
    • Check Constraint, Unique Constraint, Foreign key constraints
  • Use of indexes
    • For Example Which column should have clustered, which columns should have non-clustered index, covering index. XML index etc.
  • Use of indexed (Materialized) views
  • Avoid too much normalization
  • Use of Denormalized table
  • Summary table creation (hierarchy based table creation)
  • Archiving (Moving to different tables, databases, Data Purging)
  • Soft Deletion, Hard deletion
  • Soft Foreign key constraints, Avoid Extra constraints
  • Usage of Temp Tables
  • Usage of Triggers
  • Audit Columns data (CreatedOn,CreatedBy,ModifiedOn, ModifiedBy, IsActive)
  • Awareness about DBMS features

Leave a Reply