The following SQL SELECT concat(‘CSE_’, course_code), concat(‘CS_’, course_code) FROM classes this returns some like this: CSE_210 CS_210 CSE_570 CS_570 CSE_480 CS_480 Instead I need …
Trigger with insert and calculations
I Have two tables, TableA which has a primary key (A_ID) and a salary column. Table B has a primary key (B_ID) and a paymentAmount column. I need to create a trigger so that once TableB B_ID is …
How to join two tables using partition (or something else)
I have two tables: table A with sales products (test1, test2) with every day information table B with history of category changes of these products (product can change category once a year or every …
How can I calculate the highest difference of values between two immediate rows?
I have a table that follows this structure: Table Foo id number ——————— 1 5 2 8 3 3 4 67 5 1 6 10 From …
SQL: How to add columns for rows given an array of variable length row names
Say I have a database of items, and each item has the following columns: name, amount1, amount2, amount3. Given an array of names (that varies in length), how can I query the database to get the sum …
How to get the most recent data from a table?
Here is the table that I have The question that I have for my query is “Using the most recent month of data available, what is the range of estimated home values across the nation?” Since my table is small, I know the recent date is 2020-06. So I wrote this code: And the result that I get is: Anot…
Set default value for all columns of a table
I have multiple tables with a lot of columns. Some columns contain Null values. Now, i want to set them to a default value (empty string or 0). I found two two pieces of SQL that probably cover what i need but i can’t put them together. I can use the above code to get all columns and their data
How to assign the correct type to bind_param dynamically based on retrieved column types?
Background info: I am creating a class which will handle certain queries for me. One of these queries would be a SELECT query, where the user can select something based on a clause. I successfully retrieved the TYPES of the columns, but have no idea on how to actually decide based on the TYPE of the column wh…
Query to get IDs related to certain foreign keys but not others
I have two sites with separate WordPress instances. One is in the process of getting WooCommerce, the other has existing users with set groups to access courses. I the future access to the courses is supposed to be sold via the shop. The answer I’m looking for has nothing to do with WordPress per se but…
mysql table AUTO increment keeps incrementing in steps of instead of 1. Like 41,51,61
Even after deleting all rows in the table continues. It continues adding from the last inserted id. I used this to create my table adding users with a signup route and it keeps incrementing in steps of 10 this is the code that is used the 10 rows of json data Answer That’s normal behavior of MySQL and o…