how do i get rows for each month which contain the store with the highest number of purchases?

SELECT t1.*
FROM purchase t1
JOIN ( SELECT t2.`month`, MAX(t2.numPurchases) numPurchases
       FROM purchase t2
       GROUP BY t2.`month` ) t3
    ON t1.`month` = t3.`month`
   AND t1.numPurchases = t3.numPurchases


How to concatenate a column after x rows?

Basically, you want to result the running total and group it

This requires iterative variable manipulation.

First, your sample data quadrupled:

mysql> drop database if exists all_db;
Query OK, 1 row affected (0.03 sec)

mysql> create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql> use all_db
Database changed
mysql> create table table1
    -> (
    ->     object varchar(255),
    ->     frequency tinyint unsigned
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 values
    -> ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into table1 select * from table1;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into table1 select * from table1;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+---------+-----------+
| object  | frequency |
+---------+-----------+
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
+---------+-----------+
20 rows in set (0.00 sec)

mysql>

Let's make the query that generates group values:

set @freq_limit = 20;
set @freq_count = 0;
set @freq_group = 1;
set @freq_sum   = 0;
select cc,GROUP_CONCAT(object) objects from
(
select * from
(
    select *,
        @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
        @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
        @freq_group    := @freq_group + @freq_groupinc                 cc,
        @freq_count    := @freq_count + frequency                      dd
    FROM table1
) AA) A GROUP BY cc;

Would you believe it works?

First I'll let you see the inner subquery's output

mysql> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA;
+---------+-----------+----+------+------+------+
| object  | frequency | bb | aa   | cc   | dd   |
+---------+-----------+----+------+------+------+
| object1 |         7 |  0 |    0 |    1 |    7 |
| object2 |         5 |  0 |    7 |    1 |   12 |
| object3 |         6 |  0 |   12 |    1 |   18 |
| object4 |         8 |  0 |   18 |    1 |   26 |
| object5 |         2 |  1 |    0 |    2 |    2 |
| object1 |         7 |  0 |    2 |    2 |    9 |
| object2 |         5 |  0 |    9 |    2 |   14 |
| object3 |         6 |  0 |   14 |    2 |   20 |
| object4 |         8 |  1 |    0 |    3 |    8 |
| object5 |         2 |  0 |    8 |    3 |   10 |
| object1 |         7 |  0 |   10 |    3 |   17 |
| object2 |         5 |  0 |   17 |    3 |   22 |
| object3 |         6 |  1 |    0 |    4 |    6 |
| object4 |         8 |  0 |    6 |    4 |   14 |
| object5 |         2 |  0 |   14 |    4 |   16 |
| object1 |         7 |  0 |   16 |    4 |   23 |
| object2 |         5 |  1 |    0 |    5 |    5 |
| object3 |         6 |  0 |    5 |    5 |   11 |
| object4 |         8 |  0 |   11 |    5 |   19 |
| object5 |         2 |  0 |   19 |    5 |   21 |
+---------+-----------+----+------+------+------+
20 rows in set (0.00 sec)

mysql>

Look at the cc column. It increments every time the sum exceeds @freq_limit (20) and resets.

Now just run GROUP_CONCAT on object, grouping by cc

mysql> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object2,object3,object5,object1 |
|    3 | object4,object5,object1,object2 |
|    4 | object5,object1,object3,object4 |
|    5 | object2,object3,object4,object5 |
+------+---------------------------------+
5 rows in set (0.00 sec)

mysql>

Just set @freq_limit to whatever value you need.

To show it works for just the 5 rows you supplied:

mysql> drop database if exists all_db;
Query OK, 1 row affected (0.04 sec)

mysql> create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql> use all_db
Database changed
mysql> create table table1
    -> (
    ->     object varchar(255),
    ->     frequency tinyint unsigned
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into table1 values
    -> ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object5                         |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!


Postgres - how to randomize two columns in a table

so that the locations stay accurate, but their association with a User
  is now random.

If I understand you right, it could work like this:

UPDATE tbl
SET lat = t.lat
  , lon = t.lon
FROM (
   SELECT t2.id, t1.lat, t1.lon
   FROM  (
      SELECT lat, lon, row_number() OVER () AS rn
      FROM   tbl t1
      ) t1
   JOIN  (
      SELECT id, row_number() OVER (ORDER BY random()) AS rn
      FROM   tbl t2
      ) t2 USING (rn)
   ) t
WHERE  tbl.id = t.id;

lat / lon have moved to another row, randomly. Every connection to it's original row is gone, but lat & lon stay together.


How to views all documents in a particular collection of database in MongoDB through mongo Shell?

OK, let's start from basics!

After you have connected to mongod with command mongo.

List databases with command show dbs

iot:PRIMARY> show dbs
    admin  0.000GB
    iot    0.020GB
    local  0.042GB
    test   0.000GB
    testi  0.000GB

Select one of the DB's with use iot command

iot:PRIMARY> use iot

switched to db iot

List collections on that DB with show collections command

iot:PRIMARY> show collections
data
header
key

Make query to one of those collections

iot:PRIMARY> db.header.find()
{ "_id" : "1b5caa", "temp1" : "Temperature", "pressure1" : "Pressure", "humidity1" : "Humidity", "uv1" : "UV", "BusV1" : "Solar Panel (V)", "Current1" : "Solar Panel Current (mA)", "BusV2" : "Battery (V)", "Current2" : "Battery Current (mA)" }
{ "_id" : "30444", "temp1" : "Temperature", "pressure1" : "Pressure", "humidity1" : "Humidity" }
{ "_id" : "239684", "temp1" : "Temperature", "pressure1" : "Pressure", "humidity1" : "Humidity" }

So, you need to be connected WANTED database with use command and you need to show the one collection what you want to query with db.<collection_name>.find()

How to see to what database I'm connected currently? Just give command db and you get the answer what is your current DB.


Explain this statement about filtering in SQL Server vs MySQL

"Very few syntax changes" -- Excuse my while I laugh!  SQL Server has a lot of features that MySQL does not have.  Any use of such is not just a syntax change, but possibly a query rewrite.

TOP 10 --> LIMIT 10 -- but only approximately.

ROW_NUMBER --> does not exist

SEQUENCE --> AUTO_INCREMENT -- but only approximately

dbname.dbo.tablename --> dbname.tablename

Which version of MySQL?  MySQL 8.0 has a good chance of having some of the fancier features being used in SQL Server.  MariaDB 10.4 is likely to come even closer to SQL Server, in that it handles some of the incompatibilities that MySQL does not cover.

JOINing across databases on the same instance of MySQL has never been a problem:

SELECT a.this, b.that
    FROM db1.tblx AS a
    JOIN db2.tbly AS b
    WHERE a.foo = 1
      AND b.blat < 5;

JOINing across two separate instances (of MySQL or MariaDB) on the same server, or separate instances on separate servers is a problem.  See MariaDB's "Connect".  The quote you provided leaves the term "database" ambiguous.  (Or it was pulled out of context.)  I hope that I have made it clear with the addition of the term "instance".

As for the rest of that link.  It is aimed at CEOs, not us grunts who will have to make the transition.  Too much 'hand-waving' and fluff; no meat.

I have not figured out what it means by "row-based filtering".  Maybe it is a snazzy feature in SQL Server.  But maybe it is not being used by your code.

Start a new Question and post in it "how do I change this query to MySQL" together with a non-trivial, but typical query.  You may see some of the 4 incompatibilities I mentioned above, or there may be other things waiting to trip up the conversion.


DB Optimization with 2 to the power of N - Approach clarification

So that solution is:

Creating a Bit Array in the user table to register a user to a health plan.
Then mapping the binary digit position to the health plan table.

Benefits:

Very little storage required, You might save $20 over the lifetime of the project.
Faster updates/deletes albeit perhaps not measurable.
Faster queries when not selecting by Health Plan

Problems:

The maximum number of distinct plans has to stay small. (32 or 64 depending on field size)
Nowhere to store plan start date, plan end date or other relevant data that you will probably need.
You need to decode the plan every time you use or apply it using bit operations.
There is no quick and easy way to do generic type queries that link the tables quickly.
Slower queries for health plan driven queries (i.e. all 'H10' Plans) without special indexes and query tools.

Issues with the original premise:

When subscribing to a new plan / leaving a plan you can just insert or delete the 'changed' records.

  Insert will add a new record ('U1',3).

  Delete will only need to delete the relevant record User_Health_Plan_Mapping(U1,10)

You seem to assume that db optimization is about minimizing the storage, it is more about the following.

Placing data so that it is easy to understand.
Storing it long term (think 5 generations of the code that talks to it)
Making the data extensible, supportable, searchable, reliable, secure, robust, backed up.
Optimizing the whole ecosystem (time to market, development time, operational time, hardware cost, business process, call center hours)

Optimizing the tables / indexes for the specific use-cases that is required of your application. Which is not about processor power, but indexes, direct access to hard disk and cache optimization (unless you have a trivial sized database that can stay in memory).


Cleaning up archives in Postgres after using systemd

The problem is that the PATH environment is different.

I had this line in recovery.conf:

archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/9.3/standby/archive %r'

And Postgres doesn't find pg_archivecleanup and silently ignores it.

Solution is to change the line to this:

archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /var/lib/pgsql/9.3/standby/archive %r'


When MySQL is really slow on windows (not 30ms slower) is nearly always a matter of a forgotten localhost instead of 127.0.0.1 in settings or URL. Very few times it is due to a very slow fragmented hard drive (acting slower due to windows complex ACL and parallel processes using it). MySQL is slower in some benchmarks on windows but the difference is so small that cant change your experience.


SQL Procedure Cache getting wiped out every 60 seconds

"A significant part of sql server process memory has been paged out..."

I would start with above message. This message means SQL Server working set memory is paging out process to disk. A widely used workaround is, and since you have SQL Server running on Azure VM, to provide SQL Server service account Locked pages in memory (LPIM) privilege. Please refer to this MS support article . To assign LPIM
To assign the Lock pages in memory user right, follow these steps:

Click Start, click Run, type gpedit.msc, and then click OK.

Note The Group Policy dialog box appears.

Expand Computer Configuration, and then expand Windows Settings.

Expand Security Settings, and then expand Local Policies.

Click User Rights Assignment, and then double-click Lock pages in memory.

In the Local Security Policy Settingdialog box, click Add User or Group.

In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.

Close the Group Policy dialog box.
9.Restart the SQL Server service.


How do I use 'N' in my prepared SELECT statement

You should bind them using the (relatively new) PDO_PARAM_STR_NATL constant. This constant is available in PHP 7.2 and up. It will cause the desired N prefix to be added.

$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);

In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818

The Github commit that is linked there shows how N is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129


BACKUP LOG cannot be performed because there is no current database backup

last_log_backup_lsn will be null in sys.database_recovery_status.


Clarifications on Transparent Data Encryption

Can we still access application that is working on this database with a connection string?

Yes.  TDE is implemented only at the storage layer.  Data is decrypted as it is read from disk.  Accordingly, nothing beyond the storage layer is affected.

  If TDE fails while it is encrypting data, will the encryption fail and break the .mdf file. How would I recover the database?

I would highly recommend getting a full backup prior to encrypting just to be safe.  However, it is highly unlikely that a problem will occur that will corrupt the database or leave it unusable.

  Are the .ldf and .mdf, still accessible and we can read and write this file's?

As mentioned previously, the encryption/decryption is done at the storage layer, and the system uses the .mdf and .ldf files in the same way that it uses them for unencrypted databases.  It simply encrypts data before writing, and decrypts data after reading.

  What are the database operations that will be affected while the TDE is in progress?

The system will use significant CPU and disk I/O as it has to read, encrypt, and write every data page in the database.

  Will TDE have affect on disk usage?

No, the size of the database will stay the same. If you're using compression or other technologies it will continue to work with it at TDE is done only when reading and writing pages to disk.

  Will TDE have any impact on database backups? Are both the .bak and .trn files encrypted automatically if TDE is enabled?

The backups may be somewhat slower depending on a varying number of factors and the backup files will be somewhat larger if you were previously using compressed backups.  How much will depend on the type of data and version of SQL Server (latest versions support compression on TDE databases).  Yes, the data in the .bak and .trn files will be encrypted.

  What are the areas that we need to concentrate on while TDE is being applied? Is an outage needed?

I would recommend concentrating on a nice cup of tea or coffee.  While you are enjoying the beverage of your choice, keep an eye on the SQL Server error log.  An outage is not required, but due to the resource utilization required to encrypt the database, having a maintenance window is ideal.

  Will there be any affect on SQL Server service packs or cumulative updates?

In general, the answer is "no."  However, if a defect is found that affects only TDE, then it may be advisable to install the CU that includes the fix.  We can't see into the future, but you will install the same CU and service packs whether TDE is used or not.  It is simply a feature of the database engine, so all updates to the database engine will include all of the TDE functionality.


SQL Server 2008 - combined index + other separate index on one column

The order of the columns in indexes is critical.
If your AdministratorUserId is the first column of the index, then separating the indexes will not improve anything.
The index works like an order by clause, meaning that it is sorted by the first column, then the second, then the third and so on. Because of that, an Index on AdministratorUserId is functionally equivalent to an index on AdministratorUserId and Name.  Note that it's possible in sql server to include columns to a non-clustered index that are non key columns. This will increase the storage size of the index but may improve performance of queries since it will save a lookup operation on columns that are included in the index.


Select * from (every table in an subselect)

This is not possible without using PL/SQL or a loop to generate the SQL in the program you're using to execute the SQL.


SQL query to return columns per value in value range?

I used the basis of a pivot table query on StackOverflow. Here is a similar post to the one that I used in my query below. Here is a more general link for pivot tables.

SELECT [Range]
       ,[0-20000]=ISNULL(SUM([0-20000]),0)
       ,[20001-21000]=ISNULL(SUM([20001-21000]),0)
       ,[21001-22000]=ISNULL(SUM([21001-22000]),0)
       ,[22001-23000]=ISNULL(SUM([22001-23000]),0)
       ,[23001-24000]=ISNULL(SUM([23001-24000]),0)
FROM (
    SELECT  CASE     WHEN Value2 BETWEEN 0 AND 70 THEN 'a 0-70'
                     WHEN Value2 BETWEEN 71 AND 90 THEN 'b 71-90'
                     WHEN Value2 BETWEEN 91 AND 110 THEN 'c 91-110'
                     WHEN Value2 BETWEEN 111 AND 130 THEN 'd 111-130'
                     WHEN Value2 BETWEEN 131 AND 150 THEN 'e 131-150'
                     WHEN Value2 BETWEEN 151 AND 170 THEN 'f 151-170'
                     ELSE 'Over 170' END AS Range,
                    *
     FROM
        (SELECT     value2,
                    Range2 AS [Value],
                    COUNT(*)  AS Foo
        FROM    (SELECT
        value2,
        CASE WHEN Value1 BETWEEN 0 AND 20000 THEN '0-20000'
                     WHEN Value1 BETWEEN 20001 AND 21000 THEN '20001-21000'
                     WHEN Value1 BETWEEN 21001 AND 22000 THEN '21001-22000'
                     WHEN Value1 BETWEEN 22001 AND 23000 THEN '22001-23000'
                     WHEN Value1 BETWEEN 23001 AND 24000 THEN '23001-24000'
                     ELSE 'Over 24000' END AS Range2
                FROM    (SELECT value1,value2
                         FROM StackDBA) AS derivedtbl_1) AS t
        GROUP BY Range2, value2) q1
        pivot
        (max(foo)
         for [value] in ([0-20000],[20001-21000],[21001-22000],[22001-23000],[23001-       24000])
        )as pvt
) grp

GROUP BY [Range]

SQL Fiddle


Create a plan guide to cache (lazy spool) CTE result

Is there ANY way to make the result come up with exactly 3 distinct guids and no more? I'm hoping to be able to better answer questions in
  future by including plan guides with CTE-type queries that are
  referenced multiple times to overcome some SQL Server CTE quirks.

Not today.  Non-recursive common table expressions (CTEs) are treated as in-line view definitions and expanded into the logical query tree at each place they are referenced (just like regular view definitions are) before optimization.  The logical tree for your query is:

LogOp_OrderByCOL: Union1007 ASC COL: Union1015 ASC
    LogOp_Project COL: Union1006 COL: Union1007 COL: Union1014 COL: Union1015
        LogOp_Join
            LogOp_ViewAnchor
                LogOp_UnionAll
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const

            LogOp_ViewAnchor
                LogOp_UnionAll
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const

Notice the two View Anchors and the six calls to the intrinsic function newid before optimization gets started.  Nevertheless, many people consider that the optimizer ought to be able to identify that the expanded sub-trees were originally a single referenced object and simplify accordingly. There have also been several Connect requests to allow explicit materialization of a CTE or derived table.

A more general implementation would have the optimizer consider materializing arbitrary common expressions to improve performance (CASE with a subquery is another example where problems can occur today).  Microsoft Research published a paper (PDF) on that back in 2007, though it remains unimplemented to date.  For the time being, we are limited to explicit materialization using things like table variables and temporary tables.

  SQLKiwi has mentioned drawing up plans in SSIS, is there a way or useful tool to assist in laying out a good plan for SQL Server?

This was just wishful thinking on my part, and went well beyond the idea of modifying plan guides.  It is possible, in principle, to write a tool to manipulate show plan XML directly, but without specific optimizer instrumentation using the tool would likely be a frustrating experience for the user (and the developer come to think of it).

In the particular context of this question, such a tool would still be unable to materialize the CTE contents in a way that could used by multiple consumers (to feed both inputs to the cross join in this case).  The optimizer and execution engine do support multi-consumer spools, but only for specific purposes - none of which could be made to apply to this particular example.

  While I'm not certain, I have a fairly strong hunch that the RelOps can be followed (Nested Loop, Lazy Spool) even if the query is not exactly the same as the plan  - for instance if you added 4 and 5 to the CTE, it still continues to use the same plan (seemingly - tested on SQL Server 2012 RTM Express).

There is a reasonable amount of flexibility here.  The broad shape of the XML plan is used to guide the search for a final plan (though many attributes are ignored completely e.g. partitioning type on exchanges) and the normal search rules are considerably relaxed as well.  For example, early pruning of alternatives based on cost considerations is disabled, the explicit introduction of cross joins is allowed, and scalar operations are ignored.

There are too many details to go into in depth, but the placement of Filters and Compute Scalars cannot be forced, and predicates of the form column = value are generalized so a plan containing X = 1 or X = @X can be applied to a query containing X = 502 or X = @Y.  This particular flexibility can help greatly in finding a natural plan to force.

In the specific example, constant Union All can always be implemented as a Constant Scan; the number of inputs to the Union All does not matter.


How do I find an invalid utf8 character "somewhere" in my MySQL/trac database

You could try turning on the general query log, which logs every query the server receives, and then use that information to see what's being queried, and then issue those queries yourself and examine the data.

To see the current setting for the general log (location and whether it's enabled):

mysql> SHOW VARIABLES LIKE 'general_log%';  # shows the on/off status and file path

To turn it on:

mysql> SET GLOBAL general_log = on; # or off to disable

Remember to turn if off again when you're done, to avoid filling the disk and potentially degrading performance with excessive I/O.

There are also options for logging to a table instead of a file.

mysql> SHOW VARIABLES LIKE 'log_output';    # shows whether logging to FILE or TABLE


I don't know anything about the Redgate tool you're using, but it sounds like "transaction per second" would include read and write transactions together.  So the likely explanation is that the one database with high log flushes per second is the only one with a significant write workload (thus generating log flushes).  The other databases may have lots of read transactions, but these don't generate any log activity.

Regarding your note at the end, the log activity in the middle of the night is probably due to index maintenance, which is a common thing to do overnight that generates lots of log records.

Without details about your databases, these are mostly just educated guesses based on the information provided in your question.


How to pass a variable to a stored procedure?

There are no global variables in PostgreSQL, sou you'd have to pass d_setlog as parameter to the procedure.

An alternative is to use a custom placeholder parameter in PostgreSQL:

The calling function does

SET mylog.active = true;

and the called procedure tests

IF current_setting('mylog.active', TRUE) = 'true' THEN ...

Note that such parameters are always strings.


Generating script in SQL server taking too long

An alternative to scripting objects using SSMS is to use Powershell.

Open a Windows command prompt and fire up Powershell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

#The SQL Server instance to want to connect to - This is the default instance on my computer
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(".");

#The database you want to script objects from
$database = $server.Databases["Test"];

#Instantiating the Powershell Scripter object
#so we can easily define how we want to script the object
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);

#Do we want Batch Terminators in the script? (GO statements)
$scripter.options.ScriptBatchTerminator = $true

#Where do we want to store file containing the scripted objects?
$scripter.options.FileName = "C:\Scripts\ScriptedTables.sql"

#Don't display the objects we're scripting, just output them to the file
$scripter.options.ToFileOnly = $true

#Script any permissions defined on the objects
$scripter.options.Permissions = $true

#Script all Dri objects
#For more innformation about DriAll - (https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions_properties.aspx)
$scripter.options.DriAll = $true

#Script table triggers
$scripter.options.Triggers = $true

#Script table indexes
$scripter.options.Indexes = $true

#A comma separated list of the tables you want to script
$table = $database.Tables["account","DateRange","DateDimension"];

#Invoke the scripting method to produce the output script file
$scripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);

