SQL Server database Operations

Create a database in pure code, create a data table, and perform appropriate database operations, including retrieval, insertion, deletion, and modification, by using SQL Server to build a library statement.

The following is an example of how the database operates.

A warehouse supermarket using POS (Point of Sale) cash register is responsible for the reception of sales receipts, in order to grasp the sales information in a timely manner, and in accordance with the guidance of the purchase, the establishment of commodity, sales, storage database information management system. Through the system requirements analysis, conceptual structure design and logical structure design, we can simplify the following set of relationship patterns (which represent primary keys, representing foreign keys):

Points Card (user ID, user name, accrued amount, points)

Sales Detail List (sales serial number, product code, quantity, amount, user number, cashier, time)

Sales Day Summary (date, product code, quantity)

Inventory table (commodity code, quantity)

Arrival form (delivery number, product code, quantity, date)

Product (product code, product name, Unit price)

Please complete the following questions as required in Query Analyzer for SQL Server:

1, create a database named supermarket, the data file name is: supermarket_data.mdf, log file name is: Supermarket_log.ldf.

2, according to the requirements of table 1-6 to create 6 data tables, and set the primary key and foreign key code for each table (if any).

Table 1 integralcard Credit card information sheet

Column Name	Data type	Can be empty	Description
user_id	CHAR (10)	NOT NULL	User number
User_name	varchar (20)	NOT NULL	User name
Cumulative_consumption	Numeric (8,2)	NOT NULL	Accumulated consumption amount
Integral_point	Numeric (5,0)	NOT NULL	Integration Point

Table 2 Salesdetails Sales detail Information Form

Column Name	Data type	Can be empty	Description
sales_id	CHAR (10)	NOT NULL	Sales Serial Number
Commodity_code	CHAR (10)	NOT NULL	Product Code
Number	Numeric (4,0)	Null	Number
Amount	Numeric (9,2)	Null	Amount
user_id	CHAR (10)	NOT NULL	User number
Cashier	varchar (20)	Null	Cashier Clerk
Sd_time	Datetime	Null	Time

Table 3 Salesdatesummary Sales Day Summary Information table

Column Name	Data type	Can be empty	Description
Sds_date	Datetime	NOT NULL	Date
Commodity_code	CHAR (10)	NOT NULL	Product Code
Number	Numeric (4,0)	Null	Number

Table 4 inventorylist Inventory information table

Column Name	Data type	Can be empty	Description
Commodity_code	CHAR (10)	NOT NULL	Product Code
Number	Numeric (4,0)	Null	Number

Table 5 purchasetable Purchase Information table

Column Name	Data type	Can be empty	Description
Delivery_number	CHAR (10)	NOT NULL	Delivery number
Commodity_code	CHAR (10)	NOT NULL	Product Code
Number	Numeric (4,0)	Null	Number
Pt_date	Datetime	NOT NULL	Date

Table 6 Commodity Commodity Information table

Column Name	Data type	Can be empty	Description
Commodity_code	CHAR (10)	NOT NULL	Product Code
Commodity_name	varchar (10)	NOT NULL	Product Name
Commodity_price	Numeric (7,2)	NOT NULL	Commodity price

3, the construction of 6 tables, the use of Object Explorer to enter and update several records, the primary key code can not be empty and repeat, foreign key code can only take another table of one of the primary key code.

4, for the database of 6 tables, complete the following ten query requests:

(1) The user name, cumulative consumption amount and points of the user who arequeried for the user number ' yh23001011 ';

(2) search for the product code, commodity name, unit price, quantity and amount of all goods purchased by ' Zhang San ' users;

(3) To inquire the list of the sales quantity of various commodities in April 2016, request to display the product number, commodity name and quantity (in descending order);

(4) To update the quantity in the inventory table according to the sales serial number ' xs80020001 ' and the Commodity code ' sp03004561 ' in the Sales detail list;

(5) update the quantity in the inventory table according to the delivery number ' sh00012288 ' and the Commodity code ' sp03006677 ' in the stock list;

(6) To count the sales amount for each day of April 2016, and to display the date and sales amount in descending order.

5.for the database of 6 tables, define the following 2 views:

(1) Define a view of a commodity stock commodity_inventorylist, attributes include commodity code, commodity name, unit price and quantity;

(2) Define a detailed list of items purchased by a user user_purchase_details, including user number, user name, product code, product name, unit price, and quantity.

