SQL Practice Exercise: Online Mall Database User Information Data Operation

Online shopping mall database-user information data operation

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) User information table EUsers, the table structure is shown in Table J2-39-1.

Table J2-39-1 Eusers Table

3) The basic data in Table EUsers is shown in Table J2-39-2.

Table J2-39-2 EUsers 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 20MB, and it increases by 15%.

3) The initial value of the log file is 5MB, the maximum value is 10MB, 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=20MB,
	FILEGROWTH=15%
)
LOG ON
(
	NAME=EshopDB_log,
	FILENAME = 'D:\xxxx\EshopDB_log.ldf',
	SIZE=5MB,
	MAXSIZE=10MB
)

**Task 2:** Create the user information table EUsers using SQL language

1) Create a database table according to the provided table J2-39-1 structure and set the primary key.

CREATE TABLE EUsers
(
	UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	UserName NVARCHAR(50) NOT NULL,
	UserPwd NVARCHAR(50) NOT NULL,
)

**Task 3:** Use SQL language to operate the user information table EUsers

1) In the user information table EUsers, add user name, user phone number, and contact address fields; the user name and user phone number fields cannot be empty; the data type is self-defined.

2) Modify the user name field in the user information table EUsers. The user name can only be "letters".

3) Modify the password field in the user information table EUsers. The password length must not be less than 6 characters.

ALTER TABLE EUSERS
ADD user name NVARCHAR(20) NOT NULL, user phone number NVARCHAR(20) NOT NULL, contact address NVARCHAR(100)

ALTER TABLE EUSERS
ADD CONSTRAINT UserName CHECK (UserName LIKE '%[a-zA-Z]%')

ALTER TABLE EUSERS
ADD CONSTRAINT UserPwd CHECK (LEN(UserPwd)>=6)

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