For more information about the available Powershell scriping options, check here.


Update a unique column with an unknown number of values

Your requirements:

  The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones

suggest that a reasonable option would be your idea:

DELETE all the rows with same FK,
then INSERT all the news ones,

both (DELETE and INSERT statements) in one transaction. I tried in SQLdiddle and it worked just fine:

CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
) ;

INSERT INTO Names
  (ID_ITEM, Name)
VALUES
  (1, 'John'),
  (1, 'Amy'),
  (1, 'Joe') ;

START TRANSACTION ;
    DELETE FROM Names
    WHERE ID_ITEM = 1 ;

    INSERT INTO Names
      (ID_ITEM, Name)
    VALUES
      (1, 'John'),
      (1, 'Joe') ;
COMMIT ;

Then:

SELECT * FROM Names ;

returns:

ID   ID_ITEM   Name
--------------------
4     1        John
5     1        Joe


Select into existing table where ID is in another table

The SELECT ... INTO syntax is only for new tables.
I think you want:

INSERT dbo.TableB ( <column list> )
SELECT <column list>
FROM dbo.TableA
WHERE ID IN
  (
    SELECT ID
    FROM dbo.TableC
  ) ;


Database Restore Failed Due to Incompatible Version of SQL Server

+1 to Doug L., I'd recommend the same (export all data and structure into SQL script and data dumps, then import) but on top of that: if you can connect the two DBs you can cross-server SELECT/INSERT INTO the tables and data from old DB to the new one; you can also try upgrading the RTM server (if you are allowed to do that) - I'm unsure on this but I think if you install SP on the RTM it'll be considered as same version as the other guy.


