[go: up one dir, main page]
Scribd
Upload
6 views7 pages

Krish 064

The document outlines the creation and management of several database tables including client_mstr, product_mstr, salesman_mstr, STUD_MEMBER, DEPARTMENT, and sales. It includes SQL commands for inserting data, updating records, deleting entries, and querying information from these tables. Additionally, it provides specific assignment tasks related to database operations and manipulations.

Uploaded by

sutariyakrish
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views7 pages

Krish 064

The document outlines the creation and management of several database tables including client_mstr, product_mstr, salesman_mstr, STUD_MEMBER, DEPARTMENT, and sales. It includes SQL commands for inserting data, updating records, deleting entries, and querying information from these tables. Additionally, it provides specific assignment tasks related to database operations and manipulations.

Uploaded by

sutariyakrish
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

1.

create table client_mstr(client_no varchar(6),name varchar(20),city varchar(15),pincode


int(8),state varchar(15),bal_due float(10,2));
2. insert into client_mstr values("c0001","Riti Jain","Mumbai",400054,"M.H.",15000);
3. insert into client_mstr values("c0002","Mamta Mehta","Madras",780001,"T.N.",0);
4. insert into client_mstr values("c0003","Chhaya Vaid","Mumbai",400057,"M.H.",5000);
5. insert into client_mstr values("c0004","Ashwin Joshi","Bangalore",560001,"K.N.",0);
6. insert into client_mstr values("c0005","Hemant Gajjar","Mumbai",400060,"M.H.",2000);
7. insert into client_mstr values("c0006","Dipali Sharma","Mangalore",560050,"K.N.",0);

Database krish064
Table structure for table client_mstr
Column Type Null Default

client_no varchar(6) Yes NULL

name varchar(20) Yes NULL

city varchar(15) Yes NULL

pincode int(8) Yes NULL

state varchar(15) Yes NULL

bal_due float(10,2) Yes NULL

Dumping data for table client_mstr


c0001 Riti Jain Mumbai 400054 M.H. 15000.00

c0002 Mamta Mehta Madras 780001 T.N. 0.00

c0003 Chhaya Vaid Mumbai 400057 M.H. 5000.00

c0004 Ashwin Joshi Bangalore 560001 K.N. 0.00

c0005 Hemant Gajjar Mumbai 400060 M.H. 2000.00

c0006 Dipali Sharma Mangalore 560050 K.N. 0.00


CREATE TABLE Product_mstr(prod_no varchar(6),description varchar(15),profit_per float(4,2),quantity
int(8),sell_price int(8),cost_price int(8));

insert into product_mstr values("p0001","pr1",5,200,350,250);

insert into product_mstr values("p0345","pr2",6.2,150,500,350);

insert into product_mstr values("p0673","pr3",5,100,600,450);

insert into product_mstr values("p0786","pr4",5,100,750,500);

insert into product_mstr values("p0788","pr5",2.5,150,850,550);

insert into product_mstr values("p0796","pr6",4,100,300,175);

Database krish064
Table structure for table product_mstr
Column Type Null Default

prod_no varchar(6) Yes NULL

description varchar(15) Yes NULL

profit_per float(4,2) Yes NULL

quantity int(8) Yes NULL

sell_price int(8) Yes NULL

cost_price int(8) Yes NULL

Dumping data for table product_mstr


p0001 pr1 5.00 200 350 250

p0345 pr2 6.20 150 500 350

p0673 pr3 5.00 100 600 450

p0786 pr4 5.00 100 750 500

p0788 pr5 2.50 150 850 550

p0796 pr6 4.00 100 300 175


create table salesman_mstr(sman_no varchar(6),s_name varchar(20),city varchar(20),pincode
int(8),state varchar(20),sal_amt int(8),target int(6),sales int(6));

insert into salesman_mstr values("s0001","Aman","Mumbai",400002,"M.H.",3000,100,50);

insert into salesman_mstr values("s0002","Rajiv","Mumbai",400001,"M.H.",3000,200,100);

insert into salesman_mstr values("s0003","Ashish","Mumbai",400032,"M.H.",3200,200,120);

insert into salesman_mstr values("s0004","Manan","Mumbai",400044,"M.H.",3500,200,150);

Database krish064
Table structure for table salesman_mstr
Column Type Null Default

sman_no varchar(6) Yes NULL

s_name varchar(20) Yes NULL

city varchar(20) Yes NULL

pincode int(8) Yes NULL

state varchar(20) Yes NULL

sal_amt int(8) Yes NULL

target int(6) Yes NULL

sales int(6) Yes NULL

Dumping data for table salesman_mstr


s0001 Aman Mumbai 400002 M.H. 3000 100 50

s0002 Rajiv Mumbai 400001 M.H. 3000 200 100


s0003 Ashish Mumbai 400032 M.H. 3200 200 120

s0004 Manan Mumbai 400044 M.H. 3500 200 150

Questions

1. Find out names of all the clients.


Ans:- select name from client_mstr;

2. Retrieve the entire contents of client_mstr table.


Ans:- select * from client_mstr;

3. Retrieve the list of names, city and state of all clients.


Ans:- select name,city,state from client_mstr;

