Scribd
Upload
12 views7 pages

Advanced SQL Scripts

The document outlines SQL scripts for creating a database named 'Training_DB' and its associated tables, including 'customers', 'orders', 'order_items', 'products', 'employees', and 'departments'. It also includes insertion scripts to populate these tables with sample data. The database structure supports a typical e-commerce scenario with relationships between customers, orders, and products.

Uploaded by

ganeshchandra.cs.it
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
12 views7 pages

Advanced SQL Scripts

The document outlines SQL scripts for creating a database named 'Training_DB' and its associated tables, including 'customers', 'orders', 'order_items', 'products', 'employees', and 'departments'. It also includes insertion scripts to populate these tables with sample data. The database structure supports a typical e-commerce scenario with relationships between customers, orders, and products.

Uploaded by

ganeshchandra.cs.it
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

/* Create a database name Training_DB*/

USE Master
GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Training_DB')


BEGIN
CREATE DATABASE Training_DB
ON PRIMARY
(
NAME = TrainingData,
FILENAME = 'D:\DatabaseFiles\TrainingData.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2MB
),
(
NAME = TrainingData1,
FILENAME = 'D:\DatabaseFiles\TrainingData1.ndf',
SIZE = 100MB,
MAXSIZE = 200MB,
FILEGROWTH = 2MB
),
FILEGROUP TrainingGroup
(
NAME = TrainingData2,
FILENAME = 'E:\DataFiles\TrainingData2.ndf',
SIZE = 100MB,
MAXSIZE = 200MB,
FILEGROWTH = 2MB
)
LOG ON
(
NAME = TrainingLog,
FILENAME = 'D:\DatabaseFiles\TrainingLog.ldf',
SIZE = 100MB,
MAXSIZE = 200MB,
FILEGROWTH = 2MB
)
END
GO

/* Create a table customers */


USE Training_DB
GO

IF NOT EXISTS (
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'customers'
AND TABLE_SCHEMA = 'dbo'
)
BEGIN
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
birth_date DATE,
city VARCHAR(50),
country VARCHAR(50),
active_customer BIT -- Use BIT instead of BOOLEAN
)
END
GO

/* Create a table orders */


Use Training_DB
Go
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='orders'
AND TABLE_SCHEMA='Dbo')
Begin
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2),
order_status VARCHAR(20),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
)
End
Go

/* Create a table order_items */


Use Training_DB
Go
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='order_items'
AND TABLE_SCHEMA='Dbo')
Begin
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity
INT,
unit_price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(order_id)

)
End
Go

/* create a table products */


Use Training_DB
Go
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='products' AND TABLE_SCHEMA='Dbo')
Begin
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
unit_price DECIMAL(10,2),
units_in_stock INT
)
End
GO

/* create a table employees */


Use Training_DB
Go
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='employees' AND TABLE_SCHEMA='Dbo')
Begin
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
job_title VARCHAR(50),
department_id INT,
salary DECIMAL(10,2)
)
End
Go

/* create a table departments */


Use Training_DB
Go
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='departments' AND TABLE_SCHEMA='Dbo')
Begin
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
End
Go

--Insertion Scripts:
--customers
USE Training_DB
GO

