當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

数据库练习题（一）创建表格并做相应的查询

發布時間：2024/7/5 数据库 30 豆豆

生活随笔收集整理的這篇文章主要介紹了数据库练习题（一）创建表格并做相应的查询小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

#1. 在MySQL中分別創建表dept和employee，結構如下：

dept

+------------+--------------+------+-----+---------+----------------+

+------------+--------------+------+-----+---------+----------------+

| d_no?????? | int(11)????? | NO?? | PRI | NULL??? | auto_increment |

| d_name??? | varchar(50)? | YES? |???? | NULL??? |??????????????? |

| d_location ?| varchar(100) | YES? |???? | NULL??? |??????????????? |

+------------+--------------+------+-----+---------+----------------+

employee

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

| e_no???? | int(11)???? ??| NO?? | PRI | NULL??? |?????? |

| e_name?? | varchar(100) | NO?? |???? | NULL??? |?????? |

| e_gender | char(2)????? ?| NO?? |???? | NULL??? |?????? |

| dept_no? | int(11)????? ?| NO?? | MUL | NULL??? |?????? |

| e_job??? | varchar(100) ?| NO?? |???? | NULL??? |?????? |

| e_salary | smallint(6)? ???| NO?? |???? | NULL??? |?????? |

| hireDate | date???????? | YES? |???? | NULL??? |?????? |

+----------+--------------+------+-----+---------+-------+

#2. 在employee表中插入如下數據：

-> (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),

-> (1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),

-> (1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),

-> (1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),

-> (1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'),

-> (1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),

-> (1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),

-> (1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),

-> (1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),

-> (1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),

-> (1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),

-> (1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');

在dept表中插入如下數據：

-> (10, 'ACCOUNTING', 'ShangHai'),

-> (20, 'RESEARCH ', 'BeiJing '),

-> (30, 'SALES ', 'ShenZhen '),

-> (40, 'OPERATIONS ', 'FuJian ');

#3. 在employee表中，查詢每個部門最高工資的員工信息。

#4. 查詢員工BLAKE所在部門和部門所在地。

#5. 使用連接查詢，查詢所有員工的部門和部門信息。

#6. 在employee表中，計算不同部門的平均工資。

#7. 在employee表中，查詢員工姓名以字母’A’或’S’開頭的員工的信息。

#8. 在employee表中，將查詢記錄先按部門編號由高到低排列，再按員工工資由高到低排列。

#9. 在employee表中，查詢工資范圍在800~2500之間的員工信息。

#10.?在employee表中，查詢到目前為止，工齡大于等于10年的員工信息。

答案如下：

1、

CREATE TABLE dept

(

d_no????????INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

d_name??????VARCHAR(50),

d_location???? VARCHAR(100)

);

CREATE TABLE employee

(

e_no???????INT NOT NULL PRIMARY KEY,

e_name?????VARCHAR(100) NOT NULL,

e_gender???CHAR(2) NOT NULL,

dept_no???INT NOT NULL,

e_job??????VARCHAR(100) NOT NULL,

e_salary??SMALLINT NOT NULL,

hireDate??DATE,

CONSTRAINT dno_fk FOREIGN KEY(dept_no)

REFERENCES dept(d_no)

);

2、

向dept表中插入數據，SQL語句如下：

INSERT INTO dept

VALUES (10, 'ACCOUNTING', 'ShangHai'),

(20, 'RESEARCH ', 'BeiJing '),

(30, 'SALES ', 'ShenZhen '),

(40, 'OPERATIONS ', 'FuJian ');

向employee表中插入數據，SQL語句如下：

INSERT INTO employee

VALUES (1001, 'SMITH', 'm',20,'CLERK',800,'2005-11-12'),

(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),

(1003, 'WARD', 'f',30, 'SALESMAN',1250,'2003-05-12'),

(1004, 'JONES', 'm',20, 'MANAGER',2975,'1998-05-18'),

(1005, 'MARTIN', 'm',30, 'SALESMAN',1250,'2001-06-12'),

(1006, 'BLAKE', 'f',30, 'MANAGER',2850,'1997-02-15'),

(1007, 'CLARK', 'm',10, 'MANAGER',2450,'2002-09-12'),

(1008, 'SCOTT', 'm',20, 'ANALYST',3000,'2003-05-12'),

(1009, 'KING', 'f',10, 'PRESIDENT',5000,'1995-01-01'),

(1010, 'TURNER', 'f',30, 'SALESMAN',1500,'1997-10-12'),

(1011, 'ADAMS', 'm',20, 'CLERK',1100,'1999-10-05'),

(1012, 'JAMES', 'm',30, 'CLERK',950,'2008-06-15');

3、SELECT dept_no, MAX(e_salary) FROM employeeGROUP BY dept_no;

4、

SELECT d_no, d_location? FROM dept WHERE d_no=

(SELECT dept_no FROM employee WHEREe_name='BLAKE');

5、

SELECT e_no, e_name, dept_no,d_name,d_location

FROM employee, dept WHEREdept.d_no=employee.dept_no;

6、SELECT dept_no, AVG(e_salary) FROM employeeGROUP BY dept_no;

7、SELECT * FROM employee WHERE e_name rlike'^[as]';

8、

SELECT e_name,dept_no, e_salary

FROM employee ORDER BY dept_no DESC,e_salary DESC;

9、SELECT * FROM employee WHERE e_salaryBETWEEN 800 AND 2500;

10、SELECT * FROM employee whereYEAR(CURDATE()) -YEAR(hireDate) >= 10;

總結

以上是生活随笔為你收集整理的数据库练习题（一）创建表格并做相应的查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。