SQL Server Practice

1. Create EMP Table

EMPLOYEE_INFORMATION

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EMPLOYEE_INFORMATION](
	[ID] [uniqueidentifier] NULL,
	[USERNAME] [nvarchar](50) NULL,
	[ADDRESS] [nvarchar](50) NULL,
	[DOB] [date] NULL,
	[POSITION] [nvarchar](50) NULL
) ON [PRIMARY]
GO

2. Create SALARY Table

SALARY

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SALARY](
	[POSITION] [nvarchar](50) NULL,
	[SALARY] [int] NULL
) ON [PRIMARY]
GO

3. Create USER_LOGIN Table

USER_LOGIN

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USER_LOGIN](
	[USER_ID] [uniqueidentifier] NULL,
	[USERNAME] [nvarchar](50) NULL,
	[EMAIL] [nvarchar](50) NULL,
	[PASSWORD] [nvarchar](50) NULL
) ON [PRIMARY]
GO

4. Insert data SALARY Table

5. Insert data EMP & USER_LOGIN Table

Insert data EMP & USER_LOGIN Table

DECLARE @ID UNIQUEIDENTIFIER
SET @ID = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID, 'Chanui', 'Kyonggi', '1999-05-05', 'CEO')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID, 'Chanui', 'hcu55@naver.com', 'chanui55')

DECLARE @ID2 UNIQUEIDENTIFIER
SET @ID2 = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID2, 'Joonui', 'Seoul', '2000-12-28', 'Vice Chairman')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID2, 'Joonui', 'hju1228@naver.com', '123456')

DECLARE @ID3 UNIQUEIDENTIFIER
SET @ID3 = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID3, 'Famous', 'Kyonggi', '1999-06-04', 'General Manager')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID3, 'Famous', 'famous@naver.com', '11111')

DECLARE @ID4 UNIQUEIDENTIFIER
SET @ID4 = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID4, 'ham', 'Seoul', '1999-03-23', 'General Manager')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID4, 'ham', 'hhj@naver.com', '00000')

DECLARE @ID5 UNIQUEIDENTIFIER
SET @ID5 = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID5, 'Sunwoo', 'Kyonggi', '1998-12-04', 'Staff')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID5, 'Sunwoo', 'psw1204@nvaer.com', 'psw123')

DECLARE @ID6 UNIQUEIDENTIFIER
SET @ID6 = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID6, 'Sujin', 'Seoul', '1997-02-15', 'Staff')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID6, 'Sujin', 'suji71@naver.com', 'qwer1234')

DECLARE @ID7 UNIQUEIDENTIFIER
SET @ID7 = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID7, 'Wonbin', 'Seoul', '2000-04-21', 'Manager')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID7, 'Wonbin', 'wonbin@naver.com', 'wonbin123')

DECLARE @ID8 UNIQUEIDENTIFIER
SET @ID8 = NEWID();
INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
VALUES(@ID8, 'Jeonghyo', 'Kyonggi', '2001-09-15', 'Intern')
INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
VALUES(@ID8, 'Jeonghyo', 'jjang@naver.com', '12345')

result

• EMP_Table data
• USER_LOGIN data

6. CREATE VIEW EMP & SALARY & USER_LOGIN Table Combine

CREATE VIEW [dbo].[COMBINE_TABLE] AS
SELECT e.ID, e.USERNAME, u.EMAIL, u.[PASSWORD], e.ADDRESS, e.DOB, e.[POSITION], s.SALARY
FROM EMPLOYEE_INFORMATION AS e 
JOIN SALARY AS s ON e.[POSITION] = s.[POSITION] 
JOIN USER_LOGIN AS u ON e.USERNAME = u.USERNAME

result

• COMBINE_TABLE
  

7. CREATE procedure PROC_EMP

PROC_EMP

CREATE PROCEDURE [dbo].[PROC_EMP](
    @EMP_NAME NVARCHAR(50),
    @EMP_ADDRESS NVARCHAR(50),
    @EMP_DOB DATE,
    @EMP_POSITION NVARCHAR(50),
    @EMP_UID UNIQUEIDENTIFIER OUTPUT
) AS
BEGIN

    INSERT INTO EMPLOYEE_INFORMATION(ID, USERNAME, ADDRESS, DOB, [POSITION])
    VALUES(@EMP_UID, @EMP_NAME, @EMP_ADDRESS, @EMP_DOB, @EMP_POSITION);

    SELECT @EMP_UID, @EMP_NAME, @EMP_ADDRESS, @EMP_DOB, @EMP_POSITION;
END

8. CREATE procedure PROC_SALARY

PROC_SALARY

CREATE PROCEDURE [dbo].[PROC_SALARY](
    @SAL_POSITION NVARCHAR(50),
    @SAL_SALARY INT
) AS
BEGIN
    INSERT INTO SALARY([POSITION],SALARY)
    VALUES(@SAL_POSITION, @SAL_SALARY);

    SELECT @SAL_POSITION, @SAL_SALARY;
END

9. CREATE procedure PROC_USER_LOGIN

PROC_USER_LOGIN

CREATE PROCEDURE [dbo].[PROC_USER_LOGIN](
    @LOGIN_UID UNIQUEIDENTIFIER OUTPUT,
    @LOGIN_NAME NVARCHAR(50),
    @LOGIN_EMAIL NVARCHAR(50),
    @LOGIN_PASSWORD NVARCHAR(50)
) AS


BEGIN

    INSERT INTO USER_LOGIN(USER_ID, USERNAME, EMAIL, [PASSWORD])
    VALUES(@LOGIN_UID, @LOGIN_NAME, @LOGIN_EMAIL, @LOGIN_PASSWORD);

    SELECT @LOGIN_UID, @LOGIN_NAME, @LOGIN_EMAIL, @LOGIN_PASSWORD;
END

10. Use Procedure EXECUTE

EXEC Table

DECLARE @ID UNIQUEIDENTIFIER
SET @ID = NEWID();

EXEC PROC_EMP 'Sungwan', 'Seoul', '1999-03-29', 'Intern', @ID;
EXEC PROC_USER_LOGIN @ID, 'Sungwan', 'hongsung@gmail.com', 'sungwan123';

SELECT e.ID, e.USERNAME, u.EMAIL, u.[PASSWORD], e.ADDRESS, e.DOB, e.[POSITION], s.SALARY
FROM EMPLOYEE_INFORMATION AS e 
JOIN SALARY AS s ON e.[POSITION] = s.[POSITION] 
JOIN USER_LOGIN AS u ON e.ID = u.USER_ID

• result