Category Archives: SQL

Imagine that you have a table with a column of the type DATETIME. You’ve been storing data as localtime and after a while you need to convert these datetimes to UTC. Here’s a possible approach: UPDATE events SET start = … Continue reading →

Earlier today someone asked the following: I’m trying to move selected data from one table to another. The following works apart from the destination table is not incrementing the ID (I’m not using auto increment for that field). How can … Continue reading →

Very often i have to write queries that return all the rows where one or more columns match a specific value. If i add for every column the condition ‘P_COLUMN IS NULL OR COLUMN = P_COLUMN’ to the WHERE clause … Continue reading →

A couple of days ago i discovered Oracle SQL Developer, a new and free graphical tool for database development. At first i was impressed by all it’s features but when i tried to modify a couple of existing stored procedures … Continue reading →

As i already wrote, last couple of days i’ve been experimenting with PL/SQL. At work we use Toad for Oracle but since TOADSoft only offers a limited freeware version i decided to write my code with GVim and use SQL*Plus … Continue reading →

Imagine that you have a schema where you store all the times a player needed to complete a parcours. A possible schema could be (postgresql): CREATE TABLE laptimes ( lap_id SERIAL NOT NULL, player_id INT NOT NULL, laptime INT NOT … Continue reading →

I’ll try to explain how a join on more than one table works. I’ve noticed people get confused by it. Assume we have the following tables: newsitems(news_id,post_id) postitems(post_id,user_id,content) users(user_id,name,password) We want to display for each newsitem the content and the … Continue reading →

I’m cleaning up my code snippets and i found the following little trick in one of them that i’ve removed. Assuming that different words are separated by spaces we can use SUBSTRING_INDEX as following: SELECT SUBSTRING_INDEX(body,’ ‘,50) AS dn FROM … Continue reading →

As a follow up to Custom Ordering I discovered the nice Field function in MySQL. after it was mentionned on my favorite PHP Forum by Weirdan. It allows one to order a column on a custom order relation. SELECT * … Continue reading →