Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries
Added on -2019-09-21
This article provides an Employee Database with ER Diagram, Table Commands, Sample Data, and SQL Queries. It includes Department, Employee, Project, and WorksOn tables. The ER Diagram, CREATE TABLE commands, and sample data are provided. SQL queries include listing employees in alphabetical order of surname, listing female employees, listing managers, listing employees who work for the IT department, listing retiring managers, finding employees managed by James Adams, listing total hours worked by each employee, listing projects with more than two employees, and listing departments with more than 10 employees.
| 15 pages
| 704 words
| 88 views
Trusted by 2+ million users, 1000+ happy students everyday
B) CREATE TABLE COMMANDSCREATE DATABASE EMP_DB_SYSTEM;USE EMP_DB_SYSTEM;Deparment TableCREATE TABLE Department( deptNo INT PRIMARY KEY, deptName VARCHAR(40) NOT NULL, mgrEmpID INT UNIQUE DEFAULT NULL);Employee TableCREATE TABLE Employee( empID INT PRIMARY KEY, fName VARCHAR(10) NOT NULL, lName VARCHAR(10) NOT NULL, address VARCHAR(100) NOT NULL, dob DATE NOT NULL, sex ENUM('M','F') NOT NULL, position VARCHAR(40) NOT NULL, deptNo INT NOT NULL,FOREIGN KEY (deptNo) REFERENCES Department(deptNo));ALTER TABLE Department ADD FOREIGN KEY (mgrEmpID) REFERENCES Employee(empID);
Found this document preview useful?
You are reading a preview Upload your documents to download or Become a Desklib member to get accesss