The UNION and UNION ALL operators allow us to combine results, but there’s no guarantee that each set of results uses the same data types. So what data types are returned?…

SQL tips and tricks.

https://news.ycombinator.com/item?id=41643651

The page "Falsehoods Programmers Believe About Names" covers some of the ways names are hard to deal with in programming. This post will ignore most of those complexities, and deal with the problem of matching up loose user input to a database of names.

SQL queries don't start with SELECT

In a non-image format, the order is:

FROM/JOIN and all the ON conditions
WHERE
GROUP BY
HAVING
SELECT (including window functions)
ORDER BY
LIMIT

The SQL Server green index suggestion is great for the where clause, but doesn't take into account sort by, group by, etc.

Until I came to read Schwern’s little gem, hinting to turn AutoCommit off. That hit the nail so good that one single hit sufficed.

Text::CSV_XS is extremely fast, using that to handle the CSV should take care of that side of the performance problem.

There should be no need for special bulk insert code to make DBD::SQLite performant. An insert statement with bind parameters is very fast. The main trick is to turn off AutoCommit in DBI and do all the inserts in a single transaction.

https://stackoverflow.com/questions/15331791/dbicsv-implementation-based-on-sqlite/15337369#15337369

By: Steffen Müller, Yves Orton, Damian Gryski, Chris Veenboer, Rafaël Garcia-Suarez, Ævar Arnfjörð Bjarmason As with many things in computing, serialization of data structures is a game of…

https://github.com/Sereal/Sereal

https://medium.com/learning-sql/sql-window-functions-explained-through-4-real-world-examples-91db9972b6bc

https://modern-sql.com/caniuse/qualify

Using a window function, we could add in a new column total_order_amount which represents the total order amount per customer. We could simply write:

SELECT
  date,
  customer,
  order_amount, 
  SUM(order_amount) OVER(PARTITION BY customer) AS total_order_amount
FROM orders
ORDER BY date

the window function SUM(order_amount) OVER(PARTITION BY customer) effectively partitioned our table into different “windows” (one for each customer) and then calculated the total_order_amount for each of these windows. All of this was achieved without using a GROUP BY aggregation, allowing us to retain the same number of rows.

The only time you need to consider a client-server setup is:

• Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients.
• If your machine is extremely write busy, like accepting thousand upon thousands of simultaneous write requests every second, then you also need a client-server setup because a client-server database is specifically build to handle that.
• If you're working with very big datasets, like in the terabytes size. A client-server approach is better suited for large datasets because the database will split files up into smaller files whereas SQLite only works with a single file.

This article will show different SQL timestamp functions available with examples

Using a CTE
The second solution involves using a CTE to select all the rows that we want to delete and then deleting everything from the CTE. Let’s have a look at what that code looks like…

WITH ToDelete
AS
(SELECT TOP 10 id, first_name, last_name, email, gender, DOB
FROM people
WHERE gender = 'Female'
ORDER BY DOB ASC)

DELETE FROM ToDelete

Stored procedures are one more powerful object we have at our disposal. They can help us handle many tasks, and improve performance and security.

DROP PROCEDURE IF EXISTS p_customer_all;
GO
CREATE PROCEDURE p_customer_all
-- procedure returns all rows from the customer table
AS BEGIN
SELECT *
FROM customer;
END;

In this article we look at how to generate random dates in SQL Server to build a sample dataset along with code and examples.

SELECT DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @StartDate, @EndDate)),@StartDate) AS 'SalesDate';

CHECKSUM(NEWID()) is the salt for the rand() function

NEWID() generates a random unique ID for a row. Ordering by NEWID() will randomly order query results. Adding a TOP statement will return a finite number of random rows. For example, to get 10 random rows from a table:

SELECT TOP 10 * 
FROM tablename 
WHERE condition
ORDER BY NEWID()

Litestream is an open-source project that makes SQLite tenable for full-stack applications through the power of ✨replication✨. If you can set up a SQLite database, you can get Litestream working in less than 10 minutes.

5. Modularize Code with Common Table Expressions

CTEs create a temporary table that allow you to “query a query.”
https://www.essentialsql.com/introduction-common-table-expressions-ctes/

The advantage of a CTE (your second statement) over a derived table is that they can reference themselves and be used for recursion.

with avg_female_salary as (
   SELECT       AVG(salary) as avg_salary
   FROM       salaries
   WHERE gender = "Female"
)
SELECT name,  salary
FROM People
WHERE  salary >= (SELECT avg_salary FROM avg_female_salary)

7. Simplify Code using Temporary Functions

https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions

CREATE TEMPORARY FUNCTION seniority(tenure INT64) AS (
   CASE WHEN tenure < 1 THEN "analyst"
        WHEN tenure BETWEEN 1 and 3 THEN "associate"
        WHEN tenure BETWEEN 3 and 5 THEN "senior"
        WHEN tenure > 5 THEN "vp"
        ELSE "n/a"
   END
);
SELECT name
       , seniority(tenure) as seniority
FROM employees

PerlCon 2019 Rīga — Day 2 — 8 August 2019

https://perlcon.eu/talk/56

~20:00 - Window functions
~33:45 - CTE
~43:00 - Recursive CTE

This talk shows how to use SQL Window Functions (ISO SQL:2008) and how to use Common Table Expressions (CTE, ISO SQL:1999).

Using these two techniques, you can often avoid munging the query result afterwards. This potentially saves roundtrips between the program and the database.

The presentation assumes an intermediate familiarity with SQL. To get the most of the presentation, youshpuld be familiar with SELECT and JOIN. The Perl level is suitable for the beginner, but there will be very little Perl.

What are Window Functions?

Window functions are functions that perform calculations over multiple rows of the dataset while maintaining the same rows and row count of the original table.

4. CTE > Sub-queries

In most situations you want to use a CTE (common table expression) instead of a sub-query.

5. Using SUM and CASE WHEN Together

select 
     sum(case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end) as allergies_burl
   , sum(case when allergies = 'Penicillin' and city = 'Oakville' then 1 else 0 end)   as allergies_oak

from patients

Start where clause with true

where 1=1
and ...
and ...
and...

7. Don’t Forget About Window Functions

select
    p.*
  , MAX(weight) over (partition by city) as maxwt_by_city
 from patients p

Window functions are a great way to keep all of the data rows and then append another column with important aggregate details.

In this article we look at things you should know to help tune and optimize your SQL Server queries.

• Always Check the Execution Plan
• Find the Operators that have Highest Cost
• Look for Warnings
• Arrows Between Operators Provide Good Insight
• Execution Plan Properties Window
• Index Suggestions

In this tip we look at a simple script you can use to rebuild all SQL Server indexes for all specified databases.

SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
--PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)