ORGANIC SHOP SALES INVENTORY DATABASE MANAGEMENT SYSTEM

DBMS PROJECT WORK SUBMITTED TO PSGR KRISHNAMMAL COLLEGE

FOR WOMEN IN PARTIAL FULFILLMENT OF THE REQUIREMENTS
FOR THE AWARD OF THE DEGREE OF
BACHELOR OF COMPUTER APPLICATIONS
OF BHARATHIAR UNIVERSITY,
COIMBATORE – 641 046.

Submitted by

KAVIYUGA.M.V (22BCA053)

Guided by,

Dr.R.HEPZIBA GNANAMALAR MCA., Ph.D.,

Assistant Professor, Department of BCA,
PSGR Krishnammal College for Women,
Coimbatore – 641 004.

DEPARTMENT OF BCA
PSGR KRISHNAMMAL COLLEGE FOR WOMEN
College of Excellence
An Autonomous Institution, Affiliated to Bharathiyar University
Accredited with ‘A++’ Grade by NACC, An ISO 9001:2015 Certified Institution
Peelamedu, Coimbatore-641004.
November 2023
 CERTIFICATE

This is to certify that this DBMS project entitled “ORGANIC SHOP SALES INVENTORY
DATABASE MANAGEMENT SYSTEM” Submitted to PSGR Krishnammal College for
Women, Coimbatore in partial fulfillment of the requirement for the award of the Degree of
Bachelor of Computer Applications is a record of original work done by KAVIYUGA.M.V
(22BCA053) during her period of study in Department of BCA, PSGR Krishnammal College
for Women, Coimbatore under my supervision and guidance and her DBMS Project has not
formed the basis for the award of any Degree/ Diploma/ Associate/ Fellowship or similar title
to any candidate of any university.

Forwarded by

_________________________ ____________________________
Faculty Guide Head of the Department
Dr. R. HEPZIBA GNANAMALAR Mrs. K. GEETHALAKSHMI
MCA., Ph.D. MCA., M Phil., B.Ed., (Ph.D.)

Submitted for University Examination held on

________________ ________________
Internal Examiner External Examiner
 DECLARATION

I hereby declare that the DBMS project entitled “ORGANIC SHOP SALES INVENTORY
DATABASE MANAGEMENT SYSTEM” submitted to PSGR Krishnammal College for
Women, Coimbatore for the award of the Degree of Bachelor of Computer Application is a
record of original work done by KAVIYUGA.M.V (22BCA053) under the guidance of
Dr.R.HEPZIBA GNANAMALAR MCA., Ph.D., Assistant Professor, Department of
BCA, PSGR Krishnammal College for Women, Coimbatore and this project have not found
the basis for the award of any Degree/Diploma or similar title to any candidate of any
university.

Place: Coimbatore KAVIYUGA.M.V (22BCA053)

Date:

Endorsed by

Place: Coimbatore Dr.R.HEPZIBA GNANAMALAR MCA., (Ph.D.)

Date: (Faculty guide)
 TABLE OF CONTENTS

S No TITLE Page No
1 CASE STUDY

1.1. Abstract
1.2. Problem Definition
1.3. Objective
1.4. Table Description

2 NORMALIZATION

2.1. Entities and attributes

2.2. Cardinality and Relationships
2.3. Normal Forms Types
2.4. Normalized Tables

3 ER DIAGRAM

3.1 Primary Key Definition

3.2 Foreign Key Definition
3.3 Composite Key Definition
3.4 Composite attributes
3.5 Simple attributes
3.6 Single valued attributes
3.7 Multi valued attributes
3.8 Derived attributes
3.9 Stored attributes
3.10 Complex attributes
3.11 Null value attributes
3.12 Key attributes
3.13 Value set of attributes

4 DATA DEFINITION LANGUAGE

4.1. Create
4.2. Alter
4.3. Drop
4.4. Truncate
4.5. Rename

5 DATA MANIPULATION LANGUAGE

5.1. Select
5.2. Insert
5.3. Update
5.4. Delete

6 DATA CONTROL LANGUAGE

6.1. Grant
6.2. Revoke
7 TRANSACTION CONTROL LANGUAGE

7.1. Commit
7.2. Rollback
7.3. Savepoint

8 DATA INTEGRITY CONSTRAINTS

8.1. Primary Key

