Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints.
Client (clientNo, ClientName, addr)
Sales_order
(ordNo, ordDate)
The relationship between Client & Sales_order is one-to-many.
Constraints:
- Primary Key, ordDate should not be NULL.
Consider the
above tables and execute the following queries:
Solution:-
SQL>create
table client(cno varchar(10) primary key,cname varchar(20),addr varchar(20));
SQL>insert
into client values('CN001','Abhay','Pune');
SQL>insert
into client values('CN002','Patil','Pimpri');
SQL>select
* from client;
SQL>create
table sales_order(ordno int primary key,ordDate varchar(23) not null,cno
varchar(10));
SQL>insert
into sales_order values(1,'18/04/2019','CN001');
SQL>insert
into sales_order values(2,'09/08/2019','CN002');
SQL>select
* from sales_order;
1.Add column amount into Sales_order table with data type int.
SQL>ALTER
TABLE Sales_order ADD amount int;
2. Delete the details of the clients whose
names start with ‘A’ character.
SQL>DELETE
FROM Client WHERE ClientName like ‘a%’;
Consider the
above tables and execute the following queries
1. Delete
sales order details of client whose name is “Patil” and order date is
“09/08/2019”.
SQL>delete
from sales_order where ordDate='09/08/2019' and cno in(select cno from client
where cname='Patil');
SQL>select
* from sales_order;
2. Change
order date of client_No ‘CN001’ to ‘18/03/2019’.
SQL>update
sales_order set ordDate='18/03/2019'
where cno='CN001';
SQL>select
* from sales_order;
3. Delete
all sales_record having order date is before ‘10 /02/2018’.
SQL>delete
from sales_order where
ordDate<'20/10/2019';
4. Display date wise sales_order given by clients.
SQL>select
ordDate, ordNo, clientNo from sales_order
order by ordDate;
5. Update the address of client to “Pimpri” whose name is ‘Mr. Roy’.
SQL>update
client set addr='Pimpri' where
ClientName ='Mr.Roy';
