#Chapter VII of @OReillyMedia Learning SQL book - Data Generation, Manipulation, Conversion

note, this is a long chapter, so I divided it. Let's start
1. Working with string data (today's posts).
3 types of string;
- CHAR - 255 chars
- VARCHAR - 65K chars
- TEXT - see chapter II thread, can store up to 4 GB of data
2. the difference between them is only the stored char length, so keep Length restrictions in your mind. Image
Additional info, check sql_mode Image
3. Quotes
We can't use " ' " for quotes in SQL. However, there is a way to use them. Image
4. CHAR() function to get special characters such as ö,ç,ı
SELECT CHAR(128,129......) Image
5. LOCATE() function to find chars in string Image
6. STRCMP() function

Checks the position for the statements.
-1 if first statement is before the second
0 if they are the same
1 if the second statement is before the first
7. LIKE and REGEXP

Common used string expressions. finds the names ending with 'y' Image
Of course there are too many other string manipulation methods. see the documentation for these, and also leetcode and hackerrank challenges.

See you tomorrow.

• • •

Missing some Tweet in this thread? You can try to force a refresh
 

Keep Current with Orkun Tahir Aran, PhD

Orkun Tahir Aran, PhD Profile picture

Stay in touch and get notified when new unrolls are available from this author!

Read all threads

This Thread may be Removed Anytime!

PDF

Twitter may remove this content at anytime! Save it as PDF for later use!

Try unrolling a thread yourself!

how to unroll video
  1. Follow @ThreadReaderApp to mention us!

  2. From a Twitter thread mention us with a keyword "unroll"
@threadreaderapp unroll

Practice here first or read more on our help page!

More from @orc_arn

Jun 21
Chapter 8: Grouping and Aggregate functions of #LearningSQL @OReillyMedia

This thread will be a brief summary and a short one.
Let's dive in
1. GROUP BY is one of the most fundamental functions in #MySQL or any kind. It helps us to retrieve counts, distinct values and etc by certain categorical data.
An example: Image
2. If you need to filter your tables while using GROUP BY, generally you cannot use WHERE clause, instead use HAVING.

In this example we filtered the data after creating groups. If we'd have used WHERE before GROUP BY, server will raise an error. Image
Read 9 tweets
Jun 20
Let's continue with Temporal data, earlier we read strings and numeric data.
1. The major problem with dates is time zones, GMT , UTC. SQL server uses UTC. Image
2. There are various date data types in Sql;
DATE YY-MM-DD
DATETIME YY-MM-DD HH:MI:SS
TIMESTAMP same as datetime

Enter your dates as strings such as; Image
Read 8 tweets
Jun 3
Before sharing notes from Chapter IV, let's solve exercises in Chapter III ending.
1. Retrieve the actor ID, first name, and last name for all actors. Sort by last name and then by first name.
2. Retrieve the actor ID, first name, and last name for all actors whose last name equals 'WILLIAMS' or 'DAVIS'.
Read 5 tweets
Jun 2
Alright, let's get back to work. Feeling better already, and got some time to read Chapter III - Query Primer

Let's start
First, let's select data for the queries. I'll go with my Coffee Quality repo, the data is from @KaggleDatasets, you can access it there.

Now, back to Chapter.

This chapter is basically about #MySQL statements. It covers select, from, where, group by, order by and having.
2⃣ But first, it would be better to understand how SQL Server executes your queries. When we run a query, the server checks 3 things:
▶️ Permission to execute query
▶️ Permission to access database
▶️ Statement syntax
Read 14 tweets
May 31
1⃣ Today, I'll continue with Chapter II. This chapter focuses on data types and creating / populating data.

Since I was aware of the data types, I try to summarize the chapter. For who are new to #SQL should read in detail.

For statistics related treads, see @levikul09.
2⃣ #MySQL Datatypes
1. Character data:
☑️ Fixed-length : right-pinned with spaces
☑️ Variable length : not padded, space consumption varies
i.e : char(20) - fixed ; varchar(20) variable length examples.
3⃣ Character Sets are used for different languages with different letters
Returns a table with different char sets. If maxlen bigger than 1, the chars in that language are bigger than 1 bytes. Image
Read 10 tweets
May 30
1⃣Hi everyone, let's start as promised yesterday.

A quick note before starting, check @levikul09 for statistics and python related information.

These tweets are my notes from @OReillyMedia #LearningSQL book chapter 1. All the credit goes to that book.
2⃣What is a database? Why it is important in data world?

Well this is easy to answer, there are nearly 2.5 quintillion bytes of data created every day. So it is crucial that this data stored in someplace. That place is called a Database, a set of related information.
2
3⃣Today we are using Relational Database model most of the time. Before, there were Non-Relational Systems:

☑️Hierarchical Database Systems - same as Decision Trees
☑️Network Database Systems - sets of records and sets of link that defines the relationship
Read 13 tweets

Did Thread Reader help you today?

Support us! We are indie developers!


This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3/month or $30/year) and get exclusive features!

Become Premium

Don't want to be a Premium member but still want to support us?

Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal

Or Donate anonymously using crypto!

Ethereum

0xfe58350B80634f60Fa6Dc149a72b4DFbc17D341E copy

Bitcoin

3ATGMxNzCUFzxpMCHL5sWSt4DVtS8UqXpi copy

Thank you for your support!

Follow Us on Twitter!

:(