SQL Pocket Primer

310 Pages·2022·1.622 MB·English

by  O. Campesato

SQL
Pocket Primer
LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY
By purchasing or using this book and companion files (the “Work”), you
agree that this license grants permission to use the contents contained herein,
including the disc, but does not give you the right of ownership to any of the
textual content in the book/disc or ownership to any of the information or
products contained in it. This license does not permit uploading of the Work
onto the Internet or on a network (of any kind) without the written consent
of the Publisher. Duplication or dissemination of any text, code, simulations,
images, etc. contained herein is limited to and subject to licensing terms for
the respective products, and permission must be obtained from the Publisher
or the owner of the content, etc., in order to reproduce or network any portion
of the textual material (in any media) that is contained in the Work.
Mercury Learning and inforMation (“MLI” or “the Publisher”) and anyone
involved in the creation, writing, or production of the companion disc, accom-
panying algorithms, code, or computer programs (“the software”), and any
accompanying Web site or software of the Work, cannot and do not warrant
the performance or results that might be obtained by using the contents of the
Work. The author, developers, and the Publisher have used their best efforts
to ensure the accuracy and functionality of the textual material and/or pro-
grams contained in this package; we, however, make no warranty of any kind,
express or implied, regarding the performance of these contents or programs.
The Work is sold “as is” without warranty (except for defective materials used
in manufacturing the book or due to faulty workmanship).
The author, developers, and the publisher of any accompanying content, and
anyone involved in the composition, production, and manufacturing of this
work will not be liable for damages of any kind arising out of the use of (or the
inability to use) the algorithms, source code, computer programs, or textual
material contained in this publication. This includes, but is not limited to, loss
of revenue or profit, or other incidental, physical, or consequential damages
arising out of the use of this Work.
The sole remedy in the event of a claim of any kind is expressly limited to
replacement of the book and/or disc, and only at the discretion of the Publisher.
The use of “implied warranty” and certain “exclusions” vary from state to state,
and might not apply to the purchaser of this product.
Companion files for this title are available by writing to the publisher at
[email protected].
SQL
Pocket Primer
Oswald Campesato
Mercury Learning and Information
Dulles, Virginia
Boston, Massachusetts
New Delhi
Copyright ©2022 by Mercury Learning and inforMation LLC. All rights reserved.
This publication, portions of it, or any accompanying software may not be reproduced
in any way, stored in a retrieval system of any type, or transmitted by any means,
media, electronic display or mechanical display, including, but not limited to,
photocopy, recording, Internet postings, or scanning, without prior permission
in writing from the publisher.
Publisher: David Pallai
Mercury Learning and inforMation
22841 Quicksilver Drive
Dulles, VA 20166
[email protected]
www.merclearning.com
800-232-0223
O. Campesato. SQL Pocket Primer.
ISBN: 978-1-68392-814-0
The publisher recognizes and respects all marks used by companies, manufacturers,
and developers as a means to distinguish their products. All brand names and product
names mentioned in this book are trademarks or service marks of their respective
companies. Any omission or misuse (of any kind) of service marks or trademarks, etc.
is not an attempt to infringe on the property of others.
Library of Congress Control Number: 2022930720
222324321 This book is printed on acid-free paper in the United States of America.
Our titles are available for adoption, license, or bulk purchase by institutions,
corporations, etc. For additional information, please contact the Customer Service
Dept. at 800-232-0223(toll free).
All of our titles are available in digital format at academiccourseware.com and other
digital vendors. Companion files (figures and code listings) for this title are available
by contacting [email protected]. The sole obligation of Mercury Learning and
inforMation to the purchaser is to replace the disc, based on defective materials or
faulty workmanship, but not based on the operation or functionality of the product.
I’d like to dedicate this book to my parents
– may this bring joy and happiness into their lives.

