mariadb – Get rows above and below a certain row, based on two criteria SQL (with SUM)

Say I have a table like so:

+----------+---------+------+---------------------+
|student_no|level_id |points|      timestamp      |
+----------+---------+------+---------------------+
|     4    |    1    |  70  | 2021-01-14 21:50:38 |
|     3    |    2    |  90  | 2021-01-12 15:38:0  |
|     1    |    1    |  20  | 2021-01-14 13:10:12 |
|     5    |    1    |  50  | 2021-01-13 12:32:11 |
|     7    |    1    |  50  | 2021-01-14 17:15:20 |
|     8    |    1    |  55  | 2021-01-14 09:20:00 |
|    10    |    2    |  99  | 2021-01-15 10:50:38 |
|     2    |    1    |  45  | 2021-01-15 10:50:38 |
+----------+---------+------+---------------------+

What I want to do is find the total points for each person (student_no), and show 5 of these rows in a table, with a certain row (e.g. where id=5) in the middle and have the two rows above and below it (in the correct order – with highest at the top). This will be like a score board but only showing the user’s total points (over all levels) with the two above and two below. So because points could be equal, the timestamp column will also need to be used – so if two scores are equal, then the first person to get the score is shown above the other person.

I have tried this below but it is not outputting what I need.

SELECT 
    student_no, SUM(points)
FROM
    (
    (SELECT 
        student_no, SUM(points), 1 orderby
    FROM student_points a
    HAVING
        SUM(points) > (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
    ORDER BY SUM(points) ASC LIMIT 3) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 2 orderby
    FROM student_points a
    WHERE student_no = 40204123) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 3 orderby
    FROM student_points a
    HAVING
        SUM(points) <= (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
            AND student_no <> 40204123
    ORDER BY SUM(points) DESC LIMIT 3)
    ) t1
ORDER BY orderby ASC , SUM(points) DESC

This is a dbfiddle of what I am trying:
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=5ada81241513c9a0be0b6c95ad0f2947

sql server – Trying to deploy sp_blitzIndex – but fails

I’m trying to deploy the sp_blitzIndex procedure from Brent Ozar. I’m running it in a query window, in SSMS and get the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘tempdb..SQLServerCheckup_2A98B846_4179_496B_AAF8_60B405E3ED68_waits’.
Starting run. sp_BlitzIndex(TM) v7.99999 – December 11, 2020

This is being done on the master DB. I’m running SQL Server 2019 developer edition on my local machine.
Any suggestions on a fix?

php – Date is being sent as 0000-00-00 and returned as 11/30/-0001 when I use SQL update statement

<?php 
include 'db.php';
$WAGE = 14;
$TAX = 0.01;
$SS = 0.062;
$MEDI = 0.0145;
$GOAL = 0.75;

if($_SERVER('REQUEST_METHOD') === "POST") {
    // Variables
    $id = htmlspecialchars($_POST('id'));
    $begDate = htmlspecialchars($_POST('date1'));
    $endDate = htmlspecialchars($_POST('date2'));
    $hours = htmlspecialchars($_POST('hours'));
    $total = number_format($hours * $WAGE, 2, '.', '');
    $taxDeducted = number_format($total * $TAX, 2,'.', '');
    $SSDeducted = number_format($total * $SS, '2', '.', '');
    $MEDIDeducted = number_format($total * $MEDI, '2', '.', '');
    $deducted = number_format($taxDeducted + $MEDIDeducted + $SSDeducted, 2, '.', '');
    $total -= number_format($deducted, 2, '.', '');
    $save = number_format($GOAL * $total, 2, '.', '');
    $date = $_POST('date1');
    // SQL
    $sql = "UPDATE hours SET begDate=$date, endDate=$endDate, hours=$hours, deducted=$deducted, save=$save, total=$total WHERE id=$id;";

    // EXECUTE
    $stmt = $conn->prepare($sql);
    $stmt->execute();
}

?>
<?php 
include 'header.php'; 
include 'db.php';
if(isset($_GET('id'))) :
    $id = $_GET('id');
    $sql = "SELECT * FROM hours WHERE id=$id;";
    $stmt = $conn->prepare($sql);

    $stmt->execute();
    $result = $stmt->fetchAll();
    foreach($result as $res) :
    $begDate = $res('begDate');
    $endDate = $res('endDate');
    $hours = $res('hours');
    endforeach;
?>
<div class="container form bg-light p-5">
    <h1 class="text-center my-5">Add a week</h1>
    <form action="editForm.php" method="post">
        <input type="hidden" name="id" value="<?= $id; ?>" id="id">
        <div class="row">
            <div class="form-group col-md-5">
                <input type="date" name="date1" id="date1" value="<?=$begDate;?>"  class="form-control" required>
            </div>
            <div class="col-md-2 text-center">
                <h3>TO</h3>
            </div>
            <div class="col-md-5 form-group">
                <input type="date" name="date2" id="date2" value="<?=$endDate;?>" class="form-control" required>
            </div>
        </div>

        <div class="row">
            <div class="col-md-3 form-group mx-auto mt-5">
                <input class="form-control" value="<?= $hours;?>" type="float" name="hours" id="hours" placeholder="Hours this period" required>
            </div>
        </div>

        <div class="row text-center mt-5">
            <div class="form-group col-md-3 mx-auto">
                <input type="submit" value="SUBMIT" name="submit" class="btn btn-secondary">
            </div>
        </div>
    </form>
</div>
<?php endif; ?>

