Learning SQL For Oracle

Learn how to retrieve all data from a table using the select statement, including select star from book, and explore the book and publisher tables.

Learn to retrieve specific columns from tables using select, displaying book titles and prices, and publisher names with their cities and states, plus a year-to-date sales exercise.

Explore restricting rows with the where clause in Oracle, using price greater than 20, the star for all columns, and text comparisons with a single equals, noting case sensitivity.

Learn to use mathematical and text expressions in select lists to compute revenue, rename columns with aliases, and embed literal text using quotes.

Sort query results by column names using the order by clause, placed at the end of the select statement, including multi-column sorting with ascending or descending options.

Develop Oracle sql queries using apex to display books with title, price, and publication date, compute revenue, and filter by advances greater than zero, then order results by book title.

Display books with title, price, publication date, revenue, and publisher, city; filter where advance > 0 and order by title.

Learn what a primary key is, how it guarantees a unique, non-empty row with examples like ISBN and author ID, and its relation to foreign keys.

Use comparison operators in the where clause to filter by values, dates, and numbers, including not equal. Practice with author and book table examples and order results by title.

Explore how to build multi-column criteria in the where clause with and, or, and not in sql, including Cindy Hunter, California or Utah, and the importance of parentheses.

Learn to handle null values in SQL using is null and is not null, distinguish null from blank or zero, and query missing data examples.

Sort Oracle SQL results by column position from the select list, using order by 1 and 1,2 with descending sorts, while recognizing that position-based ordering is cautioned for programming languages.

Learn to craft one SQL statement per question in Oracle for assignment 3, using Oracle APEX or other tools, to filter books by advance and price, and identify unpublished titles.

Demonstrate crafting a single sql statement for assignment three, using where, or, and parentheses to filter books by advance and price, and by null publication dates.

Learn how a foreign key links to a primary key to connect data across tables. See examples with book and publisher tables, and learn to retrieve related data with joins.

Learn how to perform inner joins using the using clause to connect publisher and book tables by pub_id, and understand when to qualify columns and apply where conditions.

Learn how to join tables with the on clause, use table aliases to simplify multi-table joins, and explore using vs on with publisher–book and zip code examples.

Explore how joins and the order of processing in sql determine results, from joining publisher and book on publisher_id to filtering, sorting, and selecting name, title, price in advance.

Join books, publishers, and authors in Oracle apex; select sunshine publishers' and teko titles sold over 5000, and Oakland authors linked to publishers in the same state.

Construct an SQL query that joins publisher and book tables to show titles from sunshine publishers or all teko books with year-to-date sales over 5000, using aliases and conditions.

Master aggregate functions in SQL, including sum, average, min, max, and count, with null handling and the rule against mixing detail with summaries.

Explore count functions in sql, including count(column_name) that counts only non-null values, and count(*) that counts all rows, then apply where clauses and joins to count results across tables.

Master min and max functions in SQL for Oracle, returning the lowest or highest values in a column across all rows or a restricted set for numbers, text, and dates.

Learn to compute sums and averages with SQL, summing numeric column values across all rows or those filtered by a where clause, ignoring nulls, then dividing to obtain the average.

Learn how to concatenate data in Oracle using the double pipe operator to join multiple fields into a single result column, with practical examples in Oracle apex.

Explore how aggregates are processed in sql by guiding you through an example that computes the average price and year-to-date sales from joined book and publisher data, with where filtering.

Practice writing a single select statement using a join to report the average advance, total books sold, and published books for Sunshine publishers and Teko books.

Craft a single select statement with a join between book and publisher to show average advance, total books sold, and total books published for Sunshine Publishers and all Ecko books.

Learn how one-to-many and many-to-many relationships differ, and how an intersection table with a composite primary key and foreign keys links authors to books while reducing redundancy.

Explore implementing many-to-many relationships using an intersection table between author and book, with primary and foreign keys, and learn how royalties and attributes fit the relationship.

Learn to join three tables with the using clause across book, book author, and author, preserving relationship order, and apply a where clause, aggregates, and royalty share in results.