8.2. Foreign Key
8.3. Not Null
8.4. Unique
8.5. Check

9 AGGREGATE FUNCTIONS AND SORTING

9.1. Avg()
9.2. Count()
9.3. Sum()
9.4. Min()
9.5. Max()
9.6. Asc
9.7. Desc

10 JOINS
10.1. Join
10.2. Left Inner Join
10.3. Right Inner Join
10.4. Full Join
10.5. Left Outer Join
10.6. Right Outer Join
10.7. Full Outer Join

11 SUB QUERIES

11.1. Creating A Table

11.2. Inserting Values In A Table
11.3. Select, Displaying Values In A Table
11.4. Using Where Clause
11.5. Using Like Operator
11.6. Updating A Row
11.7. Aggregate Functions With Select
11.8. Deleting A Row

12 CONCLUSION

12.1 Further Scope Of The Project

12.2 Bibliography
 1. Database Management System for
Sales Inventory Tracking : A Case Study

1.1. ABSTRACT :

Pure Petals is a retail company specializing in organic and natural products including essential
oils and plant-based skincare, promoting the use of chemical-free and sustainable products that
nurture both the body and the environment. The company has been operating for several years
and has a significant customer base.

1.2. PROBLEM DEFINITION :

Pure Petals is facing challenges in efficiently managing its vast inventory. The current order
processing system is manual and error-prone. Accessing information from physical books was
time-consuming, especially when searching for specific data. It required manual browsing
through pages, making data retrieval slow and inefficient. Physical books are susceptible to
damage, loss, or theft, posing significant data security risks.

SOLUTION :

They need a database system to track product quantities and ensure real-time updates to prevent
overselling or stockouts. They want to implement a digital order management system to
streamline the process, and handle high volumes of orders. Pure Petals decides to develop a
database management system to address the mentioned challenges and enhance their overall
operations. The database will be designed using a relational model with appropriate tables and
relationships.
Thus, the company will experience improved inventory management and streamlined order
processing.
1.3. OBJECTIVES :

a. Efficient Inventory Management: Design and implement a database system that enables
Pure Petals to efficiently track and manage its vast inventory of organic and natural products.
The system should provide real-time updates on stock levels to prevent overselling or
stockouts.

b. Enhanced Data Retrieval: Replace the manual data retrieval process from physical books
with a digital database system that allows quick and efficient data access. The system should
enable users to search, retrieve, and update information with ease.

c. Data Security and Integrity: Implement robust data security measures to safeguard
sensitive customer and business information. Ensure data integrity to prevent data corruption
or unauthorized modifications.

d. Real-time Inventory Updates: Enable real-time updates of inventory levels as products are
sold or restocked. This feature will provide accurate stock information and reduce the risk of
stockouts or overstocking.

e. User-friendly Interface: Design an intuitive and user-friendly interface that allows easy
navigation and data entry. The system should be accessible to non-technical users within the
organization.

By achieving these objectives, the project aims to empower Pure Petals with a modern and
efficient database management system, resulting in improved inventory management,
streamlined order processing, enhanced data security, and a more seamless and rewarding
customer experience.
1.4. TABLE DESCRIPTION :

CUSTOMER TABLE
The "CUSTOMER" table stores customer data, including CID (primary key), NAME, MAIL,
PHONE, CITY, COUNTRY, and PINCODE columns. It uniquely identifies each customer
using CID and ensures mandatory information is provided for NAME, PHONE, and
PINCODE.

SUPPLIER TABLE
The "SUPPLIER" table stores supplier data, including SID (primary key), S_NAME, S_MAIL,
S_PHONE, and S_CITY columns. It uniquely identifies each supplier using SID and ensures
mandatory information is provided for S_NAME and S_PHONE.

PRODUCT TABLE
The "PRODUCT" table stores product data, including PID (primary key), SID (foreign key
referencing SUPPLIER table), P_NAME, BRAND, PRICE, and STOCK columns. It ensures
data integrity by linking products to their respective suppliers (SID) and requires mandatory
information for P_NAME and STOCK.
ORDERS TABLE
The "ORDERS" table records order data, including OID (primary key), CID (foreign key
referencing CUSTOMER table), PID (foreign key referencing PRODUCT table), QTY, TAX,
and TOTAL columns. It ensures data integrity by linking orders to customers (CID) and
products (PID) and requires non-null values for QTY and TOTAL.