C
ontents
Preface xv
Chapter 1: Introduction to RDBMSs and MySQL 1
What is MySQL? 2
What about MariaDB? 3
Installing MySQL 3
Useful Links for MySQL 3
What is an RDBMS? 4
What Relationships Do Tables Have in an RDBMS? 4
Features of an RDBMS 5
What is ACID? 5
When Do We Need an RDBMS? 6
Transferring Money Between Bank Accounts 6
The Importance of Normalization 7
A Four-Table RDBMS 8
Detailed Table Descriptions 9
The Customers Table 10
The purchase_orders Table 11
The line_items Table 12
The item_desc Table 13
SQL Statements for the Impatient (Optional) 14
What About an Item Inventory Table? 17
The Role of SQL 17
DCL, DDL, DQL, DML, and TCL 18
SQL Privileges 18
Properties of SQL Statements 19
The CREATE Keyword 19
Data Types in MySQL 20
The CHAR and VARCHAR Data Types 20
viii • Contents
String-Based Data Types 20
FLOAT and DOUBLE Data Types 21
BLOB and TEXT Data Types 21
MySQL Database Operations 22
Creating a Database 22
Display a List of Databases 22
Display a List of Database Users 23
Dropping a Database 23
Exporting a Database 23
Renaming a Database 24
Show Database Tables 25
The INFORMATION_SCHEMA Table 27
The PROCESSLIST Table 28
SQL Formatting Tools 29
Summary 29
Chapter 2: Working with SQL and MySQL 31
Drop Database Tables 32
Create Database Tables 32
Manually Creating Tables for mytools.com 32
Creating Tables via a SQL Script for mytools.com 34
Creating Tables with Japanese Text 35
Creating Tables from the Command Line 36
Defining Table Attributes 37
Working with Aliases in SQL 38
Alter Database Tables with the ALTER Keyword 39
Add a Column to a Database Table 39
Drop a Column from a Database Table 41
Change the Data Type of a Column 41
What are Referential Constraints? 43
Combining Data for a Table Update (Optional) 44
Merging Data Columns in Multiple CSV Files via Pandas 44
Concatenating Data from Multiple CSV Files 45
Appending Table Data from CSV Files via SQL 47
Inserting Data into Database Tables 48
Populating Tables from Text Files 49
Working with Simple SELECT Statements 51
Duplicate Versus Distinct Rows 52
Unique Rows Versus Distinct Rows 53
The EXISTS Keyword 53
The LIMIT Keyword 54
DELETE, TRUNCATE, and DROP in SQL 54
SELECT, DELETE, and LIMIT Combinations 55
More Options for the DELETE Statement in SQL 56
Contents • ix
Creating Tables from Existing Tables in SQL 56
Working with Temporary Tables in SQL 57
Creating Copies of Existing Tables in SQL 58
What is a SQL Index? 58
Types of Indexes 59
Creating an Index 59
Disabling and Enabling an Index 60
View and Drop Indexes 60
Overhead of Indexes 61
Considerations for Defining Indexes 61
When to Disable Indexes on a Table 62
Selecting Columns for an Index 62
Finding Columns Included in Indexes 63
Enhancing the mytools Database (Optional) 63
Entity Relationships 64
Summary 65
Chapter 3: Joins, Views, and Subqueries 67
Query Execution Order in SQL 67
Joining Tables in SQL 68
Types of SQL JOIN Statements 68
Examples of SQL JOIN Statements 69
An INNER JOIN Statement 71
A LEFT JOIN Statement 72
A RIGHT JOIN Statement 72
A CROSS JOIN Statement 73
MySQL NATURAL JOIN Statement 73
An INNER JOIN to Delete Duplicate Attributes 74
JOIN Statements on Tables with International Text 75
What is a View? 76
Creating a View 77
Dropping a View in SQL 77
Advantages of Views in SQL Statements 77
Views Involving a Single Table 78
Views Involving Multiple Tables 78
Updatable Views 79
Keys, Primary Keys, and Foreign Keys 79
Foreign Keys versus Primary Keys 79
A MySQL Example of Foreign Keys 80
Working with Subqueries in SQL 82
Two Types of Subqueries 82
A Subquery to Find Customers Without Purchase Orders 83
Subqueries with IN and NOT IN Clause 85
Subqueries with SOME, ALL, ANY Clause 86
x • Contents
Subqueries with the MAX() and AVG() Functions 88
Find Tallest Students in Each Classroom via a Subquery 88
SQL and Histograms 90
What are GROUP BY, ORDER BY, and HAVING Clauses? 90
Displaying Duplicate Attribute Values 92
Examples of the SQL GROUP BY and ORDER BY Clause 92
SQL Histograms on a Table Copy 93
Combine GROUP BY and ROLLUP Clause 95
The 2021 Olympics Medals and the ROLLUP Keyword 97
The 2021 Olympics Medals and the RANK Operator 98
The PARTITION BY Clause 99
GROUP BY, HAVING, and ORDER BY Clause 100
Combined GROUP BY, HAVING, and ORDER BY Clause 101
Updating the item_desc Table from the new_items Table 102
A SQL Query Involving a Four-Table Join 102
Operations with Dates in SQL 106
Day and Month Components of Dates in SQL 107
Rounding Dates in SQL 108
Working with Date Ranges 109
Tables Containing Modification Times 110
Arithmetic Operations with Dates 111
Date Components and Date Formats 112
Finding the Week in Date Values 114
Displaying Weekly Revenue 114
Assorted SQL Operators 116
Working with Column Aliases 116
SQL Variables 117
SQL Summary Reports 118
Simple SQL Reports 119
Calculating SubTotals 122
Calculating “Running” (Cumulative) Totals 123
Summary 124
Chapter 4: Assorted SQL Functions 125
Numeric Functions in SQL 126
Calculated Columns 128
The round(), ceil(), and floor() Functions 129
SQL Queries with the rand() Function 132
Log, Exponential, and Trig Functions in SQL 132
Scalar Functions in SQL 135
Aggregate Functions in SQL 136
SQL Queries with the max() and min() Functions 138
Find Maximum Values with SQL Subqueries 139
Simplify SQL Queries Containing Subqueries 142