DEV Community: imrinzzzz

A really good and comprehensive list of open source softwares!

imrinzzzz — Wed, 23 Jul 2025 18:54:28 +0000

Emmanuel Mumba

Jul 22 '25

12 Open Source Alternatives to Popular Software (For Developers)

#opensource #programming #developer

241

Comments 51

6 min read

Sending email using SMTP via Gmail with OpenSSL

imrinzzzz — Wed, 25 Sep 2024 14:51:18 +0000

Hi~ It's been a long time. So many things have changed over the years, but recently, I was tasked to send an email with an HTML body and an attachment. Sounds simple enough until you have no idea how to test your "raw email" and you are tired of making changes, opening a PR, merging to master, deploying it to staging before you can test if your little raw email works. I mean AWS SES isn't very friendly for local test after all.

TDLR: how to send raw email via Gmail using OpenSSL

Some technical background

Introducing OpenSSL

OpenSSL is an open source cryptographic toolkit that facilitates secure communications between endpoints on a network. The toolkit includes three core components: the libcrypto library, the libssl library and a command-line utility for performing cryptographic tasks.^[1]

It's helpful with a lot of things, but we're going to use the s_client command. It implements a generic SSL/TLS client which connects to a remote host using SSL/TLS.^[2]

Installing OpenSSL

(P.S. this is kinda oversimplifying the process and it might not always work for you)

Linux (with apt, but generally installed by default)

sudo apt install openssl

Mac (with brew)

brew install openssl

Tutorial fr

ok, so here's the part you came for.

connect to gmail openssl socket

openssl s_client -connect smtp.gmail.com:465 -crlf -ign_eof

now say hi to the server

HELO smtp.gmail.com

then authenticate yourself

auth login

You'll get 334 VXNlcm5hbWU6 which basically is "Username: " in base64. Now you'll type in your username, aka gmail address, but encoded in base64.

Then it'll return 334 UGFzc3dvcmQ6 which is "Password: " in base64, so just type your account password. But if you have 2fa on (which you should btw), you can use a password from app password.

An app password is a 16-digit passcode that gives a less secure app or device permission to access your Google Account. App passwords can only be used with accounts that have 2-Step Verification turned on.

now to the sending email part, type

MAIL FROM: <email@address.com>

press enter. (If the sender isn't permitted to send an email, you'll get an error.)

rcpt to: <also_email@address.com>

then enter. (If the recipient isn't valid, you guessed it, you get an error.)

DATA

And voilà! we can start sending email now! To show you an example of what I kinda drafted, this is what I sent.

Subject: test raw email
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=25b40e96420e3083793e9792f0ef83d50ba5a07f5d413e3eab5002dfdb3d

--25b40e96420e3083793e9792f0ef83d50ba5a07f5d413e3eab5002dfdb3d
Content-Type: multipart/alternative; boundary=sub-25b40e96420e3083793e9792f0ef83d50ba5a07f5d413e3eab5002dfdb3d

--sub-25b40e96420e3083793e9792f0ef83d50ba5a07f5d413e3eab5002dfdb3d
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html>
<html lang="en">
<head></head>
<body>
<h1>Hello!</h1>
<p>Please see the attached file for a list of customers to contact.</p>
</body>
</html>


--sub-25b40e96420e3083793e9792f0ef83d50ba5a07f5d413e3eab5002dfdb3d--

--25b40e96420e3083793e9792f0ef83d50ba5a07f5d413e3eab5002dfdb3d
Content-Type: application/pdf; name="some_pdf.pdf"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="some_pdf.pdf"

this-should-be-some-pdf-encoded-in-base64

--25b40e96420e3083793e9792f0ef83d50ba5a07f5d413e3eab5002dfdb3d--

**Mind you, the newline and -- thingy are sooo important. I spent several days scratching my head as to why it just wouldn't work because of them.

then if you're content and want to send your email, simply enter, type . and it's done!
you can repeat from MAIL FROM again and again until you're done with ~~your life~~ testing. To exit the openssl connection, you can just simply wait until it kicks you out or you can type

quit

Closing and sources

Of course, I didn't just know this. I looked up several places and these are my saviours: an answer from stackexchange, an answer from stackoverflow, like who'd have guessed, right? (shout out to the comments under this answer too!), and yet another answer from stackoverflow.

Also, thank www.base64decode.org for providing a noble service of base64 encoding/decoding.

Annnnd, that is all for this post. Hope this helps! (I know it will help me again in the future lol)

Using Prettier with Prisma.io (pre-commit config)

imrinzzzz — Thu, 26 Aug 2021 05:06:27 +0000

Cover photo by Anton Ivanov on Unsplash

If you're not familiar with prisma.io, it's an awesome ORM for Node.js. And in case if you don't know what an ORM (aka object-relational mapping) is, check out this stackoverflow thread for a comprehensive answer 😉.

Get started

Ok, let's get started. In one project of mine, I used node.js to develop a backend application for my app. I also used postgresql (which was why this article was created). In order for my app to communicate with the database, I used prima as an ORM.

Now, the problem was that I want the pre-commit config to check the format of **.prisma file(s). Luckily, someone made a prettier plugin for us.

First, add a dependency to your project

yarn add -D prettier-plugin-prisma

To format the **.prisma file using CLI, run

yarn prettier --write "**/*.prisma"

If you use vscode, you can edit the setting in settings.json to format the **.prisma on save

"editor.formatOnSave": true, 
"[typescript]": {
    "editor.defaultFormatter": "esbenp.prettier-vscode"
},
"[prisma]": {
    "editor.defaultFormatter": "Prisma.prisma"
},

Lastly, here's how my .pre-commit-config.yaml file looks like:

- repo: https://github.com/pre-commit/mirrors-prettier
  rev: v2.3.2 # Use the sha or tag you want to point at
  hooks:
    - id: prettier
      additional_dependencies:
        - prettier@2.1.2
        - 'prettier-plugin-prisma@2.29.1'

- repo: https://github.com/pre-commit/pre-commit-hooks
  rev: v3.4.0
  hooks:
  - id: trailing-whitespace
  - id: check-merge-conflict
  - id: check-yaml
  - id: end-of-file-fixer
  - id: no-commit-to-branch
    args: [-b, main, -b, production, -b, staging]

Easy setup PostgreSQL on MacOS

imrinzzzz — Tue, 24 Aug 2021 14:30:21 +0000

Lately, I've been working on a personal project of mine, and this one particular project uses postgresql, so I'm going to go over my setup of postgresql (particularly on MacOS) in this post. :D

Installing postgresql

I used homebrew to install postgresql on my machine.

brew install postgresql

Run postgres -V to check the version (and also if it's installed).
To get postgresql up and running, run

brew services start postgres
# or
pg_ctl -D /usr/local/var/postgres start

Note: pg_ctl is a utility for starting, stopping, or restarting the PostgreSQL backend server that comes with postgresql

Creating user and database

Postgresql installed with homebrew automatically creates a user with the same name as your username with no password assigned. This user has a SUPERUSER role. However, it's not really good practice to use this user. Hence, we're creating a new one.

Note: there are other ways besides doing it via psql, but I'm not covering that here 🥺

First of all, we're going to run psql. We need to specify which database we're running from. Since there's a pre-created database called postgres, we're going to use that.

psql -d postgres

Let's create a user (or aka role in postgresql term)

# replace <_> with your own thing
CREATE USER <user> WITH ENCRYPTED PASSWORD '<password>';
# check if your user was created
SELECT usename FROM pg_user;  # will show a table of user
# or run a psql meta command
\du

Let's give our user a role (or they'll just be crippled). For more info on role (or if there are other role attributes besides CREATEDB), check this great article from prisma.

ALTER ROLE <user> CREATEDB;

Next, let's create a database. Note that you can grant different types of access (besides ALL PRIVILEGES that was used here). For more info, check this awesome article from TablePlus or check the official document.

# again, replace <_> with yours
CREATE DATABASE <db_name>;
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user>;
# list all databases to see if ours was created
\list

Use our newly created user and database

To do this, you can...

Switch user and database inside psql session

\c <db_name> <user>

Or you can stop current psql session and start it using new user and database

\q  # to quit
psql -d <db_name> -U <user>

Final note and refs

And that's it, y'all. It wasn't hard, but it can be confusing for the first time. (Yes, I'm talking from my (in)experience here 🥲)