the implementation process on SQL Server R2 is as follows:

--Create a database--create a database named supermarket, the data file name is: supermarket_data.mdf, log file name is: Supermarket_log.ldf. Use master--using the System gocreate database supermarket--create databases on primary--main file (name= ' Supermarket_data ',--filename filename= ' d:\ Sqlproject\supermarket_data.mdf ',--path size=5mb,--Initial size MAXSIZE=100MB,--maximum capacity filegrowth=10%--growth rate) Log on--log file (name=    ' Supermarket_log ', filename= ' D:\SQLProject\Supermarket_log.ldf ', size=5mb,filegrowth=0) go--Create your database table--mode (which represents the primary key, Denotes foreign key):----------------------------------------------- Quantity)--Purchase form (delivery number, product code, quantity, date)--Product (product code, product name, unit price) use supermarketgo--points card (user number, user name, cumulative consumption amount, points) create table Integralcard ( user_id Char (TEN) primary key not null,user_name varchar (no null,cumulative_consumption numeric (8,2) not Null,integra L_point Numeric (5,0) not NULL) go--sales details (sales serial number, product code, quantity, amount, user number, cashier, time) Use Supermarketgocreate table Salesdetails (Sales _id Char (Ten) not Null,commodity_code char (TEN) 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 (TEN) NOT NULL foreign key (USER_ID) references Inte Gralcard (user_id) on delete cascade,cashier varchar () 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 (TEN) NOT NULL foreign key (Commo Dity_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 (product code, quantity) use Supermarketgocreate table Inventorylist (Commodity_code char (TEN) 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--Inbound form (delivery number, product code, quantity, date) Use Supermarketgocreate table purchasetable ( DeliveRy_number Char (Ten) not Null,commodity_code char (TEN) 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 P Urchasetable_key PRIMARY KEY (delivery_number,commodity_code) go--product (product code, product name, unit price) Use Supermarketgocreate table Commodity (Commodity_code char (TEN) primary key not null,commodity_name varchar () not null,commodity_price numeric (7,2) Not null) Go-------------------------------------------------query user's username, cumulative consumption amount, and point use for user number ' yh23001011 ' Supermarketselect user_name as name, cumulative_consumption as cumulative consumption amount, integral_point as point from Integralcardwhere User_id= ' yh23001011 ' go--inquires the product code, commodity name, unit price, quantity and amount of all goods purchased by ' Zhang San ' users; 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--query 2016 Year 4Monthly list of sales quantities of various goods, requiring display of product number, commodity name and quantity (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 ' 2016-04-01 ' and ' 2016-04-30 ' ORDER by Sal.number descgo--View results use Supermarketselect number from Inventorylistwhere Invento Rylist.commodity_code= ' sp03004561 ' go--update the quantity in the inventory table according to the sales serial number ' xs80020001 ' and the Product Code ' sp03004561 ' in the Sales detail order Supermarketupdate inventorylistset number= number-(select Sal.number from salesdetails salwhere sal.sales_id= ' xs80020001 ' and sal.commodity_code= ' sp03004561 ') go--view the results of the update use the Supermarketselect number from Inventorylistwhere Inventorylist.commodity_code= ' sp03004561 ' go--view results use Supermarketselect number from Inventorylistwhere Inventorylist.commodity_code= ' sp03006677 ' go--update the quantity in the inventory table according to the delivery number ' sh00012288 ' and the Product Code ' sp03006677 ' in the arrival form Supermarketupdate inventorylistset inventorylist.number=inventorylist.number + (select Pur.number from Purchasetable Purwhere pur.deLivery_number= ' sh00012288 ' and pur.commodity_code= ' sp03006677 ') go--view the results of the update use the Supermarketselect number from Inventorylistwhere commodity_code= ' sp03006677 ' go--statistics of sales amount per day in April 2016, requires display date, sales amount (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>= ' 2016-04-01 ' and sal.sds_date<= ' 2016-04-30 ' go--5--defines a view of a commodity's inventory commodity_inventorylist, attributes include product code, product name, unit price, and quantity use Supermarketgocreate view Commodity_ Inventorylist (Commodity_code,commodity_name,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 user-purchased items user_purchase_details, attributes include user number, user name, product code, product name, unit price, and quantity use Supermarketgocreate view User_ Purchase_details (user_id,user_name,commodity_code,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:

SQL Server database Operations