Vanshika Tiwari 23100BTCSAII14332

Experiment
-1
Introduction to SQL
● SQL stands for Structured query language .
● It is a special purpose programming language designed for managing data held in a
relational DBMS.
● SQL was initially developed at IBM by Donald Chamberlin and Raymond Boyes in the
early 1970s this person initially called SEQUEL (Structured English Query Language).
● It is standard language for accessing and manipulating database.

Features of SQl
● It can be used by a range of users including those with little or no programming experience.
● It is non procedural language .
● It reduces the amount of time required for creating and manipulating system.
● It is an english like language.

Rules for SQl

● SQl starts with a verb (eg.SELECT).
● Each verb is followed by number of clauses(eg.FROM , WHERE , HAVING).
● A space separates clauses.
● A Comma separates parameters without a clause.
● A Semicolon is used for an Sql statement.
● Identifiers must starts with an alphabetic character . Character and date must be enclosed
within single quotes.

DataBase Creation tools

● MySql
● Oracle
● MS Sql server
● DB2
● Microsoft Access.

RDBMS
● Its stands for Relation DataBase Management.

1
Vanshika Tiwari 23100BTCSAII14332

Experiment
● It is the basis for SQL and for all modern database systems such as MySql , MS Sql server , DB2
, Microsoft Access.

DataBase Languages (Components of SQL )

DDL (Data Definition Language)

● It specify the structure of the database(Schema).
● DDl commands
■ CREATE
■ DROP
■ TRUNCATE
■ ALTER

DML (Data Manipulation Language)

● It is the component of the SQL that allow changing data within the databases .
● A data manipulation is
○ The retrieval of information stored in the database.
○ The insertion of new information into the database.
○ The deletion of information from the database.
○ The modification of the information into database.
● DML commands
■ INSERT
■ DELETE
■ UPDATE

DCL (Data Control Language)

● It is the component of sql statements that control access to data and to be database
● DCL commands
■ COMMIT
■ SAVEPOINT ■
ROLLBACK
■ GRANT
■ REVOKE

DQL (Data Query Language)

● It is component of sql statement that allow getting data from the
database and imposing orders upon it .

2
Vanshika Tiwari 23100BTCSAII14332

Experiment
● This command is the heart of the SQL ● DQL commands
■ SELECT
-2
DB Schema for E-Commerce Data Source Organisation
EBAY_APP

USER_DETAILS

User_Id Username Email Password Registration_Date Past_Customer

PRODUCT_DETAILS

Product_Id Seller_Id Title Category P_Rate Listing_Date

Discounted_
Price

PURCHASING_TABLE

Purchase_Id Buyer_Id Product_Id Quantity Purchase_Date Total_Cost

PAYMENT_DETAILS

3
Vanshika Tiwari 23100BTCSAII14332

Experiment

Payment_Id Purchase_Id Payment_method Transaction_ Transaction_Date

amount

DISCOUNT_TABLE

Discount_Id Product_Id Discount_Percent Start_Date End_Date

BRAND_OUTLETS

Brand_Id Brand_Name Contact_Number Website_Url

Headquarters_
Address

SELLING_TABLE

Selling_Id Seller_Id Product_Id Listing_Date Listing_Status

REVIEW_TABLE

4
Vanshika Tiwari 23100BTCSAII14332

Experiment

Review_Id Product_Id User_Id Rating Review_Date

-3
ER Diagram for DBMS Application

5
Vanshika Tiwari 23100BTCSAII14332

Experiment

Experiment -4
SQL Commands
6
Vanshika Tiwari 23100BTCSAII14332

Experiment
Aim: To study and execute various SQL commands for an eCommerce database of
an eBay-like application.

1. CREATE DATABASE Command

The CREATE DATABASE command is used to create a new database in SQL. A database is a
structured collection of data that allows efficient storage and retrieval of information.

Syntax:

CREATE DATABASE <database_name>;

Example:

CREATE DATABASE eBayDB;

This command creates a database named eBayDB.

To start working with this database, we need to select it using the USE command.

2. USE DATABASE Command

The USE command selects an existing database to work with.

Syntax:

USE <database_name>;

7
Vanshika Tiwari 23100BTCSAII14332

Example:

USE eBayDB;

This sets eBayDB as the active database, allowing all subsequent queries to be executed within
this database.

3. CREATE TABLE Command

The CREATE TABLE command is used to create tables within a database. A table consists of
columns, each with a specific data type and constraints.

