Trying to understand SQL statements

1

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 */;
2

I really enjoyed the sql tutorial sites.

http://www.sqltutorial.org
http://www.postgresqltutorial.com/
http://www.sqlitetutorial.net/

They all have sample databases and walk you through and explain all the basics including the different joins. Using client software with a GUI helps a lot too.

They also have a playgrounds with a sample database where you can write your queries right in the webpage. Could be a good supplement for your course.

Visualizing the joins helps too. http://i.stack.imgur.com/UI25E.jpg

To get the cheapest smartphone you would write something like:

SELECT name, price FROM smartphones ORDER BY price;

If you just want to return one result you could append LIMIT 1 for example.