Monday, July 20, 2020

Oracle SQL Emp and Dept Tables For SQL Server

--Table definition - Dept
CREATE TABLE Dept( 
DeptNo INT
DName VARCHAR(14), 
Loc VARCHAR(13), 
CONSTRAINT pk_Dept PRIMARY KEY (DeptNo) ); 

-- Tabe Definition - Emp: 
CREATE TABLE Emp( 
EmpNo INT
EName VARCHAR(10), 
Job VARCHAR(9), 
Mgr INT
HireDate date
Sal INT
Comm INT
DeptNo INT
CONSTRAINT pk_Emp PRIMARY KEY (EmpNo), 
CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo) REFERENCES Dept (DeptNo) ); 

--Dept Table data: 
INSERT INTO Dept VALUES(10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO Dept VALUES(20, 'RESEARCH', 'DALLAS'); 
INSERT INTO Dept VALUES(30, 'SALES', 'CHICAGO'); 
INSERT INTO Dept VALUES(40, 'OPERATIONS', 'BOSTON'); 

--Emp Table data: 
INSERT INTO Emp VALUES( 7839, 'KING', 'PRESIDENT', NULL, CONVERT(DATETIME,'17-11-1981',103), 5000, NULL, 10 ); 
INSERT INTO Emp VALUES( 7698, 'BLAKE', 'MANAGER', 7839, CONVERT(DATETIME,'1-5-1981',103), 2850, NULL, 30 ); 
INSERT INTO Emp VALUES( 7782, 'CLARK', 'MANAGER', 7839, CONVERT(DATETIME,'9-6-1981',103), 2450, NULL, 10 ); 
INSERT INTO Emp VALUES( 7566, 'JONES', 'MANAGER', 7839, CONVERT(DATETIME,'2-4-1981',103), 2975, NULL, 20 ); 
INSERT INTO Emp VALUES( 7788, 'SCOTT', 'ANALYST', 7566, CONVERT(DATETIME,'13-JUL-87',103) - 85, 3000, NULL, 20 ); 
INSERT INTO Emp VALUES( 7902, 'FORD', 'ANALYST', 7566, CONVERT(DATETIME,'3-12-1981',103), 3000, NULL, 20 ); 
INSERT INTO Emp VALUES( 7369, 'SMITH', 'CLERK', 7902, CONVERT(DATETIME,'17-12-1980',103), 800, NULL, 20 ); 
INSERT INTO Emp VALUES( 7499, 'ALLEN', 'SALESMAN', 7698, CONVERT(DATETIME,'20-2-1981',103), 1600, 300, 30 ); 
INSERT INTO Emp VALUES( 7521, 'WARD', 'SALESMAN', 7698, CONVERT(DATETIME,'22-2-1981',103), 1250, 500, 30 ); 
INSERT INTO Emp VALUES( 7654, 'MARTIN', 'SALESMAN', 7698, CONVERT(DATETIME,'28-9-1981',103), 1250, 1400, 30 ); 
INSERT INTO Emp VALUES( 7844, 'TURNER', 'SALESMAN', 7698, CONVERT(DATETIME,'8-9-1981',103), 1500, 0, 30 ); 
INSERT INTO Emp VALUES( 7876, 'ADAMS', 'CLERK', 7788, CONVERT(DATETIME,'13-JUL-87', 103), 1100, NULL, 20 ); 
INSERT INTO Emp VALUES( 7900, 'JAMES', 'CLERK', 7698, CONVERT(DATETIME,'3-12-1981',103), 950, NULL, 30 ); 
INSERT INTO Emp VALUES( 7934, 'MILLER', 'CLERK', 7782, CONVERT(DATETIME,'23-1-1982',103), 1300, NULL, 10 );
Go

SELECT * FROM Emp
SELECT * FROM Dept

No comments:

Post a Comment

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...