Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Posts tagged sql
What is the default port number of MariaDB database server? (Remembering defaults is surprisingly hard since usually you don't need to specify them..)
I have a website about vacation villa rentals. Around 800 properties are listed. When i start a search, i get properties with my first query which filters type, style, area, no. of rooms, capacity ...
Context: I have a varchar column in a segment event table that contains version numbers. Most of these are formatted nicely as X.Y.Z (major.minor.patch) where X, Y, and Z are integers. However, the...
Lets say I have two tables, A and B and I need to join a subset of them. Is there best practices of sticking the conditions in the WHERE clause like this, SELECT * FROM A JOIN B on a.fk_b = b....
I like using NATURAL JOIN in Snowflake, because I find it more elegant than explicit join clauses. However, it appears that the natural join behaves similar to an inner join, in that null values o...
I have a query summarizing some transaction data that I'd like to summarize by day of week. For my use case, I need to return weekdays formatted according to ISO 8601, so Monday must be the first d...
The Code using Microsoft.EntityFrameworkCore; public class BloggingContext : DbContext { public DbSet<Blog> Blogs { get; set; } public DbSet<Post> Posts { get; set; } ...
Can someone explain why this PHP code is not excluding the watched videos? The email_id parameter is used to specify the location of the watched videos array. The code should remove the watched_vi...
I had three data with where gender=female. I was trying to call those data with limit and offset (Since in future there might be more than 1000 data). SELECT * FROM `users` WHERE `gender`='Female'...
Is it possible to add listeners via VBScript/ JS directly into a SRSS report? Goal: Track changes of input fields and key press inside the wrapper React based on the events Directly in the...
I have to migrate a bunch of text from an old application into a new one. Some of these texts contain HTML tags and entities (HTML editor was used) and now they do not want to support this in the n...
I have the following scenario (in MySQL 8): CREATE TABLE `steps` ( `id` int NOT NULL AUTO_INCREMENT, `number` varchar(30) DEFAULT NULL, `parent_number` varchar(30) DEFAULT NULL, `timestamp` ti...
Let's say I have a table with 10,000 rows and instead of selecting all of the rows at once I select 1,000 at a time like LIMIT 0,1000 LIMIT 1000,1000 etc. Without an ORDER BY statement the or...
One of the legacy applications my team has to maintain has almost always this pattern for dealing with data modification: try { // get the connection // begin transaction // optional...
A person I used to work with several years ago was hired to rewrite a product using a .NET-based modern tech stack. One of the aspects that stroke me was that he believes that the product should mo...
Our team is currently transferring all functionality (+ some changes) from small and very old project A (almost code freeze) to project B (actively developed). As part of the data migration, there ...
I am building an MVC cart system. I have the MERCHANTS table and the PRODUCTS table. A merchant (store) can have one or more products, everything works fine but what I want is to display products b...
The context A reports related process is directly reading the production operational database once about two hours. This involves reading all the data from some 70 tables which takes a couple minu...
Despite the fact that I have programmed against SQL Server for quite a while I did not pay much attention to the tempdb database. This is especially true if application logic is mostly written usin...
query = "CREATE TABLE "+TABLE+" ("+COLUMN1+" INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, "+COLUMN2+" VARCHAR(255), " + COLUMN3+" VARCHAR(255), "+COLUMN4+" TEXT, "+ COLUMN5+" VARCHAR(255)...
What's the better way to store base64 in SQLite Database? I was thinking to put base64 as TEXT since base64 has lots of chars. CREATE TABLE name (id INTEGER PRIMARY KEY AUTO_INCREMENT, image TEXT)...
I have a column that is a concatenation of 5 other columns plus a join to a different table. UPDATE db.a JOIN db.b ON fk_b = b.pk SET concat_field = CONCAT(field1,field2,field3,b.field,field4,fi...
I had installed mysql using the following command sudo pacman -Sy mysql Earlier, I was using Lampp(Xampp for Linux) where I could start using the command /opt/lampp/start start I am not sur...
I have recently an issue at work after upgrading an ASP.NET Core 3.1 application to .NET 5. It worked correctly on all environments (e.g. PreProd, Prod) when targeting 3.1, but failed on Prod only ...
In some situations, could it be problematic to migrate a website's database (MySQL) from one website hosting provider to another without the information schema or with the information schema? In m...
I had a curiosity about how much the experienced users wait for their questions to be answered on Stack Overflow and had written a query for it: SELECT YEAR(q.CreationDate) * 100 + MONTH(q.Creatio...
I have a parent child relation in my table, with possibly circular cases. Is it possible to break the infinite recursivity in CTE checking values of all previous rows? I would need something like t...
On 25th October 2020 in Europe/Berlin clocks where set back from 03:00 AM to 02:00 AM to change from summer time (CEDT) to winter time (CET). Which means there is a 1 hour separation between 02:30...
For my purposes, it is secure enough to use passwords to execute MySQL commands on the command line, what I would like to do is disable the warning that says, mysql: [Warning] Using a password on ...
I have extensive working experience with Entity Framework ORM and have noticed two major ways of writing LINQ (LINQ2SQL): lot of eager loading: the most prevalent, uses (lots) of Includes to eag...
Let's say we have two tables A and B and a join table C that has foreign keys to both A and B and the combination of those foreign keys is unique. One could either do a unique constraint or a comp...
Its possible to execute a MySQL file from the command line like so, mysql -u USER -pPASSWORD < FILENAME which triggers a warning, mysql: [Warning] Using a password on the command line int...
In a stored procedure let's say I have a VARCHAR variable, DECLARE example_variable VARCHAR(100); how much memory is allocated to it and is that independent of the length of the data in the var...
Due to a series of unfortunate events I have some tables where the auto_increment value got behind what it should be. If the auto_increment value is 9 and there are 20 rows in the table the next 1...
I have a PHP program that does a SELECT and then updates some of the values based on an algorithm. Rather than updating one row at a time UPDATE example_table SET COLUMN_A = 1 WHERE primary_k...
I have a very large table that I need to iterate through with a PHP script and I can't do all of the results at once so I do it in sections with a LIMIT like for ($x = 0; $x < 10000000; $x += 1...
So I had a table with a primary key and a bunch of different columns. Columns A, B, and C were all unsigned ints (like the primary key column) and each column had a unique constraint I dropped the...
I have tables A and B and then I have a many to many join table with foreign keys to both called a_b. Neither foreign key can be null and the combinations for the foreign keys to A and B are unique...
I need to drop all of the stored procedures from my MySQL database before I recreate them. How can I do so?
I have a query that returns the owner of jobs on an SQL instance, select s.name as JobName , s.owner_sid , ISNULL(L.name,'AccessViaGroup') as LoginName --Trying to figure out how to turn that ...
My project has the following set up for the production and preproduction ("clone") environment. Production is not accessible at all for the development team, only the preproduction database. Pr...
Currently, we have a CRUD plus reporting application that talks to one MySQL database. Intermittently users will report locks when searching, currently, I can only get the approximate time of when ...
Almost every table in my DB has triggers that fire on INSERT, UPDATE, DELETE and write the changes to a separate read only DB. This makes it possible to track changes and undo things well after the...
I found a confusing construction in several stored procs in an MS SQL 2008 R2 database: DATEADD(dd, 0, DATEDIFF(dd, 0, some_date)) As I understand it, these are the relevant function signatures: D...
I am considering either removing some columns or changing the datatypes if I could significantly reduce the amount of storage that is currently used by those columns. Some of the columns are ints a...
I have a database with quite a few VARCHAR fields. When the database was first built the lengths of the columns were set a bit larger than absolutely necessary. Now after, having used the DB for a ...
I just completed my first install of SQL as a server-based, Enterprise Edition. I wanted to check it out using SSMS on PC using my Admin Access. But could not connect. For security reasons my adm...
Sometimes, either to reduce the amount of typing big table names or when joining a table to itself one will need to alias a table. Personally I find SQL statements with aliases harder to read and a...
I have some SQL scripts that contain DDL to create tables and schemas for a database. I'd like to be able to comment this SQL and then use those comments to generate output documentation (in HTML)...
I have two tables with millions of records. Every so often I need to join them and exclude just a handful of records where a bit(1) column is set to 1 instead of 0. I can do it with either, WHERE ...
