Company Database using SQL
Samath
Posted by Samath
Last Updated: January 18, 2024
SQL

Hello World! Today I am going to create a simple company database using SQL syntax.

Create Database Company;

The CREATE DATABASE statement is used to create a database. In this case I am creating a simple database called Company.

Use Company;

The USE statement is use to select database. If this statement is not included the Database engine won't know which Database to operate on.

CREATE TABLE DEPARTMENT(Dname varchar(50),
						Dnumber INT NOT NULL PRIMARY KEY,
						Mgr_ssn INT NULL, 
						Mgr_start_date date);

Create a Table Called department in the Company database. All the attributes including the PRIMARY KEY has been added.

INSERT INTO DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date) VALUES('Finince',1111,9090,'5/8/7');
INSERT INTO DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date) VALUES('Human Resources',2222,8080,'5/6/9');
INSERT INTO DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date) VALUES('Design',3333,7070,'8/4/7');
INSERT INTO DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date) VALUES('Programming',4444,6060,'5/9/9');

Insert data into the department table.

CREATE TABLE EMPLOYEE(Fname varchar(50),
					 Minit varchar(4),
					 Lname varchar(50),
					 Ssn INT NOT NULL PRIMARY KEY,
					 Bdate date,
					 Address varchar(50),
					 Sex varchar(10),
					 Salary float,
					 Super_ssn int,
					 Dno INT,
					 foreign key(Dno) REFERENCES DEPARTMENT(Dnumber));

Create Employee table in the Company Database, the table has a primary key that references the department database.

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('Peter','PW','Williams',9090,'5/9/1','Kingston','Male',125478,98745,2222);

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('Jenny','JS','Scott',7070,'5/9/12','St. Ann','Female',124576,69875,4444);

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('Jimmy','JW','Walker',9785,'9/9/8','Mandeville','Male',234512,87545,3333);	

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('Tillroy','TB','Bent',6060,'8/9/7','St. Ann','Male',147891,69874,4444);	

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('Sandra','SW','Williams',9069,'5/9/9','Ochi Rios','Female',189756,98754,1111);

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('Roy','RP','Palmer',8080,'5/9/1','Kingston','Male',1798541,98745,2222);

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('John','WS','Smith',3270,'5/9/8','Mandeville','Male',1321458,45756,3333);	

INSERT INTO EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 
VALUES('Samath','SS','Scott',6098,'8/9/6','St. Ann','Male',1587896,65987,4444);

Insert Data into the Employee Table.

CREATE TABLE DEPT_LOCATIONS(Dnumber INT,
							Dlocation INT NOT NULL PRIMARY KEY, 
							foreign key(Dnumber) REFERENCES DEPARTMENT(Dnumber));

INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(1111,65878);
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(2222,78564);
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(3333,365478);
INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES(4444,89874);

Create and insert data into the DEPT_LOCATIONS table.

CREATE TABLE PROJECT(Pname varchar(50),
					 Pnumber INT NOT NULL PRIMARY KEY, 
					 Plocation varchar(50),
					 Dnumber INT,
					foreign key(Dnumber) REFERENCES DEPARTMENT(Dnumber));

INSERT INTO PROJECT(Pname,Pnumber,Plocation,Dnumber) VALUES('Game Development',9999,'North Wing',1111);
INSERT INTO PROJECT(Pname,Pnumber,Plocation,Dnumber) VALUES('System Development',8888,'South Wing',2222);
INSERT INTO PROJECT(Pname,Pnumber,Plocation,Dnumber) VALUES('Unit Testing',7777,'West Wing',3333);
INSERT INTO PROJECT(Pname,Pnumber,Plocation,Dnumber) VALUES('Code Writing',6666,'North-East Wing',4444);

Create and insert data into the PROJECT table.

CREATE TABLE WORKS_ON(Essn INT,
					  Pno INT,
					  Hours INT,
					  foreign key(Pno) REFERENCES PROJECT(Pnumber),
					  foreign key(Essn) REFERENCES EMPLOYEE(Ssn));

INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES(9090,9999,35);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES(8080,8888,30);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES(7070,7777,35);
INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES(6060,6666,25);

Create the WORKS_ON table. Reference both Project and Employee table. Add data to table. 

CREATE TABLE DEPENDENT(Essn INT,
					   Dependent_name varchar(50) NOT NULL PRIMARY KEY ,
					   Sex varchar(10),
					   Bdate Date,
					   Relationship varchar(20),
					  foreign key(Essn) REFERENCES EMPLOYEE(Ssn));

INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES(9090,'Jenny Peru','Female','5/9/8','Sister');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES(8080,'James Brown','Male','5/9/7','Brother');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES(7070,'Peter Miller','Female','5/9/8','Daughter');
INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES(6060,'Billy Rend','Male','5/9/8','Son');

Create DEPENDENT table, reference employee table and add record to table. 
Default Image