SQL Server database operations

Use SQL Server statements to create databases, create data tables, and perform database operations, including retrieval, insertion, deletion, and modification. The following is an example of database operations. A warehouse supermarket uses the POS (PointofSale) Cash Register to collect the sales information at the front end.

Create a database, create a data table, and perform database operations, including searching, inserting, deleting, and modifying, using SQL Server statements. The following is an example of database operations. A warehouse supermarket uses the POS (PointofSale) Cash Register to collect the sales information at the front end.

Create a database, create a data table, and perform database operations, including searching, inserting, deleting, and modifying, using SQL Server statements.

The following is an example of database operations.

A warehouse supermarket uses the POS (Point of Sale) cashier to collect the sales receipts at the front end. In order to keep abreast of the sales information and follow the instructions to purchase goods, it is proposed to establish an information management system for commodity import, sale and storage. After system requirement analysis, conceptual structure design, and logical structure design, you can simplify the following link modes (including primary keys and Foreign keys ):

Credit accumulation (User ID, user name, cumulative consumption amount, points)

Sales details (sales flow number, product code, quantity, amount, user number, cashier, time)

Sales day Summary (date, product code, quantity)

Inventory table (item code, quantity)

Purchase table (delivery number, product code, quantity, date)

Product (product code, product name, unit price)

Complete the following questions in the SQL Server Query analyzer as required:

1. Create a database named Supermarket. The data file name is Supermarket_data.mdf, and the log file name is Supermarket_log.ldf.

2. Create 6 data tables according to Table 1-6 requirements, and set the primary key and external key codes for each table (if any ).

Table 1 Integralcard points information table

Column name	Data Type	Can it be blank?	Description
User_id	Char (10)	Not null	User ID
User_name	Varchar (20)	Not null	User Name
Cumulative_consumption	Numeric (8, 2)	Not null	Cumulative consumption amount
Integral_point	Numeric (5, 0)	Not null	Points

Table 2 Salesdetails sales Details table

Column name	Data Type	Can it be blank?	Description
Sales_id	Char (10)	Not null	Sales serial number
Commodity_code	Char (10)	Not null	Product Code
Number	Numeric (4, 0)	Null	Quantity
Amount	Numeric (9, 2)	Null	Amount
User_id	Char (10)	Not null	User ID
Cashier	Varchar (20)	Null	Cashier
Sd_time	Datetime	Null	Time

Table 3 sales datesummary sales day summary information table

Column name	Data Type	Can it be blank?	Description
Sds_date	Datetime	Not null	Date
Commodity_code	Char (10)	Not null	Product Code
Number	Numeric (4, 0)	Null	Quantity

Table 4 Inventorylist inventory information table

Column name	Data Type	Can it be blank?	Description
Commodity_code	Char (10)	Not null	Product Code
Number	Numeric (4, 0)	Null	Quantity

Table 5 Purchasetable purchase information table

Column name	Data Type	Can it be blank?	Description
Delivery_number	Char (10)	Not null	Delivery number
Commodity_code	Char (10)	Not null	Product Code
Number	Numeric (4, 0)	Null	Quantity
Pt_date	Datetime	Not null	Date

Table 6 Commodity information table

Column name	Data Type	Can it be blank?	Description
Commodity_code	Char (10)	Not null	Product Code
Commodity_name	Varchar (10)	Not null	Product Name
Commodity_price	Numeric (7,2)	Not null	Unit Price

3. In the six tables that have been created, use the object Resource Manager to enter and update several records respectively. The primary key code cannot be empty or repeated, and the foreign key code can only be one of the primary key codes of the other table.

4. Complete the following 10 query requests for the six tables of the database:

(1) query the username, accumulated consumption amount, and points of the user whose ID is 'yh23001011;

(2) query the product code, product name, unit price, quantity, and amount of all commodities purchased by the 'zhangsan' user;

(3) query the ranking list of the sales quantity of various commodities in April 2016. The product numbers, product names, and quantities must be displayed in descending order );

(4) Update the quantity in the inventory table based on the sales serial number 'xs80020001 'and the product code 'sp03004561' in the sales detail form;

(5) Update the quantity in the inventory table based on the shipping number 'sh00012288' and product code 'sp03006677 'in the purchase table;

(6) count the sales amount for each day in June April 2016. The date and sales amount must be displayed (in descending order ).

5. For the six tables of the database, the following two views are defined:

(1) define the view Commodity_Inventorylist of a product inventory. The attributes include the product code, product name, unit price, and quantity;

(2) define a detailed list of items purchased by a user, User_Purchase_Details. The attributes include user ID, user name, product code, product name, unit price, and quantity.

The implementation process on SQL Server 2008 R2 is as follows:

