Trying to understand SQL statements

[Exonyan1]

I recently joined a class to learn Databasing, and today im trying to figure some things out.
im trying to get the name & price from the cheapest smartphone, whats the correct SQL statement for that?

& im having alot of struggles understanding the " JOIN " statement. lets say im trying to get the name / brand name and name of the operating system, but i only want to find this information using the JOIN statement, whats the shortest way to do this?

CREATE DATABASE  IF NOT EXISTS `mobilenet` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mobilenet`;
-- MySQL dump 10.13  Distrib 5.7.17, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: mobilenet
-- ------------------------------------------------------
-- Server version	5.5.5-10.1.32-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `brands`
--

DROP TABLE IF EXISTS `brands`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `brands` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `brands`
--

LOCK TABLES `brands` WRITE;
/*!40000 ALTER TABLE `brands` DISABLE KEYS */;
INSERT INTO `brands` VALUES (1,'Samsung'),(2,'Sony'),(3,'Apple'),(4,'Huawei'),(5,'Nokia'),(6,'Wiko'),(7,'Honor');
/*!40000 ALTER TABLE `brands` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `operating_systems`
--

DROP TABLE IF EXISTS `operating_systems`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `operating_systems` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `codename` varchar(45) NOT NULL,
  `release_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `operating_systems`
--

