SQL Fundamentals

John Patrickis a computer consultant and software developer specializing in relational database technology. He teaches SQL Fundamentals and other database courses at the University of California, Berkeley Extension, and has over 20 years experience in software development at Bank of America, Chevron, Kaiser Permanente and other leading firms.

1. Storing Information in Tables.


Introduction. What is SQL? What is a Relational Database and why would you use one? Why learn SQL? What is in this book? The parts of a table. Data is stored in tables. A row represents an object and the information about it. A column represents one type of information. A cell is the smallest part of a table. Each cell should express just one thing. Primary key columns identify each row. Most tables are tall and thin. Examples of tables. An example of a table in Oracle and Access. Some database design decisions. The Lunches database. Oracle and Access. Obtaining Oracle and Access. Using Oracle. Using Access.



2. Getting Information from a Table.


The Select statement. The goal: get a few columns and rows from a table. Overview of the Select statement. The Select clause. Overview of the Select clause. Using the Select clause to get a list of columns. Using the Select clause to get all the columns. Using the Select clause to get the distinct values in one column. Using the Select clause to get the distinct values of several columns. The Where clause. Overview of the Where clause. Using an Equal condition in the Where clause. Using a Less Than condition in the Where clause. Using a Not Equal To condition in the Where clause. Using the In condition in the Where clause. Using the Between condition in the Where clause. Using the Like condition in the Where clause. Using the Is Null condition in the Where clause. Using a compound condition in the Where clause. Using a complex compound condition in the Where clause. Using NOT with IN, BETWEEN, LIKE, and IS NULL. The Order By clause. Overview of the Order By clause. Sorting the rows of several columns in ascending order. Sorting the rows of several columns with some in ascending order and others in descending order. Other techniques. Using a lookup table to find the meanings of codes. The Whole Process. The whole process—so far. Punctuation matters. Summary. Exercises.



3. Saving Your Results.


Saving your results in a new table or view. Creating a new table from the results of a Select statement. Creating a new view from the results of a Select statement. The similarities and differences between a table and a view. Deleting a table or a view. One view can be built on top of another view. Preventative deletes. Modifying the data in a table. Adding one new row to a table. Adding many new rows to a table with a Select statement. Changing data in the rows already in a table. Deleting rows from a table. Using the GUI environment to change the table data in Access. Modifying data through a view. Changing data through a view. Example of changing data through a view. Views using With Check Option. Finding information about tables and views. The Data Dictionary. How to find the names of all the tables. How to find the names of all the views. How to find the Select statement that defines a view. How to find the names of the columns in a table or view. How to find the primary key of a table. Summary. Exercises.



4. Creating Your Own Tables.


Creating tables. The Create Table command. Datatypes in Oracle and Access. Sequences. Changing tables. Adding a primary key to a table. Deleting a primary key from a table. Adding a new column to a table. Deleting a column from a table. Making other changes to tables. Tables with duplicate rows. Finding more information about tables in the Data Dictionary. Finding information about columns. Finding information about sequences. Finding information about your database objects. Finding where to find things in the Data Dictionary. Formats. Date formats. Displaying formatted dates. Entering formatted dates. Summary. Exercises.



5. Row Functions.


Introduction to row functions. Getting data directly from the beginning table. Understanding a row function. An example of a row function. Using a series of steps with a row function. Number functions. Functions on numbers. Testing row functions. Text functions. Functions on text. Combining the first and last names. Capitalization. Date functions. Function on dates. An example of a date function. Other functions. Other functions. Using functions to change the datatype. Using functions to change nulls to other values. Using functions to identify the user and the date. Starting Expression Builder in Access. Creating patterns of numbers and dates. Listing the multiples of three. Listing the prime numbers. Listing all the days of one week. Summary. Exercises.



6. Summarizing Data.


Introduction to the column functions. Summary of all the data in a column. Summary of groups of data within a column. A list of the column functions. Finding the maximum and minimum values. Using a Where clause with a column function. Finding the rows that have the maximum or minimum value. Two types of counting: counting rows and counting data. Counting to zero. Counting the number of distinct values in a column. The Sum and Average functions. The problem with addition and how to solve it. A practical example. Dividing a table into groups of rows and summarizing each group. The Group By clause. Groups formed on two or more columns. Summarized data cannot mix with non-summarized data in the same Select statement. Null groups in a single grouping column. Null groups in two or more grouping columns. An example. The Having clause. Occasions when a Where clause and a Having clause can do the same thing. Solutions to some problems. Counting the number of nulls in a column. Using DISTINCT more than once in the same query. Summary. Exercises.



7. Inner Joins.


Introduction to joins. A query can use data from several tables. The best approach is to join two tables at a time. Inner joins of two tables. A one-to-one relationship. A many-to-one relationship. A one-to-many relationship. A many-to-many relationship. Dropping unmatched rows. Dropping rows with a null in the matching column. Variations of the join condition. Using two or more matching columns. Using Between to match on a range of values. Using Greater Than in the join condition. Inner joins of three or more tables. Joining three tables with one Select statement. Joining three tables with a series of steps. The new syntax for an inner join. Writing the join condition in the From clause. Joining three tables with the new syntax in one Select statement. Joining three tables with the new syntax in a series of steps. Other issues. Inner joins are symmetric—the order in which the tables are joined does not matter. Some tables should always be joined to other tables in one particular way. A view can standardize the way tables are joined. Sometimes tables can be joined to each other in several different ways. Summary. Exercises.



8. Outer Joins and Unions.


Outer joins. Outer joins are derived from inner joins. The three types of outer joins. The left outer join. The right outer join. The full outer join. An introduction to unions. An example of a union of two tables with matching columns. A full outer join in sorted order. The symmetry of full outer joins. Unions. The difference between a union and a join. Union all. Using a literal in a union to identify the source of the data. Automatic datatype conversion in a union. Using datatype conversion functions in a union. A union of two tables with different numbers and types of columns. Applications of unions (divide and conquer). Determining whether two tables are equal. Attaching messages for warnings and errors. Overriding several rows of data in a table. Dividing data into different columns. Applying two functions to different parts of the data. Set Intersection and Set Difference in Oracle. Set Intersection. Set Difference. Summary. Exercises.



9. Self Joins, Cross Joins, and Subqueries.


Self joins. Why join a table with itself? An example of a self join. Generating the numbers from 0 to 99. Generating the numbers from 1 to 10,000. Numbering the lines of a report in Oracle and Access. Numbering the lines of a report in standard SQL. Numbering the lines of each group. Cross joins. What is a cross join? Inner joins are derived from cross joins. The properties of an inner and outer join are derived from the properties of a cross join. An error in the join condition can appear as a cross join. Subqueries. Introduction to subqueries. Subqueries that result in a list of values. Subqueries that result in a single value. Avoiding NOT IN with nulls. Many subqueries can be written with a join. Finding the differences between two tables. Summary. Exercises.



10. Advanced Queries.


The Decode and IIF functions. The Decode function in Oracle. The Immediate If (IIF) function in Access. Attaching messages to rows. Overriding several rows of a table. Dividing data into different columns. Applying two functions to different parts of the data. Using the environment in which SQL runs. Parameter queries in Oracle. Parameter queries in Access. Reporting details, subtotals, and grand totals in Oracle. Reporting details, subtotals, and grand totals in Access. Spreadsheet reports. Database format and spreadsheet format. Creating a spreadsheet report in Oracle. Creating a spreadsheet report in Access. Creating a spreadsheet report in Access using the IIF function. Creating a spreadsheet report using a union. Graphing one column against another. Summary.



Index.