TRANSACTION TABLE
The "TRANSACTION" table stores transaction data, including TID (primary key), OID
(foreign key referencing ORDERS table), DELIVERY_STATUS, TRACKING_NO,
PAYMENT_MODE, P_DATE, and REVIEW columns. It ensures data integrity by linking
transactions to orders (OID) and requires a non-null value for the REVIEW.
 2. NORMALIZATION

Normalisation refers to the database design process that aims to reduce data redundancy and
improve data integrity by organizing data into related tables. The process involves multiple
normal forms (e.g., 1NF, 2NF, 3NF) that address specific issues related to data redundancy and
dependency.

2.1. ENTITIES AND ATTRIBUTES :

i. CUSTOMER Table
- CID (Primary Key)
- NAME
- MAIL
- PHONE
- CITY
- COUNTRY
- PINCODE

ii. SUPPLIER Table

- SID (Primary Key)
- S_NAME
- S_MAIL
- S_PHONE
- S_CITY

iii. PRODUCT Table

- PID (Primary Key)
- SID (Foreign Key, SUPPLIER table)
- P_NAME
- BRAND
- PRICE
- STOCK
 iv. ORDERS Table
- OID (Primary Key)
- CID (Foreign Key, CUSTOMER table)
- PID (Foreign Key, PRODUCT table)
- QTY
- TAX
- TOTAL
- O_DATE

v. TRANSACTION Table
- TID (Primary Key)
- OID (Foreign Key, ORDERS table)
- DELIVERY_STATUS
- TRACKING_NO
- PAYMENT_MODE
- P_DATE
- REVIEW

2.2. CARDINALITY AND RELATIONSHIPS :

CUSTOMER Table
Cardinality: One-to-Many (1:N)
Relationship: Each customer (CID) can place multiple orders, but each order is associated
with only one customer.

SUPPLIER Table
Cardinality: One-to-Many (1:N)
Relationship: Each supplier (SID) can supply multiple products, but each product is
associated with only one supplier.
PRODUCT Table
Cardinality: Many-to-One (N:1)
Relationship: Each product (PID) can be supplied by only one supplier, but each supplier can
supply multiple products.

ORDERS Table
Cardinality: Many-to-One (N:1) for both CUSTOMER and PRODUCT tables
Relationships: Each order (OID) is placed by one customer (CID), but each customer can
place multiple orders.
Each order (OID) contains details of one product (PID), but each product can be part of
multiple orders.

TRANSACTION Table:
Cardinality: One-to-One (1:1) with the ORDERS table
Relationship: Each transaction (TID) corresponds to one order (OID), and each order can
have only one associated transaction.

2.3. NORMAL FORM TYPES :

i. First Normal Form (1NF)

- Each column should contain only atomic values.

ii. Second Normal Form (2NF)

- The table should be in 1NF.

- There should be no partial dependencies and all non-key attributes
depend on the entire primary key.

iii. Third Normal Form (3NF)

- The table should be in 2NF.

- There should be no transitive dependencies and all non-key
attributes should depend directly on the primary key.
2.4. NORMALIZED TABLES :

CUSTOMER TABLE – 3NF

SUPPLIER TABLE – 3NF

PRODUCT TABLE – 2NF

ORDERS TABLE – 3NF

TRANSACTION TABLE – 3NF

3. ER DIAGRAM
3.1. Primary Key

CUSTOMER TABLE : CID - Customer ID

SUPPLIER TABLE : SID - Supplier ID
PRODUCT TABLE : PID - Product ID
ORDER TABLE : OID - Order ID
TRANSACTION : TID - Transaction ID

3.2. Foreign Key

CUSTOMER TABLE : NIL

SUPPLIER TABLE : NIL
PRODUCT TABLE : SID - Supplier ID
ORDER TABLE : CID - Customer ID, PID - Product ID
TRANSACTION : OID - Order ID

3.3. Composite Key

CUSTOMER TABLE : CID - Customer ID

SUPPLIER TABLE : SID - Supplier ID
PRODUCT TABLE : PID - Product ID
ORDER TABLE : OID - Order ID
TRANSACTION : TID - Transaction ID

3.4. Composite Attributes : NIL

3.4. Simple Attributes