SQL Server Patch Level Is Inaccurate, Causing Upgrade Issues

Don't be a dummy and run the right stuff. Using this patch https://www.microsoft.com/en-us/download/details.aspx?id=53168 and this command everything works great.

  C:\FILELOCATION\SQLServer2014SP2-KB3171021-x64-ENU.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances


PosgtreSQL - Change multiple fields collation in one query

There isn't a single SQL command that will do that for you.  You can write a loop in a different language, using the results from

SELECT table_name, column_name
  FROM information_schema.columns
  WHERE table_schema IN ('your_schemas')
    AND data_type = 'text'
    AND collation_name IS NULL;

or similar.


Why does my DB2 silent/unattended Install instead invokes DB2 Install wizard and GUI. Can someone help on ths issue?

You will need to use the wizard to install DB2 at least on the primary partition/system of the partitioned database system. See here for the instructions. Thereafter you can use a response file to set up the other partitions. The steps for using a response file mention as a requirement that the primary computer has been set up using the wizard.


What versions of SSMS have an Express edition?

The SSMSE was different in what it can manage as documented here.

Now you can use SSMS (SQL Server Management Studio) as a seperate download - SSMS or SQL server 2014 express - SSMSE that will manage all editions of sql server.

Aaron has a really good explanation here with screenshots :-)

  Express version of Management Studio, since 2012 Service Pack 1, has been a fully functional product with absolutely no differences


PostgreSQL - WAL segment file creation and naming conventions

