My SQL Diary 00 - The Basics

I did not expect the premises of SQL being so simple. It’s fair to say that I was scared of learning it, expecting it to be something incredibly complex and way beyond my understanding, and as of right now, I think I was wrong! However, I’ve only scratched surface, so my view might change soon (although I hope not).

I’ll be making some posts to keep syntax and concepts I find important and or useful.

Basic Syntax

SQL has an intuitive base syntax, the logic is easy to follow. You work with databases, and databases often contain tables. A table will contain rows that will be populated with data. Expected stuff.

CREATE DATABASE arcane;
USE arcane;
CREATE TABLE characters (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE romance (
	wouldidate VARCHAR(255) NOT NULL,
	status VARCHAR(255) NOT NULL,
	char_id INT NOT NULL,
	FOREIGN KEY (char_id) REFERENCES characters(id)
);

INSERT INTO characters (name)
	VALUE ('VI'),('Jinx'),('Caitlyn'),('Viktor'),('Silco'),('Jayce');

INSERT INTO romance (wouldidate, status, char_id)
	VALUES ('Yes', 'Single', 1), ('No', 'Single', 2), ('Yes', 'Single', 3),
	('Yes', 'Single', 4), ('Yes', 'Dead', 5), ('No', 'Taken', 6)

SELECT * FROM romance WHERE wouldidate LIKE '%Yes%';

DELETE * FROM romance WHERE status = 'Dead';
DELETE * FROM characters WHERE id = 5;

SELECT * FROM characters JOIN romance ON characters.id = romance.char_id;

SELECT c.name AS char_name, COUNT(a.id) AS 


SELECT wouldidate, COUNT(wouldidate) AS count
FROM romance
WHERE wouldidate ='Yes'
GROUP BY wouldidate;

On the simplicity of these commands, SQL syntax feels very natural. Will that be kept on more complax applications?

Inner, Left and Right Join

Joining two tables is quite useful, and there’ll be times when you have similar content on the both of them, which you don’t want repeated, or maybe you just want them to have the same ID. You can do that by joining them. Just using JOIN won’t do the trick, that’s why we use RIGHT or LEFT join.

INNER JOIN combines values that are present on both tables, given the parameters of the sentence, the regular JOIN function works in the same way. In the example above, that meant that every character.id that had an equivalent in romance.char_id would be joined.

LEFT JOIN will list everything on the left table, that being the first table cited on the join sentence. And will join that with the matching values on the right table. The difference here being that, even if the left table has values that don’t match the right, they’ll still show up. The same logic aplies to the RIGHT JOIN, only with the inverted direction.

Quite intuitive, isn’t it?

See you next time.