This assignment requires me to insert 2-3 records into each table command by usi
ID: 3928195 • Letter: T
Question
This assignment requires me to insert 2-3 records into each table command by using the INSERT command. Below is a copy of my database with its query and ERD Diagram.
SQL Queries
CREATE TABLE department
(
dep_Id int NOT NULL,
depart_Name varchar(255) NOT NULL,
manager varchar(255),
PRIMARY KEY (dep_ID_));
CREATE TABLE employee
(
emp_ID int NOT NULL,
dept_code int NOT NULL,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
DOB varchar(255) NOT NULL,
manager_id int NOT NULL,
hiredate varchar(255) NOT NULL,
classification varchar(255),
PRIMARY KEY (emp_Id));
CREATE TABLE projects
(
prj_Id int NOT NULL,
Project_name varchar(255) NOT NULL,
Start_date varchar(255) NOT NULL,
End_date varchar(255) NOT NULL,
Critiera_level varchar(255),
PRIMARY KEY (prj_ID));
CREATE TABLE salaried_emp
(
Emp_Id int NOT NULL,
Prj_ID int NOT NULL,
Yearly_salary varchar(255),
PRIMARY KEY (emp_Id),
CONSTRAINT FK_PROJECTS FOREIGN KEY (prj_Id) REFERENCES
projects (prj_Id));
CREATE TABLE hourly_emp
(
Emp_Id int NOT NULL,
Hourly_pay int,
PRIMARY KEY (emp_Id),
CONSTRAINT FK_employee FOREIGN KEY (emp_Id) REFERENCES
Employee (emp_Id));
Link of the ERD Diagram is below
https://postimg.org/image/qz068ceov/
Explanation / Answer
insertion command is
INSERT INTO Table_name( fields ) VALUES (values );
EX:
INSERT INTO Table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
INSERT INTO department ( dep_Id,depart_Name,manager) VALUES ( 120,'CSE','Jhon' );
INSERT INTO department ( dep_Id,depart_Name,manager) VALUES ( 121,'ECE','Smith' );
INSERT INTO department ( dep_Id,depart_Name,manager) VALUES ( 122,'Mech','Josilin' );
INSERT INTO employee ( emp_ID,dept_code,fname,lname,DOB,manager_id,hiredate,classification) VALUES ( 100,1000,'Josilin','josh','10/10/1980',2001,'10/10/2011','Teacher' );
INSERT INTO employee ( emp_ID,dept_code,fname,lname,DOB,manager_id,hiredate,classification) VALUES ( 101,1000,'liam','Noah','10/10/1985',2010,'10/10/2012','Clerk' );
INSERT INTO projects ( prj_Id,Project_name,Start_date,End_date,Critiera_level) VALUES ( 1001,'UniversityInfo','20/10/2016','20/11/2016','Hard' );
INSERT INTO projects ( prj_Id,Project_name,Start_date,End_date,Critiera_level) VALUES ( 1002,'DepartmentInfo','20/10/2016','20/11/2016','Medium');
INSERT INTO salaried_emp ( Emp_Id,Prj_ID,Yearly_salary) VALUES ( 100,1001,'50000' );
INSERT INTO salaried_emp ( Emp_Id,Prj_ID,Yearly_salary) VALUES ( 101,1002,'60000' );
INSERT INTO hourly_emp ( Emp_Id,Hourly_pay) VALUES ( 100,10);
INSERT INTO hourly_emp ( Emp_Id,Hourly_pay) VALUES ( 101,10);