Cover Photo by Shubham Dhage on Unsplash
ref1 | ref2

Basic DB Using SQL Server (Part 7) - Set Operations in SQL

imrinzzzz — Thu, 14 May 2020 15:50:13 +0000

Basic DB Using SQL Server (Part 7) - Set Operations in SQL

Hello again :) This was written by my lovely friend! She's new to the community, but she's one of the most incredible people I've ever met!

ampereFollow

/ampere111

This 7^th post will be about Set Operations. The topics we're covering today are...

Meaning of UNION and UNION ALL
Meaning of EXCEPT and INTERSECT
Union Compatible
Using APPLY operator in MS-SQL Server

More information about SET operation

1) Meaning of `UNION` and `UNION ALL`

-- Q0 Find Customer Numbers and Names --
SELECT CustomerNum, CustomerName FROM Customer

-- Q1 UNION --
SELECT CustomerNum, CustomerName FROM Customer UNION 
SELECT CustomerNum, CustomerName FROM Customer

UNION combines the results of two or more queries into a distinct single result set

include all the rows that belong to all queries
put lines from queries after each other
remove the duplicates

Difference between UNION and JOIN

- `JOIN` makes a certesian product and subset it

-- Q2 UNION ALL --
SELECT CustomerNum, CustomerName FROM Customer UNION ALL 
SELECT CustomerNum, CustomerName FROM Customer

-- Q3 UNION ALL with ORDER BY
SELECT CustomerNum, CustomerName FROM Customer UNION ALL 
SELECT CustomerNum, CustomerName FROM Customer
ORDER BY CustomerNum

UNION ALL combines the two or more row sets and keeps duplicates

Order of UNION ALL matters

-- Q4 Add Condition
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;

-- Q5 be Q4 UNION ALL Q0
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500 
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer

-- Q6 be Q0 UNION ALL Q4
SELECT CustomerNum, CustomerName FROM Customer
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500

UNION keeps only "distinct" - order doesn't matter

relationship
- inclusive: the result will be the same
- exclusive: the result will be sorted in ascending order

-- Q7 be Q4 UNION Q0
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500
UNION
SELECT CustomerNum, CustomerName FROM Customer

-- Q8 be Q0 UNION Q4
SELECT CustomerNum, CustomerName FROM Customer
UNION
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500

Since Q4 and Q0 have inclusive relationship, these two queries will have the same result
- Q0 includes the result of Q4

-- Q9 be Q0 WHERE Balance > 10,000
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 10000

-- Q10 be Q9 UNION ALL Q4
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 10000
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;

-- Q11 be Q9 UNION Q4
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 10000
UNION
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;

Since Q9 and Q4 have exclusive relationship, these two queries have different result
- UNION ALL: order matters
- UNION: result will be sorted in ascending order

-- Q12 be Q0 WHERE Balance > 5000;
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 5000

-- Q13 be Q12 UNION ALL Q4
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 5000
UNION ALL
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;

-- Q14
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance > 5000
UNION
SELECT CustomerNum, CustomerName FROM Customer
WHERE Balance BETWEEN 3000 AND 5500;

CusNum 408 from Q4 include in Q12
- UNION ALL: 408 appears twice
- UNION: 408 appears once

2) Meaning of `EXCEPT` and `INTERSECT`

EXCEPT returns distinct rows from the left input that are NOT output by the right input query

INTERSECT retirns distinct rows that are output by both the left and right input queries operator.

-- Q15 
SELECT CustomerNum FROM Orders

-- Q16
SELECT CustomerNum FROM Customer

-- Q17 be Q16 Customer EXCEPT 15 Order
SELECT CustomerNum FROM Customer
EXCEPT
SELECT CustomerNum FROM Orders

-- Q18 be Q16 Customer INTERSECT Q15 Order
SELECT CustomerNum FROM Customer
INTERSECT
SELECT CustomerNum FROM Orders

-- Q19 be Q15 Order EXCEPT Q16 Customer
SELECT CustomerNum FROM Orders
EXCEPT
SELECT CustomerNum FROM Customer

-- Q20 be Q15 Order INTERSECT Q16 Customer
SELECT CustomerNum FROM Orders
INTERSECT
SELECT CustomerNum FROM Customer

Q17 Customers who does NOT order any product
Q18 Customers who order a product
Q19 There is NO Order without Customer
Q20 Product that is ordered by Customers

3) UNION Compatibility

Basic rules for combining the result sets of two or more queries that use UNION, UNION ALL, EXCEPT, INTERSECT > 1) The number and order of the columns must be the SAME in all queries > 2) The data types must be compatible

4) APPLY operator

APPLY operator allows user to invoke a table-valued function for each row returned by an outer table expression (left input) of a query.
- Table-valued function: Right input
- Outer table expression: Left input
The Right input is evaluated for each row from the Left input
Final Output
- Rows produced by Apply operator are combined
- Columns is the set om the Left input followed by the list columns returned by the Right input
CROSS APPLY acts as INNER JOIN
- return only rows from the Outer table the produce a result set from the table-valued function
OUTTER APPLY acts as OUTER JOIN
- return both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function

CROSS APPLY (INNER JOIN)

 -- Q25 INNER JOIN
 SELECT * FROM Customer
 INNER JOIN Orders
 ON Customer.CustomerNum = Orders.CustomerNum

 -- Q26 CROSS APPLY
SELECT * FROM Customer
CROSS APPLY
(
    SELECT *
    FROM Orders
    WHERE Customer.CustomerNum = Orders.CustomerNum
)AS T

SELECT * FROM Customer
SELECT * FROM Orders

OUTTER APPLY (OUTTER JOIN)

-- Q27 LEFT OUTTER JOIN
SELECT * FROM Customer
LEFT OUTER JOIN Orders
ON Customer.CustomerNum = Orders.CustomerNum

-- Q28 OUTER APPLY
SELECT * FROM Customer
OUTER APPLY(
    SELECT *
    FROM Orders
    WHERE Customer.CustomerNum = Orders.CustomerNum
) AS T

Note: `CROSS APPLY` / `OUTER APPLY` is NOT always equivalent to `INNER JOIN` / `OUTTER JOIN`

-- Q29 CROSS APPLY
SELECT CustomerNum, CustomerName, T.TotalNumberOfOrders FROM Customer
CROSS APPLY
(
    SELECT COUNT(*) AS TotalNumberOfOrders
    FROM Orders
    WHERE Customer.CustomerNum = Orders.CustomerNum
)AS T