-- Create database -- create a database named Supermarket. The data file name is Supermarket_data.mdf, and the log file name is Supermarket_log.ldf. USE master -- USE System gocreate database Supermarket -- create database on primary -- master file (NAME = 'supermarket _ data', -- file name filename = 'd: \ SQLProject \ Supermarket_data.mdf ', -- path SIZE = 5 MB, -- initial size maxsize = 100 MB, -- maximum capacity FILEGROWTH = 10% -- growth speed) log on -- log file (NAME = 'supermarket _ log ', FILENAME = 'd: \ SQLProject \ Supermarket_log.ldf ', SIZE = 5 MB, FILEGROWTH = 0) GO -- create your database table -- mode (which indicates the primary key and the foreign key ): -- credit card (user number, user name, cumulative consumption amount, points) -- detailed sales ticket (sales serial number, product code, quantity, amount, user number, cashier, time) -- sales day Summary (date, commodity code, quantity) -- inventory table (commodity code, quantity) -- purchase table (shipping number, commodity code, quantity, date) -- product (product code, product name, unit price) use Supermarketgo -- accumulate (User ID, user name, cumulative consumption amount, points) create table Integralcard (User_id char (10) primary key not null, User_name varchar (20) not null, Cumulative_consumption numeric (8, 2) not null, Integral_point numeric (5, 0) not null) go -- Sales details (sales flow number, product Code, quantity, amount, user number, cashier, time) use Supermarketgocreate table Salesdetails (sales_id char (10) not null, commodity_code char (10) not null foreign key (commodity_code) references Commodity (commodity_code) on delete cascade, number numeric (4, 0) null, amount numeric (9, 2) null, User_id char (10) not null foreign key (User_id) references Integralcard (User_id) on delete cascade, cashier varchar (20) null, sd_time datetime null) go -- foreign key alter table SalesdetailsADD CONSTRAINT Salesdetails_KEY primary key (sales_id, commodity_code, User_id) go -- sales day Summary (date, product code, quantity) use Supermarketgocreate table Salesdatesummary (sds_date datetime not null, commodity_code char (10) not null foreign key (commodity_code) references Commodity (commodity_code) on delete cascade, number numeric (4, 0) null) goALTER TABLE SalesdatesummaryADD CONSTRAINT Salesdatesummary_KEY primary key (commodity_code) Go -- inventory TABLE (item code, quantity) use Supermarketgocreate table Inventorylist (commodity_code char (10) not null foreign key (commodity_code) references Commodity (commodity_code) on delete cascade, number numeric (4, 0) null) goALTER TABLE InventorylistADD CONSTRAINT Inventorylist_KEY primary key (commodity_code) Go -- purchase table (shipping number, item code, quantity, date) use Supermarketgocreate TABLE Purchasetable (delivery_number char (10) not null, commodity_code char (10) not null foreign key (commodity_code) references Commodity (commodity_code) on delete cascade, number numeric (4, 0) null, pt_date datetime not null) go -- foreign key alter table PurchasetableADD CONSTRAINT Purchasetable_KEY primary key (delivery_number, commodity_code) Go -- Item (product code, product name, unit price) use Supermarketgocreate table Commodity (commodity_code char (10) primary key not null, commodity_name varchar (10) not null, commodity_price numeric (7,2) not null) go --------------------------------------------- query the user name, cumulative consumption amount, and points of use Supermarketselect User_name as, Cumulative_consumption, integral_point as point from Integralcardwhere User_id = 'yh23001011' go -- query the code, name, unit price, quantity, and amount of all the items purchased by 'zhangsan'. use Supermarketselect com. commodity_code, com. commodity_name, com. commodity_price, sal. number, sal. amount from Commodity com, Salesdetails sal, Integralcard cawhere com. commodity_code = sal. commodity_code and sal. user_id = ca. user_id and ca. user_name = 'zhang san' go -- queries the ranking list of the sales quantities of various commodities in April 2016. The product numbers, names, and quantities must be displayed (sorted in descending order). use Supermarketselect com. commodity_code, com. commodity_name, sal. number, sal. sd_timefrom Commodity com, Salesdetails salwhere com. commodity_code = sal. commodity_code and sal. sd_time between '2014-04-01 'and '2014-04-30' order by sal. number descgo -- view the result use Supermarketselect number from Inventorylistwhere Inventorylist. commodity_code = 'sp03004561 'go -- Based on the sales serial number 'xs80020001' and the product code 'sp03004561' in the sales detail ticket ', use Supermarketupdate Inventorylistset number = number-(select sal. number from Salesdetails salwhere sal. sales_id = 'xs80020001 'and sal. commodity_code = 'sp03004561 ') go -- view the update result use Supermarketselect number from Inventorylistwhere Inventorylist. commodity_code = 'sp03004561 'go -- view the result use Supermarketselect number from Inventorylistwhere Inventorylist. commodity_code = 'sp03006677 'go -- Update the quantity in the inventory table according to the shipping number 'sh00012288' and commodity code 'sp03006677' in the purchase table. use Supermarketupdate Inventorylistset Inventorylist. number = Inventorylist. number + (select pur. number from Purchasetable purwhere pur. delivery_number = 'sh00012288 'and pur. commodity_code = 'sp03006677 ') go -- view the update result use Supermarketselect number from Inventorylistwhere commodity_code = 'sp03006677' go -- count the sales amount for each day in June April 2016, requires that the date and sales amount be displayed (in descending order) use Supermarketselect sal. sds_date, salemoney = com. commodity_price * sal. numberfrom Commodity com, Salesdatesummary salwhere com. commodity_code = sal. commodity_code and sal. sds_date> = '2017-04-01 'and sal. sds_date <= '1970-04-30 'go -- 5 -- Define a commodity inventory view Commodity_Inventorylist. attributes include product code, product name, unit price, and quantity. use Supermarketgocreate view Commodity_Inventorylist, commodity_price, number) as select com. commodity_code, com. commodity_name, com. commodity_price, inve. number from Commodity com, Inventorylist invewhere com. commodity_code = inve. commodity_codego -- defines a detailed list of items purchased by a user User_Purchase_Details. The attributes include user ID, user name, product code, product name, unit price, and quantity, commodity_name, commodity_price, number) as select inte. user_id, inte. user_name, com. commodity_code, com. commodity_name, com. commodity_price, sal. numberfrom Integralcard inte, Salesdetails sal, Commodity comwhere inte. user_id = sal. user_id and sal. commodity_code = com. commodity_codego

Database implementation: