-- DDL for Table CUSTOMERT -------------------------------------------------------- CREATE TABLE "CUSTOMERT" ("CUSTOMER_ID" NUMBER(5,0), "CUST_NAME" VARCHAR2(30), "CITY" VARCHAR2(15), "GRADE" NUMBER(3,0), "SALESMAN_ID" NUMBER(5,0)); REM INSERTING into CUSTOMERT SET DEFINE OFF; Insert into CUSTOMERT (CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID) values (3001,'Brad Guzan','London',0,5005); Insert into CUSTOMERT (CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID) values (3002,'Nick Rimando','New York',100,5001); Insert into CUSTOMERT (CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID) values (3004,'Fabian Johns','Paris',300,5006); Insert into CUSTOMERT (CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID) values (3005,'Graham Zusi','California',200,5002); Insert into CUSTOMERT (CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID) values (3007,'Brad Davis','New York',200,5001); Insert into CUSTOMERT (CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID) values (3009,'Geoff Cemero','Berlin',100,5003); -------------------------------------------------------- -------------------------------------------------------- -------------------------------------------------------- -- DDL for Table ORDERST -------------------------------------------------------- CREATE TABLE "ORDERST" ("ORD_NO" NUMBER(5,0), "PURCH_AMT" NUMBER(8,2), "ORD_DATE" DATE, "CUSTOMER_ID" NUMBER(5,0), "SALESMAN_ID" NUMBER(5,0)); REM INSERTING into ORDERST SET DEFINE OFF; Insert into ORDERST (ORD_NO,PURCH_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID) values (70001,150.5,'5-OCT-2012',3005,5002); Insert into ORDERST (ORD_NO,PURCH_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID) values (70002,65.26,'5-OCT-2012',3002,5001); Insert into ORDERST (ORD_NO,PURCH_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID) values (70004,110.5,'17-AUG-2012',3009,5003); Insert into ORDERST (ORD_NO,PURCH_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID) values (70005,2400.6,null,3007,5001); Insert into ORDERST (ORD_NO,PURCH_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID) values (70007,948.5,'10-SEP-2012',3005,5002); Insert into ORDERST (ORD_NO,PURCH_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID) values (70009,270.65,'10-SEP-2012',3001,5005); -------------------------------------------------------- -------------------------------------------------------- -------------------------------------------------------- -- DDL for Table SALESMANT -------------------------------------------------------- CREATE TABLE "SALESMANT" ("SALESMAN_ID" NUMBER(5,0), "NAME" VARCHAR2(30), "CITY" VARCHAR2(15), "COMMISSION" NUMBER(5,2)); REM INSERTING into SALESMANT SET DEFINE OFF; Insert into SALESMANT (SALESMAN_ID,NAME,CITY,COMMISSION) values (5001,'James Hoog','New York',0.15); Insert into SALESMANT (SALESMAN_ID,NAME,CITY,COMMISSION) values (5002,'Nail Knite','Paris',0.13); Insert into SALESMANT (SALESMAN_ID,NAME,CITY,COMMISSION) values (5003,'Lauson Hen',null,0.11); Insert into SALESMANT (SALESMAN_ID,NAME,CITY,COMMISSION) values (5005,'Pit Alex','London',0.14); Insert into SALESMANT (SALESMAN_ID,NAME,CITY,COMMISSION) values (5006,'MC Lyon','Paris',0.12); Insert into SALESMANT (SALESMAN_ID,NAME,CITY,COMMISSION) values (5007,'Paul Adam','Rome',0.13);

Using these please answer the questions. Thank you!

Answer the following questions: 1. Write SQL query to list employee_id, first_name, last_name and hire_date from Employee table and their department_name from Department table. Use Aliases. 2. Write SQL query to list customers (customer_id, first_name and address_state) from Customer table who are not located in "OR", "IN" or "TX" states. 3. Write SQL query to list departments which have 28 or more employees. 4. Write SQL query to find the product with the lowest price. Show the product_id, product_name and price. 5. Write SQL query to find the total salary of all employees who work in department with department_id=4. 6. Write SQL query to find number of orders per each customer. Show customer_id, first_name, last_name and number of orders. 7. Write SQL query to list all information about customers who placed orders for products with product_id = 2 or product id = 7. 8. Write SQL query to find sum of salaries for employees per each department for only the departments who have sum of salaries > 1000000. Show department_id, department_name, and sum of salaries. 9. Write SQL query to list all products ordered based on price in descending order. Show product_name, price, and department_name which produce the product. 10. Write SQL query to create a view "DepartmentInfo" to list department_id, department_name and number of employees in each department. Answer the following questions: 1. Write SQL query to list employee_id, first_name, last_name and hire_date from Employee table and their department_name from Department table. Use Aliases. 2. Write SQL query to list customers (customer_id, first_name and address_state) from Customer table who are not located in "OR", "IN" or "TX" states. 3. Write SQL query to list departments which have 28 or more employees. 4. Write SQL query to find the product with the lowest price. Show the product_id, product_name and price. 5. Write SQL query to find the total salary of all employees who work in department with department_id=4. 6. Write SQL query to find number of orders per each customer. Show customer_id, first_name, last_name and number of orders. 7. Write SQL query to list all information about customers who placed orders for products with product_id = 2 or product id = 7. 8. Write SQL query to find sum of salaries for employees per each department for only the departments who have sum of salaries > 1000000. Show department_id, department_name, and sum of salaries. 9. Write SQL query to list all products ordered based on price in descending order. Show product_name, price, and department_name which produce the product. 10. Write SQL query to create a view "DepartmentInfo" to list department_id, department_name and number of employees in each department