SQL Practice Exercise: Online Mall Database Product Category Data Operation

Online shopping mall database-product category data operation (I)

Project Description

In the context of the rise of e-commerce, establishing an information portal that uses the Internet to open up sales channels, help companies adjust their product structure in a timely manner, and assist dealers in opening up sources of goods has become an effective solution to the problem of poor information flow. E-commerce is conducive to companies transforming their operating mechanisms, establishing a modern enterprise system, and improving their sales level and competitiveness. It has realized a series of online shopping services such as directly browsing products, purchasing products, creating orders, viewing various new products, special products, hot-selling products, leaving messages, customer management, product management, product information search, order management, product classification management, announcement/feedback management, etc. This project is to realize the operation of the online mall user information form.

The online mall system database operation requirements are as follows:

1) Database EshopDB.

2) Product category parent table EPType, the table structure is shown in Table J2-40-1.

Table J2-40-1 EPType Table

3) EPType basic data, as shown in Table J2-40-2.

Table J2-13-2 EPType table basic data

4) Commodity category subtable ECType, the table structure is shown in Table J2-40-3.

Table J2-40-3 ECType Table

5) Table ECType basic data, as shown in Table J2-40-4.

Table J2-40-4 ECType table basic data

(1) Task Description

**Task 1:**Create an online shopping mall database using SQL language

1) Create the database EshopDB and determine whether there is a database with the same name in the system. If so, delete it; if not, create it.

2) The initial value of the main database file is 10MB, the maximum is 30MB, and it increases by 15%.

3) The initial value of the log file is 5MB, the maximum value is 20MB, and it grows automatically.

IF DB_ID('EshopDB') IS NOT NULL DROP DATABASE EshopDB
GO
CREATE DATABASE EshopDB
ON PRIMARY
(
	NAME=EshopDB,
	FILENAME='D:\xxxx\EshopDB.mdf',
	SIZE=10MB,
	MAXSIZE=30MB,
	FILEGROWTH=15%
)
LOG ON
(
	NAME=EshopDB_log,
	FILENAME = 'D:\xxxx\EshopDB_log.ldf',
	SIZE=5MB,
	MAXSIZE=20MB
)

**Task 2:**Use SQL to create the product category parent table EPType and product category child table ECType

1) Create database tables according to the provided table J2-40-1 and table J2-40-3 structures, and set primary keys and foreign keys.

CREATE TABLE EPType
(
	EPTID INT NOT NULL PRIMARY KEY,
	EPTName NVARCHAR(50) NOT NULL,
)

CREATE TABLE ECType
(
	ECTID INT NOT NULL PRIMARY KEY,
	EPTID INT NOT NULL,
	ECTName NVARCHAR(50) NOT NULL,
	FOREIGN KEY(EPTID) REFERENCES EPType(EPTID)
)

**Task 3: **Use SQL language to operate on the product category parent table EPType and the product category child table ECType

1) Create a view to display the sub-category records under the Hats category.

2) Insert records of women's leather shoes, men's sports shoes, and children's shoes into the commodity category sub-table ECType to complete the records of shoes in the commodity category parent table EPType.

3) Count the total number of product subcategories.

INSERT INTO EPType VALUES(1,'Tops'),(2,'Hat'),(3,'Children's Clothing')
INSERT INTO ECType VALUES(1,1,'shirt'),(2,1,'sportswear'),(3,1,'jacket'),(4,2,'warm hat'),(5,2,'sports hat'),(6,3,'boy'),(7,3,'girl')

CREATE VIEW HAT
AS 
SELECT EPType.EPTName,ECType.ECTName 
FROM EPType,ECType 
WHERE ECType.EPTID=(SELECT EPTID FROM EPType WHERE EPType.EPTName='hat') AND EPType.EPTName='hat'
GO
SELECT * FROM HAT

INSERT INTO EPType VALUES(4,'shoes')
INSERT INTO ECType VALUES(8,4,'Women's leather shoes'),(9,4,'Men's sports shoes'),(10,4,'Children's shoes')

SELECT COUNT(*) FROM ECType

This is the end of this article about SQL practical exercises on online shopping mall database product category data operations. For more relevant SQL product category data operations, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!