-- Q29 LEFT OUTTER JOIN
SELECT c.CustomerNum, CustomerName, COUNT(o.CustomerNum) AS TotalNumberOfOrders FROM Customer c
LEFT OUTER JOIN Orders o
ON c.CustomerNum = o.CustomerNum
GROUP BY C.CustomerNum, CustomerName

-- Q30 CROSS APPLY with INNER JOIN
SELECT c.CustomerNum,c.CustomerName, T.SumPrice
FROM Customer c
CROSS APPLY (
    SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
    FROM Orders
    INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
    WHERE Orders.CustomerNum = c.CustomerNum
    HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T

-- Q31 OUTER APPLY with INNER JOIN
SELECT c.CustomerNum,c.CustomerName, T.SumPrice
FROM Customer c
OUTER APPLY (
    SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
    FROM Orders
    INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
    WHERE Orders.CustomerNum = c.CustomerNum
    HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T

-- Q32 CROSS APPLY twice
SELECT c.CustomerNum,c.CustomerName, T1.OrderNum, T2.SumPrice
FROM Customer c
CROSS APPLY
(
    SELECT COUNT(o.OrderNum) AS OrderNum
    FROM Orders o
    WHERE c.CustomerNum = o.CustomerNum
)AS T1
CROSS APPLY (
    SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
    FROM Orders
    INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
    WHERE Orders.CustomerNum = c.CustomerNum
    HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T2

-- Q33 OUTTER APPLY twice
SELECT c.CustomerNum,c.CustomerName, T1.OrderNum, T2.SumPrice
FROM Customer c
Outer APPLY
(
    SELECT COUNT(o.OrderNum) AS OrderNum
    FROM Orders o
    WHERE c.CustomerNum = o.CustomerNum
)AS T1
Outer APPLY (
    SELECT SUM(NumOrdered * QuotedPrice) AS SumPrice
    FROM Orders
    INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum
    WHERE Orders.CustomerNum = c.CustomerNum
    HAVING SUM(NumOrdered * QuotedPrice) > 0
) AS T2

Basic DB Using SQL Server (Part 6)

imrinzzzz — Thu, 14 May 2020 11:57:46 +0000

Hey there! We have come to the 6^th post of the series! This post will be about Aggregations. The topics we're covering today are...

Basic aggregation functions
Aggregation with WHERE clause
Aggregation with GROUP BY clause
HAVING VS WHERE
ORDER BY clause

Before we explore each topic, let's take a look at what Aggregation function means. Aggregation function basically means a function where values of multiple rows are grouped together to form a single summary value. For example, you have a salary column and you have 100 rows with each person's salary. You want to know the average salary of this table. That is when we use the aggregation function.

1) Basic Aggregation functions

Some of the aggregation functions that are widely used include COUNT(), SUM(), AVG(), MIN(), and MAX().

We are going to use the table Part from the database called PremierProducts to demonstrate each of the function. The table looks like this.

`COUNT()`

COUNT() returns the number of rows that match the criteria.

-- How to use COUNT --
SELECT COUNT(*) FROM Table_name
-- or --
SELECT COUNT(Column_name) FROM Table_name
-- or --
SELECT COUNT(DISTINCT Column_name) FROM Table_name

If you want to count every row in the table, you can write something like

SELECT COUNT(*) FROM Part;
-- or --
SELECT COUNT(Class) FROM Part; 
-- It doesn't matter which column we choose 
-- because every row in the column will be counted

The result is 10 because we have a total of 10 rows.

However, if you want to count only the unique value among duplicates, you add the keyword DISTINCT.

SELECT COUNT(DISTINCT Class) FROM Part;
-- using different column may yield a different reult!

The result is now 3 because there are only 3 unique Class in the table.

`SUM()`

SUM() returns total number (sum) of a numeric column.

For example, we want to find the total price of every part in the table

SELECT SUM(Price) FROM Part;

We will get 4504.74 as a result.

`AVG()`

AVG() returns the average value of a numeric column.

For example, we want to find the average price of every part in the table

SELECT AVG(Price) FROM Part;

We will get 450.474 as a result.

`MIN()`

MIN() returns the smallest value of a column.

We can use MIN() to find the minimum price of every part in the table

SELECT MIN(Price) FROM Part;

We will get 24.95 as a result.

However, we can use MIN() in a non-numeric column as well. For example,

SELECT MIN(Class) FROM Part;

This will return AP as the resul because the letter A comes before other letters.

`MAX()`

MAX() returns the largest value of a column.

This works similarly to MIN() but instead of returning the minimum value, it returns the maximum value.

SELECT MAX(Price) FROM Part; -- the result is 1390.00
SELECT MAX(Class) FROM Part; -- the result is SG

Tips~

When we run the aggregation function (like above), the result will displayed as a new, untitled column. Like this..

However, we can rename that column with alias AS! Like so

SELECT AVG(Price) AS average_price FROM Part        -- without space
SELECT AVG(Price) AS 'average price' FROM Part      -- with space

And the results are..

2) Aggregation with `WHERE` clause

Like any other SQL queries, you can use WHERE clause with aggregation functions to specify a condition.

SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price
FROM Part;
-- versus --
SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price
FROM Part
WHERE Class='AP';

The results of both queries are

See the difference? The second query only includes the row that meets the criteria (class='AP').

3) Aggregation with `GROUP BY` clause

GROUP BY is useful when we want to learn about each group's characteristics. Remember the last query where we ask for the number of element, the total price, and the average price from Part? What if we use GROUP BY with it?

SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price, Class
FROM Part
GROUP BY Class

As you can see below, instead of getting the information on the whole table, we get information of each Class.

`HAVING` clause

Sometimes we want to use filter the results of aggregate functions with GROUP BY clause, this is when HAVING comes into play.

Now, let's use the previous query

SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price, Class
FROM Part
GROUP BY Class
HAVING Class <> 'SG'    -- <> is the same as !=

The result of this query is the table below. The row which has Class = 'SG' isn't included in the table.

We can also use aggregate functions with HAVING clause as well.

SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price, Class
FROM Part
GROUP BY Class
HAVING AVG(Price) > 200     -- like so

The result will return a table that includes row that has an average price (of each class) larger than 200.

4) `HAVING` VS `WHERE`

The main difference between WHERE and HAVING clause is that WHERE clause is used to filter the row BEFORE grouping while HAVING is used to filter the row AFTER grouping.

This means we cannot use HAVING without GROUP BY clause. At the same time, we cannot use aggregate function with WHERE clause.

5) `ORDER BY` clause

This is an optional clause to display the results of your query in a sorted mamner. We will use types of arguments which are ASC and DESC.

For example,

SELECT * FROM Part 
ORDER BY Class      -- you can add ASC or leave it blank like this

The result will return a table that is sorted by Class in an ascending order.

If we want to display the same result but in descending order, we can add DESC like so...

SELECT * FROM Part 
ORDER BY Class DESC

The result will be like so...

Moreover, we can even sort using more than one column! Let's sort the table above using Class and then Price in an ascending order.

SELECT * FROM Part 
ORDER BY Class, Price ASC   -- you can leave out the ASC argument

The result of the above query is the table below.

EOF !!

We have come to the end of this post! The script to create PremierProducts database can be found here. There's an exercise if you want to test your knowledge here and as usual the SQL queries to the exercise can be found here.

