5.Which One Would GreenMart Actually Run?

"Skip the first 20 matching orders, then give me the next 10" — SQLite, PostgreSQL, and MySQL all understand LIMIT ... OFFSET ... exactly the same way. This exact query runs unchanged on any of the three.

SELECT * FROM Orders
ORDER BY OrderDate DESC
LIMIT 10 OFFSET 20;

MySQL also accepts an older shorthand, LIMIT 20, 10 (offset first, then count) — the same result, different word order, and easy to misread.

SQL Server has no LIMIT keyword at all — copy the query above in verbatim and it fails with a syntax error. The same "skip 20, take 10" idea needs OFFSET ... ROWS FETCH NEXT ... ROWS ONLY instead, and a plain ORDER BY without an OFFSET clause can't be paginated this way at all.

SELECT * FROM Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SQL Server's older, more common habit is SELECT TOP 10 ... — but TOP has no built-in way to skip rows, so it only ever answers "give me the first N," never real page 2, 3, 4.

Oracle 12c and later accept the exact same OFFSET ... FETCH NEXT ... syntax as SQL Server — the two converged on the same standard-SQL wording, even though neither supports LIMIT.

SELECT * FROM Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Plenty of Oracle code still running today predates 12c, and instead filters on the pseudo-column ROWNUM (SELECT * FROM (SELECT * FROM Orders ORDER BY OrderDate DESC) WHERE ROWNUM <= 30) — and that alone only gives the first 30 rows; skipping the first 20 needs a second nested layer on top.

SQLite, PostgreSQL, and Oracle all use || to glue text together — this exact expression runs unchanged on any of the three, and it's the actual SQL-standard operator for concatenation.

SELECT FirstName || ' ' || LastName AS FullName FROM Customers;

MySQL treats || as logical OR by default, not concatenation — the exact query above doesn't error out, it just silently evaluates a completely different boolean expression instead. MySQL needs its own CONCAT() function to glue text together.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Customers;

This is one of the most common "it worked everywhere else" surprises in real migrations: no syntax error, no warning — just a query that quietly means something else.

SQL Server concatenates with the + operator — the same one used for addition. That means concatenating a text column with a NULL value returns NULL for the whole expression, silently swallowing everything else in it.

SELECT FirstName + ' ' + LastName AS FullName FROM Customers;

SQL Server 2012+ also has a real CONCAT() function that treats NULL as an empty string instead, avoiding that exact surprise.

In SQLite, an INTEGER PRIMARY KEY column is automatically an alias for the table's internal rowid, and already behaves like an auto-incrementing key with no extra keyword at all.

CREATE TABLE Orders (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL
);

Adding AUTOINCREMENT (INTEGER PRIMARY KEY AUTOINCREMENT) guarantees an ID is never reused even after its row is deleted — rarely necessary, but occasionally load-bearing.

MySQL needs the explicit AUTO_INCREMENT keyword on the column. Leave it off, and MySQL doesn't fill OrderID in automatically — every INSERT has to supply its own value by hand.

CREATE TABLE Orders (
  OrderID INT AUTO_INCREMENT PRIMARY KEY,
  CustomerID INT NOT NULL
);

PostgreSQL and Oracle (12c and later) both settled on the same standard-SQL wording: GENERATED ALWAYS AS IDENTITY. Older code for either one often uses a different shortcut instead — PostgreSQL's SERIAL, or Oracle's separate CREATE SEQUENCE plus a trigger that reads the next value on every insert.

CREATE TABLE Orders (
  OrderID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  CustomerID INT NOT NULL
);

SQL Server uses IDENTITY(seed, increment) — here, start counting at 1 and increase by 1 with every new row.

CREATE TABLE Orders (
  OrderID INT IDENTITY(1,1) PRIMARY KEY,
  CustomerID INT NOT NULL
);

"Order" and "Date" are both SQL reserved words — using them as plain column names would collide with the language itself. SQLite, PostgreSQL, and Oracle all resolve that the same way: wrap the name in double quotes.

SELECT "Order", "Date" FROM Orders;

MySQL uses backticks for the exact same purpose. The double-quoted version above isn't a syntax error in MySQL by default — it's usually just interpreted as a plain text string instead of a column name, which fails or returns the wrong thing without ever looking like a typo.

SELECT `Order`, `Date` FROM Orders;

SQL Server's own convention is square brackets. Three engines, three different characters, for the exact same idea.

SELECT [Order], [Date] FROM Orders;

PostgreSQL has a real BOOLEAN type, with genuine TRUE/FALSE literals — the column can only ever hold one of exactly two values (or NULL, if allowed).

