井上  康弘

井上 康弘

1659328873

如何使用 LIKE 過濾特定字符串匹配的 SQL 記錄

使用 LIKE 過濾特定字符串匹配的 SQL 記錄。本教程教您在 LIKE 中使用通配符、NOT、LOWER、UPPER 和 CASE WHEN。

用於模式匹配的 SQL LIKE 運算符

不管你喜不喜歡,LIKE運算符在 SQL 中是必不可少的。它使數據從業者能夠過濾特定字符串匹配的數據。本文LIKE為初學者和中級者提供了一個快速教程。如果您通過動手實踐學得更好,您還可以在此DataCamp Workspace上跟隨(並運行代碼) 。

僱員
emp_no出生日期性別聘用日期
100011953-09-02T00:00:00.000Z喬治法塞洛1986-06-26T00:00:00.000Z
100021964-06-02T00:00:00.000Z比撒列簡單的F1985-11-21T00:00:00.000Z
100031959-12-03T00:00:00.000Z部分班福德1986-08-28T00:00:00.000Z

假設您有一個employees表,並且想要查找所有以 . 開頭的名稱‘A’。您可以花時間手動查看表格。但是,當你有 LIKE 運算符時,你為什麼要這樣做呢?

SELECT DISTINCT
	first_name
FROM employees
WHERE first_name LIKE 'A%'

這裡的魔力在從句`WHERE first_name LIKE ‘A%’`中,意思是“查找所有first_name以 A 開頭並以任意數量的字符結尾的內容”。這裡`A%`被稱為匹配模式。 

`%`不是唯一可以與運算符一起使用的通配符LIKE。您也可以使用下劃線符號`_`。 

  • `%`匹配任意數量的字符。
  • `_`匹配任何單個字符。

語法很容易記住。簡直是

column_name LIKE pattern

你可以用它LIKE來實現多種模式匹配。就是這樣。

初級 SQL LIKE 示例

下面,我們概述了一些實際示例,說明如何使用該LIKE語句以及來自我們的示例數據集的結果。 

1.LIKE用於精確字符串匹配

如果您想執行精確的字符串匹配,請使用不帶‘%’或的 LIKE‘_’

SELECT
    first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’

2.‘%’用於匹配任意數量的字符 

‘%’可用於匹配任意(甚至零)個字符——數字、字母或符號。

假設您要查找姓名以‘Adam’;開頭的所有員工。你可以使用模式‘Adam%’

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Adam%'

要查找以 結尾的名稱’Z’,請嘗試該模式‘%z’。您也可以‘%’在一個模式中使用多個。例如,如果要查找包含 z 的名稱,請使用‘%z%’.

3.‘_’用於匹配一個(也只有一個)字符

和遊戲劊子手一樣,下劃線符號_只能容納一個字符。

拼音有幾種方式Le_n?該模式將匹配來自‘Lexn’‘LeAn’Le3n’或的任何內容‘Le-n’

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Le_n'

只有三個字符的不同名稱有哪些?我們可以用三個連續的下劃線 ___ 作為模式找出來。

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '___'

4.同時使用‘%’‘_’匹配任何模式

當然,您可以同時使用‘%’‘_’來創建有趣的模式。 

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '%ann_'

該模式‘%ann_’匹配以任意數量的字符開頭並以‘ann’另一個字符結尾的字符串。

5.NOT用於查找與模式不匹配的字符串

如果您想查找所有與特定條件不匹配的行怎麼辦?您可以使用NOT LIKE運算符。例如,要查找除 Staff 之外的所有標題,我們可以使用語法

`WHERE title NOT LIKE ‘Staff’`

這完全等同於語法

`WHERE title != ‘Staff’`
SELECT DISTINCT
    title
FROM titles
WHERE title NOT LIKE 'Staff'

當然,您可以使用`NOT LIKE`我們描述的任何模式。

SELECT DISTINCT
    title
FROM titles 
WHERE title NOT LIKE '%engineer'

6. 使用LOWER(or UPPER) withLIKE進行不區分大小寫的模式匹配