I'll see you in the next post!

Basic DB Using SQL Server (Part 4) - SQL JOIN

imrinzzzz — Sun, 16 Feb 2020 15:26:30 +0000

Hey there! This is another post about SQL server. This post is about SQL JOIN. Today's topic includes:

INNER JOIN
OUTER JOIN
1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL JOIN
CROSS JOIN
(bonus) UNION

JOIN is an SQL command that lets you connect information from 2 or more tables into one result through key attributes (depends on how user tell JOIN to put the data together.)

1) INNER JOIN

It is the most common kind of JOIN which returns only the records where there are matches.

SELECT <select_list>
FROM <first_table> INNER JOIN <second_table>
ON <join_conditions>

-- or you can just use 'JOIN' (automatically means INNER JOIN) --
SELECT <select_list>
FROM <first_table> JOIN <second_table>
ON <join_conditions>

Table Films:

Table Actors:

If we run the script below

SELECT *
FROM Films f INNER JOIN Actors a
-- the 'f' and 'a' is an alias of the tables to shorten the name --
ON f.filmId = a.filmId

we will get

NOTE: Alias is a temporary name we call our table, so we don't have to write the full table name. It is optional.

2) OUTER JOIN

There are 3 kinds of OUTER JOIN; LEFT JOIN, RIGHT JOIN, and FULL JOIN.

-- syntax --
SELECT <select_list>
FROM <first_table>
<LEFT | RIGHT | FULL> JOIN
<second_table>
ON <join_condition>

i. LEFT OUTER JOIN

A LEFT JOIN includes all the information from the table on the LEFT plus the matched information from the right table.

Using the same Films and Actors tables, if we run the script below

SELECT * 
FROM Actors a LEFT INNER JOIN Films f
ON a.filmId = f.filmId

we will get

As you can see, in the table Actors, we have a record with filmId = 5. However, in the table Films, we don't have a record with filmId = 5. The result of joining these 2 using LEFT JOIN, we get NULL for the empty record from Actors.

ii. RIGHT OUTER JOIN

A RIGHT JOIN includes all the information from the table on the RIGHT plus the matched information from the left table. Basically, it is a reverse of LEFT JOIN. You can use these 2 interchangeably with the correct order of the selected tables.

Again, using the same Films and Actors tables, if we run the script below

SELECT * 
FROM Films f RIGHT JOIN Actors a
ON a.filmId = f.filmId

we will get

As you can see, I swapped the order between Films and Actors tables, so all information from the Actors table are included (just like what we did in the LEFT JOIN.)

If we were to swap the order of the 2 tables just like the script below

SELECT * 
FROM Actors a RIGHT JOIN Films f
ON a.filmId = f.filmId

we will get a different result like this

Instead of getting Humphrey Bogart, we get NULL cells and filmId = 3's Amelie which only exists in the Films table.

iii. FULL OUTER JOIN

FULL JOIN include all the information from both sides of the table. It can be said that the result after doing LEFT JOIN and RIGHT JOIN in the same as doing FULL JOIN.

If we run the script below

SELECT * 
FROM Actors a FULL JOIN Films f
ON a.filmId = f.filmId

we will get

3) CROSS JOIN

The result of CROSS JOIN is a Cartesian product of all records on both sides of the JOIN.

If we run the script below using the same tables

SELECT * 
FROM Actors, Films 

-- or you can use the alternative script below --
SELECT * 
FROM Actors CROSS JOIN Films

we will get

Since we have 3 records from the Films table and 4 records from the Actors table, using CROSS JOIN, we get 3*4 or 12 records as a result.

4) UNION

UNION is a special operator used to append data from one query onto the end of another query.

JOIN combines information horizontally (adding more columns)
UNION combines data vertically (adding more rows)

However, there are a few key points about UNION to note.

All the UNION queries must have the same number of columns in the SELECT list.
The data types of each column in a query must be implicitly compatible with the data type of the same column from another query.
The default return option for UNION is DISTINCT rather than ALL.

Let's look at some examples

-- first query --
SELECT filmId, filmName
FROM Films
UNION 
-- second query --
SELECT filmId, firstName
FROM Actors

We are combining 2 queries together using UNION. The result of this is.

However, if we run the script below

-- first query --
SELECT filmId, filmName
FROM Films
UNION ALL
-- second query --
SELECT filmId, firstName
FROM Actors

we will get

The result will contain dulicate(s) if we use UNION ALL instead of just UNION.

EOF !

And that's the end of part 4! There is a script file that you can used to create the AdventureWorks2014 database! Some exercise to test your knowledge can be found here and the SQL queries to the exercise can be found here!

Hope you have a good day~

Basic DB Using SQL Server (Part 3) - retrieve data using SQL

imrinzzzz — Mon, 27 Jan 2020 15:05:38 +0000

In this post, we are going to learn how to retrieve data in a relational database by using Structured Query Language (SQL).

Topics that we are going to cover:

SELECT statement
Search condition (WHERE)
Computed fields
Intro to Aggregation functions
Sorting function
Intro to Joining

In the queries below, we are going to use tables from PremierProduct database. The SQL script to create PremiereProduct database, create tables and relations, and add records to the tables can be found here.

1) SELECT statement

The SELECT statement is used to select data from a database. The data returned is displayed as a table¹.

SELECT select_list [INTO new_table]
FROM table_source [WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]

note: the query expressions inside the [ ] are optional. We are going to go over these expressions later.

The table that we are going to use is called Customer and it looks like this...

-- List all information from the table Customer --
SELECT * FROM Customer

-- List the number, name, balance, and credit limit from the table Customer --
SELECT CustomerNum, CustomerName, Balance, CreditLine FROM Customer

Distinct

Sometimes, we just want to list distinct values from a column because a column can have a lot of duplicate values, for example, the City column contains duplicate city names.

-- list distinct city from the table Customer --
SELECT DISTINCT City FROM Customer

2) Search condition

We are going to use WHERE clause to filter records and get only the ones that fulfill our condition(s).

-- find the Name of Customer number 148 --
SELECT CustomerName FROM Customer 
WHERE CustomerNum=148

Another table that we are going to use is called Part and it looks like this.

AND / OR conditions

-- List the description, on-hand value, and warehouse number for parts 
-- that have more than 10 units on-hand AND are located in warehouse 3. --
SELECT Description, OnHand, Warehouse FROM Part
WHERE OnHand>10 AND Warehouse=3

-- List the description, on-hand value, and warehouse number for parts 
-- that have more than 10 units on-hand OR are located in warehouse 3. --
SELECT Description, OnHand, Warehouse FROM Part
WHERE OnHand>10 OR Warehouse=3

-- List the Part number, Description, and Price where the price is between $100 and $400. --
SELECT PartNum, Description, Price FROM Part
WHERE Price>100 AND Price<400

NOT

To select records that do NOT have a certain property can be written in 3 ways.

-- list all information of all the customers who are not from the city Grove --
SELECT * FROM Customer
WHERE City!='Grove'

SELECT * FROM Customer
WHERE City<>'Grove'

SELECT * FROM Customer
WHERE NOT City='Grove'

3) Computed fields

A Computed field is a field that is the result of a calculating using one or more existing fields.

Arithmetic operators (e.g. +, -, *, /) and parentheses can be used.

