IT retraining Week Thirteen ½: First steps with SQL and databases
I'm a bit behind on my training entries, so I'm just going to summarize two weeks now.
Technically, we only had two and a half weeks of SQL and databases by this point, but oh well.
I have to admit, this hasn't been my favorite learning block so far. It's just pretty dry and doesn't leave much room for creativity, in my opinion. At least not in the given format, and probably not in the real world at a job in some company either. But I'll have to get through it. As far as I know, we’ll have a test next week, and then we'll move on to Java.
When we return to Java, we'll learn how to combine it with our newly acquired database knowledge. That will definitely be more interesting, I'm sure.
In the first week with SQL, we got familiar with some new terms and became acquainted with the syntax. We’re using Xampp, which I already know from WordPress development, MariaDB, and HeidiSQL. The first week was pretty easy, but in the second week, a whole bunch of new commands came along, as well as strange things like different primary keys and normal forms. Oh yeah, and then there are the ER diagrams…
I've included some examples here that cover many of the things discussed in class. However, the tables we worked with in class were about customers, wines, libraries, and so on. Of course, I came up with something else – a table about Mass Effect!
PS: Sorry, the comments are in German!
| **Column Name** | **Data Type** | **Description** |
|---------------------|-----------------|--------------------------------------------------|
| galactic_id | CHAR(22) | Unique galactic identifier (Primary Key) |
| crew_member_name | VARCHAR(100) | Name of the crew member |
| birthdate | DATE | Birth date of the crew member |
| join_date | DATE | Date when the crew member joined |
| leave_date | DATE | Date when the crew member left (if applicable) |
| active | BOOLEAN | Indicates if the crew member is currently active |
| salary | DECIMAL(6,2) | Salary of the crew member (credits) |
| role | TEXT | Role of the crew member on the Normandy |
| specialization | TEXT | Specialization or profession of the crew member |
| species | VARCHAR(50) | Species of the crew member |
| loyalty_level | INT | Loyalty level of the crew member (optional) |
| **galactic_id** | **crew_member_name** | **birthdate** | **join_date** | **leave_date** | **active** | **salary** | **role** | **specialization** | **species** | **loyalty_level** |
|------------------------|----------------------|---------------|---------------|----------------|------------|------------|-----------|-----------------------|-------------|------------------|
| N7-Commander-Shepard | Shepard | 2154-04-11 | 2183-10-20 | NULL | TRUE | 10000.00 | Commander | Infiltrator | Human | 100 |
| SPECTRE-Turian | Garrus Vakarian | 2155-06-17 | 2183-10-23 | NULL | TRUE | 8500.00 | Sniper | Turian Security | Turian | 90 |
| Quarian-Machinist | Tali'Zorah | 2161-03-03 | 2183-10-20 | NULL | TRUE | 9000.00 | Engineer | Machinist | Quarian | 95 |
Tabelle erstellen
CREATE DATABASE IF NOT EXISTS MassEffect;
CREATE TABLE normandy_crew (
crew_member_name VARCHAR(100),
crew_id SMALLINT,
birthdate DATE,
contact_info TEXT,
join_date DATE,
leave_date DATE,
active BOOLEAN,
galactic_id CHAR(22),
salary DECIMAL(6,2), /* 6 Zahlen gesamt, 2 Nachkommastellen */
role TEXT,
specialization TEXT
);
Primary Key vergeben
CREATE TABLE normandy_crew (
galactic_id CHAR(22) PRIMARY KEY, -- Primärschlüssel ist jetzt die galaktische ID
crew_member_name VARCHAR(100),
birthdate DATE,
contact_info TEXT,
join_date DATE,
leave_date DATE,
active BOOLEAN,
salary DECIMAL(6,2),
role TEXT,
specialization TEXT
);
Tabelle bearbeiten
-- Tabelle umbenennen
RENAME TABLE normandy_crew
TO normandy_crew_members;
-- Einträge in Tabelle aktualisieren
UPDATE normandy_crew_members
SET role = 'Commander'
WHERE galactic_id = 'N7-Commander-Shepard';
-- Einträge aus Tabelle löschen
DELETE FROM normandy_crew_members
WHERE galactic_id = 'N7-Commander-Shepard';
-- Tabelle löschen
DROP TABLE normandy_crew_members;
-- Tabelleninhalt anzeigen
SELECT * FROM normandy_crew_members;
ALTER_TABLE
-- Spalten hinzufügen
ALTER TABLE normandy_crew_members
ADD species VARCHAR(50);
-- Spalten zu einer Tabelle hinzufügen (weitere Spalte)
ALTER TABLE normandy_crew_members
ADD loyalty_level INT;
-- Spalte umbenennen
ALTER TABLE normandy_crew_members
CHANGE birthdate birth_year DATE;
-- Spalte löschen
ALTER TABLE normandy_crew_members
DROP contact_info;
INSERT INTO
-- ## INSERT INTO
-- Einträge (Zeilen) in die Tabelle einfügen
INSERT INTO normandy_crew_members (
galactic_id,
crew_member_name,
birthdate,
join_date,
active,
salary,
role,
specialization)
VALUES ('N7-Commander-Shepard',
'Shepard',
'2154-04-11',
'2183-10-20',
TRUE,
10000.00,
'Commander',
'Infiltrator'),
('SPECTRE-Turian',
'Garrus Vakarian',
'2155-06-17',
'2183-10-23',
TRUE,
8500.00,
'Sniper',
'Turian Security'),
('Quarian-Machinist',
'Tali\'Zorah',
'2161-03-03',
'2183-10-20',
TRUE,
9000.00,
'Engineer',
'Machinist');
SELECT
-- Crewmitglieder anzeigen, die aktiv sind und zur Spezialgruppe 'Biotic' gehören
SELECT *
FROM normandy_crew_members
WHERE active = TRUE
AND specialization = 'Biotic';
-- Crewmitglieder sortiert nach Gehalt anzeigen, nur wenn das Gehalt kleiner als 5000 ist
SELECT *
FROM normandy_crew_members
WHERE salary < 5000
ORDER BY salary DESC;
-- Crewmitglieder mit einem Gehalt zwischen 7000 und 10000 anzeigen
SELECT *
FROM normandy_crew_members
WHERE salary >= 7000
AND salary <= 10000;
-- Crewmitglieder von Shepard oder mit einem Gehalt über 12000 anzeigen
SELECT *
FROM normandy_crew_members
WHERE salary > 12000
OR crew_member_name = 'Shepard';
Schlüssel vergeben
-- Natürlicher Primärschlüssel
CREATE TABLE IF NOT EXISTS natural_primary_key (
galactic_id CHAR(22) PRIMARY KEY,
crew_member_name VARCHAR(100)
);
INSERT INTO natural_primary_key (galactic_id, crew_member_name)
VALUES
('N7-Commander-Shepard', 'Shepard'),
('SPECTRE-Turian', 'Garrus Vakarian'),
('Quarian-Machinist', 'Tali\'Zorah');
-- Künstlicher Primärschlüssel
CREATE TABLE IF NOT EXISTS artificial_primary_key (
id INT AUTO_INCREMENT PRIMARY KEY,
crew_member_name VARCHAR(100)
);
INSERT INTO artificial_primary_key (crew_member_name)
VALUES
('Shepard'),
('Garrus Vakarian'),
('Tali\'Zorah');
-- Zusammengesetzter Primärschlüssel
CREATE TABLE IF NOT EXISTS composite_primary_key (
crew_id INT,
galactic_id CHAR(22),
PRIMARY KEY (crew_id, galactic_id)
);
-- PK in bestehende Tabelle einfügen
ALTER TABLE normandy_crew_members
ADD PRIMARY KEY (crew_id);
Music: To Die Among Strangers - ROME
Mood: tired