DBA Challenges - Filegroup_Demo

FileGroup_Demo.sql

CREATE DATABASE [FileGroup_DEMO]
ON 
PRIMARY 
(
    NAME = N'FileGroup_DEMO_Data', -- Logical name of primary data file
    FILENAME = N'C:\SQLData\FileGroup_DEMO_Data.mdf', -- Specify the primary data file path
    SIZE = 4MB, -- Initial size of primary data file
    FILEGROWTH = 256MB -- File growth increment for the primary data file
),
FILEGROUP [INDEXES]
(
    NAME = N'FileGroup_DEMO_Indexes', -- Logical name of indexes file
    FILENAME = N'C:\SQLData\FileGroup_DEMO_Indexes.ndf', -- Specify the indexes file path
    SIZE = 2MB, -- Initial size of indexes file
    FILEGROWTH = 256MB -- File growth increment for the indexes file
)
LOG ON
(
    NAME = N'FileGroup_Demo_Log', -- Logical name of transaction log file
    FILENAME = N'C:\SQLData\FileGroup_DEMO_Log.ldf', -- Specify the log file path
    SIZE = 2MB, -- Initial size of log file
    FILEGROWTH = 64MB-- Log file growth increment
);
GO

USE FileGroup_DEMO; -- Replace with your actual TestDatabase name
GO

-- Customers Table
CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Phone NVARCHAR(20),
    Address NVARCHAR(255)
);

-- Contacts Table
CREATE TABLE dbo.Contacts (
    ContactID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    CustomerID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Phone NVARCHAR(20),
    Role NVARCHAR(50)
);

-- Leads Table
CREATE TABLE dbo.Leads (
    LeadID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Phone NVARCHAR(20),
    Source NVARCHAR(100),
    Status NVARCHAR(50)
);

-- Deals Table
CREATE TABLE dbo.Deals (
    DealID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    OpportunityID INT,
    Name NVARCHAR(100),
    Amount DECIMAL(18, 2),
    CloseDate DATETIME,
    Stage NVARCHAR(50)
);

-- Sales Table
CREATE TABLE dbo.Sales (
    SaleID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    CustomerID INT,
    ProductID INT,
    SaleDate DATETIME,
    Quantity INT,
    TotalAmount DECIMAL(18, 2)
);

-- Interactions Table
CREATE TABLE dbo.Interactions (
    InteractionID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    ContactID INT,
    InteractionType NVARCHAR(50),
    InteractionDate DATETIME,
    Notes NVARCHAR(MAX)
);

-- Accounts Table
CREATE TABLE dbo.Accounts (
    AccountID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    AccountName NVARCHAR(100),
    Industry NVARCHAR(100),
    AnnualRevenue DECIMAL(18, 2),
    NumberOfEmployees INT
);

-- Opportunities Table
CREATE TABLE dbo.Opportunities (
    OpportunityID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    AccountID INT,
    Name NVARCHAR(100),
    EstimatedValue DECIMAL(18, 2),
    CloseDate DATETIME,
    Stage NVARCHAR(50)
);

-- Activities Table
CREATE TABLE dbo.Activities (
    ActivityID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    ContactID INT,
    Type NVARCHAR(50),
    Subject NVARCHAR(100),
    ActivityDate DATETIME,
    Status NVARCHAR(50),
    Notes NVARCHAR(MAX)
);

-- Campaigns Table
CREATE TABLE dbo.Campaigns (
    CampaignID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    Name NVARCHAR(100),
    StartDate DATETIME,
    EndDate DATETIME,
    Budget DECIMAL(18, 2),
    Notes NVARCHAR(MAX)
);

-- EmailTemplates Table (Heap with non-clustered index)
CREATE TABLE dbo.EmailTemplates (
    TemplateID INT IDENTITY(1,1),
    Subject NVARCHAR(255),
    Body NVARCHAR(MAX)
);
CREATE NONCLUSTERED INDEX IX_EmailTemplates_ID ON EmailTemplates (TemplateID) ON [INDEXES];

