a. Create Database name salesorderDB. (Take screenshot.) b. Create Tables salesman, customer and order. (Take screenshot.)...

Transcribed Image Text:

a. Create Database name "salesorderDB". (Take screenshot.) b. Create Tables "salesman", "customer" and "order". (Take screenshot.) salesman salesman id 5001 5002 5005 5006 5003 5007 name James Hoog Nail Knite www.w Pit Alex Mc Lyon Lauson Hen wwwwwww Paul Adam order order no 70001 70009 city New York Paris London Paris Rome commission 150.5 270.65 PA 0.15 0.13 0.11 0.14 0.12 0.13 customer customer id wwwwwwww 3002 3005 3001 3004 3007 3009 3008 3003 purch amt order date 2016-10-05 2016-09-10 2016-10-05 customer name city wwwwwwwwwwwww Nick Rirando Graham Zusi Brad Guzan Fabian Johns Brad Davis Geoff Camero wwwwwww Julian Green New York California London Paris New York Berlin London Jozy Altidor Moncow www customer id 3005 3001 2002 grade 100 200 300 200 100 300 200 5002 salesman id 5001 5001 5002 5006 5001 salesman id 5002 5007 100 ( C 71. order order no 70001 70009 70002 70004 70007 70005 70008 3008 3003 purch amt order date 150.5 270.65 65.26 110.5 948.5 2400.6 5760 Julian Green London Jozy Altidor Moncow www customer id 2016-10-05 3005 2016-09-10 3001 2016-10-05 3002 2016-08-17 3009 2016-09-10 3005 2016-07-27 3007 2016-09-10 3002 c. Insert the records as given in the above tables. (Take screenshot.) 300 200 salesman id 5002 5001 5002 5007 5002 5001 5001 d. Display the name and city of all the salesman. (Take screenshot.) e. Delete the record from salesman where salesman_id is "5005". (Take screenshot.) f. Update city from London to Berlin in table customer where customer_id is "3008". (Take screenshot.) g. Alter table salesman and add a new column "email_id" and add the email id of all the salesman in the format firstname.city@yahoo.com. (Take screenshot.) Main Content h. Display the records from order where purch_amt is greater than 500. (Take screenshot.) i. Find all the details from order table and arrange the data according to dates in ascending order by using "ORDER BY". (Take screenshot.) j. Find the commission according to the city by using "GROUP BY". (Take screenshot.) k. Show all the names from the customers with name start from 'J'. (Take screenshot.) 1. Display all customer details from customer table where the grade is between 200 to 300 by using BETWEEN. (Take screenshot.) K m. Write a SQL statement that count all the orders for the date "2016-09- 10" using COUNT function. (Take screenshot.) n. Find the highest purchase amount order by each salesman with their customer_id and highest purchase amount by using MAX and GROUP BY clause in order table. (Take screenshot.) o. Display the minimum purchase amount from table order using MIN function. (Take screenshot.) p. Find the average commission from table salesman using AVG function. (Take screenshot.) q. Find the total purchase amount from order table using SUM function. (Take screenshot.) r. Find the highest purchase amount from Order table where customers have the highest purchase amount in the day more than 2000 using HAVING and MAX. (Take screenshot.) s. Rename database from "salesorderDB" to "salescustomerorderDB" using rename command. (Take screenshot.) t. Drop table order using DROP command. (Take screenshot.) a. Create Database name "salesorderDB". (Take screenshot.) b. Create Tables "salesman", "customer" and "order". (Take screenshot.) salesman salesman id 5001 5002 5005 5006 5003 5007 name James Hoog Nail Knite www.w Pit Alex Mc Lyon Lauson Hen wwwwwww Paul Adam order order no 70001 70009 city New York Paris London Paris Rome commission 150.5 270.65 PA 0.15 0.13 0.11 0.14 0.12 0.13 customer customer id wwwwwwww 3002 3005 3001 3004 3007 3009 3008 3003 purch amt order date 2016-10-05 2016-09-10 2016-10-05 customer name city wwwwwwwwwwwww Nick Rirando Graham Zusi Brad Guzan Fabian Johns Brad Davis Geoff Camero wwwwwww Julian Green New York California London Paris New York Berlin London Jozy Altidor Moncow www customer id 3005 3001 2002 grade 100 200 300 200 100 300 200 5002 salesman id 5001 5001 5002 5006 5001 salesman id 5002 5007 100 ( C 71. order order no 70001 70009 70002 70004 70007 70005 70008 3008 3003 purch amt order date 150.5 270.65 65.26 110.5 948.5 2400.6 5760 Julian Green London Jozy Altidor Moncow www customer id 2016-10-05 3005 2016-09-10 3001 2016-10-05 3002 2016-08-17 3009 2016-09-10 3005 2016-07-27 3007 2016-09-10 3002 c. Insert the records as given in the above tables. (Take screenshot.) 300 200 salesman id 5002 5001 5002 5007 5002 5001 5001 d. Display the name and city of all the salesman. (Take screenshot.) e. Delete the record from salesman where salesman_id is "5005". (Take screenshot.) f. Update city from London to Berlin in table customer where customer_id is "3008". (Take screenshot.) g. Alter table salesman and add a new column "email_id" and add the email id of all the salesman in the format firstname.city@yahoo.com. (Take screenshot.) Main Content h. Display the records from order where purch_amt is greater than 500. (Take screenshot.) i. Find all the details from order table and arrange the data according to dates in ascending order by using "ORDER BY". (Take screenshot.) j. Find the commission according to the city by using "GROUP BY". (Take screenshot.) k. Show all the names from the customers with name start from 'J'. (Take screenshot.) 1. Display all customer details from customer table where the grade is between 200 to 300 by using BETWEEN. (Take screenshot.) K m. Write a SQL statement that count all the orders for the date "2016-09- 10" using COUNT function. (Take screenshot.) n. Find the highest purchase amount order by each salesman with their customer_id and highest purchase amount by using MAX and GROUP BY clause in order table. (Take screenshot.) o. Display the minimum purchase amount from table order using MIN function. (Take screenshot.) p. Find the average commission from table salesman using AVG function. (Take screenshot.) q. Find the total purchase amount from order table using SUM function. (Take screenshot.) r. Find the highest purchase amount from Order table where customers have the highest purchase amount in the day more than 2000 using HAVING and MAX. (Take screenshot.) s. Rename database from "salesorderDB" to "salescustomerorderDB" using rename command. (Take screenshot.) t. Drop table order using DROP command. (Take screenshot.)

Answer rating: 100% (QA)

Solutions Step 1 a To CREATE a database name salesorderDB CREATE DATABASE salesorderDB Explanation We can create a database using CREATE DATABASE comm... View the full answer