Practical Exercises for writing SQL Queries Across Multiple Databases
DCross-Platform SQL Exercises with solutions [20 exercises with solution]
1. Writing a Query that Works in Both MySQL and PostgreSQL
Write a SQL query to retrieve all employees whose salary is greater than the average salary, ensuring compatibility with both MySQL and PostgreSQL.
2. Comparing Window Functions in SQL Server & PostgreSQL
Write a SQL query using window functions to calculate a running total of sales, and compare the syntax between SQL Server and PostgreSQL.
3. Query with LIMIT in MySQL and FETCH FIRST in SQL Server
Write a SQL query to retrieve the top 5 highest-paid employees, using LIMIT for MySQL and FETCH FIRST for SQL Server.
4. Handling String Concatenation Across Databases
Write a SQL query to concatenate first and last names into a full name column, comparing syntax between MySQL, PostgreSQL, and SQL Server.
5. Using Date Functions Across Databases
Write a SQL query to calculate the age of employees based on their birthdate, comparing syntax between MySQL, PostgreSQL, and Oracle.
6. Implementing Pagination in MySQL and SQL Server
Write a SQL query to implement pagination, using LIMIT and OFFSET in MySQL and OFFSET FETCH in SQL Server.
7. Writing a Query that uses CASE Statements Across Databases
Write a SQL query to categorize employees by salary range using CASE statements, ensuring compatibility across MySQL, PostgreSQL, and SQL Server.
8. Using Common Table Expressions (CTEs) Across Databases
Write a SQL query using a Common Table Expression (CTE) to calculate department-wise averages, ensuring compatibility across MySQL, PostgreSQL, and SQL Server.
9. Handling JSON Data in MySQL and PostgreSQL
Write a SQL query to extract data from a JSON column, comparing syntax between MySQL and PostgreSQL.
10. Using Recursive Queries Across Databases
Write a SQL query to calculate employee hierarchies using recursive queries, comparing syntax between SQL Server and PostgreSQL.
11. Using GROUP_CONCAT in MySQL & STRING_AGG in PostgreSQL
Write a SQL query to concatenate employee names by department, using GROUP_CONCAT in MySQL and STRING_AGG in PostgreSQL.
12. MERGE in SQL Server and ON DUPLICATE KEY UPDATE in MySQL
Write a SQL query to upsert data, using MERGE in SQL Server and INSERT ... ON DUPLICATE KEY UPDATE in MySQL.
13. Writing a Query that uses ROW_NUMBER() Across Databases
Write a SQL query to assign row numbers to employees within each department, ensuring compatibility across SQL Server, PostgreSQL, and Oracle.
14. Using EXPLAIN in MySQL and EXPLAIN ANALYZE in PostgreSQL
Write a SQL query to analyze query execution plans, using EXPLAIN in MySQL and EXPLAIN ANALYZE in PostgreSQL.
15. Query that uses TRUNCATE in MySQL and DELETE in SQL Server
Write a SQL query to clear all rows from a table, using TRUNCATE in MySQL and DELETE in SQL Server.
16. Using ILIKE in PostgreSQL and LIKE in MySQL/SQL Server
Write a SQL query to perform case-insensitive searches, using ILIKE in PostgreSQL and LIKE with LOWER() in MySQL/SQL Server.
17. Query Uses SEQUENCE in PostgreSQL and IDENTITY in SQL Server
Write a SQL query to create an auto-incrementing column, using SEQUENCE in PostgreSQL and IDENTITY in SQL Server.
18. Using ARRAY Data Types in PostgreSQL and JSON in MySQL
Write a SQL query to store and retrieve multiple values in a single column, using ARRAY in PostgreSQL and JSON in MySQL.
19. Query uses INTERSECT in PostgreSQL and INNER JOIN in MySQL
Write a SQL query to find common rows between two queries, using INTERSECT in PostgreSQL and INNER JOIN in MySQL.
20. Using RETURNING in PostgreSQL and OUTPUT in SQL Server
Write a SQL query to insert data and return the inserted rows, using RETURNING in PostgreSQL and OUTPUT in SQL Server.
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics