Is it possible to make a query?

|
Swapnil 2022-06-01 18:41:19
Nagarjun 2022-06-01 18:37:17
SELECT salary,rank() over(order by salary desc) as rankn from EMPLOYEE

How can I get only rows with 2nd rank?
Does anyone know

select a.*
from (put ur main query here) a
where a.rankn = 2

Nagarjun 2022-06-01 18:42:10
Swapnil 2022-06-01 18:41:19
select a.*
from (put ur main query here) a
where a.rankn = 2

Thank you I’ll try

Nagarjun 2022-06-01 18:44:27
Swapnil 2022-06-01 18:41:19
select a.*
from (put ur main query here) a
where a.rankn = 2

Thank you so much it worked

hgrgdagddsada 2022-06-01 18:54:04
mysql_en-21239.jpg
how to fix this want to change the db name
Vamshimuskam 2022-06-01 19:58:29
Hi guys
I’ve a column ‘number’ it should be 10digits else it’s invalid can anybody help with query to validate that coulmn in sql
Sachin Kansal 2022-06-01 20:32:09
Vamshimuskam 2022-06-01 19:58:29
Hi guys
I’ve a column ‘number’ it should be 10digits else it’s invalid can anybody help with query to validate that coulmn in sql

One method can be to Add check constraint on value must be greater than 999999999 and smaller than 10000000000

Sachin Kansal 2022-06-01 20:33:41
It’s 9 times 9 so min value can be inserted is 1000000000
Sachin Kansal 2022-06-01 20:34:13
And max value can be 9999999999
Swapnil 2022-06-01 21:25:33
Vamshimuskam 2022-06-01 19:58:29
Hi guys
I’ve a column ‘number’ it should be 10digits else it’s invalid can anybody help with query to validate that coulmn in sql

If you do not have to introduce check constraint then
Select Case when length(columnname) <> 10
then ‘invalid’
else
‘Valid’ end as valid_invalid_column
from table_name

Vamshimuskam 2022-06-02 03:58:29
Sachin Kansal 2022-06-01 20:32:09
One method can be to Add check constraint on value must be greater than 999999999 and smaller than 10000000000

Ohh thanks

Amrendra_Singh1 2022-06-02 08:24:15
How to schedule a full backup in mysql plz help. I am using RHEL 7
smatch 2022-06-02 08:27:07
Amrendra_Singh1 2022-06-02 08:24:15
How to schedule a full backup in mysql plz help. I am using RHEL 7

Use Cron and xtrabackup

Amrendra_Singh1 2022-06-02 09:44:19
smatch 2022-06-02 08:27:07
Use Cron and xtrabackup

Thanks can you please provide me the the script?

Naveen K 2022-06-02 10:01:52
Anyone available for give offline work support?
Location: Anantapur, Hyderabad or banglore, if available please ping me
suricruise1 2022-06-02 12:37:13
mysql_en-21258.jpg

suricruise1 2022-06-02 12:37:13
mysql_en-21257.jpg
How to write subquery in 4th question
Swapnil 2022-06-02 12:46:16
suricruise1 2022-06-02 12:37:13

select distinct sname from
sailors
where sid in (select sid from reserves where bid in
(select bid from boats)
)

suricruise1 2022-06-02 12:58:39
Swapnil 2022-06-02 12:46:16
select distinct sname from
sailors
where sid in (select sid from reserves where bid in
(select bid from boats)
)

Tq you swapnil

s Sravs 2022-06-02 18:12:36
Hi all we will provide full course videos with material of below domains
SQL dba azure dba az admin az Devops AWS devops if anyone need DM me
Amrendra_Singh1 2022-06-02 18:13:16
Anyone MYSQL DBA TRAINER here?
mkval3 2022-06-03 06:57:14
mysql_en-21272.jpg
Hi, I’m finding it difficult to create a sql query with this problem. Is it possible to make a query? I’ve tried with basic queries and also subqueries. But still not getting the expected result. Are there friends who can help?
Thank you
mkval3 2022-06-03 06:59:16
If there are friends who are curious and want to try with this problem, here is an example of the database
Rocky 2022-06-03 09:00:07
OperationalError: (MySQLdb._exceptions.OperationalError) (2005, “Unknown server host ‘ ——–‘ (11003)”)
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Rocky 2022-06-03 09:00:21
kindly help to resolve above error
Swapnil 2022-06-03 11:52:16
mkval3 2022-06-03 06:57:14
Hi, I’m finding it difficult to create a sql query with this problem. Is it possible to make a query? I’ve tried with basic queries and also subqueries. But still not getting the expected result. Are there friends who can help?
Thank you