如果您需要執行模式匹配但不確定字符串是以小寫、大寫還是混合大小寫存儲的,您可以使用以下語法。

`LOWER(column_name) LIKE pattern`

該函數LOWER()以小寫形式返回所有字符串,無論它們是以大寫、小寫還是混合大小寫存儲。 

使用語法時,請確保將模式拼寫為全部小寫!否則,您可能不會得到任何匹配項。

您也可以在上面的語法中替換LOWER為。UPPER請務必以大寫字母拼出模式。

`UPPER(column_name) LIKE PATTERN`

例如,要確定員工的姓名是 Joanne、JoAnne、Joanna 還是 JoAnna,請嘗試以下任一方法。

SELECT DISTINCT
    first_name
FROM employees
WHERE lower(first_name) LIKE 'joann_'
SELECT DISTINCT
    first_name
FROM employees
WHERE UPPER(first_name) LIKE 'JOANN_'

7.LIKE多值 SQL 使用OR/AND

LIKE您也可以使用語法 組合多個條件。

例如,使用條件來查找滿足多個模式OR中的至少一個的結果。LIKE

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE 'Ad_l' OR
first_name LIKE 'Ad_m'

另一方面,要查找匹配多個LIKE條件的字符串,請使用AND關鍵字。

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE '%am%' AND
first_name LIKE '%me%'

LIKE語法可以應用於多個列,只要它們的變量類型是可變長度字符 ( varchar)。知道我們可以找出姓名縮寫為的員工的姓名‘Z. Z.’

SELECT DISTINCT
    first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'

8.LIKESELECT CASE WHEN從句 中使用

到目前為止,我們一直專注於使用LIKE作為選擇WHERE子句中記錄的條件。我們也在LIKE從句中SELECT使用。例如,我們能否找出‘Adam’我們的數據庫中有多少具有該名稱的員工?

SELECT
    COUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam
FROM employees

另一方面,有多少員工有姓名縮寫‘A.Z.’

SELECT
    COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employees

SQL LIKE 的中間示例

LIKE函數在不同風格的 SQL(例如 PostgreSQL、MySQL、Redshift 等)中非常相似。有些具有LIKE值得一提的功能的其他變體。 

1.ILIKE操作員

在 Redshift 和 PostgreSQL 中可用的ILIKE是不區分大小寫的LIKE. 因此,以下所有內容都是等效的。

SELECT
  datacamp ILIKE ‘datacamp’, -- returns TRUE
  DATACAMP ILIKE ‘datacamp’, -- returns TRUE
  Datacamp ILIKE ‘datacamp’, -- returns TRUE
  datacamp ILIKE ‘DataCamp’, -- returns TRUE

2. 使用方括號[][^]作為通配符

T-SQL 或 SQL Server 的用戶有額外的通配符來進行更複雜的模式匹配。

方括號語法[]匹配範圍或集合內特定的任何單個字符。例如,以下將全部返回 TRUE。

SELECT
  ‘Carson’ LIKE ‘[C-K]arson’, -- returns TRUE because C is in the range C-K
  ‘Karson’ LIKE ‘[C-K]arson’, -- returns TRUE because K is in range
  ‘Larson’ LIKE ‘[CKL]arson’, -- returns TRUE because L is in the set [CKL]
  ‘Parson’ LIKE ‘[C-K]arson’ -- returns FALSE because P is out of range

方括號中的插入符號[^]通配符匹配不在指定範圍或集合內的任何單個字符。你能明白為什麼下面的結果是這樣的嗎? 

SELECT
  ‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE 
  ‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUE

在這裡,由於C在 的範圍內[C-K],模式‘C’將不匹配[^C-K]。因此,‘Carson’將不匹配‘[^C-K]arson

3.RLIKE操作員

MySQL 中可用的RLIKE運算符可識別模式中的正則表達式 (RegEx)。RegEx 是一種功能強大且用途廣泛的高級模式匹配工具。 

對 RegEx 有一個基本的了解並沒有什麼壞處,特別是如果您的 SQL 風格支持 RLIKE。您可以通過我們的 Python 正則表達式課程了解更多關於 RegEx 的信息。

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name RLIKE 'Susann[a-e]'

