--1
.open sales
--2
--2.1
create table Salesman(
sid int primary key,
name text,
city text,
commission float);
insert into Salesman values
(5001 ,'Mohan Patel','Anand', 0.15),
(5002 ,'Nail Shah','Surat', 0.13),
(5005 ,'Preet Vyas','Ahmedabad',0.11),
(5006 ,'Jeevan Mehta','Navsari',0.14),
(5003 ,'Paul Adam','Nadiad',0.12),
(5007 ,'Ramesh Patel','Surat',0.13);
.mode table
select * from salesman;
--2.2
Create table Ord(
oid int primary key,
pur_amt int,
ord_date date,
sid int references salesman(sid));
insert into ord values
(1 ,12000 ,'2020-12-05', 5001),
(2, 42000 ,'2020-12-20', 5002),
(3, 2000 ,'2021-02-02', 5005),
(4, 14000 ,'2021-03-23', 5005),
(5, 23000 ,'2021-04-15', 5003),
(6, 33000 ,'2021-05-20', 5001),
(7, 32000 ,'2021-06-22', 5003),
(8,23500 ,'2021-07-01', 5003),
(9, 43000 ,'2021-07-05', 5001),
(10, 12000 ,'2021-07-15', 5002);
select * from ord;
--2.3
Create table Customer(
cid text primary key,
cust_name text,
grade int,
sid int references salesman(sid));
insert into customer values
('C1','Mohit Patel', 100, 5001),
('C2','Geeta Vyas', 200, 5003),
('C3','Jaya Patil', 100, 5005),
('C4','Vishal Gohel', 300, 5005),
('C5','Kartik Goyenka', 200, 5002),
('C6','Meera Prajapati', 100, 5001),
('C7','Veer Vyas', 300, 5002),
('C8','Maya Mehta', 200, 5003);
select * from customer;
--3
--3.1
.mode table
select * from salesman;
--3.2
.mode column
select * from customer;
--3.3
.mode csv
select * from ord;
--3.4
.mode html
select * from salesman;
--3.5
.mode insert
select * from customer;
--3.6
.tables
--4.1
Insert into customer values
('C11','Manav Ninave', 200, 5001),
('C12','Bhatu Rajput',100, 5005)
rollback;
--5.1
.mode box
select * from customer where grade=200;
--5.2
select * from salesman where name like 'P%';
--5.7
select * from ord order by pur_amt desc limit 5 offset 7;
--5.9
select [Link],name,pur_amt from salesman s, ord o where [Link]=[Link] order by [Link];
--5.12
select
cid,
cust_name,
grade
case grade
when 100 then pur_