SQL Journey: Blog #8

Given:

CREATE TABLE persons (
id INTEGER PRIMARY KEY AUTOINCREMENT,
fullname TEXT,
age INTEGER);

INSERT INTO persons (fullname, age) VALUES ("Bobby McBobbyFace", "12");
INSERT INTO persons (fullname, age) VALUES ("Lucy BoBucie", "25");
INSERT INTO persons (fullname, age) VALUES ("Banana FoFanna", "14");
INSERT INTO persons (fullname, age) VALUES ("Shish Kabob", "20");
INSERT INTO persons (fullname, age) VALUES ("Fluffy Sparkles", "8");

CREATE table hobbies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person_id INTEGER,
name TEXT);

INSERT INTO hobbies (person_id, name) VALUES (1, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (1, "coding");
INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
INSERT INTO hobbies (person_id, name) VALUES (2, "coding");
INSERT INTO hobbies (person_id, name) VALUES (3, "skating");
INSERT INTO hobbies (person_id, name) VALUES (3, "rowing");
INSERT INTO hobbies (person_id, name) VALUES (3, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (4, "coding");
INSERT INTO hobbies (person_id, name) VALUES (4, "dilly-dallying");
INSERT INTO hobbies (person_id, name) VALUES (4, "meowing");

CREATE table friends (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person1_id INTEGER,
person2_id INTEGER);

INSERT INTO friends (person1_id, person2_id)
VALUES (1, 4);
INSERT INTO friends (person1_id, person2_id)
VALUES (2, 3);

Step 1

We've created a database for a friend networking site, with a table storing data on each person, a table on each person's hobbies, and a table of friend connections between the people. In this first step, use a JOIN to display a table showing people's names with their hobbies.

Code:

SELECT persons.fullname,hobbies.name FROM persons JOIN hobbies ON persons.id = hobbies.person_id;

Query results:

fullname	name
Bobby McBobbyFace	drawing
Bobby McBobbyFace	coding
Lucy BoBucie	dancing
Lucy BoBucie	coding
Banana FoFanna	skating
Banana FoFanna	rowing
Banana FoFanna	drawing
Shish Kabob	coding
Shish Kabob	dilly-dallying
Shish Kabob	meowing

***Notice that Fluffy Sparkles is not displayed in the first column because this is an (inner) JOIN not an outer join.

Step 2

Now, use another SELECT with a JOIN to show the names of each pair of friends, based on the data in the friends table.

Code:

SELECT a.fullname, b.fullname FROM friends

JOIN persons a

ON friends.person1_id = a.id

JOIN persons b

ON friends.person2_id = b.id;

Query results:

fullname	fullname
Bobby McBobbyFace	Shish Kabob
Lucy BoBucie	Banana FoFanna

Comments

Privacy Policy of ShinStats: descriptives calc

Privacy Policy Shin Nix built the ShinStats app as an Ad Supported app. This SERVICE is provided by Shin Nix at no cost and is intended for use as is. This page is used to inform visitors regarding my policies with the collection, use, and disclosure of Personal Information if anyone decided to use my Service. If you choose to use my Service, then you agree to the collection and use of information in relation to this policy. The Personal Information that I collect is used for providing and improving the Service. I will not use or share your information with anyone except as described in this Privacy Policy. The terms used in this Privacy Policy have the same meanings as in our Terms and Conditions, which are accessible at ShinStats unless otherwise defined in this Privacy Policy. Information Collection and Use For a better experience, while using our Service, I may require you to provide us with certain personally identifiable information. The information that I request will be retaine...

SQL Journey: Blog #3

I have reached the end of lesson 2. The final project is entitled "Data dig" where we are given a set of interesting data sets: NASA astronauts , Superbowl results , Pokemon stats , NBA players , Top movies , Top countries by population , Solar system objects by size , Marvel characters , Furniture store sales , Earned KA badges , Winston's donut logs , Card game results , and NFL draft picks . We are to pick one of those data sets and use advanced SELECT queries to discover things about the data. What sort of questions might one have about that data, like if they were using it for an app or a business idea? Here are some ideas:What are average, max, and min values in the data? What about those numbers per category in the data (using HAVING)? What ways are there to group the data values that don’t exist yet (using CASE)? What interesting ways are there to filter the data (using AND/OR)? Basically, the above given questions will serve as a guide on what we could dig from ...

SQL Journey: Blog #5

Let's do the challenge associated with the JOIN clause entitled, "Bobby's Hobbies". Instructions: Step 1 We've created a database of people and hobbies, and each row in hobbies is related to a row in persons via the person_id column. In this first step, insert one more row in persons and then one more row in hobbies that is related to the newly inserted person. I inserted the first female character that came to mind in one of my favorite romantic sitcoms. Code: INSERT INTO persons (name, age) VALUES ("Robin Scherbattsky", 32); INSERT INTO hobbies (person_id, name) VALUES (6, "reporting"); Step 2 Now, select the 2 tables with a join so that you can see each person's name next to their hobby. Code: SELECT persons.name, hobbies.name FROM persons JOIN hobbies ON persons.id = hobbies.person_id; ***A more better code is: SELECT persons.name AS person, hobbies.name AS hobby FROM persons JOIN hobbies ON persons.id = hobbies.person_id; but ...

Shin Nix

Search This Blog