Nested Column Queries

In the previous lesson we examined nested queries that returned a single value. In this lesson we’ll see nested queries that return values belonging to the same column.

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/31lesson.sh and wait for the MySQL prompt to start-up.

MySQL

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 

-- Query 1
SELECT * FROM Actors 
INNER JOIN DigitalAssets ON ActorId=Id 
WHERE AssetType = ANY (SELECT DISTINCT AssetType 
                       FROM DigitalAssets
                       WHERE AssetType != 'Website');

-- Query 2
SELECT * FROM Actors 
INNER JOIN DigitalAssets ON ActorId=Id 
WHERE AssetType != 'Website';

-- Query 3
SELECT FirstName, SecondName
FROM Actors
WHERE Id = ANY (SELECT ActorId
                FROM DigitalAssets
                WHERE AssetType = 'Facebook');

-- Query 4
SELECT FirstName, SecondName
FROM Actors
WHERE Id IN (SELECT ActorId
             FROM DigitalAssets
             WHERE AssetType = 'Facebook');

-- Query 5
SELECT FirstName, SecondName 
FROM Actors 
WHERE NetworthInMillions > ALL (SELECT NetworthInMillions 
                                FROM Actors
                                WHERE FirstName LIKE "j%");

1.Introduction

2.Basic SQL

3.Joins

4.Nested Queries

5.Multi Table Operations

6.Views

7.Stored Procedures

8.Triggers

9.Miscellaneous

10.Practice & Prep

11.Epilogue

Nested Column Queries

Nested Column Queries