Practice MS SQL Database with DC Comics Characters

A minimalist oil painting of two indian women, wearing crop tops and shorts, working at a computer store, pastel color palette, retro futuristic setting

As part of my ongoing DP 900 exam prep, I am also practicing some basic, hello world relational database queries. Here, I present a simple ‘dc comics’ style db with heroes and villains.

You can, find the entire query file collection, here, on GitHub.

I do want to add a most important advice, especially if you are playing around with Azure SQL databases, like I always do (instead of using a local DB).

This query.

-- Create the database
--If you are using Azure, this will create a very expensive azure db
--BE CAREFUL ABOUT THE CLOUD DB COSTS
--Or, better to create a free or low cost DB manually in the portal 
-- instead of running this script
-- Create the database
CREATE DATABASE DCComicsDB;
GO

So, be careful when you create new DBs from the server. It tends to create a VERY EXPENSIVE DB on your Azure Server.

I have written more about low cost/free Azure SQL servers, here.

Microsoft Azure ‘Always’ free Database Things

Next, here is the create query (partial)

-- Create tables
CREATE TABLE Superheroes (
    SuperheroID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Alias NVARCHAR(100),
    FirstAppearance DATE,
    Publisher NVARCHAR(50) DEFAULT 'DC Comics'
);

CREATE TABLE Villains (
    VillainID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Alias NVARCHAR(100),
    FirstAppearance DATE,
    Publisher NVARCHAR(50) DEFAULT 'DC Comics'
);

I added a bunch of relationships (partial)

CREATE TABLE SuperheroTeams (
    SuperheroID INT,
    TeamID INT,
    PRIMARY KEY (SuperheroID, TeamID),
    FOREIGN KEY (SuperheroID) REFERENCES Superheroes(SuperheroID),
    FOREIGN KEY (TeamID) REFERENCES Teams(TeamID)
);

CREATE TABLE VillainTeamMemberships (
    VillainID INT,
    VillainTeamID INT,
    PRIMARY KEY (VillainID, VillainTeamID),
    FOREIGN KEY (VillainID) REFERENCES Villains(VillainID),
    FOREIGN KEY (VillainTeamID) REFERENCES VillainTeams(VillainTeamID)
);

bunch of insertions. (partial)

-- Insert sample data into Comics table
INSERT INTO Comics (Title, IssueNumber, ReleaseDate, SuperheroID, VillainID)
VALUES 
    ('Action Comics', 1, '1938-06-01', 1, 1), -- Superman vs Lex Luthor
    ('Detective Comics', 27, '1939-05-01', 2, 2), -- Batman vs Joker
    ('Wonder Woman', 1, '1941-12-01', 3, 4), -- Wonder Woman vs Cheetah
    ('The Flash', 123, '1961-09-01', 4, 6), -- Flash vs Reverse-Flash
    ('Green Lantern', 1, '1959-07-01', 5, 7), -- Green Lantern vs Sinestro
    ('Aquaman', 35, '1967-09-01', 6, 5), -- Aquaman vs Black Manta
    ('Teen Titans', 2, '1980-12-01', 7, 8); -- Cyborg vs Deathstroke

And also some queries (partial)

-- 7. List all superheroes who are part of the 'Justice League'
SELECT s.Name AS SuperheroName
FROM Superheroes s
JOIN SuperheroTeams st ON s.SuperheroID = st.SuperheroID
JOIN Teams t ON st.TeamID = t.TeamID
WHERE t.TeamName = 'Justice League';

-- 7. Retrieve all villains along with the teams they belong to
SELECT v.Name AS VillainName, vt.TeamName
FROM Villains v
JOIN VillainTeamMemberships vtm ON v.VillainID = vtm.VillainID
JOIN VillainTeams vt ON vtm.VillainTeamID = vt.VillainTeamID;

All said and done, I don’t think the DP-900 exam stresses too much on SQL queries, but, I am prepping. Might as well go deep into it.

This post is part of my ‘Azure C Sharp Corporate Training Syllabus’ Project.

Find the GitHub Project Board, here.

Azure C Sharp Corporate Training Syllabus * Jay-study-nildana

Find the Project Repo, here.

https://github.com/Jay-study-nildana/Azure-CSharp-Corp-Trainer-Syllabus

I work as a coding tutor. You can hire me on Upwork, Fiverr and Codementor. You can also book a session on calendly, and visit my website. Also, video tutorials on my YouTube Channel.