SQL Cheat Sheet

If you're new to SQL, or just can't find the right query, or don't understand how to put one together, I hope these examples might help out. As I work on various database-related projects, I'll post here examples that go beyond just the basic SELECT statements. It'll help me as much as anyone else, because I have a horrible habit of forgetting syntax for anything I don't work with day in and day out.

Filter results by using IN(SELECT...)

There's a table that lists a whole bunch of courses. Not all of them are managed in our course management system. I want to grab only the users associated with courses that are managed by our course management system:

SELECT course_id, lab_id, uid
FROM univ_enrollment
WHERE course_id IN
(SELECT DISTINCT course_idnumber FROM dept_courses)

In other words, "I want to know which users are in which lab sessions, but only if the university course id matches the course management system ID." So if the University keeps track of these mappings for 500 courses, but I only care about 5 of them, that's all I'll get information for with this query.

INSERT based on SELECT statement

This is a query I used in a project that automated the process of updating user->group associations in Moodle (a PHP course management system) without writing any PHP. It's an INSERT statement. The only non-Moodle entity here is v_enrollment, which is a view of registration data maintained centrally, but outside of moodle. Oddly, Moodle can automatically enroll students in *courses* by looking at external data, but not groups.

Anyway, you can work up to these complex INSERT queries. Start by getting a SELECT statement that returns a dataset that looks exactly like the data you want to populate. Once you have that, just wrap it in an INSERT statement. Then, you should also remember that you can test your statement by just ripping out the SELECT portion of the whole thing and running just that portion of it by itself.

INSERT INTO groups_members (userid,groupid) (SELECT u.id, g.id
FROM user AS u, groups AS g, v_enrollment AS v, course AS c
WHERE v.coursename = c.idnumber
AND g.name = v.precept
AND u.username = v.id
AND g.courseid = c.id
AND v.precept IS NOT NULL

UPDATE one table based on content in another

The above query takes care of new users who have not yet been associated with groups in Moodle. However, there's also the possibility that users will switch groups. A PHP coder would probably either a) place the new and old datasets entirely into memory and do some sort of merge operation, or b) dump the data and recreate it all using the authoritative data after checking to see if there were any changes. SQL handles this with almost zero code:


UPDATE groups_members AS gm, groups_pending_changes AS gc SET gm.groupid = gc.newgid
WHERE (gm.userid = gc.uid AND gm.groupid = gc.oldgid AND gc.oldgid IS NOT NULL)

OK, so I kinda cheated. The 'groups_pending_changes' entity is a view I created from some external data as well, but that itself was only 4 lines of SQL. So, 6 lines of SQL takes care of something was initially sifting through hundreds of lines of PHP just to figure out how Moodle handles all of this. Meanwhile, it turns out that a group->user association is defined with a single record in a single table.

Updating a substring of a column

It had been requested that I change all email addresses for all users to point at 'mydomain.com' instead of 'sub.mydomain.com'. This means changing the 'email' column of the user table... but not the whole thing. Here's what I did:

UPDATE user SET email = (REPLACE(email, '@sub.mydomain.com','@mydomain.com'));

Filling in 'default' or fixed values on INSERT statements

The goal here is to perform an INSERT statement much like our first INSERT example, by using a SELECT to define what gets INSERTed. However, the table we're inserting into has some required fields, and those required fields aren't defined in the tables we're selecting from. In my case, I lucked out: one of the fields was an 'auth' field which I knew would always be set to 'ldap', and the other was a user 'description' field which I could set to say 'Edit your profile'. Those values don't exist anywhere in the database yet, but I can put them in.

Another required field was 'email'. I didn't have email addresses in the database, but in our environment, everyone's email address is formed by putting their UNIX id in front of the @ sign, and our domain name after it. So, I just grabbed their nix ID and used a CONCAT() function to tag on the @mydomain.com portion, and called the resulting column in the output 'email'.

INSERT INTO user (auth,username,firstname,lastname,email,description)
(
SELECT DISTINCT 'ldap' AS auth, v.netid, v.first, v.last,
CONCAT(v.nixid,'@mydomain.com') AS email,
'Edit your profile' AS description
FROM v_enrollment AS v
LEFT JOIN user AS u ON u.username = v.nixid
WHERE u.username IS NULL v.nixid != ''
ORDER BY v.nixid
)