-- CallLogs Table (Heap with non-clustered index)
CREATE TABLE dbo.CallLogs (
    CallLogID INT IDENTITY(1,1),
    ContactID INT,
    CallDateTime DATETIME,
    Duration INT,
    Notes NVARCHAR(MAX)
);
CREATE NONCLUSTERED INDEX IX_CallLogs_ID ON CallLogs (CallLogID) ON [INDEXES];

-- Meetings Table (Heap with non-clustered index)
CREATE TABLE dbo.Meetings (
    MeetingID INT IDENTITY(1,1),
    ContactID INT,
    MeetingDateTime DATETIME,
    Duration INT,
    Notes NVARCHAR(MAX)
);
CREATE NONCLUSTERED INDEX IX_Meetings_ID ON Meetings (MeetingID) ON [INDEXES];

-- Notes Table (Heap with non-clustered index)
CREATE TABLE dbo.Notes (
    NoteID INT IDENTITY(1,1),
    ContactID INT,
    NoteDate DATETIME,
    NoteText NVARCHAR(MAX)
);
CREATE NONCLUSTERED INDEX IX_Notes_ID ON Notes (NoteID) ON [INDEXES];

-- Tasks Table (Heap with non-clustered index)
CREATE TABLE dbo.Tasks (
    TaskID INT IDENTITY(1,1),
    ContactID INT,
    Description NVARCHAR(MAX),
    DueDate DATETIME
);
CREATE NONCLUSTERED INDEX IX_Tasks_ID ON Tasks (TaskID) ON [INDEXES];

-- Products Table (In INDEXES filegroup)
CREATE TABLE dbo.Products (
    ProductID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    Name NVARCHAR(100),
    Description NVARCHAR(MAX),
    Price DECIMAL(18, 2)
) ON [INDEXES];

-- Services Table (In INDEXES filegroup)
CREATE TABLE dbo.Services (
    ServiceID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    Name NVARCHAR(100),
    Description NVARCHAR(MAX),
    Price DECIMAL(18, 2)
) ON [INDEXES];

-- Invoices Table (In INDEXES filegroup)
CREATE TABLE dbo.Invoices (
    InvoiceID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    CustomerID INT,
    InvoiceDate DATETIME,
    TotalAmount DECIMAL(18, 2)
) ON [INDEXES];

-- Quotes Table (In INDEXES filegroup)
CREATE TABLE dbo.Quotes (
    QuoteID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    CustomerID INT,
    QuoteDate DATETIME,
    ValidUntil DATETIME,
    TotalAmount DECIMAL(18, 2)
) ON [INDEXES];

-- Orders Table (In INDEXES filegroup)
CREATE TABLE dbo.Orders (
    OrderID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(18, 2)
) ON [INDEXES];

GO
USE FileGroup_DEMO
GO
INSERT INTO dbo.Customers (FirstName, LastName, Email, Phone, Address) VALUES
('John', 'Doe', 'john.doe@email.com', '123-456-7890', '1234 Main St, Anytown, USA'),
('Jane', 'Smith', 'jane.smith@email.com', '123-456-7891', '1235 Main St, Anytown, USA'),
('Emily', 'Johnson', 'emily.johnson@email.com', '123-456-7892', '1236 Main St, Anytown, USA'),
('Michael', 'Brown', 'michael.brown@email.com', '123-456-7893', '1237 Main St, Anytown, USA'),
('Sarah', 'Davis', 'sarah.davis@email.com', '123-456-7894', '1238 Main St, Anytown, USA'),
('William', 'Miller', 'william.miller@email.com', '123-456-7895', '1239 Main St, Anytown, USA'),
('Jessica', 'Wilson', 'jessica.wilson@email.com', '123-456-7896', '1240 Main St, Anytown, USA'),
('David', 'Moore', 'david.moore@email.com', '123-456-7897', '1241 Main St, Anytown, USA'),
('Ashley', 'Taylor', 'ashley.taylor@email.com', '123-456-7898', '1242 Main St, Anytown, USA'),
('Chris', 'Anderson', 'chris.anderson@email.com', '123-456-7899', '1243 Main St, Anytown, USA'),
('Jennifer', 'Thomas', 'jennifer.thomas@email.com', '123-456-7800', '1244 Main St, Anytown, USA'),
('Joshua', 'Jackson', 'joshua.jackson@email.com', '123-456-7801', '1245 Main St, Anytown, USA'),
('Amanda', 'White', 'amanda.white@email.com', '123-456-7802', '1246 Main St, Anytown, USA'),
('Daniel', 'Harris', 'daniel.harris@email.com', '123-456-7803', '1247 Main St, Anytown, USA'),
('Melissa', 'Martin', 'melissa.martin@email.com', '123-456-7804', '1248 Main St, Anytown, USA');


