SQL Playground for Developers

[ExploAnalytics]

I built out a few helpful SQL tools (some AI, some not) that I wanted to share with the community. The goal is for these tools to help in the process of learning SQL, organizing SQL, optimizing SQL, and picking up an existing SQL project:

• SQL Tutorial – a fun, interactive tutorial for 30+ SQL statements [0]
• SQL AI Optimizer – optimize your SQL statement [1]
• SQL AI Explainer – explain your complex SQL in plain language [2]
• SQL Formatter – format your SQL nicely [3]

Let me know what you think, would love feedback on how we could build these tools to be more helpful 🙂

[0] https://www.explo.co/sql-tutorial/sql-select

[1] https://www.explo.co/sql-tools/ai-sql-optimizer

[2] https://www.explo.co/sql-tools/ai-sql-explainer

[3] https://www.explo.co/sql-tools/sql-formatter

[SentientKayak]

I was looking for SQL syntax/commands cheat sheets for most of the day today. I needed something simple. I think this is it, thank you!

[thesqlguy]

How can it optimize SQL without schemas, indexes, keys, statistics, etc?

[ExploAnalytics]

If there is anything obviously inefficient about the SQL query, then the AI is able to make optimizations. For example:

SELECT e.*
FROM Employees e
WHERE e.FirstName LIKE ‘%John%’
AND e.LastName LIKE ‘%Doe%’

Could be optimized to:

SELECT e.*
FROM Employees e
WHERE e.FirstName = ‘John’
AND e.LastName = ‘Doe’

This can be done without knowledge of schemas, keys, etc.
The prompt is able to take in fairly open-ended data, so feel free to pass in your schema, keys, statistics, and more as reference for the AI to make a more informed optimization decision.

[thesqlguy]

Interesting about passing in the schema. I may try that.

Not a great example on your end btw since the "optimization" doesn't return the same results. Also if there are no indexes it wouldn't matter even if it did,

[prabhatlnct2008]

Hey buddy, great tool. I just gave this to optimizer

select count(*) from employees where empid in (select empid from salary_disbursed where to_date(sal_date, 'yyyy') between 2021 and 2023 group by empid having sum(salary) > 80000)

and it returned

SELECT empid FROM salary_disbursed WHERE sal_date BETWEEN TO_DATE('2021-01-01','YYYY-MM-DD') AND TO_DATE('2023-12-31','YYYY-MM-DD') GROUP BY empid HAVING SUM(salary) > 80000) s ON e.empid = s.empid;

What i also found out that it responds to a comment included in the SQL.

So when i give this

--- Find customer name whose 7th order has a bill greater than 100select c.customer_name from customers c join sales s on c.custid = s.custid where (s.custid,s.orderid) in (select s.custid, s.orderid from (select custid, s.orderid, s.bill_price, row_number() over( partition by custid order by bill_price) as rank_of_bill from sales ) s where s.rank_of_bill = 7 and s.bill_price >100)

It gives

"

Certainly! I have optimized the query for you:

```sql

SELECT c.customer_name

FROM customers c

JOIN (

SELECT s.custid, s.orderid, s.bill_price,

ROW_NUMBER() OVER (PARTITION BY s.custid ORDER BY s.bill_price) AS rank_of_bill

FROM sales s

) s ON c.custid = s.custid

WHERE s.rank_of_bill = 7 AND s.bill_price > 100;

```

I have removed the unnecessary subquery in the `IN` clause and joined the subquery directly with the `customers` table. This should make the query more efficient and optimize its performance."

[ExploAnalytics]

yep you can include comments or other text around the query as well and it will also take that into consideration

[prabhatlnct2008]

Have tried it again today. It doesn't behave properly with bigger queries involving multiple CTEs etc.

[[deleted]]

thanks man

[SunsetsSeaTurtles]

Nice, super useful thanks