SQL Queries

To demonstrate the power of SQL, I created a database using T-SQL. I then wrote a large number of queries. Please click below to download the file.

One thing I learned is that window functions can be much faster than subqueries. Please consider this code.

-- Query 19A

-- Previous and next order using subqueries

-- Please note that subqueries are much slower than window functions

SELECT orderdate, empid, custid,orderid,

 (SELECT MAX(o2.orderid)

 FROM orders AS O2

 WHERE o2.orderid < o1.orderid

 AND o2.custid = o1.custid

  AND o2.custid IS NOT NULL) AS prevorderid,

 (SELECT MIN(o3.orderid)

 FROM orders AS o3

 WHERE o3.orderid > o1.orderid

 AND o3.custid = o1.custid

  AND o3.custid IS NOT NULL) AS nextorderid

FROM Orders AS o1

WHERE o1.custid IS NOT NULL

ORDER BY o1.custid, o1.orderid;

-- Query 19B

-- Previous and next order using window functions

SELECT orderdate, empid, custid,orderid,

 LAG(orderid)  OVER(PARTITION BY custid

   ORDER BY orderid) AS prevorderid,

 LEAD(orderid)  OVER(PARTITION BY custid

   ORDER BY orderid) AS nextorderid

FROM Orders

WHERE custid IS NOT NULL

ORDER BY custid, orderid;

SQL Query Download