WHILE LOOP Stored Procedure - Class example

Add 5 new customers and date

ALTER PROCEDURE spAddCustomerIDtoOrdersFiveTimes
    @CustID NCHAR(5)
AS
    DECLARE @counter INT
    SET @counter = 0
    
    WHILE @counter < 5
    BEGIN
        INSERT INTO Orders (CustomerID, OrderDate)
        VALUES (@CustID, DATEADD(day,@counter,GETDATE()))
        
        SET @counter = @counter +1
    END
    
    RETURN

Calculate to new salary average

ALTER PROCEDURE spIncreaseSalUntilTargetReached
    @target money
AS
    DECLARE @avg MONEY
    DECLARE @count INT
    
    SET @count = 0
    SELECT @avg = AVG(SAL) FROM EMP
    
    WHILE @avg < @target
    BEGIN
        UPDATE EMP
            SET SAL = SAL + 50
        
        SELECT @avg = AVG(SAL) FROM EMP
        SET @count = @count + 1
    END
    
    PRINT 'No of records updated: ' + CAST(@count AS VARCHAR(20))
    PRINT 'Calculated AVG: ' + CAST(@avg AS VARCHAR(20))
        
    RETURN

Integrated Project - Part 1 - 8 HIGH LEVEL USE CASES

Please post your comments concerning your questions about use cases here. :)
This post is open to discussion, so feel free to ask questions/suggest answers!

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

EXERCISE 1

CREATE DATABASE TEMP
NAME =TEMP_EMPLOYEE
FILENAME=S:\WEERAPOL\DDL_DEMO\APP_DATA\TempEmployee.mdf

EXERCISE 2

create table publishers
(
pub_id char(4) primary key check(pub_id like'[1-2][0-9][0-9][0-9]'),
pub_name varchar(40),
suburb varchar(40),
state char(3),
country varchar(30) default 'Australia',

)   

create table jobs
(
job_id smallint primary key identity (1,1),
job_desc varchar(50) not null default 'New Position',
min_lvl tinyint not null check(min_lvl >= 10),
max_lvl tinyint not null check(max_lvl <= 250),
)      

create table employees
(
emp_id char(90) primary key check(emp_id like '[A-Z][0-9][0-9][0-9][0-9][GS]'),
fname varchar(20) not null,
minit char(1),
lname varchar(30) not null,
job_id smallint not null default 1,
job_lvl tinyint default 10,
pub_id char(4) not null default 1000,
hire_date datetime not null default getdate(),

)      

EXERCISE 3

alter table employees
add constraint FKEmployeesjobs foreign key (job_id) references jobs(job_id)
on update no action
on delete cascade

alter table employees
add constraint FKEmployeespublishers foreign key (pub_id) references publishers(pub_id)
on update no action
on delete cascade

EXERCISE 4

TABLE PUBS

//INSERT ONE AT A TIME
1>
INSERT INTO employees
                        (pub_id)
VALUES        ('1000')

2>

INSERT INTO employees
                        (pub_id)
VALUES        ('1001')

3>
INSERT INTO employees
                        (pub_id)
VALUES        ('2000')

//FROM W3SCHOOLS (DOES NOT WORK)

INSERT INTO employees
                        (pub_id)
VALUES        ('2000'),('2002'),('2003')


TABLE JOBS
1>
INSERT INTO jobs
                        (job_desc, min_lvl, max_lvl)
VALUES        ('clerk', '10', '30')

2>
INSERT INTO jobs
       (job_desc, min_lvl, max_lvl)
VALUES         ('Director','200','250')

3>
INSERT INTO jobs
       (min_lvl, max_lvl)
VALUES         ('30','100')


TABLE EMPLOYEES
1>
INSERT INTO employees
                        (emp_id, fname, lname, job_id, pub_id, hire_date)
VALUES        ('JA1000G', 'Joe', 'Abra', '1', '1000', '10/1/2008')


INSERT INTO employees
                        (emp_id, fname, lname, hire_date)
VALUES        ('JA1000G', 'Joe', 'Abra', '10/1/2008')



2>

INSERT INTO employees
values('BA2000S','Jack','S','Majo','2','220','1001')

