SQL - My Database

I've included the queries to create the tables for an online TShirt Order Management System (My project).
I have also created a couple of views and stored procedures to add, delete and update tables. Feel free to use and modify the code below for your own database :)

TABLE CREATION

--
--***        CREATE TABLES
--***           -------------------------------------------------------------------------------
--***        Customers, Orders, Suppliers, TShirts, OrderDetails
--***
--******************************************************************************
--*****        AUTHOR: Denis K        DATE: 05/10/2009
--******************************************************************************
CREATE TABLE Customers
(
CustomerID INT IDENTITY(1000,1) PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
City VARCHAR(20) NOT NULL,
Country VARCHAR(20) NOT NULL,
DOB DATETIME NOT NULL,
Mobile NCHAR(10),
Status INT DEFAULT 0 
)


CREATE TABLE Orders
(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
OrderDate DATETIME,
CustomerID INT NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
)

CREATE TABLE Suppliers
(
SupplierID INT IDENTITY(1000,1) PRIMARY KEY,
CompanyName VARCHAR(20) NOT NULL,
ContactName VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
City VARCHAR(20) NOT NULL,
Phone VARCHAR(15) NOT NULL,
Fax VARCHAR(15) NOT NULL,
HomePage VARCHAR(30),
Status INT DEFAULT 0
)

