--1
USE Master
GO
CREATE DATABASE UFO
--2,4,5,6
USE UFO
GOCREATE TABLE products
(
productid int,
productname nvarchar(20),
unitprice money,
quanlity int,
PRIMARY KEY (productid)
)CREATE TABLE employees
(
employeeid int,
employeename nvarchar(20),
telphone nvarchar(15) CONSTRAINT [DF_employees_telphone] DEFAULT ((27)-(85773750)),
age int CHECK (age<35),
address nvarchar(120) UNIQUE,
PRIMARY KEY (employeeid),
)CREATE TABLE orders
(
orderid int,
employeeid int,
productid int,
date datetime,
quanlity int,
PRIMARY KEY (orderid),
FOREIGN KEY (employeeid) REFERENCES employees(employeeid)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (productid) REFERENCES products(productid)
ON UPDATE CASCADE
ON DELETE CASCADE
)--3
INSERT products
SELECT 1, 'P1', 1.00, 100 UNION ALL
SELECT 2, 'P2', 2.00, 200 UNION ALL
SELECT 3, 'P3', 3.00, 300INSERT employees
SELECT 1, 'E1', '027-85773723', '20', 'ABC' UNION ALL
SELECT 2, 'E2', '027-85773723', '30', 'EFG' UNION ALL
SELECT 3, 'E3', '027-85773723', '22', 'UIJ'INSERT orders
SELECT 1, 1, 1, '2008-05-11',11 UNION ALL
SELECT 2, 2, 2, '2008-05-12',22 UNION ALL
SELECT 3, 3, 3, '2008-05-13',33-- 7
create trigger Trg_Test on orders for insert
as
begin
update products
set products.quanlity=products.quanlity-A.quanlity
from inserted A
WHERE A.productid=products.productid
end
go-- 8
CREATE VIEW [V_UFO]
AS
SELECT employees.employeename, products.productname, orders.orderid, orders.quanlity
FROM employees INNER JOIN
orders ON employees.employeeid = orders.employeeid INNER JOIN
products ON orders.productid = products.productid
USE Master
GO
CREATE DATABASE UFO
--2,4,5,6
USE UFO
GOCREATE TABLE products
(
productid int,
productname nvarchar(20),
unitprice money,
quanlity int,
PRIMARY KEY (productid)
)CREATE TABLE employees
(
employeeid int,
employeename nvarchar(20),
telphone nvarchar(15) CONSTRAINT [DF_employees_telphone] DEFAULT ((27)-(85773750)),
age int CHECK (age<35),
address nvarchar(120) UNIQUE,
PRIMARY KEY (employeeid),
)CREATE TABLE orders
(
orderid int,
employeeid int,
productid int,
date datetime,
quanlity int,
PRIMARY KEY (orderid),
FOREIGN KEY (employeeid) REFERENCES employees(employeeid)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (productid) REFERENCES products(productid)
ON UPDATE CASCADE
ON DELETE CASCADE
)--3
INSERT products
SELECT 1, 'P1', 1.00, 100 UNION ALL
SELECT 2, 'P2', 2.00, 200 UNION ALL
SELECT 3, 'P3', 3.00, 300INSERT employees
SELECT 1, 'E1', '027-85773723', '20', 'ABC' UNION ALL
SELECT 2, 'E2', '027-85773723', '30', 'EFG' UNION ALL
SELECT 3, 'E3', '027-85773723', '22', 'UIJ'INSERT orders
SELECT 1, 1, 1, '2008-05-11',11 UNION ALL
SELECT 2, 2, 2, '2008-05-12',22 UNION ALL
SELECT 3, 3, 3, '2008-05-13',33-- 7
create trigger Trg_Test on orders for insert
as
begin
update products
set products.quanlity=products.quanlity-A.quanlity
from inserted A
WHERE A.productid=products.productid
end
go-- 8
CREATE VIEW [V_UFO]
AS
SELECT employees.employeename, products.productname, orders.orderid, orders.quanlity
FROM employees INNER JOIN
orders ON employees.employeeid = orders.employeeid INNER JOIN
products ON orders.productid = products.productid
(
employeeid int,
employeename nvarchar(20),
telphone nvarchar(15) CONSTRAINT [DF_employees_telphone] DEFAULT ('27-85773750'),
age int CHECK (age<35),
address nvarchar(120) UNIQUE,
PRIMARY KEY (employeeid),
)