May 26, 2016 at 10:53 am
I'm new to the SQL world! I am trying to create a script for the attached is the document with instruction below is what I created so to the end but I'm sure it is incorrect. Could someone please create the correct script so I can see where I went wrong?:-)
CREATE DATABASE DAYCLASS013DB;
GO
CREATE TABLE dbo.Employees
(
EmpID int IDENTITY (1,1) PRIMARY KEY NOT NULL,
LastName varchar (15) NOT NULL,
FirstName varchar (10) NOT NULL,
JobTitle nvarchar (35) NOT NULL,
Title varchar (5) NOT NULL,
BirthDate DATE NOT NULL,
HireDate DATE NOT NULL
);
GO
INSERT INTO dbo.Employees (LastName, FirstName, JobTitle, Title, BirthDate, HireDate)
VALUES
('Babster','Judy','CEO','Ms.','12/8/1978','5/1/2007'),
('Foley','Bob','Vice President-Sales','Dr.','2/19/1982','8/14/2007'),
('Lewis','Sarah','Sales Manager','Ms.','8/30/1983','4/1/2007'),
('Palmer','Helen','Senior Sales Reprensentative','Mrs.','9/19/1967','5/3/2008');
GO
SELECT *
FROM dbo.Employees
CREATE TABLE dbo.Orders
(
OrderID int IDENTITY (11058,1) PRIMARY KEY NOT NULL,
EmpID int NOT NULL,
OrderDate DATE NOT NULL,
ShipDate DATE
);
GO
INSERT INTO dbo.Orders (EmpID, OrderDate, ShipDate)
VALUES
('4','9/29/2014',NULL),
('2','9/29/2014',NULL),
('2','9/30/2014','10/2/2014'),
('4','9/30/2014',NULL),
('4','9/30/2014',NULL),
('3','9/30/2014','10/2/2014'),
('3','10/1/2014','10/3/2014'),
('3','10/3/2014',NULL),
('2','10/1/2014','10/5/2014'),
('4','10/2/2014','10/6/2014');
GO
SELECT *
FROM dbo.Orders
ALTER TABLE dbo.Orders
ADD EmpFullName varchar (35),
DateOrderPlaced DATE;
CREATE VIEW Employee_Shipped_Orders
WITH SCHEMABINDING
AS
SELECT OrderID,EmpFullName,DateOrderPlaced
FROM dbo.Orders
WITH CHECK OPTION
SELECT *
FROM Employee_Shipped_Orders
DROP VIEW Employee_Shipped_Orders;
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
CREATE UNIQUE CLUSTERED INDEX VIX_Emp_OrderID
ON dbo.Orders (OrderID);
GO
ALTER TABLE dbo.Orders
DROP CONSTRAINT pk_OrderID
CREATE PROCEDURE usp_Get_Employee_Shipped_Orders
AS
SELECT OrderID,EmpFullName,DateOrderPlaced
FROM dbo.Orders AS o
JOIN dbo.Employees AS e
ON o.empid = e.empid
GO
EXEC usp_Get_Employee_Shipped_Orders
@EmpId = 3
CREATE FUNCTION fn_See_Employee_Shipped_Orders
RETURNS TABLE
AS
RETURN
SELECT EmpID, OrderID, EmpFullName, DateOrderPlaced
FROM dbo.Orders
GO
EXEC usp_Get_Employee_Shipped_Orders;
May 26, 2016 at 11:47 am
Here are some errors:
- You didn't create the specified columns on your view, you just mentioned them. You need to use a join to get information from the employees table and format the date correctly. You didn't ensure that the orders were shipped.
- You didn't create the clustered index on the view, you tried to create it on the table.
- You didn't use parameters on the stored procedure to limit the employees.
- You didn't use parameters on the function to limit the employees and a special option to return only one row (which should be ensured to be the correct one).
Try correcting them your self. If needed, post back to get more help. I'm not giving the answers of an exam.
May 26, 2016 at 12:11 pm
Thank you is just says practical exam this is me studying.
May 26, 2016 at 12:19 pm
Don't get me wrong. I can help you, that's why I gave simple hints on were to fix the code.
But if I just give you the answers, you won't get through the process of learning SQL.
May 26, 2016 at 12:35 pm
I understand!!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply