Create these tables and execute the query:-
INVENTORY:-
| CarId | CarName | Price | Model | YearManufacture | Fueltype |
|---|---|---|---|---|---|
| D001 | Car1 | 582613.00 | LXI | 2017 | Petrol |
| D002 | Car1 | 586321.00 | VXI | 2018 | Petrol |
| B001 | Car2 | 567031.00 | Sigma1.2 | 2019 | Petrol |
| B002 | Car2 | 647858.00 | Delta1.2 | 2018 | Petrol |
| E001 | Car3 | 355205.00 | 5 STR STD | 2017 | CNG |
| E002 | Car3 | 654914.00 | CARE | 2018 | CNG |
| S001 | Car4 | 514000.00 | LXI | 2017 | PETROL |
| S002 | Car4 | 614000.00 | VXI | 2018 | PETROL |
CUSTOMER:-
| CustId | CustName | CustAdd | Phone | |
|---|---|---|---|---|
| C0001 | AmitSaha | L-10, Pitampura | 4564587852 | amitsaha2@gmail.com |
| C0002 | Rehnuma | J-12, SAKET | 5527688761 | Rehnuma@hotmail.com |
| C0003 | CharviNayyar | 10/9, FF, Rohini | 6811635425 | CharviNayyar@hotmail.com |
| C0004 | Gurpreet | Gurpreet | 3511056125 | Gurpreet23@hotmail.com |
SALE:-
| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice |
|---|---|---|---|---|---|---|
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613247.00 |
| I00002 | S001 | C0002 | 2018-12-12 | Online | E001 | 590321.00 |
| I00003 | S002 | C0004 | 2019-01-25 | Cheque | E010 | 604000.00 |
| I00004 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 |
| I00005 | E001 | C0003 | 2018-12-20 | Credit Card | E002 | 369310.00 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 |
EMPLOYEE:-
| EmpID | EmpName | DOB | DOJ | Designation | Salary |
|---|---|---|---|---|---|
| E001 | Rushil | 1994-07-10 | 2017-12-12 | Salesman | 25550 |
| E002 | Sanjay | 1990-03-12 | 2016-06-05 | Salesman | 33100 |
| E003 | Zohar | 1975-08-30 | 1999-01-08 | Peon | 20000 |
| E004 | Arpit | 1989-06-06 | 2010-12-02 | Salesman | 39100 |
| E006 | Sanjucta | 1985-11-03 | 2012-07-01 | Receptionist | 27350 |
| E007 | Mayank | 1993-04-03 | 2017-01-01 | Salesman | 27352 |
| E010 | Rajkumar | 1987-02-26 | 2013-10-23 | Salesman | 31111 |
- In order to increase sales, suppose the car dealer decides to offer his customers to pay the total amount in 10 easy EMIs (equal monthly installments). Assume that EMIs are required to be in multiples of 10,000. For that, the dealer wants to list the CarID and Price along with the following data from the Inventory table:
- Calculate GST as 12% of Price and display the result after rounding it off to one decimal place.
- Add a new column FinalPrice to the table inventory, which will have the value as sum of Price and 12% of the GST.
- Calculate and display the amount to be paid each month (in multiples of 10000) which is to be calculated after dividing the FinalPrice of the car into 10 instalments.
- After dividing the amount into EMIs, find out the remaining amount to be paid immediately, by performing modular division.
- Let us now add a new column Commission to the SALE table. The column Commission should have a total length of 7 in which 2 decimal places to be there.
- Let us now calculate commission for sales agents as 12 per cent of the SalePrice, insert the values to the newly added column Commission and then display records of the table SALE where commission > 73000.
- Display InvoiceNo, SalePrice and Commission such that commission value is rounded off to 0.
- Display customer name in lower case and customer email in upper case from table CUSTOMER
- Display the length of the email and part of the email from the email ID before the character ‘@’. Note - Do not print ‘@’.
- Let us assume that four digit area code is reflected in the mobile number starting from position number 3. For example, 2630 is the area code of mobile number 4726309212. Now, write the SQL query to display the area code of the customer living in Rohini.
- Display emails after removing the domain name extension “.com” from emails of the customers
- Display details of all the customers having yahoo emails only.
- Select the day, month number and year of joining of all employees.
- If the date of joining is not a Sunday, then display it in the following format "Wednesday, 26, November, 1979."
- Display the total number of records from table INVENTORY having a model as VXI.
- Display the total number of different types of Models available from table INVENTORY.
- Display the average price of all the cars with Model LXI from table INVENTORY.
- Display the number of cars purchased by each customer from the SALE table.
- Display the customer Id and number of cars purchased if the customer purchased more than 1 car from SALE table.
- Display the number of people in each category of payment mode from the table SALE.
- Display the PaymentMode and number of payments made using that mode more than once.