According to the source code (assuming I'm looking at the correct location, Postgres will take an old WAL file and rename it based on the current timeline ID.  According to this comment, timeline ID of old files are ignored -- basically, the assumption is that if you have files with LSN < current LSN, it doesn't matter if it's from another timeline: it's just "old" and can be recycled


Return a fully dynamic table from an Oracle function

It is possible, though quite complicated, to write a pipelined table function that returns a variable structure.  Your pipeline table function could take the two arguments and use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime.  You can then use that in subsequent SQL statements as if it was a table, i.e.

SELECT *
  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

Dynamic SQL Pivoting
The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide


Add column with default in postgresql without table level lock

This problem is solved in PostgreSQL 11, which unfortunately isn't released yet at this moment. The release is planned for the third quarter of 2018.

There is a more detailed explanation in this blog post, but the short version is that in Version 11 the table won't have to be rewritten to add a default value to a column. So adding a column with a default should be pretty much instant.


This can happen if PostgreSQL is compiled against an older version of openssl than the version used to generate the keys.

Recompiling with the correct openssl library fixes the problem.


Cannot login to Oracle11g with System username

You mentioned you were on a Windows machine and a brand new DB.  A problem I've run into is the ORA_DBA group.  Windows has a habit of not allowing you to log into an Oracle DB with high level credentials if your OS account is not in the ORA_DBA group.  We found this problem when we migrated to Windows 2008.  I have no experience with Windows 7, but feel like its a safe assumption that it has a security setup similar to 2008, and this may be your problem.


How do I find an object that doesn't seem to exist in the objects table?

Object types in the same namespace as a table are:

Stand-alone procedures
Stand-alone stored functions
Packages
User-defined types
Sequences
Views
Private Synonyms
Materialized Views

Therefore it is probably one of those types. If you can select from it then it rules out the first five leaving it to be either a table, view, private synonym or materialized view.

When you searched in all_objects etc. you did use upper case? For example,

select *
  from ALL_OBJECTS
 where OBJECT_NAME = 'COT_NTN_PI_V';

If you use some tool like SQL Developer or Toad you can let it describe the object for you. Highlight the name in the tool and hit Shift-F4 in Developer or F4 in Toad. Toad provides a lot of description on the object while Developer, in the Details tab, will have a row with TABLE_NAME or MVIEW_NAME in it and that'll show you what it is.

Once you know what it is then it will make it easier to know how to drop it.


I would like to get a unique based on email address (MySQL)

Are you saying just all the unique email addresses?  Have you tried incorporating DISTINCT in any way?

As for the others, seems like an WHERE statement would be the way to go.


Why does SQL try to convert rows that are excluded by my where clause?

The view is not being used, since it is being "optimized away" by the query optimizer.  You can see this by looking at the query plan:

You could create an indexed materialized view, then use the NOEXPAND table hint on the target query to prevent this optimization.

An example:

USE tempdb;

IF OBJECT_ID(N'dbo.TestView', N'V') IS NOT NULL
BEGIN
    DROP VIEW dbo.TestView;
    DROP TABLE dbo.Test;
END
CREATE TABLE dbo.Test
(
    TableKey int
    , TestField nvarchar(24)
);
GO

Create a the view, with SCHEMABINDING:

CREATE VIEW dbo.TestView
WITH SCHEMABINDING
AS
    SELECT
        t.TableKey
        , t.TestField
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;
GO

Create an index on the view:

CREATE UNIQUE CLUSTERED INDEX PK_TestView
ON dbo.TestView (TableKey);
GO

Insert the test data:

INSERT INTO dbo.Test
select 0,        '0'
union select 1,  'Rejected'
union select 2,  'Unlinked'
union select 0,  '0'
union select 3,  '1'
union select 162,'1000'
union select 16, '10000'
union select 17, '10010'
union select 18, '10011'
union select 19, '10012'
union select 20, '10031'
union select 21, '10041'

Query the view:

SELECT *
FROM dbo.TestView WITH (NOEXPAND)
WHERE Testfield = 1000

Results:

╔══════════╦═══════════╗
║ TableKey ║ TestField ║
╠══════════╬═══════════╣
║ 162      ║ 1000      ║
╚══════════╩═══════════╝

The query plan:

Notice the triangle exclamation in the SELECT node in the query plan above - that is a warning:

  Type conversion in expression (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)=CONVERT_IMPLICIT(int,[@1],0)) may affect "SeekPlan" in query plan choice

The type conversion warning can be eliminated if we modify the view like so:

CREATE VIEW dbo.TestView
WITH SCHEMABINDING
AS
    SELECT
        t.TableKey
        , TestField = TRY_CONVERT(int, t.TestField)
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;

Now, the view will return the TestField column typed as an integer, and since we are using NOEXPAND in combination with persisted data, the implicit type conversion is eliminated:


You could try querying various dmvs with the following script.

SELECT TEXT,
   request_time,
   grant_time,
   query_cost,
   timeout_sec,
   wait_order,
   wait_time_ms,
   group_id,
   requested_memory_kb,
   granted_memory_kb,
   required_memory_kb,
   used_memory_kb,
   max_used_memory_kb,
   ideal_memory_kb,
   query_plan,
   'all information >',
   *
FROM   sys.dm_exec_query_memory_grants qmg
   OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) qp

It queries the following dmvs for information:

sys.dm_exec_query_memory_grants
sys.dm_exec_sql_text
sys.dm_exec_query_plan

This script was handed over to me by Ramesh Meyyappan at a course on "SQL Server Performance Tuning and Optimisation" at Microsoft SA, Wallisellen, Switzerland.


DynamoDB: How to model data that's shared and queried by all users?

I'm intrigued as well.  :)  I have a use in mind that would likewise have userID as the PK.

Sounds like you could put an 'announcements.json' file in an S3 bucket, then have your application read announcements from that file.

S3 can handle 5,500 read requests per second, according to:
https://aws.amazon.com/about-aws/whats-new/2018/07/amazon-s3-announces-increased-request-rate-performance/


Login failed for user even though it has read access

Verify if your user has permision to connect to database Engine.
Right click on Logins -> Properties -> Status.

Or grant CONNECT for user.

CREATE LOGIN [mydomain\mygroup] FROM WINDOWS;
GRANT CONNECT SQL TO [mydomain\mygroup]


How to query with the condition with subquery

Your question isn't terribly clear but I think what you are expecting is that if there is a row in TableB where column condition contains the text of a SQL clause like someField = 123 then the query will do:

SELECT * FROM table_A AS a
WHERE  a.id = 1
AND    (SELECT b.condition FROM tableB AS b)

would have the effect of:

SELECT * FROM table_A AS a
WHERE  a.id = 1
AND    (someField = 123)

This is not possible in the SQL standard nor directly possible in any SQL implementation that I am aware of. In MS SQL Server you could achieve this in a stored procedure by putting together ad-hoc SQL and running that via calls like EXEC (@VaraiableContainingSQL) and I presume the same is also possible in postgres' implementation of stored procedures, but it cannot be done in a simple single statement as described in the question.

As a side note: if those conditions are being provided by your users or any third party then this a very bad idea as it is an easy way to introduce SQL injection attacks. See https://www.owasp.org/index.php/SQL_Injection and https://xkcd.com/327/, amongst other references, for details and implications of this.

With regard to:

  table b itself is a foreign key for table A

... that sentence does not really make any sense. A table can not be a foreign key, a column (or group of columns) with a table can.

As suggested by a_horse_with_no_name you need to edit the question to provide much more detail of the schema (table layouts, their columns, keys and indexes), inputs, data, and intended outputs, to improve our ability to help you.


Setting default charset/collation for MySQL database

Yes, you can set the default character set and collation on various levels.

First, in your question, on the database level:

CREATE DATABASE IF NOT EXISTS foo
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

or

ALTER DATABASE foo
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

All new tables in that database should be created with the new character set and database if it's not specified in the table creation command (if it is specified in table command, the specification will override the database default).

You can set the default at a server level (so that all tables are created with the default for all databases unless explicitly stated otherwise) by providing the --character-set-server and --collation-server options in your my.cnf and restarting the server.

And finally, verify the client connection settings. This doc explains how the client connection can fine-tune (and override) the default settings.


When you create a database snapshot, it logs a message in the Error Log that says 'starting up database...' and the database name. You could filter the error log on those messages.

CREATE DATABASE test_snap ON
(
NAME = StackOverflow,
FILENAME ='D:\Data\stack_snap.mdf'
) AS SNAPSHOT OF StackOverflow;

When you run checkdb, the filename looks like this, and it's not listed under the Database Snapshots folder.

As for tracking the process down, I'd probably create a DDL Trigger, and use EVENTDATA() to figure it out.

CREATE TRIGGER database_create
ON ALL SERVER
FOR CREATE_DATABASE ...


I would add the following to that schema:

refer_mem_id - The member unique identifier of the person who has accepted the referral.

Remove the accepted column, the value in refer_mem_id gives you this information.


Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?

This is most likely due to the fact that at least the 5th WAL segment was used for recovery to get the database into a consistent state after an immediate stop. I'm not sure why the WAL segments wouldn't still be useful (e.g., for point in time recovery).


How to deal with datetime ranges in a table with separate columns for date and time?

You can use the simple condition:

WHERE (ev_date, ev_time) BETWEEN (start_date, start_time)
                             AND (end_date, end_time)

or this one:

WHERE ev_date + ev_time BETWEEN start_date + start_time
                            AND end_date + end_time

What's the best of these two depends on what indexes you have on the table. If you have an index on (ev_date, ev_time) use the first. If you can add a filtered index on (ev_date + ev_time), use the second.

*: I'm assuming that all these are parameters: start_date, start_time, end_date, end_time.


To answer my own question - the answer is NO - the imp command cannot create the user for me.


I believe the error was due to the mismatch in the foreign keys that cursor.executemany was trying to insert. If the foreign key to be inserted in TABLE2 is not present in TABLE1 (primary key holder), it throws an error.

It means that if TABLE 1 has 2 values in the primary key column then while inserting values in TABLE2, the values for the foreign key must exactly match to one of the primary key values in the TABLE1.


Why do OLAP cubes perform better than Relational databases on certain tasks?

OLAP cube stores data in an optimized way to provide a quick response to various types of complex queries by using dimensions and measures. Dimensions organize data with relation to an area of interest, such as customers, stores, or locations, time etc. Measure performs business-specific calculations to the required format. Both these drastically improves the performance of queries when come to reporting.

Advantages:

•   High Speed of Data Processing

•   Aggregated and Detailed Data