4.‘~~’操作員

在 PostgreSQL 中,‘~~’完全是LIKE. 還有ILIKE,NOT LIKE和的等價物NOT ILIKE

操作員相等的
~~LIKE
~~*ILIKE
!~~NOT LIKE
!~~*NOT ILIKE

自信地使用 SQL LIKE

掌握 SQL 函數是數據科學成功的關鍵,SQL 的 LIKE 命令也不例外。對 SQL 的良好掌握將加速您的分析進度,所以一定要好好學習!

鏈接:https ://www.datacamp.com/tutorial/sql-like-pattern-matching-tutorial

#sql

What is GEEK

Buddha Community

如何使用 LIKE 過濾特定字符串匹配的 SQL 記錄
Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Cayla  Erdman

Cayla Erdman

1596441660

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.

The easiest way to perform that would be to use the T-SQL debugger feature, which used to be built-in over the SQL Server Management Studio tool. But since the T-SQL debugger feature was removed completely from SQL Server Management Studio 18 and later editions, we need a replacement for that feature. This is because we cannot keep using the old versions of SSMS just to support the T-SQL Debugger feature without “enjoying” the new features and bug fixes that are released in the new SSMS versions.

If you plan to wait for SSMS to bring back the T-SQL Debugger feature, vote in the Put Debugger back into SSMS 18 to ask Microsoft to reintroduce it.

As for me, I searched for an alternative tool for a T-SQL Debugger SSMS built-in feature and found that Devart company rolled out a new T-SQL Debugger feature to version 6.4 of SQL – Complete tool. SQL Complete is an add-in for Visual Studio and SSMS that offers scripts autocompletion capabilities, which help develop and debug your SQL database project.

The SQL Debugger feature of SQL Complete allows you to check the execution of your scripts, procedures, functions, and triggers step by step by adding breakpoints to the lines where you plan to start, suspend, evaluate, step through, and then to continue the execution of your script.

You can download SQL Complete from the dbForge Download page and install it on your machine using a straight-forward installation wizard. The wizard will ask you to specify the installation path for the SQL Complete tool and the versions of SSMS and Visual Studio that you plan to install the SQL Complete on, as an add-in, from the versions that are installed on your machine, as shown below:

Once SQL Complete is fully installed on your machine, the dbForge SQL Complete installation wizard will notify you of whether the installation was completed successfully or the wizard faced any specific issue that you can troubleshoot and fix easily. If there are no issues, the wizard will provide you with an option to open the SSMS tool and start using the SQL Complete tool, as displayed below:

When you open SSMS, you will see a new “Debug” tools menu, under which you can navigate the SQL Debugger feature options. Besides, you will see a list of icons that will be used to control the debug mode of the T-SQL query at the leftmost side of the SSMS tool. If you cannot see the list, you can go to View -> Toolbars -> Debugger to make these icons visible.

During the debugging session, the SQL Debugger icons will be as follows:

The functionality of these icons within the SQL Debugger can be summarized as:

  • Adding Breakpoints to control the execution pause of the T-SQL script at a specific statement allows you to check the debugging information of the T-SQL statements such as the values for the parameters and the variables.
  • Step Into is “navigate” through the script statements one by one, allowing you to check how each statement behaves.
  • Step Over is “execute” a specific stored procedure if you are sure that it contains no error.
  • Step Out is “return” from the stored procedure, function, or trigger to the main debugging window.
  • Continue executing the script until reaching the next breakpoint.
  • Stop Debugging is “terminate” the debugging session.
  • Restart “stop and start” the current debugging session.

#sql server #sql #sql debugger #sql server #sql server stored procedure #ssms #t-sql queries

Carmen  Grimes

Carmen Grimes

1595494844

How to start an electric scooter facility/fleet in a university campus/IT park

Are you leading an organization that has a large campus, e.g., a large university? You are probably thinking of introducing an electric scooter/bicycle fleet on the campus, and why wouldn’t you?

Introducing micro-mobility in your campus with the help of such a fleet would help the people on the campus significantly. People would save money since they don’t need to use a car for a short distance. Your campus will see a drastic reduction in congestion, moreover, its carbon footprint will reduce.