CREATE TABLE TShirts
(
TShirtID INT IDENTITY(50000,1) PRIMARY KEY,
ProductName VARCHAR(20) NOT NULL,
Size VARCHAR(20) NOT NULL,
Color VARCHAR(20) NOT NULL,
UnitPrice MONEY,
UnitsInStock INT,
Discontinued INT DEFAULT 0,
Picture VARCHAR(40),
SupplierID INT NOT NULL, 
FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE OrderDetails
(
OrderDetailsID INT IDENTITY(1,1) PRIMARY KEY,
Quantity INT NOT NULL,
Discount MONEY,
OrderID INT NOT NULL, 
TShirtID INT NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
FOREIGN KEY (TShirtID) REFERENCES TShirts (TShirtID)
)

VIEWS AND STORED PROCEDURES

********************************************************************************
CUSTOMERS
********************************************************************************

CREATE VIEW view_DisplayAllCustomers AS 
SELECT     CustomerID, FirstName, LastName, Address, City, Country, DOB, Mobile, Status
FROM         Customers


CREATE VIEW view_DisplayActiveCustomers AS 
SELECT     CustomerID, FirstName, LastName, Address, City, Country, DOB, Mobile, Status
FROM         Customers WHERE Status = 0


CREATE VIEW view_DisplayInactiveCustomers AS 
SELECT     CustomerID, FirstName, LastName, Address, City, Country, DOB, Mobile, Status
FROM         Customers WHERE Status = 1



    
    (
    @FirstName VARCHAR(20),
    @LastName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Country VARCHAR(20),
    @DOB DATETIME,
    @Mobile NCHAR(10),
    @Status INT
    )
    
AS
    INSERT INTO Customers (FirstName, LastName, Address, City, Country, DOB, Mobile, Status)
    VALUES (@FirstName, @LastName, @Address, @City, @Country, @DOB, @Mobile, @Status)
    RETURN


CREATE PROCEDURE dbo.spUpdateCustomer
    (
    @CustomerID INT,
    @FirstName VARCHAR(20),
    @LastName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Country VARCHAR(20),
    @DOB DATETIME,
    @Mobile NCHAR(10),
    @Status INT
    )
AS
    UPDATE Customers
        SET FirstName = @FirstName,
        LastName = @LastName,
        Address = @Address,
        City = @City,
        Country = @Country,
        DOB = @DOB,
        Mobile = @Mobile,
        Status = @Status
    WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spDeleteCustomer
    (
    @CustomerID INT
    )
    
AS
    DELETE FROM Customers WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spDeactivateCustomer
    
    (
    @CustomerID INT
    )
    
AS
    UPDATE Customers SET Status = 1
    WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spDeactivateCustomer
    
    (
    @CustomerID INT
    )
    
AS
    UPDATE Customers SET Status = 1
    WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spActivateCustomer
    (
    @CustomerID INT
    )
    
AS
    UPDATE Customers SET Status = 0
    WHERE CustomerID = @CustomerID
    RETURN




********************************************************************************
SUPPLIERS
********************************************************************************

CREATE VIEW view_DisplayAllSuppliers AS
SELECT     *
FROM         Suppliers


CREATE VIEW view_DisplayActiveSuppliers AS
SELECT     *
FROM         Suppliers
WHERE Status = 0


CREATE VIEW view_DisplayInactiveSuppliers AS
SELECT     *
FROM         Suppliers
WHERE Status = 1


CREATE PROCEDURE dbo.spAddNewSupplier
    
    (
    @CompanyName VARCHAR(20),
    @ContactName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Phone VARCHAR(15),
    @Fax VARCHAR(15),
    @HomePage VARCHAR(30),
    @Status INT
    )
    
AS
    INSERT INTO Suppliers (CompanyName, ContactName, Address, City, Phone,
    Fax, HomePage, Status)
    VALUES
    (@CompanyName, @ContactName, @Address, @City, @Phone,
    @Fax, @HomePage, @Status)
    RETURN


CREATE PROCEDURE dbo.spUpdateSupplier
    
    (
    @SupplierID INT,
    @CompanyName VARCHAR(20),
    @ContactName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Phone VARCHAR(15),
    @Fax VARCHAR(15),
    @HomePage VARCHAR(30),
    @Status INT
    )
    
AS
    UPDATE Suppliers SET CompanyName = @CompanyName,
                    ContactName = @ContactName,
                    Address = @Address,
                    City = @City,
                    Phone = @Phone,
                    Fax = @Fax,
                    HomePage = @HomePage,
                    Status = @Status
                    WHERE SupplierID = @SupplierID
    RETURN



CREATE PROCEDURE dbo.spDeleteSupplier
    
    (
    @SupplierID INT
    )
    
AS
    DELETE FROM Suppliers WHERE SupplierID = @SupplierID
    RETURN


CREATE PROCEDURE dbo.spDeactivateSupplier
    (
    @SupplierID INT
    )
    
AS
    UPDATE Suppliers SET Status = 1
    WHERE SupplierID = @SupplierID
    RETURN


CREATE PROCEDURE dbo.spActivateSupplier
    (
    @SupplierID INT
    )
    
AS
    UPDATE Suppliers SET Status = 0
    WHERE SupplierID = @SupplierID
    RETURN


********************************************************************************
T-SHIRTS
********************************************************************************
CREATE VIEW view_DisplayAllTShirts AS
SELECT     *
FROM         TShirts


CREATE VIEW view_DisplayActiveTShirts AS
SELECT     *
FROM         TShirts
WHERE Discontinued = 0


CREATE VIEW view_DisplayDiscontinuedTShirts AS
SELECT     *
FROM         TShirts
WHERE Discontinued = 1


CREATE PROCEDURE dbo.spAddNewTShirt
    
    (
    @ProductName VARCHAR(20),
    @Size VARCHAR(20),
    @Color VARCHAR(20),
    @UnitPrice MONEY,
    @UnitsInStock INT,
    @Discontinued INT,
    @Picture VARCHAR(40),
    @SupplierID INT 
    )
    
AS
    INSERT INTO TShirts (ProductName, Size,Color, UnitPrice, UnitsInStock,
    Discontinued, Picture, SupplierID)
    VALUES
    (@ProductName, @Size, @Color, @UnitPrice, @UnitsInStock,
    @Discontinued, @Picture, @SupplierID)
    RETURN


CREATE PROCEDURE dbo.spUpdateTShirt
    (
    @TShirtID INT,
    @ProductName VARCHAR(20),
    @Size VARCHAR(20),
    @Color VARCHAR(20),
    @UnitPrice MONEY,
    @UnitsInStock INT,
    @Discontinued INT,
    @Picture VARCHAR(40),
    @SupplierID INT)
AS
    UPDATE TShirt SET ProductName = @ProductName,
    Size = @Size, Color = @Color, UnitPrice = @UnitPrice,
    UnitsInStock = @UnitsInStock, Discontinued = @Discontinued,
    Picture = @Picture, SupplierID = @SupplierID
    WHERE TShirtID = @TShirtID
    RETURN


CREATE PROCEDURE dbo.spDeleteTShirt
    
    (
    @TShirtID INT
    )
    
AS
    DELETE FROM TShirts WHERE TShirtID = @TShirtID
    RETURN



CREATE PROCEDURE dbo.spDeactivateTShirt
    (
    @TShirtID INT
    )
    
AS
    UPDATE TShirts SET Discontinued = 1
    WHERE TShirtID = @TShirtID
    RETURN


CREATE PROCEDURE dbo.spActivateTShirt
    (
    @TShirtID INT
    )
    
AS
    UPDATE TShirts SET Discontinued = 0
    WHERE TShirtID = @TShirtID
    RETURN

SQL Practice In class - My SQL solution

Q1

CREATE TABLE Employee
(
FirstName VARCHAR(30),
LastName VARCHAR(30),
email VARCHAR(30),
DOB DATETIME,
Phone VARCHAR(12)
)

INSERT INTO Employee
VALUES
('John', 'Smith', 'John.Smith@yahoo.com', '2/4/1968','626 222-222')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('Steven', 'Goldfish', 'goldfish@fishhere.net', '4/4/1974', '323 455-4545')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('Paula', 'Brown', 'pb@herowndomain.org', '5/24/1978', '416 323-3232')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('James', 'Smith', 'jim@supergig.co.uk', '10/20/1980', '416 323-8888')

Q2

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (LastName LIKE 'SMITH')

Q3

SELECT     COUNT(*) AS Num_of_Emp_Like_SMITH
FROM         Employee
WHERE     (LastName LIKE 'SMITH')

Q4

SELECT     LastName, COUNT(*) AS NumberOfEmp
FROM         Employee
GROUP BY LastName
ORDER BY LastName DESC

Q5

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (DOB >= '01/01/1970')

Q6

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (Phone LIKE '416%')

Q7

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (email LIKE '%.%@%.%')

Q8

UPDATE    Employee
SET              DOB = '05/10/1974'
WHERE     (LastName = 'Goldfish') AND (FirstName = 'Steven')

Q9

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
ORDER BY DOB DESC

Q10

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (FirstName LIKE '_____%')

Q11

ALTER TABLE Employee ADD Id INT IDENTITY
CONSTRAINT pk_ID PRIMARY KEY(Id)

Q12

SELECT     FirstName, LastName, email, DOB, Phone, Id
FROM         Employee
WHERE     (DATEPART(m, DOB) = DATEPART(m, GETDATE()))

Q13

create table EmployeeHours
(
empFName Varchar(30),
empLName Varchar(30),
Date DATETIME,
Hours 



insert into employeehours
VALUES
('John', 'Smith', '5/6/2004', 8)

insert into employeehours
VALUES
('John', 'Smith', '5/7/2004', 9)

insert into employeehours
VALUES
('Steven', 'Goldfish', '5/7/2004', 8)

insert into employeehours
VALUES
('James', 'Smith', '5/7/2004', 9)

insert into employeehours
VALUES
('John', 'Smith', '5/8/2004', 8)

insert into employeehours
VALUES
('James', 'Smith', '5/8/2004', 8)

Q13


Alter table EmployeeHours
add EmployeeID INT
CONSTRAINT fk_ID Foreign Key (EmployeeID) References Employee(id)

Q14

CREATE PROCEDURE UpdateEmpId
    
AS
    UPDATE    EmployeeHours
    SET       EmployeeId =
             (SELECT     Id
              FROM       Employee
WHERE      (EmployeeHours.EmpFName = FirstName) AND (EmployeeHours.EmpLName = LastName))

PART 1 & 2 - SQL User Defined Functions - Homework solutions

Question 1
Create a function called fnGetTimeOnly to return the time part (hh:mm) of a DateTime value

ALTER FUNCTION dbo.fnGetTimeOnly(@datefield DATETIME)
RETURNS varchar(5)
AS
    BEGIN
        RETURN CONVERT(VARCHAR(2),
        DATEPART(hh,@datefield)) + ':' + CONVERT(VARCHAR(2),DATEPART(mi,@datefield))
    END

----------------------------------------------------

SELECT     dbo.fnGetTimeOnly(GETDATE()) AS Expr1

Create a function called fnGetDateOnly to return the date part (dd/mm/yyyy) of a DateTime value

ALTER FUNCTION dbo.fnGetDateOnly
    (@datefield DATETIME)
RETURNS VARCHAR(10)
AS
    BEGIN
        RETURN CONVERT(VARCHAR(2),DATEPART(dd,@datefield))+ '/' +
        CONVERT(VARCHAR(2),DATEPART(mm,@datefield))+ '/' +
        CONVERT(VARCHAR(4),DATEPART(yyyy,@datefield))
    END

---------------------------------------------------------

SELECT     dbo.fnGetDateOnly(NOW()) AS Expr1

Create a stored procedure called OrderDateTimeSpecific to return all order dates by splitting the date part and the time part into different columns. Use the functions created above in the stored procedure.

ALTER PROCEDURE dbo.OrderDateTimeSpecific

AS
        SELECT dbo.fnGetDateOnly(OrderDate) AS OrderDate, 
        dbo.fnGetTimeOnly(OrderDate) AS OrderTime 
        FROM Orders
    RETURN

Question 2
Create a function called fnContactInfo that returns a table containing all employees full name and their contact number

ALTER FUNCTION dbo.fnContactInfo()
RETURNS @contactInfoTable TABLE (fullname varchar(30), contactnumber varchar(24))
AS
    BEGIN
        INSERT INTO @contactInfoTable
           SELECT FirstName + ' ' + LastName, HomePhone FROM Employees
    RETURN
    END

Create a query to return all values from the fnContactInfo function

SELECT     fullname, contactnumber
FROM         dbo.fnContactInfo() AS fnContactInfo_1

Question 3
Create a function called fnLastDay to get the last day in a month of a given date.

ALTER FUNCTION dbo.fnLastDay(@givendate DATETIME)
RETURNS VARCHAR(30)
AS
    BEGIN
        DECLARE @dayindex INT
        DECLARE @lastday VARCHAR(30)
        DECLARE @lastdate DATETIME
        
        SET @lastdate = DATEADD(day, - 1, DATEADD(month, DATEDIFF(month, 0, @givendate) + 1, 0))
        SET @dayindex = CONVERT(INT,DATEPART(weekday,@lastdate))
        
        SELECT @lastday =
        CASE @dayindex
            WHEN 1 
                THEN 'Family Day Sunday'
            WHEN 2 
                THEN 'Back to work Day Monday'
            WHEN 3 
                THEN 'Movie night Tuesday'
            WHEN 4
                THEN 'Borrow money day Wednesday'
            WHEN 5 
                THEN 'Shopping spree Thursday'
            WHEN 6
                THEN 'Binge drinking night Friday'
            WHEN 7
                THEN 'Activities day Saturday'
        END
        
        
        RETURN @lastday
    END

Create a query to return all employees and their pay day last month, this month, and next month. The pay day is always the last day of the month.

SELECT     LastName, FirstName, 
dbo.fnLastDay(DATEADD(month, - 1, GETDATE())) AS PreviousMonthPayDay, 
dbo.fnLastDay(GETDATE()) AS ThisMonthPayDay, 
dbo.fnLastDay(DATEADD(month, 1, GETDATE())) AS NextMonthPayDay
FROM         Employees

Question 4
Create a function called fnRoundCurrency to format the currency value into the correct monetary format. For e.g. 39.07 should be rounded to 39.10.

ALTER FUNCTION dbo.fnRoundCurrency(@currency MONEY)

RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN CAST ((ROUND((@currency * 2),1) /10) *5 AS DECIMAL(10,2))
END

Modify the stored procedure created last week and call this function to format the new price of the first ten products into the right format.

ALTER PROCEDURE dbo.spShowCorrectPrice
AS
    SELECT TOP 10 *,dbo.fnRoundCurrency(UnitPrice) as RoundedPrice FROM Products
    
    RETURN

PART II
Create a table called Books in Northwind database containing BookID, ISBN, Publisher, Author, Year, Country, Category, Description.

CREATE TABLE Books
(
BookID INT,
ISBN VARCHAR(13),
Publisher VARCHAR(30),
Author VARCHAR(30),
Year INT,
Country VARCHAR(20),
Category VARCHAR(30),
Description VARCHAR(30),
CONSTRAINT pk_BookID PRIMARY KEY (BookID)
)

Question 1: FUNCTIONS TO CHECK CONSTRAINTS IN A TABLE DEFINITION
Create a function called fnValidISBN to check the value entered into ISBN field in the Books table. The following requirements are to check for valid ISBN:
ISBN numbers can contain 10 or 13 digits number.
Assuming that the company only accepts English-language publisher books, make sure that the ISBN follows the systematic pattern.
To check if the numbers entered are valid or not, use the formula and pattern in this webpage: http://en.wikipedia.org/wiki/International_Standard_Book_Number

ALTER FUNCTION dbo.fnValidISBN 
    (@isbn VARCHAR(13))
RETURNS INT
AS
    BEGIN
        DECLARE @result INT
        DECLARE @isbn_ten INT
        DECLARE @isbn_thirteen INT
        DECLARE @isbn_sum INT
        DECLARE @startpos INT
        DECLARE @isbnlen INT
        
        
        SET @isbn_ten = 9
        SET @isbn_thirteen = 12
        SET @isbn_sum = 0
        SET @startpos = 1
        
        
        SET @isbnlen =  LEN(@isbn)
        
        IF (@isbnlen = @isbn_ten) 
        BEGIN
                WHILE (@startpos<= @isbn_ten+1)
                BEGIN
                    SET @isbn_sum = @isbn_sum + (@isbn_ten * SUBSTRING(@isbn, @startpos, 1))
                    SET @startpos = @startpos + 1
                END
                
                IF (11-@isbn_sum%11) = SUBSTRING(@isbn,10,1)
                    SET @result = 0    --Success
                ELSE 
                    SET @result= 1    --Failure
        END
        
        ELSE IF (@isbnlen = @isbn_thirteen+1)
        BEGIN
                WHILE (@startpos <= @isbn_thirteen)
                BEGIN
                    IF @startpos % 2 <> 0
                        BEGIN
                            SET @isbn_sum = @isbn_sum + (1 * SUBSTRING(@isbn, @startpos, 1))
                            SET @startpos = @startpos + 1
                        END
                    ELSE
                        BEGIN
                            SET @isbn_sum = @isbn_sum + (3 * SUBSTRING(@isbn, @startpos, 1))
                            SET @startpos = @startpos + 1
                        END
                END
                
                
                IF (10 - @isbn_sum % 10) = SUBSTRING(@isbn,13,1)
                    SET @result = 0    --Success
                ELSE 
                    SET @result= 1    --Failure
        END
        
        ELSE SET @result = 1
        
        RETURN @result
    END


---------------------------------------------

SELECT     dbo.fnValidISBN(9780306406156) AS Expr1

SQL Northwind DB - Stored Procedures

1. Create a stored procedure to search employees by the first few letters in their last name.

CREATE PROCEDURE dbo.spSearchEmployeebyLName
    @LName nvarchar(20)

AS
    SET @LName = @LName + '%'
    SELECT * FROM Employees
    WHERE LastName LIKE @LName 
    RETURN

2. Create a stored procedure to create a new product and a new category by passing the CategoryName, ProductName, and the status (Discontinued) is False.

CREATE PROCEDURE spCreateNewProduct
    
    @CategoryName nvarchar(15),
    @ProductName nvarchar(40),
    @Status bit = 'False'

AS
    DECLARE @CatID INT;
    
    INSERT INTO Categories (CategoryName)
    VALUES ( @CategoryName )
    SET @CatID=@@IDENTITY
    
    INSERT INTO Products (ProductName, CategoryID, Discontinued) 
    VALUES (@ProductName, @CatID, @Status)
    
    RETURN

3. Create a stored procedure to show the stock level of every product in the Products table. For unit stock under 20, the stock level is low. Unit stock above 100, the stock level is high. For the rest, the stock level is medium.

CREATE PROCEDURE spShowStockLevel 
    
AS
    SELECT ProductID, ProductName, UnitsInStock,
    CASE
    WHEN UnitsInStock <20
        THEN 'LOW'
    WHEN UnitsInStock >100
        THEN 'HIGH'
    ELSE
        'MEDIUM'
    END
    AS StockLevel
    FROM Products
    RETURN

4. Create a stored procedure to display the territory status for each territory in Territories table. Territories id that starts with 9 is considered as big cities. Territories id starts with 0 or 1 is considered as small cities. Territories id starts with 2 to 6 is considered as other cities. Territories id starts with 7 or 8 is considered as popular cities.

CREATE PROCEDURE spShowTerritoryStatus

AS
    SELECT TerritoryID, TerritoryDescription,
    CASE 
        WHEN TerritoryID LIKE '9%'
            THEN 'big cities'
        WHEN TerritoryID LIKE '[0-1]%'
            THEN 'small cities'
        WHEN TerritoryID LIKE '[2-6]%'
            THEN 'other cities'
        WHEN TerritoryID LIKE '[7-8]%'
            THEN 'big cities'
    END
    AS TerritoryStatus
    FROM Territories
    
    RETURN

5. Create a stored procedure to find the new price of the first 10 products in Products table. The new price will be calculated based on the amount of percentage inputted into the procedure. The output should show the old and the new prices.

CREATE PROCEDURE spFindNewPrice
    @Percentage DECIMAL
AS
    SELECT ProductID, ProductName, UnitPrice, 
    NewUnitPrice = ROUND(( ( (@Percentage/100) +1 ) * UnitPrice ),2)     
    FROM Products
    RETURN