I’m a self-taught, vaguely competent SQL user. For a view that I’m writing, I’m trying to develop a ‘conditional LEFT’ string-splitting command (presumably later to be joined by a ‘conditional RIGHT’ – whereby: If a string (let’s call it ‘haystack…
How to check the query is using the indexes in mysql
I have a SQL query with where clause, I have created the index for the columns which are being used in the where clause. How can I check the query is using the indexes for these columns? Answer Write “explain ” in front of your query. The result will tell you which indexes might be used. For examp…
BigQuery GROUP_CONCAT and ORDER BY
I am currently using BigQuery and GROUP_CONCAT which works perfectly fine. However, when I try to add a ORDER BY clause to the GROUP_CONCAT statement like I would do in SQL, I receive an error. So e….
SQL query to get Stored Procedure crop result if the routine_definition is greater than 4000 char
I use this query to get all the stored procedure in my database (I’m using Microsoft SQL Server 2008): For almost all result everything is ok, but for the row with a very long ROUTINE_DEFINITION the result is cropped. Do you know how to solve it? Answer Please try with sp_helptext ‘ProcedureName&#…
C# sql create one connection and open and close for each query
I recently inherited a C# Web app that creates a new connection for every query like so: I understand that this is typically the best way to do it, but is it acceptable or “best practice” to have the constructor create the connection object, and then have each method/Query open and then close that…
How to convert Laravel migrations to raw SQL scripts?
Developers of my team are really used to the power of Laravel migrations, they are working great on local machines and our dev servers. But customer’s database admin will not accept Laravel migrations. He asks for raw SQL scripts for each new version of our application. Is there any tool or programming …
Find out available days and slots for doctor appointment
I have three tables: TimeSlotToken is the number of patients a doctor will see in his TimeSlot. TimeSlot 1, 2, and 3 are Morning, Afternoon and Evening respectively. I need to find the days where the TimeSlot is not full, i.e TimeSlot token is less than total bookings on that particular day and that slot. Sin…
How to retrieve count of records in SELECT statement
I am trying to retrieve the right count of records to mitigate an issue I am having. The below query returns 327 records from my database: Where I run into trouble is trying to reconcile that with the below query. One is for showing just a count of all the particular students the other is showing pertinent in…
How to use ANY instead of IN in a WHERE clause?
I used to have a query like in Rails: Which generates sql query like: Now I want to change this to use ANY instead of IN. I created this: Now when I use empty array ids = [] I get the folowing error: Answer There are two variants of IN expressions: expression IN (subquery) expression IN (value [, …]) Si…
Connect to SQLite3 server using PyODBC, Python
I’m trying to test a class that loads data from an SQL server given a query. To do this, I was instructed to use sqlite3. Now, the problem is that while the class manages to connect to the real …