You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
43 lines
1.7 KiB
43 lines
1.7 KiB
USE master
|
|
GO
|
|
CREATE DATABASE Study -- MagicCorp Database
|
|
GO
|
|
|
|
ALTER DATABASE Study -- MagicCorp Database
|
|
SET RECOVERY FULL
|
|
GO
|
|
|
|
USE Study -- MagicCorp Database
|
|
GO
|
|
CREATE TABLE DEPARTMENT(
|
|
DNO INT PRIMARY KEY,
|
|
DNAME VARCHAR(20), LOC VARCHAR(20)
|
|
)
|
|
CREATE TABLE EMPLOYEE(
|
|
ENO INT PRIMARY KEY,
|
|
ENAME NVARCHAR(20), JOB NVARCHAR(20),
|
|
MANAGER INT, HIREDATE DATETIME,
|
|
SALARY INT, COMMISSION INT,
|
|
DNO INT REFERENCES DEPARTMENT(DNO)
|
|
)
|
|
CREATE TABLE SALGRADE(
|
|
GRADE INT PRIMARY KEY IDENTITY,
|
|
LOWSAL INT, HIGHSAL INT
|
|
)
|
|
INSERT INTO DEPARTMENT VALUES(10, 'Accounting', 'Seoul'); INSERT INTO DEPARTMENT VALUES(20, 'Human', 'Incheon');
|
|
INSERT INTO DEPARTMENT VALUES(30, 'Sales', 'Yungin'); INSERT INTO DEPARTMENT VALUES(40, 'Computing', 'Suwon');
|
|
|
|
INSERT INTO SALGRADE VALUES(901, 1000); INSERT INTO SALGRADE VALUES(501, 900);
|
|
INSERT INTO SALGRADE VALUES(401, 500); INSERT INTO SALGRADE VALUES(301, 400)
|
|
INSERT INTO SALGRADE VALUES(201, 300)
|
|
|
|
INSERT INTO EMPLOYEE VALUES(101, 'e1', 'staff', 113, '2007-03-01', 300, NULL, 20)
|
|
INSERT INTO EMPLOYEE VALUES(102, 'e2', 'deputy', 105, '2007-04-02', 250, 80, 30)
|
|
INSERT INTO EMPLOYEE VALUES(103, 'e3', 'section', 105, '2005-02-10', 500, 100, 30)
|
|
INSERT INTO EMPLOYEE VALUES(104, 'e4', 'chief', 108, '2003-09-02', 600, NULL, 20)
|
|
INSERT INTO EMPLOYEE VALUES(105, 'e5', 'section', 105, '2005-04-07', 450, 200, 30)
|
|
INSERT INTO EMPLOYEE VALUES(106, 'e6', 'chief', 108, '2003-10-09', 480, NULL, 30)
|
|
INSERT INTO EMPLOYEE VALUES(107, 'e7', 'chief', 108, '2004-01-08', 520, NULL, 10)
|
|
INSERT INTO EMPLOYEE VALUES(108, 'e8', 'senior', 103, '2004-03-08', 500, 0, 30)
|
|
INSERT INTO EMPLOYEE VALUES(109, 'e9', 'ceo', NULL, '1996-10-04',1000, NULL, 20)
|
|
INSERT INTO EMPLOYEE VALUES(110, 'e10', 'section', 103, '2005-04-07', 500, NULL, 10) |