MYSQL数据库SQL语句练习实验 EXERCISES 7 Data Manipulation

最新推荐文章于 2024-01-04 14:00:00 发布

jennie佳妮

最新推荐文章于 2024-01-04 14:00:00 发布

阅读量469

点赞数 2

分类专栏：数据库sql 文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_51695846/article/details/121865999

版权

9 篇文章 5 订阅

订阅专栏

Create a new table called loans with columns named LNO NUMERIC (3), EMPNO NUMERIC (4), TYPE CHAR(1), AMNT NUMERIC (8,2)
```
create table loans2019274072(
LNO NUMERIC(3) PRIMARY KEY,
EMPNO NUMERIC(4),
TYPE CHAR(1),
AMNT NUMERIC(8,2));
```
Insert the following data
LNO             EMPNO       TYPE           AMNT

              23          7499                    M           20000.00

              42          7499                    C           2000.00

              65          7844                    M           3564.00
```
insert into loans2019274072 values (23,7499,'M',20000.00);
insert into loans2019274072 values (42,7499,'C',2000.00);
insert into loans2019274072 values (65,7844,'M',3564.00);
```
Check that you have created 3 new records in Loans.
```
select * from loans2019274072;
```
The Loans table must be altered to include another column OUTST NUMERIC(8,2)
```
alter table loans2019274072 add column (OUTST NUMERIC(8,2));
```

Add 10% interest to all M type loans

update loans2019274072 set amnt=amnt*1.1 where type='M';

Remove all loans less than £3000.00

delete from loans2019274072 where amnt<3000;

Change the name of loans table to accounts

alter table loan2019274072 rename account2019274072;

Change the name of column LNO to LOANNO.

ALTER TABLE xujiani2019274072.account2019274072 CHANGE COLUMN `LNO` `LOANNO` DECIMAL(3,0) NOT NULL ;

Create a view for use by personnel in department 30 showing employee name, number, job and hiredate.

create view dept30_2019274072 as select ename, empno, job, hiredate from emp2019274072 where deptno=30;

Use the view to show employees in department 30 having jobs which are not salesman.
```
select ename,job from dept30_2019274072 where job!='salesman';
```
Create a view which shows summary information for each department.
```
create view view_dept2019274072 as select * from dept2019274072;
```

关注