CUSTOMER TABLE : NAME, MAIL, PHONE, CITY, COUNTRY, PINCODE

SUPPLIER TABLE : S_NAME, S_MAIL, S_PHONE, S_CITY
PRODUCT TABLE : P_NAME, BRAND, PRICE, STOCK
ORDER TABLE : QTY, TAX, TOTAL, O_DATE
TRANSACTION : DELIVERY_STATUS,TRACKING_NO,
PAYMENT_MODE,P_DATE,REVIEW

3.5. Single Valued Attributes

CUSTOMER TABLE : CID, NAME, MAIL, PHONE, CITY, COUNTRY, PINCODE

SUPPLIER TABLE : SID, S_NAME, S_MAIL, S_PHONE, S_CITY
PRODUCT TABLE : PID, SID, P_NAME, BRAND, PRICE, STOCK
ORDER TABLE : OID, CID, PID, QTY, TAX, TOTAL, O_DATE
TRANSACTION : TID, OID, DELIVERY_STATUS, TRACKING_NO,
PAYMENT_MODE, P_DATE, REVIEW

3.7. Multi Valued Attributes : NIL

3.8. Derived Attributes : NIL

3.9. Stored Attributes

CUSTOMER TABLE : CID, NAME, MAIL, PHONE, CITY, COUNTRY, PINCODE

SUPPLIER TABLE : SID, S_NAME, S_MAIL, S_PHONE, S_CITY
PRODUCT TABLE : PID, SID, P_NAME, BRAND, PRICE, STOCK
ORDER TABLE : OID, CID, PID, QTY, TAX, TOTAL, O_DATE
TRANSACTION : TID, OID, DELIVERY_STATUS, TRACKING_NO,
PAYMENT_MODE, P_DATE, REVIEW

3.10. Complex Attributes : NIL

3.11. Null Value Attributes

CUSTOMER TABLE : MAIL - CUSTOMER MAIL

SUPPLIER TABLE : S_MAIL - SUPPLIER MAIL
PRODUCT TABLE : NIL
ORDER TABLE : NIL
TRANSACTION : TRACKING_NO

3.12. Key Attributes

CUSTOMER TABLE : CID - Customer ID

SUPPLIER TABLE : SID - Supplier ID
PRODUCT TABLE : PID - Product ID
ORDER TABLE : OID - Order ID
TRANSACTION : TID - Transaction ID

3.13. Value Set of Attributes

CUSTOMER TABLE : CITY, COUNTRY, PINCODE

SUPPLIER TABLE : S_CITY - SUPPLIER CITY
PRODUCT TABLE : BRAND
ORDER TABLE : TAX
TRANSACTION : REVIEW
 4. DATA DEFINITION LANGUAGE

DDL is a subset of SQL used to define and manage the structure of a database,
including creating, altering, and deleting database objects like tables, indexes, and
constraints.

4.1. CREATE : WRITE QUERY TO CREATE A TABLE NAMED “CUSTOMER_INFO”

THAT RECORDS THE INFORMATION ABOUT THE CUSTOMER.

mysql> CREATE TABLE CUSTOMER_INFO ( CID VARCHAR(20) PRIMARY

KEY, NAME VARCHAR(20) NOT NULL, MAIL VARC0HAR(50), PHONE INT
UNIQUE, CITY VARCHAR(20), COUNTRY VARCHAR(20), PINCODE INT NOT
NULL);
Query OK, 0 rows affected (0.02 sec)

4.2. ALTER : WRITE A QUERY TO ADD A NEW COLUMN “GENDER” TO THE

CUSTOMER_INFO TABLE.

mysql> ALTER TABLE CUSTOMER_INFO ADD GENDER VARCHAR(20);

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

4.3. RENAME : WRITE A QUERY TO CHANGE THE TABLE NAME FROM

“CUSTOMER_INFO” TO “CUSTOMER_DETAILS”.

mysql> ALTER TABLE CUSTOMER_INFO RENAME TO

CUSTOMER_DETAILS;
Query OK, 0 rows affected (0.01 sec)

4.4. TRUNCATE : WRITE A QUERY TO TRUNCATE THE VALUES STORED IN THE

“CUSTOMER_DETAILS” TABLE.

mysql> TRUNCATE CUSTOMER_DETAILS;

Query OK, 0 rows affected (0.01 sec)

