系统设计与数据库系统 作业一 Basic SQL command LAB

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:

EMPNO	PRIMARY KEY NOT NULL IF PRIMARY KEY MODIFED THEN CHILD MUST BE UPDATED	NUMBER(4)
ENAME	NOT NULL	VARCHAR2(10)
JOB	NOT NULL	VARCHAR2(9)
MGR	REFERENCES EMP(EMPNO)	NUMBER(4)
HIREDATE	NOT NULL CANNOT BE GREATER THAN TODAYS DATE	DATE
SAL	NOT NULL AND MORE THAN 5000	NUMBER(7,2)
COMM		NUMBER(7,2)
DEPTNO	REFERENCES DEPT TABLE DEFAULT 10	NUMBER(2)

DEPT TABLE:

DEPTNO	PRIMARY KEY NOT NULL DON’T ALLOW PRIMARY KEY TO BE MODIFIED IF CHILD RECORD EXIST	NUMBER(2)
DNAME		CHAR(10)
LOC		CHAR(10)

INSERT TUPLES FOR EMP TABLE:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-90	13750	NULL	20
7499	ALLEN	SALESMAN	7698	20-FEB-89	19000	6400	30
7521	WARD	SALESMAN	7698	1993-02-22	18500	4250	30
7566	JONES	MANAGER	7839	1989-04-02	26850	NULL	20
7654	MARTIN	SALESMAN	7698	1997-09-28	15675	3500	30
7698	BLAKE	MANAGER	7839	1990-05-01	24000	NULL	30
7782	CLARK	MANAGER	7839	1988-06-09	27500	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	19500	NULL	20
7839	KING	PRESIDENT		1983-11-17	82500	NULL	10
7844	TURNER	SALESMAN	7698	1992-09-08	18500	6250	30
7876	ADAMS	CLERK	7788	1996-05-23	11900	NULL	20
7900	JAMES	CLERK	7698	1995-12-03	12500	NULL	30
7902	FORD	ANALYST	7566	1991-12-03	21500	NULL	20
7934	MILLER	CLERK	7782	1995-01-23	13250	NULL	10
3258	GREEN	SALESMAN	4422	24-JUL-95	18500	2750	50
4422	STEVENS	MANAGER	7839	14-JAN-94	24750		50
6548	BARNES	CLERK	4422	16-JAN-95	11950		50
7500	CAMPBELL	ANALYST	7566	30-OCT-92	24500	0	40

INSERT TUPLES FOR DEPT TABLE:

DEPTNO	DNAME	LOC
10	ACCOUNTING	LONDON
30	SALES	LIVERPOOL
40	OPERATIONS	STAFFORD
50	MARKETING	LUTON
20	RESEARCH	PRESTON

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

7. 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;

8. Find all employees whose job is either Clerk or Salesman.

SELECT * FROM emp2019284073 WHERE JOB = "CLERK" OR JOB = "SALESMAN"

9. Find any Clerk who is not in department 10

SELECT * FROM emp2019284073 WHERE DEPTNO!=10

10. Find everyone whose job is Salesman and all the Analysts in department 20

SELECT * FROM emp2019284073 WHERE JOB = 'SALESMAN' OR (JOB = 'ANALYST' AND DEPTNO = 20);

11. Find all the employees who earn between £15,000 and £20,000. Show the employee name, department and salary.

SELECT ENAME,DEPTNO,SAL FROM emp2019284073 WHERE SAL BETWEEN 15000 AND 20000;

12. Find the name of the President.

SELECT ENAME FROM emp2019284073 WHERE JOB = 'PRESIDENT';

13. Find all the employees whose last names end with S

SELECT * FROM emp2019284073 WHERE ENAME LIKE '%S';

14. List the employees whose names have TH or LL in them

SELECT * FROM emp2019284073 WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';

15. List only those employees who receive commission.

SELECT * FROM emp2019284073 WHERE COMM IS NOT NULL;

16. Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.

SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO FROM emp2019284073 ORDER BY ENAME;

17. Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.

SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO FROM emp2019284073 ORDER BY SAL DESC;

18. List all salesmen in descending order by commission divided by their salary

SELECT * FROM emp2019284073 ORDER BY ifnull(COMM, 0)/SAL DESC;

19. Order employees in department 30 who receive commision, in ascending order by commission

SELECT * FROM emp2019284073 WHERE DEPTNO=30 AND COMM IS NOT NULL ORDER BY COMM DESC;

20. Find the names, jobs, salaries and commissions of all employees who do not have managers.

SELECT ENAME,JOB,SAL,COMM FROM emp2019284073 WHERE MGR IS NULL;

21. Find all the salesmen in department 30 who have a salary greater than or equal to £18000.

SELECT * FROM emp2019284073 WHERE DEPTNO=30 AND JOB = 'SALESMAN' AND SAL >= 18000;

22. Find the employees who were hired before 01-Jan-1998 and have salary above 5000 or below 1000.

SELECT * FROM emp2019284073 WHERE HIREDATE<'1998-01-01' AND (SAL > 5000 OR SAL<1000);

23. What is the command to add primary key constraint to EMPNO

ALTER TABLE emp2019284073 ADD PRIMARY KEY(EMPNO);

24. What is the command to add a new column EMP_family_name to existing EMP table

ALTER TABLE emp2019284073 ADD EMP_family_name VARCHAR(20);

25. How to drop primary key constraint for EMPNO

ALTER TABLE emp2019284073 DROP PRIMARY KEY;

26. rename EMP table to EMPLOYEE

RENAME TABLE emp2019284073 TO EMPLOYEE_2019284073;

27. rename EMPLOYEE back to EMP

RENAME TABLE EMPLOYEE_2019284073 TO emp2019284073;

28. What is the SQL command to remove column EMP_family_name from EMP table

ALTER TABLE emp2019284073 DROP COLUMN EMP_family_name;

29. What is the SQL command to copy emp table to employee table

CREATE TABLE EMPLOYEE_2019284073 AS SELECT * FROM emp2019284073;

30. What is the SQL command to drop employee table

DROP TABLE EMPLOYEE_2019284073;

31. What is the SQL command to display name’s of employee entered interactively from user

SELECT * FROM emp2019284073 WHERE ENAME = '&ENTER';

32. What is the SQL command to find the employee whose commission is NULL

SELECT * FROM emp2019284073 WHERE COMM IS NULL;

3. Experiment process and content

1. From this experiment of MySQL, I learned how to build a database, how to initialize the database, how to insert data into tables and how to select and edit values by language of MySQL.
2. MySQL has more convenient way to deal with huge data, which makes it fast to run. In addition, it offers enough operations to deal with this data.
3. During this experiment, errors occur sometimes. With the help of my classmates and knowledge in Internet, I finally finished this experiment successfully.