Some SQL questions

Create DATABASE SC

On

(Name= ' SC ',

Filename= ' D:\SC.mdf ')

Log on

(Name= ' Sc_log ',

Filename= ' D:\SC_log.dlf ')

Go

Use SC

Go

CREATE TABLE commodity supply record

(merchant name nvarchar) NOT NULL,

Product name nvarchar (20),

Commodity price Decimal (7,2)

)

INSERT into product supply record values (' Changhong ', ' Color TV ', 4500.00)

INSERT into commodity supply record values (' Nature company ', ' Bird ', 200.00)

INSERT into product supply record values (' Hongqi ', ' egg ', 2.50)

INSERT into product supply record values (' Hongqi ', ' powdered milk ', 19.00)

INSERT into product supply record values (' Super sound ', ' VCD ', 860.00)

INSERT into commodity supply record values (' Dayi rice ', ' rice ', 1.50)

INSERT into product supply record values (' Super sound ', ' cassette recorder ', 1100.00)

INSERT into product supply record values (' Lake sound ', ' Walkman ', 200.00)

INSERT into product supply record values (' Lake sound ', ' speakers ', 1000.00)

INSERT into commodity supply record values (' Lake sound ', ' VCD ', 680.00)

CREATE TABLE customer record

(Customer name nvarchar () NOT NULL,

Address nvarchar (100),

Customer balance Decimal (7,2)

)

Insert into customer record values (' Wang Hong ', ' Chengdu Tai Sheng Nan Road 18th ', 1200.00)

Insert into customer record values (' Zhao Yiqian ', ' Chongqing Stone Bridge Shop ', 1578.20)

Insert into customer record values (' Zhang Jing ', ' Chengdu Renmin South Road # 4th ', 2000.00)

Insert into customer record values (' John Doe ', ' Chengdu Double Bridge Sub 10th ', 980.00)

Insert into customer record values (' Zhang Yang ', ' Chengdu Dong Zi Port No. 130th ', 450.00)

Insert into customer record values (' Shu-yu ', ' Chengdu Chunxi Road 2nd ', 58.00)

Insert into customer record values (' Li Ying ', ' Chongqing Liberation Monument No. 130th ',-200.00)

Insert into customer record values (' Shu Hong ', ' Chengdu Chunxi Road 8th ',-80.00)

CREATE TABLE Order record

(Order number char () NOT NULL,

Date the order was issued,

Customer name nvarchar (10)

)

INSERT into order record values (' 0024 ', ' 01/20/1997 ', ' Wang Hong ')

INSERT into order record values (' 2025 ', ' 06/09/1997 ', ' Zhang Jing ')

INSERT into order record values (' 3026 ', ' 02/28/1997 ', ' Zhang Yang ')

INSERT into order record values (' 1100 ', ' 04/24/1997 ', ' Zhang Yang ')

INSERT into order record values (' 1000 ', ' 03/25/1997 ', ' John Doe ')

INSERT into order record values (' 1580 ', ' 05/22/1997 ', ' Zhao Yiqian ')

INSERT into order record values (' 2035 ', ' 08/08/1997 ', ' Shu-yu ')

INSERT into order record values (' 0014 ', ' 09/10/1997 ', ' Li Ying ')

CREATE TABLE Order product record

(Order number char () NOT NULL,

Product name nvarchar (20),

Order Quantity int

)

INSERT into order commodity record values (' 0024 ', ' Color TV ', 30)

INSERT into order commodity record values (' 2025 ', ' bird ', 16)

INSERT into order commodity record values (' 3026 ', ' eggs ', 500)

INSERT into order commodity record values (' 1100 ', ' VCD ', 12)

INSERT into order commodity record values (' + ', ' VCD ', 1000)

INSERT into order commodity record values (' 1580 ', ' cassette recorder ', 22)

INSERT into order commodity record values (' 2035 ', ' Walkman ', 50)

INSERT into order commodity record values (' 0014 ', ' speakers ', 5)

--Write the following SQL statement

--1, find out the balance 1000~2000 the customer information between the elements.

SELECT * FROM customer record where customer balance between and 2000

--2, find out the name and address of the customer with no balance.

Select customer name, address from customer record where customer balance <=0

--3, find out the customers who live in Chengdu area.

SELECT * FROM customer record where address like ' Chengdu% '

--4, insert supplier "Super Sound" to provide VCD for 500 yuan price.

INSERT into product supply record values (' Super sound ', ' VCD ', ' 500 ')

--5, find suppliers that offer less than two types of goods.

Select Merchant name from product supply record group up merchant name having COUNT (*) <2

Select " Field 1 ", SUM (" field 2 ")

From " Table name "  

GROUP by " Field 1 "

Having ( function Condition )

Please note to the reader: If you are SELECT only the function bar, it does not need GROUP by clause.

--6, find a supplier that supplies at least one Zhang Yang ordered goods.

Select Merchant Name

From product supply record join order product record on commodity supply record. Product name = Order Commodity record.

Join order record on order product record. Order number = Order record. Order number

Where order record. Customer name = ' Zhang Yang '

--7, the supplier for the "Changhong" to provide the price of goods reduced by 20%.

Update item supply record set commodity price = commodity price *0.8 where merchant name = ' Changhong company '

--8, delete all relevant information of "Dayi rice industry"

Delete Commodity supply record where merchant name = ' Dayi m industry '

--9, Statistics "Chengdu" the number of customers.

Select COUNT (*) as Chengdu customer number form customer record where address like ' Chengdu% '

--10, query "Lake Audio" total supply of how many kinds of goods.

Select COUNT (*) as Lakes Audio Product category from product supply record where merchant name = ' Lake Sound '