4.5. DROP : WRITE A QUERY TO DROP THE TABLE.

mysql> DROP TABLE CUSTOMER_DETAILS;

Query OK, 0 rows affected (0.01 sec)
 5. DATA MANIPULATION LANGUAGE

DML is a subset of SQL used to manipulate data stored in a database, including

operations like selecting, inserting, updating, and deleting data within tables.

5.1. INSERT : WRITE A QUERY TO INSERT VALUES INTO “CUSTOMER” TABLE.

mysql> INSERT INTO CUSTOMER VALUES ('C021', 'Alexa',

'Alexaland@GOOGLE.com', 98425322, 'Tokyo', 'Japan', 100768);
Query OK, 1 row affected (0.01 sec)

5.2. SELECT : WRITE A QUERY TO DISPLAY THE DETAILS STORED IN

“CUSTOMER” TABLE.

mysql> SELECT * FROM CUSTOMER;

21 ows in set (0.00 sec)

5.3. UPDATE : WRITE A QUERY TO UPDATE THE PINCODE AS “10768” WHERE

CID=”C021”.

mysql> UPDATE CUSTOMER SET PINCODE=10768 WHERE CID="C021";

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

5.4. DELETE : WRITE A QUERY TO DELETE THE DETAILS OF THE CUSTOMER

WHOSE CID=”C021”.

mysql> DELETE FROM CUSTOMER WHERE CID="C021";

Query OK, 1 row affected (0.01 sec)
 6. DATA CONTROL LANGUAGE

DCL is used to control access and permissions within a database. It includes

commands like GRANT (to provide access rights) and REVOKE (to remove
access rights).

6.1. GRANT : WRITE A QUERY TO GRANT PERMISSION TO THE USER TO VIEW

DATAS IN THE CUSTOMER TABLE.

mysql> CREATE USER 'JIHYO'@'localhost' IDENTIFIED BY 'passwd';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON sales_inventory.customer TO 'JIHYO'@'localhost';

Query OK, 0 rows affected (0.00 sec)

6.2. REVOKE : WRITE A QUERY TO REVOKE THE PERMISSION PROVIDED TO

THE USER.

mysql> REVOKE SELECT ON CUSTOMER FROM 'JIHYO'@'localhost';

Query OK, 0 rows affected (0.00 sec)

7. TRANSACTION CONTROL LANGUAGE

TCL is used to manage transactions in a database. It includes commands like

COMMIT (to save changes), ROLLBACK (to undo changes), and SAVEPOINT
(to set a point for partial rollback).

7.1. COMMIT : WRITE A QUERY TO SAVE THE CHANGES MADE IN

“TRANSACTION” TABLE PERMANENTLY.

mysql> UPDATE CUSTOMER SET PINCODE=98760 WHERE CID="C016";

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

7.2. ROLLBACK : WRITE A QUERY TO UNDO THE CHANGES MADE IN CURRENT

TRANSACTION.

mysql> INSERT INTO TRANSACTION VALUES('T21', 'O1020', 'Delivered', 1021,

'PayPal', '2023-09-24', 4);
Query OK, 1 row affected (0.00 sec)
 mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

7.3. SAVEPOINT : WRITE A QUERY TO CREATE AND ROLLBACK TO A

SAVEPOINT.

mysql> SAVEPOINT ONE;

Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM TRANSACTION WHERE TID = "T20";

Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK TO ONE;

Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
 8. DATA INTEGRETY CONSTRAINS

8.1. PRIMARY KEY - Ensures uniqueness and identifies each row uniquely in a table.

CREATE :

