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

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

   

   ?

2. Insert the following data

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

   

   ?

   ?

4. Check that you have created 3 new records in Loans.

   select * from loans2019274072;

   

   ?

5. The Loans table must be altered to include another column OUTST NUMERIC(8,2)

   alter table loans2019274072 add column (OUTST NUMERIC(8,2));

   

   ?

6. Add 10% interest to all M type loans

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

   

   ?

7. Remove all loans less than ￡3000.00

   delete from loans2019274072 where amnt<3000;

   

   ?

8. Change the name of loans table to accounts

   alter table loan2019274072 rename account2019274072;

   

   ?

9. Change the name of column LNO to LOANNO.

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

   

   ?

   ?

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

    

    ?

11. Use the view to show employees in department 30 having jobs which are not salesman.

    select ename,job from dept30_2019274072 where job!='salesman';

    

    ?

12. Create a view which shows summary information for each department.

    create view view_dept2019274072 as select * from dept2019274072;

    

    ?