Explain MySQL to MS SQL Server query syntax differences.
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
transactions
| id | user_id | amount | txn_date |
|----|---------|--------|----------|
| 1 | 1001 | 25.00 | 2023-07-01 |
| 2 | 1002 | 40.00 | 2023-07-02 |
| 3 | 1001 | 15.00 | 2023-07-03 |
| 4 | 1003 | 60.00 | 2023-07-03 |
| 5 | 1001 | 30.00 | 2023-07-04 |
##### Scenario
You are migrating analytics queries from MySQL to MS SQL Server and need to verify candidates understand subtle syntax differences, CTE structure, aliases, ordering and equivalence of filtering clauses.
##### Question
In MySQL the query
SELECT id, COUNT(*) AS transactions
FROM transactions
GROUP BY id
HAVING transactions > 10;
runs, but the same fails in MS SQL Server. Explain why and rewrite a version portable to both engines.
What does the statement "SELECT * FROM A B;" do and why can it be legal SQL?
Given ORDER BY id, ORDER BY id ASC and ORDER BY id DESC, which one produces a different ordering and why?
Provide the correct multi-CTE skeleton that defines two CTEs, A and B, then queries them.
Will the following three predicates return identical rows for a char column containing only upper-case letters? Justify.
a) col IN ('A','B','C')
b) col BETWEEN 'A' AND 'C'
c) col >= 'A' AND col <= 'C'
##### Hints
Focus on alias scope rules, default sort order, comma vs alias syntax, WITH clause commas, and lexical range comparisons.
Overview: This question evaluates a candidate's understanding of SQL dialect differences between MySQL and MS SQL Server — including alias scope, CTE structure, ordering semantics, and lexical range comparisons that affect query portability and correctness.
You are migrating analytics queries from MySQL to Microsoft SQL Server and need to verify understanding of cross‑engine SQL syntax details.
Using the transactions table below:
1) In MySQL, the query
SELECT id, COUNT(*) AS transactions
FROM transactions
GROUP BY id
HAVING transactions > 10;
runs, but it fails in SQL Server. Explain why, and rewrite a version that is portable to both MySQL and SQL Server.
2) Explain what the statement SELECT * FROM A B; does and why it can be legal SQL. Clarify how it differs from SELECT * FROM A, B;.
3) Given ORDER BY id, ORDER BY id ASC, and ORDER BY id DESC, which one produces a different ordering and why? State the default sort direction.
4) Provide the correct multi‑CTE skeleton that defines two CTEs, A and B, then queries them in a final SELECT (you may use the transactions table to illustrate).
5) For a CHAR(1) column containing only upper‑case letters, will the following predicates return identical rows? Justify your answer.
a) col IN ('A','B','C')
b) col BETWEEN 'A' AND 'C'
c) col >= 'A' AND col <= 'C'
Write a single SQL script that includes: (i) the portable version of the aggregation query from part (1), and (ii) a concrete multi‑CTE example that computes, per user_id, both the number of transactions and the total transaction amount using two CTEs A and B.
Tables
transactions(id INTEGER, user_id INTEGER, amount DECIMAL(10,2), txn_date DATE)
Hints
- In SQL Server, a HAVING clause cannot reference a SELECT-list alias; repeat the aggregate or wrap the query in an outer SELECT.
- SELECT * FROM A B means: select from table A, giving it alias B; spaces introduce aliases, while commas introduce additional tables (joins).