Example script for teaching DDL and CRUD/DML operations

I have taught some SQL to others before, and I thought of making a script that has these attributes, for the purposes of teaching:

-
Fully functional to run on local DB instance with no fuss

-
Easy to follow along

-
Representative examples of real-life types of operations

-
Documented

The example first goes over some Data Definition Language (DDL) operations, including initializing a database and schema, creating tables with various data types, and creating a few simple triggers.

Then it covers fundamental CRUD (or Data Manipulation Language (DDL)) operations.

I would like to know, first of all, am I doing things optimally from a SQL standpoint? I want to make sure I am not accidentally teaching bad habits.

Besides that, do you think this could be improved from a teaching standpoint to make it easier or more clear to a beginner who is interested in learning SQL?

Here is the query:

```
/ Initialize by deleting the database if it already exists, then creating the database: /
use master;
go
if exists (select name from master.dbo.sysdatabases where name = 'ACME_MFG_CO')
drop database ACME_MFG_CO;
go
create database ACME_MFG_CO;
go
/ Switch to our new database, so we can perform operations on it: /
use ACME_MFG_CO;
go
/ Initialize by deleting the schema if it already exists, then creating the schema: /
if (select 1 from sys.schemas where name = 'PRODUCTS') is not null
drop schema PRODUCTS;
go
create schema PRODUCTS;
go

/ Data Definition Language (DDL) operations /

/* The OBJECT_ID built-in function makes the DB engine check system objects by their name as string
* and returns the OBJECT_ID, if found, otherwise null. The following commands will check if
the temporary tables we are about to create already exist, and if they do, they will be dropped/deleted. /
if object_id('ACME_MFG_CO.PRODUCTS.PRODUCT_CATALOG') is not null drop table ACME_MFG_CO.PRODUCTS.PRODUCT_CATALOG;
if object_id('ACME_MFG_CO.PRODUCTS.PRODUCT_DEVELOPMEN

-
Also said before, but I think it worth mentioning again: there is not need for prefix_ for all columns. As far as I know, Hungarian and Leszynski notations are not exactly recommended. Since column names are almost always used in conjunction with their table, the table alias will make the reader known the table for a column.

-
It's a matter of taste, but instead of all-caps table (and other object names), I would use CamelCase identifiers. They are easier to read, more compact (on variable length fonts) and are more friendlier with ORMs (e.g. Entity Framework).

-
Triggers - I know they are part of the language, but I would avoid using for a basic course. I would find defaults more appropriate. Triggers are very tricky objects that should be avoided in most cases, as they: can create various hard to debug side effects, can raise performance problems, are ones of the last objects to look for when looking for the root cause of a problem.

-
Transactions containing a single DML operations: DML operations act as an atomic operation (all or none values are changes), so there is not need for BEGIN / COMMIT TRAN. Illustrating transactions should contain several operations. Also playing with SET XACT_ABORT option is a good idea, as its default value is OFF and some errors may leave the transaction un-aborted (and this can get really ugly, as it might block other SPIDs!)

-
Mark dev completed and add the product from dev to catalog: besides provided code, a MERGE example will be fit. The same thing could be done elegantly using a MERGE, thus having a single statement that executes atomically.

-
(Advanced) Faster delete: after the last DELETE statement, you can also show how to avoid using % at the beginning of the token and getting a full scan of the table:

• Having a separated column for the actual user name, that is populated when a record in inserted.

OR

-
Adding a persisted computed column in table PRODUCTS.PRODUCT_CATALOG that gets the actual user name

• Creating an index for this column

• Performing DELETE using this column

StackExchange Code Review Q#110613, answer score: 9