CREATE TABLE tb_city (
id_city int ,
city varchar(30) DEFAULT NULL,
PRIMARY KEY (id_city)
);

insert into tb_city(id_city,city) values
(1,’Banda Aceh’),
(2,’Langsa’),
(3,’Lhokseumawe’),
(4,’Sabang’),
(5,’Subulussalam’),
(6,’Bandung’),
(7,’Bekasi’),
(8,’Bogor’),
(9,’Cimahi’),
(10,’Cirebon’),
(11,’Depok’),
(12,’Sukabumi’),
(13,’Tasikmalaya’),
(14,’Banjar’),
(15,’Magelang’),
(16,’Pekalongan’),
(17,’Salatiga’),
(18,’Semarang’),
(19,’Surakarta’),
(20,’Tegas’),
(21,’Batu’),
(22,’Blitar’),
(23,’Kediri’),
(24,’Madiun’),
(25,’Malang’),
(26,’Mojokerto’),
(27,’Pasuruan’),
(28,’Probolinggo’),
(29,’Surabaya’),
(30,’Binjai’),
(31,’Gunungsitoli’),
(32,’Medan’),
(33,’Padangsidimpuan’),
(34,’Pematangsiantar’),
(35,’Tanjung Balai’),
(36,’Tebing Tinggi’),
(37,’Bukit Tinggi’),
(38,’Padang’),
(39,’Padang Panjang’),
(40,’Pariaman’),
(41,’Payakumbuh’),
(42,’Sawahlunto’),
(43,’Solok’);

CREATE TABLE tb_prov (
id_prov int,
name_prov varchar(30) DEFAULT NULL,
PRIMARY KEY (id_prov)
);

insert into tb_prov(id_prov,name_prov) values
(1,’West Java’),
(2,’Central Java’),
(3,’East Java’),
(4,’North Sumatra’),
(5,’West Sumatra’),
(6,’Aceh’);

CREATE TABLE tb_cases (
id int,
id_prov int DEFAULT NULL,
id_city int DEFAULT NULL,
cases varchar(30) DEFAULT NULL,
PRIMARY KEY (id),
–KEY id_prov (id_prov),
–KEY id_kota (id_city),
CONSTRAINT tb_cases_ibfk_1 FOREIGN KEY (id_prov) REFERENCES tb_prov (id_prov) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT tb_cases_ibfk_2 FOREIGN KEY (id_city) REFERENCES tb_city (id_city) ON DELETE CASCADE ON UPDATE CASCADE
);

insert into tb_cases(id,id_prov,id_city,cases) values
(1,1,7,’86’),
(2,1,6,’75’),
(3,2,15,’93’),
(4,2,19,’154′),
(5,1,9,’136′),
(6,3,23,’89’),
(7,3,25,’177′),
(8,1,11,’193′),
(9,1,8,’99’),
(10,1,10,’144′),
(11,2,16,’79’),
(12,2,17,’156′),
(13,2,18,’68’),
(14,3,24,’180′),
(15,3,22,’73’),
(16,3,21,’148′),
(17,6,1,’83’),
(18,6,5,’95’),
(19,6,2,’40’),
(20,6,3,’163′),
(21,6,4,’58’),
(23,5,38,’68’),
(24,5,37,’190′),
(25,5,43,’64’),
(26,5,41,’59’),
(27,5,40,’123′),
(28,4,31,’77’),
(29,4,32,’187′),
(30,4,35,’74’),
(31,4,34,’52’),
(32,4,33,’143′);

select a.name_prov as prov,city,a.cases
from
(select c.name_prov,b.city,a.cases,
row_number () over (partition by c.name_prov order by a.cases::int desc) as rn
from tb_cases a
join tb_city b on a.id_city = b.id_city
join tb_prov c on a.id_prov = c.id_prov
order by c.name_prov
) a
order by a.name_prov,rn

Output:
prov | city | cases
—————+—————–+——-
Aceh | Lhokseumawe | 163
Aceh | Subulussalam | 95
Aceh | Banda Aceh | 83
Aceh | Sabang | 58
Aceh | Langsa | 40
Central Java | Salatiga | 156
Central Java | Surakarta | 154
Central Java | Magelang | 93
Central Java | Pekalongan | 79
Central Java | Semarang | 68
East Java | Madiun | 180
East Java | Malang | 177
East Java | Batu | 148
East Java | Kediri | 89
East Java | Blitar | 73
North Sumatra | Medan | 187
North Sumatra | Padangsidimpuan | 143
North Sumatra | Gunungsitoli | 77
North Sumatra | Tanjung Balai | 74
North Sumatra | Pematangsiantar | 52
West Java | Depok | 193
West Java | Cirebon | 144
West Java | Cimahi | 136
West Java | Bogor | 99
West Java | Bekasi | 86
West Java | Bandung | 75
West Sumatra | Bukit Tinggi | 190
West Sumatra | Pariaman | 123
West Sumatra | Padang | 68
West Sumatra | Solok | 64
West Sumatra | Payakumbuh | 59

