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
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.
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_DEVELOPMENT') is not null drop table ACME_MFG_CO.PRODUCTS.PRODUCT_DEVELOPMENT; go /* We create two tables which we will use throughout the examples in this script: */ create table PRODUCTS.PRODUCT_CATALOG ( pc_id int identity(1,1), /* Empty string if product code not provided, since we cannot use a nullable field as part of the PRIMARY KEY: */ pc_code varchar(50) not null default '', pc_name varchar(200) not null, /* In this case it is preferable to have a value stating that there is no description, rather than an empty field * (for example to be displayed to users in a web page or application): */ pc_description varchar(max) default 'No description found.', pc_active bit, /* Product will be marked as discontinued = FALSE by default, since we rarely will be adding discontinued products to the catalog: */ pc_discontinued bit default 0, /* Default to null as the MFG Cost might not be known yet when product is first added: */ pc_mfgcost decimal(12,4) null default null, /* Default to null as the Retail Price might not be decided yet when product is first added: */ pc_retailprice decimal(12,4) null default null, pc_insertdate datetime not null default getdate(), /* Unless specified otherwise this defaults to the user name/ID of the SPID that executes the insert: */ pc_insertedby varchar(100) not null default CURRENT_USER, /* This will be updated via a trigger on record updates: */ pc_lastupdate datetime, pc_lastupdatedby varchar(100), /* Unique binary ID */ pc_uniqueid uniqueidentifier default newid(), primary key (pc_id, pc_code, pc_name, pc_uniqueid), unique (pc_code, pc_name) ); go create table PRODUCTS.PRODUCT_DEVELOPMENT ( pd_id int identity(1,1), pd_name varchar(200) not null, pd_goal varchar(max) not null, pd_engineer varchar(200), pd_marketingcontact varchar(200), pd_devcompletedate datetime, pd_insertdate datetime default getdate(), /* This will be updated via a trigger on record updates: */ pd_lastupdate datetime, pd_lastupdatedby varchar(100), pd_uniqueid uniqueidentifier default newid(), primary key (pd_id, pd_name, pd_uniqueid), unique (pd_name) ); go create trigger PRODUCTS.tr_PRODUCT_CATALOG_update on PRODUCTS.PRODUCT_CATALOG for update as begin; update pc set pc_lastupdate = getdate(), pc_lastupdatedby = CURRENT_USER from PRODUCTS.PRODUCT_CATALOG as pc inner join inserted as i on pc.pc_uniqueid = i.pc_uniqueid; end; go create trigger PRODUCTS.tr_PRODUCT_DEVELOPMENT_update on PRODUCTS.PRODUCT_DEVELOPMENT for update as begin; update pd set pd_lastupdate = getdate(), pd_lastupdatedby = CURRENT_USER from PRODUCTS.PRODUCT_DEVELOPMENT as pd inner join inserted as i on pd.pd_uniqueid = i.pd_uniqueid; end; go /***** Data Manipulation Language (DML) operations *****/ /*Add existing catalog products:*/ begin transaction; insert into PRODUCTS.PRODUCT_CATALOG (pc_code, pc_name, pc_description, pc_active, pc_discontinued, pc_mfgcost, pc_retailprice) values ('ABC-123', 'Foo Product', 'Our customers'' favorite!', 1, 0, 42.1234, 89.99), ('DEF-345', 'Bar Product', 'A low-fat alternative to Foo Product!', 1, 0, 45.6789, 95.99), ('XYZ-987', 'Awful Product', 'This was a terrible idea.', 1, 0, 741.4555, 299.99); commit transaction; select * from PRODUCTS.PRODUCT_CATALOG; /*Add a new product to development with basic information:*/ begin transaction; insert into PRODUCTS.PRODUCT_DEVELOPMENT (pd_name, pd_goal, pd_engineer, pd_marketingcontact, pd_insertdate, pd_lastupdate) select [pd_name] = 'FooBaz Product-DEV', [pd_goal] = 'To make an upgraded version of Foo Product with added features X and Y.', [pd_engineer] = 'Harold E., PHD', [pd_marketingcontact] = 'Maria M., VP Sales', [pd_insertdate] = dateadd(month, -2, getdate()), --inserted 2 months ago [pd_lastupdate] = dateadd( day, -4, dateadd(week, -3, getdate()) ); --updated 3 weeks and 4 days ago (two separate operations) commit transaction; select * from PRODUCTS.PRODUCT_DEVELOPMENT; /*Mark dev completed and add the product from dev to catalog:*/ begin transaction; declare @targetProduct varchar(200) = 'FooBaz Product-DEV'; if not exists ( select 1 from PRODUCTS.PRODUCT_CATALOG as pc inner join PRODUCTS.PRODUCT_DEVELOPMENT as pd on pc.pc_uniqueid = pd.pd_uniqueid where pd.pd_name = @targetProduct ) begin; update PRODUCTS.PRODUCT_DEVELOPMENT set pd_devcompletedate = getdate() where pd_name = @targetProduct; insert into PRODUCTS.PRODUCT_CATALOG (pc_name, pc_insertedby, pc_uniqueid) select replace(pd_name, '-DEV', ''), --remove the '-DEV' marker from name pd_marketingcontact, pd_uniqueid from PRODUCTS.PRODUCT_DEVELOPMENT where pd_name = @targetProduct; commit transaction; end; else begin; select 'Product ' + @targetProduct + ' already exists in PRODUCTS.PRODUCT_CATALOG' as [NOTICE]; print 'Product ' + @targetProduct + ' already exists in PRODUCTS.PRODUCT_CATALOG'; rollback transaction; end; select * from PRODUCTS.PRODUCT_CATALOG as pc left join PRODUCTS.PRODUCT_DEVELOPMENT as pd on pc.pc_uniqueid = pd.pd_uniqueid; /*Later on, update the new product with more information:*/ begin transaction; update PRODUCTS.PRODUCT_CATALOG set pc_code = 'GHJ-789', pc_name = 'FooBaz Product', pc_description = 'Our newest product in the FooBar series!', pc_active = 1, pc_mfgcost = 97.9876, pc_retailprice = 139.99 where pc_name = 'FooBaz Product'; commit transaction; select * from PRODUCTS.PRODUCT_CATALOG order by pc_lastupdate desc; /*Inactivate and discontinue unprofitable products:*/ begin transaction; update PRODUCTS.PRODUCT_CATALOG set pc_active = 0, pc_discontinued = 1 where pc_mfgcost > pc_retailprice; commit transaction; select * from PRODUCTS.PRODUCT_CATALOG order by pc_lastupdate desc; /*Some application user playing a joke on us...*/ begin transaction; insert into PRODUCTS.PRODUCT_CATALOG (pc_code, pc_name, pc_description, pc_insertedby) values ('HAHAHAHA', 'GARBAGE!!', 'Eat my shorts.', 'ApplicationLoginSession\20151110\JoeSpammer'); commit transaction; select * from PRODUCTS.PRODUCT_CATALOG order by pc_insertdate desc; /*Our application admins blocked their account, now let's clean up after this spammer:*/ begin transaction; delete from PRODUCTS.PRODUCT_CATALOG where pc_insertedby like '%\JoeSpammer'; --using the % wildcard in place of any number of any characters before the user name commit transaction; select * from PRODUCTS.PRODUCT_CATALOG order by pc_insertdate desc;
After the whole query is executed (takes approximately 1-2 seconds) it displays the 7 individual result sets that show the various operations we made, like so: