Ask a question from expert

Ask now

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

   Added on 2019-09-21

BookmarkShareRelated Documents
EMPLOYEE DATABASE Student Name: Student ID: Course Name: Course ID:Faculty Name: University Name:
Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries_1
ContentsA)ER DIAGRAM.....................................................................................................................2B) CREATE TABLE COMMANDS..............................................................................................3Deparment Table.........................................................................................................................3Employee Table...........................................................................................................................3Project Table................................................................................................................................4WorksOn Table............................................................................................................................4C) SCREEN SHOTS OF SAMPLE DATA....................................................................................4SQL QUERIES................................................................................................................................7
Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries_2
A)ER DIAGRAM
Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries_3
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);
Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries_4

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Draft SQL Implementation Assignment
|12
|1254
|190

Database Concepts Question Answer 2022
|14
|1624
|54

Task 1 CREATE TABLE EMPDB
|7
|1224
|62

Data Modelling & Database Design | Assignment
|9
|842
|22

Database Design and SQL
|9
|1255
|102

ISYS1055 - Database Concepts | Assignment
|14
|1619
|106