<script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/js/bootstrap.bundle.min.js" integrity="sha384-ygbV9kiqUc6oa4msXn9868pTtWMgiQaeYH7/t7LECLbyPA2x65Kgf80OJFdroafW" crossorigin="anonymous"></script>
<script>
    $('form').on('submit', function(e) {
        e.preventDefault();
        $.ajax({
            url: 'editForm.php',
            method: 'post',
            data: $('form').serialize()
        })
        .done(function(res, status) {
            if(status == 'success') {
                window.location.href = 'index.php';
            }
        })
        .fail(function(res, status) {
            if(status == 'error') {
                console.log('error');
            }
        });
    })

</script>
<?php include 'footer.php'; ?>

mysql – Uso de várias condicionais numa query de SQL

Estou com dúvida em como realisar uma query com condicionais diferentes.
A query se trata de uma validação de login, preciso verificar se o dado contido no input usuario é igual ao username ou ao email ou ao cpf e se a senha está correta.
Usei esta query

SELECT * FROM student WHERE username = '$user' OR email = '$user' OR cpf = '$user' AND password = '$password'

Porém quando a senha está incorreta ele ainda retorna o usuário su pudesse separar por parênteses como em operações matemáticas algo assim daria certo

SELECT * FROM student WHERE (username = '$user' OR email = '$user' OR cpf = '$user') AND password = '$password'

azure sql database – Vertical Elastic Query is not identifying Object

I did create an External Datasource, identical to the guide described here https://www.sqlservercentral.com/articles/cross-database-queries-in-azure-sql-database .The process is pretty simple, so just for illustration.

CREATE MASTER KEY ENCRYPTION ...
CREATE DATABASE SCOPED CREDENTIAL ...

CREATE LOGIN <externaldbname> WITH PASSWORD = '<somepassword1>'; 
CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE EXTERNAL DATA SOURCE ...
CREATE EXTERNAL TABLE ..

All executed successfully. Now when I try to select something from the external database, this error is raised

Msg 46823, Level 16, State 1, Line 3
Error retrieving data from sar*****.database.windows.net.carDB. The underlying error message received was: ‘Invalid object name ‘SalesLT.Addresstest’.’.

I didn’t get the error msg as the method I followed is used/shown in multiple website and tutorials.

sql – ON DELETE SET DEFAULT não funciona no PostgreSQL?

Estou tentando deixar o valor default 'SEM CURSO' na tabela aluno quando o curso for excluído, porém da forma que estou fazendo o valor da chave estrangeira não está ficando default e sim NULL. Estou fazendo o script da forma incorreta ou o PostgreSQL não suporta ON DELETE SET DEFAULT?

O estranho é que mesmo que o PostgreSQL não suporte, o script roda normalmente.

Segue:

CREATE TABLE curso (
    nome varchar(30) primary key default 'SEM CURSO'
);

CREATE TABLE aluno (
    nro_matric int primary key,
    nome varchar(50),
    curso_id varchar(30),
        foreign key (curso_id) references curso(nome) on delete set default on update cascade
);

insert into curso(nome) values ('Sistemas de Informação');
insert into curso(nome) values ('Ciencia da Computação');

insert into aluno(nro_matric, nome, curso_id) values (201701, 'Marcio Alves', 'Sistemas de Informação');
insert into aluno(nro_matric, nome, curso_id) values (201702, 'Caio Ribeiro', 'Sistemas de Informação');
insert into aluno(nro_matric, nome, curso_id) values (201703, 'Leticia Alves', 'Ciencia da Computação');
insert into aluno(nro_matric, nome, curso_id) values (201704, 'Fabio Antonio', 'Sistemas de Informação');

delete from curso where nome = 'Ciencia da Computação';

select * from aluno;

Unable to use SSL Certificates in SQL Server on Linux

Hello everyone so here is the problem:

SQL Server 2019 installed on Ubuntu 20.04 (working before setting SSL certificates)

I was following the Official guide of Microsoft to secure my SQL server with SSL on Ubuntu 20.04.

I firstly tried using directly CA certificates created with Certbot and Let’s encrypt. I spend hours trying without any luck.

So I decided to try the tutorial as it was with self signed certificates following the guide AS IS without any change.

These are my commands:

root@racknerd:~# openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=beta.mydomain.com' -keyout mssql.key -out mssql.pem -days 365
Generating a RSA private key
................+++++
.......................................................................................+++++
writing new private key to 'mssql.key'
-----
root@racknerd:~# sudo chown mssql:mssql mssql.pem mssql.key
root@racknerd:~# sudo chmod 600 mssql.pem mssql.key
root@racknerd:~# sudo mv mssql.pem /etc/ssl/certs/
root@racknerd:~# sudo mv mssql.key /etc/ssl/private/
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/mssql.key
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.forceencryption 0
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# systemctl stop mssql-server

Obviously I used a real domain that it’s mapped to this machine IP but this is not the problem.
Following Microsoft’s guide I restart the service and I keep having this error:

Unable to open one or more of the user-specified certificate file(s)

2021-01-16 08:58:05.35 spid23s     Error: 49940, Severity: 16, State: 1.
2021-01-16 08:58:05.35 spid23s     Unable to open one or more of the user-specified certificate file(s). Verify that the certificate file(s) exist with read permissions for the user and group running SQL Server.
2021-01-16 08:58:05.37 spid23s     Error: 49939, Severity: 16, State: 1.
2021-01-16 08:58:05.37 spid23s     Unable to initialize user-specified certificate configuration. The server is being shut down. Verify that the certificate is correctly configured. Error(30). State(51).
2021-01-16 08:58:05.39 spid21s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

This is the same error that I get when I do the process using the Let’s Encrypt certificates. I suppose so that stating I set the right permission on the certificates, the right owner there is something wrong with the official documentation of Microsoft or there is no way to make this work. I saw that someone else had the same problems but didn’t find any fix for that, has something changed since then?

Many thanks