mysql> CREATE TABLE SUPPLIER ( SID VARCHAR(20) PRIMARY KEY,

S_NAME VARCHAR(20), S_MAIL VARCHAR(50), S_PHONE INT, S_CITY
VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

INSERT :

mysql> INSERT INTO SUPPLIER VALUES ('S01', 'ORGANIC VALLEY',

'ORGANICVALLEY@GOOGLE.com', 682054628, 'Rome');
Query OK, 1 row affected (0.00 sec)

8.2. FOREIGN KEY - Enforces referential integrity by linking two tables.

CREATE :

mysql> CREATE TABLE PRODUCT ( PID VARCHAR(20) PRIMARY KEY, SID

VARCHAR(20), FOREIGN KEY (SID) REFERENCES SUPPLIER(SID), P_NAME
VARCHAR(50), BRAND VARCHAR(20), PRICE INT, STOCK INT);
Query OK, 0 rows affected (0.01 sec)

INSERT :

mysql> INSERT INTO PRODUCT VALUES ('P01', 'S01', 'Organic Milk', 'Organic
Valley', 5, 100);
Query OK, 1 row affected (0.00 sec)

8.3. NOT NULL - Ensures that a column cannot have a null (empty) value.

CREATE :

mysql> CREATE TABLE ORDERS ( OID VARCHAR(20) PRIMARY KEY, CID

VARCHAR(20), FOREIGN KEY (CID) REFERENCES CUSTOMER(CID), PID
VARCHAR(20), FOREIGN KEY (PID) REFERENCES PRODUCT(PID), QTY INT
NOT NULL, TAX INT, TOTAL INT NOT NULL, O_DATE DATE);
Query OK, 0 rows affected (0.01 sec)

INSERT :

mysql> INSERT INTO ORDERS VALUES ('O1001', 'C017', 'P11', 10, 2, 52, '2023-
09-01');
Query OK, 1 row affected (0.00 sec)
8.4. UNIQUE - Ensures that values in a column are unique across rows.

CREATE :

mysql> CREATE TABLE TRANSACTION ( TID VARCHAR(20) PRIMARY KEY,

OID VARCHAR(20), FOREIGN KEY(OID) REFERENCES ORDERS(OID),
DELIVERY_STATUS VARCHAR(20) NOT NULL, TRACKING_NO INT
UNIQUE, PAYMENT_MODE VARCHAR(20), P_DATE DATE, REVIEW INT NOT
NULL);
Query OK, 0 rows affected (0.01 sec)

INSERT :

mysql> INSERT INTO TRANSACTION VALUES ('T01', 'O1001', 'In Transit', 1001,
'Cash on Delivery', '2023-09-05', 3);
Query OK, 1 row affected (0.00 sec)

8.5. CHECK - Defines conditions that must be met for data in a column.

CREATE :

mysql> CREATE TABLE CUSTOMER ( CID VARCHAR(20) PRIMARY KEY,

NAME VARCHAR(20), MAIL VARCHAR(50), PHONE INT, CITY
VARCHAR(20), COUNTRY VARCHAR(20), PINCODE INT,
CHECK(PINCODE<>0));
Query OK, 0 rows affected (0.01 sec)

INSERT :

mysql> INSERT INTO CUSTOMER VALUES ('C001', 'Nayeon',

'nayeoff@GOOGLE.com', 12345678, 'Tokyo', 'Japan', 10001);
Query OK, 1 row affected (0.00 sec)
 9. AGGREGATE FUNCTIONS AND SORTING

Aggregate functions are special functions that perform calculations on a set of

values and return a single result. These functions allow you to summarize or
aggregate data in a table or a result set.

TABLE NAME : PRODUCT

9.1. Avg ( ) : WRITE A QUERY TO DISPLAY AVERAGE OF PRICE FROM PRODUCT

TABLE.

mysql> SELECT AVG(PRICE) FROM PRODUCT;

1 row in set (0.00 sec)

9.2. Count ( ) : WRITE A QUERY TO DISPLAY COUNT OF TOTAL NO. OF PRODUCTS

FROM PRODUCT TABLE.

mysql> SELECT COUNT(P_NAME) FROM PRODUCT;

1 row in set (0.00 sec)

9.3. Sum ( ) : WRITE A QUERY TO DISPLAY SUM OF STOCK FROM PRODUCT

TABLE.

mysql> SELECT SUM(STOCK) FROM PRODUCT;

 1 row in set (0.00 sec)

9.4. Min ( ) : WRITE A QUERY TO DISPLAY MINIMUM PRICE OF PRODUCT FROM

PRODUCT TABLE.

mysql> SELECT MIN(PRICE) FROM PRODUCT;

1 row in set (0.00 sec)

9.5. Max ( ) : WRITE A QUERY TO DISPLAY MAXIMUM PRICE OF PRODUCT FROM

PRODUCT TABLE.

mysql> SELECT MAX(PRICE) FROM PRODUCT;

1 row in set (0.00 sec)

9.6. WRITE A QUERY TO DISPLAY PRICE OF THE PRODUCTS IN ASCENDING

ORDER OF PRODUCT NAME.

mysql> SELECT P_NAME, PRICE FROM PRODUCT ORDER BY P_NAME ASC;

20 ows in set (0.00 sec)

9.7. WRITE A QUERY TO DISPLAY PRICE OF THE PRODUCTS IN DESCENDING
ORDER OF PRODUCT NAME.

mysql> SELECT P_NAME, PRICE FROM PRODUCT ORDER BY P_NAME

DESC;

20 rows in set (0.00 sec)

 10. JOIN OPERATIONS

Join operations in SQL combine rows from two or more tables based on a
related column between them, creating a result set that combines data from
multiple tables.

10.1 JOIN :

mysql> SELECT CID,PID,DELIVERY_STATUS FROM ORDERS O join

TRANSACTION T on O.OID=T.OID;

20 ows in set (0.00 sec)

10.2. LEFT JOIN :

mysql> SELECT * FROM ORDERS O LEFT join TRANSACTION T on

O.OID=T.OID;

20 ows in set (0.00 sec)

10.3. RIGHT JOIN :

mysql> SELECT * FROM TRANSACTION T RIGHT join ORDERS O on

O.OID=T.OID;
 20 ows in set (0.00 sec)

10.4. FULL JOIN :

mysql> SELECT * FROM ORDERS O LEFT JOIN TRANSACTION T ON O.OID

= T.OID UNION SELECT * FROM ORDERS O RIGHT JOIN TRANSACTION T
ON O.OID = T.OID;

20 ows in set (0.00 sec)

10.5. LEFT OUTER JOIN :

mysql> SELECT * FROM ORDERS O LEFT OUTER join TRANSACTION T on

O.OID=T.OID;

20 ows in set (0.00 sec)

10.6. RIGHT OUTER JOIN :

mysql> SELECT * FROM TRANSACTION T RIGHT OUTER join ORDERS O on

O.OID=T.OID;

20 ows in set (0.00 sec)

10.7. FULL OUTER JOIN :

mysql> SELECT * FROM ORDERS O LEFT OUTER JOIN TRANSACTION T ON

O.OID = T.OID UNION SELECT * FROM ORDERS O RIGHT OUTER JOIN
TRANSACTION T ON O.OID = T.OID;

20 rows in set (0.00 sec)

 11.SUB QUERIES

11.1. Creating A Table :

mysql> CREATE TABLE CUSTOMER ( CID VARCHAR(20) PRIMARY KEY,

NAME VARCHAR(20) NOT NULL, MAIL VARCHAR(50), PHONE INT UNIQUE,
CITY VARCHAR(20), COUNTRY VARCHAR(20), PINCODE INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)

11.2. Inserting Values In A Table :

mysql> INSERT INTO CUSTOMER VALUES ('C021', 'Alexa',

'Alexaland@GOOGLE.com', 98425322, 'Tokyo', 'Japan', 100768);
Query OK, 1 row affected (0.00 sec)

11.3. Select, Displaying Values In A Table :

mysql> SELECT * FROM CUSTOMER;

21 ows in set (0.00 sec)

11.4. Using Where Clause : Write a query to display the details of

customers who are from USA.

mysql> SELECT * FROM customer WHERE COUNTRY="USA";

4 rows in set (0.01 sec)

11.5. Using Like Operator : Write a query to display the details of

 customers whose name starts with “E”.

mysql> SELECT * FROM customer WHERE NAME LIKE "E%";

2 rows in set (0.00 sec)

11.6. Updating A Row : WRITE A QUERY TO UPDATE THE PINCODE AS

“10768” WHERE CID=”C021”.

mysql> UPDATE CUSTOMER SET PINCODE=10768 WHERE

CID="C021";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

11.7. Aggregate Functions With Select : WRITE A QUERY TO DISPLAY

THE COUNT OF TOTAL NO. OF CUSTOMERS.

mysql> SELECT COUNT(NAME) FROM CUSTOMER;

1 row in set (0.01 sec)

11.8. Deleting A Row : WRITE A QUERY TO DELETE THE DETAILS OF

THE CUSTOMER WHOSE CID=”C021”.

mysql> DELETE FROM CUSTOMER WHERE CID="C021";

Query OK, 1 row affected (0.01 sec)