哪位专家帮忙解答一下,加急!!!!!!
数据库market中有三个表 customers, Goods, orders, 阅读下面创建三个表的语句,
并回答问题
*************创建客户信息表customers************* */
CREATE TABLE customers
( customerID int IDENTITY(1,1) PRIMARY KEY ,
Name char(8) NOT NULL,
Address varchar(100) NULL,
City varchar(20) NULL,
Tel varchar(20) NULL UNIQUE,
Company varchar(50) NULL,
Birthday datetime NULL,
Type tinyint DEFAULT 1
)
/* **********创建货品信息表Goods*********** */
CREATE TABLE Goods
( goodsname varchar(20) CONSTRAINT PK_GOODS PRIMARY KEY ,
Description varchar(100),
storage int ,
supplier varchar(50),
status tinyint default(0),
price money )
/* ***************创建订单信息表Orders****************** */
CREATE TABLE Orders
( OrderID int IDENTITY(1,1) PRIMARY KEY,
Goodsname varchar(20) NOT NULL
references Goods(GoodsName) ON DELETE CASCADE,
CustomerID int NOT NULL foreign key(CustomerID)
references Customers(customerID),
Quantity int NOT NULL CHECK(Quantity>0),
OrderSum money NOT NULL,
OrderDate datetime Default(getdate())
)
1. 指出上面3个表中有哪写约束类型,约束名(如果是系统自动取名,请明确指出)
2. customers 表有2 行数据 :
customerID Name Address City Tel Company Birthday Type
1 aaa xueyuanroad beijing 82321111 tcl 1989-12-12 2
2 bbb haiding beijing 82322222 legend 1990-01-12 1
goods表也有2 行数据:
goodsname Description storage supplier status price
hub for network 100 tcl 1 125
TV for play 200 tcl 1 1200
orders 表有2行数据
OrderID Goodsname CustomerID Quantity OrderSum OrderDate
1 hub 1 10 1250 2005-01-06
3 TV 1 5 7500 2005-01-06
请指出下面的insert 语句有2个错误
insert into orders
values
(hub, 3, 10, 1250, '2005-01-06' )
3. 如果 customers, goods, orders 表中的数据与上题相同, 请写出下面的select 语句的结果
use market
go
SELECT orderID, name as customer_name, quantity,ordersum
FROM orders ,customers
WHERE orders.customerID=customers.customerID
4. 如果 customers, goods, orders 表中的数据与上题2相同,写出下面的存储过程结果
use market
go
CREATE PROC proc1
@cname varchar(20)
AS
SELECT Name,OrderID,GoodsName,Quantity,Orderdate,Ordersum
FROM Customers join Orders
ON Customers.CustomerID= Orders. CustomerID
Where Name LIKE @Cname
执行
exec proc1 @cname=’aaa’
5. 如果 customers, goods, orders 表中的数据与上题2相同,写出下面select 语句结果
SELECT * FROM Orders