'SQL' Tag

  • Tool of the day: q

    August 8, 2013

    If you work with command line and know your SQL, q is a great tool to use: q allows you to query your text files or standard input with SQL. You can: SELECT c1, COUNT(*) FROM /home/shlomi/tmp/my_file.csv GROUP BY c1 And you can: SELECT all.c2 FROM /tmp/all_engines.txt AS all LEFT JOIN /tmp/innodb_engines.txt AS inno USING […]

  • Merging tables with INSERT...ON DUPLICATE KEY UPDATE

    February 21, 2013

    Had a case recently where I had to merge data from two identically structured tables containing nearly identical data. "Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one […]

  • Hierarchical data in INFORMATION_SCHEMA and derivatives

    January 8, 2013

    Just how often do you encounter hierarchical data? Consider a table with some parent-child relation, like the this classic employee table: CREATE TABLE employee (   employee_id INT UNSIGNED PRIMARY KEY,   employee_name VARCHAR(100),   manager_id INT UNSIGNED,   CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ) engine=innodb ; +-------------+---------------+------------+ | employee_id | employee_name […]

  • Pop quiz: funny syntax

    November 5, 2012

    The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript. Can you guess the results of the following statements? Pop quiz 1 SET @x := 7; SELECT ++@x; What is the computation result? What will be the value of @x? Pop quiz 2 SET @ := […]

  • Three wishes for a new year

    September 16, 2012

    Another new year by Jewish calendar. What do I wish for the following year? World peace Good health to all Get a decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL. I mean, I like GROUP_CONCAT, and the many hacks it provides: [1], [2], [3], [4], [5], [6], [7], [8], [9], […]

  • SQL: selecting top N records per group, another solution

    August 21, 2012

    A while back I presented SQL: selecting top N records per group, a "give me the top 5 countries in each continent" type of query, and which used an external numbers table and a lot of tedious casting. Here's another solution I came up with (*). Still using GROUP_CONCAT (how else?), but no external table […]

  • MySQL joins: ON vs. USING vs. Theta-style

    July 11, 2012

    What is the difference between the following three syntaxes? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id The difference is mostly syntactic sugar, but with a couple interesting notes. To put names, the first two are […]

  • Auto caching tables

    March 6, 2012

    Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated? Hint: yes. But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have […]

  • MySQL/QueryScript use case: DELETE all but top N records per group

    February 9, 2012

    Some administrative tasks can be simplified by using common_schema/QueryScript. I'm collecting a bunch of these for documentation. Here's one for example: The DBA/developer has the task of retaining only top 3 most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th, ... most populated counties in each continent. Is it possible […]

  • Syntax of the day: IS TRUE and IS FALSE

    January 26, 2012

    What makes for a true statement? We usually test statements using a WHERE clause: SELECT * FROM world.City WHERE Population > 1000000 The "Population > 1000000" statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF(): SET @val := 7; SELECT IF(@val > 2, […]

 
Powered by Wordpress and MySQL. Theme by openark.org