Look Who’s Back
Well, well, well. It’s been roughly one year since my last blog post. Time definitely has its wings. A year out of the program, I can say that I am currently employed — since April 2015 — as a web dev / ui/ux designer for an education startup in the city. The team is quite small, but I enjoy the fact that everyone can bring something different to the table and help build this company with our array of skills.
So really, what am I doing back on this thing-a-ma-blog? I’m sure no one really reads this, so I’m going to be selfish and say this blog is to help me. Me! To document this upcoming 2016 year on how much — or little (yikes!) — I will grow in my professional life and career path. And also to help take notes of what I’ve been learning and refreshing my mind with in terms of web development.
Here’s a quick one for today. I have just revisited the realm of SQL. SQL is a database programming language where data is organized and stored into a bunch of tables — not coffee or dining, but the ones with rows and columns :)
SELECT column(s) FROM (table data is in) WHERE (match case for this clause);
ex: SELECT title FROM movies WHERE title = ‘The Kid’;
SELECT column(s) FROM (table data is in) ORDER BY (the column to be ordered);
ex: SELECT title FROM movies ORDER BY duration DESC;
Q(UICK) TIP: ORDER BY defaults to displaying data by ASCending order, so if you want it DESCending, you have to add the DESC statement.
To select precise data, you can put the AND operator after the WHERE statement.
To select data with different conditions, you can put the OR operator after the WHERE statement.
INSERT INTO tablename (columns(s) we want to add to) VALUES (the actual values you want to add separated by commas);
ex: INSERT INTO movies (id, title) VALUES (5, ‘The Circus’);
Q-TIP: SQL will automatically increment the primary key for a table for new rows. How thoughtful of them.
Q-TIP II: If a field is left blank / unknown data, it’ll have a NULL value as a placeholder
UPDATE tablename SET column name = value needed to change column to WHERE condition to help pinpoint where change will take place;
ex: UPDATE movies SET genre = ‘Romance’, duration = 70 WHERE id = 3 OR id = 5;
DELETE FROM tablename WHERE condition to help pinpoint where change will take place;
ex: DELETE FROM movies WHERE id = 5;
QTIP: if it was just “DELETE FROM movies”, the whole table will be gone!
CREATING / DROPPING NEW DATABASES
CREATE DATABASE name of database;
DROP DATABASE name of database;
→ to create a table in your new database you…
CREATE TABLE tablename (column_name1 datatype, column_name2 datatype, column_name3 datatype);
ex: CREATE TABLE movies (id int, title varchar(20), genre(100), duration int);
ex: DROP TABLE movies;
ALTER TABLE tablename ADD COLUMN new columnname1 datatype;
ex: ALTER TABLE movies ADD COLUMN ratings int;
ex: ALTER TABLE movies DROP COLUMN ratings;
— — — — — — — — — — — — — — — — — — — — — — — — — — — — —
JOINING TWO TABLE
SELECT the classid (on teacher_class) table and email (on user table)
FROM user table and gave it the alias TaniT
JOIN teacher_class table — gave it an alias of t2 — ON t2.teacher equals TaniT.userid
WHERE TaniT.userid = ‘tani’, pulling up my classids and email address.(since both information are located in two separate tables)
JOINING MORE THAN TWO TABLES
SELECT userid(on notification table) and classid (on teacher_class table)
FROM user table and gave it the alias t1
JOIN teacher_class table — gave it the alias t2 — on t2.teacher equals t1.userid
JOIN notification table — gave it the alias t3 — on t3.userid = t1.userid
WHERE t1.userid = ‘tani’