Micro-mobility is relatively new though and you would need help. You would need to select an appropriate fleet of vehicles. The people on your campus would need to find electric scooters or electric bikes for commuting, and you need to provide a solution for this.

To be more specific, you need a short-term electric bike rental app. With such an app, you will be able to easily offer micro-mobility to the people on the campus. We at Devathon have built Autorent exactly for this.

What does Autorent do and how can it help you? How does it enable you to introduce micro-mobility on your campus? We explain these in this article, however, we will touch upon a few basics first.

Micro-mobility: What it is

micro-mobility

You are probably thinking about micro-mobility relatively recently, aren’t you? A few relevant insights about it could help you to better appreciate its importance.

Micro-mobility is a new trend in transportation, and it uses vehicles that are considerably smaller than cars. Electric scooters (e-scooters) and electric bikes (e-bikes) are the most popular forms of micro-mobility, however, there are also e-unicycles and e-skateboards.

You might have already seen e-scooters, which are kick scooters that come with a motor. Thanks to its motor, an e-scooter can achieve a speed of up to 20 km/h. On the other hand, e-bikes are popular in China and Japan, and they come with a motor, and you can reach a speed of 40 km/h.

You obviously can’t use these vehicles for very long commutes, however, what if you need to travel a short distance? Even if you have a reasonable public transport facility in the city, it might not cover the route you need to take. Take the example of a large university campus. Such a campus is often at a considerable distance from the central business district of the city where it’s located. While public transport facilities may serve the central business district, they wouldn’t serve this large campus. Currently, many people drive their cars even for short distances.

As you know, that brings its own set of challenges. Vehicular traffic adds significantly to pollution, moreover, finding a parking spot can be hard in crowded urban districts.

Well, you can reduce your carbon footprint if you use an electric car. However, electric cars are still new, and many countries are still building the necessary infrastructure for them. Your large campus might not have the necessary infrastructure for them either. Presently, electric cars don’t represent a viable option in most geographies.

As a result, you need to buy and maintain a car even if your commute is short. In addition to dealing with parking problems, you need to spend significantly on your car.

All of these factors have combined to make people sit up and think seriously about cars. Many people are now seriously considering whether a car is really the best option even if they have to commute only a short distance.

This is where micro-mobility enters the picture. When you commute a short distance regularly, e-scooters or e-bikes are viable options. You limit your carbon footprints and you cut costs!

Businesses have seen this shift in thinking, and e-scooter companies like Lime and Bird have entered this field in a big way. They let you rent e-scooters by the minute. On the other hand, start-ups like Jump and Lyft have entered the e-bike market.

Think of your campus now! The people there might need to travel short distances within the campus, and e-scooters can really help them.

How micro-mobility can benefit you

benefits-micromobility

What advantages can you get from micro-mobility? Let’s take a deeper look into this question.

Micro-mobility can offer several advantages to the people on your campus, e.g.:

  • Affordability: Shared e-scooters are cheaper than other mass transportation options. Remember that the people on your campus will use them on a shared basis, and they will pay for their short commutes only. Well, depending on your operating model, you might even let them use shared e-scooters or e-bikes for free!
  • Convenience: Users don’t need to worry about finding parking spots for shared e-scooters since these are small. They can easily travel from point A to point B on your campus with the help of these e-scooters.
  • Environmentally sustainable: Shared e-scooters reduce the carbon footprint, moreover, they decongest the roads. Statistics from the pilot programs in cities like Portland and Denver showimpressive gains around this key aspect.
  • Safety: This one’s obvious, isn’t it? When people on your campus use small e-scooters or e-bikes instead of cars, the problem of overspeeding will disappear. you will see fewer accidents.

#android app #autorent #ios app #mobile app development #app like bird #app like bounce #app like lime #autorent #bird scooter business model #bird scooter rental #bird scooter rental cost #bird scooter rental price #clone app like bird #clone app like bounce #clone app like lime #electric rental scooters #electric scooter company #electric scooter rental business #how do you start a moped #how to start a moped #how to start a scooter rental business #how to start an electric company #how to start electric scooterrental business #lime scooter business model #scooter franchise #scooter rental business #scooter rental business for sale #scooter rental business insurance #scooters franchise cost #white label app like bird #white label app like bounce #white label app like lime

