I’m doing a challenge problem on DataCamp. It uses two tables, “economies” which contains economic information by country code, and “countries” which contains general country information by country code. The challenge is: Get country code, inflation rate, and unemployment rate in 2015 from the “economies” table, where the gov_form is not ‘Constitutional Monarchy’ or ‘%Republic%’ in the “countries” table. With
What is difference between where and join in Hive SQL when joining two tables?
For example, What is difference between where and join in Hive SQL when joining two tables? Answer Join like this is a bad practice because in general, WHERE is being applied after join and transforming it to JOIN and pushing predicates is upon optimizer, to convert it to proper join and avoid CROSS join (join without ON condition). Always use
Trying to get sum of specific column cells for last 4 rows sum in MySQL
I am trying to get sum of capacity column of last 4 rows of a table in MySQL. My Table is : My SQL is : Result is giving different sum. It should be 150+200+250+300 = 900. I am looking for sum of the red circled numbers. Answer You could order using subquery and apply SUM in the outer query.
SQL efficient way to match ANY in a large table
I am joining a small table and a very large table and want to return a distinct item if ANY items match. The table is so large that it takes hours for something that I think should take seconds. The problem is that I am “iterating” over every single entry in the second table. I want to be able to
First row of results should never be displayed
DB-Fiddle Expected Result In the results I want to display the quantity for all dates except for the first row. I know in the example above I can do this with a simple WHERE-condition like this: However, it is neither guaranteed that the first time_stamp will be always 2020-01-14 nor that the MIN(2020-01-14) is always in the first row. Therefore,
Postgres – Add a column to a table with a default value, but set existing rows to a different default value
I want to add a boolean column to a table and have the default value be false for newly created rows, but all existing rows should be set to true. How can I do that? Answer First alter table and add column as alter table table_name add column column_name boolean default false; Then update value of that column as update
SQL – Ordering table with subquery for select
I am trying to do ordering on query with subquery for a name from uuid. Let’s have those two tables in MySQL: bans: id uuid time reason 1 c6b8eade-7db9-345b-b838-8f8361552cf5 1642369382 Test 2 0138c279-c5fa-3acd-adaa-8edb7b4569d8 1642384565 Spam 3 3c01262c-a3c3-3133-ba43-92a9ded01c27 1631876477 Hax users: id uuid name 45 c6b8eade-7db9-345b-b838-8f8361552cf5 John 100 0138c279-c5fa-3acd-adaa-8edb7b4569d8 Mike 35 3c01262c-a3c3-3133-ba43-92a9ded01c27 Norman With this query, I can select whole ban
Adding constraint in PostgreSQL that requires information from another table
I have the following schema in PostgreSQL How can I enforce the constraint that studentmarks.marks_scored <= exam.total_marks such that the behaviour is just like the CHECK constraint? Answer Use trigger. You need to create trigger function first. Then create trigger. NOTE I have tested in postgres
Calculate total difference of values between two timestamps
DB-Fiddle Expected Result: I want to calculate the total difference of the quantity between two timestamps. Therefore, I tried to use the query from this question: However, I am not able to get the expected result. What do I need to change to make it work? Answer The partition by clause in the lag call of your query is redundant.
mysql error 1822 when trying to create new table with a FOREIGN KEY
trying the following code: Give back Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ‘stock_ibfk_1’ in the referenced table ‘donation’. Why? how can I solve this problem? Answer to create a foreign key relationship, the parent table column on which you are creating relation must be unique or primary and they must have the