-- list the number, name, and available credit for all customers --
SELECT CustomerNum, CustomerName, (CreditLine - Balance) AS Available_Credit FROM Customer

As you can see, we don't have Available_credit column, so we have to make one. The available credit is the subtraction of CreditLine and Balance.

We rename the newly made column using AS command and the column is called Available_credit. This is called Alias.

4) Intro to Aggregation Functions

Aggregation Functions are functions where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning².

The commonly used SQL aggregate functions are AVG, COUNT, MIN, MAX, and SUM.

COUNT

Count is a function that returns the number of rows that matches a specified criteria³.

-- how many customers does sales rep 35 represent? --
SELECT COUNT(CustomerNum) AS Total_customers FROM Customer
WHERE RepNum=35

The AS command is optional. We use COUNT on one of the column (in the example, we use CustomerNum column) to count how many rows fit the condition.

AVERAGE

AVG function returns the average value of a numeric column³.

-- what is the average balance of all customers of sales rep 35? --
SELECT AVG(Balance) AS Average_balance FROM Customer
WHERE RepNum=35

SUM

-- find the total balance of all the customers living in Grove --
SELECT SUM(Balance) AS total_balance FROM Customer
WHERE City='Grove'

Grouping

Grouping means creating groups of records that share some common characteristics. We can use Aggregation functions together with grouping. The calculations will affect groups of records.

-- what is the average balance for all customers of each sales rep? --
SELECT RepNum, AVG(Balance) AS Average_balance FROM Customer
GROUP BY RepNum

5) Sorting function

ORDER function helps us list the records in query results in a particular way. Sorting records can be performed on more than one field.

-- list all customers and sort the output alphabetically by customer name --
SELECT * FROM Customer
ORDER BY CustomerName ASC

You can add ASC or DESC to specify whether you want to sort in ascending order (ASC) or descending order (DESC). If nothing is specified, the default is ASC.

-- list all customers and sort the output by sales rep num
-- within the sales rep num, sort the output by customer name --
SELECT * FROM Customer
ORDER BY RepNum, CustomerName

6) Intro to Joining

Sometimes, it is necessary to join the tables based on matching fields to query data from different tables.

Another table that will be used is called Rep and it looks like this.

-- list customer's number and name, along with the number, last and first name of each customer's sale rep --
SELECT Customer.CustomerNum, Customer.CustomerName, Rep.RepNum, Rep.LastName, Rep.FirstName
FROM Rep JOIN Customer ON Rep.RepNum = Customer.RepNum

We join Rep table with Customer table on the common column they both have; RepNum. However, when we SELECT the columns, we have to specify which table that column is from; e.g. Customer.CustomerNum.

However, specifying which table it is using the table name can be too long. We can shorten it by making a nickname for the table(s).

SELECT C.CustomerNum, C.CustomerName, R.RepNum, R.LastName, R.FirstName
-- we are making a nickname here --
-- Table Rep is called R and table Customer is called C --
FROM Rep R JOIN Customer C ON R.RepNum = C.RepNum

More examples on JOIN (with WHERE)

-- list the customer's number and name, along with the number, last and first name of the sales rep
-- for customer where credit limit is $10,000 --
SELECT C.CustomerNum, C.CustomerName, R.RepNum, R.LastName, R.FirstName
FROM Rep R JOIN Customer C ON R.RepNum = C.RepNum
WHERE C.CreditLine = 10000

Let's join more than 2 tables! The additional tables that we are going to use are Orders and OrderLine.

This is Order.

And this is OrderLine

-- For each order, list order number, order date, customer number and name
-- in addition, list the part number, description, number of units and quoted price --
SELECT O.OrderNum, O.OrderDate, C.CustomerNum, C.CustomerName, P.PartNum,
P.Description, P.OnHand AS [Number of Units], OL.QuotedPrice
FROM Customer C JOIN Orders O ON C.CustomerNum=O.CustomerNum
JOIN OrderLine OL ON OL.OrderNum=O.OrderNum
JOIN Part P ON P.PartNum=OL.PartNum

We can also use another table to come up with another query command. The table is called CurrentOrders which look like this.

-- another query command that produces the same result --
SELECT CO.OrderNum, O.OrderDate, C.CustomerNum, CO.CustomerName, P.PartNum,
CO.Description, P.OnHand AS [Number of Units], CO.QuotedPrice
FROM CurrentOrders CO JOIN Customer C ON CO.CustomerName=C.CustomerName
JOIN Orders O ON O.OrderNum=CO.OrderNum
JOIN Part P ON P.PartNum=CO.PartNum

EOF!

And so, we have come to the end of part 3! If anyone wishes to try out SQL command, there's another SQL script that will produce another database, tables, and values. The database is called ALEXAMARA and can be accessed here.

Some query problems to exercise with can be found here. There are also queries to those problems in case you want to check if the result is correct.

And I'll see you in the next part! Ciao~

Basic DB Using SQL Server (Part 1) - Basic commands!

imrinzzzz — Mon, 27 Jan 2020 14:52:24 +0000

Hello! This post will be about "Basic commands" just like what the topic said. We will cover 2 types of SQL commands; DDL commands and DML commands. Let's see what these 2 mean first...

DDL, or Data Definition Language, is used to create and/or modify the structure of a database and database objects¹. DDL commands include CREATE, ALTER, RENAME, DROP, and TRUNCATE.

DML, or Data Manipulation Language, is used to manipulate data in the server ². DML commands include INSERT, DELETE, UPDATE, LOCK, and MERGE.

For more information, click here.

1) DDL Commands

Now let's look at some DDL commands that we commonly use.

-- Create a database user account --
CREATE LOGIN login_name
WITH PASSWORD = 'type_password_here';
GO  -- this is optional. If the above command doesn't work, the program won't stop and keep GOing --

-- Create user and add server role --
CREATE USER user_name FOR LOGIN login_name
GO
EXEC sp_addsrvrolemember 'login_name', 'role'
GO

When we successfully enter into the server, we don't want to use sa, or system administrator, user account. Thus, we will create a new account into the server using the above commands. The role can be sysadmin.

Create a new database

CREATE DATABASE database_name COLLATE THAI_CI_AS
GO

According to this site, collation is a set of rules that tell database engine how to compare and sort the character data in SQL Server.

What we did above is creating a database and naming it whatever you put in the database_name part while also specifying some set of rules; in this case, THAI_CI_AS.

Delete an existing database

DROP DATABASE database_name
GO

-- optional step --
-- checking to see if database_name exists --
IF EXISTS(SELECT * FROM sys.databases WHERE name='database_name')
DROP DATABASE database_name -- if it exists, drop it --

Sometimes, there's an error saying cannot drop database "database_name" because it is currently in use.. In order to solve this, we will switch to another database using the following command.

USE master  -- every SQL server will have one --

2) DML Commands

After creating a database (or two), we can now create tables inside!

Create a new table

CREATE TABLE database_name.schema_name.table_name  -- usually we use dbo schema --
(
      column_name1 data_type(size),
      column_name2 data_type(size),
      ...
);

-- AN EXAMPLE --
CREATE TABLE test_db.dbo.Staff
(
      StaffId int,
      LastName varchar(255),
      FirstName varchar(255)
);

Delete an existing table

DROP TABLE database_name.schema_name.table_name 
-- or --
DROP TABLE table_name

Insert a new record into a table

INSERT INTO table_name
VALUES (value1, value2, value3);

