In the Database Management System (DBMS), a query language, known as QL, is used to specify database retrievals, such as SELECT in SQL. In this blog, I will show you how to query data with SELECT in so many ways and how to relate different relations at one time by examples.

A quick review, the SELECT statement looks like this

# Minimun SELECT Statement
SELECT [Attributes] FROM [Table]

# Complete SELECT Statement
SELECT [Attributes] FROM [Table] WHERE [Where conditions] GROUP BY [Groupby conditions] HAVING [Having conditions] ORDER BY [Orderby conditions]

And what we need to do is to find the shortest statement to fetch the data you want

Example relations we will use in this blog

Person

ID Name Age
1 John 22
2 Tom 16
3 Linda 22
4 Finka 18

SocialMedia

ID Name URL
1 Twitter twitter.com
2 Facebook facebook.com
3 Github github.com
4 Youtube youtube.com

Person_SocialMedia

PID SID Name
1 1 Grimfire
1 2 Grimfire
1 4 MKBD
2 2 Airworks
2 3 Airworks-777
3 1 Exbot
3 4 GTAS

Variables

# Set a person as a variable P, and select this person's ID, name and age
# Since we don't have WHERE clause, it will find all rows in the Person relation table

SELECT P.ID, P.Name, P.Age FROM Person P
# Select the data of all attributes from Person

SELECT P.* FROM Person P
ID Name Age
1 John 22
2 Tom 16
3 Linda 22
4 Finka 18
# Using WHERE condition to filter the result
# Find the ids and names of people who are elder than 18 from the Person relation

SELECT P.ID P.Name
FROM Person P
WHERE P.Age > 18
ID Name
1 John
3 Linda

Have fun

# Find names of people who have Twitter account
# For AND operations, you could think every statement after 'AND' is a filter of previous result

SELECT Name
FROM Person P, SocialMedia S, Person_SocialMedia PS
WHERE P.ID = PS.PID AND S.ID = PS.SID AND S.Name = 'Twitter'

# Or Nested SQL using EXISTS

SELECT Name
FROM Person P
WHERE EXISTS (
	SELECT * FROM Person_SocialMedia PS
	WHERE P.ID = PS.PID AND EXISTS (
		SELECT * FROM SocialMedia S
		WHERE S.ID = PS.SID AND S.Name = 'Twitter'
	)
)

# Or Nested SQL using IN

SELECT Name
FROM Person
WHERE PID IN (
	SELECT PID FROM Person_SocialMedia
	WHERE SID IN (
		SELECT SID FROM SocialMedia
		WHERE Name = 'Twitter'
	)
) AND ID = PID
Name
John
Linda
# List name pairs for people with same age

SELECT P1.Name, P2.Name
FROM Person P1, Person P2
WHERE P1.Age = P2.Age AND P1.ID != P2.ID
Name Name
John Linda
# List names of people who have both Twitter and Facebook accounts

SELECT Name
FROM Person P, SocialMedia S1, SocialMedia S2, Person_SocialMedia PS1, Person_SocialMedia PS2
WHERE P.ID = PS1.PID AND P.ID = PS2.PID AND PS1.SID = S1.ID AND PS2.SID = S2.ID AND S1.Name = 'Twitter' AND S2.Name = 'Facebook'
Name
John
# Aggregate functions
# Find number of person and their average age

SELECT COUNT(*) C, AVG(Age) Avg
FROM Person
C Avg
4 19.5
# Find ID of person and the number of social accounts they have
# "Group By PID" will count the number of acounts by counting how many times the same ID/PID appeared in the Person_SocialMedia

SELECT PID, COUNT(*) C
FROM Person_SocialMedia
GROUP BY PID
ID C
1 3
2 2
3 2
# Find names of people who have at least 1 social account
# Natural Join is an operation of JOIN. It makes an intersection between two relations based on the common attributes(ID-PID)

SELECT Name
FROM Person NATURAL JOIN Person_SocialMedia
GROUP BY Name
HAVING COUNT(*) >= 1
Name
John
Tom
Linda

Do more practices as much as you can. Enjoy it