SQL Data Analysis Programming for Beginners

INTRODUCTION ........................................................................... 6

CHAPTER 1: WHAT IS SQL?......................................................... 8

1.1 WHY SQL?..............................................................................9

1.2 A BRIEF HISTORY OF SQL.........................................................10

CHAPTER 2 - HOW SQL WORKS?.............................................. 11

2.1 SQL PROCESS........................................................................11

2.2 SQL COMMANDS ..................................................................12

2.3 SQL - RDBMS CONCEPTS ......................................................14

2.4 SQL - RDBMS DATABASES......................................................19

2.5 SQL SYNTAX ..........................................................................24

2.6 SQL OPERATORS....................................................................31

CHAPTER 3 - BASIC RULES AND PRACTICAL APPLICATION ... 36

3.1 DATABASE EXAMPLE FOR BASIC STATEMENTS .............................36

3.2 SELECT.................................................................................40

3.3 LIMIT ....................................................................................41

3.4 ORDER BY ...........................................................................44

3.5 WHERE.................................................................................48

3.6 AGGREGATE FUNCTIONS.........................................................54

3.7 GROUP BY...........................................................................55

3.8 ARITHMETIC OPERATORS .........................................................59

3.9 JOIN ....................................................................................60

CHAPTER 4 - MAKE YOUR OWN SQL DATABASE..................... 69

4.1 INSTALL AND SET UP SQL.........................................................69

4.2 CREATING A NEW DATABASE USING THE CREATE DATABASE STATEMENT ..........................76

4.3 CREATING A DATABASE USING SQL SERVER MANAGEMENT STUDIO ...................................78

4.4 CREATING A DATABASE USING TRANSACT-SQL.........................83

CHAPTER 5 - MISTAKES TO AVOID WITH SQL .......................... 85

5.1 NULLS AND THE NOT IN PREDICATE .........................................86

5.2 FUNCTIONS ON INDEXED COLUMNS IN PREDICATES .....................90

5.3 INCORRECT SUBQUERY COLUMN ..............................................93

5.4 DATA TYPE MISMATCH IN PREDICATES........................................96

5.5 PREDICATE EVALUATION ORDER ...............................................98

5.6 OUTER JOINS AND PLACEMENT OF PREDICATES ....................... 102

5.7 SUBQUERIES THAT RETURN MORE THAN ONE VALUE................... 109

5.8 USE OF SELECT * ................................................................ 111

5.9 SCALAR USER-DEFINED FUNCTIONS ........................................ 113

5.10 OVERUSE OF CURSORS ....................................................... 115

CONCLUSION.......................................................................... 119