Showing posts with label Employee and Department Tables for SQL Server. Show all posts
Showing posts with label Employee and Department Tables for SQL Server. Show all posts

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

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...