How to rename columns with `SELECT`?
SELECT table1.price, table2.price AS other_price …
sql
How do I count columns in a table
SELECT count(*) FROM information_schema.columns WHERE table_name=”tbl_ifo”
How to show the trigger(s) associated with a view or a table in PostgreSQL?
This will return all the details you want to know select * from information_schema.triggers; or if you want to sort the results of a specific table then you can try SELECT event_object_table ,trigger_name ,event_manipulation ,action_statement ,action_timing FROM information_schema.triggers WHERE event_object_table=”tableName” — Your table name comes here ORDER BY event_object_table ,event_manipulation; the following will return table … Read more
PostgreSQL: Drop a function without parameters
In Postgres functions can be overloaded, so parameters are necessary to distinguish overloaded functions. To unambiguously identify a function you can put only types of its parameters. DROP FUNCTION my_func(INT);
Declare local variables in PostgreSQL?
Postgresql historically doesn’t support procedural code at the command level – only within functions. However, in Postgresql 9, support has been added to execute an inline code block that effectively supports something like this, although the syntax is perhaps a bit odd, and there are many restrictions compared to what you can do with SQL … Read more
How to do a FULL OUTER JOIN in SQLite?
Yes. See the Join (SQL) > Outer join > Full outer join article on Wikipedia. SELECT employee.*, department.* FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION ALL SELECT employee.*, department.* FROM department LEFT JOIN employee ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL
MySQL : transaction within a procedure
Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html Basically you declare error handler which will call rollback START TRANSACTION; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; EXIT PROCEDURE; END; COMMIT;
MySQL: update field only if condition is met
Yes! Here you have another example: UPDATE prices SET final_price= CASE WHEN currency=1 THEN 0.81*final_price ELSE final_price END This works because MySQL doesn’t update the row, if there is no change, as mentioned in docs: If you set a column to the value it currently has, MySQL notices this and does not update it.
Order results by COUNT without GROUP BY
You need to aggregate the data first, this can be done using the GROUP BY clause: SELECT Group, COUNT(*) FROM table GROUP BY Group ORDER BY COUNT(*) DESC The DESC keyword allows you to show the highest count first, ORDER BY by default orders in ascending order which would show the lowest count first.
Show procedure definition in MySQL
SHOW CREATE PROCEDURE <name> Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement. Swap PROCEDURE for FUNCTION for a stored function.