--enter multiple records --
INSERT INTO table_name
VALUES (value1, value2, value3),
       (value1, value2, value3),
       (value1, value2, value3),
       ...
       (value1, value2, value3);

However, if your table has 4 values to insert, but you would like to insert only 3 values, there are 2 approaches to not cause an error.

-- 1) use null --
INSERT INTO table_name
VALUES(value1, value2, value3, null);

-- 2) specify which column you want to insert into --
INSERT INTO table_name(column1, column2, column3)
VALUES (value1, value2, value3);

Update an existing record

UPDATE table_name
SET column1=value1, column2=value2,...
WHERE condition;

-- for example --
-- updating all records in Staff --
UPDATE Staff
SET Address = 'Bangkok'
-- updating only StaffId = 124 --
UPDATE Staff
SET Address = 'Chiang Mai'
WHERE StaffId = 124;

Delete an existing record

DELETE FROM table_name
WHERE condition;    -- this is optional --

And that's all for this part, see you in the next part!

Basic DB Using SQL Server (Part 2) - modify columns and learn some constraints

imrinzzzz — Mon, 27 Jan 2020 14:51:54 +0000

Hello! Part 2 is out now!! Part 2 will cover 3 things:

1) Modifying columns in existing table (like adding new columns)
2) Creating primary key and foreign key
3) Creating and deleting constraints

1) Modifying columns in existing table

Showing details of columns

EXEC SP_COLUMNS table_name

This simple line of code is a command to have the details of the columns from the specified table be shown. The details of the columns are called metadata.

Add a new column

ALTER TABLE table_name
ADD column_name datatype;

-- for example --
ALTER TABLE Staff
ADD DateOfBirth VARCHAR(10);

This command lets you add another column into the existing table. You can specify the name and the data type of the new column.

Add a multiple column

ALTER TABLE table_name
ADD col_name1 datatype1, col_name2 datatype2, ...;

Of course, if you can add one more column, you can add A TON MORE columns.

Alter an existing column

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

-- for example --
ALTER TABLE Staff
ALTER COLUMN DateOfBirth DATE;

Assume that originally DateOfBirth was of another data type, so we decided to change to DATE data type instead.

However, it is important to note that if the existing table has data in it, those data or values cannot be converted to the new data type. Sometimes, this cause an error when we alter the column.

Drop an existing column

ALTER TABLE table_name
DROP COLUMN column_name;

The result of this code is as simple as the code itself. Basically, it drops a column_name column from the table_name table.

Drop multiple columns

ALTER TABLE table_name
DROP COLUMN col_name1, col_name2, ...;

If you can drop one column, you can drop a looooot of them.

2) Creating primary and foreign key

Before we proceed to the code, let's look at what actually are primary keys and foreign keys.

Primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records¹.

Primary key contains a unique value for each row of data and cannot be NULL value.

Foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.².

Create primary key (during table creation)

CREATE TABLE table_name
(
  col_name datatype NOT NULL PRIMARY KEY,
  col_name datatype,
  col_name datatype
  ...
)

-- or --
CREATE TABLE table_name
(
  col_name datatype NOT NULL,
  col_name datatype,
  col_name datatype,
  -- if you add more columns inside the brackets, 
  -- that means a primary key is made up of more than 1 columns
  CONSTRAINT constraint_name PRIMARY KEY (col_name)
  ...
)

The idea is pretty simple. When you are in the process of creating a new table (links to part 1), you add PRIMARY KEY behind one of the columns that you want as a primary key.

However, you can also make a constraint and name it, then specify which column(s) you want as your primary key. You can add more than 1 columns. This means a primary key is made up of more than 1 columns.

Once you have set the primary key, the value of that column cannot be duplicated nor can it be left as null.

Create primary key (with an existing table)

ALTER TABLE table_name
ADD PRIMARY KEY (column_name)

-- or --
ALTER TABLE table_name
-- if you add more columns inside the brackets, 
-- that means a primary key is made up of more than 1 columns
ADD CONSTRAINT constraint_name PRIMARY KEY (col_name)

With this, we can specify which column we want to set as a primary key. However, that column must be declared not NULL values or else an error may occur. To fix the NULL issue, you can type the code below.

ALTER TABLE table_name
ALTER COLUMN col_name datatype NOT NULL;

Just alter the (existing) column to be NOT NULL. And of course, there should be no duplicate value in the primary key column.

Create foreign key (during table creation)

CREATE TABLE table_name
(
  col_name datatype NOT NULL PRIMARY KEY,
  col_name datatype,
  col_name datatype FOREIGN KEY REFERENCES table2_name(table2_primary_key),
  ...
);

-- for example --
CREATE TABLE NewStaff
(
  StaffId int NOT NULL PRIMARY KEY,
  LastName varchar(50),
  FirstName varchar(50),
  StaffDeptId int FOREIGN KEY REFERNECES Department(DeptID)
);

We add a foreign key and specify which primary key will we be referencing on and from which table in the code above.

-- another method of creating a foreing key --
CREATE TABLE table_name
(
  col_name1 datatype NOT NULL,
  col_name2 datatype,
  PRIMARY KEY (col_name1),
  CONSTRAINT constraint_name FOREIGN KEY (col_name2)
  REFERENCES table2_name (table2_primary_key)
);

Create foreign key (with an existing table)

ALTER TABLE table_name
ADD FOREIGN KEY (col_name)
REFERENCES table_name_we_referenced_from(col_name_with_PK)

-- for example --
ALTER TABLE Staff
ADD FOREIGN KEY (StaffDeptID)
REFERENCES Department(DeptID)

In the above code, we add a foreign key to an existing column in the selected table, table_name or Staff. We referenced from table_name_we_referenced_from or Department table and col_name_with_PK or DeptID column.

-- another method of adding a foreing key --
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (col_name) REFERENCES table2_name (table2_primary_key);

Updating values of foreign key

Note that we cannot change the value to some other values that were not in the referenced primary key column. For example, we have these 2 tables.

-- we can do this --
UPDATE Staff
SET StaffDeptID = 1
WHERE StaffID = 126

-- but we cannot do this --
UPDATE Staff
SET StaffDeptID = 3
WHERE StaffID = 127

..simply because StaffDeptID is a foreign key referenced to DeptID from Department table and in Department table, there is a value of 1 but not 3.

Additional Options for Foreign Key

When we update the value of a primary key, but we have a foreign key that referenced to the updated primary key. That foreign key might not get updated if we don't include the option(s) below.

ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}

Usually, if we don't specify what happens ON DELETE or ON UPDATE, the default value is NO ACTION. But we can specify which action should happen if there was an update to the referenced primary key. Here's what each action means.

(We will call the data from the referenced primary key as parent data and the data of the foreign key which references to the parent data as child data.)

NO ACTION - basically means no action will be performed on the child data when parent data is updated or deleted.
CASCADE - the child data is deleted when the parent data is deleted and the child data is updated when the parent data is updated.
SET NULL - the child data is set to NULL when the parent data is updated or deleted.
SET DEFAULT - the child data is set to the default value (that we have to specify) when the parent data is updated or deleted.

Let's take a look at some examples..

-- ex1. CASCADE --
ALTER TABLE child_table
ADD CONSTRAINT constraint_name FOREIGN KEY (col_name)
REFERENCES parent_table (primary_key_col)
ON DELETE CASCADE
ON UPDATE CASCADE

