1. Purpose of Experiment purpose and Requirements

Please show all work for these problems.

Just writing down the answer will not get full credit.

Note: create a Table EMP_your_StudentID and DEPT_your_studentID with all required constraints both Integrity and Referential Integrity Constraint.

EMP Table:

DEPT TABLE:

INSERT TUPLES FOR EMP TABLE:

INSERT TUPLES FOR DEPT TABLE

2. Methods and steps

FIRST: CREATE THE DATABASE

We can create the database by the following code:

CREATE DATABASE `dong2019284073` /*!40100 DEFAULT CHARACTER SET latin1 */

SECOND: CREAT THE TABLES

We can create the tables and then initialize each column by the following code:

①dept2019284073

CREATE TABLE `dept2019284073` (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` char(10) DEFAULT NULL,
  `LOC` char(10) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

②emp2019284073

CREATE TABLE `emp2019284073` (
  `EMPNO` decimal(4,0) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` decimal(4,0) DEFAULT NULL,
  `HIREDATE` date NOT NULL,
  `SAL` decimal(7,2) NOT NULL,
  `COMM` decimal(7,2) DEFAULT NULL,
  `DEPTNO` decimal(2,0) DEFAULT '10',
  PRIMARY KEY (`EMPNO`),
  KEY `fk_EMP_DEPT` (`DEPTNO`),
  KEY `fk_EMP_EMP1` (`MGR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

THIRD：INSERT VALUES INTO TABLES

We can use the following code to insert values into tables. In addition, we must keep the data we type in corresponding order of columns of the tables.

INSERT INTO dept2019284073   
VALUES   
(10 , "ACCOUNTING",  "LONDON" ),
(30  ,"SALES" , "LIVERPOOL" ), 
(40 , "OPERATIONS" , "STAFFORD" ), 
(50 , "MARKETING" , "LUTON" ), 
(20 , "RESEARCH"  ,"PRESTON")

The structure can be shown in the following two pictures.

FORTH: USE THE COMMAND TO QUERY AND EDIT THE TABLES

1. List all information about the employees.

SELECT * FROM dong2019284073.emp2019284073;

2. List all information about the departments

SELECT * FROM dong2019284073.dept2019284073;

3. List only the following information from the EMP table ( Employee name, employee number, salary, department number)

SELECT ENAME,EMPNO,SAL,DEPTNO FROM emp2019284073;

4. List details of employees in departments 10 and 30.

SELECT * FROM emp2019284073 WHERE DEPTNO = 10 OR DEPTNO = 30;

5. List all the jobs in the EMP table eliminating duplicates.

SELECT DISTINCT JOB FROM emp2019284073;

6. What are the names of the employees who earn less than £20,000?

SELECT ENAME FROM emp2019284073 WHERE SAL < 20000

What is the name, job title and employee number of the person in department 20 who earns more than £25000?

SELECT ENAME,JOB,EMPNO FROM emp2019284073 WHERE DEPTNO=20 AND SAL > 25000;