Filtering Rows Using Join and a Sub-query

In this Post I demonstrate how we combine two sets of information and come up with a filtered result set using 'join' and 'sub-queries'.
For this demonstration we use two Excel files which you could download from the following links. One is a list of all Nobel_Laureates and the other is a list of all the countries in the world.

Open SQL Server Management Studio and create a new database with name "Nobel_Laureates".

Create DATABASE Nobel_Laureates

Use Nobel_Laureates

Import the Excel tables to your database. You could refer  how you import Excel data into SQL Server here.
Run the following queries.

SELECT * 

FROM [dbo].[Countries-Continents-csv$]

Now lets filter the countries by continent. We run the following query to see countries only in South America.

SELECT [Country]FROM [dbo].['Countries-Continents-csv$']WHERE [Continent] = 'SOUTH AMERICA'

The partial result set looks like the following.


SELECT * 
FROM [dbo].[List of Nobel Laureates$]

Now we filter the Nobel Laureates by country of birth. Very easy, we could see Nobel Prize Winners who were born in Brazil using the following query.

SELECT * 
FROM [dbo].[List of Nobel Laureates$]
Where [Country of Birth] = 'Brazil'

Now lets try to see Nobel_Laureates from South America, To do that we should know which countries are from South America. You remember We could get that using the following query.

SELECT [Country]
FROM [dbo].['Countries-Continents-csv$']
WHERE [Continent] = 'SOUTH AMERICA'

But what we wanted here is the nobel_Laureates from South America.

SELECT * 
FROM [dbo].[List of Nobel Laureates$]
--Where [Country of Birth] in (countries of south america)

So using a sub-query  the solution for this problem will be

SELECT Winner 
FROM [dbo].[List of Nobel Laureates$]
Where [Country of Birth] IN (SELECT [Country]
                              FROM [dbo].['Countries-Continents-csv$']
                              WHERE [Continent] = 'SOUTH AMERICA'
                             )

But we could also use join to get the same result set. See the following query.

Select Winner
FROM [dbo].[List of Nobel Laureates$] Inner Join [dbo].['Countries-Continents-csv$'] on [Country of Birth] = [country]
where continent = 'south america'

Importing Excel files to SQL server
In this I post I will try to demonstrate how we import a Excel files to SQL Server.We use the table we import to do some other SQL manipulations in the next topics.
We start by creating a database called 'Nobel_Laureates'. Open SQL Server Management Studio and create a new query window by clicking on 'New Query' menu or by hitting 'Ctrl + N' on your keyboard.
Lets create a database called 'Nobel_Laureates' using the following code.

CREATE DATABASE Nobel_Laureates
USE Nobel_Laureates

Now lets download the list of all the Nobel Laureates from online using this link, or by copying and pasting 'http://www.downloadexcelfiles.com/sites/default/files/docs/list_of_nobel_laureates-944j.xlsx' on your browser.
The link downloads an Excel file with a name 'list_of_nobel_laureates-944j.xlsx' on your default downloads folder.
Once the list_of_nobel_laureates-944j.xlsx file is downloaded, We import it to our Database as follows. Right click on the 'Nobel_Laureates' database.

 And go to 'task' and then click on  'import data'.The 'SQL Server Import and Export Wizard' dialogue box pops up.

Read the content and Click Next.

From the Data Source list Select Flat File Sources,click Browse button to select the nobel.csv file from your downloads folder. Leave everything else as it is and click Next.

Leave everything as it is and click Next.

 Leave everything as it is and click Next.

Leave everything as it is and click Next.

Leave everything as it is and click Next.

Leave everything as it is and click Finish.

Leave everything as it is and click Finish.

Now You have the 'nobel' table in your database.

Importing .csv files to SQL server
In this I post I will try to demonstrate how we import a .csv file which we downloaded from online to SQL Server.We use the table which we import to do some other SQL practices in the next topics.
We start by creating a database called 'Nobel_Laureates'. Open SQL Server Management Studio and create a new query window by clicking on 'New Query' menu or by hitting 'Ctrl + N' on your keyboard.
Lets create a database called 'Nobel_Laureates' using the following code.

CREATE DATABASE Nobel_Laureates
USE Nobel_Laureates

Now lets download the list of all the Nobel Laureates from online using this link, or by copying and pasting 'https://www.aggdata.com/download_sample.php?file=nobel.csv' on your browser.
The link downloads a .csv file with a name 'nobel.csv' on your default downloads folder.
Once the nobel.csv file is downloaded, We import it to our Database as follows. Right click on the 'Nobel_Laureates' database.

 And go to 'task' and then click on  'import data'.The 'SQL Server Import and Export Wizard' dialogue box pops up.

Read the content and Click Next.

From the Data Source list Select Flat File Sources,click Browse button to select the nobel.csv file from your downloads folder. Leave everything else as it is and click Next.

Leave everything as it is and click Next.

 Leave everything as it is and click Next.

Leave everything as it is and click Finish.

Now You have the 'nobel' table in your database.