Class xii Informatics practices 1st Chapter SQL

#copy paste all code on MySQL prompt
#creating and selecting database
create database myschool02;
use myschool02;

#creating Inventory table
create table Inventory (CarID varchar(20) primary key,CarName varchar(20),Price int,Model varchar(20),YearManufacture int,FuelType varchar(20));

#creating Customer table
create table Customer (CustId varchar(20) primary key,CustName varchar(20), CustAdd varchar(20), phone varchar(20),email varchar(20));

#creating Employee table
create table Employee (EmpID varchar(20) primary key, EmpName varchar(20), DOB date, DOJ date,Designation varchar(20), Salary int);

#creating Sale table
create table Sale (InvoiceNo varchar(20) primary key,CarID varchar(20) references Inventory(CarID), CustID varchar(20) references Customer(CustID), SaleDate date,PaymentMode varchar(20),EmpID varchar(20) references Employee(EmpID), SalePrice int);

#Insert data in Inventory table
insert into Inventory values('D001','Car1',582613,'LXI',2017,'Petrol');
insert into Inventory values('D002','Car1',673112,'VXI',2018,'Petrol');
insert into Inventory values('B001','Car2',567031,'Sigma1.2',2019,'Petrol');
insert into Inventory values('B002','Car2',647858,'Delta1.2',2018,'Petrol');
insert into Inventory values('E001','Car3',355205,'5 STR STD',2017,'CNG');
insert into Inventory values('E002','Car3',654914,'CARE',2018,'CNG');
insert into Inventory values('S001','Car4',514000,'LXI',2017,'Petrol');
insert into Inventory values('S002','Car4',614000,'VXI',2018,'Petrol');

#Insert data in Customers table
insert into Customer values('C0001','AmitSaha','L-10, Pitampura','4564587852','amitsaha2@gmail.com');
insert into Customer values('C0002','Rehnuma','J-12, Saket','5527688761','rehnuma@hotmail.com');
insert into Customer values('C0003','CharviNayyar','10/9, FF, Rohini','6811635425','charvi123@yahoo.com');
insert into Customer values('C0004','Gurpreet','A-10/2,SF,MayurVihar','3511056125','gur_singh@yahoo.com');

#Insert data in in Employee
insert into Employee values('E001','Rushil','1994-07-10','2017-12-12','Salesman',25550);
insert into Employee values('E002','Sanjay','1990-03-12','2016-06-05','Salesman',33100);
insert into Employee values('E003','Zohar','1975-08-30','1999-01-08','Peon',20000);
insert into Employee values('E004','Arpit','1989-06-06','2010-12-02','Salesman',39100);
insert into Employee values('E006','Sanjucta','1985-11-03','2012-07-01','Receptionist',27350);
insert into Employee values('E007','Mayank','1993-04-03','2017-01-01','Salesman',27352);
insert into Employee values('E010','Rajkumar','1987-02-26','2013-10-23','Salesman',31111);


#Insert data in Sale
insert into Sale values('I0001','D001','C0001','2019-01-24','Credit Card','E004',613247);
insert into Sale values('I0002','S001','C0002','2018-12-12','Online','E001',590321);
insert into Sale values('I0003','S002','C0004','2019-01-25','Cheque','E010',604000);
insert into Sale values('I0004','D002','C0001','2018-10-15','Bank Finance','E007',659982);
insert into Sale values('I0005','E001','C0003','2018-12-20','Credit Card','E002',369310);
insert into Sale values('I0006','S002','C0002','2019-01-30','Bank Finance','E007',620214);