-- Inserting data into Contacts
INSERT INTO dbo.Contacts (CustomerID, FirstName, LastName, Email, Phone, Role) VALUES
(1, 'Alice', 'Johnson', 'alice.johnson@email.com', '555-0200', 'Manager'),
(2, 'Bob', 'Williams', 'bob.williams@email.com', '555-0201', 'Supervisor'),
(1, 'Carol', 'Davis', 'carol.davis@email.com', '555-0202', 'Analyst'),
(2, 'David', 'Brown', 'david.brown@email.com', '555-0203', 'Clerk'),
(1, 'Eve', 'Miller', 'eve.miller@email.com', '555-0204', 'Assistant Manager'),
(2, 'Frank', 'Wilson', 'frank.wilson@email.com', '555-0205', 'Sales Representative'),
(1, 'Grace', 'Martinez', 'grace.martinez@email.com', '555-0206', 'Marketing Coordinator'),
(2, 'Henry', 'Anderson', 'henry.anderson@email.com', '555-0207', 'HR Specialist'),
(1, 'Isabel', 'Thomas', 'isabel.thomas@email.com', '555-0208', 'Product Manager'),
(2, 'Jacob', 'Jackson', 'jacob.jackson@email.com', '555-0209', 'Accountant'),
(1, 'Karen', 'White', 'karen.white@email.com', '555-0210', 'Executive Assistant'),
(2, 'Louis', 'Harris', 'louis.harris@email.com', '555-0211', 'IT Support'),
(1, 'Mona', 'Sanchez', 'mona.sanchez@email.com', '555-0212', 'Operations Manager'),
(2, 'Nathan', 'Garcia', 'nathan.garcia@email.com', '555-0213', 'Research Analyst'),
(1, 'Olivia', 'Clark', 'olivia.clark@email.com', '555-0214', 'Project Coordinator');


-- Inserting data into Accounts
INSERT INTO Accounts (AccountName, Industry, AnnualRevenue, NumberOfEmployees) VALUES
('Global Tech', 'Technology', 1000000, 500),
('City Bank', 'Banking', 2000000, 800),
('Green Energy', 'Energy', 500000, 300),
('Express Logistics', 'Logistics', 750000, 200),
('Bright Media', 'Media', 600000, 150),
('River Hotels', 'Hospitality', 1200000, 400),
('Crown Construction', 'Construction', 900000, 350),
('Harvest Foods', 'Retail', 800000, 250),
('Quick Transport', 'Transportation', 450000, 175),
('MediCare Clinic', 'Healthcare', 700000, 300),
('Sunrise Electronics', 'Electronics', 1100000, 450),
('First Legal', 'Legal', 650000, 220),
('TechAdvantage', 'Technology', 1500000, 550),
('Urban Development', 'Real Estate', 1300000, 370),
('Alpha Investments', 'Finance', 1700000, 480);


