Rows to be combined based on mutual keys (B_02 in this case) using a Pipe delimiter
How we can achieve it using mysql
100% Private Proxies – Fast, Anonymous, Quality, Unlimited USA Private Proxy!
Get your private proxies now!
Rows to be combined based on mutual keys (B_02 in this case) using a Pipe delimiter
How we can achieve it using mysql
I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option.
So basically I am having two data directories i.e. One is old data directory (v9.5) and the current one in running state (v12.4).
Yesterday I have dropped two tables of size 700MB and 300MB.
After connecting to postgres using psql utility I can see database size whose tables was dropped got decreased (with \l+ ) but what is making me worry is that only a few MBs have been freed from storage partition.
I have run vacuumdb only on that database but no luck. I have checked if any deleted open file is there on OS level using lsof but there is none.
Looking for the solution.
Say I had the following 2 queries, summing values from separate tables.
I would like the sum of recorded time
SELECT SUM(minutes) as recorded_minutes, SUM(hours) as recorded_hours FROM recorded_time WHERE project_id = 1
To be combined with the sum of budgeted time in a single row
SELECT SUM(minutes) as budgeted_minutes, SUM(hours) as budgeted_hours FROM budgeted_time WHERE project_id = 1
Is it possible to do this in a single query?
I have two tables, Reviews and Critic
I’m pretty new to RDBMS and I was wondering if the connection between Review.rID and Critic.Review is redundant?
My reasoning is that a single critic can have many reviews but each review is unique so I need a way to enforce the uniqueness of the Reviews. I did this via Review.rID which is unique.
However, I am already connecting the critic to the review by including a criticID within Review, so is it necessary for me to also connect a reviewID to a critic within Critic?
I am using sqlplus and i need to achieve this result, where stipend is the max stipend for each faculty
There are 3 tables, student ( with name and surname), faculty ( with faculty name) and money (with the stipends), connections are faculty to student and student to money
This is my current code, which onyly returns one main maximums from one faculty
SELECT ROW_NUMBER() OVER (ORDER BY faculty_name DESC) AS "Nr.", faculty.faculty_name, student.surname, student.name, money.stipend AS "STIPEND" FROM faculty INNER JOIN student ON faculty.id_faculty = student.faculty_id INNER JOIN money ON student.id_student = money.student_id GROUP By money.stipend, faculty.faculty_name, student.surname, student.name having max(money.stipend) = ( select max(stipend) FROM faculty INNER JOIN student ON faculty.id_faculty = student.faculty_id INNER JOIN money ON student.id_student = money.student_id );
how would i get this end result
What are the benefits of not having a clustered index on a table in SQL server. Will a
SELECT * INTO TABLE_A FROM TABLE_B
Be faster if TABLE_A
is a heap? Which operation will benefit if the table is a heap? I am quite sure UPDATE
‘s and DELETE
‘s will benefit from a clustered index. What about INSERTS
? My understanding is that INSERT
"might" benefit from the table being a heap, both in term of speed but also other resources and hardware (I/O, CPU, memory and storage…).
What is the most scarce resource in terms of hardware? In terms of storage is a heap going to occupy less space? Is disk storage not the least expensive resource? If so is it rational to keep table as heap in order to save disk space? How will a heap affect CPU and I/O with SELECT
, INSERT
, UPDATE
and DELETE
? What cost goes up when table is a heap and we SELECT
, UPDATE
and DELETE
from it?
Thansk
I have a situation where I have three tables: user
, assignment
and test
. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.
I could write a sql statement like this: insert into test (name, user_id, assignment_id) values ("final exam", 1, 1)
which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.
The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I’m wondering if I can accomplish both in one query though. I’m not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.
I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?
I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.
There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.
Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.
I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records.
This table has a fixed structure, so if there are 5 such entities in my system, there will be 5 different tables with the same schema.
The schema is generic with one of the columns in the schema as JSON for flexibility. I do not expect queries based on the fields in the JSON. I expect the following queries on each entity:
I expect thousands of such entities to be created on the fly so in turn there will be thousands of tables in the database.
In future when one of these entities have fulfilled their purpose, the table would be simply deleted.
I expect most of these tables to have not more than 100 rows while there will be a few with at least 1M rows as time goes by. This design makes data easy to query as my application can determine the table name from the entity name.
Is this a bad design?
Is there a limit to the number of tables in a database in RDBMS (the above design is with Postgresql 11 in mind) keeping performance in mind?
Should I use any different datastore to achieve this other than RDBMS? Any suggestions?
A processor uses $ 36$ bit physical address and $ 32$ bit virtual addresses, with a page frame size of $ 4$ Kbytes. Each page table entry is of size $ 4$ bytes. A three level page table is used for virtual to physical address translation, where the virtual address is used as follows:
The number of bits required for addressing the next level page table(or page frame) in the page table entry of the first, second and third level page tables are respectively
(a) $ \text{20,20,20}$
(b) $ \text{24,24,24}$
(c) $ \text{24,24,20}$
(d) $ \text{25,25,24}$
I got the answer as (b) as in each page table we are after all required to point to a frame number in the main memory for the base address.
But in this site here it says that the answer is (d) and the logic which they use of working in chunks of $ 2^{11} B$ I feel ruins or does not go in with the entire concept of paging. Why the system shall suddenly start storing data in main memory in chucks other than the granularity defined by the page size of frame size. I do not get it.