Northwind and pubs sample databases for Microsoft SQL Server Before you can use the Northwind database, you have to run the downloaded instnwnd.sql script file to recreate the database on an instance of SQL Server by using SQL Server Management Studio or a similar tool. Follow the instructions in the Readme file in the repository. Northwind is the name of the sample database for SQL Server 2000, that later got replaced with the AdventureWorks sample database in SQL Server 2005. However, it is still used in e.g. Microsoft SQL Server 2008 Database Development Training Kit so may still be relevant for users trying to learn SQL essentials. However, in this case, I wanted to see if I could use SQL queries to not only download the data that I needed, but to calculate some basic summary stats that would be useful. Structured Query Language (SQL) Non-procedural (declarative) language common to most relational database systems. – Used by the database system internally and by users to manipulate and query the data. – Uses set theory (as opposed to row processing) to process requests. – Limited but flexible set of commands. – Free-form, uses semi-colon as statement terminator in most databases.

This is part 2 of the tutorial series - converting the popular Microsoft Access Northwind database queries toMySQL queries. These queries are originated from Access Northwind Traders application. Some of them are relatively complex aggregated queries with sub-queries.

6. Order Details Extended

This query calculates sales price for each order after discount is applied.

Here is the query result. 2,155 records returned.

7. Sales by Category

For each category, we get the list of products sold and the total sales amount.Note that, in the second query, the inner query for table c is to get sales for each product on each order. It then joins with outer query on Product_ID. In the outer query, products are grouped for each category.

Here is the query result. 77 records returned.
Reinforced concrete design handbook.

8. Ten Most Expensive Products

The two queries below return the same result. It demonstrates how MySQL limits the number of records returned.

The first query uses correlated sub-query to get the top 10 most expensive products.

The second query retrieves data from an ordered sub-query table and then the keyword LIMIT is used outside the sub-query to restrict the number of rows returned.

Here is the query result. 10 records returned.

9. Products by Category

This is a simple query just because it's in Access Northwind so we converted it here in MySQL.

Here is the query result. 69 records returned.

10. Customers and Suppliers by City

This query shows how to use UNION to merge Customers and Suppliers into one result set by identifying them as having different relationships to Northwind Traders - Customers and Suppliers.

Sql Queries On Northwind Database

Here is the query result. 120 records returned.

Sql Database Queries Examples

Other tutorials in this category

1. MySQL Northwind Queries - Part 1
2. MySQL Northwind Queries - Part 3
3. How to Work with Two Unrelated Values
4. How to Fill Gaps in Sales Data
5. How to Calculate Totals, Subtotals and Grand Total
6. How to Work with NULL Values
7. How to fill down empty cells with values from a previous non-empty row
8. Use RANK function to update a previous record within a group or partition
9. Two ways to add a unique number or ID to each row
10. 3 ways to get Top N rows from MySQL
11. How to generate Cumulative Sum (running total) by MySQL - Part 1

Northwind Database Sql Queries

northwindMySQL

Description

A step-by-step tutorial for importing the Northwind Access Database into MySQL.

Discussion

The Northwind database is a Microsoft Access database that ships with Alpha Anywhere. In order to perform CRUD (Create, Read, Update, Delete) operations on the Northwind Access database, the database must be copied to a directory where you have read/write access or converted to another database format. The Northwind Access database can be found inside the MDBFiles directory in your Alpha Anywhere installation. Your Alpha Anywhere install is typically located at C:Program Files (x86)a5v12. In this tutorial, you will learn how to convert the Northwind Access database to a MySQL database.

This tutorial demonstrates importing the Northwind database into MySQL Server using MySQL Workbench 6.3 CE.

Download the northwindmysql.zip file and extract its contents somewhere on your computer. The .zip contains the Northwind Access database in a self-contained SQL file named 'northwindmysql.sql'.
Open MySQL Workbench. Under Management, open the Data Import/Restore tool.
On the Import from Disk tab, select Import from Self-Contained File. Use the browse button to select the northwindmysql.sql file you extracted from the download in the first step.
Select the Default Target Schema:. The northwindmysql.sql file does not have a target schema. Use the New button to create a new target schema named 'northwind'.
Click the Start Import button to import the Northwind database into the selected target schema. The Import Progress tab will display the progress of the import.
You should now see a list of tables in the northwind schema. The Northwind database is now imported into MySQL.

Build a MySQL Connection String

Now that the Northwind database has been imported into MySQL, the next step is to create a Connection String to the database.

From the Web Projects Control Panel, select [ManageConnStr] Manage Connection Strings > Alpha DAO Connection strings from the Edit menu to create a new connection string.
Click the New button to create a new connection string. Type in 'Northwind' for the Connection Name and click the Build button to construct the Connection String.
Creating a new connection to the Northwind MySQL database.
Select MySQL from the list of options in the Select an API window and click OK.
Configure the MySQL Options for the connection. Choose the Version of MySQL that is installed.
Enter 'localhost' in Hostname. If the MySQL database is hosted on another computer, enter the host IP address. For example, 127.0.0.1.
Enter the MySQL Port number. The default port number for a typical MySQL installation is '3306'.
Enter the User for accessing the MySQL database. On your development machine, this can be 'root' or a user account you created.
Enter the password for the User account specified.
Verify the connection to MySQL is correct by clicking the Test Connection button. You should see a success message. If you receive an error message, verify the Hostname, Port, User, and Password are correct.
Success! The connection string to the MySQL database works.
Select the MySQL Northwind database you created from the Database list. If Database does not have any options, click the Refresh button. If it still does not list any databases, verify the User you have chosen has access to the MySQL Northwind database.
Configuring the MySQL connection string. The Northwind database in this MySQL instance is named 'northwind'.
Click OK to create the MySQL connection string.
Click OK to save the new Northwind connection string.