mkval3 2022-06-03 15:40:48
Swapnil 2022-06-03 11:52:16
CREATE TABLE tb_city (
id_city int ,
city varchar(30) DEFAULT NULL,
PRIMARY KEY (id_city)
);

insert into tb_city(id_city,city) values
(1,’Banda Aceh’),
(2,’Langsa’),
(3,’Lhokseumawe’),
(4,’Sabang’),
(5,’Subulussalam’),
(6,’Bandung’),
(7,’Bekasi’),
(8,’Bogor’),
(9,’Cimahi’),
(10,’Cirebon’),
(11,’Depok’),
(12,’Sukabumi’),
(13,’Tasikmalaya’),
(14,’Banjar’),
(15,’Magelang’),
(16,’Pekalongan’),
(17,’Salatiga’),
(18,’Semarang’),
(19,’Surakarta’),
(20,’Tegas’),
(21,’Batu’),
(22,’Blitar’),
(23,’Kediri’),
(24,’Madiun’),
(25,’Malang’),
(26,’Mojokerto’),
(27,’Pasuruan’),
(28,’Probolinggo’),
(29,’Surabaya’),
(30,’Binjai’),
(31,’Gunungsitoli’),
(32,’Medan’),
(33,’Padangsidimpuan’),
(34,’Pematangsiantar’),
(35,’Tanjung Balai’),
(36,’Tebing Tinggi’),
(37,’Bukit Tinggi’),
(38,’Padang’),
(39,’Padang Panjang’),
(40,’Pariaman’),
(41,’Payakumbuh’),
(42,’Sawahlunto’),
(43,’Solok’);

CREATE TABLE tb_prov (
id_prov int,
name_prov varchar(30) DEFAULT NULL,
PRIMARY KEY (id_prov)
);

insert into tb_prov(id_prov,name_prov) values
(1,’West Java’),
(2,’Central Java’),
(3,’East Java’),
(4,’North Sumatra’),
(5,’West Sumatra’),
(6,’Aceh’);

CREATE TABLE tb_cases (
id int,
id_prov int DEFAULT NULL,
id_city int DEFAULT NULL,
cases varchar(30) DEFAULT NULL,
PRIMARY KEY (id),
–KEY id_prov (id_prov),
–KEY id_kota (id_city),
CONSTRAINT tb_cases_ibfk_1 FOREIGN KEY (id_prov) REFERENCES tb_prov (id_prov) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT tb_cases_ibfk_2 FOREIGN KEY (id_city) REFERENCES tb_city (id_city) ON DELETE CASCADE ON UPDATE CASCADE
);

insert into tb_cases(id,id_prov,id_city,cases) values
(1,1,7,’86’),
(2,1,6,’75’),
(3,2,15,’93’),
(4,2,19,’154′),
(5,1,9,’136′),
(6,3,23,’89’),
(7,3,25,’177′),
(8,1,11,’193′),
(9,1,8,’99’),
(10,1,10,’144′),
(11,2,16,’79’),
(12,2,17,’156′),
(13,2,18,’68’),
(14,3,24,’180′),
(15,3,22,’73’),
(16,3,21,’148′),
(17,6,1,’83’),
(18,6,5,’95’),
(19,6,2,’40’),
(20,6,3,’163′),
(21,6,4,’58’),
(23,5,38,’68’),
(24,5,37,’190′),
(25,5,43,’64’),
(26,5,41,’59’),
(27,5,40,’123′),
(28,4,31,’77’),
(29,4,32,’187′),
(30,4,35,’74’),
(31,4,34,’52’),
(32,4,33,’143′);

select a.name_prov as prov,city,a.cases
from
(select c.name_prov,b.city,a.cases,
row_number () over (partition by c.name_prov order by a.cases::int desc) as rn
from tb_cases a
join tb_city b on a.id_city = b.id_city
join tb_prov c on a.id_prov = c.id_prov
order by c.name_prov
) a
order by a.name_prov,rn

