Northwind Database SQL Queries Display all columns and all rows from the Employees table. Display the regionid, regiondescription for all rows in the Regions. These scripts were originally created for SQL Server 2000. Write a query to count current and discontinued products. Go to the editor. Click me to see the solution. Write a query to get Product list (name, units on order, units in stock) of stock is less than the quantity on order. Go to the editor. Click me to see the solution. Structure of 'northwind' database: MySQL Online Editor. Simple SQL queries in MariaDB To begin with, let’s try looking at a single table. From a command line run mysql -uroot and use the Northwind database as shown below. Note that it’s safe to use the. The Northwind database was Microsoft’s first attempt at making a unified set of objects available for cross-platform examples. That means Microsoft Access users can start with a database in SQL Server that they are already familiar with, and applications designed to work against this database in Access will work once you point them at SQL Server.
- Northwind Database Sql Queries Examples
- Northwind Database Sql Queries Free
- Northwind Database Script Sql
Subqueries
Subqueries can be characterized in two main ways. One is by the expected number of values (either scalar or multivalued), and another is by the subquery’s dependency on the outer query (either self-contained or correlated). Both scalar and multivalued subqueries can be either self-contained or correlated.
Self-Contained Subqueries
A self-contained subquery is a subquery that can be run independently of the outer query. Self-contained subqueries are very convenient to debug, of course, compared to correlated subqueries.
Scalar subqueries can appear anywhere in the query where an expression resulting in a scalar value is expected, while multivalued subqueries can appear anywhere in the query where a collection of multiple values is expected.
A scalar subquery is valid when it returns a single value, and also when it returns no valuesin which case, the value of the subquery is NULL. However, if a scalar subquery returns more than one value, a run-time error will occur.
………………
Logically, a self-contained subquery can be evaluated just once for the whole outer query. Physically, the optimizer can consider many different ways to achieve the same thing, so you shouldn’t think in such strict terms.
…………………
Correlated Subqueries
Correlated subqueries are subqueries that have references to columns from the outer query. Logically, the subquery is evaluated once for each row of the outer query. Again, physically, it’s a much more dynamic process and will vary from case to case. There isn’t just one physical way to process a correlated subquery.
………………….
Northwind Database Sql Queries Examples
Let’s start with the basic request to return the orders with the maximum OrderDate for each employee. Here you can get multiple rows for each employee because an employee can have multiple orders with the same order date.
You might be tempted to use this solution, which includes a self-contained subquery similar to the one used to return orders on the last actual order date of the month:
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM dbo.Orders
WHERE OrderDate IN
(SELECT MAX(OrderDate) FROM dbo.Orders
GROUP BY EmployeeID);
However, this solution is incorrect. The result set will include the correct orders (the ones with the maximum OrderDate for each employee). But you will also get any order for employee A with an OrderDate that happens to be the maximum for employee B, even though it’s not also the maximum for employee A. This wasn’t an issue with the previous problem, as an order date in month A can’t be equal to the maximum order date of a different month B.
Northwind Database Sql Queries Free
In our case, the subquery must be correlated to the outer query, matching the inner EmployeeID to the one in the outer row:
Northwind Database Script Sql
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM dbo.Orders AS O1
WHERE OrderDate =
(SELECT MAX(OrderDate)
FROM dbo.Orders AS O2
WHERE O2.EmployeeID = O1.EmployeeID);