SQL ZOO 练习 —— SELECT names

2023-11-21 03:10

本文主要是介绍SQL ZOO 练习 —— SELECT names,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

在这里插入图片描述

1.You can use WHERE name LIKE ‘B%’ to find the countries that start with “B”.

The % is a wild-card it can match any characters
Find the country that start with Y

SELECT name FROM worldWHERE name LIKE 'Y%'

2.Find the countries that end with y

SELECT name FROM worldWHERE name LIKE '%Y'

3.Luxembourg has an x - so does one other country. List them both.

Find the countries that contain the letter x

SELECT name FROM worldWHERE name LIKE '%x%'

4.Iceland, Switzerland end with land - but are there others?

Find the countries that end with land

SELECT name FROM worldWHERE name LIKE '%land'

5.Columbia starts with a C and ends with ia - there are two more like this.

Find the countries that start with C and end with ia

SELECT name FROM worldWHERE name LIKE 'C%'AND name LIKE '%ia'

6.Greece has a double e - who has a double o?

Find the country that has oo in the name

SELECT name FROM worldWHERE name LIKE '%oo%'

7.Bahamas has three a - who else?

Find the countries that have three or more a in the name

SELECT name FROM worldWHERE name LIKE '%a%a%a%'

8.India and Angola have an n as the second character. You can use the underscore as a single character wildcard.

SELECT name FROM worldWHERE name LIKE '_n%'
ORDER BY name

Find the countries that have “t” as the second character.

SELECT name FROM worldWHERE name LIKE '_t%'
ORDER BY name

9.Lesotho and Moldova both have two o characters separated by two other characters.

Find the countries that have two “o” characters separated by two others.

SELECT name FROM worldWHERE name LIKE '%o__o%'

10.Cuba and Togo have four characters names.

Find the countries that have exactly four characters.

SELECT name FROM worldWHERE name LIKE '____'

11.The capital of Luxembourg is Luxembourg. Show all the countries where the capital is the same as the name of the country

Find the country where the name is the capital city.

SELECT nameFROM worldWHERE name = capital

12.The capital of Mexico is Mexico City. Show all the countries where the capital has the country together with the word “City”.

Find the country where the capital is the country plus “City”.

SELECT nameFROM worldWHERE capital = concat(name, ' City')

13.Find the capital and the name where the capital includes the name of the country.

SELECT capital, name 
FROM world 
WHERE capital  LIKE concat('%',name,'%')

14.Find the capital and the name where the capital is an extension of name of the country.

You should include Mexico City as it is longer than Mexico. You should not include Luxembourg as the capital is the same as the country.

SELECT capital,name
FROM world 
WHERE capital LIKE concat(name,'_%')

15

For Monaco-Ville the name is Monaco and the extension is -Ville.
Show the name and the extension where the capital is an extension of name of the country.
You can use the SQL function REPLACE.

select name,replace(capital, name, '') from  world 
where capital like concat(name,'%_')

这篇关于SQL ZOO 练习 —— SELECT names的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/399396

相关文章

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述:数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景:表空却 “吃满” 磁盘的怪事二、问题复现:一步步编程还原异常场景1. 准备测试源表与数据

Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

《Mac电脑如何通过IntelliJIDEA远程连接MySQL》本文详解Mac通过IntelliJIDEA远程连接MySQL的步骤,本文通过图文并茂的形式给大家介绍的非常详细,感兴趣的朋友跟... 目录MAC电脑通过 IntelliJ IDEA 远程连接 mysql 的详细教程一、前缀条件确认二、打开 ID

MySQL的配置文件详解及实例代码

《MySQL的配置文件详解及实例代码》MySQL的配置文件是服务器运行的重要组成部分,用于设置服务器操作的各种参数,下面:本文主要介绍MySQL配置文件的相关资料,文中通过代码介绍的非常详细,需要... 目录前言一、配置文件结构1.[mysqld]2.[client]3.[mysql]4.[mysqldum

MySQL中查询和展示LONGBLOB类型数据的技巧总结

《MySQL中查询和展示LONGBLOB类型数据的技巧总结》在MySQL中LONGBLOB是一种二进制大对象(BLOB)数据类型,用于存储大量的二进制数据,:本文主要介绍MySQL中查询和展示LO... 目录前言1. 查询 LONGBLOB 数据的大小2. 查询并展示 LONGBLOB 数据2.1 转换为十

Go语言连接MySQL数据库执行基本的增删改查

《Go语言连接MySQL数据库执行基本的增删改查》在后端开发中,MySQL是最常用的关系型数据库之一,本文主要为大家详细介绍了如何使用Go连接MySQL数据库并执行基本的增删改查吧... 目录Go语言连接mysql数据库准备工作安装 MySQL 驱动代码实现运行结果注意事项Go语言执行基本的增删改查准备工作

MySQL按时间维度对亿级数据表进行平滑分表

《MySQL按时间维度对亿级数据表进行平滑分表》本文将以一个真实的4亿数据表分表案例为基础,详细介绍如何在不影响线上业务的情况下,完成按时间维度分表的完整过程,感兴趣的小伙伴可以了解一下... 目录引言一、为什么我们需要分表1.1 单表数据量过大的问题1.2 分表方案选型二、分表前的准备工作2.1 数据评估