15
\$\begingroup\$

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:

PRODUCTS EXAMPLE QUERY RESULTS

\$\endgroup\$
5
  • 2
    \$\begingroup\$ I would personally use UPPER CASE for the keywords; however that is likely to start a holy war, so perhaps not. \$\endgroup\$ Nov 12, 2015 at 23:01
  • 3
    \$\begingroup\$ When defining primary keys, unique constraints etc, you probably should specify the name of the constraint. \$\endgroup\$ Nov 12, 2015 at 23:03
  • 1
    \$\begingroup\$ Depending on the SQL version you might want to read up on datetime2 and what max said is somewhat consensus. At least you got the semicolon right \$\endgroup\$
    – Tom V
    Nov 15, 2015 at 20:48
  • 1
    \$\begingroup\$ Oh god @Phrancis! Why did you prefix all of the fields with an abbreviation of their table name?! Why? Maybe it's just a pet peeve of mine, but I find it terribly redundant. Of course, you were consistent, so that counts for something at least. \$\endgroup\$
    – RubberDuck
    Nov 26, 2015 at 11:48
  • \$\begingroup\$ @RubberDuck I could lie and say that I thought it was a good practice, but the reality of it is that it is what I'm used to at work, and I wrote this to teach to a friend who wanted to apply. But yes I agree, it seems redundant. On the flip side, at least it makes table aliasing easy lol. \$\endgroup\$
    – Phrancis
    Nov 26, 2015 at 14:01

1 Answer 1

9
+50
\$\begingroup\$
  1. 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.

  2. 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).

  3. 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.

  4. 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!)

  5. 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.

  6. (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
\$\endgroup\$
2
  • 3
    \$\begingroup\$ Welcome to Code Review! Good job on your first answer. \$\endgroup\$
    – SirPython
    Nov 30, 2015 at 19:26
  • 3
    \$\begingroup\$ @Alexei very good answer and lots of great points! \$\endgroup\$
    – Phrancis
    Dec 1, 2015 at 4:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.