-- ex2. SET NULL / DEFAULT --
ALTER TABLE child_table 
ADD CONSTRAINT constraint_name FOREIGN KEY (col_name)
REFERENCES parent_table (primary_key_col)
ON UPDATE SET DEFAULT
ON DELETE SET NULL;

-- set default value
ALTER TABLE child_table
ADD CONSTRAINT constraint_name2 DEFAULT 0 FOR col_name;

3) Creating and Deleting constraints

Before we head over to the "creating and deleting constraints" part, we will first understand what constraints mean.

Constraints are rules for the data in a table. If there is a violation of any constraints, the action is terminated. (e.g. Insert a null value into the table, but the constraint specifies 'no null value allowed'. The insert action will be terminated.)

Constraints can be specified when creating a table or modifying a table.

The commonly used constraints are as follows:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
INDEX

For more information, click here.

NOT NULL

The NOT NULL constraint enforces a column to NOT accept NULL values. You cannot insert a new record or update a record without adding a value to the column.

-- when creating table --
CREATE TABLE Table_name
(
  col_name datatype NOT NULL,
  col_name datatype NOT NULL,
  col_name datatype,
  ...
);

-- using alter table --
ALTER TABLE table_name
ALTER COLUMN col_name datatype NOT NULL

UNIQUE

The UNIQUE constraint ensures that the values in the unique column are different. A PRIMARY KEY constraint automatically has a UNIQUE constraint. The difference is that you can add only one PRIMARY KEY constraint while you can add as many UNIQUE constraints as you want.

When adding a constraint, it is fine to not add the constraint_name, however, when dropping a constraint, a constraint_name is required.

-- when creating table --
CREATE TABLE Table_name
(
  col_name datatype NOT NULL UNIQUE,
  col_name datatype UNIQUE,
  col_name datatype,
  ...
);

CREATE TABLE Table_name
(
  col_name datatype,
  col_name datatype,
  col_name datatype,
  -- can add more than 1 column inside the brackets)
  CONSTRAINT constraint_name UNIQUE (col_name)
);

-- using alter table --
ALTER TABLE table_name
ADD UNIQUE(col_name)

ALTER TABLE table_name
 -- can add more than 1 column inside the brackets)
ADD CONSTRAINT constraint_name UNIQUE (col_name)

-- drop a UNIQUE constraint --
ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;

PRIMARY KEY and FOREIGN KEY

You can check the second topic to find out more about what are PRIMARY KEY or FOREIGN KEY and how to create them.

-- drop a PRIMARY KEY and FOREIGN KEY --
ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;

ALTER TABLE table_name
DROP CONSTRAINT foreign_key_constraint_name;

CHECK

The CHECK constraint is used to limit the value range in a column. For example, you only allow filling in an age that is greater than or equal to 18 (like the example below). If a value lower than 18 is filled in, the action will not get executed.

-- when creating table --
CREATE TABLE table_name
(
  col_name datatype,
  col_name datatype CHECK (condition)
);

-- or 
CREATE TABLE table_name
(
  col_name datatype,
  col_name datatype,
  -- defining CHECK constraint on multiple columns
  CONSTRAINT constraint_name CHECK (condition1 AND condition2)
);

-- example --
CREATE TABLE Persons
(
  ID int,
  Name varchar(255),
  Age int CHECK (Age >= 18)
);

-- using alter table --
ALTER TABLE table_name
ADD CHECK (condition);

-- or
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition1 AND condition2);

-- drop a CHECK constraint --
ALTER TABLE table_name
DROP CONSTRAINT check_constraint_name;

DEFAULT

The DEFAULT constraint is used to provide a default value for a column if no value is specified; usually it is NULL. You can check out a way to create a DEFAULT constraint here. Another way is as follows.

-- when creating table --
CREATE TABLE table_name
(
  col_name datatype,
  col_name datatype,
  col_name datatype DEFAULT value
);

-- example --
CREATE TABLE Persons
(
  ID int NOT NULL,
  Name varchar(255),
  City varchar(100) DEFAULT 'Bangkok'
);

-- using alter table --
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
DEFAULT value FOR col_name;

-- example --
ALTER TABLE Persons
ADD CONSTRAINT df_city
DEFAULT 'Bangkok' FOR City

-- drop a DEFAULT constraint --
ALTER TABLE table_name
ALTER COLUMN col_name DROP DEFAULT;

INDEX

Indexes are used to retrieve data from the database more quickly. They are only used to speed up searches/queries and not seen by the users.

-- when creating table --
CREATE INDEX index_name
ON table_name (column1, column2, ...);

-- or create a UNIQUE INDEX --
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

-- drop INDEX --
DROP INDEX table_name.index_name;

And that's all for part 2! See you in the next part!

Process Synchronization with Busy Waiting

imrinzzzz — Fri, 24 May 2019 07:27:35 +0000

HELLO :D This post is dedicated to BUSY WAITING! But first of all, we have to (or should) know what's process synchronization is first.

Process Synchronization (???)

Process synchronization means many processes share some system resources in a way that concurrent accesses to those resources cause little to no data inconsistency. But sometimes, some resources cannot be accessed at the same time or there will be some inconsistency.

For example, you and your friend want to use a printer to print your hundreds of pages report. Now, imagine you and your friends both hit print at the same time and one of you guys' reports is gone because the printer only picked up one! Ok, I know these days printers are smart, but that's because it has amazing process synchronization, but you get the picture. The printer is the shared resource and you guys are processes that want to use the resources.

Busy Waiting

That is where many algorithms and techniques come in to solve the problem where everyone wants to enter the critical region; a.k.a. an area where no more than one process should be in, so there's no error (like using shared resources).

One of those algorithms is Mutual Exclusion. The name sounds complicated, but the concept is simple; 1 process working IN the critical region, the others keep waiting. Yes, it is that simple; I'll be using this printer, you wait.

That's fine and all, but how do the other processes KNOW when they can take their turn? Well, they just keep checking to see if the process working inside the critical region is done. They've got to keep checking, and checking, and checking, and checking...... This makes them SO busy, they can do NOTHING ELSE!

And that, sire, is what we call Busy Waiting.

No one:
Literally no one:
Me: But.. ugh.. It sounds too easy
Also me: hold your Operation System

According to Wikipedia,

busy waiting is a technique in which a process repeatedly checks to see if a condition is true, such as whether keyboard input or a lock is available.

Oh, but we are far from done. *smirk* If only life WAS the-above-paragraph easy. The thing is, there are a variety of busy waiting's versions. They range from plain stupid to 'ok, fine'. In this post, I'm gonna introduce 5 of them!
*SPOILER ALERT* You're not gonna use any of these in your OS.

Before we go over them, remember that all these algorithms are here to make process synchronization works when there is a critical region. So, their main goal is to make sure mutual exclusion happens.

1) Disabling Interrupt

The first one is the plain stupid one I talked about. You see, the genius behind this might actually be thinking something like...

Which is TRUE, but that kills the whole point of PROCESS SYNCHRONIZATION! What this algorithm does is not allowing other processes to get in at all, they can just wait indefinitely until who knows when. The process inside will keep working until it's done which, of course, we don't know when! Thus, this is not the ideal algorithm.

2) Lock Variable

