sql

SELECT e.FirstName+' '+e.LastName AS EmployName,c.CompanyName AS "Customer Company",p.ProductName AS "Ordered Product",DATEPART(YEAR,o.OrderDate) AS OrderYear,od.Quantity,o.ShipAddress
FROM dbo.Customers c
LEFT JOIN dbo.Orders o
ON o.CustomerID = c.CustomerID
LEFT JOIN dbo.[Order Details] od
ON od.OrderID = o.OrderID
LEFT JOIN dbo.Products p
ON p.ProductID = od.ProductID
LEFT JOIN dbo.Employees e
ON e.EmployeeID = o.EmployeeID
WHERE DATEPART(YEAR,o.OrderDate) = '1997'

 

起初我在where中用的是 OrderYear=‘1997’,发现sql assistant 的智能提示中并没有找到OrderYear，而运行后会出来一下的错误提示:

Invalid column name 'OrderYear'.

 

所以，应该是不能这么用，将where 改为 DATEPART(YEAR,o.OrderDate)=‘1997’，发现这样就可以了

 

 

--1tables constraints.

--1tabletabletbl_Productstbl_OrderItemstbl_OrderItems,tbl_Products.

--2table primary key,

USE SQLLearning

Go

--Create Vendor Table

IF EXISTS

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_OrderItems]')

AND type in (N'U'))

DROP TABLE [dbo].[tbl_OrderItems]

GO

 

IF EXISTS

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Orders]')

AND type in (N'U'))

DROP TABLE [dbo].[tbl_Orders]

GO

IF EXISTS

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Customers]')

AND type in (N'U'))

DROP TABLE [dbo].[tbl_Customers]

GO

IF EXISTS

(

SELECT * FROM sys.objects where object_id=OBJECT_ID(N'[dbo].[tbl_Products]')

AND type in (N'U')

)

DROP TABLE [dbo].[tbl_Products]

GO

IF EXISTS

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Vendor]')

AND type in (N'U'))

DROP TABLE [dbo].[tbl_Vendor]

GO

 

Create Table tbl_Vendor

(

vend_id [uniqueidentifier] NOT NULL primary key,

vend_name nvarchar(50) NOT NULL,

vend_address nvarchar(500),

vend_city nvarchar(256),

vend_state nvarchar(256),

vend_zip char(10),

vend_country nvarchar(256)

)

 

--Create Products Table

Create Table tbl_Products

(

prod_id uniqueidentifier NOT NULL Primary Key,

vend_id uniqueidentifier NOT NULL references tbl_Vendor(vend_id),

prod_name nvarchar(50) NOT NULL,

prod_price Money NOT NULL,

prod_desc nvarchar(256),

)

--Create Customers Table

Create Table tbl_Customers

(

cust_id uniqueidentifier NOT NULL Primary key,

cust_name nvarchar(50) NOT NULL,

cust_address nvarchar(500),

cust_city nvarchar(256),

cust_state nvarchar(256),

cust_zip char(10),

cust_country nvarchar(256),

cust_contact nvarchar(50),

cust_email nvarchar(50),

)

--Create Orders Table

Create Table tbl_Orders

(

order_num int NOT NULL Primary key,

order_date DATETIME NOT NULL,

cust_id uniqueidentifier NOT NULL references tbl_Customers(cust_id)

)

 

--Create OrderItems Table

Create Table tbl_OrderItems

(

order_num int NOT NULL references tbl_Orders(order_num),

order_item int NOT NULL,

prod_id uniqueidentifier NOT NULL references tbl_Products(prod_id),

quantity int NOT NULL,

item_price Money NOT NULL,

Constraint PK_OrderItems Primary key CLUstered(order_num,order_item)

)

 

 

 

--2tables tableconstraints.

--

 

USE SQLLearning

 

Go

IF EXISTS

 

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_OrderItems]')

 

AND type in (N'U'))

 

DROP TABLE [dbo].[tbl_OrderItems]

 

GO

 

IF EXISTS

 

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Orders]')

 

AND type in (N'U'))

 

DROP TABLE [dbo].[tbl_Orders]

 

GO

IF EXISTS

 

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Customers]')

 

AND type in (N'U'))

 

DROP TABLE [dbo].[tbl_Customers]

 

GO

IF EXISTS