CREATE TABLE Orders (
  OrderID INTEGER PRIMARY KEY,
  IsPaid BOOLEAN NOT NULL DEFAULT FALSE
);

SELECT * FROM Orders WHERE IsPaid = TRUE;

MySQL accepts the same BOOLEAN keyword and TRUE/FALSE literals — but only as a convenience. Underneath, BOOLEAN is just an alias for TINYINT(1), and the column actually stores a plain 1 or 0.

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  IsPaid BOOLEAN NOT NULL DEFAULT FALSE
);

SQL Server has no BOOLEAN type or TRUE/FALSE literal at all. The standard substitute is BIT, a column that only ever holds 0 or 1 — the query above has to compare against 1, not TRUE.

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  IsPaid BIT NOT NULL DEFAULT 0
);

SELECT * FROM Orders WHERE IsPaid = 1;

Oracle SQL has no boolean type at all — not even a BIT-style stand-in. The common workaround is a NUMBER(1) column with a CHECK constraint enforcing only two valid values, representing true/false by convention rather than by an actual type.

CREATE TABLE Orders (
  OrderID NUMBER PRIMARY KEY,
  IsPaid NUMBER(1) NOT NULL CHECK (IsPaid IN (0, 1))
);

PostgreSQL's LIKE is case-sensitive by default — 'priya' and 'Priya' are treated as different text entirely. Reaching for a case-insensitive match means using ILIKE instead, a PostgreSQL-specific extension with no SQL-standard equivalent.

SELECT * FROM Customers WHERE Name LIKE 'priya';
-- 0 rows: PostgreSQL's LIKE is case-SENSITIVE by default

SELECT * FROM Customers WHERE Name ILIKE 'priya';
-- matches 'Priya': ILIKE is PostgreSQL's case-insensitive version

Act 5's Query Optimization chapter showed the opposite default: SQLite's own LIKE is case-insensitive out of the box, which is exactly why a trailing-wildcard LIKE couldn't safely use a sorted index there. Same keyword, opposite default behavior, depending entirely on which engine is running it.

"Insert this row, but if a customer with this ID already exists, update their balance instead" — SQLite and PostgreSQL both handle it with ON CONFLICT ... DO UPDATE SET ..., referencing the would-be-inserted row through the special excluded alias.

INSERT INTO Customers (CustomerID, Name, Balance)
VALUES (501, 'Priya', 140)
ON CONFLICT (CustomerID) DO UPDATE SET Balance = excluded.Balance;

MySQL's own wording for the same idea is ON DUPLICATE KEY UPDATE, referencing the would-be-inserted value through the VALUES() function instead of an excluded alias.

INSERT INTO Customers (CustomerID, Name, Balance)
VALUES (501, 'Priya', 140)
ON DUPLICATE KEY UPDATE Balance = VALUES(Balance);

MySQL 8.0.20+ actually discourages VALUES() here in favor of a row alias (... AS new ON DUPLICATE KEY UPDATE Balance = new.Balance) — both still work, but it's a real sign of how even one engine's own syntax keeps shifting release to release.

SQL Server and Oracle have no ON CONFLICT or ON DUPLICATE KEY shortcut at all — the same "insert or update" idea needs a full MERGE statement: match against a source row, then spell out exactly what happens on a match and what happens without one.

MERGE INTO Customers AS target
USING (SELECT 501 AS CustomerID, 'Priya' AS Name, 140 AS Balance) AS source
ON target.CustomerID = source.CustomerID
WHEN MATCHED THEN UPDATE SET Balance = source.Balance
WHEN NOT MATCHED THEN INSERT (CustomerID, Name, Balance) VALUES (source.CustomerID, source.Name, source.Balance);

Same result as the two snippets above, in roughly four times the words — a good example of how a shortcut convenience in one engine can simply not exist in another.

CURRENT_TIMESTAMP is part of the actual SQL standard, and SQLite, PostgreSQL, and MySQL all honor it, returning the current date and time with no engine-specific syntax at all.

SELECT CURRENT_TIMESTAMP;

Each also has its own shorter, non-standard alias for the same thing: PostgreSQL and MySQL both accept NOW(), and SQLite accepts datetime('now').

SQL Server and Oracle each use their own non-standard function name instead — GETDATE() and SYSDATE respectively — and neither one honors CURRENT_TIMESTAMP the same way the three engines above do.

-- SQL Server:
SELECT GETDATE();

-- Oracle:
SELECT SYSDATE FROM DUAL;

Oracle's DUAL is a real quirk worth knowing by name: Oracle requires every SELECT to have a FROM clause, even one with no real table behind it, so DUAL exists purely as a built-in one-row dummy table to make queries like this syntactically valid.