Carmen  Grimes

Carmen Grimes

1595491178

Best Electric Bikes and Scooters for Rental Business or Campus Facility

The electric scooter revolution has caught on super-fast taking many cities across the globe by storm. eScooters, a renovated version of old-school scooters now turned into electric vehicles are an environmentally friendly solution to current on-demand commute problems. They work on engines, like cars, enabling short traveling distances without hassle. The result is that these groundbreaking electric machines can now provide faster transport for less — cheaper than Uber and faster than Metro.

Since they are durable, fast, easy to operate and maintain, and are more convenient to park compared to four-wheelers, the eScooters trend has and continues to spike interest as a promising growth area. Several companies and universities are increasingly setting up shop to provide eScooter services realizing a would-be profitable business model and a ready customer base that is university students or residents in need of faster and cheap travel going about their business in school, town, and other surrounding areas.

Electric Scooters Trends and Statistics

In many countries including the U.S., Canada, Mexico, U.K., Germany, France, China, Japan, India, Brazil and Mexico and more, a growing number of eScooter users both locals and tourists can now be seen effortlessly passing lines of drivers stuck in the endless and unmoving traffic.

A recent report by McKinsey revealed that the E-Scooter industry will be worth― $200 billion to $300 billion in the United States, $100 billion to $150 billion in Europe, and $30 billion to $50 billion in China in 2030. The e-Scooter revenue model will also spike and is projected to rise by more than 20% amounting to approximately $5 billion.

And, with a necessity to move people away from high carbon prints, traffic and congestion issues brought about by car-centric transport systems in cities, more and more city planners are developing more bike/scooter lanes and adopting zero-emission plans. This is the force behind the booming electric scooter market and the numbers will only go higher and higher.

Companies that have taken advantage of the growing eScooter trend develop an appthat allows them to provide efficient eScooter services. Such an app enables them to be able to locate bike pick-up and drop points through fully integrated google maps.

List of Best Electric Bikes for Rental Business or Campus Facility 2020:

It’s clear that e scooters will increasingly become more common and the e-scooter business model will continue to grab the attention of manufacturers, investors, entrepreneurs. All this should go ahead with a quest to know what are some of the best electric bikes in the market especially for anyone who would want to get started in the electric bikes/scooters rental business.

We have done a comprehensive list of the best electric bikes! Each bike has been reviewed in depth and includes a full list of specs and a photo.

Billy eBike

mobile-best-electric-bikes-scooters https://www.kickstarter.com/projects/enkicycles/billy-were-redefining-joyrides

To start us off is the Billy eBike, a powerful go-anywhere urban electric bike that’s specially designed to offer an exciting ride like no other whether you want to ride to the grocery store, cafe, work or school. The Billy eBike comes in 4 color options – Billy Blue, Polished aluminium, Artic white, and Stealth black.

Price: $2490

Available countries

Available in the USA, Europe, Asia, South Africa and Australia.This item ships from the USA. Buyers are therefore responsible for any taxes and/or customs duties incurred once it arrives in your country.

Features

  • Control – Ride with confidence with our ultra-wide BMX bars and a hyper-responsive twist throttle.
  • Stealth- Ride like a ninja with our Gates carbon drive that’s as smooth as butter and maintenance-free.
  • Drive – Ride further with our high torque fat bike motor, giving a better climbing performance.
  • Accelerate – Ride quicker with our 20-inch lightweight cutout rims for improved acceleration.
  • Customize – Ride your own way with 5 levels of power control. Each level determines power and speed.
  • Flickable – Ride harder with our BMX /MotoX inspired geometry and lightweight aluminum package