Output:
prov | city | cases
—————+—————–+——-
Aceh | Lhokseumawe | 163
Aceh | Subulussalam | 95
Aceh | Banda Aceh | 83
Aceh | Sabang | 58
Aceh | Langsa | 40
Central Java | Salatiga | 156
Central Java | Surakarta | 154
Central Java | Magelang | 93
Central Java | Pekalongan | 79
Central Java | Semarang | 68
East Java | Madiun | 180
East Java | Malang | 177
East Java | Batu | 148
East Java | Kediri | 89
East Java | Blitar | 73
North Sumatra | Medan | 187
North Sumatra | Padangsidimpuan | 143
North Sumatra | Gunungsitoli | 77
North Sumatra | Tanjung Balai | 74
North Sumatra | Pematangsiantar | 52
West Java | Depok | 193
West Java | Cirebon | 144
West Java | Cimahi | 136
West Java | Bogor | 99
West Java | Bekasi | 86
West Java | Bandung | 75
West Sumatra | Bukit Tinggi | 190
West Sumatra | Pariaman | 123
West Sumatra | Padang | 68
West Sumatra | Solok | 64
West Sumatra | Payakumbuh | 59

mysql_en-21284.jpg
I’ve tried running the code in sqlyog, but an error message appears like this. I have also removed :int but the result is not the same.

Swapnil 2022-06-03 15:43:55
mkval3 2022-06-03 15:40:48
I’ve tried running the code in sqlyog, but an error message appears like this. I have also removed :int but the result is not the same.

I have tried on PostgreSQL

Swapnil 2022-06-03 15:44:09
mkval3 2022-06-03 15:40:48
I’ve tried running the code in sqlyog, but an error message appears like this. I have also removed :int but the result is not the same.

What result are you getting ?

mkval3 2022-06-03 15:45:57
Swapnil 2022-06-03 11:52:16
CREATE TABLE tb_city (
id_city int ,
city varchar(30) DEFAULT NULL,
PRIMARY KEY (id_city)
);

insert into tb_city(id_city,city) values
(1,’Banda Aceh’),
(2,’Langsa’),
(3,’Lhokseumawe’),
(4,’Sabang’),
(5,’Subulussalam’),
(6,’Bandung’),
(7,’Bekasi’),
(8,’Bogor’),
(9,’Cimahi’),
(10,’Cirebon’),
(11,’Depok’),
(12,’Sukabumi’),
(13,’Tasikmalaya’),
(14,’Banjar’),
(15,’Magelang’),
(16,’Pekalongan’),
(17,’Salatiga’),
(18,’Semarang’),
(19,’Surakarta’),
(20,’Tegas’),
(21,’Batu’),
(22,’Blitar’),
(23,’Kediri’),
(24,’Madiun’),
(25,’Malang’),
(26,’Mojokerto’),
(27,’Pasuruan’),
(28,’Probolinggo’),
(29,’Surabaya’),
(30,’Binjai’),
(31,’Gunungsitoli’),
(32,’Medan’),
(33,’Padangsidimpuan’),
(34,’Pematangsiantar’),
(35,’Tanjung Balai’),
(36,’Tebing Tinggi’),
(37,’Bukit Tinggi’),
(38,’Padang’),
(39,’Padang Panjang’),
(40,’Pariaman’),
(41,’Payakumbuh’),
(42,’Sawahlunto’),
(43,’Solok’);

CREATE TABLE tb_prov (
id_prov int,
name_prov varchar(30) DEFAULT NULL,
PRIMARY KEY (id_prov)
);

insert into tb_prov(id_prov,name_prov) values
(1,’West Java’),
(2,’Central Java’),
(3,’East Java’),
(4,’North Sumatra’),
(5,’West Sumatra’),
(6,’Aceh’);

CREATE TABLE tb_cases (
id int,
id_prov int DEFAULT NULL,
id_city int DEFAULT NULL,
cases varchar(30) DEFAULT NULL,
PRIMARY KEY (id),
–KEY id_prov (id_prov),
–KEY id_kota (id_city),
CONSTRAINT tb_cases_ibfk_1 FOREIGN KEY (id_prov) REFERENCES tb_prov (id_prov) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT tb_cases_ibfk_2 FOREIGN KEY (id_city) REFERENCES tb_city (id_city) ON DELETE CASCADE ON UPDATE CASCADE
);