This one seems better because instead of not making a door, now we have a door and a lock; to be more precise, a single shared lock. When the process wants to enter the critical region, it checks whether the door is locked (test lock) where the lock is initially set as 0. If the lock = 0, then the door is not locked, the process can enter the critical region and lock the door (set it to 1). However, if the lock = 1, the door is locked, so the process has to wait until it turns into 0.

Now, this might sound promising because we have a door and a lock to prevent other processes from entering at the same time, BUT we just create another problem. The processes not only have to compete to enter the critical region (get in the room), but it has to compete for the lock! This is called race condition or a condition where 2 or more processes (in this case) have to compete for a resource which cannot be used simultaneously (I know, I introduced many weird vocabs, but bear with me. TvT).

Also, what if the process inside finishes, but doesn't give up the lock and continues using it? Well, the other processes have to keep busy waiting. What's worse is 2 processes can be inside AT THE SAME TIME, but HOW? When the first process checks and sees that the lock = 0, it will change the lock to 1, right? But before it could change to 1, process 2 comes in and check the lock, too, and finds that the lock is 0 (even though it's about to be changed into 1!). Now, both of them accidentally posses the key to enter the room and accidentally enter at the same time. AND with those, this idea is also a no.

3) TSL

The next one here is called Test and Set Lock. This one is like Lock Variable, but it is a bit better because this algorithm requires a little help from the hardware. It solves the problem where one process can read the lock's value before the first process changes the value and thus 2 processes get inside the critical region at the same time. But TSL allows one process to read and change the value of the lock in an atomic way; meaning unless it finishes reading and writing the lock, no other processes can interfere. However, it still doesn't solve a problem where only one process keeps hogging the lock to itself.

4) Strict Alternation

Ok, fine, let's get a little smarter. If they have to compete for the lock, then we will specify whose lock this is, so only the owner of the lock can unlock the door and doesn't have to compete for it. This algorithm works with only 2 processes though. But now, no one is competing and no more than specified (which is one by the way) process can stay inside. This sounds better, right? Well, yeah, it is better; a bit better.

But imagine this scenario; process A just finished using the critical region, thus process A gave the key to process B. HOWEVER, process B doesn't want to use the critical region, yet, so it doesn't use the key and the key remain its. Meanwhile, process A now wants to use the key, but it's with process B and unless B uses the key, no other processes can use it. Thus, this algorithm is also problematic and so this doesn't quite work.

5) Peterson's Solution

We're getting more sophisticated with this algorithm. We have more variables which are turn and flag. This algorithm also works with only 2 processes.

The way it works is first, let's say process A wants to use the critical region, so it sets its own flag to 1 which means process A is interested in entering the critical region, and it changes the turn to its turn (process A's turn). Then process B, which comes a little later, wants to enter the critical region, so it does the same thing; set its flag to 1 and changes the turn to its own. Since process B comes later and changes the turn after process A, the turn, instead of being process A's, it becomes process B's.

The algorithm will first check if both process A's and B's flags are 1 to see if they're both interested. Then, it will check whose turn it is. If they're both interested and the turn is process B's, then process A can enter the critical region first. This is because it knows that whoever comes later can overwrite the turn as its own, and whoever comes first will get its turn overwritten. Then process B which comes later can enter after process A is done. However, if only one process's flag is 1, then that process can just enter the critical region.

This one is great because every process will have a chance to get inside and no more than 1 process can stay inside simultaneously. Everything seems perfect and all, but is this all?

The sad truth

The thing is, no matter how perfect these algorithms are, they still create some busy waiting processes. Some processes aside from the one inside the critical region will have to wait and check and wait and check until they can enter. This wastes CPU resources because instead of actually working on other important tasks, it just waits and checks indefinitely.

So, what can we do? What we can is we have to think of other algorithms that don't require busy waiting (hint: sleep and wake algorithm). But we are not covering that in this post 😬😬, so see you in the next one! (maybe 😅)

Is programming for me?

imrinzzzz — Wed, 06 Mar 2019 14:26:26 +0000

Hi everyone.. I have this doubt last Sunday for the first time since I have started to learn programming (last year). I’m really new to this and everyone around me are all experienced in the field. For example, a friend of mine, he started programming just a year before me, but he has always loved tech stuff, so he’s really knowledgeable despite being just a beginner in coding. Meanwhile, I’m also into tech but not as invested as people around me are.

So my questions are..

Have anyone ever felt this?
What did you do when you were just a newbie?
Any advice? (;-;)

Thank you so much for taking the time to read this, and extra thanks to people who answer my questions!

DEV Community: imrinzzzz

A really good and comprehensive list of open source softwares!

12 Open Source Alternatives to Popular Software (For Developers)

Sending email using SMTP via Gmail with OpenSSL

Some technical background

Introducing OpenSSL

Installing OpenSSL

Linux (with apt, but generally installed by default)

Mac (with brew)

Tutorial fr

Closing and sources

Using Prettier with Prisma.io (pre-commit config)

Get started

Easy setup PostgreSQL on MacOS

TOC

Installing postgresql

Creating user and database

Use our newly created user and database

Final note and refs

Basic DB Using SQL Server (Part 7) - Set Operations in SQL

Basic DB Using SQL Server (Part 7) - Set Operations in SQL

ampereFollow

1) Meaning of UNION and UNION ALL

- JOIN makes a certesian product and subset it

Order of UNION ALL matters

UNION keeps only "distinct" - order doesn't matter

2) Meaning of EXCEPT and INTERSECT

3) UNION Compatibility

4) APPLY operator

- Columns is the set om the Left input followed by the list columns returned by the Right input

CROSS APPLY (INNER JOIN)

OUTTER APPLY (OUTTER JOIN)

Note: CROSS APPLY / OUTER APPLY is NOT always equivalent to INNER JOIN / OUTTER JOIN

Basic DB Using SQL Server (Part 6)

1) Basic Aggregation functions

COUNT()

SUM()

AVG()

MIN()

MAX()

Tips~

2) Aggregation with WHERE clause

3) Aggregation with GROUP BY clause

HAVING clause

4) HAVING VS WHERE

5) ORDER BY clause

EOF !!

Basic DB Using SQL Server (Part 4) - SQL JOIN

1) INNER JOIN

2) OUTER JOIN

i. LEFT OUTER JOIN

ii. RIGHT OUTER JOIN

iii. FULL OUTER JOIN

3) CROSS JOIN

4) UNION

EOF !

Basic DB Using SQL Server (Part 3) - retrieve data using SQL

1) SELECT statement

Distinct

2) Search condition

AND / OR conditions

NOT

3) Computed fields

4) Intro to Aggregation Functions

COUNT

AVERAGE

SUM

Grouping

5) Sorting function

6) Intro to Joining

EOF!

Basic DB Using SQL Server (Part 1) - Basic commands!

1) DDL Commands

Create a new database

Delete an existing database

2) DML Commands

Create a new table

Delete an existing table

Insert a new record into a table

Update an existing record

1) Meaning of `UNION` and `UNION ALL`

- `JOIN` makes a certesian product and subset it

2) Meaning of `EXCEPT` and `INTERSECT`

Note: `CROSS APPLY` / `OUTER APPLY` is NOT always equivalent to `INNER JOIN` / `OUTTER JOIN`

`COUNT()`

`SUM()`

`AVG()`

`MIN()`

`MAX()`

2) Aggregation with `WHERE` clause

3) Aggregation with `GROUP BY` clause

`HAVING` clause

4) `HAVING` VS `WHERE`

5) `ORDER BY` clause