Specifications

  • Maximum speed: 20 mph (32 km/h)
  • Range per charge: 41 miles (66 km)
  • Maximum Power: 500W
  • Motor type: Fat Bike Motor: Bafang RM G060.500.DC
  • Load capacity: 300lbs (136kg)
  • Battery type: 13.6Ah Samsung lithium-ion,
  • Battery capacity: On/off-bike charging available
  • Weight: w/o batt. 48.5lbs (22kg), w/ batt. 54lbs (24.5kg)
  • Front Suspension: Fully adjustable air shock, preload/compression damping /lockout
  • Rear Suspension: spring, preload adjustment
  • Built-in GPS

Why Should You Buy This?

  • Riding fun and excitement
  • Better climbing ability and faster acceleration.
  • Ride with confidence
  • Billy folds for convenient storage and transportation.
  • Shorty levers connect to disc brakes ensuring you stop on a dime
  • belt drives are maintenance-free and clean (no oil or lubrication needed)

**Who Should Ride Billy? **

Both new and experienced riders

**Where to Buy? **Local distributors or ships from the USA.

Genze 200 series e-Bike

genze-best-electric-bikes-scooters https://www.genze.com/fleet/

Featuring a sleek and lightweight aluminum frame design, the 200-Series ebike takes your riding experience to greater heights. Available in both black and white this ebike comes with a connected app, which allows you to plan activities, map distances and routes while also allowing connections with fellow riders.

Price: $2099.00

Available countries

The Genze 200 series e-Bike is available at GenZe retail locations across the U.S or online via GenZe.com website. Customers from outside the US can ship the product while incurring the relevant charges.

Features

  • 2 Frame Options
  • 2 Sizes
  • Integrated/Removable Battery
  • Throttle and Pedal Assist Ride Modes
  • Integrated LCD Display
  • Connected App
  • 24 month warranty
  • GPS navigation
  • Bluetooth connectivity

Specifications

  • Maximum speed: 20 mph with throttle
  • Range per charge: 15-18 miles w/ throttle and 30-50 miles w/ pedal assist
  • Charging time: 3.5 hours
  • Motor type: Brushless Rear Hub Motor
  • Gears: Microshift Thumb Shifter
  • Battery type: Removable Samsung 36V, 9.6AH Li-Ion battery pack
  • Battery capacity: 36V and 350 Wh
  • Weight: 46 pounds
  • Derailleur: 8-speed Shimano
  • Brakes: Dual classic
  • Wheels: 26 x 20 inches
  • Frame: 16, and 18 inches
  • Operating Mode: Analog mode 5 levels of Pedal Assist Thrott­le Mode

Norco from eBikestore

norco-best-electric-bikes-scooters https://ebikestore.com/shop/norco-vlt-s2/

The Norco VLT S2 is a front suspension e-Bike with solid components alongside the reliable Bosch Performance Line Power systems that offer precise pedal assistance during any riding situation.

Price: $2,699.00

Available countries

This item is available via the various Norco bikes international distributors.

Features

  • VLT aluminum frame- for stiffness and wheel security.
  • Bosch e-bike system – for their reliability and performance.
  • E-bike components – for added durability.
  • Hydraulic disc brakes – offer riders more stopping power for safety and control at higher speeds.
  • Practical design features – to add convenience and versatility.

Specifications

  • Maximum speed: KMC X9 9spd
  • Motor type: Bosch Active Line
  • Gears: Shimano Altus RD-M2000, SGS, 9 Speed
  • Battery type: Power Pack 400
  • Battery capacity: 396Wh
  • Suspension: SR Suntour suspension fork
  • Frame: Norco VLT, Aluminum, 12x142mm TA Dropouts

Bodo EV

bodo-best-electric-bikes-scootershttp://www.bodoevs.com/bodoev/products_show.asp?product_id=13

Manufactured by Bodo Vehicle Group Limited, the Bodo EV is specially designed for strong power and extraordinary long service to facilitate super amazing rides. The Bodo Vehicle Company is a striking top in electric vehicles brand field in China and across the globe. Their Bodo EV will no doubt provide your riders with high-level riding satisfaction owing to its high-quality design, strength, breaking stability and speed.

Price: $799

Available countries

This item ships from China with buyers bearing the shipping costs and other variables prior to delivery.

