I have drawn a simple ERD: The query I perform: select s.*, count(c.store_id), count(p.store_id) from store s inner join customer c on c.store_id = s.id inner join product p on p.store_id = p.id group by s.id, s.store_name, s.address The result:…
I am currently working on a project that is to display data that come from a MySQL database I realized a script in PHP which is composed of 3 buttons, which is characterized by a value of the database. I…
I have the following tables that model a book database: CREATE TABLE Country ( ISO_3166 CHAR(2) PRIMARY KEY, CountryName VARCHAR(256), CID varchar(16) ); CREATE TABLE Users ( UID INT PRIMARY KEY, Username VARCHAR(256), DoB DATE, Age INT, ISO_3166 CHAR(2) REFERENCES…
My application has a myriad of queries and I wanted to get the execution plan of some of them. Most if not all of the queries have multiple parameters and I can't find how to get the execution plan for…
I'm working with a Postgres table named orders that looks like this: user_id product order_date 1 pants 7/1/2022 2 shirt 6/1/2022 1 socks 3/17/2023 3 pants 2/17/2023 4 shirt 3/13/2023 2 pants 8/15/2022 1 hat 4/15/2022 5 hat 3/14/2023 2…
I have a huge table with over 10M rows. I need to update the following rows: select * from customers_card_psp where status="not_added" and psp_id = 2 order by id limit 50000 Since I have a composite index on customers_card_psp(psp_id, status),…
This question is related to this previous question of mine: I have a table "Friendship", which looks like this: CREATE TABLE Friendship ( User1 INT, User2 INT, PRIMARY KEY (User1, User2), CONSTRAINT columns_cannot_equal CHECK (User1 <> User2) ); Here is…
I need help regarding my sql project. I have a table in which football results in column as VARCHAR. And I want to make it impossible to insert data into them other than in this format: number:number For example: 2:1,…
I have this simple structure in my database CREATE TABLE species ( _id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE compatibility ( _id INTEGER PRIMARY KEY, speciesA INTEGER, speciesB INTEGER, compatibility TINYINT NOT NULL ); I want…
I am using postgres and pgadmin. I am trying to insert a record into table called movie_revenues, I get the following error: duplicate key value violates unique constrain "movie_revenues_pkey" Key (revenue_id) = (9) already exists This is the record I…