Learning SQL by Alan Beaulieu
Author:Alan Beaulieu [Alan Beaulieu]
Language: eng
Format: epub, pdf
Tags: COMPUTERS / Database Management / Data Mining
ISBN: 9780596555580
Publisher: O'Reilly Media
Published: 2009-04-10T16:00:00+00:00
In some cases, the all operator is a bit more natural. The next example uses all to find accounts having an available balance smaller than all of Frank Tucker’s accounts:
mysql> SELECT account_id, cust_id, product_cd, avail_balance -> FROM account -> WHERE avail_balance < ALL (SELECT a.avail_balance -> FROM account a INNER JOIN individual i -> ON a.cust_id = i.cust_id -> WHERE i.fname = 'Frank' AND i.lname = 'Tucker'); +------------+---------+------------+---------------+ | account_id | cust_id | product_cd | avail_balance | +------------+---------+------------+---------------+ | 2 | 1 | SAV | 500.00 | | 5 | 2 | SAV | 200.00 | | 10 | 4 | CHK | 534.12 | | 11 | 4 | SAV | 767.77 | | 14 | 6 | CHK | 122.37 | | 19 | 8 | SAV | 387.99 | | 21 | 9 | CHK | 125.67 | | 25 | 10 | BUS | 0.00 | +------------+---------+------------+---------------+ 8 rows in set (0.17 sec)
Here’s the data returned by the subquery, which consists of the available balance from each of Frank’s accounts:
mysql> SELECT a.avail_balance -> FROM account a INNER JOIN individual i -> ON a.cust_id = i.cust_id -> WHERE i.fname = 'Frank' AND i.lname = 'Tucker'; +---------------+ | avail_balance | +---------------+ | 1057.75 | | 2212.50 | +---------------+ 2 rows in set (0.01 sec)
Frank has two accounts, with the lowest balance being $1,057.75. The containing query finds all accounts having a balance smaller than any of Frank’s accounts, so the result set includes all accounts having a balance less than $1,057.75.
Download
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.
Access | Data Mining |
Data Modeling & Design | Data Processing |
Data Warehousing | MySQL |
Oracle | Other Databases |
Relational Databases | SQL |
Algorithms of the Intelligent Web by Haralambos Marmanis;Dmitry Babenko(8031)
Learning SQL by Alan Beaulieu(5716)
Weapons of Math Destruction by Cathy O'Neil(5366)
Azure Data and AI Architect Handbook by Olivier Mertens & Breght Van Baelen(4667)
Building Statistical Models in Python by Huy Hoang Nguyen & Paul N Adams & Stuart J Miller(4664)
Serverless Machine Learning with Amazon Redshift ML by Debu Panda & Phil Bates & Bhanu Pittampally & Sumeet Joshi(4545)
Data Wrangling on AWS by Navnit Shukla | Sankar M | Sam Palani(4319)
Driving Data Quality with Data Contracts by Andrew Jones(4239)
Big Data Analysis with Python by Ivan Marin(4189)
Machine Learning Model Serving Patterns and Best Practices by Md Johirul Islam(4015)
Data Engineering with dbt by Roberto Zagni(3243)
Blockchain Basics by Daniel Drescher(3051)
Solidity Programming Essentials by Ritesh Modi(2872)
Time Series Analysis with Python Cookbook by Tarek A. Atwan(2746)
Hands-On Machine Learning for Algorithmic Trading by Stefan Jansen(2744)
Feature Store for Machine Learning by Jayanth Kumar M J(2670)
Learn T-SQL Querying by Pam Lahoud & Pedro Lopes(2660)
Pandas Cookbook by Theodore Petrou(2625)
Mastering Python for Finance by Unknown(2604)