INSERT INTO [dbo].[customers](customer_id, first_name, last_name, email, birth_date, city, country,


active_customer)
VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '1980-01-15', 'New York', 'USA', 1),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '1985-02-20', 'Los Angeles', 'USA', 1),
(3, 'Michael', 'Johnson', 'michael.johnson@example.com', '1990-03-10', 'Chicago', 'USA', 1),
(4, 'Emily', 'Davis', 'emily.davis@example.com', '1975-04-05', 'Houston', 'USA', 1),
(5, 'William', 'Brown', 'william.brown@example.com', '1982-05-25', 'Phoenix', 'USA', 1),
(6, 'Sophia', 'Martinez', 'sophia.martinez@example.com', '1988-06-30', 'Philadelphia', 'USA', 1),
(7, 'James', 'Garcia', 'james.garcia@example.com', '1979-07-10', 'San Antonio', 'USA', 1),
(8, 'Olivia', 'Rodriguez', 'olivia.rodriguez@example.com', '1986-08-15', 'San Diego', 'USA', 1),
(9, 'Daniel', 'Hernandez', 'daniel.hernandez@example.com', '1992-09-20', 'Dallas', 'USA', 1),
(10, 'Ava', 'Lopez', 'ava.lopez@example.com', '1983-10-25', 'San Jose', 'USA', 1),
(11, 'Jacob', 'Gonzalez', 'jacob.gonzalez@example.com', '1995-11-30', 'Austin', 'USA', 1),
(12, 'Mia', 'Wilson', 'mia.wilson@example.com', '1984-12-10', 'Jacksonville', 'USA', 1),
(13, 'Matthew', 'Anderson', 'matthew.anderson@example.com', '1978-01-20', 'San Francisco', 'USA',
1),
(14, 'Charlotte', 'Thomas', 'charlotte.thomas@example.com', '1993-02-15', 'Columbus', 'USA', 1),
(15, 'David', 'Taylor', 'david.taylor@example.com', '1981-03-25', 'Fort Worth', 'USA', 1),
(16, 'Amelia', 'Moore', 'amelia.moore@example.com', '1991-04-30', 'Indianapolis', 'USA', 1),
(17, 'Joseph', 'Jackson', 'joseph.jackson@example.com', '1987-05-15', 'Charlotte', 'USA', 1),
(18, 'Evelyn', 'White', 'evelyn.white@example.com', '1976-06-25', 'San Francisco', 'USA', 1),
(19, 'Christopher', 'Harris', 'christopher.harris@example.com', '1989-07-30', 'Seattle', 'USA', 1),
(20, 'Abigail', 'Clark', 'abigail.clark@example.com', '1990-08-10', 'Denver', 'USA', 1);
GO

--orders
USE Training_DB
GO

INSERT INTO [Dbo].[orders](order_id, customer_id, order_date, total_amount, order_status)


VALUES
(1, 1, '2024-01-01', 150.00, 'Shipped'),
(2, 2, '2024-01-02', 200.00, 'Shipped'),
(3, 3, '2024-01-03', 300.00, 'Pending'),
(4, 4, '2024-01-04', 120.00, 'Shipped'),
(5, 5, '2024-01-05', 180.00, 'Canceled'),
(6, 6, '2024-01-06', 220.00, 'Shipped'),
(7, 7, '2024-01-07', 250.00, 'Shipped'),
(8, 8, '2024-01-08', 350.00, 'Pending'),
(9, 9, '2024-01-09', 275.00, 'Shipped'),
(10, 10, '2024-01-10', 400.00, 'Shipped'),
(11, 11, '2024-01-11', 450.00, 'Shipped'),
(12, 12, '2024-01-12', 500.00, 'Pending'),
(13, 13, '2024-01-13', 275.00, 'Shipped'),
(14, 14, '2024-01-14', 325.00, 'Shipped'),
(15, 15, '2024-01-15', 200.00, 'Canceled'),
(16, 16, '2024-01-16', 425.00, 'Shipped'),
(17, 17, '2024-01-17', 150.00, 'Pending'),
(18, 18, '2024-01-18', 275.00, 'Shipped'),
(19, 19, '2024-01-19', 500.00, 'Shipped'),
(20, 20, '2024-01-20', 600.00, 'Shipped');
GO

--order_items
USE Training_DB
GO

INSERT INTO [Dbo].[order_items] (order_item_id, order_id, product_id, quantity, unit_price)


VALUES
(1, 1, 1, 2, 50.00),
(2, 2, 2, 1, 200.00),
(3, 3, 3, 3, 100.00),
(4, 4, 4, 1, 120.00),
(5, 5, 5, 2, 90.00),
(6, 6, 6, 3, 70.00),
(7, 7, 7, 1, 250.00),
(8, 8, 8, 4, 80.00),
(9, 9, 9, 2, 120.00),
(10, 10, 10, 5, 80.00),
(11, 11, 1, 2, 50.00),
(12, 12, 2, 1, 200.00),
(13, 13, 3, 3, 100.00),
(14, 14, 4, 1, 120.00),
(15, 15, 5, 2, 90.00),
(16, 16, 6, 3, 70.00),
(17, 17, 7, 1, 250.00),
(18, 18, 8, 4, 80.00),
(19, 19, 9, 2, 120.00),
(20, 20, 10, 5, 80.00);
GO