Features

  • Reliable
  • Environment friendly
  • Comfortable riding
  • Fashionable
  • Economical
  • Durable – long service life
  • Braking stability
  • LED lighting technology

Specifications

  • Maximum speed: 45km/h
  • Range per charge: 50km per person
  • Charging time: 8 hours
  • Maximum Power: 3000W
  • Motor type: Brushless DC Motor
  • Load capacity: 100kg
  • Battery type: Lead-acid battery
  • Battery capacity: 60V 20AH
  • Weight: w/o battery 47kg

#android app #autorent #entrepreneurship #ios app #minimum viable product (mvp) #mobile app development #news #app like bird #app like bounce #app like lime #autorent #best electric bikes 2020 #best electric bikes for rental business #best electric kick scooters 2020 #best electric kickscooters for rental business #best electric scooters 2020 #best electric scooters for rental business #bird scooter business model #bird scooter rental #bird scooter rental cost #bird scooter rental price #clone app like bird #clone app like bounce #clone app like lime #electric rental scooters #electric scooter company #electric scooter rental business #how do you start a moped #how to start a moped #how to start a scooter rental business #how to start an electric company #how to start electric scooterrental business #lime scooter business model #scooter franchise #scooter rental business #scooter rental business for sale #scooter rental business insurance #scooters franchise cost #white label app like bird #white label app like bounce #white label app like lime

Cayla  Erdman

Cayla Erdman

1596448980

The Easy Guide on How to Use Subqueries in SQL Server

Let’s say the chief credit and collections officer asks you to list down the names of people, their unpaid balances per month, and the current running balance and wants you to import this data array into Excel. The purpose is to analyze the data and come up with an offer making payments lighter to mitigate the effects of the COVID19 pandemic.

Do you opt to use a query and a nested subquery or a join? What decision will you make?

SQL Subqueries – What Are They?

Before we do a deep dive into syntax, performance impact, and caveats, why not define a subquery first?

In the simplest terms, a subquery is a query within a query. While a query that embodies a subquery is the outer query, we refer to a subquery as the inner query or inner select. And parentheses enclose a subquery similar to the structure below:

SELECT 
 col1
,col2
,(subquery) as col3
FROM table1
[JOIN table2 ON table1.col1 = table2.col2]
WHERE col1 <operator> (subquery)

We are going to look upon the following points in this post:

  • SQL subquery syntax depending on different subquery types and operators.
  • When and in what sort of statements one can use a subquery.
  • Performance implications vs. JOINs.
  • Common caveats when using SQL subqueries.

As is customary, we provide examples and illustrations to enhance understanding. But bear in mind that the main focus of this post is on subqueries in SQL Server.

Now, let’s get started.

Make SQL Subqueries That Are Self-Contained or Correlated

For one thing, subqueries are categorized based on their dependency on the outer query.

Let me describe what a self-contained subquery is.

Self-contained subqueries (or sometimes referred to as non-correlated or simple subqueries) are independent of the tables in the outer query. Let me illustrate this:

-- Get sales orders of customers from Southwest United States 
-- (TerritoryID = 4)

USE [AdventureWorks]
GO
SELECT CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (SELECT [CustomerID]
                     FROM [AdventureWorks].[Sales].[Customer]
                     WHERE TerritoryID = 4)

As demonstrated in the above code, the subquery (enclosed in parentheses below) has no references to any column in the outer query. Additionally, you can highlight the subquery in SQL Server Management Studio and execute it without getting any runtime errors.

Which, in turn, leads to easier debugging of self-contained subqueries.

The next thing to consider is correlated subqueries. Compared to its self-contained counterpart, this one has at least one column being referenced from the outer query. To clarify, I will provide an example:

USE [AdventureWorks]
GO
SELECT DISTINCT a.LastName, a.FirstName, b.BusinessEntityID
FROM Person.Person AS p
JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID
WHERE 1262000.00 IN
    (SELECT [SalesQuota]
    FROM Sales.SalesPersonQuotaHistory spq
    WHERE p.BusinessEntityID = spq.BusinessEntityID)

