This is a collection of T-SQL queries written against the well-known BikeStores sample database (production and sales schemas), focused on practicing intermediate-to-advanced querying techniques: recursive CTEs, window functions, join variations, date logic, and dynamic SQL.
| # | Technique | Description |
|---|---|---|
| 1 | Recursive CTE | Renders a threaded forum as an indented hierarchy |
| 2 | GROUP BY / HAVING |
Finds product names that appear more than once |
| 3 | ROW_NUMBER() |
Flags duplicate product rows for cleanup |
| 4 | JOIN + DISTINCT |
Lists products that have actually been ordered |
| 5 | RIGHT JOIN (anti-join) |
Finds products that were never ordered |
| 6 | JOIN + IN |
Filters products by a set of brand names |
| 7 | UNION ALL |
Combines customers and staff into one contact list |
| 8 | LIKE |
Filters customers by email domain |
| 9 | DATEDIFF + CASE |
Finds orders that took more than 2 days to ship (or haven't shipped yet) |
| 10 | EOMONTH |
Finds orders placed on the last day of their month |
| 11 | CTE + STRING_SPLIT + ROW_NUMBER() |
Gets each selected customer's most recent order, with computed line-item totals |
| 12 | Dynamic SQL (sp_executesql) |
Builds and runs a parameterized SELECT ... ORDER BY from column-name variables |
- Restore the BikeStores sample database on a SQL Server instance.
- Open
queries.sqlin SQL Server Management Studio (or Azure Data Studio). - Run each numbered block independently — they don't depend on one another.
Built with 🗃️ by Ester-Developer