Master joining multiple tables with the on clause and when to use the using clause. Practice matching city and state between authors and publishers with table aliases and conditions.

Eliminate duplicate rows when joining tables by using select distinct or Oracle's unique. Distinct applies to the whole row, and you can count distinct values with aggregates.

Sort results using column aliases such as revenue, and alias profit as price times year to date sales minus the advance, sorting by profit while excluding nulls.

Master assignment 6 with Oracle SQL: list books and California authors, format results as quoted titles with publishers, and display authors' SSNs where city and state match a publisher.

Solve Oracle SQL assignments with a single select, join books and authors on ISBN and author id, filter California authors, and format results to include titles and publishers.

Explore how the group by clause creates per publisher and per type aggregates, using where and order by, handling nulls, and understanding distinct-like grouping and Oracle error behavior.

Learn to use the having clause to restrict groups after a group by, with aggregates like average price, and understand how nulls are handled.

Master the having clause with compound conditions by filtering grouped data using and/or, applying aggregates like average price and total sales across single or multi-column groups.

Master SQL with Oracle by completing assignment 7 using Apex or Toad, writing a select statement to show book type averages above $30 and author sales between 5,000 and 10,000.

Deliver 7 solutions in learning sql for oracle, showing group by and having to filter book types by average price, and joining author, book, and book_author for sales by type.

Learn to use the in operator in a select statement to filter results by a list of values. Replace long or conditions with a concise where clause and explore subqueries.

Master the between operator with and to define inclusive ranges in Oracle SQL, using price between 20 and 40 and date between June 15, 2012 and June 30, 2012.

Learn to use the like operator for pattern matching in sql, using percent and underscore wildcards to filter last names and other fields, with practical Oracle examples and exercises.

Explain outer joins in Oracle SQL by using left and right joins to combine authors, editors, and books through an association table, handling unmatched rows with nulls.

Explore assignment 8 by crafting SQL select statements in Oracle: list books with their authors starting with t, filter editors by salary, and find titles containing stressed.

Write select queries to join books and authors with left outer joins, filter titles starting with t, apply between for editor salaries, and find titles containing stressed case-insensitively.

Explore how subqueries work with the in operator to filter authors by states from publisher, using inner and outer queries, distinct lists, and nested selects.

Master subqueries with the in operator in Oracle SQL by building inner queries that return lists for the outer select, ensuring type-safe comparisons.

Master subqueries using a comparison operator to return a single value from an inner query. Apply aggregates like average price in where and having clauses.

Compare subqueries and joins in sql, learn when to use in with lists, harness aggregate value comparisons, and understand when self-joins or nested subqueries offer advantages.

Compare subqueries and self-joins to retrieve a manager's details from the editor table. Explore recursive relationships and foreign keys in a real-world employee example, and practice coding both approaches.

answer assignment 9 using sequel sql tool to create subqueries: books by sunshine publishers and teko sold over 5000; advances below average; authors in states where teko books are based.

Explore assignment 9 solutions using subqueries and joins to filter books by Sunshine Publishers or Alltech books with year-to-date sales over 5000, and list authors by state using average advance.

Explore how data is organized in a relational database to reduce redundancy, using entity-relationship modeling, normalization, and foreign key relationships between authors and books.

Explore entity relationship modeling by identifying entities and attributes, defining tables, and mapping one-to-many relationships using real estate examples and ER diagrams.

Learn how normalization reduces data redundancy by transforming real estate data into first, second, and third normal forms, using primary keys and foreign keys.

Learn normalization guidelines from first through third normal form, breaking data into smallest parts, using primary and foreign keys, and designing non-redundant tables for scalable sql queries.

Create an entity relationship diagram using crow's foot notation and apply normalization to model company attributes, identify entities, and express tables with primary and foreign keys in relational notation.

Walks through solving assignment 10 by modeling employees, departments, and jobs with an entity-relationship diagram and applying normalization guidelines using primary keys, foreign keys, and relational notation.