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_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:
