SQL guide

sudo apt update
sudo apt install -y docker.io
sudo systemctl enable --now docker
sudo usermod -aG docker $USER
newgrp docker

sudo pacman -S docker
sudo systemctl enable --now docker
sudo usermod -aG docker $USER
newgrp docker

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Admin1234!" \
  -p 1433:1433 --name sqlserver \
  -d mcr.microsoft.com/mssql/server:2019-latest

docker stop sqlserver    # stop
docker start sqlserver   # start again

wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak

docker cp AdventureWorks2019.bak sqlserver:/var/opt/mssql/data/

docker exec -it sqlserver /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U SA -P "Admin1234!" -No \
  -Q "RESTORE DATABASE AdventureWorks2019 FROM DISK='/var/opt/mssql/data/AdventureWorks2019.bak'
  WITH MOVE 'AdventureWorks2019' TO '/var/opt/mssql/data/AdventureWorks2019.mdf',
  MOVE 'AdventureWorks2019_log' TO '/var/opt/mssql/data/AdventureWorks2019_log.ldf'"

docker exec -it sqlserver /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U SA -P "Admin1234!" -No \
  -d AdventureWorks2019

docker exec -it sqlserver /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U SA -P "Admin1234!" -No \
  -d AdventureWorks2019 \
  -Q "SELECT TOP 5 Name FROM Production.Product"

SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables
ORDER BY schema_name, table_name

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'
ORDER BY ORDINAL_POSITION

SELECT t.TABLE_NAME, p.rows AS RowCount
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.tables st ON st.name = t.TABLE_NAME
JOIN sys.partitions p ON p.object_id = st.object_id
WHERE t.TABLE_TYPE = 'BASE TABLE' AND p.index_id IN (0,1)
ORDER BY p.rows DESC

SELECT c.Name AS Category,
       SUM(od.LineTotal) AS TotalSales,
       COUNT(DISTINCT oh.SalesOrderID) AS OrderCount
FROM Sales.SalesOrderDetail od
JOIN Production.Product p ON od.ProductID = p.ProductID
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID
JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID = oh.SalesOrderID
GROUP BY c.Name
ORDER BY TotalSales DESC

SELECT YEAR(OrderDate) AS Year,
       MONTH(OrderDate) AS Month,
       SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month

SELECT TOP 10
    p.Name AS Product,
    SUM(od.OrderQty) AS UnitsSold,
    SUM(od.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail od
JOIN Production.Product p ON od.ProductID = p.ProductID
GROUP BY p.Name
ORDER BY UnitsSold DESC

SELECT
    p.FirstName + ' ' + p.LastName AS Salesperson,
    COUNT(oh.SalesOrderID) AS OrderCount,
    SUM(oh.TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader oh
JOIN Sales.SalesPerson sp ON oh.SalesPersonID = sp.BusinessEntityID
JOIN Person.Person p ON sp.BusinessEntityID = p.BusinessEntityID
GROUP BY p.FirstName, p.LastName
ORDER BY TotalSales DESC

SELECT TOP 10
    p.FirstName + ' ' + p.LastName AS Customer,
    COUNT(oh.SalesOrderID) AS OrderCount,
    SUM(oh.TotalDue) AS TotalSpent
FROM Sales.SalesOrderHeader oh
JOIN Sales.Customer c ON oh.CustomerID = c.CustomerID
JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
GROUP BY p.FirstName, p.LastName
ORDER BY TotalSpent DESC

SELECT p.FirstName + ' ' + p.LastName AS Customer,
       MAX(oh.OrderDate) AS LastPurchase
FROM Sales.Customer c
JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader oh ON c.CustomerID = oh.CustomerID
GROUP BY p.FirstName, p.LastName
HAVING MAX(oh.OrderDate) < DATEADD(YEAR, -1, GETDATE())
    OR MAX(oh.OrderDate) IS NULL
ORDER BY LastPurchase

SELECT p.Name, p.ListPrice
FROM Production.Product p
LEFT JOIN Sales.SalesOrderDetail od ON p.ProductID = od.ProductID
WHERE od.ProductID IS NULL
ORDER BY p.Name

SELECT p.Name,
       pi.Quantity AS CurrentStock,
       p.ReorderPoint AS MinStock
FROM Production.Product p
JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
WHERE pi.Quantity < p.ReorderPoint
ORDER BY pi.Quantity ASC

SELECT c.Name AS Category,
       p.Name AS Product,
       p.ListPrice
FROM Production.Product p
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID
WHERE p.ListPrice = (
    SELECT MAX(p2.ListPrice)
    FROM Production.Product p2
    JOIN Production.ProductSubcategory s2 ON p2.ProductSubcategoryID = s2.ProductSubcategoryID
    WHERE s2.ProductCategoryID = c.ProductCategoryID
)
ORDER BY c.Name

SELECT EmailAddress, COUNT(*) AS Duplicates
FROM Person.EmailAddress
GROUP BY EmailAddress
HAVING COUNT(*) > 1
ORDER BY Duplicates DESC

SELECT
    COUNT(*) AS TotalRows,
    SUM(CASE WHEN Color IS NULL THEN 1 ELSE 0 END) AS Color_Nulls,
    SUM(CASE WHEN Size IS NULL THEN 1 ELSE 0 END) AS Size_Nulls,
    SUM(CASE WHEN Weight IS NULL THEN 1 ELSE 0 END) AS Weight_Nulls
FROM Production.Product