sql

School of Programming

I still got the idea of founding a real "school of programming". These topics come into my mind, for a course about Web Development:

- Time Estimations
- Self Management
- Relational databases and high-performance SQL
- Design Patterns (incl. MVC)
- JavaScript and AJAX

Comma separated list in SQL

Just a short example of how to write a stored function that returns a comma separated list of values (Microsoft Transact-SQL):


CREATE FUNCION get_authors (@pubId int)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
  DECLARE @authorList nvarchar(max);
     
  SELECT @authorList = @authorList + ', ' + 
    authorFirstName + ' ' + authorLastName
    FROM pdb_authors a, pdb_publicationAuthors b 
    WHERE a.authorId = b.authorId AND b.pubId = @pubId;

  RETURN(SUBSTRING(@authorList, 3, LEN(@authorList) - 2))
END

Optimizing SQL - Part 1

Design approaches for multi-lingual data

In this example, we're assuming a dataset consisting of 6 multi-lingual fields. Each field holds strings with an average length of 200 characters in the 4 locales en, de, zh and ru. A fallback function is implemented using the CASE expression (doesn't add much overhead and is faster than fetching data in two locales and implementing the fallback at application level).

Fun with SQL

I had heaps of fun with SQL today (again). Ever heard of Common Table Expressions? Here is an example of a recursive query:


WITH
  nodeCTE (nodeId, nodeName)
AS (
  SELECT 
    a.nodeId, 
    nodeName = CONVERT(varchar(8000), nodeName) 
    FROM 
    nodeNames a JOIN nodes b ON a.nodeId = b.nodeId 
    AND a.locale = 'en' AND b.parentId = 0 
  UNION ALL SELECT y.nodeId, 
    x.nodeName + ' -> ' + CONVERT(varchar(8000),
    z.nodeName)
    FROM 
    nodeCTE x JOIN nodes y ON y.parentId = x.nodeId      
    JOIN nodeNames z ON z.nodeId = y.nodeId AND 
    z.locale = 'en'
)
SELECT * FROM nodeCTE;

I'm a Drupal user now

I just installed drupal as blog software and converted the old blog entries to the new table format.

This is (a part of) the SQL code:

INSERT INTO node_revisions SELECT a.id, a.id, 1, c.value, b.value, SUBSTRING_INDEX(b.value, '.', 6), null, changed, 4 FROM public_websites.ndev_liveContent a JOIN public_websites.ndev_liveContentData b ON a.id = b.content_id JOIN public_websites.ndev_liveContentData c ON a.id = c.content_id WHERE b.name = 'body' AND b.language_id = 1 AND c.name = 'title' AND c.language_id = 1 AND a.parent_id = 1 AND a.type_id = 9;

Country list as MySQL dump

I just imported a list of all countries from a CSV file and re-exported it as (My)SQL dump:

http://www.nulldevice.eu/files/downloads/countries.sql

Feel free to download and use it! An even more complete list (including TLDs etc) will follow...


Theme & Icons by N.Design Studio
© 2000-2007 Michael Mayer
Syndicate content