p.28
Working with Multiple Tables
How can you reference aliased columns in the WHERE clause?
By wrapping the query as an inline view.
What is the address of O’Reilly Media, Inc.?
1005 Gravenstein Highway North, Sebastopol, CA 95472.
p.19
Advanced SQL Techniques
What is required by the ANSI SQL standard for inline views?
They must be given table aliases.
What is an inline view?
A temporary result set that can be aliased for use in a query.
p.15
Advanced SQL Techniques
What is the purpose of the new recipe in Chapter 6?
To help match data by the sound of the text.
What is the preferred case for SQL statements according to the book?
lowercase for both keywords and user-specified identifiers.
Who should comments and questions about the book be addressed to?
The publisher: O’Reilly Media, Inc.
p.21
Advanced SQL Techniques
What role did Georgia play in the author's writing process?
She provided support throughout the writing of the book.
What is the example SQL query provided in the text?
select empno, ename from emp;
p.31
Advanced SQL Techniques
What happens if you omit the ELSE clause in a CASE expression?
The CASE expression will return NULL for any row that does not satisfy the test condition.
Which section covers finding rows that satisfy multiple conditions?
1.3 Finding Rows That Satisfy Multiple Conditions.
p.21
Advanced SQL Techniques
What unique qualification does Jonathan Gennick have that benefits his editing?
He is an experienced database administrator (DBA).
What is a potential problem with using SELECT * in SQL queries?
It can lead to unexpected results if the query returns a different set of columns than expected.
What does the presence of parentheses in a SQL query do?
It causes conditions within them to be evaluated together.
What is the purpose of using the AS keyword in SQL?
To change the names of the columns returned by a query to make them more readable and understandable.
What does this book aim to expand for SQL users?
Users' tool boxes and understanding of SQL capabilities.
What is the purpose of using AND and OR in a SQL query?
To combine multiple conditions for filtering records.
What section discusses retrieving all rows and columns from a table?
1.1 Retrieving All Rows and Columns from a Table.
What should users ensure regarding code samples in the SQL Cookbook?
That their use complies with open source licenses or intellectual property rights.
What is the benefit of using meaningful names for columns in query results?
It makes the results more readable and understandable for users.
p.14
Advanced SQL Techniques
What are Common Table Expressions (CTEs) used for?
To solve practical limitations of SQL.
What is the purpose of the ROWNUM function in Oracle?
It assigns an increasing number to each row returned, starting from one.
p.15
Advanced SQL Techniques
What new material is introduced in Chapters 6 and 7?
New data analysis applications, including recipes about the median absolute deviation and Benford’s law.
p.31
Advanced SQL Techniques
What does the CASE expression do in SQL?
It performs conditional logic directly in a SELECT statement.
Who is this book intended for?
Any SQL user who wants to enhance their queries, including data engineers, data scientists, and BI professionals.
p.29
String Manipulation in SQL
What SQL query would you use to concatenate employee names and their jobs from the EMP table?
SELECT ename || ' WORKS AS A ' || job AS msg FROM emp WHERE deptno = 10.
How does Oracle restrict the number of rows returned in a query?
By using ROWNUM in the WHERE clause.
What happens if no WHERE clause is specified in a SELECT statement?
Every row will be returned.
p.19
Advanced SQL Techniques
What does the letter X represent in the query 'select job, sal from (select job, max(sal) sal from emp group by job) x;'?
The name of the 'table' returned by the subquery in the FROM clause.
p.22
Advanced SQL Techniques
What did Olivier Pomel contribute to the book?
A DB2 solution for creating delimited lists from rows.
What happens when you use ROWNUM <= 5 in Oracle?
Oracle fetches rows and returns them until it reaches five rows.
p.9
Working with Multiple Tables
How can you find rows in common between two tables?
By using intersection queries or inner joins.
What SQL command would you use to view all employees assigned to department number 10?
SELECT * FROM emp WHERE deptno = 10;
p.9
Working with Multiple Tables
What is a Cartesian product?
A result set that combines every row from one table with every row from another table.
p.29
String Manipulation in SQL
Which databases use the double vertical bar for concatenation?
DB2, Oracle, and PostgreSQL.
What does aliasing refer to in SQL?
The process of giving new names to columns returned by a query using the AS keyword.
What is the purpose of the SQL Cookbook?
To provide educational, business, or sales promotional use.
p.17
Working with Multiple Tables
What does the number following the 'T' in the pivot tables signify?
The number of rows in each table, starting from 1.
Where can you find errata and additional information about the book?
On the book's web page: https://oreil.ly/sql-ckbk-2e.
Why is it easier to use SELECT * in ad hoc queries?
It retrieves all columns without listing them individually.
p.19
Advanced SQL Techniques
What kind of names are typically given to aliases on inline views?
Trivial names such as X, Y, Z, TMP1, and TMP2.
p.14
Advanced SQL Techniques
What are window functions?
A feature in SQL that allows for advanced data analysis.
Why is using ROWNUM = 5 problematic in Oracle?
Because it will never return the fifth row; it discards rows that don't meet the criteria.
What is a common mistake made by Oracle developers regarding ROWNUM?
Attempting to return a specific row using an equality condition with ROWNUM.
p.11
Date and Time Functions
What is the purpose of section 8.3?
Determining the Number of Business Days Between Two Dates.
What is an example of a query that finds employees in department 10 or those who earn a commission?
SELECT * FROM emp WHERE deptno = 10 OR commission IS NOT NULL;
How would you find employees in department 20 who earn at most $2,000?
By adding a condition in the WHERE clause: AND deptno = 20 AND salary <= 2000.
p.10
String Manipulation in SQL
What does it mean to determine whether a string is alphanumeric?
To check if the string contains only letters and numbers.
p.13
String Manipulation in SQL
What is a technique for searching mixed alphanumeric strings?
Using pattern matching functions.
p.16
Working with Multiple Tables
What is the many-to-one relationship in the context of these tables?
Many employees belong to one department.
p.12
Advanced SQL Techniques
What are Knight values in SQL?
Specific values used in certain algorithms or calculations, often related to game theory or optimization.
Why is it important to specify columns in the SELECT clause?
To ensure that no extraneous data is returned, which saves time and resources, especially over a network.
p.29
String Manipulation in SQL
How can you concatenate values from multiple columns in SQL?
By using the built-in concatenation function or operator provided by the DBMS.
What theoretical aspects does this book not cover?
Database design, theory behind SQL, and extensions for data types like XML and JSON.
p.17
Working with Multiple Tables
What is the purpose of the pivot tables?
To facilitate the creation of a series of rows for queries.
p.21
Advanced SQL Techniques
What famous quote by Isaac Newton is referenced in the acknowledgments?
“If I have seen a little further it is by standing on the shoulders of giants.”
What is the SQL command to fetch the first 5 rows from the 'emp' table in DB2?
SELECT * FROM emp FETCH FIRST 5 ROWS ONLY.
p.34
Advanced SQL Techniques
What is the difference between using a function and a numeric constant in the ORDER BY clause?
A function sorts based on its evaluated result for each row, while a numeric constant sorts based on the column's ordinal position.
p.11
Date and Time Functions
What does section 8.1 cover?
Adding and Subtracting Days, Months, and Years.
What does section 10.1 address?
Locating a Range of Consecutive Values.
p.13
Numerical Operations in SQL
How can whole numbers be converted to binary in Oracle?
Using conversion functions.
p.13
Advanced SQL Techniques
What is the purpose of pivoting a ranked result set?
To display data in a more readable format.
What is the purpose of using an alias in a WHERE clause?
To reference columns that may not have well-named identifiers.
Why does referencing an aliased column in the WHERE clause fail?
Because the WHERE clause is evaluated before the SELECT clause, so aliases do not exist yet.
p.31
Advanced SQL Techniques
How can you provide a more readable result set when using the CASE expression?
By providing an alias for the CASE expression.
p.28
Advanced SQL Techniques
What are some scenarios where you need to use an inline view?
When referencing aggregate functions, scalar subqueries, windowing functions, or aliases in the WHERE clause.
What is the book's approach to coding style?
Use whatever coding style you prefer or what your project requires.
How can you limit the number of rows returned in a DB2 query?
By using the FETCH FIRST clause.
p.17
Working with Multiple Tables
What is the purpose of using the T1 table in the book?
To provide a single row instead of using partial queries for clarity.
p.17
Working with Multiple Tables
How does the T1 table compare to Oracle's DUAL table?
It serves a similar purpose but is standardized across all implementations discussed in the book.
p.22
Advanced SQL Techniques
What functionality did Bruno Denuit of Microsoft help with?
Questions regarding window functions in SQL Server 2005.
p.10
String Manipulation in SQL
How can unwanted characters be removed from a string in SQL?
By using string functions that filter or replace characters.
Why is it important to list foreign keys without corresponding indexes?
To identify potential performance issues and ensure referential integrity.
What is the purpose of using ORDER BY with a random function?
To sort rows randomly before limiting the number of rows returned.
p.16
Working with Multiple Tables
What types of fields are present in the EMP table?
Numeric, string, and date fields.
p.13
Numerical Operations in SQL
How can you calculate percent relative to total in SQL?
By using aggregate functions.
p.12
Advanced SQL Techniques
What does shifting row values entail?
Adjusting the position of data within a result set to compare current and previous values.
p.12
Advanced SQL Techniques
How can simple forecasts be generated in SQL?
By using historical data to predict future values through aggregation and trend analysis.
p.12
Working with Multiple Tables
What is the significance of hierarchical queries?
To retrieve data that has a parent-child relationship, allowing for structured data representation.
p.23
Advanced SQL Techniques
What is the purpose of the GENERATE_SERIES function?
To provide more elegant solutions compared to pivot tables.
p.23
Advanced SQL Techniques
What type of problems did Kay Young and the author work on together?
Everyday SQL problems at Wireless Generation.
p.23
Advanced SQL Techniques
What role did Kay Young play in the writing of the book?
Provided advice, grammar corrections, and code assistance.
How can you retrieve specific columns from a table?
By specifying the columns you are interested in the SELECT statement.
How does the book differentiate SQL keywords in the text?
By using uppercase for SQL keywords and identifiers.
What prior knowledge is suggested for readers of this book?
Some familiarity with SQL, such as having read 'Learning SQL' by Alan Beaulieu.
What does the * character represent in SQL?
It returns every column for the specified table.
What is the order of evaluation for SQL clauses?
FROM clause is evaluated before the WHERE clause, and SELECT clause is evaluated after.
p.34
Advanced SQL Techniques
How do you limit the number of rows returned in Oracle?
Use ROWNUM in the WHERE clause.
What is the focus of section 1.4?
Retrieving a Subset of Columns from a Table.
p.14
Advanced SQL Techniques
What significant change has occurred regarding CTEs since the first edition?
CTEs are now available in all five implementations of SQL.
p.22
Advanced SQL Techniques
Who is thanked for reviewing the MODEL clause article?
John Haydu and the MODEL clause development team at Oracle Corporation.
What is the SQL command to limit the number of rows to 5 in MySQL?
SELECT * FROM emp LIMIT 5.
p.9
Working with Multiple Tables
What is the method to retrieve values from one table that do not exist in another?
Using a LEFT JOIN with a NULL check or NOT EXISTS clause.
What do many SQL vendors provide to limit the number of rows returned?
Clauses such as FETCH FIRST and LIMIT.
p.11
Date and Time Functions
What is covered in section 9.1?
Determining Whether a Year Is a Leap Year.
p.13
String Manipulation in SQL
What is a method to extract elements of a string from unfixed locations?
Using string functions in SQL.
p.13
String Manipulation in SQL
What is the method for parsing serialized data into rows?
Using string manipulation functions.
p.12
Numerical Operations in SQL
How do you find records with the highest and lowest values?
By using the MAX() and MIN() functions in SQL queries.
What is the method for suppressing duplicates in SQL results?
Using the DISTINCT keyword to eliminate duplicate rows from the result set.
What is the purpose of creating aliases in SQL queries?
To make the query and its results more understandable to others.
What does constant width bold indicate in examples?
User input, tips, suggestions, or warnings.
What is SQL often referred to as in the data profession?
The lingua franca of the data professional.
p.34
Advanced SQL Techniques
How do you select 5 random employees in PostgreSQL?
Use the query: SELECT ename, job FROM emp ORDER BY random() LIMIT 5.
p.21
Advanced SQL Techniques
What did the author's in-laws provide during his writing experience?
Support and a feeling of being at home.
What email address can be used to ask technical questions about the book?
bookquestions@oreilly.com.
What is a common misconception about SQL users?
That they only need to know the simplest queries.
What versions of SQL platforms were used in the preparation of this text?
DB2 11.5, Oracle Database 19c, PostgreSQL 12.
What social media platforms can you follow O’Reilly Media on?
Facebook, Twitter, and YouTube.
What is a recommended practice when writing program code for SQL queries?
Specify each column individually instead of using SELECT *.
p.19
Advanced SQL Techniques
What is the purpose of numbering SQL in the 'Solution' section of recipes?
To reference parts of the query by number in the 'Discussion' section.
p.21
Advanced SQL Techniques
What does Ales Spetic hope for the book in relation to other SQL authors?
That it will complement the existing works of outstanding authors.
Why is this book considered a 'second book on SQL'?
It is meant for readers who have learned the basics and want to expand their knowledge.
How can you specify multiple conditions in a SQL query?
By using AND, OR, and parentheses in the WHERE clause.
Why does ROWNUM = 1 work to return the first row?
Oracle must attempt to fetch at least once to determine if there are any rows in the table.
p.13
Advanced SQL Techniques
What is the difference between a scalar subquery and a composite subquery in Oracle?
A scalar subquery returns a single value, while a composite subquery can return multiple values.
How can you determine which rows are reciprocals in a dataset?
By comparing values in two columns to identify pairs that are inverses of each other.
p.12
Sorting Query Results
How can results be ranked in SQL?
Using the RANK() or DENSE_RANK() functions to assign a rank to each row based on specified criteria.
p.12
Reporting and Reshaping
How can you create buckets of data in SQL?
By using the GROUP BY clause to categorize data into defined ranges or intervals.
p.28
Working with Multiple Tables
What is the structure of the inline view used to reference aliases?
SELECT * FROM (SELECT sal AS salary, comm AS commission FROM emp) x WHERE salary < 5000.
What SQL command is used to retrieve all rows and columns from a table?
SELECT * FROM table_name;
What result set is produced by concatenating the ENAME and JOB columns?
CLARK WORKS AS A MANAGER, KING WORKS AS A PRESIDENT, MILLER WORKS AS A CLERK.
What would the query 'select * from emp where deptno = 10 or comm is not null or sal <= 2000 and deptno=20' return?
Rows where DEPTNO is 10, COMM is not NULL, or salary is $2,000 or less for DEPTNO 20.
p.19
Advanced SQL Techniques
What is the purpose of using aliases like X and Y in inline views?
To identify the result sets from inline views.
What is the emphasis of this book?
Practical queries that can solve real-world problems.
p.34
Advanced SQL Techniques
What does the ORDER BY clause do when used with a function?
It changes the order of the result set based on the function's return value.
p.14
Advanced SQL Techniques
What was the status of window functions at the time of the first edition?
They were not available in every implementation.
p.11
Date and Time Functions
What is discussed in section 8.2?
Determining the Number of Days Between Two Dates.
p.9
Inserting, Updating, and Deleting Records
What is the purpose of merging records?
To combine data from multiple records into a single record based on certain criteria.
p.30
String Manipulation in SQL
What is the SQL syntax to concatenate employee name and job in MySQL?
SELECT CONCAT(ename, ' WORKS AS A ', job) AS msg FROM emp WHERE deptno=10.
p.13
Advanced SQL Techniques
How can you add a column header into a double pivoted result set?
By modifying the query structure.
p.12
Reporting and Reshaping
What is the purpose of pivoting a result set?
To transform rows into columns for better data representation and analysis.
How would you write a query to rename 'sal' and 'comm' to 'salary' and 'commission'?
SELECT sal AS salary, comm AS commission FROM emp.
How would you write a query to see only the name, department number, and salary for employees?
select ename, deptno, sal from emp.
What is a notable feature of some vendors regarding SELECT statements?
They allow partial SELECT statements without a FROM clause.
What does section 1.5 address?
Providing Meaningful Names for Columns.
p.21
Advanced SQL Techniques
Who does the author acknowledge as influential figures in the SQL community?
Joe Celko, David Rozenshtein, Anatoly Abramovich, Eugine Berger, Iztik Ben-Gan, Richard Snodgrass, and others.
What is discussed in section 2.1?
Returning Query Results in a Specified Order.
Which section deals with sorting by multiple fields?
2.2 Sorting by Multiple Fields.
p.10
String Manipulation in SQL
What is the significance of extracting initials from a name?
It allows for concise representation of names in data processing.
p.10
Numerical Operations in SQL
What SQL function can be used to find the minimum or maximum value in a column?
MIN() for minimum and MAX() for maximum.
p.30
Advanced SQL Techniques
Provide an example of a result set using conditional logic for employee salaries.
ENAME, SAL, STATUS: SMITH, 800, UNDERPAID; KING, 5000, OVERPAID.
p.16
Working with Multiple Tables
What is the purpose of using the EMP and DEPT tables in this book?
To provide examples that are likely to be implemented in the real world.
p.12
Advanced SQL Techniques
What is the purpose of investigating future rows in SQL?
To analyze upcoming data points based on current trends or historical data.
p.12
Date and Time Functions
How can you group rows by units of time in SQL?
By using the GROUP BY clause with date or time functions to aggregate data over specified time intervals.
p.9
Working with Multiple Tables
What is the purpose of stacking one rowset atop another?
To combine multiple sets of data into a single result set.
How can you retrieve only specific rows from a table?
By using the WHERE clause to specify conditions.
How can you find all rows that are null for a particular column?
Use a query with the WHERE clause to check for NULL values.
What does section 2.5 focus on?
Dealing with Nulls When Sorting.
Why does ROWNUM = 5 fail to return the fifth row?
You can't have a fifth row if rows one through four haven't been returned first.
p.11
Advanced SQL Techniques
What does section 11.1 discuss?
Paginating Through a Result Set.
p.16
Working with Multiple Tables
What types of fields are present in the DEPT table?
Numeric and string fields.
What is the method for selecting the top n records in SQL?
Using the LIMIT clause or equivalent to restrict the number of returned rows.
p.12
Advanced SQL Techniques
What is a sparse matrix in SQL?
A matrix representation where most elements are zero or empty, often used for efficient storage.
What are the benefits of specifying each column in a SQL query?
It improves clarity and ensures you know what columns are being returned.
Why are coding conventions important in this book?
They enhance understanding and cannot be repeated for each recipe.
What operators are commonly supported in the WHERE clause?
=, <, >, <=, >=, !, and <>.
p.10
String Manipulation in SQL
What is one method to count occurrences of a character in a string?
Using specific SQL functions designed for string manipulation.
p.10
String Manipulation in SQL
What is the purpose of separating numeric and character data?
To facilitate data analysis and ensure accurate calculations.
What is the function of listing tables in a schema?
To view all tables available within a specific database schema.
What is the problem when trying to return a specific number of random records from a table?
You want to produce a different set of rows with each execution.
p.10
Numerical Operations in SQL
How can you compute an average in SQL?
By using the AVG() function on a numeric column.
p.10
Numerical Operations in SQL
What is a running total in SQL?
A cumulative sum of values in a column over a specified range.
What is a technique for testing the existence of a value within a group?
Using EXISTS or IN clauses.
p.12
Working with Multiple Tables
What is the purpose of incorporating OR logic when using outer joins?
To combine rows from two or more tables based on a related column, allowing for more flexible query results.
p.9
Working with Multiple Tables
What is the significance of using NULLs in operations and comparisons?
NULLs represent missing or unknown data, affecting query results and logic.
p.9
Inserting, Updating, and Deleting Records
How can you delete specific records from a table?
By using the DELETE statement with a WHERE clause to specify conditions.
p.13
Advanced SQL Techniques
What is the purpose of the UNPIVOT operator in SQL Server?
To unpivot a cross-tab report.
How can you return random records in DB2?
Use the RAND function in conjunction with ORDER BY and FETCH.
What SQL statement can be used in MySQL to return five random records?
SELECT ename, job FROM emp ORDER BY RAND() LIMIT 5.
