Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries
15 Pages704 Words88 Views
Added on 2019-09-21
About This Document
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.
Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries
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);
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Draft SQL Implementation Assignmentlg...
|12
|1254
|190
Database Concepts Question Answer 2022lg...
|14
|1624
|54
Task 1 CREATE TABLE EMPDBlg...
|7
|1224
|62
Data Modelling & Database Design | Assignmentlg...