--products
USE Training_DB
GO

INSERT INTO [Dbo].[products] (product_id, product_name, category, unit_price, units_in_stock)


VALUES
(1, 'Laptop', 'Electronics', 800.00, 50),
(2, 'Smartphone', 'Electronics', 600.00, 100),
(3, 'Tablet', 'Electronics', 300.00, 200),
(4, 'Headphones', 'Accessories', 150.00, 150),
(5, 'Smartwatch', 'Wearables', 250.00, 100),
(6, 'Monitor', 'Electronics', 200.00, 75),
(7, 'Keyboard', 'Accessories', 50.00, 300),
(8, 'Mouse', 'Accessories', 25.00, 500),
(9, 'Charger', 'Accessories', 20.00, 600),
(10, 'USB Cable', 'Accessories', 10.00, 1000),
(11, 'Gaming Console', 'Electronics', 400.00, 30),
(12, 'Bluetooth Speaker', 'Accessories', 100.00, 200),
(13, 'External Hard Drive', 'Storage', 80.00, 150),
(14, 'Memory Card', 'Storage', 30.00, 400),
(15, 'Camera', 'Electronics', 500.00, 40),
(16, 'Tripod', 'Accessories', 75.00, 100),
(17, 'Printer', 'Electronics', 120.00, 50),
(18, 'Scanner', 'Electronics', 100.00, 50),
(19, 'Projector', 'Electronics', 350.00, 25),
(20, 'Webcam', 'Accessories', 60.00, 80);
GO

--employees
USE Training_DB
GO

INSERT INTO [Dbo].[employees] (employee_id, first_name, last_name, hire_date, job_title,


department_id, salary)
VALUES
(1, 'John', 'Doe', '2020-01-01', 'Manager', 1, 75000.00),
(2, 'Jane', 'Smith', '2019-02-15', 'Sales Representative', 2, 55000.00),
(3, 'Michael', 'Johnson', '2018-03-10', 'Developer', 3, 70000.00),
(4, 'Emily', 'Davis', '2020-04-05', 'Analyst', 4, 65000.00),
(5, 'William', 'Brown', '2017-05-20', 'HR Specialist', 5, 60000.00),
(6, 'Sophia', 'Martinez', '2016-06-15', 'IT Support', 3, 50000.00),
(7, 'James', 'Garcia', '2021-07-01', 'Marketing Specialist', 6, 55000.00),
(8, 'Olivia', 'Rodriguez', '2019-08-20', 'Designer', 7, 60000.00),
(9, 'Daniel', 'Hernandez', '2018-09-10', 'Accountant', 8, 65000.00),
(10, 'Ava', 'Lopez', '2017-10-25', 'Data Scientist', 4, 80000.00),
(11, 'Jacob', 'Gonzalez', '2016-11-15', 'Consultant', 2, 75000.00),
(12, 'Mia', 'Wilson', '2020-12-05', 'Project Manager', 1, 85000.00),
(13, 'Matthew', 'Anderson', '2021-01-15', 'Software Engineer', 3, 75000.00),
(14, 'Charlotte', 'Thomas', '2019-02-20', 'Business Analyst', 4, 70000.00),
(15, 'David', 'Taylor', '2018-03-30', 'QA Engineer', 3, 65000.00),
(16, 'Amelia', 'Moore', '2017-04-10', 'Legal Advisor', 9, 90000.00),
(17, 'Joseph', 'Jackson', '2021-05-05', 'Trainer', 10, 50000.00),
(18, 'Evelyn', 'White', '2020-06-20', 'Recruiter', 5, 60000.00),
(19, 'Christopher', 'Harris', '2019-07-25', 'Financial Analyst', 8, 70000.00),
(20, 'Abigail', 'Clark', '2018-08-30', 'Operations Manager', 1, 80000.00);
GO

--departments
USE Training_DB
GO

INSERT INTO [Dbo].[departments] (department_id, department_name)


VALUES
(1, 'Management'),
(2, 'Sales'),
(3, 'IT'),
(4, 'Analytics'),
(5, 'HR'),
(6, 'Marketing'),
(7, 'Design'),
(8, 'Finance'),
(9, 'Legal'),
(10, 'Training');
GO

You might also like