In my ten years of working with SQL, I've honed several critical skills that significantly enhance database management and data manipulation. Here’s a detailed tutorial on these skills, complete with practical examples.
Exercise Setup
--Your Preparation
CREATE TABLE Customers (
CustomerUID UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
CustomerNumber BIGINT IDENTITY(1,1) NOT NULL,
LastName NVARCHAR(100) NOT NULL,
FirstName NVARCHAR(100) NOT NULL,
DOB DATE NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
CHECK (YEAR(DOB) >= 1900),
PRIMARY KEY (CustomerUID)
);
CREATE TABLE Products (
ProductUID UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
ProductName NVARCHAR(1000) NOT NULL,
ProductCode NVARCHAR(1000) NOT NULL,
AvailableQuantity INT NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
CHECK (AvailableQuantity >= 0),
PRIMARY KEY (ProductUID)
);
CREATE TABLE Orders (
OrderUID UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
CustomerUID UNIQUEIDENTIFIER,
OrderNumber NVARCHAR(1000) NOT NULL,
OrderDate DATETIME NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (OrderUID),
FOREIGN KEY (CustomerUID) REFERENCES Customers(CustomerUID)
);
CREATE TABLE OrderItems (
OrderItemUID UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
OrderUID UNIQUEIDENTIFIER,
ProductUID UNIQUEIDENTIFIER,
Quantity INT NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (OrderItemUID),
FOREIGN KEY (OrderUID) REFERENCES Orders(OrderUID),
FOREIGN KEY (ProductUID) REFERENCES Products(ProductUID),
);
--Create customers
INSERT INTO Customers (LastName, FirstName, DOB) VALUES
('Au Yeung', 'David', '19801231')
, ('Chan', 'Peter', '19820115')
--Create products
INSERT INTO Products (ProductName, ProductCode, AvailableQuantity) VALUES
('Android Phone', 'A0001', 100)
, ('iPhone', 'I0001', 100)
--David bought 10 iPhone
INSERT INTO Orders (CustomerUID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerUID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0)
, 'ORD0001'
, GETDATE())
INSERT INTO OrderItems (OrderUID, ProductUID, Quantity) VALUES
((SELECT TOP 1 OrderUID FROM Orders WHERE OrderNumber = 'ORD0001' AND IsDeleted = 0)
, (SELECT TOP 1 ProductUID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0)
, 10)
SELECT * FROM Customers
SELECT * FROM Products
SELECT * FROM Orders
SELECT * FROM OrderItems
Skill #1: Finding Customers Without Orders Using LEFT JOIN
One common task is identifying customers who have not placed any orders. This can be efficiently done using LEFT JOIN combined with a check for NULL values.
Example:
SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON o.CustomerUID = c.CustomerUID AND o.IsDeleted = 0
WHERE o.OrderUID IS NULL;
This query retrieves all customers who do not have associated orders, allowing you to target them for marketing or engagement strategies.
Skill #2: Avoiding Duplicates with NOT EXISTS
When inserting new records, especially in batch operations, ensuring that duplicates do not occur is crucial. Using NOT EXISTS can prevent this effectively.
Example:
IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductName = 'iPhone')
INSERT INTO Products (ProductName, ProductCode) VALUES ('iPhone', 'I0001')
ELSE
PRINT 'Duplicate Product Name!';
This query checks if the product already exists before attempting to insert it, thus maintaining data integrity.
Skill #3: Enhancing Readability with Temporary Tables
Using temporary tables can simplify complex queries, especially when dealing with subqueries. This improves readability and maintainability of your SQL code.
Example:
SELECT ProductUID
INTO #BestSeller
FROM OrderItems
WHERE IsDeleted = 0
GROUP BY ProductUID
HAVING SUM(Quantity) > 5;
SELECT * FROM Products WHERE ProductUID IN (SELECT * FROM #BestSeller);
DROP TABLE IF EXISTS #BestSeller;
Here, we create a temporary table to hold order IDs of best-selling items, making the subsequent query clearer.
Skill #4: Utilizing Common Table Expressions (CTEs) for Sequential Queries
CTEs are beneficial for creating more readable and organized queries, especially when dealing with sequential data or hierarchical relationships.
Example:
;WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerUID ORDER BY CreateDate DESC) AS rn
FROM Orders
)
SELECT *
FROM cte
WHERE rn = 1;
This CTE retrieves the most recent order for each customer, showcasing how CTEs can simplify complex logic.
Skill #5: Using Transactions for Data Integrity
When performing updates, especially those that could potentially affect large portions of your data, wrapping your operations in a transaction is essential. This practice allows you to ensure data integrity by either committing or rolling back changes.
Example:
BEGIN TRAN;
UPDATE Products
SET AvailableQuantity = 0
WHERE ProductCode = 'I0001'
AND IsDeleted = 0;
-- Check the results before COMMIT
SELECT * FROM Products WHERE ProductCode = 'I0001';
-- Uncomment to commit or rollback
-- COMMIT;
-- ROLLBACK;
This transaction ensures that your updates are only applied if you are satisfied with the results, helping to avoid unintended consequences.
Conclusion
These five skills—using LEFT JOIN to find unmatched records, preventing duplicates with NOT EXISTS, enhancing query readability with temporary tables, utilizing CTEs for complex queries, and ensuring data integrity with transactions—are invaluable in SQL. Mastery of these techniques can significantly improve your efficiency and effectiveness in database management.
Feel free to share your experiences or ask questions in the comments below!
If you want to learn more about SQL, please comment here :)