Basic Syntax

CREATE TABLE TableName (

Column1 DataType Constraints,

Column2 DataType Constraints,

...

PRIMARY KEY (ColumnName),

FOREIGN KEY (ColumnName) REFERENCES AnotherTable(PrimaryKeyColumn)

1. Primary Key

8
Vanshika Tiwari 23100BTCSAII14332

A Primary Key is a column (or a set of columns) in a table that uniquely identifies each row in
that table. It ensures that the values in this column(s) are unique and not NULL.

Syntax for Primary Key

CREATE TABLE TableName (

Column1 DataType PRIMARY KEY,

Column2 DataType,

...

);

2. Foreign Key

A Foreign Key is a column (or a set of columns) in a table that establishes a relationship between
two tables. It refers to the Primary Key of another table, ensuring referential integrity.

Syntax for Foreign Key

CREATE TABLE TableName (

Column1 DataType,

Column2 DataType,

FOREIGN KEY (ColumnName) REFERENCES AnotherTable(PrimaryKeyColumn)

);

Example Tables:

USER_DETAILS Table

CREATE TABLE USER_DETAILS (

User_Id INT PRIMARY KEY,

Username VARCHAR(50),

Email VARCHAR(100) ,

9
Vanshika Tiwari 23100BTCSAII14332

Password VARCHAR(255),

Registration_Date INT,

Past_Customer BOOLEAN

);

This table stores details of registered users.

PRODUCT_DETAILS Table

CREATE TABLE PRODUCT_DETAILS (

Product_Id INT PRIMARY KEY,

Seller_Id INT,

Title VARCHAR(200),

Category VARCHAR(100),

P_Rate DECIMAL(10,2) CHECK (P_Rate > 0),

Listing_Date DATE,

Discounted_Price DECIMAL(10,2),

FOREIGN KEY (Seller_Id) REFERENCES USER_DETAILS(User_Id)

);

10
Vanshika Tiwari 23100BTCSAII14332

This table stores product listings with pricing and category information.

PURCHASING_TABLE

CREATE TABLE PURCHASING_TABLE (

Purchase_Id INT PRIMARY KEY,

Buyer_Id INT,

Product_Id INT,

Quantity INT CHECK (Quantity > 0),

Purchase_Date DATE,

Total_Cost DECIMAL(10,2) CHECK (Total_Cost > 0),

FOREIGN KEY (Buyer_Id) REFERENCES USER_DETAILS(User_Id),

FOREIGN KEY (Product_Id) REFERENCES PRODUCT_DETAILS(Product_Id)

);

11
Vanshika Tiwari 23100BTCSAII14332

This table records product purchases, linking buyers and purchased products.

PAYMENT_DETAILS Table

CREATE TABLE PAYMENT_DETAILS (

Payment_Id INT PRIMARY KEY,

Purchase_Id INT,

Payment_method VARCHAR(50) NOT NULL,

Transaction_amount DECIMAL(10,2) CHECK (Transaction_amount > 0),

Transaction_Date DATE,

FOREIGN KEY (Purchase_Id) REFERENCES PURCHASING_TABLE(Purchase_Id)

);

12
Vanshika Tiwari 23100BTCSAII14332

This table stores payment-related details for completed transactions.

DISCOUNT_TABLE

CREATE TABLE DISCOUNT_TABLE (

Discount_Id INT PRIMARY KEY,

Product_Id INT,

Discount_Percent DECIMAL(5,2) CHECK (Discount_Percent >= 0 AND Discount_Percent

<= 100),

Start_Date DATE,

End_Date DATE,

FOREIGN KEY (Product_Id) REFERENCES PRODUCT_DETAILS(Product_Id)

);

13
Vanshika Tiwari 23100BTCSAII14332

This table tracks product discounts with applicable time periods.

BRAND_OUTLETS Table

CREATE TABLE BRAND_OUTLETS (

Brand_Id INT PRIMARY KEY,

Brand_Name VARCHAR(100) NOT NULL,

Headquarters_Address TEXT,

Contact_Number VARCHAR(20),

Website_Url VARCHAR(255)

);

14
Vanshika Tiwari 23100BTCSAII14332

This table holds information about brands selling on the platform.

SELLING_TABLE

CREATE TABLE SELLING_TABLE (

Selling_Id INT PRIMARY KEY,

Seller_Id INT,

Product_Id INT,

Listing_Date DATE,

Listing_Status ENUM('Active', 'Inactive', 'Sold') DEFAULT 'Active',

FOREIGN KEY (Seller_Id) REFERENCES USER_DETAILS(User_Id),

FOREIGN KEY (Product_Id) REFERENCES PRODUCT_DETAILS(Product_Id)

);

15
Vanshika Tiwari 23100BTCSAII14332

This table manages product listings and their statuses.

REVIEW_TABLE

CREATE TABLE REVIEW_TABLE (

Review_Id INT PRIMARY KEY,

Product_Id INT,

User_Id INT,

Rating INT CHECK (Rating >= 1 AND Rating <= 5),

Review_Date DATE,

FOREIGN KEY (Product_Id) REFERENCES PRODUCT_DETAILS(Product_Id),

FOREIGN KEY (User_Id) REFERENCES USER_DETAILS(User_Id)

);

16
Vanshika Tiwari 23100BTCSAII14332

This table stores customer reviews for purchased products.

4. DESCRIBE Command

The DESCRIBE command is used to view the structure of a table, including column names, data
types, and constraints.

Syntax:

DESCRIBE <table_name>;

Example:

DESCRIBE USER_DETAILS;

17
Vanshika Tiwari 23100BTCSAII14332

Shows the structure of the USER_DETAILS table, including column names, data types, and
constraints.

DESCRIBE PRODUCT_DETAILS;

Displays the structure of the PRODUCT_DETAILS table with product-related attributes.

DESCRIBE PURCHASING_TABLE;

Shows the structure of the PURCHASING_TABLE, detailing purchases made by buyers.

18
Vanshika Tiwari 23100BTCSAII14332

DESCRIBE PAYMENT_DETAILS;

Displays the structure of the PAYMENT_DETAILS table, including transaction details.

DESCRIBE DISCOUNT_TABLE;

Shows the structure of the DISCOUNT_TABLE, detailing discounts applied to products.

19
Vanshika Tiwari 23100BTCSAII14332

DESCRIBE BRAND_OUTLETS;

Displays the structure of the BRAND_OUTLETS table, listing brand details.

DESCRIBE SELLING_TABLE;

Shows the structure of the SELLING_TABLE, managing product listings by sellers.

DESCRIBE REVIEW_TABLE;

20
Vanshika Tiwari 23100BTCSAII14332

Displays the structure of the REVIEW_TABLE, storing user reviews and ratings for products

5. INSERT Command

The INSERT command is used to add new records (rows) into a table. It allows you to specify
values for each column in the table.

Syntax:

INSERT INTO <table_name> (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

Example:
1. Insert into USER_DETAILS Table

INSERT INTO USER_DETAILS (User_Id, Username, Email, Password, Registration_Date,

Past_Customer)

VALUES

21
Vanshika Tiwari 23100BTCSAII14332

(1, 'john_doe', '[email protected]', 'password1', '2025-03-08', 1),

(2, 'jane_doe', '[email protected]', 'password2', '2025-03-08', 0);

(3, 'michael_b', '[email protected]', 'mikepass', '2025-03-22', 1),

(4, 'sarah_jones', '[email protected]', 'sarahpass', '2025-03-23', 0),

(5, 'david_wilson', '[email protected]', 'david123', '2025-03-24', 1);

This command inserts five new users into the USER_DETAILS table with their respective
details.

2. Insert into PRODUCT_DETAILS Table

INSERT INTO PRODUCT_DETAILS (Product_Id, Seller_Id, Title, Category, P_Rate,

Listing_Date, Discounted_Price)

VALUES

(101, 1, 'Smartphone X', 'Electronics', 699.99, '2025-03-05', 649.99),

(102, 2, 'Gaming Laptop', 'Computers', 999.99, '2025-03-06', 949.99),

22
Vanshika Tiwari 23100BTCSAII14332

(103, 3, 'Wireless Headphones', 'Accessories', 199.99, '2025-03-07', 179.99),

(104, 4, 'Smartwatch Pro', 'Wearables', 499.99, '2025-03-08', 479.99),

(105, 5, 'Bluetooth Speaker', 'Audio', 299.99, '2025-03-09', 279.99);

This command inserts five new products into the PRODUCT_DETAILS table with their
respective details.

3. Insert into PURCHASING_TABLE Table

INSERT INTO PURCHASING_TABLE (Purchase_Id, Buyer_Id, Product_Id, Quantity,

Purchase_Date, Total_Cost)

VALUES

(201, 2, 101, 1, '2025-03-09', 649.99),

23
Vanshika Tiwari 23100BTCSAII14332

(202, 3, 102, 1, '2025-03-10', 949.99), (203,

4, 103, 2, '2025-03-11', 359.98),

(204, 5, 104, 1, '2025-03-12', 479.99),

(205, 1, 105, 1, '2025-03-13', 279.99);

This command inserts five new purchase records into the PURCHASING_TABLE with their
respective details.

4. Insert into PAYMENT_DETAILS Table

INSERT INTO PAYMENT_DETAILS (Payment_Id, Purchase_Id, Payment_method,

Transaction_amount, Transaction_Date)

VALUES

(301, 201, 'Credit Card', 649.99, '2025-03-09'),

(302, 202, 'PayPal', 949.99, '2025-03-10'),

24
Vanshika Tiwari 23100BTCSAII14332

(303, 203, 'Debit Card', 359.98, '2025-03-11'),

(304, 204, 'UPI', 479.99, '2025-03-12'),

(305, 205, 'Net Banking', 279.99, '2025-03-13');

This command inserts five new payment records into the PAYMENT_DETAILS table with their
respective details.

5. Insert into DISCOUNT_TABLE Table

INSERT INTO DISCOUNT_TABLE (Discount_Id, Product_Id, Discount_Percent, Start_Date,

End_Date)

VALUES

(401, 101, 7.14, '2025-03-01', '2025-03-30'),

(402, 102, 5.00, '2025-03-02', '2025-03-31'),

25
Vanshika Tiwari 23100BTCSAII14332

(403, 103, 10.00, '2025-03-03', '2025-04-01'),

(404, 104, 4.00, '2025-03-04', '2025-04-02'),

(405, 105, 6.67, '2025-03-05', '2025-04-03');

This command inserts five new discount records into the DISCOUNT_TABLE table with their
respective details.

6. Insert into BRAND_OUTLETS Table

INSERT INTO BRAND_OUTLETS (Brand_Id, Brand_Name, Headquarters_Address,

Contact_Number, Website_Url)

VALUES

(501, 'Apple', 'Cupertino, CA, USA', '+1-800-692-7753', 'https://www.apple.com'),

(502, 'Samsung', 'Suwon, South Korea', '+82-2-2255-0114', 'https://www.samsung.com'),

(503, 'Sony', 'Tokyo, Japan', '+81-3-6748-2111', 'https://www.sony.com'),

(504, 'Microsoft', 'Redmond, WA, USA', '+1-800-642-7676', 'https://www.microsoft.com'),

26
Vanshika Tiwari 23100BTCSAII14332

(505, 'Google', 'Mountain View, CA, USA', '+1-650-253-0000', 'https://www.google.com');

This command inserts five new brand outlet records into the BRAND_OUTLETS table with their
respective details.

7. Insert into SELLING_TABLE Table

INSERT INTO SELLING_TABLE (Selling_Id, Seller_Id, Product_Id, Listing_Date,

Listing_Status)

VALUES

(601, 1, 101, '2025-03-08', 'Active'),

(602, 2, 102, '2025-03-09', 'Active'),

(603, 3, 103, '2025-03-10', 'Inactive'),

(604, 4, 104, '2025-03-11', 'Active'),

(605, 5, 105, '2025-03-12', 'Inactive');

27
Vanshika Tiwari 23100BTCSAII14332

This command inserts five new selling records into the SELLING_TABLE table with their
respective details.

8. Insert into REVIEW_TABLE Table

INSERT INTO REVIEW_TABLE (Review_Id, Product_Id, User_Id, Rating, Review_Date)

VALUES

(701, 101, 2, 4.5, '2025-03-10'),

(702, 102, 3, 5.0, '2025-03-11'),

(703, 103, 4, 3.8, '2025-03-12'),

(704, 104, 5, 4.2, '2025-03-13'),

(705, 105, 1, 4.0, '2025-03-14');

28
Vanshika Tiwari 23100BTCSAII14332

This command inserts five new review records into the REVIEW_TABLE table with their
respective details.

6. SELECT Command

The SELECT command is used to retrieve data from a table. Using SELECT *, you can fetch all
columns and records from a specified table.

Syntax:

SELECT * FROM <table_name>;

Example:
A . Retrieve all rows and all columns :

(Fetch everything from a table)

1. Retrieve All User Details

SELECT * FROM USER_DETAILS;

29
Vanshika Tiwari 23100BTCSAII14332

This command retrieves all records and columns from the USER_DETAILS table, displaying
user details such as User_Id, Username, Email, Password, Registration_Date, and
Past_Customer.

2. Retrieve All Product Details

SELECT * FROM PRODUCT_DETAILS;

This command retrieves all records from the PRODUCT_DETAILS table, showing details like
Product_Id, Seller_Id, Title, Category, P_Rate, Listing_Date, and Discounted_Price.

30
Vanshika Tiwari 23100BTCSAII14332

3. Retrieve All Purchase Records

SELECT * FROM PURCHASING_TABLE;

Description: Retrieves purchase details including Purchase_Id, Buyer_Id, Product_Id, Quantity,

Purchase_Date, and Total_Cost.

4. Retrieve All Payment Details

SELECT * FROM PAYMENT_DETAILS;

Description: Retrieves payment records including Payment_Id, Purchase_Id, Payment_method,

Transaction_amount, and Transaction_Date.

31
Vanshika Tiwari 23100BTCSAII14332

5. Retrieve All Discount Records

SELECT * FROM DISCOUNT_TABLE;

Description: Retrieves discounts with Discount_Id, Product_Id, Discount_Percent, Start_Date,

and End_Date.

6. Retrieve All Brand Outlets

SELECT * FROM BRAND_OUTLETS;

Description: Retrieves brand information including Brand_Id, Brand_Name,

Headquarters_Address, Contact_Number, and Website_Url.

7. Retrieve All Selling Records

SELECT * FROM SELLING_TABLE;

32
Vanshika Tiwari 23100BTCSAII14332

Description: Retrieves product listings including Selling_Id, Seller_Id, Product_Id,

Listing_Date, and Listing_Status.

8. Retrieve All Product Reviews

SELECT * FROM REVIEW_TABLE;

Description: Retrieves product reviews including Review_Id, Product_Id, User_Id, Rating, and
Review_Date.

B . Retrieve selected columns and all rows :

(Fetch only specific columns for all records in the table)

33
Vanshika Tiwari 23100BTCSAII14332

1. Retrieve Username and Registration_Date from USER_DETAILS

SELECT Username, Registration_Date FROM USER_DETAILS;

Table: USER_DETAILS
Description: Fetches only Username and Registration_Date for all users.

2. Retrieve Product_Id and P_Rate from PRODUCT_DETAILS

SELECT Product_Id, P_Rate FROM PRODUCT_DETAILS;

Table: PRODUCT_DETAILS
Description: Fetches only Product_Id and P_Rate for all products.

34
Vanshika Tiwari 23100BTCSAII14332

C . Retrieve selected rows and all columns :

(Fetch only specific records but show all columns)

1. Retrieve all details of purchases made after 2025-03-15 from

PURCHASING_TABLE

SELECT * FROM PURCHASING_TABLE WHERE Purchase_Date > '2025-03-09';

Table: PURCHASING_TABLE
Description: Fetches all purchase details where Purchase_Date is after 2025-03-15.

2. Retrieve all details of discounts greater than 15% from DISCOUNT_TABLE

SELECT * FROM DISCOUNT_TABLE WHERE Discount_Percent > 6;

35
Vanshika Tiwari 23100BTCSAII14332

Table: DISCOUNT_TABLE
Description: Fetches all columns for discounts where Discount_Percent is greater than
15%.

D . Retrieve selected columns and selected rows :

(Fetch specific columns for specific records)

36
Vanshika Tiwari 23100BTCSAII14332

1. Retrieve Payment_method and Transaction_amount for transactions greater than 500

from PAYMENT_DETAILS

SELECT Payment_method, Transaction_amount FROM PAYMENT_DETAILS WHERE

Transaction_amount > 500;

Table: PAYMENT_DETAILS
Description: Fetches only Payment_method and Transaction_amount where Transaction_amount
is greater than 500.

2. Retrieve Review_Id and Rating for products rated 4 or higher from

REVIEW_TABLE

SELECT Review_Id, Rating FROM REVIEW_TABLE WHERE Rating >= 4;

37
Vanshika Tiwari 23100BTCSAII14332

Table: REVIEW_TABLE
Description: Fetches only Review_Id and Rating where the rating is 4 or higher.

38