Hey guys, what’s up new ? Anyway, on this section we are focus on how is a SET used in Python. So set is a collection which is unordered and unindexed. In python, set is written with curly brackets.

Step for creating a set :

Access Items

Loop through the set and prints the values :

Check If Item Exists

Option 1 :

Option 2 :

Add Item

If you add only one item :

If you add more than 1 item :

Count Item On a Set

Example :

Remove Item

Example :

Join Two Items

Example :

Insert a Set into Other Set

Example :

On this example, I insert the items on b into a.

Okay friends, so that’s all for this section. See you on the next chapters. Thank you :)

Many times, we need to work on more than 1 tables of data. Means, table one needs to connect with other tables. In SQL, we called it joining. So, on this section, we are going to answer some questions regarding to joining table. So we have tables like below :

Bonus table :

Title table :

Employee table :

Questions :

1. Select the employee(s) that got salary
2. List the employees along with their title
3. List the employees who got bonus in 2016
4. List the employees who didn’t get bonus

Answer :

1. Select the employee(s) that got salary

Input :

Output :

2. List the employees along with their title

Input :

Output :

3. List the employees who got bonus in 2016

Input :

Output :

4. List the employees who didn’t get bonus

Input :

Output :

Okay, so this section will be end up here. See you all on the next parts. Thank you :)

Have you familiar withTuples ? I gues you have not if you are reading this writing. So on this chapter, we are going to discuss about Tuples. How are tuples used in Python. Okay, in order to make the time efficient, let’s start :

A tuple is a collection which is ordered and unchangable. In ptyhon, tuples are written in round brackets.

Example 1 :

Here, we are creating a tuple.

Example 2 :

On this, we are select the second item on m tuple.

Example 3 :

On this example, I am printing the last item on m tuple.

Example 4 :

Here, we are choose items that range from second to fourth.

Change Tuples Values

Once a tuple is created, you can not change its values. Tuples are unchangeable, or immutable. But don’t worry, there is an alternative way to do that. You can convert the value into a list, change the list, and convert the list back into a tuple.

Example :

On this example, at first m, we can not make a change so that we need to convert it into list x first. after that, we select the item (second) that we want to change.

Loop Through A Tuple

Example :

Check If Item Exists

Example :

Remove Items

Example :

Join Three Tuples

Example :

Okay guys, so that is all for this section. Thank you, I hope this is usefull for you. See you on the next chapters. :)

Hi guys, actually this part should be 16 but because yesterday I accidently erased it. So sad because already wrote it perfectly but nothing to do because it’s already gone so just let go it with happy.

Okay guys, so on this moment we are going to continue the exercise on last part. So, below is the table that we have :

Employee table :

Question :

1. How many employees on each department? Ordered it from the top to buttom
2. Take out the employees that join in 2024 and order it from the last to the old year
3. Select the employees that join in February
4. Who is the top gainer ?
5. Who is the buttom gainer ?

Okay guys, so those are the questions that we are going to answer. Let’s get started :

1. How many employees on each department? Ordered it from the top to buttom

Input :

Output :

On the output, we then can know the number of each employee on each department

2. Take out the employees that join in 2024 and order it from the last to the old year

Input :

Output :

As we can see that all information on Employee table is

3. Select the employees that join in February

Input :

Output :

As the table showed that only employees that joined in February that selected.

4. Who is the gainer ?

Input :

Output :

So the top gainer is Kristina Paseru.

5. Who is the buttom gainer ?

Input :

Output :

Okay friends, so this section will be end up here. Another exercises will be coming on the next parts. Thank you :)

Hey everyone, last part about this subject discussed about OPERATORS used in Python. On this chapter, I am going to share about COLLECTIONS in Pyhton.

In Python, there are four collection data types, that is :

• List : a collection which is ordered and can be changed. It allows duplicate members.
• Tuple : a collection which is ordered and unchangeable. It allows duplicate members.
• Set : a collection which is unordered and unindexed. Duplicate members are allowed
• Dictionary : a collection which is unordered, changeable and indexed. No duplicate members.

List

Example 1 (creating list) :

Example 2 (Access item) :

Example 3 (Negative indexing) :

Index -1 means refer to the last item, index -2 means refer to the second last item.

Change item value

Loop through a list

Check if item exists

List length

Append item

Insert item into third position

Join two lists

The list constructor

So that’s all for this section guys, see you on the next parts. Thank you :)

Hey guys, how is your day ? I hope each of us have a great day. On this section, as I mentioned in the previous post that after understanding the definition and functions of commands in SQL, we are going to move to practiceable questions as an exercises.

Related to the last section about this topic, I am going to continue to make some questions and answer that on this part. Let’s started :

1. Write a query to order the employee ranging from biggest to smallest- salary

Input :

Output :

2. Write a query to list the employee that earns biggest salary.

Input :

Output :

3. Write a query to list the employee that earns the smallest salary.

Input :

Output :

4. Write a query to list two buttom employees based on salary.

Input :

Output :