(

 

SELECT * FROM sys.objects where object_id=OBJECT_ID(N'[dbo].[tbl_Products]')

 

AND type in (N'U')

)

 

DROP TABLE [dbo].[tbl_Products]

 

GO

IF EXISTS

 

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Vendor]')

 

AND type in (N'U'))

 

DROP TABLE [dbo].[tbl_Vendor]

 

GO

 

--Create Vendor Table

 

Create Table tbl_Vendor

 

(

 

vend_id [uniqueidentifier] NOT NULL,

 

vend_name nvarchar(50) NOT NULL,

 

vend_address nvarchar(500),

 

vend_city nvarchar(256),

 

vend_state nvarchar(256),

 

vend_zip char(10),

 

vend_country nvarchar(256)

)

 

 

--Create Products Table

 

Create Table tbl_Products

 

(

 

prod_id uniqueidentifier NOT NULL,

 

vend_id uniqueidentifier NOT NULL,

 

prod_name nvarchar(50) NOT NULL,

 

prod_price Money NOT NULL,

 

prod_desc nvarchar(256),

)

 

--Create Customers Table

 

Create Table tbl_Customers

 

(

 

cust_id uniqueidentifier NOT NULL,

 

cust_name nvarchar(50) NOT NULL,

 

cust_address nvarchar(500),

 

cust_city nvarchar(256),

 

cust_state nvarchar(256),

 

cust_zip char(10),

 

cust_country nvarchar(256),

 

cust_contact nvarchar(50),

 

cust_email nvarchar(50),

)

 

--Create Orders Table

 

Create Table tbl_Orders

 

(

 

order_num int NOT NULL,

 

order_date DATETIME NOT NULL,

 

cust_id uniqueidentifier NOT NULL

)

 

 

--Create OrderItems Table

 

Create Table tbl_OrderItems

 

(

 

order_num int NOT NULL,

 

order_item int NOT NULL,

 

prod_id uniqueidentifier NOT NULL,

 

quantity int NOT NULL,

 

item_price Money NOT NULL

)

 

--constraints

--Add Primary key

 

Alter Table tbl_Vendor WITH NOCHECK Add constraint PK_Vendor primary key CLUSTERED(vend_id)

 

Alter Table tbl_Products WITH NOCHECK Add constraint PK_Products primary key CLUSTERED(prod_id)

 

Alter Table tbl_Customers WITH NOCHECK Add constraint PK_Customers primary key CLUSTERED(cust_id)

 

Alter Table tbl_Orders WITH NOCHECK Add constraint PK_Orders primary key CLUSTERED(order_num)

 

Alter Table tbl_OrderItems WITH NOCHECK Add constraint PK_OrderItems primary key CLUSTERED(order_num,order_item)

 

--Add foreign key

--1.Foreign key (column_name): column_name

--2. constraint

 

Alter Table tbl_Products Add constraint FK_Products_Vendor Foreign key (vend_id) references tbl_Vendor(vend_id)

 

GO

Alter Table tbl_Orders Add constraint FK_Orders_Customers Foreign key (cust_id) references tbl_Customers(cust_id)

 

GO

Alter Table tbl_OrderItems Add

 

constraint FK_OrderItems_Orders Foreign key (order_num) references tbl_Orders(order_num),

 

constraint FK_OrderItems_Products Foreign key (prod_id) references tbl_Products(prod_id);

 

 

[Issue1]

 'CREATE FUNCTION' must be the first statement in a query batch

IF EXISTS
(SELECT * FROM sys.objects o 
 WHERE o.[object_id]=OBJECT_ID(N'dbo.Exports')
 AND TYPE IN (N'U')
	)
DROP TABLE dbo.Exports
Go

Create Table Exports
(
movie_title CHAR(25) NOT NULL,
country_code CHAR(2) NOT NULL, --USE 2-letter ISO nation codes
sales_amt DECIMAL(12,2) NOT NULL,
PRIMARY KEY (movie_title,country_code),
)
Go

CREATE FUNCTION MovieCount()
RETURNS int
AS
BEGIN
	DECLARE @retval INT
	SELECT @retval= COUNT(movie_title)
	FROM Exports e
	GROUP BY e.country_code
	RETURN @retval
END
GO ---加了这个之后就没有问题了。