I have a scenario which needs to be handled in Oracle SQL – that is a requirement. This is a book lending scenario which I am trying to create. Each student is entitled only so many number of books which is based on a credit system. In this sample Student ID ‘100’ is entitled for 4 books. Ne…
Tag: sql
Most efficient way to delete duplicate entries from MySQL tables
We have a table (let us call it originalTbl) that has duplicate entries that we want to delete. By duplicate I mean all values other than an AUTO INCREMENT index field are the same. One way to do this is to create a new table, like the existing table (let us call it uniqueTbl), and then have a query like:
How to use a calculated value in Oracle SQL count
I have a column called BIRTH_DATE in my view. I want to calculate the age using that column and today’s date, and count the number of staff in an age bracket. For example: staff between 40 and 49 etc. Other answers in plain SQL statements are also welcome. My code is shown below: I am calculating the ag…
How can I load only unique data into my oracle table without specifying PK manually?
Let’s say I have a table real_table with a primary key on column1. I can load data into it like this: But this means I have to specify the PKs manually. Doing it manually is not a huge problem for a relatively small table. I can also do this: But what do I do for a table that has 10
How to Sort this MySQL Query
I am trying to insert the ORDER BY in this query to sort the Total column: With the following result: Here’s what I have tried so far but no luck: Answer I think you want: Rationale: the ORDER BY clause should go after all UNION ALL subqueries – so it needs to be outside of the GROUP_CONCAT(), in …
SQL Group by Transpose
I have the following Table charges Using the following query gets my the counts by Charge and Date Is there a way to transpose this to get the following? Thank you. Answer Use conditional aggregation:
What is the proper term for one of the disjuncts in a query using UNION?
Complete the sentence: In the query the sub-selects SELECT 0 and SELECT 1 are properly known as the ___________s of the UNION. I am not looking for your opinion on what would be a good name – I want you to back your answer up with a reference to some kind of authoritative source. Answer If I understand …
SQL Aggregate Statement to Include Null Values and Values Not in a List
The result I am looking for is a table with each city, the number of unique yes votes, and the total yes votes. If there are yes votes in the votes table that don’t belong to a city in the users table, then it needs to return “other city” with the same aggregate stats. The result should look…
Multiple LIKE clauses in Laravel Eloquent
I currently have this code in my controller: The above code works, but what I want is to get “Accounting” and “Web Design” from an array, and just loop through that array so that the query is dynamic instead of hard coded on the controller Answer Try this :
Where with OR condition for the same column in Rails
I’m trying to retrieve all records that have expired_at as nil and expired_at greater than the current time. I’ve tried this: But that is only returning me the records expired_at greater than DateTime.now. Why is the nil being neglected? Answer Your current query checks that expired_at is equal to…