38 lines
1.1 KiB
Transact-SQL
38 lines
1.1 KiB
Transact-SQL
-- sql server sample sql
|
|
CREATE TABLE dbo.EmployeeSales
|
|
( DataSource varchar(20) NOT NULL,
|
|
BusinessEntityID varchar(11) NOT NULL,
|
|
LastName varchar(40) NOT NULL,
|
|
SalesDollars money NOT NULL
|
|
);
|
|
GO
|
|
CREATE PROCEDURE dbo.uspGetEmployeeSales
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName,
|
|
sp.SalesYTD
|
|
FROM Sales.SalesPerson AS sp
|
|
INNER JOIN Person.Person AS c
|
|
ON sp.BusinessEntityID = c.BusinessEntityID
|
|
WHERE sp.BusinessEntityID LIKE '2%'
|
|
ORDER BY sp.BusinessEntityID, c.LastName;
|
|
GO
|
|
--INSERT...SELECT example
|
|
INSERT INTO dbo.EmployeeSales
|
|
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
|
|
FROM Sales.SalesPerson AS sp
|
|
INNER JOIN Person.Person AS c
|
|
ON sp.BusinessEntityID = c.BusinessEntityID
|
|
WHERE sp.BusinessEntityID LIKE '2%'
|
|
ORDER BY sp.BusinessEntityID, c.LastName;
|
|
GO
|
|
|
|
|
|
CREATE VIEW hiredate_view
|
|
AS
|
|
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate
|
|
FROM HumanResources.Employee e
|
|
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;
|
|
GO
|
|
|