SQL-Basics: Create – Read – Update – Delete

This episode is about the basic statements needed to create, read, update and delete data in a database system.

Let’s assume we work as a data scientist for Knight Industries.  We want to help the Foundation of Law and Government to keep track of our operatives.

We decide to use a classic relational database management system or RDBMS. In order to explore Database Management Systems we can either install one locally or we can use an online tool like SQLFiddle.

To interact with RDBMS we use SQL – the Structured Query Language.

As the name says SQL (speak either S-Q-L or Sequel) is used to write structured queries. Think of “conversations” when You think of “queries”.

So, let’s fire up SQLFiddle.

At first we have an empty database, so we create a table named operatives with the following columns: id, name and birthday

CREATE TABLE operatives 
(id INTEGER, name TEXT, birthday DATE);

So we learned our first SQL statement: CREATE TABLE. It does exactly what its name suggest: create a table in a database.

 

We add Michael Arthur Long to our operatives:

INSERT INTO operatives (id, name, birthday) VALUES 
(1, "Michael Arthur Long", "1949-01-09");

Good practice: Although You could insert the first record into the database without repeating the column names it is considered a good practice to state the names of the columns you want to insert your data into.

We add three more of our employees to the operatives table.

INSERT INTO operatives (id, name, birthday)VALUES 
(2, "Devon Miles", "1942-07-12");
INSERT INTO operatives (id, name, birthday)VALUES 
(3, "Dr. Bonnie Barstow", "1954-11-24");
INSERT INTO operatives (id, name, birthday)VALUES 
(4, "Reginald Cornelius III", "1952-05-04");

Michael Arthur Long gets his new name: Michael Knight. We update his record in our database.

UPDATE operatives SET name = "Michael Knight" WHERE id = 1;

We want to track the different occupations of the team members and add a new column to operatives table.

ALTER TABLE operatives ADD COLUMN occupation TEXT;

Update the occupation of all operatives

UPDATE operatives SET occupation = "Field Agent" WHERE id = 1;
UPDATE operatives SET occupation = "Head of Operations" WHERE id = 2;
UPDATE operatives SET occupation = "Technician" WHERE id = 3;
UPDATE operatives SET occupation = "Truck Driver" WHERE id = 4;

Bonnie left in 1983 to pursue graduate studies. We remove her from operatives.

DELETE FROM operatives WHERE id = 3;

We add April Curtis as Head of Design Team.

INSERT INTO operatives VALUES 
(5, "April Curtis", "1952-05-04", "Senior Design Officer");

To evaluate ebery step of our implementation we can always use:

SELECT * FROM operatives;

My personal road map for learning data science in 2018

I got confused by all the buzzwords: data science, machine learning, deep learning, neural nets, artificial intelligence, big data, and so on and so on.

As an engineer I like to put some structure to the chaos. Inspired by Roadmap: How to Learn Machine Learning in 6 Months and Tetiana Ivanova – How to become a Data Scientist in 6 months a hacker’s approach to career planning I build my own learning road map for this year: Continue reading “My personal road map for learning data science in 2018”

Personal Retrospective 2018

As this year is coming slowly to an end, I like to use this Sunday to experiment with a little personal Retrospective. We already did two good retros at work so I decided to one for myself.

Set the stage

As a method for setting the stage I use Check In-Questions

What do I expect to get out of this Retrospective?

Analyzing good and bad habits

Gather Data

Because I’m not really mad or sad about things happening this year I formulate the questions to: What do I want to

  • Do More
  • Do Less
  • Keep Doing

Here are my items

Do more

  • Exercise
  • Spend time with kids
  • Eat healthier
  • Reading

Do Less

  • Buying books
  • Social Media (FB, 9Gag)

Keep doing

  • Going to barcamps
  • Drink enough water
  • Budgeting with YNAB

Generate insights

As a method to generate insights I choose the 5 Whys.

Let’s start with the do Less list:

I buy so many books that I barely read them cover to cover.

  • Why do I buy so many books? – Because I enjoy reading
  • Why? – Because I like to know more about things
  • Why? – Because I want to have an knowledge advantage at work
  • Why? – Perhaps I fear a job loss
  • Why? – Times are tough in the automotive industry

Decide what to do

Smart goals

Regarding books I will restrict myself to buying one book per month.

Close the Retrospective

I drink a beer 🍺