Basic SQL statements

Time:2021-5-4

Table structure




Experiment content

1

  • Create a database cap with SQL statements. The data file name is capdata.mdf. The initial storage space of the data file is 50m, the maximum storage space is 500m, and the automatic growth of the storage space is 10m.
CREATE DATABASE CAP
ON
(
	NAME = CAP,
	File name ='c:: - users, zero, desktop, capdata. MDF '-- file name
	Size = 50, - initial storage size
	Maxsize = 500, - maximum storage size
	Filegrowth = 10 -- auto growth
)

2

  • In the cap database, create the following four tables with SQL statements, reasonably design the data type of each field, and establish the primary key and foreign key constraints. Price field in table products cannot be empty. The value range of discnt field of table customers is between [0,30]. Use SQL statements to add data in the table structure to the table.
use CAP
CREATE TABLE Customers
(
	"cid" char(4) CONSTRAINT Custom_Prim PRIMARY KEY,
	"cname" varchar(10),
	"city" varchar(10),
	"discnt" numeric(4,2) CONSTRAINT DISCNT_CHK CHECK(discnt BETWEEN 0 AND 30)
)
CREATE TABLE Products
(
	"Pid" char(3) CONSTRAINT Product_Prim PRIMARY KEY,
	"pname" varchar(10),
	"city" varchar(10),
	"quantity" int,
	"price" numeric(10,2) CONSTRAINT Price_NotNull NOT NULL
)
CREATE TABLE Agents
(
	"Aid" char(3) CONSTRAINT Agent_Prim PRIMARY KEY,
	"aname" varchar(10),
	"city" varchar(10),
	"percent" TINYINT
)
CREATE TABLE Orders
(
	"OrDno" char(4) CONSTRAINT Order_Prim PRIMARY KEY,
	"month" char(3) CONSTRAINT Month_CHK CHECK(month in ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') ),
	"cid" char(4) CONSTRAINT Cid_Fore FOREIGN KEY REFERENCES Customers(cid),
	"aid" char(3) CONSTRAINT Aid_Fore FOREIGN KEY REFERENCES Agents(Aid),
	"pid" char(3) CONSTRAINT Pid_Fore FOREIGN KEY REFERENCES Products(Pid),
	"qty" int,
	"dollars" numeric(10,2)
)

3

  • Using the system predefined stored procedure sp_ Helpdb looks up information about the database, such as owner, size, creation date, etc.
EXEC sp_helpdb

4

  • Using the system predefined stored procedure sp_ Helpconstraint view the constraints in the table (including primary key, foreign key, check constraint, default, unique).
EXEC sp_helpconstraint Orders

5

  • Create a table orders_ Jan, the structure of the table is the same as that of orders. Copy the order record with month ‘Jan’ in the orders table to the orders table_ In Jan.
CREATE TABLE Orders_Jan
(
	"OrDno" char(4) CONSTRAINT Order_Jan_Prim PRIMARY KEY,
	"month" char(3) ,
	"cid" char(4) CONSTRAINT Jan_Cid_Fore FOREIGN KEY REFERENCES Customers(cid),
	"aid" char(3) CONSTRAINT Jan_Aid_Fore FOREIGN KEY REFERENCES Agents(Aid),
	"pid" char(3) CONSTRAINT Jan_Pid_Fore FOREIGN KEY REFERENCES Products(Pid),
	"qty" int,
	"dollars" numeric(10,2)
);

INSERT INTO Orders_Jan
SELECT * 
FROM Orders
WHERE month='Jan'

6

  • Delete all the order records with month ‘Jan’ in the orders table.
DELETE 
FROM Orders
WHERE month='Jan'

7

  • For customers who have placed an order with a dollar value greater than 500, increase the discnt value by 2 percentage points (+ 2).
UPDATE Customers
Set disc NT = disc NT + 2 -- update disc NT
WHERE cid IN( 			--  Find out the customer CID who has placed 500 orders
			Select distinct CID -- use distinct to prevent duplicate updates of discnt to the same customer
            --In fact, you can directly select CID, in will automatically remove duplicate values
			FROM Orders
			WHERE dollars > 500
			)

8

  • Write a TSQL program, add 5000 records to the table orders, and require the orders to be distributed as evenly as possible in 12 months.
use CAP
DECLARE @i AS INT,@randNum AS FLOAT,@mon AS char(3),@OrdNo AS SMALLINT,@cid AS char(4),@aid AS char(3),@pid AS char(3),@price AS numeric(10,2);
SET @i=1;
SET @OrdNo=1030;
While @ I < = 5000 -- process control, 5000 cycles
	BEGIN
		SET @randNum=RAND()*12;
		SET @mon= 							--  Randomly generated month
		CASE
			WHEN @randNum<1 THEN 'Jan'
			WHEN @randNum>=1 AND @randNum<2 THEN 'Feb'
			WHEN @randNum>=2 AND @randNum<3 THEN 'Mar'
			WHEN @randNum>=3 AND @randNum<4 THEN 'Apr'
			WHEN @randNum>=4 AND @randNum<5 THEN 'May'
			WHEN @randNum>=5 AND @randNum<6 THEN 'Jun'
			WHEN @randNum>=6 AND @randNum<7 THEN 'Jul'
			WHEN @randNum>=7 AND @randNum<8 THEN 'Aug'
			WHEN @randNum>=8 AND @randNum<9 THEN 'Sep'
			WHEN @randNum>=9 AND @randNum<10 THEN 'Oct'
			WHEN @randNum>=10 AND @randNum<11 THEN 'Nov'
			ELSE 'Dec'
		END

		SELECT  @cid=cid  	--  Get a user ID at random
		FROM Customers
		ORDER BY NEWID()

		SELECT @ pid=Pid,@price=price 	--  Randomly obtain a product ID and the corresponding product price
		FROM Products
		ORDER BY NEWID()
		
		SET @aid=(SELECT TOP 1 Aid  			--  Get an agent ID at random
				  FROM Agents
		          ORDER BY NEWID()
				  )
		
		SET @randNum=RAND()*2000+400;  --  Generate a random number of orders from 400 to 2400

		INSERT INTO Orders 				--  Insert an order record
		VALUES(CONVERT(char(4),@OrdNo),@mon,@cid,@aid,@pid,CONVERT(INT,@randNum),CONVERT(INT,@randNum) * @price);
        --Use the conversion function convert ()

		SET @[email protected]+1;
		SET @[email protected]+1;
	END

9

  • Index on the ‘month’ field of the table orders.
CREATE INDEX Orders_Index
ON Orders(month)

10

  • Create a view order_ month_ Summary: the fields in the view include the month, the total order amount of the month, and the total order amount of the month. Based on view order_ month_ Summary: to query the total order amount and total order amount of each month in the first quarter.
CREATE VIEW order_month_summary(month,total_qty,total_dollars)
As select month, sum (quantity), sum (dollars)
   FROM Orders
   GROUP BY month
SELECT month,total_qty,total_dollars
FROM order_month_summary
Where month in ('jan ',' Feb ',' Mar ') -- Query Q1