Were you attentive enough to notice the reference to BusinessEntityID from the Person table? Well done!

Once a column from the outer query is referenced in the subquery, it becomes a correlated subquery. One more point to consider: if you highlight a subquery and execute it, an error will occur.

And yes, you are absolutely right: this makes correlated subqueries pretty harder to debug.

To make debugging possible, follow these steps:

  • isolate the subquery.
  • replace the reference to the outer query with a constant value.

Isolating the subquery for debugging will make it look like this:

SELECT [SalesQuota]
    FROM Sales.SalesPersonQuotaHistory spq
    WHERE spq.BusinessEntityID = <constant value>

Now, let’s dig a little deeper into the output of subqueries.

Make SQL Subqueries With 3 Possible Returned Values

Well, first, let’s think of what returned values can we expect from SQL subqueries.

In fact, there are 3 possible outcomes:

  • A single value
  • Multiple values
  • Whole tables

Single Value

Let’s start with single-valued output. This type of subquery can appear anywhere in the outer query where an expression is expected, like the WHERE clause.

-- Output a single value which is the maximum or last TransactionID
USE [AdventureWorks]
GO
SELECT TransactionID, ProductID, TransactionDate, Quantity
FROM Production.TransactionHistory
WHERE TransactionID = (SELECT MAX(t.TransactionID) 
                       FROM Production.TransactionHistory t)

When you use a MAX() function, you retrieve a single value. That’s exactly what happened to our subquery above. Using the equal (=) operator tells SQL Server that you expect a single value. Another thing: if the subquery returns multiple values using the equals (=) operator, you get an error, similar to the one below:

Msg 512, Level 16, State 1, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Multiple Values

Next, we examine the multi-valued output. This kind of subquery returns a list of values with a single column. Additionally, operators like IN and NOT IN will expect one or more values.

-- Output multiple values which is a list of customers with lastnames that --- start with 'I'

USE [AdventureWorks]
GO
SELECT [SalesOrderID], [OrderDate], [ShipDate], [CustomerID]
FROM Sales.SalesOrderHeader
WHERE [CustomerID] IN (SELECT c.[CustomerID] FROM Sales.Customer c
INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
WHERE p.lastname LIKE N'I%' AND p.PersonType='SC')

Whole Table Values

And last but not least, why not delve into whole table outputs.

-- Output a table of values based on sales orders
USE [AdventureWorks]
GO
SELECT [ShipYear],
COUNT(DISTINCT [CustomerID]) AS CustomerCount
FROM (SELECT YEAR([ShipDate]) AS [ShipYear], [CustomerID] 
      FROM Sales.SalesOrderHeader) AS Shipments
GROUP BY [ShipYear]
ORDER BY [ShipYear]

Have you noticed the FROM clause?

Instead of using a table, it used a subquery. This is called a derived table or a table subquery.

And now, let me present you some ground rules when using this sort of query:

  • All columns in the subquery should have unique names. Much like a physical table, a derived table should have unique column names.
  • ORDER BY is not allowed unless TOP is also specified. That’s because the derived table represents a relational table where rows have no defined order.

In this case, a derived table has the benefits of a physical table. That’s why in our example, we can use COUNT() in one of the columns of the derived table.

That’s about all regarding subquery outputs. But before we get any further, you may have noticed that the logic behind the example for multiple values and others as well can also be done using a JOIN.

-- Output multiple values which is a list of customers with lastnames that start with 'I'
USE [AdventureWorks]
GO
SELECT o.[SalesOrderID], o.[OrderDate], o.[ShipDate], o.[CustomerID]
FROM Sales.SalesOrderHeader o
INNER JOIN Sales.Customer c on o.CustomerID = c.CustomerID
INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
WHERE p.LastName LIKE N'I%' AND p.PersonType = 'SC'

In fact, the output will be the same. But which one performs better?

Before we get into that, let me tell you that I have dedicated a section to this hot topic. We’ll examine it with complete execution plans and have a look at illustrations.

So, bear with me for a moment. Let’s discuss another way to place your subqueries.

#sql server #sql query #sql server #sql subqueries #t-sql statements #sql