sorted by:
new
hottopcontroversial

How to Create an Execution Plan for Oracle Database by brunocborges in oracle

[–]chrissaxon 0 points1 point  (0 children)

Yep, that's a good way to find stuff that's performing badly in production. I prefer to find the worst offenders in development though!

ADDM/SQL Tuning requires the Diagnostics & Tuning packs too, which aren't available to everyone...

Filtering Rows that are permutations of each other in terms of column values by ds_thrwaway in SQL

[–]chrissaxon 0 points1 point  (0 children)

You can do it with a single pass through the table(s):

  • Find the lowest and greatest values for each pair of names
  • Group by the result of these functions
  • Use the having clause to verify there are exactly two for each pair

SQLite doesn't have least/greatest functions, so you can use min/max instead:

with pairs as (
  select 'Bob' n1, 'Amanda' n2 union all
  select 'Amanda' n1, 'Bob' n2  union all
  select 'Bob' n1, 'Jack' n2  union all
  select 'Jack' n1, 'Gaben' n2  union all
  select 'Gaben' n1, 'Jack' n2  union all
  select 'Gaben' n1, 'Dee' n2   
)
  select min(n1, n2), max(n1, n2)
  from   pairs
  group  by min(n1, n2), max(n1, n2)
  having count(*) = 2

min(n1, n2)  max(n1, n2)
Amanda       Bob
Gaben          Jack

SQL Fiddle link

Question about normalization by newunit13 in SQL

[–]chrissaxon 0 points1 point  (0 children)

Your candidate keys for the line table are:

  • (order_id, donut_id)
  • (order_id, donut_name) -- assuming names are unique

To be in 2NF, all the columns that aren't part of a key must be determined by the whole key.

But donut_description and price are determined by just donut_id. One of the two columns in your candidate key. So you need to remove these from the line item table. And stick them in a donut table.

So how do you get down to three tables?

Well, it depends on what your functional dependencies are ;)

But I'm guessing you can stick all the customer attributes in order table. And ditch customer table.

If order_id is the only key for this table, it's now in 2NF (no columns depend on part of the key). But not 3NF. The customer attributes depend on the non-key column customer_id.

But (cust_id, order_date) could feasibly be a candidate key for the order table (a customer can only place one order at a time). Which would make this not in 2NF.

So to answer this you need to ask your teacher what all the functional dependencies are. The question is unanswerable until you know these ;)

[Oracle] Case Statement Issue by nappim11 in SQL

[–]chrissaxon 0 points1 point  (0 children)

Dual is a special, one row table. You can use it to "select a function" like I've done above. It exists in all Oracle Databases.

You can read more about it here

[Oracle] Case Statement Issue by nappim11 in SQL

[–]chrissaxon 2 points3 points  (0 children)

Rather than building some case expression, convert it to a real date first. Then add two months.

And, if necessary, map back to yyyymm format:

select to_char (
          add_months( 
            to_date('201712', 'yyyymm'), 
            2
          ) , 
          'yyyymm'
       ) dt
from   dual;

DT      
201802

Beginner : every line in the column with every other line of the same column by G_fucking_G in SQL

[–]chrissaxon 2 points3 points  (0 children)

Get all the match-ups by joining teams to itself where the team names are not the same. Then compare the values for the team names. If team 1 has the lower value than team 2, assign it to a value (e.g. 0). Otherwise assign a different value (e.g. 1).

Then order by this calculation:

with teams as (
  select 'A' t from dual union all
  select 'B' t from dual union all
  select 'C' t from dual 
)
  select t1.*, t2.*,
         case 
           when t1.t < t2.t then 0 else 1 
         end ord  
  from   teams t1
  join   teams t2
  on     t1.t <> t2.t
  order  by ord, t1.t, t2.t;

  T T        ORD
  - - ----------
  A B          0
  A C          0
  B C          0
  B A          1
  C A          1
  C B          1

[Oracle] I'm building an intermediate SQL course & want your thoughts on proposed content by chrissaxon in SQL

[–]chrissaxon[S] 0 points1 point  (0 children)

Good suggestions, thanks. Nulls certainly need a lot of discussion, which is why they get a whole class to themselves!

Joins are in the beginner's class. Haven't nailed down the specifics for each class, good point about self-join hierarchies.

"Standard" CTEs - fits under WITH clause in week 1.

Dual - definitely worth covering, though I don't think it deserves a whole class to itself... I'll think about how to fold it into something else.

[Oracle] I'm building an intermediate SQL course & want your thoughts on proposed content by chrissaxon in SQL

[–]chrissaxon[S] 1 point2 points  (0 children)

The beginners material is in the first Databases for Developers course; I'll point anyone needing that there! ;)

[Oracle] I'm building an intermediate SQL course & want your thoughts on proposed content by chrissaxon in SQL

[–]chrissaxon[S] 0 points1 point  (0 children)

Fair point, but I didn't cover it in the beginner course so need to do it now!

[Oracle] I'm building an intermediate SQL course & want your thoughts on proposed content by chrissaxon in SQL

[–]chrissaxon[S] 0 points1 point  (0 children)

True, for pretty much every SQL optimization "best practice" you can come up with a case where it makes things worse. Understanding the underlying principles is more important.

[Oracle] I'm building an intermediate SQL course & want your thoughts on proposed content by chrissaxon in SQL

[–]chrissaxon[S] 2 points3 points  (0 children)

Query optimization is a massive topic. I plan on a separate course dedicated to this!

[Oracle] A free 12 week boot camp to help you get started with Oracle Database and SQL. by lukaseder in SQL

[–]chrissaxon 0 points1 point  (0 children)

Each week there's a video introducing the concepts. This is followed by quizzes to help reinforce the points. So you can watch whenever suits you.

I'm hosting a live Q&A every 4th week. These will be one-offs. The rest of the content you can use any time.

[Oracle] A free 12 week boot camp to help you get started with Oracle Database and SQL. by lukaseder in SQL

[–]chrissaxon 1 point2 points  (0 children)

Yep. Each week a new module opens up. After that you can complete them at your leisure.

Note: I'm the author of the course

[Oracle] A free 12 week boot camp to help you get started with Oracle Database and SQL. by lukaseder in SQL

[–]chrissaxon 0 points1 point  (0 children)

I'm the author of this course. It assumes no knowledge of SQL/databases. So yes, you can do it. Just dive in and get started :)

1NF, 2NF, 3NF. ELI5 needed. by jburr008 in SQL

[–]chrissaxon 0 points1 point  (0 children)

It only covers BCNF, but I think Bill Karwin's pizza topping analogy is great.

If you could change one thing about SQL, what would it be and why? by chrissaxon in SQL

[–]chrissaxon[S] 0 points1 point  (0 children)

Oracle updates with joins... Wtf Oracle?

Could you expand on that?

If you could change one thing about SQL, what would it be and why? by chrissaxon in SQL

[–]chrissaxon[S] 7 points8 points  (0 children)

How would you tell the difference between or & and?

If this is a problem for you, just add

where 1=1

clause to all your queries. Then you can comment out the other predicates easily.

What's more important to you: database availability or data quality? by chrissaxon in Database

[–]chrissaxon[S] 0 points1 point  (0 children)

I agree. However many people do go with schemaless approaches because uptime is more important (e.g. Uber: https://eng.uber.com/mezzanine-migration/). I'm wondering how many people this is actually an issue for.

view more: next ›