4. List the various products available from product_mstr table.


Ans:- select prod_no from product_mstr;

5. List all the clients who are located in Mumbai.


Ans:- select name from client_mstr where city="Mumbai";

6. Find the names of salesman who have a salary equal to Rs.3000.


Ans:- select s_name from salesman_mstr where sal_amt=3000;

7. Change the city of client_no ‘c0005’ to Bangalore.


Ans:- UPDATE CLIENT_MSTR SET CITY="Bangalore" where client_no="c0005";

8. Change the bal_due of client_no c0001 to Rs.1000.


Ans:- update client_mstr set bal_due=1000 where client_no="c0001";

9. Change the cost price of pr3 to Rs.525.


Ans:- update product_mstr set cost_price=525 where description="pr3";

10. Change the city of the salesman to Pune


Ans:- update salesman_mstr set city="Pune";

11. Delete all salesman from salesman_mstr whose salary equal to Rs.3500.
Ans:- delete from salesman_mstr where sal_amt=3500;
12. Delete all products from product_mstr where quantity is equal to 100.
Ans:- delete from product_mstr where quantity=100;

13. Delete from client_mstr where column state holds the value ‘T.N.’
Ans:- delete from client_mstr where state="T.N.";

14. Add a column called ‘Telephone’ of datatype ‘number’ and size=10 to client_mstr table.
Ans:- alter table client_mstr add column(Telephone int(10) );\

15. Change the size of sell_price column in product_mstr to 10.


Ans:= alter table product_mstr modify sell_price int(10);

16. Destroy the table client_mstr along with its data.


Ans:- drop table client_mstr;

17. Change the name of the salesman_mstr table to sman_mast.


Ans:-rename table salesman_mstr to sman_mstr;

Assignment 2

create table STUD_MEMBER(Roll_no int(10) primary key, FName varchar(20),MName


varchar(20),SName varchar(20),Dept_ID int(2),Semester int(1),ontact_No int(10),Gender
varchar(1),foreign key(Dept_ID)references DEPARTMENT(Dept_ID));
create table DEPARTMENT(Dept_ID int(2) primary key,Dept_Name varchar(50));

insert into STUD_MEMBER values(1,"Ankur","Samir","Kahar",1,1,272121,"M");


insert into STUD_MEMBER values(2,"Dhaval","Dhiren","Joshi",1,1,232122,"M");
insert into STUD_MEMBER values(3,"Ankita","Biren","Shah",1,1,112121,"F");
insert into STUD_MEMBER values(10,"Komal","Maheshkumar","Pandya",2,3,123123,"F");
insert into STUD_MEMBER values(13,"Amit","Jitenkumar","Mehta",3,3,453667,"M");
insert into STUD_MEMBER values (23,"Jinal","Ashish","Gandhi",2,1,123232,"M");
insert into STUD_MEMBER values (22,"Ganesh","Asha","Patel",2,3,124244,"M");
insert into STUD_MEMBER values (4,"Shweta","Mihir","Patel",3,1,646342,"F");
insert into STUD_MEMBER values (7,"Pooja","Mayank","Desai",3,3,328646,"F");
insert into STUD_MEMBER values(8,"Komal","Krishnaraj","Bhatia",2,3,257422,"F");
insert into STUD_MEMBER values(43,"Kiran","Viraj","Shah",1,1,754124,"F");

insert into DEPARTMENT values(1,"Information Technology");


insert into DEPARTMENT values(2,"Electrical");
insert into DEPARTMENT values(3,"Civil");
insert into DEPARTMENT values(4,"Mechanical");
insert into DEPARTMENT values(5,"Chemical");
1. select FName,ontact_No from STUD_MEMBER;
2. select FName,Roll_no from stud_member where dept_id in(select dept_id from department
where Dept_name="Information Technology");
3. select dept_name from department where dept_id in (select dept_id from stud_member );
4. select dept_name from department where dept_id not in (select dept_id from
stud_member );
5. select dept_name from department;
6. select count(Roll_no) from stud_member where dept_id in(select dept_id from department
where dept_name="Electrical");
7. select * from stud_member where FName like 'A%';
8. select * from stud_member where gender="M";
9. select * from stud_member where semester=3;
10. select * FROM stud_member where gender="F" order by FName ;

Assignment 3
create table sales(orderid int(2) primary key,orderdate date,orderprice int(10),orderquantity
int(2),customername varchar(50));

insert into sales values(1,"2005-12-22",160,2,"Smith");

insert into sales values(2,"2005-08-10",190,2,"Johnson");

insert into sales values(3,"2005-07-13",500,5,"Baldwin");

insert into sales values(4,"2005-07-15",420,2,"Smith");

insert into sales values(5,"2005-12-22",1000,4,"Wood");

insert into sales values(6,"2005-10-02",820,4,"Smith");

insert into sales values(7,"2005-11-03",2000,2,"Baldwin");

1. select count(orderid) from sales where customername="Smith";


2. select count(Distinct(customername )) from sales;
3. select sum(orderquantity) from sales;
4. select avg(orderquantity) from sales;
5. select avg(orderquantity) from sales where orderprice >200;
6. select min(orderprice) from sales;

You might also like