MySQL - Program to create client table

2.Client Table Creation and Query

Create table client (

  cno varchar(10) check (cno like 'c%') primary key,

  cname varchar(20),

  address varchar(50),

  state varchar(20),

  baldue number(10,2));

2: From table client, create a new table client1 that contains only cno, cname, and baldue from a specified state. Accept the state during runtime.

Create table client1 as 

select cno, cname, baldue 

from client 

where state = '&state';

3. Create a new table client2 with the same structure as client but no records:

Create table client2 as 

select * from client where 1=0;

4. Add a new column penalty to the table client:

Alter table client 

add penalty number(10,2);

5. Assign penalty values:

10% of baldue for clients C1002, C1005, C1009

8% of baldue for others

6.Update client 

set penalty = 8/100 * baldue;

Update client 

set penalty = 10/100 * baldue 

where cno in ('C1002', 'C1005', 'C1009');

4. Change the name of table client to newclient:

Rename client to newclient;

5. Delete the table client2:

 

Drop table client2;