So, I got in this situation: I create a table (let say model_a), in this table I have 2 cols (let say col1 and col2). I need a constraint that satisfy this reality: It’s not UNIQUE constraint, you can duplicate the first row. The only one time this constraint active is col1 is the same but col2 is diffe…
How calculate filtered by date aggregate sum after grouping by some column in SQL?
I have a table which contains payments received by person per date. I.e. We know how to calculate aggregate sum in SQL: We know how to calculate aggregated sum for some period of dates: Here is the question: what if I need last aggregate but I dont want to remove persons which never had payments on 12-12-2020…
Sql join only 1 row
I want to join the table CustomerAgreementRole with only one row from AgreementRoleGroup, based on ViewPriority. Example: CustomerAgreementRole can have three rows. I only want the joined row where ViewPriority is highest Current query: Expected result: Answer Made it working with this:
Azure SQL Server “clr strict security” can this be changed
I’m trying to change the ‘clr strict security’ in Azure SQL Server, but not able, is this no more possible in Azure Managed SQL Server. I’ve created a certificate first and then I tried to create a …
PostgreSQL UPDATE statement treats keyword as a column
sorry for the noob question, just getting started with SQL and I have a problem that I could not resolve. Basically, I created a table with “date of birth” column and chose wrong TYPE (integer), so every time I am trying to enter date of birth as “1998-01-04” it is just showing 1998. S…
Reading Excel file with SQL returns null due to empty column
I am attempting to read in an Excel File from a third party but it falls over and returns null due to empty column. The file has 50 columns, a blank column and a further 48 columns. I only want the first 50 columns. If I delete or populate the empty column in Excel it works but I need to
A question about a correlated subquery in SQL and AVG function
We need to query the DB for students whose points are greater than the average for the uni they go to. Now, in the handbook, it is used with GROUP BY clause, but I fail to understand why one is needed here at all if the universities are already grouped in the inner query’s WHERE condition. Could you som…
How would one return the students that are below the average in SQL query
For my assignment, we have to return the count of students that are below the average of the average where students are in section_id 86 and the grade_type_code is ‘FI’ I feel like I have it structured right because I’m able to return all the results just I can not figure out how to have the…
Query to obtain value among selected values in a comma separated varchar
I have an SQL table as given below How can I write a Select query such that all titles with genre “Crime” will be displayed Answer First, you should fix your data model! Storing multiple values in a column is a really, really, really bad idea. That said, sometimes we are stuck with other people…
Query Optimization in SQL in given case
These are the given tables: Question is: Write a optimized query to find out the name of all the customerIDs who ordered coffee that originated from ‘Costa Rica’ or ‘Indonesia’. My attempt: My teacher says it can be optimized more but I don’t see any way. Please Help me out. Answer Rather than these nes…