-- Inserting data into Products
INSERT INTO Products (Name, Description, Price) VALUES
('GigaStore 1TB HDD', '1TB Hard Disk Drive for high-capacity storage', 49.99),
('SpeedFlash 128GB USB', '128GB USB Flash Drive with high-speed data transfer', 19.99),
('TurboSSD 512GB', '512GB Solid State Drive with ultra-fast performance', 69.99),
('MegaCloud 2TB External HDD', '2TB External Hard Disk Drive for extra storage needs', 89.99),
('RapidTransfer 256GB SSD', '256GB Solid State Drive for efficient data access', 59.99),
('UltraSpeed 1TB SSD', '1TB Solid State Drive with maximum performance', 109.99),
('PocketDrive 500GB External', 'Portable 500GB External Drive for easy storage on the go', 59.99),
('FlashStore 64GB USB Stick', '64GB USB Stick for convenient data transfer', 14.99),
('HyperDisk 4TB HDD', '4TB Hard Disk Drive for extensive storage requirements', 129.99),
('CompactFlash 32GB USB', '32GB USB Flash Drive, compact and reliable', 9.99),
('NanoDrive 120GB SSD', '120GB Solid State Drive, small but mighty', 49.99),
('VaultDisk 8TB HDD', '8TB Hard Disk Drive for vast data storage', 199.99),
('StreamLine 250GB External SSD', '250GB External SSD for fast, portable storage', 79.99),
('DataGuard 500GB HDD', '500GB Hard Disk Drive, reliable and affordable', 39.99),
('FlashMax 256GB USB 3.0', '256GB USB 3.0 Flash Drive for rapid file transfers', 29.99),
('SilverLine 2TB SSD', '2TB Solid State Drive, premium performance and reliability', 249.99),
('TravelEase 1TB External HDD', '1TB External HDD, perfect for travel and backups', 69.99),
('MemoryPlus 128GB SD Card', '128GB SD Card, ideal for photography and video', 24.99),
('QuickSync 2TB External SSD', '2TB External SSD, high-capacity and high-speed', 299.99),
('ProStorage 6TB HDD', '6TB Hard Disk Drive, professional-grade storage', 179.99),
('CloudConnect 4TB Network HDD', '4TB Network HDD, accessible anywhere via cloud', 159.99),
('Endurance 750GB SSD', '750GB SSD, durable and reliable for heavy use', 99.99),
('MiniFlash 64GB MicroSD', '64GB MicroSD Card, ultra-compact storage solution', 12.99),
('ThunderDrive 500GB Thunderbolt SSD', '500GB Thunderbolt SSD, lightning-fast data speeds', 149.99),
('DataSphere 10TB External HDD', '10TB External HDD, store everything you need', 299.99),
('ArchiveMaster 1TB Tape Drive', '1TB Tape Drive, long-term archival storage', 399.99),
('PixelStore 512GB CFexpress', '512GB CFexpress Card, extreme speed for high-res video', 199.99),
('RapidSpace 3TB NVMe SSD', '3TB NVMe SSD, next-gen performance for enthusiasts', 399.99),
('GigaFlash 1TB USB-C', '1TB USB-C Flash Drive, versatile and super-fast', 179.99),
('DualDrive 4TB Hybrid HDD', '4TB Hybrid HDD, blend of speed and capacity', 119.99),
('StreamVault 2TB Streaming HDD', '2TB Streaming HDD, optimized for media streaming', 89.99),
('Optima 480GB SSD', '480GB SSD, the perfect balance of speed and value', 79.99),
('EcoStore 3TB Eco-Friendly HDD', '3TB Eco-Friendly HDD, energy-efficient storage', 109.99),
('SafeBackup 8TB Backup Drive', '8TB Backup Drive, keep your data secure and backed up', 229.99),
('PicoDisk 32GB USB Mini', '32GB USB Mini Drive, ultra-portable and convenient', 7.99),
('UltraVault 12TB NAS Drive', '12TB NAS Drive, network-attached, high-capacity storage', 349.99),
('FlashPro 400GB High-Speed SDXC', '400GB High-Speed SDXC, for professional photographers', 129.99),
('PowerDisk 5TB Portable HDD', '5TB Portable HDD, large capacity on the go', 149.99),
('DataRocket 16TB Server HDD', '16TB Server HDD, enterprise-grade storage solution', 599.99),
('SwiftMemory 256GB UHS-II SD Card', '256GB UHS-II SD Card, ultra-high-speed data recording', 99.99),
('MightyFlash 2TB Rugged USB', '2TB Rugged USB Drive, tough and reliable', 249.99),
('QuantumStore 18TB Data Center HDD', '18TB Data Center HDD, high-density server storage', 799.99),
('VelocityDrive 1TB PCIe SSD', '1TB PCIe SSD, top-tier performance for gaming and work', 199.99),
('ArchivePro 6TB LTO Tape', '6TB LTO Tape, large-scale backup and archival', 499.99),
('OrbitDisk 8TB Wireless HDD', '8TB Wireless HDD, access your files wirelessly', 259.99),
('MegaStore 14TB External RAID', '14TB External RAID, dual-drive redundancy', 549.99),
('PocketVault 500GB Slim SSD', '500GB Slim SSD, sleek and compact design', 99.99),
('Thunderbolt 3TB Portable SSD', '3TB Portable SSD with Thunderbolt, ultra-fast external storage', 349.99),
('InfiniteSpace 20TB Cloud Storage Drive', '20TB Cloud Storage Drive, virtually unlimited space', 999.99);