5. Write a query to sum salary paid for each department and order it from the biggest to the lowest.

Input :

Output :

Okay guys, so this section will be ended up here and to be continued on the next sections. See you and thank you :)

Hi friends, finally we meet here again after a week I did not post anything here due to work on some urgent and crucial projects. Last section about this topic, we did some exercises. We are going to do exercise as well, but for different cases. Let’s get started :

So, here we are going to learn about how to create table and how to insert data into the created tables. Besides, we are going to answer some questions regarding to the provided tables. Firstly, we are going to create some related tables as shown below :

Write a query to create table Employee :

Input :

Output :

Write a query to create a table Bonus :

Input :

Output :

Write a query to create table Title :

Input :

Output :

As we can see that three related tables already created. So now, we are going to answers some questions related to these 3 tables :

1. From the table Employee, write a query to fetch “first_name” using the alias name as Employee_name.

Input :

Output :

2. Write a query to fetch “First_name” employee table in upper case.

Input :

Output :

3. Write a query to fetch unique value of Department from Employee table.

Input :

Output :

4. Write a query to print the first four characters of first_name from Employee table.

Input :

Output :

5. Write a query to select employee_id and first name and then order the first_name from A to Z alphabet.

Input :

Output :

Okay guys, so this section will be ended up here and we are going to continue this topic on the next sections. Please stay tuned and thank you :)

Hi everyone, how is everything going? Anyway, we already at June and it seems that many of us still work from home, especially we in Indonesia where the virus started to attack us early on last March. But it does not meant that such kind of issue will anticipate us to be productive.

On this part, I am going to share to you about OPERATORS in Python. Popular operator that we know is addition (+), subtraction (-), multiplication (*), and Division (/). But hey, there are more operators beside those that already mentioned.

So OPERATORS are used to perform operations on variables and values. In python, operators are divided into some groups, that is :

• Arithmetic operators
• Assignment operators
• Comparison operators
• Logical operators
• Identity operators
• Membership operators

Let’s go thru those one by one.

Arithmetic operators

Arithmetic operators are used with numeric values to perform common mathematical operations. this include +, /, -, x. For instance :

Assignment operators

Assignment operators are used to assign values to variables. Let’s have a look for the examples below :

Example 1 :

We can see that x=x+3 is same with a +=3, where x and a have the same value.

Example 2 :

So on the example 2, we can see that y -=5 is equal to x=x-5 where x and y have the same value.

Comparison operators

Comparison operators are used to compare two values. For instance :

Example 1 :

On the example above, because a is not equal to b so that the result is False.

Example 2 :

Meanwhile,the result above is True because value a is equal to value b.

Example 3 :

The result is False because a is smaller than b.

Example 4 :

The result is True because value a is greater than b. So, actually this applicable to another comparison operators, the result is True or False is depent on the condition.

Logical operators

Logical operators are used to combine conditional statements. The examples can be seen below :

Example 1 :

The result is False because 13 is greated than 12, other words one of the statement is is not true. So both statement will be True only if both statements are true as below :

Example 2 :

It is different with OR. The result could be true if there is one statement that met the condition as shown below :

Identify Operators

Identify operators are used to compare the objects, not if they are equal, but if they are actually the same object, with the same memory location. For instance :

Example 1 :

The result is true because the value of x and y is equal.

Example 2 :

The result if True because x and y is not equal.

Membership operators

Members operators are used to test if a sequence is presented in an object. For instance :

The result is True because there 7 in sequence y.

The result is True because Mango indeed is not present in y.

Okay guys, so I will stop this part here and will see you on the next sections. Thank you :)

Hi guys, on this part and the next parts, we are going to concentrate on exercises because all the important and frequently used commands, operator, clauses in SQL already exposured together. So, let’s jump into the exercise :

Below is a table named “sql_exercise”. The table consists of id, first name, last name and department. Below are some questions that we are going to answer regarding to the provided table :

1. Based on the orginal table, write a query in SQL to display all information showed on the table

Answer

Input :

Output :

2. Based on the orginal table, write a query to show all information showed in the column first_name and department

Answer

Input :

Output :

3. Based on the orginal table, write a query to display a unique apartments that available.

Answer

Input :

Output :

4. Based on the orginal table, write a query to count how many unique department available.

Answer

Input :

Output :

5. Based on the orginal table, write a query to select all information on department 95.

Answer

Input :

Output :

Okay friends, so this section will be end up here. See you on the next exercises. Thank you :)

Thankfully for being able to write here again. Guys, so on this section I am going to share about the usage of INSERT INTO SELECT and CASE in SQL. Let’s discuss it one by one :

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. Important to remember that :

• INSERT INTO SELECT requires that data types in source and target tables match
• The existing records in the target table are unaffected

Take a look on the example below :

Input :

Output :

So, from the example above, I want to insert data into table Pelanggan1 from table Pelanggan.

the CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns null.

Let’s jump into the example to make this clear.

Input :

Output :

Okay, so that is all for this part and see you on the next sections. Thank you :)