電子工業出版社 JEUISHING HOUSE DF ELECTRONCS INDSTR 【例6.34】在样例数据库pubs中,查询出版过图书的出版社 USE pubs SELECT *k FROM publishers id IN(SELECT pub id FROM titles) 运行结果: pub_id pub_name state country 1389AlgodataInfosystemsBerkeley 0877 Binnet Hardley Washington DC USA 36N Boston (所影响的行数为3行) 此例中,首先, select pub id from titles子查询从 titles表中返回了 1389,087,0736三个 pub id,然后外部查询 SELECT* FROM publishers Where pub id IN (1389,0877,0736)语句查询出最后结果。 【例6.35】在样例数据库pubs中,查询居住在出版社 Algodata Infosystems所在城市的 作者。 SELECt au Iname, au_ fname, city FROM authors Where city=(select city FROM publishers WheRe pub name ='Algodata Infosystems) 运行结果: au lname u fname cIty Berkeley Abraha Berkeley (所影响的行数为2行) 此例中,首先, select city FROM publishers WHERE pub name=' algodata Infosystems’子查询从 publishers表中返回了 Algodata Infosys tems出版社的所在城市 Berkeley,然后外部查询 SeLECT au_ Iname, au fname, city from authors WherE city (' Berkeley’)语句查询出最后结果。此例中,使用“=”运算符,当然还可以使用其它运算 符引入子查询。 【例636】在样例数据库pubs中,查询作者收到的预付款大于 New moon books出版 社支付的最低预付款金额的书名及预付款 USE pubs select title, advance from titles
【例 6.34】在样例数据库 pubs 中，查询出版过图书的出版社 USE pubs SELECT * FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles) ORDER BY pub_name 运行结果： pub_id pub_name city state country ------ -------------------------------------------------------- 1389 Algodata Infosystems Berkeley CA USA 0877 Binnet & Hardley Washington DC USA 0736 New Moon Books Boston MA USA （所影响的行数为 3 行） 此例中， 首先， SELECT pub_id FROM titles 子 查 询从 titles 表 中返 回 了 1389,0877,0736 三个 pub_id，然后外部查询 SELECT * FROM publishers WHERE pub_id IN (1389,0877,0736)语句查询出最后结果。 【例 6.35】在样例数据库 pubs 中，查询居住在出版社 Algodata Infosystems 所在城市的 作者。 USE pubs SELECT au_lname, au_fname, city FROM authors WHERE city = (SELECT city FROM publishers WHERE pub_name ='Algodata Infosystems') 运行结果： au_lname au_fname city ---------------------------------------- ----- Carson Cheryl Berkeley Bennet Abraham Berkeley （所影响的行数为 2 行） 此例中， 首先， SELECT city FROM publishers WHERE pub_name = 'Algodata Infosystems'子查询从 publishers 表中返回了 Algodata Infosystems 出版社的所在城市 Berkeley，然后外部查询 SELECT au_lname, au_fname，city FROM authors WHERE city = ('Berkeley')语句查询出最后结果。此例中，使用“=”运算符，当然还可以使用其它运算 符引入子查询。 【例 6.36】在样例数据库 pubs 中，查询作者收到的预付款大于 New Moon Books 出版 社支付的最低预付款金额的书名及预付款。 USE pubs SELECT title, advance FROM titles
電子工業出版社 SHING HOUSE DF ELECTRONCS WHERE advance >ANY (SELECT advance FRoM publishers INNER JOIN titles ON titles. pub id publishers. pub id AND pub name = 'New Moon Books') ORDER BY advance 运行结果 Is Anger the Enemy? 2275,0000 Emotional Security: A New Algorithm 4000.0000 Fifty Years in Buckingham Palace Kitchens 4000.0000 The Busy Executive s Database Guide Cooking with Computers: Surreptitious Balance Sheets 5000.0000 Straight Talk About Computers Life Without Fear Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 7000 0000 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 7000 0000 But Is It User Friendly? 7000.0000 Secrets of Silicon Valley 8000.0000 Sushi, Anyone? 8000.0000 You Can Combat Computer Stress! 10125.0000 The Gourmet Microwave 15000.0000 (所影响的行数为14行) 此例中,首先, SeLECt advance from publishers inner join titles on titles. pub id = publishers. pub id and pub name=' New moon books’子查询返回了 New Moon books 出版社的所有书籍的预付款(2000.0000,2275.00,400000,6000.000,10125.0000,而 ANY关键字的含义是任何一个值,所以>ANY的取值为大于集合中的最小值,即>2000.0000 然后外部查询 seleCt title, advance from titles Where advance>2000.0000语句查询出 最后结果 【例6.37】在样例数据库pubs中,查询作者收到的预付款大于 New moon books出版 社支付的最高预付款金额的书名及预付款 USE pubs select title, advance from titles WHERE advance >ALL (SELECT advance FROM publishers INNER JOIN titles ON titles. pub _id publishers. pub id AND pub name =New Moon Books') ORDER BY advance 运行结果 The Gourmet Microwave 15000.0000 (所影响的行数为1行
WHERE advance > ANY (SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books') ORDER BY advance 运行结果： title advance ------------------------------------------------------------------------------- Is Anger the Enemy? 2275.0000 Emotional Security: A New Algorithm 4000.0000 Fifty Years in Buckingham Palace Kitchens 4000.0000 The Busy Executive's Database Guide 5000.0000 Cooking with Computers: Surreptitious Balance Sheets 5000.0000 Straight Talk About Computers 5000.0000 Life Without Fear 6000.0000 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 7000.0000 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 7000.0000 But Is It User Friendly? 7000.0000 Secrets of Silicon Valley 8000.0000 Sushi, Anyone? 8000.0000 You Can Combat Computer Stress! 10125.0000 The Gourmet Microwave 15000.0000 （所影响的行数为 14 行） 此例中， 首先，SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books'子查询返回了 New Moon Books 出版社的所有书籍的预付款(2000.0000,2275.0000,4000.0000,6000.0000,10125.0000),而 ANY 关键字的含义是任何一个值，所以>ANY 的取值为大于集合中的最小值，即>2000.0000； 然后外部查询 SELECT title, advance FROM titles WHERE advance>2000.0000 语句查询出 最后结果。 【例 6.37】在样例数据库 pubs 中，查询作者收到的预付款大于 New Moon Books 出版 社支付的最高预付款金额的书名及预付款。 USE pubs SELECT title, advance FROM titles WHERE advance >ALL (SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books') ORDER BY advance 运行结果： title advance -------------------------------------------------- The Gourmet Microwave 15000.0000 （所影响的行数为 1 行）
電子工業出版社 JSHING HOUSE DF ELECTRONCS INDSTE 此例中,首先, SELECt advance FRoM publishers INNEr Join titles on titles.pubi publishers. pub id and pub name=' New moon books’子查询返回了 New Moon books 出版社的所有书籍的预付款(2000.000,2275.0000,400000,6000.0000,10125.0000),而 AL关键字的含义是每一个值,所以>ALL的取值为大于集合中的最大值,即》10125.0000 然后外部查询 select title, advance from titles Where advance10125.000语句查询 出最后结果。 【例638】在样例数据库pubs中,查询出版过 psychology类型的书籍的出版社名单 USE pubs SELECT a* FROM publishers WHERE EXISTS (SELECT FROM titles Where pub id =publishers. pub id AND type psychology') 运行结果: pub id pub name Ity state country New Moon books Boston MA USA 0877 Binnet Hardley Washington DC USA (所影响的行数为2行) 此例中,首先, EXISTS( SELECT* From titles Where pub id= publishers. pub id ANd type=' psychology')子査询测试是否存在出版过 psychology类型书籍的出版社,如 果存在,则外部查询从 publisher表中查询出该出版社的信息
此例中，首先，SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books'子查询返回了 New Moon Books 出版社的所有书籍的预付款(2000.0000,2275.0000,4000.0000,6000.0000,10125.0000),而 ALL 关键字的含义是每一个值，所以>ALL 的取值为大于集合中的最大值，即>10125.0000； 然后外部查询 SELECT title, advance FROM titles WHERE advance>10125.0000 语句查询 出最后结果。 【例 6.38】在样例数据库 pubs 中，查询出版过 psychology 类型的书籍的出版社名单。 USE pubs SELECT * FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'psychology') 运行结果： pub_id pub_name city state country ------ ---------------------------------------- ------------- 0736 New Moon Books Boston MA USA 0877 Binnet & Hardley Washington DC USA （所影响的行数为 2 行） 此例中， 首先，EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'psychology')子查询测试是否存在出版过 psychology 类型书籍的出版社，如 果存在，则外部查询从 publisher 表中查询出该出版社的信息