insert into tb_cases(id,id_prov,id_city,cases) values
(1,1,7,’86’),
(2,1,6,’75’),
(3,2,15,’93’),
(4,2,19,’154′),
(5,1,9,’136′),
(6,3,23,’89’),
(7,3,25,’177′),
(8,1,11,’193′),
(9,1,8,’99’),
(10,1,10,’144′),
(11,2,16,’79’),
(12,2,17,’156′),
(13,2,18,’68’),
(14,3,24,’180′),
(15,3,22,’73’),
(16,3,21,’148′),
(17,6,1,’83’),
(18,6,5,’95’),
(19,6,2,’40’),
(20,6,3,’163′),
(21,6,4,’58’),
(23,5,38,’68’),
(24,5,37,’190′),
(25,5,43,’64’),
(26,5,41,’59’),
(27,5,40,’123′),
(28,4,31,’77’),
(29,4,32,’187′),
(30,4,35,’74’),
(31,4,34,’52’),
(32,4,33,’143′);

select a.name_prov as prov,city,a.cases
from
(select c.name_prov,b.city,a.cases,
row_number () over (partition by c.name_prov order by a.cases::int desc) as rn
from tb_cases a
join tb_city b on a.id_city = b.id_city
join tb_prov c on a.id_prov = c.id_prov
order by c.name_prov
) a
order by a.name_prov,rn

Output:
prov | city | cases
—————+—————–+——-
Aceh | Lhokseumawe | 163
Aceh | Subulussalam | 95
Aceh | Banda Aceh | 83
Aceh | Sabang | 58
Aceh | Langsa | 40
Central Java | Salatiga | 156
Central Java | Surakarta | 154
Central Java | Magelang | 93
Central Java | Pekalongan | 79
Central Java | Semarang | 68
East Java | Madiun | 180
East Java | Malang | 177
East Java | Batu | 148
East Java | Kediri | 89
East Java | Blitar | 73
North Sumatra | Medan | 187
North Sumatra | Padangsidimpuan | 143
North Sumatra | Gunungsitoli | 77
North Sumatra | Tanjung Balai | 74
North Sumatra | Pematangsiantar | 52
West Java | Depok | 193
West Java | Cirebon | 144
West Java | Cimahi | 136
West Java | Bogor | 99
West Java | Bekasi | 86
West Java | Bandung | 75
West Sumatra | Bukit Tinggi | 190
West Sumatra | Pariaman | 123
West Sumatra | Padang | 68
West Sumatra | Solok | 64
West Sumatra | Payakumbuh | 59

results like this, but if possible display a city that can be set like a task.

mkval3 2022-06-03 15:46:15
mkval3 2022-06-03 06:57:14
Hi, I’m finding it difficult to create a sql query with this problem. Is it possible to make a query? I’ve tried with basic queries and also subqueries. But still not getting the expected result. Are there friends who can help?
Thank you

this

Swapnil 2022-06-03 15:46:52
mkval3 2022-06-03 15:45:57
results like this, but if possible display a city that can be set like a task.

Check this out

select a.name_prov as prov,city,a.cases
from
(select c.name_prov,b.city,a.cases,
row_number () over (partition by c.name_prov order by a.cases::int desc) as rn
from tb_cases a
join tb_city b on a.id_city = b.id_city
join tb_prov c on a.id_prov = c.id_prov
order by c.name_prov
) a
where rn in (1,2,3)
order by a.name_prov,rn

Swapnil 2022-06-03 15:47:29
mkval3 2022-06-03 15:45:57
results like this, but if possible display a city that can be set like a task.

What does a city that can be set like a task mean?

mkval3 2022-06-03 15:51:22
Swapnil 2022-06-03 15:46:52
Check this out

select a.name_prov as prov,city,a.cases
from
(select c.name_prov,b.city,a.cases,
row_number () over (partition by c.name_prov order by a.cases::int desc) as rn
from tb_cases a
join tb_city b on a.id_city = b.id_city
join tb_prov c on a.id_prov = c.id_prov
order by c.name_prov
) a
where rn in (1,2,3)
order by a.name_prov,rn

looks like ::INT can’t run on mysql

Swapnil 2022-06-03 15:51:52
mkval3 2022-06-03 15:51:22
looks like ::INT can’t run on mysql

Why is cases column varchar ?

Swapnil 2022-06-03 15:52:35
mkval3 2022-06-03 15:51:22
looks like ::INT can’t run on mysql

select a.name_prov as prov,city,a.cases
from
(select c.name_prov,b.city,a.cases,
row_number () over (partition by c.name_prov order by cast(a.cases as int) desc) as rn
from tb_cases a
join tb_city b on a.id_city = b.id_city
join tb_prov c on a.id_prov = c.id_prov
order by c.name_prov
) a
where rn in (1,2,3)
order by a.name_prov,rn

|