•   Multidimensional Data Representation

Limitations:

•   OLAP requires restructuring of data into a star/snowflake schema

•   There is a limited number of dimensions (fields) a single OLAP cube

•   It is nearly impossible to access transactional data in the OLAP cube

•   Changes to an OLAP cube requires a full update of the cube

ROLAP and MOLAP are two models in OLAP. ROLAP creates a multidimensional view of data dynamically while MOLAP already stores the static multidimensional view of data in MDDBs.


Load sybase .db into new sybase developer server

Your DB files are SQLAnywhere files, so first you will need to get SQLAnywhere.  Sybase has some documentation on upgrading databases from earlier versions here.


When you see CHECKPOINT as the log_reuse_wait_desc for that database, it is because no checkpoint has happened since the last time the log was truncated.

You can alleviate this issue by manually kicking off a CHECKPOINT command.

Supporting references:
Factors That Can Delay Log Truncation
Checkpoints and the Active Portion of the Log


How do I intentionally fragment a SQL Server Index?

One quick way I can imagine is creating a table with UNIQUEIDENTIFIER as a primary key and inserting lots of random values. This could be achieved using this script:

CREATE TABLE dbo.Tests (Id UNIQUEIDENTIFIER PRIMARY KEY);
GO
INSERT INTO dbo.Tests (Id)
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT NEWID()
FROM x AS x1, x AS x2, x AS x3, x AS x4, x AS x5, x AS x6;

This will generate million rows.

Knowing that NEWID() does not guarantee any ordering, SQL Server will have to insert into random spots in table - that's going to fragment the primary key.


MySql group the difference of 2 timestamp columns in minutes

SELECT  LEFT(startTime, 13) AS 'start of Hour',
        COUNT(*)  AS 'Total Jobs',
        SUM(TO_SECONDS(endTime) - TO_SECONDS(startTime) <= 300) AS '0to5m',
        SUM(TO_SECONDS(endTime) - TO_SECONDS(startTime) BETWEEN 301 AND 600) AS '5to10m',
        ...
    FROM tbl
    GROUP BY LEFT(startTime, 13)

Optionally have WHERE jobID = 12.

Problems:

Does the startTime determine which hour bucket it is in?
If you want a row of zeros for missing hours, then you need a table with all hours and a LEFT JOIN from it.


Should I include another column here to keep track of cost + tax or not?

This is a legal question.  The database schema should mimic what the laws mandate.

Since the tax_rate can change all too often, you really should include the rounded value of tax in the table.

On the other hand, total_cost is necessarily equal to subtotal of cost and tax, you should not include it.  A general rule in schema design is not to include any redundant data; that includes readily computable values.

Another thing to think about is "when" the tax is computed.  In some types of stores, the tax must be calculated from the subtotal.  In other types of stores, the laws may require computing the tax on each item (or multiple of items).

Perhaps you need a boolean flag called taxable.

Some laws say that some items are taxed and some are not.  For example, in California, food generally does not incur sales tax.  But a grocery store often sells non-food items, so there are two subtotals: "taxable" and "non-taxable".  To further confuse things there was, briefly, a "junk food tax" on various 'foods'.

Then comes "VAT" -- which seems to be reverse-computed from the total that the customer pays.


SQLCMD mode: environment variable syntax?

The sqlcmd syntax for setting variables is :setvar.  You can access environment variables from sqlcmd mode (using the $(variableName) syntax) however I find these case-sensitive and it's  a bit fussy about what you can and can't do.  Try this, hopefully it makes sense:

:setvar mypath "$(SystemRoot)"

:out $(SystemRoot)\test.txt     -- will work

print '$(mypath)'   -- oops won't work
print '$(SystemRoot)'   -- should work

go

:out stdout


Oracle Text - Changing SYNC to MANUAL not working always

Try this alter option.

alter index IXFTS_ABC_TABLE parameters ('REPLACE METADATA SYNC(Manual)');


Problems Linking the max value of an attribute in a table to its primary Key

Maybe you need simple

select count( distinct z.item_id) f, z.order_id as g
from  Order_item z, /* Item i, */ Ordert o
where o.order_id=z.order_id
group by z.order_id
order by f desc limit 1

?


Ubuntu / MySQL - How to choose from the various downloads?

Well, I have some advice for you.

Given https://help.ubuntu.com/12.04/serverguide/mysql.html,

  As of Ubuntu 12.04, MySQL 5.5 is installed by default

Go with the 5.5.x version they supply, and update accordingly.

If you don't want to do that and really want 5.5.29 for a specific reason, go for mysql-5.5.29-debian6.0-x86_64.deb as Ubuntu was derived from Debian.

You may have to remove the version of MySQL that came with your ubuntu installation.


ALL_TAB_COLUMNS

  LAST_ANALYZED

  Date on which this column was most recently analyzed

ALL_INDEXES

  LAST_ANALYZED

  Date on which this index was most recently analyzed


Count the number of occurences in a query result

I think you just need a window count():

COUNT(*) OVER (PARTITION BY AuctioneerSurname, AuctioneerName)

The query with the added computation - and using aliases for the long table names:

select
    app.AppointmentDate,  app.AppointmentTime,
    auc.AuctioneerName,  auc.AuctioneerSurname,
    buy.BuyerName,  buy.BuyerSurname,
    AuctioneerCount = count(*) over
                         (partition by auc.AuctioneerSurname, auc.AuctioneerName)
from
    Appointment as app
    inner join Auctioneer as auc
        on app.AuctioneerId = auc.AuctioneerId
    inner join Buyer as buy
        on app.BuyerId = buy.BuyerId
where
    app.AppointmentDate between '2017-01-01' and '2017-12-31' ;


How to mix pairs of rows without JOIN (in a temporary table)?

This type of data transformation is known as a PIVOT but unfortunately MySQL does not have a pivot function.  However, this can be replicated using both an aggregate function and a CASE statement:

select pair,
  max(case when type = 'key' then col1 end) Key_col1,
  max(case when type = 'key' then col2 end) Key_col2,
  max(case when type = 'value' then col1 end) value_col1,
  max(case when type = 'value' then col2 end) value_col2
from temp
group by pair

See SQL Fiddle with Demo

The result of the query is:

| PAIR | KEY_COL1 | KEY_COL2 | VALUE_COL1 | VALUE_COL2 |
--------------------------------------------------------
|    1 |     key1 |     key2 |     value1 |     value2 |
|    2 |     key3 |     key4 |     value3 |     value4 |
|    3 |     key5 |     key6 |     value5 |     value6 |


You can do only one thing

var mystring = "2020-03-22T20:38:48.347Z";
mystring = mystring.replace(/Z/, '');
mystring = mystring.replace(/T/, ' ');
console.log(mystring);

2020-03-22 20:38:48.347

see jsfiddle

Replace the T and the Z the resulting string is no problem for Mysql


How to ORDER BY typical software release versions like X.Y.Z?

To produce your desired output, you can simply:

SELECT id, version
FROM   versions
ORDER  BY string_to_array(version, '.')::int[];

One can cast a whole text array to an integer array (to sort 9 before 10).
One can ORDER BY array types. This is the same as ordering by each of the elements. And shorter arrays come before longer ones with identical leading part.

db<>fiddle here
Old SQL Fiddle.


[INS-30131]Initial setup required for the execution of installer validations failed. Encountering this error in Fedora 20

To see the real reason of this error in console output, use:

./runInstaller -debug -logLevel finest

In my case the reason was no execute permission for the file:
    '/tmp/CVU_12.1.0.2.0_oracle/exectask.sh'

So you simply need to add execute permission to all scripts and executable files in this directory, without exiting the installer.
Open the second console and use:

cd /tmp/CVU_12.1.0.2.0_oracle
chmod +x *.sh
chmod +x exectask
chmod +x cvuhelper

Then resume installation. It should install without further problems!

P.S.

Important remark about starting the database. It is not sufficient to use:

dbstart $ORACLE_HOME

and / or

lsnrctl start

You need to log in to the idle instance:

sqlplus SYS AS SYSDBA

and then use sqlplus command:

startup;

Then you can connect SYS@ORCL AS SYSDBA without error ORA-12514, assuming that you have set environment variable ORACLE_SID=orcl

Read also Oracle 12c Post Installation Mandatory Steps.


How do I query the running pg_hba configuration?

Version 10 and above

There is a very convenient view for this called pg_hba_file_rules.

table pg_hba_file_rules ;

 line_number │   type    │   database    │  user_name  │  address  │                 netmask                 │ auth_method │ options │ error