INSERT INTO employees
                        (emp_id, fname, minit, lname, job_id, job_lvl, pub_id)
VALUES        ('BA2000S', 'Jack', 'S', 'Majo', '2', '220', '1001')

EXERCISE 5

UPDATE jobs
SET job_desc = 'Receptionist'
WHERE job_desc = 'New Position'

UPDATE employees
SET job_lvl = ceiling(job_lvl + (job_lvl *10)/100)

EXERCISE 6

DELETE
FROM pubs
WHERE pub_id = 2000

EXERCISE 7

SELECT        employees.fname + '  ' + employees.lname + ' is a ' + jobs.job_desc + ' hired  in ' + CAST(DATEPART(yyyy, employees.hire_date) AS char) AS Expr1
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
WHERE        (employees.emp_id = 'JA1000G')

SELECT        employees.fname + ' ' + ISNULL(employees.minit, '') + ' ' + employees.lname + ' is a ' + jobs.job_desc + ' hired  in ' + CAST(DATEPART(yyyy,
                        employees.hire_date) AS char) AS Expr1
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
EXERCISE 8

SELECT        employees.emp_id, employees.fname, employees.minit, employees.lname, employees.job_id, employees.job_lvl, employees.pub_id, employees.hire_date,
                        jobs.job_id AS Expr1, jobs.job_desc, jobs.min_lvl, jobs.max_lvl
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
WHERE        (employees.job_lvl = jobs.min_lvl)

SQL The second Homework

1.
SELECT COUNT(DISTINCT JOB) AS Expr1
FROM EMP

SELECT COUNT(*) AS Expr1, JOB
FROM EMP
GROUP BY JOB

2.
SELECT HIREDATE
FROM EMP
ORDER BY HIREDATE DESC

3.
SELECT HIREDATE
FROM EMP
ORDER BY HIREDATE

4.
SELECT ENAME, JOB, HIREDATE
FROM EMP
ORDER BY HIREDATE DESC

5.
SELECT DEPTNO, JOB, ENAME
FROM EMP
ORDER BY DEPTNO, SAL DESC

6.
SELECT DNAME, DEPTNO
FROM DEPT
WHERE (DEPTNO > 20)

7.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (COMM > SAL)

8.SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (COMM < SAL)

9.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL BETWEEN 500 AND 1500)

10.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL BETWEEN 500 AND 1500)
11.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

12.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL < 2000)

13.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL > 2000)

14.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (MGR IN (7902, 7566, 7788))

15.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (MGR BETWEEN 7092 AND 7788)

16.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 's%')

17.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'a%')

18.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME = 'scott')


19.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME IN ('smith', 'king'))

20.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '[wk]%') AND (DEPTNO NOT IN (10, 20, 40))

21.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'scott%martin')

22.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'm%') AND (DEPTNO = 30)

23.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '____')

24.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '_____') AND (JOB = 'manager')

25.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '____') AND (JOB = 'salesman')

26.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'b%') AND (JOB = 'manager')

27.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB LIKE '[^a]%')

28.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB LIKE '[^c]%')

29.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB IN ('manager', 'clerk')) AND (DEPTNO = 10)

30.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB IN ('analyst', 'salesman')) AND (DEPTNO = 30)

31.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'clerk') AND (SAL BETWEEN 1000 AND 2000)

32.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'manager') AND (SAL BETWEEN 2500 AND 3000)

33.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB IN ('clerk', 'manager')) AND (SAL BETWEEN 2500 AND 3000)

34.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'manager') AND (SAL BETWEEN 2000 AND 3000)

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'manager') OR (SAL BETWEEN 2000 AND 3000)

Homework - IComparable and IComparer - A step-by-step tutorial

1) Define your class City, as usual.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CityCompare
{
    public class City
    {
        //fields
        private string name;
        private string state;

        //properties
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        public string State
        {
            get { return state; }
            set { state = value; }
        }

        //constructors
        public City() { }   //null constructor
        public City(string _name, string _state)
        {
            this.name = _name;
            this.state = _state;
        }

        //override toString method
        public override string ToString()
        {
            return name + ", " + state;
        }
    }
}

