SQL Puzzles & Answers- P1

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST

Báo xấu

94
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Quay lại vào đầu và giữa thập niên 1990, tôi đã viết trên tạp chí thường xuyên trong cột Cơ sở dữ liệu Lập trình & Thiết kế và sau này trong DBMS tạp chí. Các gimmick tôi được sử dụng để thu hút người đọc được câu trả lời để kết thúc mỗi cột với một câu đố lập trình SQL. Mười năm sau, hai tạp chí được hợp nhất thành thông minh Doanh nghiệp. câu đố của tôi chuyển đến SQL một số ấn phẩm nhỏ hơn và sau đó cuối cùng đã phai mờ...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: SQL Puzzles & Answers- P1

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
JOE CELKO’S SQL PUZZLES & ANSWERS Second Edition Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Morgan Kaufmann Series in Data Management Systems Series Editor: Jim Gray, Microsoft Research Joe Celko’s Analytics and OLAP in SQL Joe Celko Data Preparation for Data Mining Using SAS Mamdouh Refaat Querying XML: XQuery, XPath, and SQL/XML in Context Jim Melton and Stephen Buxton Data Mining: Concepts and Techniques, Second Edition Jiawei Han and Micheline Kamber Database Modeling and Design: Logical Design, Fourth Edition Toby J, Teorey, Sam S. Lightstone and Thomas P. Nadeau Foundations of Multidimensional and Metric Data Structures Hanan Samet Joe Celko’s SQL for Smarties: Advanced SQL Programming, Third Edition Joe Celko Moving Objects Databases Ralf Hartmut Güting and Markus Schneider Joe Celko’s SQL Programming Style Joe Celko Data Mining, Second Edition: Concepts and Techniques Ian Witten and Eibe Frank Fuzzy Modeling and Genetic Algorithms for Data Mining and Exploration Earl Cox Data Modeling Essentials, Third Edition Graeme C. Simsion and Graham C. Witt Location-Based Services Jochen Schiller and Agnès Voisard Database Modeling with Microsft“ Visio for Enterprise Architects Terry Halpin, Ken Evans, Patrick Hallock, Bill Maclean Designing Data-Intensive Web Applications Stephano Ceri, Piero Fraternali, Aldo Bongio, Marco Brambilla, Sara Comai, and Maristella Matera Mining the Web: Discovering Knowledge from Hypertext Data Soumen Chakrabarti Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Advanced SQL: 1999—Understanding Object-Relational and Other Advanced Features Jim Melton Database Tuning: Principles, Experiments, and Troubleshooting Techniques Dennis Shasha and Philippe Bonnet SQL:1999—Understanding Relational Language Components Jim Melton and Alan R. Simon Information Visualization in Data Mining and Knowledge Discovery Edited by Usama Fayyad, Georges G. Grinstein, and Andreas Wierse Transactional Information Systems: Theory, Algorithms, and Practice of Concurrency Control and Recovery Gerhard Weikum and Gottfried Vossen Spatial Databases: With Application to GIS Philippe Rigaux, Michel Scholl, and Agnes Voisard Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design Terry Halpin Component Database Systems Edited by Klaus R. Dittrich and Andreas Geppert Managing Reference Data in Enterprise Databases: Binding Corporate Data to the Wider World Malcolm Chisholm Understanding SQL and Java Together: A Guide to SQLJ, JDBC, and Related Technologies Jim Melton and Andrew Eisenberg Database: Principles, Programming, and Performance, Second Edition Patrick and Elizabeth O'Neil The Object Data Standard: ODMG 3.0 Edited by R. G. G. Cattell and Douglas K. Barry Data on the Web: From Relations to Semistructured Data and XML Serge Abiteboul, Peter Buneman, and Dan Suciu Data Mining: Practical Machine Learning Tools and Techniques with Java Implementations Ian Witten and Eibe Frank Joe Celko’s SQL for Smarties: Advanced SQL Programming, Second Edition Joe Celko Joe Celko’s Data and Databases: Concepts in Practice Joe Celko Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Developing Time-Oriented Database Applications in SQL Richard T. Snodgrass Web Farming for the Data Warehouse Richard D. Hackathorn Management of Heterogeneous and Autonomous Database Systems Edited by Ahmed Elmagarmid, Marek Rusinkiewicz, and Amit Sheth Object-Relational DBMSs: Tracking the Next Great Wave, Second Edition Michael Stonebraker and Paul Brown,with Dorothy Moore A Complete Guide to DB2 Universal Database Don Chamberlin Universal Database Management: A Guide to Object/Relational Technology Cynthia Maro Saracco Readings in Database Systems, Third Edition Edited by Michael Stonebraker and Joseph M. Hellerstein Understanding SQL’s Stored Procedures: A Complete Guide to SQL/PSM Jim Melton Principles of Multimedia Database Systems V. S. Subrahmanian Principles of Database Query Processing for Advanced Applications Clement T. Yu and Weiyi Meng Advanced Database Systems Carlo Zaniolo, Stefano Ceri, Christos Faloutsos, Richard T. Snodgrass, V. S. Subrahmanian, and Roberto Zicari Principles of Transaction Processing Philip A. Bernstein and Eric Newcomer Using the New DB2: IBMs Object-Relational Database System Don Chamberlin Distributed Algorithms Nancy A. Lynch Active Database Systems: Triggers and Rules For Advanced Database Processing Edited by Jennifer Widom and Stefano Ceri Migrating Legacy Systems: Gateways, Interfaces, & the Incremental Approach Michael L. Brodie and Michael Stonebraker Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Atomic Transactions Nancy Lynch, Michael Merritt, William Weihl, and Alan Fekete Query Processing for Advanced Database Systems Edited by Johann Christoph Freytag, David Maier, and Gottfried Vossen Transaction Processing: Concepts and Techniques Jim Gray and Andreas Reuter Building an Object-Oriented Database System: The Story of O2 Edited by François Bancilhon, Claude Delobel, and Paris Kanellakis Database Transaction Models for Advanced Applications Edited by Ahmed K. Elmagarmid A Guide to Developing Client/Server SQL Applications Setrag Khoshafian, Arvola Chan, Anna Wong, and Harry K. T. Wong The Benchmark Handbook for Database and Transaction Processing Systems, Second Edition Edited by Jim Gray Camelot and Avalon: A Distributed Transaction Facility Edited by Jeffrey L. Eppinger, Lily B. Mummert, and Alfred Z. Spector Readings in Object-Oriented Database Systems Edited by Stanley B. Zdonik and David Maier Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This Page Intentionally Left Blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
JOE CELKO’S SQL PUZZLES & ANSWERS Second Edition Joe Celko Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Publisher Diane Cerra Publishing Services Manager George Morrison Editorial Assistant Asma Palmeiro Cover Design Side by Side Studios Cover Image Side by Side Studios Cover Designer Eric DeCicco Composition Multiscience Press, Inc. Copyeditor Multiscience Press, Inc. Proofreader Multiscience Press, Inc. Indexer Multiscience Press, Inc. Interior printer The Maple-Vail Book Manufacturing Group Cover printer Phoenix Color Corp. Morgan Kaufmann Publishers is an imprint of Elsevier. 500 Sansome Street, Suite 400, San Francisco, CA 94111 This book is printed on acid-free paper. © 2007 by Elsevier Inc. All rights reserved. Designations used by companies to distinguish their products are often claimed as trademarks or registered trademarks. In all instances in which Morgan Kaufmann Publishers is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means-electronic, mechanical, photocopying, scanning, or otherwise-without prior written permission of the publisher. Permissions may be sought directly from Elsevier’s Science & Technology Rights Department in Oxford, UK: phone: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: permissions@elsevier.com.uk. You may also complete your request on-line via the Elsevier homepage (http://elsevier.com) by selecting “Customer Support” and then “Obtaining Permissions.” Library of Congress Cataloging-in-Publication Data Application submitted. ISBN-10 : 0-12-373596-3 ISBN-13: 978-0-12-373596-3 For information on all Morgan Kaufmann publications, visit our Web site at www.mkp.com or www.books.elsevier.com Printed in the United States of America 06 07 08 09 5 4 3 2 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
To chanticleer Michael— I now have a convincing argument against solipsism for you. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This Page Intentionally Left Blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
C O N T E N T S Introduction xv Acknowledgments, Corrections, and Future Editions xvi Puzzle 1 Fiscal Year Tables 1 2 Absentees 4 3 The Anesthesia Puzzle 9 4 Security Badges 16 5 Alpha Data 19 6 Hotel Reservations 21 7 Keeping A Portfolio 24 8 Scheduling Printers 29 9 Available Seats 34 10 Wages Of Sin 37 11 Work Orders 45 12 Claims Status 48 13 Teachers 53 14 Telephone 56 15 Find The Last Two Salaries 60 16 Mechanics 69 17 Employment Agency 75 18 Junk Mail 80 19 Top Salespeople 82 20 Test Results 86 21 Airplanes And Pilots 88 22 Landlord 92 23 Magazine 94 24 One In Ten 103 25 Milestone 107 26 Dataflow Diagrams 112 27 Finding Equal Sets 115 28 Calculate The Sine Function 121 29 Find The Mode Computation 123 30 Average Sales Wait 126 31 Buying All The Products 129 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xii CONT ENTS 32 Computing Taxes 132 33 Computing Depreciation 137 34 Consultant Billing 141 35 Inventory Adjustments 145 36 Double Duty 148 37 A Moving Average 152 38 Journal Updating 155 39 Insurance Losses 158 40 Permutations 163 41 Budgeting 169 42 Counting Fish 172 43 Graduation 176 44 Pairs Of Styles 179 45 Pepperoni Pizza 183 46 Sales Promotions 186 47 Blocks Of Seats 190 48 Ungrouping 192 49 Widget Count 200 50 Two Of Three 203 51 Budget Versus Actual 208 52 Personnel Problem 212 53 Collapsing A Table By Columns 215 54 Potential Duplicates 218 55 Playing The Ponies 221 56 Hotel Room Numbers 224 57 Gaps—version One 227 58 Gaps—version Two 230 59 Merging Time Periods 234 60 Barcodes 237 61 Sort A String 242 62 Report Formatting 244 63 Contiguous Groupings 254 64 Boxes 257 65 Age Ranges For Products 261 66 Sudoku 263 67 Stable Marriages Problem 267 68 Catching The Next Bus 280 69 Lifo-fifo Inventory 283 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
C ONTE NTS xiii 70 Stock Trends 292 71 Calculations 297 72 Scheduling Service Calls 300 73 A Little Data Scrubbing 304 74 Derived Tables Or Not? 306 75 Finding A Pub 309 Index 313 About the Author 327 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This Page Intentionally Left Blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER Introduction Back in the early and mid-1990s, I wrote regular magazine columns in Database Programming & Design and later in DBMS magazine. The gimmick I used to attract reader responses was to end each column with a SQL programming puzzle. Ten years later, those two magazines were consolidated into Intelligent Enterprise. My SQL puzzles moved to some smaller publications and then finally faded away. Today, I throw out a puzzle or two on the www.dbazine.com Web site and other places on the Internet rather than in print media. Over the years, college students had all kinds of programming contests that used the procedural language du jour—C, Pascal, then Java and C++ today. There is not much for database programmers to test themselves against, except my little puzzle book. I would often find my puzzles showing up in homework assignments because I was the only source that teachers knew about for SQL problems. I would then get an e-mail from a lazy student wanting me to do his homework for him, unaware of the source of the assignment. Back in those early days, the de facto standard was SQL-86, and the SQL-92 standard was a design goal for the database vendors. Today, most vendors have gotten most of SQL-92 into their products. The design goal is now the SQL-99 standard’s OLAP features. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xvi INTRODUCTION A decade ago, college students took RDBMS courses, and becoming an SQL programmer required some expertise. SQL products were expensive and the best ones lived on mainframes. Today, colleges are not teaching RDBMS theory in the undergrad curriculum. SQL is not as exotic as it once was, and you can get cheap or open-source SQL databases. The Internet is full of newsgroups where you can get help for particular products. The bad news is that the quality of SQL programmers has gotten worse because people who have no foundations in RDBMS or training in SQL are being asked to write SQL inside their native programming languages. This collection of puzzles includes the original puzzles, so that the original readers can look up their favorites. But now many of them have new solutions, some use the older syntax, and some use the newer features. Many of the original solutions have been cooked by other people over the years. The term “cooked” is a puzzler’s term for finding a better solution than the proposer of the problem presented. The original book contained 50 puzzles; this edition has 75 puzzles. In the first edition, I tried to organize the puzzles by categories rather than in chronological order or by complexity. This edition, I have given up my informal category scheme because it made no sense. A problem might be solved by a change to the DDL or a query, so should it be categorized as a DDL puzzle or a DML puzzle? I have tried to credit the people involved with each problem, but if I missed someone, I apologize. Acknowledgments, Corrections, and Future Editions I will be glad to receive corrections, new tricks and techniques, and other suggestions for future editions of this book. Send your ideas to or contact me through the publisher, Morgan Kaufmann. I would like to thank Diane Cerra of Morgan Kaufmann, David Kalman of DBMS magazine, Maurice Frank of DBMS magazine, David Stodder of Database Programming & Design, Phil Chapnick of Miller- Freeman, Frank Sweet of Boxes & Arrows, and Dana Farver at www.dbazine.com. Special thanks to Richard Romley of Smith Barney for cooking so many of my early puzzles, all the people on CompuServe and SQL newsgroups who sent me e-mail all these years, and the people who are posting on the newsgroups today (I used your newsgroup handles, so people can search for your postings). These include, but are not limited to, Raymond D’Anjou, Dieter Noeth, Alexander Kuznetsov, Andrey Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Acknowledgments, Corrections, and Future Editions xvii Odegov, Steve Kass,Tibor Karaszi, David Portas, Hugo Kornelis, Aaron Bertrand, Itzik Ben-Gan, Tom Moreau, Serge Rielau, Erland Sommarskog, Mikito Harakiri, Adam Machanic, and Daniel A. Morgan. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This Page Intentionally Left Blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PUZZLE 1 FISCAL YEAR TABLES 1 PUZZLE 1 FISCAL YEAR TABLES Let’s write some CREATE TABLE statements that are as complete as possible. This little exercise is important because SQL is a declarative language and you need to learn how to specify things in the database instead of in the code. The table looks like this: CREATE TABLE FiscalYearTable1 (fiscal_year INTEGER, start_date DATE, end_date DATE); It stores date ranges for determining what fiscal year any given date belongs to. For example, the federal government runs its fiscal year from October 1 until the end of September. The scalar subquery you would use to do this table lookup is: (SELECT F1.fiscal_year FROM FiscalYearTable1 AS F1 WHERE outside_date BETWEEN F1.start_date AND F1.end_date) Your assignment is to add all the constraints you can think of to the table to guarantee that it contains only correct information. While vendors all have different date and time functions, let’s assume that all we have is the SQL-92 temporal arithmetic and the function EXTRACT ([YEAR | MONTH | DAY] FROM ), which returns an integer that represents a field within a date. Answer #1 1. First things first; make all the columns NOT NULL since there is no good reason to allow them to be NULL. 2. Most SQL programmers immediately think in terms of adding a PRIMARY KEY, so you might add the constraint PRIMARY KEY (fiscal_year, start_date, end_date) because the fiscal year is really another name for the pair (start_date, end_date). This is not enough, because it would allow this sort of error: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.