LOCK TABLES `operating_systems` WRITE;
/*!40000 ALTER TABLE `operating_systems` DISABLE KEYS */;
INSERT INTO `operating_systems` VALUES (1,'iOS 9','','2015-09-16'),(2,'iOS 10','','2016-09-13'),(3,'iOS 11','','2017-09-19'),(4,'iOS 12','','2018-09-17'),(5,'Android 5.0','Lollipop','2014-11-04'),(6,'Android 6.0','Marshmallow','2015-10-02'),(7,'Android 7.0','Nougat','2016-08-22'),(8,'Android 8.0','Oreo','2017-08-21'),(9,'Android 9.0','Pie','2018-08-07'),(10,'Windows 8.0','','2012-10-29'),(11,'Windows 10.0','','2015-01-21');
/*!40000 ALTER TABLE `operating_systems` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `smartphones`
--

DROP TABLE IF EXISTS `smartphones`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `smartphones` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `price` decimal(6,2) NOT NULL,
  `stock` int(4) NOT NULL,
  `release_date` date NOT NULL,
  `brand` int(2) NOT NULL,
  `operating_system` int(2) NOT NULL,
  PRIMARY KEY (`id`,`brand`,`operating_system`),
  KEY `fk_smartphones_brands_idx` (`brand`),
  KEY `fk_smartphones_operating_systems1_idx` (`operating_system`),
  CONSTRAINT `fk_smartphones_brands` FOREIGN KEY (`brand`) REFERENCES `brands` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_smartphones_operating_systems1` FOREIGN KEY (`operating_system`) REFERENCES `operating_systems` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `smartphones`
--

LOCK TABLES `smartphones` WRITE;
/*!40000 ALTER TABLE `smartphones` DISABLE KEYS */;
INSERT INTO `smartphones` VALUES (1,'Galaxy S10',899.95,5,'2019-04-05',1,9),(2,'Lumia 650',164.95,14,'2016-02-15',5,11),(3,'Xperia 10 Plus',429.49,8,'2019-03-15',2,9),(4,'Xperia XA1',169.00,0,'2017-03-31',2,7),(5,'iPhone X',949.99,18,'2017-11-03',3,4),(6,'Galaxy A6',189.00,31,'2016-10-07',1,9),(7,'Xperia XZ1',441.12,6,'2017-08-31',2,8),(8,'Lumia 950',89.95,3,'2015-10-06',5,11),(9,'iPhone 8',644.99,0,'2017-09-22',3,2),(10,'Mate 20',481.95,15,'2018-10-16',4,9),(11,'iPhone Xs',1139.29,12,'2018-09-21',3,2);
/*!40000 ALTER TABLE `smartphones` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'mobilenet'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-04-01 18:07:57

[m_hutley]

Right. In order of your post:

The SELECT syntax.

@Exonyan1

im trying to get the name & price from the cheapest smartphone, whats the correct SQL statement for that?

Let me take your sentence, color it in a bit, and see if I can point out how an SQL statement is (almost) the same thing as asking your question.

“to get the name & price from the cheapest smartphone”
So you’ve got a Verb, some Nouns, an Adjective, and a Subject.
In SQL terms, this query would be written as:
“SELECT name, price FROM smartphones ORDER BY price ASC LIMIT 1”

• The first thing we want to do is tell the database what operation we want to perform. You said “get”, which is a SELECT query. Other common operations include INSERT, UPDATE, and DELETE. (You’ll be introduced to this 4-major-operation theory as “CRUD” or Create, Retrieve, Update, Delete. In SQL, it would be ISUD.)
• Next we need to tell the database what information we want to retrieve. As you said in your statement, you want the name and price. commas separate things, so the database knows where one field ends and another begins.
• Next the database needs to know where to get that information FROM. We tell it to look in the smartphones table.
• The last bit may be a bit confusing to translate, but the database engine doesnt understand words like “cheapest”. So we tell it to ORDER the rows of the table by their price, ASCending (which is the default. Our other option is DESCending, which would put them in most expensive to least expensive). We only want the least expensive one of those rows, so we put a LIMIT on our results, selecting only 1 row. Because we sorted by our price, that will retrieve only the least expensive phone.

The JOIN syntax

Sometimes, we want to know things that come from multiple tables. In your example, you want the name of the OS, which lives in the operating_systems table, and the name of the phone, which lives in the smartphones table.

Again, you want to get some information from these two tables, so you’re still going to form a SELECT statement. JOIN isn’t a statement unto itself. But this time, you want information from two different places, which introduces a bit of extra language.

The proper syntax for this will be:
SELECT smartphones.name, operating_systems.name FROM smartphones JOIN operating_systems ON smartphones.operating_system = operating_systems.id;

• SELECT, again, is exactly the same as before. It’s our verb.
• The things we want to get are green. Note that this time I have to tell it which table each field is coming from, because there are two "name"s when we join the tables up, one in the smartphones table, and one in the operating_system table, so the database would get confused if i just said “name, name”. We put a period (.) between the table name and the field name, so the database knows what we’re saying.
• smartphones is our initial table. In the case of this join (formally an INNER JOIN, with the ‘INNER’ being implied if you don’t specify),this behaves exactly as it did before.
• Now we get to our JOIN. We’re going to join these two tables, but the database needs to know how to join those two tables. (With a Foreign Key, it may be able to intuit the join, but its good to spell it out anyway)
• Our join condition is specified with the keyword ON. We then provide a boolean conditional test to apply to each pairings of rows; in this case, that the id field in the operating_system table matches the operating_system field in the smartphone table.

Other JOINs, and what they represent.

In our example, we used an (implied) INNER JOIN. But there are 3 other joins: a LEFT JOIN, a RIGHT JOIN, and an OUTER JOIN.
To understand them, i’m going to use a simple diagram:

(Thanks Lucidchart.)

Let’s say that…
A is a table of People.
B is a table of Guitars.

Some of the guitars are owned by people. That’s the middle bit. Not all of the guitars are owned by people, and not every person owns a guitar. Those are the bits of A and B that aren’t in the middle.

• an INNER JOIN (also called the INTERSECTION in maths) narrows your results down to just the middle bit. The people who own guitars.
• a LEFT JOIN selects EVERYONE in A, both those that have a guitar, and those that don’t. If they own a guitar, then their guitar information is included in the join. If they don’t, all the guitar-table fields will be NULL.
• a RIGHT JOIN is the same thing as a LEFT JOIN, except for it selects every guitar in B.
• LEFT vs RIGHT is just dependant on the order you put them in your query. If I said "SELECT * FROM people LEFT JOIN guitars, then i’m selecting every person, because ‘people’ is on the left side of those words.
• an OUTER JOIN (also called a FULL OUTER JOIN or a UNION) selects EVERYTHING. It includes every person and every guitar; those that have a matching element will have the corresponding data filled in, and those that don’t will have NULL’s.

Pop Quiz:
Let’s say my tables are defined in this way:

guitar {
   id INT(20) PRIMARY KEY AUTOINCREMENT,
   model VARCHAR(50) NOT NULL,
   maker VARCHAR(50) NOT NULL,
   price_paid FLOAT(9,2) NOT NULL DEFAULT 0.00,
   purchase_date DATE NOT NULL
}

person {
   name VARCHAR(255) PRIMARY KEY,
   address VARCHAR(255),
   age INT(3) NOT NULL DEFAULT 18,
   guitar_id INT(20),
 }

1. What SQL would I need to get the model of the highest cost guitar that has been bought?
2. What SQL would I need to get the names of people who have bought guitars?
3. What SQL would I need to get a list of everyone in my database, and if they have done so, what date they purchased a guitar?

Bonus. The last person who bought a guitar from me forgot their free case. How can I find what address I should send it to?

[Exonyan1]

Damn, thanks for the help! I thought there was only 1 join, little did i know…
Thinks are starting to make sense now tho.

& Also, lets say, i want all the products which are in store ( available ) from APPLE, how should i do that?

[WebSteve]

You write it out first, using the reasoning from earlier, and that’ll tell us how well you are picking it up.

[SamuelCalifornia]

@Exonyan1

but i only want to find this information using the JOIN statement

I hope you understand that that is not a good way to solve problems normally. Normally we decide what needs to be done then decide how to do it instead of deciding how to do it before we decide what we need to do.