2) We need IComparable to our class to provide for a simple sorting capability
We need to provide a simple sorting capability to the code. We make use of the interface IComparable. To do that, we need to tell the class to implement the interface IComparable. Just add a column (:) and the interface name next to the class name.

public class City : IComparable

3) IComparable Sorting capability - The CompareTo function
Now, we need to implement IComparable CompareTo function to provide the default sort order. We add the following code to our class:

//IComparable.CompareTo needs to be implemented 
        //to provide default sort order. Make it public
        public int CompareTo(Object obj)
        {
            City c = (City)obj; //cast Object type to City object
            return string.Compare(this.name, c.name);
        }

4) Writing your tester class with an ArrayList
In this tutorial, I used the Form1 class as the tester class. The tester class will populate the Arraylist with values.
A listbox is made available on the form so that the cities are listed there.
Also, a button that shows "Simple Sort" is available and uses the ArrayList.Sort() method and does not require any additional implementation.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;       //add this line to use ArrayList


namespace CityCompare
{
    public partial class Form1 : Form
    {
        //fields
        private ArrayList cities = new ArrayList();

        //properties
        public ArrayList Cities
        {
            get { return cities; }
            set { cities = value; }
        }

        //constructors
        public Form1()
        {
            InitializeComponent();
        }

        //methods
        //Populate the listbox
        public void PopulateListBox()
        {
            //clear listbox
            lisCitiesList.Items.Clear();

            //Populate the listbox
            foreach (Object item in cities)
            {
                lisCitiesList.Items.Add(item.ToString());
            }
        }

        //Populate the ArrayList with cities and states
        public void PopulateArrayList()
        {
            cities.Add(new City("Sydney", "New South Wales"));
            cities.Add(new City("Albury", "New South Wales"));
            cities.Add(new City("Armindale", "New South Wales"));
            cities.Add(new City("Bathurst", "New South Wales"));
            cities.Add(new City("Blue Mountains", "New South Wales"));
            cities.Add(new City("Palmerston", "Northern Territory"));
            cities.Add(new City("Darwin", "Northern Territory"));
            cities.Add(new City("Melbourne", "Victoria"));
            cities.Add(new City("Perth", "Western Australia"));
            cities.Add(new City("Albany", "Western Australia"));
            cities.Add(new City("Canning", "Western Australia"));
            cities.Add(new City("Gosnells", "Western Australia"));
            cities.Add(new City("Hobart", "Tasmania"));
            cities.Add(new City("Hobart", "AnotherState"));
        }

        //events
        private void Form1_Load(object sender, EventArgs e)
        {
            //Populate cities arraylist
            PopulateArrayList();

            //Populate listbox
            PopulateListBox();
        }

        private void btnNormalSort_Click(object sender, EventArgs e)
        {
            cities.Sort();
            PopulateListBox();  //refresh list
        }

    }
}

5) Sort list of cities first by state name, then by city name, without modifying the implementation of the City class
As Alan stated, you cannot redefine the CompareTo method. Let's stick to the requirement and define a CityComparer class that implements the Comparator<City> interface.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;       //add this line to use interface IComparer

namespace CityCompare
{                                           //STEP 2
    public class CityComparer: IComparer    //we use this IComparer as it gives
                                            //additional comparison mechanisms
    {
        //we need to implement the interface member Compare(obj,obj)
        //from the interface IComparer
        public int Compare(Object a, Object b)  //make it public
        {
            City c1 = (City)a;  //cast Object to City
            City c2 = (City)b;  //cast Object to City

            return String.Compare(c1.State, c2.State);  //State is a string, 
                                                        //hence we use String.Compare
        }

        //StateSort method
        //sorts the ArrayList elements by state
        public static IComparer StateSort()
        {
            return (IComparer)new CityComparer();
        }
    }
}

6) Call the method StateSort to sort by state in the Form1 class.

private void btnCityComparer_Click(object sender, EventArgs e)
{
    cities.Sort(CityComparer.StateSort());  //STEP 3
    PopulateListBox();  //refresh list
}

All done! Suggestions and comments are welcome.

C# - Understanding IComparable and IComparer

Check this link for an article about IComparable and IComparer.