-- Inserting data into Orders
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount) VALUES
(1, '2023-01-15', (SELECT Price FROM dbo.Products WHERE ProductID = 1) * 2 + (SELECT Price FROM dbo.Products WHERE ProductID = 2)), -- 2 units of Product 1 and 1 unit of Product 2
(2, '2023-01-18', (SELECT Price FROM dbo.Products WHERE ProductID = 3)), -- 1 unit of Product 3
(1, '2023-02-05', (SELECT Price FROM dbo.Products WHERE ProductID = 4) + (SELECT Price FROM dbo.Products WHERE ProductID = 5)), -- 1 unit each of Product 4 and 5
(2, '2023-02-10', (SELECT Price FROM dbo.Products WHERE ProductID = 6) * 3), -- 3 units of Product 6
(1, '2023-03-01', (SELECT Price FROM dbo.Products WHERE ProductID = 7)), -- 1 unit of Product 7
(2, '2023-03-15', (SELECT Price FROM dbo.Products WHERE ProductID = 8) + (SELECT Price FROM dbo.Products WHERE ProductID = 9)), -- 1 unit each of Product 8 and 9
(1, '2023-04-05', (SELECT Price FROM dbo.Products WHERE ProductID = 10)), -- 1 unit of Product 10
(2, '2023-04-18', (SELECT Price FROM dbo.Products WHERE ProductID = 11) * 2), -- 2 units of Product 11
(1, '2023-05-10', (SELECT Price FROM dbo.Products WHERE ProductID = 12) + (SELECT Price FROM dbo.Products WHERE ProductID = 13)), -- 1 unit each of Product 12 and 13
(2, '2023-05-20', (SELECT Price FROM dbo.Products WHERE ProductID = 14) * 4); -- 4 units of Product 14

-- Inserting data into dbo.Orders
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount) VALUES
(1, '2023-01-20', (SELECT Price FROM dbo.Products WHERE ProductID = 1) + (SELECT Price FROM dbo.Products WHERE ProductID = 2) + (SELECT Price FROM dbo.Products WHERE ProductID = 3)),
(2, '2023-02-15', (SELECT Price FROM dbo.Products WHERE ProductID = 4) + (SELECT Price FROM dbo.Products WHERE ProductID = 5) + (SELECT Price FROM dbo.Products WHERE ProductID = 6)),
(1, '2023-03-10', (SELECT Price FROM dbo.Products WHERE ProductID = 7) + (SELECT Price FROM dbo.Products WHERE ProductID = 8) + (SELECT Price FROM dbo.Products WHERE ProductID = 9)),
(2, '2023-04-05', (SELECT Price FROM dbo.Products WHERE ProductID = 10) + (SELECT Price FROM dbo.Products WHERE ProductID = 11) + (SELECT Price FROM dbo.Products WHERE ProductID = 12)),
(1, '2023-05-01', (SELECT Price FROM dbo.Products WHERE ProductID = 13) + (SELECT Price FROM dbo.Products WHERE ProductID = 14) + (SELECT Price FROM dbo.Products WHERE ProductID = 15));


GO