What is semi-join in database?
Simple example. Let’s select students with grades using left outer join: Now the same with left semi-join: The latter is much more efficient.
Database
SQL ZOO List each continent and the name of the country that comes first alphabetically
when you use x.name=y.name you are comparing country name from x with the country name from y if both instances have the same country name. That basically would just return you the full table x. You want to use x.continent=y.continent because you only want to compare the country name of an instance from x with the country name of … Read more
sqldeveloper error : no ocijdbc11 in java.library.path on windows 7
Your path of directory Tnsnames is incorrect, check it in Tools\Preferences\Data Base\Avanced:
Order a MySQL table by two columns
Default sorting is ascending, you need to add the keyword DESC to both your orders:
How do you create a yes/no boolean field in SQL server?
The equivalent is a BIT field. In SQL you use 0 and 1 to set a bit field (just as a yes/no field in Access). In Management Studio it displays as a false/true value (at least in recent versions). When accessing the database through ASP.NET it will expose the field as a boolean value.
mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists
It seems odd that this directory was not created at install – have you manually changed the path of the socket file in the my.cfg? Have you tried simply creating this directory yourself, and restarting the service?
Oracle SQL query for Date format
to_date() returns a date at 00:00:00, so you need to “remove” the minutes from the date you are comparing to: You probably want to create an index on trunc(es_date) if that is something you are doing on a regular basis. The literal ’27-APR-12′ can fail very easily if the default date format is changed to anything different. So make sure … Read more
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer. I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily. If there is a table called STUDENTS Result I expected was: I … Read more
LAST_INSERT_ID() MySQL
You could store the last insert id in a variable : Or get the max id from table1 (EDIT: Warning. See note in comments from Rob Starling about possible errors from race conditions when using the max id) (Warning: as Rob Starling points out in the
Remote table-Valued Function Calls are not allowed
You need to add WITH (NOLOCK). Not entirely sure why but I just ran into this issue today and this solved my issue.