January 4, 2016

Mafia Games Monday (MGM) – Writing SQL 102

Happy new year all!

I hope everyone has enjoyed their holiday. I know I have! However, now that the holidays are over, you are probably heading back to work today, which means that you might need a small refresher in order to get back on your feet!

If you would like a refresher on SQL, the link to that is here. I would suggest reading that again before continuing on with reading if you do not really know what SQL is or how to write it.

Now since that’s out of the way, SQL is great when you want to gather data from a database. But what about putting in some new data or updating older data? For instance, consider the following scenario:

A student (ID 54356) wants to enroll in a new course (ID 589) for the upcoming semester (SEMESTER_VAL 22015). The following are the relational tables below:

STUDENT(ID, LAST_NAME, FIRST_NAME, MIDDLE_INIT, SSN, DOB)
COURSE(ID, NAME, DESCR)
STUDENT_COURSE(SID, CID, SEMESTER_VAL)

So, by enrolling in a new course, the student is adding a new course to their schedule for that term.

This will involve some new syntax:

INSERT: This keyword is to signify that you want to add new data to a table.
VALUES: This keyword is to signify what the values of the data that you would like to insert into said table.

This would be another query. Now, this is what an insert query would look like:

INSERT INTO table VALUES (data1, data2);

So, taking the example, we would want to insert a new row into the STUDENT_COURSE table. The query would look like this:

INSERT INTO STUDENT_COURSE VALUES (54356, 589, 22015);

And there you have it! You now added that course to the student’s schedule. Pretty easy, right?

However, in order to write it this way, you must have all of your data for all of your columns for that table. What if you don’t have that luxury? Well, there’s a solution for that too!

You can write your query like this:

INSERT INTO STUDENT_COURSE (SID, CID, SEMESTER_VAL) VALUES (54356, 589, 22015);

As you can see, the SID will be 54356, the CID will be 589, and SEMESTER_VAL will be 22015. Writing it this way will help you identify what data you’re putting where, which may be a great thing for you to do when you’re just starting out.

What if you want to update data? Well, you will have to wait until my next SQL tutorial, which will be pretty soon!

That’s all for now, until next time…keep that tech gear ticking.

~Goliath7470