How to use current date in H2 database SQL query
use CURRENT_TIMESTAMP select * from tableName where date_column > CURRENT_TIMESTAMP() CURRENT_TIMESTAMP
sql
Concat all column values in sql
In SQL Server: SELECT col1 AS [text()] FROM foo FOR XML PATH (”) In MySQL: SELECT GROUP_CONCAT(col1 SEPARATOR ”) FROM foo In PostgreSQL: SELECT array_to_string ( ARRAY ( SELECT col1 FROM foo ), ” ) In Oracle: SELECT * FROM ( SELECT col1, ROW_NUMBER() OVER(ORDER BY 1) AS rn FROM foo MODEL DIMENSION BY (rn) … Read more
performance – single join select vs. multiple simple selects
There is only one way to know: Time it. In general, I think a single join enables the database to do a lot of optimizations, as it can see all the tables it needs to scan, overhead is reduced, and it can build up the result set locally. Recently, I had about 100 select-statements which … Read more
How do I select records less than 1 year old?
select * from MyTable where MyDate > DATEADD(year, -1, GetDate())
INSERT into table without specifying Column names
As long as you have the right number of columns in your INSERT statement, and as long as all the values except KEYBOARD are some numeric data type, and as long as you have suitable permissions, this should work. INSERT INTO INVOICE VALUES( 1,1,’KEYBOARD’,1,15,5,75); SQL requires single quotes around text values. But not using column … Read more
Ugly formatting in SQL*Plus
Increase the linesize, e.g SET LINESIZE 32000 or use SET WRAP OFF (but this will truncate long values)
Is it possible to use Aggregate function in a Select statment without using Group By clause?
All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY Good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3 Also good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3, col5, col6 No other columns = no GROUP BY needed SELECT … Read more
Using LIKE in an Oracle IN clause
What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL SELECT * FROM tbl WHERE my_col LIKE ANY (ARRAY[‘%val1%’, ‘%val2%’, ‘%val3%’, …]) Unfortunately, that syntax is not available in Oracle. You can expand the quantified comparison predicate using OR, however: SELECT * FROM tbl WHERE my_col LIKE ‘%val1%’ OR … Read more
get basic SQL Server table structure information
Instead of using count(*) you can SELECT * and you will return all of the details that you want including data_type: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=”Address” MSDN Docs on INFORMATION_SCHEMA.COLUMNS
What is the order of execution for this SQL statement
Check out the documentation for the SELECT statement, in particular this section: Logical Processing Order of the SELECT statement The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, … Read more