─────────────┼───────────┼───────────────┼─────────────┼───────────┼─────────────────────────────────────────┼─────────────┼─────────┼───────
           3 │ local     │ {all}         │ {all}       │           │                                         │ trust       │         │
           4 │ hostssl   │ {all}         │ {+users}    │ 127.0.0.1 │ 255.255.255.255                         │ pam         │         │
           5 │ host      │ {all}         │ {all}       │ 127.0.0.1 │ 255.255.255.255                         │ md5         │         │
           6 │ hostssl   │ {all}         │ {+users}    │ ::1       │ ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff │ pam         │         │
           7 │ host      │ {all}         │ {all}       │ ::1       │ ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff │ md5         │         │
           8 │ hostssl   │ {replication} │ {standby}   │ all       │                                         │ md5         │         │
           9 │ hostnossl │ {all}         │ {all}       │ all       │                                         │ reject      │         │

Up to version 9.6

For earlier versions, it is a bit cumbersome and error-prone (and works only for superusers, see the notes).

The only built-in way of reading pg_hba.conf I can currently imagine is using pg_read_file().

SELECT pg_read_file('pg_hba.conf');

will return you the whole file, in a single row, which then contains the newline characters (be them whichever flavour your OS likes:

[...]
 # TYPE  DATABASE        USER            ADDRESS                 METHOD↵
                                                                       ↵
 # "local" is for Unix domain socket connections only                  ↵
 local   all             all                                     md5   ↵
 # IPv4 local connections:                                             ↵
 host    all             all             127.0.0.1/32            md5   ↵
[...]

You can further process this to your liking.

Notes:

The documentation tells us the following:

  The functions shown in Table 9-86 [pg_read_file() included - me] provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files. Use of these functions is restricted to superusers.

The bit about superusers shouldn't be a problem for you, as you are setting up replication.  If pg_hba.conf is not in the mentioned directories, you can add a symlink to it to trick the system:

postgres@Z22309: cd /var/lib/postgresql/9.5/main
postgres@Z22309:~/9.5/main$ ln -s /etc/postgresql/9.5/main/pg_hba.conf pg_hba.conf

All this won't tell you if the file was changed but the config not reloaded.  For this, you have to compare the results of pg_conf_load_time() and pg_stat_file(), as shown below.  At the same time, you will still don't know exactly which changes were made since the last reload.  Also, as mentioned in a comment, pg_conf_load_time() will advance even when loading the file was not successful, leading to confusion - at least I am not aware how one could tell the reloading was successful or not.

SELECT pg_conf_load_time() > modification FROM pg_stat_file('pg_hba.conf');

In another answer, I show a way how to split the lines returned above.


What would cause newly restored SQL Databases to show tables, but display an 'Invalid Object' error when trying to view table data?

It sounds like the objects are still there but are owned by the schema nurse.  So for example instead of dbo.Table1 it would be nurse.Table1.

Your users aren't see the tables because they are not including the schema part of the name.  If the schema is not included

SELECT * FROM Table1

Then the schema used is the default schema from the database principal (user).  In this case, as you said, it's dbo which is the norm.  An easy way to check this would be to do the following:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'yourtablename'

If the TABLE_SCHEMA value is nurse then that's your problem.  Try changing the default_schema of your principals like this:

alter user [username] with default_schema = [nurse];

Also you could query the tables by including the two part name:

SELECT * FROM nurse.Table1


Removing records from database periodically that surpass timeline

Recently I set up a similar archiving solution.  The numbers and the target are a bit different (the latter being moving old data into an archive partition), but the idea is the same.

First, you will need an index on the timestamp, for quickly finding the rows to be deleted.  To avoid excessive locking and blocking other processes as a consequence, this is preferably done with the CONCURRENTLY option.

Second, you have to keep your table tidy by vacuuming it regularly - if you have a relatively recent PostgreSQL version, there are chances that the database will take care of it with autovacuum.

The actual DELETE statement will be a rather simple one.  In my case, I use a function for this, so that we can keep the database logic under version control.  This may look like

CREATE OR REPLACE FUNCTION delete_function()
RETURNS void
LANGUAGE sql
AS $body$
DELETE FROM big_table
WHERE report_timestamp < now() - interval '6 months';
$body$;

You will need a script (say a bash script), which simply calls the statement, like

#!/bin/bash

psql {connection parameters} -c 'SELECT delete_function()'

The only thing that remains is setting up a cron job which calls this script at the desired intervals.

There is still one thing to consider, namely that the first delete would be a huge one.  I would prevent this by deleting the big amount of old data in suitably sized chunks, again for avoiding blocking other processes that want to access the table.  The size of these chunks can be very different based on your circumstances.  Note that this is more or less a manual process, unless you have already a 'chunker' in use (as we have).


How to do Postgresql 9.2 version check in Linux?

That means that there is no file named

postgresql-9.2.24.service

in your systemd directory.

The exact location varies between Linux distributions, but it is usually /usr/lib/systemd/system or /lib/systemd/system.


Calendar Event table - best practice setup for range queries and individual retrieval

So there's probably a few different things you can try but what stands out to me from your schema and examples are the following:

Avoid using UniqueIdentifiers if possible, and try to use Int or BigInt instead. (Int has over 8 billion values, BigInt over 18 quintillion.) It'll definitely make a difference in indexing performance.

Create a unique clustered index on your PK field (which it looks like you're already doing, so you can keep it as is).

Create a nonclustered index on CalendarId and dtStart.

Avoid SELECT * in your queries, unless you're using every column every time, it's faster to only select the exact columns you need (and either way it's best practice to list your columns out instead of using * for reliability reasons).

*5. If you're able to do #4 then when you create the nonclustered index in #3, you can use the INCLUDE keyword to specify names of the columns you'll be normally selecting. This eliminates key lookups and will be a little more performant.

You can look into creating an indexed view on top of:
select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId

You just have to replace * with the actual columns you'll be using, and you need a unique value (likely the primary key of one of the tables) that you can create a unique clustered index on. This will basically store the data of this view as if it was a table and can save you some time. You also get the added benefit of being able to create additional nonclustered indexes on it if necessary as well.
This Brent Ozar article is also useful to be aware of for the limitations of Indexed Views: What You Can (and Can’t) Do With Indexed Views

You can also look into Table / Index Compression but this will be dependent on how much data is in your tables / indexes and if the bigger hardware bottleneck is I/O vs CPU.


Orders and Quotes Tables - Database Design

How about combine them? Keep the idea of an order table as the base, and add a status field with values:

Entered - data being typed in, just started
Quoted - quote provided to customer
Ordered - customer has accepted the quote and the quote converted to an order

You mentioned keeping the original which is doable if you have an ORDERS_HISTORY or ORDERS_VERSION table. Basically, whenever the status of an order changes, update the main table but keep a snapshot of what it used to look like in the history table. This would let you pull up old versions of the quote/order to quote against.


MongoDB can't add new replica set member [rsSync] SEVERE: Got signal: 6

In the dump you have above, I see this error

  SocketException: remote: xx.xx.xx.xx:27017 error: 9001 socket
  exception [RECV_ERROR] server [168.63.252.61:27017]
  2014-11-02T22:53:29.099+0000 [rsSync] DBClientCursor::init call()
  failed 2014-11-02T22:53:29.307+0000 [rsSync] replSet initial sync
  exception: 13386 socket error for mapping query 0 attempts remaining
  2014-11-02T22:53:36.113+0000 [conn2013] end connection xx.xx.xx.xx:

Which indicates that there is a socket exception error. To me that means that the configuration of a replica is incorrect or the host definded in the replica set configuration can't be contacted (firewall, network issues, mongodb not running)

STARTUP2 indicates that threads were forked for replication. While in the STARTUP2 state, the member creates threads to handle internal replication operations, and then the member transitions to the:replstate:RECOVERING state. During initial sync, members stay in:replstate:STARTUP2 until the all data transfers and all indexes finishing building. Members in the STARTUP2 state are not eligible to vote.

Running rs.status() from the primary will show you how the primary sees the secondaries and what address (host/port) it's using to contact them. You can also run rs.status() from a secondary after running the rs.slaveOK() command.

Try connnecting from the secondary to the primary and vis-versa using the mongo client.
Or you can use telnet mongo_host mongo_port_number from either server to see if both are listening to the ports you've defined.


Most probably this is fake alarm. the space UNDO tablespace is allocated differently then on other tablespaces. Oracle simply does not clean out old versions of block unless necessary. There are no real data in this tablespace.

You UNDO space was really full you would get ORA-01555 Snapshot too old, or other types of errors. Check the view v$undostat.

PS: what type of monitoring tool are you using?


Post replication powershell script - on subscriber

Add another job step to the snapshot agent which runs sqlcmd with what ever T-SQL command needs to be run using the -S parameter to specify to connect to the remote SQL Server.


Concurrent SELECT and UPDATE in multiprocessing python

As I dug deeper into the matter at hand trying various approaches I found out that there is no solution to this problem with PostgreSQL versions <= 9.4.

PostgreSQL 9.5 (current development version) has a new feature called SKIP LOCKED which does skip rows that have been locked by a query using SELECT * ... FOR UPDATE

For more information on the new feature have a look at http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/


What's a good client tool to access Oracle RDBMS?

I haven't found anything 'free' that works as well as Oracle SQL Developer. In the past it was pretty buggy and frustrating. I am on a 3.0 Early Adopter release now and very happy with the improvements. I am a heavy user and have had no crashes in the 3 months since going to this release. I wasn't sold on the interface early on but I am familiar with it now and very productive with it. I also make great use of the User Defined Reports -- all the SQL Queries I had floating in text documents for troubleshooting I wrap into their own custom reports.

Toad is an option... but can be pricey. It satisfies your want-list. If you want to pay for all the bells and whistles and have to support a lot of differenct databases it might be worth it for you.

A lot of developers  here use AllRoundAutomations "PL/SQL Developer" tool, so many in fact that we bought a site license. Single seats can be had for US$180 per seat. It fits your requirements. I have not personally used the debugger but it is a mature product, I would expect it to work as advertised.


Which columns need index in recursive table and soft delete

Try this index for some noticeable improvement:

CREATE NONCLUSTERED INDEX [CatIndex1] ON [dbo].[Category]
(
    [ParentID] ASC
)
INCLUDE (   [CategoryID],
    [CategoryName],
    [DisplayORder])

I am interested in the time reduction. Please let me know your findings.

p.s. consider sorting only once with the ORDER BY if you're running it multiple times. Perhaps put data into a C# DataTable or SQL temp table and sort at the very end of all retrieval operations.


Can I restore a SQLServer db from URL but not from azure?

No, the FROM URL syntax of the restore command is meant only to work with Azure.  You must find another way of restoring the backups from Amazon storage.

Unfortunately, I don't know much about Amazon's offerings, but did find.

You can mount the storage as a drive in windows.  Then restore from
that.
Does Amazon offer an SMB accessible storage solution much like
Azure Files?

But failing those, you will just need to copy locally and then restore.  But this is probably your best bet anyways, as you want to avoid involving delicate restore operations with pulling (supposedly large) files across the internet.  By doing this in two distinct steps, you can more easily identify where it failed.


Is there a better way to create dynamic time averaged series over a dynamic frequency?

Your join between the tables is embedded in a function making it really hard for the optimizer to do anything smart with it. I guess it has to compare every row in one table against every other row in the other table.

Rewriting your join with a range check should be a lot faster. I also added a primary key to your table variable to remove a sort operation from the query plan and I turned your table variable into a temp table instead. The difference in my tests was that the query plan started to use Parallelism.

declare @start datetime;
declare @end datetime;
set @start = '20120301';
set @end = '20120303';
declare @interval int;
set @interval = 300;

create table #tpart
(
  dt datetime primary key
);

with CTE_TimeTable
as
(
  select @start as [date]
  union all
  select dateadd(second ,@interval, [date])
  from CTE_TimeTable
  where dateadd(second, @interval, [date]) <= @end
)
insert into #tpart
select [date]
from CTE_TimeTable
option (maxrecursion 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)
from clean.data c
  inner join #tpart t
    on c.Date >= t.dt and
       c.Date < dateadd(second, @interval, t.dt)
group by t.dt;

drop table #tpart;

Note: This query does not return exactly the same intervals as your query does. It will divide the date range into equally sized parts where your query had one half interval at the start and one half interval at the end of the range. It is of course possible to modify the query to do be equivalent to your query if that is desired.

Update

I tested on a table with a total of 1036801 rows and with 34560 in the interval 2012-03-01 to 2012-03-03. In my tests the original query takes 4.1 seconds. The query above takes 0.1 seconds.

Script to generate the test data:

create table clean.data
(
    Date datetime primary key,
    x1 int,
    x2 int
);

go

with C as
(
  select cast('20120201' as datetime) as D
  union all
  select dateadd(second, 5, D)
  from C
  where D < '20120401'
)
insert into clean.data(Date, x1, x2)
select D, checksum(newid()) % 1000, checksum(newid()) % 1000
from C
option (maxrecursion 0);


What big limitations should I expect from Linked SQL Servers?

How transparent will it be to the application code? How much change should I expect?

Not transparent at all. Expect major changes.

You should be prepared for very substantial performance degradation.

Distributed Query (the framework for linked servers) uses a general OLEDB model whatever the server on the other end happens to be. It is true that a SQL Server target may be able to offer more complete information (metadata, statistics etc.), but the result is still nowhere near as tightly integrated or capable as a native cross-database operation.

Remote queries have a deserved reputation for slow performance, and poor plan choices by the optimizer. Statements that change data (delete, insert, update, merge) are particularly prone since the basic model is often that of a cursor.

If you never need to perform ad-hoc cross-instance queries, you might be able to hand-tune each stored query for acceptable performance, but this is a lot of work, and success is by no means guaranteed.

For cross-instance bulk operations, you would be much better off using real bulk operations (bcp, BULK INSERT, SSIS...etc.) between instances than using linked servers.

All that said, the basic idea seems much more trouble than it is worth to me. Specify hardware that will work within the constraints of Standard Edition; or, if the client requires higher performance, get a bigger server and use Enterprise Edition.


Recommendations for a SQL Server 2014 Database Administrators Course

I would recommend signing up for a trial of PluralSight. They have a TON of content, including courses specifically aimed at 70-461 and 70-462. I have been a few of their courses and they are excellent: http://pluralsight.com/training/Courses#sql-server

Also, you may want to check out the MCM Readiness videos on Microsoft's site. They are a little out of date, but for things like index internals it won't matter a whole lot: http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx


You have to edit the Forward Engineering Template (FE Template).

Simple said, hard done.

As each class of entities (table, views, SPs, etc.) have their own block of generation code, it has to be done to each major entry points.

As for the SQL Server 2005 Erwin's template at its 7.3 version, the code below, injected at the beginning of the 'Create Entity' template will do for table DMLs.

/*

  We will save a global flag '_SQL_USE_DATABASE' is used to flag a variable of the same name initialization */
/

@ifnot( IsGlobalFlagSet("_SQL_USE_DATABASE") )
{
    Set( "_SQL_USE_DATABASE", "")
    SetGlobalFlag( "_SQL_USE_DATABASE" )
}

@if( IsPropertyNotNull("SQLServer_File_Group_Ref") )
{
    PushReference( "SQLServer_File_Group_Ref" )
    PushOwner

    @ifnot( Equal(Value("_SQL_USE_DATABASE"), Property("Name")) )
    {
        FE::Bucket( "90" )
        "USE " Property("Name")
        FE::EndOfStatement

        Set( "_SQL_USE_DATABASE", Property("Name") )
    }

    Pop
    Pop
}
@else
{
    Set( "_SQL_USE_DATABASE", "" )
}


PostgreSQL fsync off for a single database

You might want to check unlogged tables, they do not produce WALs at all. But of course if crash happen - all data from them will be lost and they cannot be replicated.

Also there is much safer setting synchronous_commit which could reduce IO and it's possible to set it per session or per user/database.


Retrieve Names of Check Constraints Specific to a Column

how can I select all of the check constraints associated with that column?

pg_constraint.conkey is an array column, so we join on a.attnum = ANY(c.conkey) and the oid of the relation additionally. Like:
SELECT conname -- pg_get_constraintdef(oid), *
FROM   pg_constraint c
JOIN   pg_attribute  a ON a.attrelid = c.conrelid     -- !
                      AND a.attnum   = ANY(c.conkey)  -- !
WHERE  c.conrelid = 'hypothetical_table'::regclass
AND    c.contype = 'c'  -- c = check constraint
AND    a.attname = 'some_col';

But I am doubtful of your overall objective:

When I rename a column in a table, I would like to dynamically rename the matching check constraints as well.

CHECK constraints are always table constraints in Postgres and can reference any number of table columns. Even when formulated as column constraints! The manual:

Column Check Constraints
The SQL standard says that CHECK column constraints can only refer
to the column they apply to; only CHECK table constraints can refer
to multiple columns. PostgreSQL does not enforce this restriction; it
treats column and table check constraints alike.

So some_col and some_col_also can be referenced at the same time (multiple times). And both can be in the constraint name. I don't see how a completely dynamic solution can be bullet-proof, unless you can rely on a strict naming convention. (Can you really?). Is the following a reference to some_col or some_col_also?
some_col_also_is_a_fruit  -- oh, wait!

Rewriting the constraint expression itself also demands attention.


How to index for a query with independent range conditions?

MySQL can do Index Intersection (more commonly know as Index Merging). However, in your case, I have something more reasonable to suggest.
You mentioned in a comment

X is a VARCHAR and Y is a DATE

For the sake of example, we'll use this query
SELECT A,B FROM mydb.mytable
WHERE X < ? and Y > ? ;

You need to create an index, but you must examine which order of columns forms the smaller range for a first column. Is it (X,Y) or (Y,X) ?
CHOOSING X (VARCHAR) FIRST : This means looking over at least 1296 (36 * 36) possible alphanumeric ranges. Then, within each character range, compare a range of dates up to today's date. 36 is 26 Alphabetic and 10 Numeric.
CHOOSING Y (DATE) FIRST : This means looking a range of dates up to today's date. Then, within each date, compare a range at least 1296 (36 * 36) possible alphanumeric ranges.
You can decide which by running these two queries:
SELECT COUNT(DISTINCT Y) DateCount FROM mydb.mytable WHERE Y > ?
SELECT COUNT(DISTINCT LEFT(X,2)) CharCount FROM mydb.mytable WHERE X < ?

Which combination forms the smaller set? This would be hard to judge if you have something lopsided like X < 'Umbrella' or Y > '1929-10-29'. These are ranges too unreasonable for a query optimizer to want to use an index.
If the date range is short, create an index with (Y,X)
If the string value range is short, create an index with (X,Y)
I would create both indexes and let the Query Optimizer make the decision. Yet, there is something else you can do. Look again at the sample query.
SELECT A,B FROM mydb.mytable
WHERE X < ? and Y > ? ;

You can boost performance by creating a covering index. That's an index that has all the needed data in the index which bypasses the need to read data from the table. In the case of the above query, you could create the two indexes like this:
ALTER TABLE mydb.mytable ADD INDEX idx1 (Y,X,A,B);
ALTER TABLE mydb.mytable ADD INDEX idx2 (X,Y,A,B);

With these two indexes, the Query Optimizer will do an index scan in the worst case scenario since all the needed data is already in the index and there is no need to get anything else from the table. This is very beneficial for both MyISAM and InnoDB.

MyISAM : only caches index pages into the key buffer
InnoDB : only index page are loaded into the InnoDB Buffer Pool
See my post What are the main differences between InnoDB and MyISAM?

As for Covering Indexes, I mentioned using them in past posts

Jan 01, 2013 : Performance impact of including result column in indexed columns
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?
Oct 17, 2012 : Combining columns in index
Mar 12, 2012 : How to index this table (a_level, b_level, item_id)

SUMMARY
Dealing with independent ranges may make accurately forecasting which index combination is more relevant can be challenging. Furthermore, imagine the anxiety you will have as to how the  query optimizer will evaluate thing. In light of these, you will need to go with covering indexes as much as possible. You will have to also schedule a periodic run of ANALYZE TABLE mydb.mytable; if the table experiences heavy INSERTs, UPDATEs, and DELETEs.


How to decide whether to enable MySQL query cache using InnoDB

I had discussed this in earlier posts

Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?
Sep 26, 2013 : query cache hit value is not changing in my database

InnoDB internals has a very hands-on approach to the query cache since it micromanages query cache entry invalidation. In the MySQL 4.x days, the query cache was disabled by default for the sake of InnoDB. In MySQL 5.x, it can be a toss-up. The comments from @akuzminsky show local issues center around  InnoDB.

Given the fact you are using Amazon RDS, you find the following challenges

Changing some InnoDB settings can be cumbersome
Some InnoDB settings cannot be changed
Tuning aspects of InnoDB has limits due to RDS

I would take away any guess work and just leave the query cache disabled. If having a bigger buffer pool size (as suggested in @akuzminsky's second comment) you will have to migrate to a larger Server Model ($$$ Cha-Ching $$$). Here are the Buffer Pool sizes for the Server Models

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)


How to conditionally raise an error in MySQL without stored procedure

Retrieve column from a table with multiple rows in the else part

select case when foo = "bar" then 1 else (select table_name from information_schema.tables) end;

As an example, let's use @foo instead of foo

mysql> select case when @foo = "bar" then 1 else (select table_name from information_schema.tables) end;
ERROR 1242 (21000): Subquery returns more than 1 row

Give it a Try !!


Moving a table to a different tablespace in Oracle using Online Redefinition Vs Alter table move tablespace

Those two methods are very different.

Alter table mytable move mytablespace

will move all the data blocks into the new tablespace. while it doing so, the table will be locked for DML.

DBMS_REDEFINITION will allow you to create a new table on the desired tablespace and sync the data between them ONLINE. when the sync ends the tables names will be switched.
you will have to be careful with DBMS_REDEFINITION as you have to create the new table youself including indexes, constraints, partition, privs, etc.


@zadam,I would like to say some bit about media set. I also expect some more details from our expert like Mr. Aaron Bertrand & some other StackExchange DBA Experts.
First of all , what is media set.

A successful backup operation adds a single backup set to the media
set.

The backup set is described in terms of the media set to which the backup belongs.If the backup media consists of only one media family, that family contains the entire backup set.
A media set is created on the backup media during a backup operation by formatting the backup media.
Backing Up to an Existing Media Set
When you are backing up to an existing media set, you have the following two options:

Append to the existing backup set.

Note Here:- which is the default behavior of the BACKUP, can be explicitly specified by using the NOINIT option.

Overwrite all existing backup sets with the current backup, leaving
the current media header in place.

Note Here :- Overwriting existing backup sets is specified by using the INIT option of the BACKUP statement.
For Example:-

BACKUP DATABASE DatabaseName
TO DISK = 'd:\bu\mm_labeled.bak'
WITH MEDIANAME = 'BackupsToTheDDrive',
MEDIADESCRIPTION = 'Portable Drive Used for Backups',
INIT;

As per Grant Fritchey Here The MediaSetID is a GUID that uniquely defines this set.It will also be recorded in msdb so you can link a backup to a particular server.
For example :

SELECT  *
FROM    dbo.backupmediaset AS b
WHERE   b.media_uuid = '720C1417-3889-4730-9D33-74EBF11E854A' ;

For example if your full backup TSQL will be like that
Use DatabaseName
Go
Backup Database DatabaseName
To Disk=N'E:\Database_Backup\DatabaseName_Backup_16022016\DatabaseName.bak'
with FORMAT,
Medianame='E_SQLServerBackup',
Name='DatabaseName-Full Database Backup'
GO

Every volume of backup media (disk file or tape) contains a media
header that is created when by the first backup operation that uses
the tape (or disk). That header remains intact until the media is
reformatted.

If you will execute this TSQL statement to know about media_set_id & 'family_sequence_number'.
select * from msdb.dbo.backupmediafamily;

Here i would like to say that if you shall take backup in Disk. So always your 'family_sequence_number' will be 1. But if you shall take backup in Tape then 'family_sequence_number' will be like the sequence number of the initial tape is 1, the sequence number of the second tape is 2, and so forth.

The media header contains all of the information required to identify the media (disk file or tape) and its place within the media family to which it belongs. This information includes:
The name of the media.
The media name is optionally, but we recommend consistently using media names that clearly identify your media. A media name is assigned by whoever formats the media.
The unique identification number of the media set.
The number of media families in the media set.
The sequence number of the media family containing this media.
The unique identification number for the media family.
The sequence number of this media in the media family. For a disk file, this value is always 1.
The Microsoft Tape Format media label or the media description (in free-form text).
The name of the backup software that wrote the label.
The unique vendor identification number of the software vendor that formatted the media.
The date and time the label was written.

Note :- In case of Disk, The sequence number of this media in the media family.This value is always 1.
For your ref of Tape Media Set along with Backup see Media Sets, Media Families, and Backup Sets (SQL Server)  and
Here


Slecting specific query returnned data related records from another table

See if this give you the desired results.

--demo setup
DECLARE @ProductTable TABLE (
  productId INTEGER,
  productName VARCHAR(10),
  unitPrice INTEGER,
  supplierId INTEGER
);

INSERT INTO @ProductTable
  (productId, productName, unitPrice, supplierId)
VALUES
  ('1', 'Iphone8', '1500', '3'),
  ('2', 'Nokia 6', '800', '1'),
  ('3', 'Samsung S9', '1500', '2');

DECLARE @SupplierTable TABLE (
  supplierId INTEGER,
  supplierName VARCHAR(11),
  country VARCHAR(5)
);

INSERT INTO @SupplierTable
  (supplierId, supplierName, country)
VALUES
  ('1', 'Nokia Inc', 'India'),
  ('2', 'Samsung Inc', 'Korea'),
  ('3', 'Apple Inc', 'USA');

--solution
SELECT p.productId
    ,p.unitPrice
    ,p.supplierId
    ,s.supplierName
    ,s.country
FROM @ProductTable P
JOIN @SupplierTable s
    ON s.supplierId = P.supplierId
where p.unitPrice=1500
order by p.productId

| productId | unitPrice | supplierId | supplierName | country |
|-----------|-----------|------------|--------------|---------|
| 1         | 1500      | 3          | Apple Inc    | USA     |
| 3         | 1500      | 2          | Samsung Inc  | Korea   |