Evil Programmer

In this video, I show you how to query SQL Server with Microsoft Access and work with small chunks of data at a time.

In this video, I show you how to use Offset and Fetch in SQL Server. AdventureWorks2017 Query: USE AdventureWorks2017; GO SELECT PurchaseOrderID, OrderDate, SubTotal, ModifiedDate FROM Purchasing.PurchaseOrderHeader ORDER BY SubTotal DESC OFFSET 10 ROW FETCH FIRST 5 ROWS ONLY Get "Evil Programmer" Merchandise here: https://teespring.com/stores/evil-programmer Never surf the net without a VPN. Get a great offer on Nord VPN here: https://go.nordvpn.net/aff_c?offer_id=15&aff_id=30592&url_id=902​ Nord VPN has a "no logs" policy so all your activity is private.

In this video, I show you how to use the Top filter in SQL Server Get "Evil Programmer" Merchandise here: https://teespring.com/stores/evil-programmer

In this video, we'll fetch information from a database and export it to Excel The pyodbc library to connect to databases: https://pypi.org/project/pyodbc/ The xlsxwriter library: https://pypi.org/project/XlsxWriter/ xlsxwriter documentation: https://xlsxwriter.readthedocs.io/ Connection strings: https://www.connectionstrings.com Always use a VPN and protect your privacy. Get a great offer on Nord VPN here: https://go.nordvpn.net/aff_c?offer_id=15&aff_id=30592&url_id=902 Nord VPN has a no logs policy so all your activity is private. Evil Programmer Merchandise (world wide): https://teespring.com/stores/evil-programmer ------------------------------------------------------- Script: ------------------------------------------------------- import xlsxwriter, pyodbc #put database connection string here conn = pyodbc.connect(" ") #cursor cur = conn.cursor() #sql statement to retrieve product category sql = "SELECT DISTINCT Production.ProductCategory.[Name] AS [Category] FROM Production.ProductCategory WHERE Production.ProductCategory.[Name] IS NOT NULL" #execute sql cur.execute(sql) #results rs = cur.fetchall() #Create workbook wb = xlsxwriter.Workbook("Report.xlsx") sql = "SELECT Production.Product.ProductID AS [Product ID], Production.Product.[Name] AS [Product], Production.ProductModel.[Name] AS [Model], Production.ProductCategory.[Name] AS [Category], Production.ProductSubcategory.[Name] AS [Sub Category] FROM Production.Product LEFT JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID LEFT JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID LEFT JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE Production.ProductCategory.[Name] IS NOT NULL AND Production.ProductCategory.[Name]=" for rec in rs: #get records for each category cur.execute(sql + "'" + rec.Category + "'") records = cur.fetchall() print("Category: " + rec.Category) #create a worksheet for each category ws = wb.add_worksheet(rec.Category) #column headers columns = [column[0] for column in cur.description] #header ws.write_row(0,0,columns) rownum = 1 for record in records: ws.write_row(rownum,0,record) rownum += 1 #close workbook wb.close() cur.close() del cur conn.close() print("Completed!") ---------------------------------------------------------------------- The AdventureWorks2017 SQL query for products: USE AdventureWorks2017; SELECT Production.Product.ProductID AS [Product ID], Production.Product.[Name] AS [Product], Production.ProductModel.[Name] AS [Model], Production.ProductCategory.[Name] AS [Category], Production.ProductSubcategory.[Name] AS [Sub Category] FROM Production.Product LEFT JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID LEFT JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID LEFT JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE Production.ProductCategory.[Name] IS NOT NULL

In this video I show you the identity property and the sequence object. Documentation The identity property: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15 The sequence object: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15 Alter sequence: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-sequence-transact-sql?view=sql-server-ver15 Never surf the net without a VPN. Get a great offer on Nord VPN here: https://nordvpn.com/special/?utm_medium=affiliate&utm_term&utm_content&utm_campaign=off15&utm_source=aff30592 Nord VPN has a no logs policy so all your activity is private. Get your Evil Programmer merchandise here: https://teespring.com/stores/evil-programmer

In this video, I show you the Merge statement. Official documentation for the Merge statement can be found here: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 Never surf the net without a VPN. Get a great offer on Nord VPN here: https://go.nordvpn.net/aff_c?offer_id... Nord VPN has a no logs policy so all your activity is private. Get "Evil Programmer" Merchandise here: https://teespring.com/stores/evil-programmer