I have a large database where each month more data is added, in this database if a new user is added it creates a new row. The issue I have is that due to spelling error in the past which I have since corrected. The data is appearing as one row. What I need is for both rows to be
Tag: sql
How perform a transaction in ibm db2
I’m working with an accounts table I tried to run this sql which transfer funds from one account to another. I got this error savepoint “FIRST_SAVEPOINT” does not exist or is invalid in this context.. SQLCODE=8080, SQLSTATE=3B001, DRIVER=3.69.56 What could i do ? I’m using IBM Data Stu…
Sql – Query to calculate no. of buyers monthly
i would really need your help in creating a query where i can show the number of buyers who made more than 1 order in a month for the period January 2017 to December 2017. Also, the no. of these buyers that didn’t order again within the next 12 months. Below is sample my data. Date CID (customer id) Ord…
SQL (HUE) : Is there any way to convert 24 hrs time into 12 hrs AM / PM format with hours buckets
I have table A which contains column time stored as timestamp datatype. Table A: Contains time column in HH:MM:SS in 24 hrs format. Answer Please use below code. Replace now() with time for your query. Explanation – firstly i am checking if hour is >12. If yes, deducting 12 to get the hour. Then sett…
Performing a Union on groups of rows without the same columns. Trying to generate empty histogram bins
I am using MySQL. I have a table grades, that looks like so: I have a view that counts how many grades are in each bin, for each class using the following query. This returns: I want to add empty bins to this view, like so: So the bins increment by 5, starting at 40, and stopping at 100. My
Query to display Employee and Manager
I have to write a SQL Server query to display the employee last name and his respective manager’s name, as given below. These are the schema for the two tables. Expected Output : I tried this code but its not right Detailed Schema: Schema Answer I guess something as simple as this should do it How it works: T…
Count number of students per one role SQL
I have a table students with student id, course id and role number looking like this (ordered by student_id): How to count number of students for each role? If student is enrolled in multiple courses with the same role number, then count this as one. The expected output for above table would be similar to thi…
Set minutes of timestamp to a specific value
What is the easiest way to set the exact minutes value of a timestamp? This only adds minutes instead of setting the exact value: Answer Use date_trunc() before you add 2 minutes: Or, to retain seconds and sub-seconds: Demo: Should be substantially faster than manipulating the text representation and casting …
sql left join on same table
Write an SQL query to find the team size of each of the employees. The answer above is correct I am just confused as to why you use a LEFT JOIN on two tables that are the same. Answer The best way to understand what is happening is to just view the intermediate table which results from the self join.
I was asked the following pseudocode in a SQL interview based on joining condition. Can anyone explain what went went wrong with my approach?
[two tables-ta and tb with respective columns ] I was asked to put a LEFT JOIN on item id and explain what would be the possible output. I wrote a pseudocode like this- I told them the output would look like this, which they told is not the right answer. Can anyone explain what would be the correct answer alo…