10 Sep 2020 Agnieszka Kozubek-Krycuń Creating SQL Courses: Behind the Scenes at LearnSQL.com Take a peek behind the scenes and learn our secret for creating great SQL courses! Have you ever wondered how LearnSQL.com courses are created? Are we looking at a lot of hard work or just a bit of magic? Or maybe both? Here’s a hint: we rely on proven methods to create user-friendly online courses. But it’s not easy! Read on to see the 8 steps we take when creating a new SQL course. Read more 7 Jul 2020 Agnieszka Kozubek-Krycuń What Is a SQL Dialect, and Which one Should You Learn? Updated on: September 12, 2023 SQL, standard SQL, SQL Server, MySQL, PostgreSQL, Oracle, … You’d like to learn SQL, but you feel overwhelmed with options and you don’t know where to start. We’ll explain what each of these terms mean and what this has to do with SQL dialects. Diving into the world of SQL dialects can feel like jumping into a deep pool without knowing how to swim. There's a ton of stuff to pick up! Read more 17 Apr 2020 Agnieszka Kozubek-Krycuń Why Take the “SQL Basics” Course at LearnSQL.com LearnSQL.com’s interactive “SQL Basics” course teaches the foundations of SQL. Discover why we built this online course, our philosophy behind it, and what it contains! What Is SQL, and Why Should You Learn It? SQL is a language used to talk to databases, computer programs that hold data and can process it efficiently. SQL can select data from a database and perform various computations on the data, be they simple or complex. Read more 2 Apr 2020 Agnieszka Kozubek-Krycuń How to Get the First Day of the Week in SQL Server This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server. This interactive course will teach you how to analyze customer lifecycle in a SQL database in over 70 hands-on exercises. You will learn how to find out compare registration rates between periods of time, compute conversion rates in SQL, compare customer cohorts, analyze customer activity and customer churn over time. This course is essential for anyone doing customer analysis in their job. Read more 2 Apr 2020 Agnieszka Kozubek-Krycuń How to Group Data by Week in SQL Server This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server. In this course, we showed you how you can analyze the customer lifecycle (customer acquisition, conversion, activity, retention, and churn) with SQL. We discussed customer registration cohorts, or groups of customers who registered during the same period (e.g. same week, same month). Analyzing customer registration cohorts lets you see registration trends and relate registration cohorts with marketing campaigns. Read more 27 Mar 2020 Agnieszka Kozubek-Krycuń Why Window Functions Are Not Allowed in WHERE Clauses Window functions are extremely powerful, but there are a lot of nuances and little details you need to learn about using window functions in order to use them correctly and efficiently. A fairly common question SQL users have is why window functions are not allowed in WHERE. TL;DR: You can use window functions in SELECT and ORDER BY. You cannot use window functions in WHERE, GROUP BY, or HAVING. Read more 31 Dec 2014 Agnieszka Kozubek-Krycuń MySQL Collations List Collations To list all collations available in MySQL, use SHOW COLLATION; +-------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | | dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 | | dec8_bin | dec8 | 69 | | Yes | 1 | | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 | | cp850_bin | cp850 | 80 | | Yes | 1 | | hp8_english_ci | hp8 | 6 | Yes | Yes | 1 | | hp8_bin | hp8 | 72 | | Yes | 1 | | koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 | | koi8r_bin | koi8r | 74 | | Yes | 1 | | latin1_german1_ci | latin1 | 5 | | Yes | 1 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | Yes | 1 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | Yes | 1 | . Read more 22 Dec 2014 Agnieszka Kozubek-Krycuń How Does a Database Sort Strings? Different languages have different alphabets and different ways to order letters within those alphabets. For example, a Polish character Ł comes right after L and before M. In Swedish, a letter Å comes almost at the end, right after Z. In French diacritics marks have no impact on the alphabetical order, so the letters À, Á and  are treated as the letter A when sorting strings. A collation is a set of rules that defines how to compare and sort character strings. Read more 22 May 2014 Agnieszka Kozubek-Krycuń DELETE RETURNING clause in PostgreSQL The standard DELETE statement in SQL returns the number of deleted rows. DELETE FROM external_data; DELETE 10 In PostgreSQL you can make DELETE statement return something else. You can return all rows that have been deleted. DELETE FROM external_data RETURNING *; id | creation_date | user_id | data ----+---------------------------+---------+---------------- 101 | 2014-05-06 13:10:45.09484 | 23 | 'Some text' 102 | 2014-06-10 22:23:12.12045 | 25 | 'Some other text' (2 rows) DELETE 2 Read more 21 May 2014 Agnieszka Kozubek-Krycuń How to Select the First Row in a Group? Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON. Let's say I want to select one weather report for each location. location time report Ottawa 2014-05-15 8:00 sunny 2014-05-15 11:00 cloudy 2014-05-15 15:00 rainy Warsaw 2014-05-15 8:00 overcast 2014-05-15 11:00 sunny 2014-05-15 15:00 rainy SELECT DISTINCT ON (location) location, time, report FROM weather_reports; Read more «« « 1 2 3 » »»