University Database

I am building a database for a hypothetical university. This is my SQL definition, with generated data added for my future use, such as demonstrating queries:

```
Create Table Semesters (
Semester varchar(6) Primary Key Not Null,
);

Insert Into UniversityDatabase.dbo.Semesters
Values ('Spring'),
('Summer'),
('Fall');

Create Table Grades (
Grade_Letter char(1) Primary Key,
);

Insert Into UniversityDatabase.dbo.Grades
Values ('A'),
('B'),
('C'),
('D'),
('F'),
('S'),
('N');

Create Table LectureSeries (
Lecture_Series char(3) Primary Key Not Null,
);

Insert Into UniversityDatabase.dbo.LectureSeries
Values ('001'),
('002'),
('003'),
('E90'),
('E91'),
('E92');

Create Table Departments (
Department_Code varchar(4) Primary Key,
Department_Name varchar(30) Unique Not Null,
);

Insert Into UniversityDatabase.dbo.Departments
Values ('ECON', 'Economics'),
('PHYS', 'Physics'),
('FIN', 'Finance'),
('LEGL', 'Legal'),
('CHEM', 'Chemistry'),
('CS', 'Computer Science'),
('PSY', 'Psychology'),
('PHIL', 'Philosophy');

Create Table [Degrees] (
Degree_ID int Identity(10000,1) Primary Key,
Department varchar(4) Foreign Key References Departments(Department_Code) Not Null,
[Description] varchar(200) Not Null,
);

Insert Into UniversityDatabase.dbo.[Degrees]
Values ('ECON', 'Economics BS'),
('PHYS', 'Physics BS'),
('FIN', 'Business Finance BA'),
('LEGL', 'Constitutional Law BA'),
('CHEM', 'Chemistry BS'),
('CS', 'Computer Engineering BS'),
('PSY', 'Psychology BS'),
('PHIL', 'Philosophy BA');

Create Table Instructors (
Instructor_ID int Identity(10000,1) Primary Key,
FName nvarchar(200) Not Null,
LName nvarchar(200) Not Null,
Year_Hired int Not Null,
);

Insert Into UniversityDatabase.dbo.Instructors
Values ('Bill', 'Smith', 1995),
('James',

Smaller issues

Full referencing of tables

There is a consistent inconsistency in your script.

Create Table Semesters (
    --...
);

Insert Into UniversityDatabase.dbo.Semesters
Values --...

On the one hand, you are creating the table without any reference, and the other you are fully referencing it while inserting. Perhaps a better way of doing this is declaring the database catalog you want to use at the top, and then just reference the schema and table. SQL Server will always use that database catalog until it encounters another USE statement with a different database.

Use UniversityDatabase;
GO
Create Table dbo.Semesters (
    --...
);

Insert Into dbo.Semesters
Values --...

This is pretty minor, but it helps improve DRY as well as possible ambiguity if you have multiple schemas, which is not uncommon. The only time fully-qualified references are needed are when querying across database catalogs (fairly common), or across linked server instances (much more rare, and there are usually better solutions).

Implicit Inserts

You are using implicit Inserts without referencing columns, which is not only more error prone, as it relies on the column IDs and the order they are in, which can break if columns are added, removed, etc. (which can and does happen in real environments).

It also makes the code harder to read, as you need to go back to the table definition to know for sure what columns the data is getting inserted in. So, instead of doing this:

Insert Into dbo.Students
Values ('Jack', 'Johnson', 10005, null),
       ('Dolly', 'Denver', 10000, 2.89),
       --...

It is better to do this:

Insert Into dbo.Students
    (FName, LName, Degree, Current_GPA)
Values 
    ('Jack', 'Johnson', 10005, null),
    ('Dolly', 'Denver', 10000, 2.89),
    --...

This will become especially important as you start working with bigger scripts like stored procedures and such, that can insert data in dozens of rows, and not necessarily all in one operation.

FName, LName

I think your naming overall is quite good. But I would change these to be First_Name and Last_Name just to be consistent, and make it read easier. This will save you some annoying aliasing in everyday queries when you're sending data sets (reports etc.) to non-IT people who may think FName and LName are not very user-friendly, or you always having to do:

Select 
  FName As First_Name, 
  LName As Last_Name
From dbo.Students
Order By LName Asc, FName Asc;

Bigger issues

Auto-commit mode

You are currently running the script in auto-commit mode, which means that you're leaving it up to the SQL engine to decide on your behalf when to begin and commit transactions. While it may do a fine job at it usually, imagine if you had an error (say a key violation or data-type error) halfway down your script, chances are it has already committed the operations that came before that part. In a small script like this, in a "virgin" database, this may not be a big deal, just delete the database and start over. But on a production environment, many tables have triggers that fire once for example an insert transaction is committed, which then can be extremely frustrating and time-consuming to undo.

So, ideally, at least wrap the whole script into one transaction. More details coming in a minute.

Use UniversityDatabase;
GO
Begin Transaction;
  --do all the work you need
Commit Transaction;

No error handling

Ideally, we would want to handle the transaction differently depending on whether or not there are errors/problems during it. Good news is that SQL supports TRY/CATCH operations...

Begin Transaction;
Begin Try
    Insert Into Foo (Bar) Values ('Hello'), ('World');
    --whatever other work you need done
    Commit Transaction;
End Try
Begin Catch
    Rollback Transaction;
    --Get information about the error:
    Select
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
End Catch

So this way, not only will it rollback the transaction if something goes wrong, but also will give you all the details about why it failed. In bigger scripts you can also use RAISERROR() (not a typo, only one "E") to define your own error handling, like you would in traditional programming. For example:

```
Begin Try
If (1=0)
Begin
Declare
@ErrMessage Varchar(200) = 'Something went wrong',
@ErrSeverity Int = 16, --Severity from 11 to 20 redirects to Catch block
@ErrState Int = 1; --Multiple states from 0-255 can be used in different areas of the script, to help identify errors in specific sections
Raiserror(
@ErrMessage,
@ErrSeverity,
@ErrState
);
End
End Try
Begin Catch
--Goes here after Raiserror is encountered in If statement, with severity 11-20
E

StackExchange Code Review Q#111430, answer score: 2