1. Write a SQL statement to insert a new record into the customer table with the following information: Ο CustomerID = 100 Ο CustomerName = Salem Saleem Ο ZipCode = 214 2. Retrieve all employees whose last name is null. 3. Write a SQL statement to retrieve the actual order date and customer ID for all customers who ordered more than 2 orders. 4. Write a SQL statement to retrieve the last names of all employees who have both an "a" and an "e" in their last name. 5. Write a SQL statement to retrieve the last name and first name for all employeesSort data in descending order of last name. 6. Write a SQL statement to retrieve all OrdersItems that are priced between 10 and 50. 7. Write a query to count order items for every order. 8. Write a query to retrieve order items when the order have more than 3 items
Added by Luis Miguel M.
Close
Step 1
We will need to write SQL statements to answer each question. The specific database schema (table names and column names) is not provided, so we will make some assumptions. We will assume tables named `Customers`, `Orders`, and `OrderItems` with appropriate Show more…
Show all steps
Your feedback will help us improve your experience
Akash M and 69 other AP CS educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Key Concepts
Recommended Videos
Please help me with these SQL queries 1. Give a list of customers (IDs and names) who have purchased multiple items, but all of which were the same color. Multiple items means more than one sale item. Use what you learned in the previous problem about counting colors. 2. Give a list of products and their list prices for sneakers whose list price is less than the average for flats. Only show product names and prices. 3. What is the most expensive sale item (SalePrice * Quantity) ever made and to whom was it made (first and last name)? Name the column LargestItemSale. (Use a max subquery in the WHERE clause.) Manufacturer: - ManufacturerID - ManufacturerName - Address1 - Address2 - City - State - PostalCode - Country - Phone - Fax - Contact - URL Customer: - CustomerID - FirstName - LastName - StreetAddress - City - State - PostalCode - Country - Phone Product: - ManufacturerID - ProductID - ProductName - Composition - ListPrice - PostalCode - Gender - Category - Color - Description - Phone - Fax - Contact - URL SaleItem: - SaleID - ProductID - ItemSize - SaleID - Quantity - SalePrice InventoryItem: - ProductID - ItemSize - QtyOnHand PurchaseItem: - ProductID - ItemSize - PurchaseID - Quantity - PurchasePrice Sale: - SaleID - SaleDate - CustomerID Purchase: - PurchaseID - PurchaseDate - EmployeeID - ExpectedDeliveryDate - ManufacturerID - Shipping Employee: - EmployeeID - FirstName - LastName - Address - City - State - ZIP - Phone - ManagerID - SSN - EmailAddress - HireDate Shipping: - ShippingID - EmployeeID - ShippingDate SalaryEmployee: - EmployeeID - Salary WageEmployee: - EmployeeID - Wage - MaxHours
Akash M.
SQL 1) Write a query to display the first earliest hiring date, latest hiring date, and maximum salary among the employees that work only under departments 20, 40, 50, 60, and 90. Hint: Use MIN, MAX, and GROUP BY. 2) Write a query that returns the department id and the number of employees (in the associated department) whose salary is over $2500. The result page should only show department ids that have more than 5 employees. Please note: We are not interested in all department ids in our grouping action, and we do not want to see any kind of total employee number in the result page. We want to see a limited result set. Hint 1: The SELECT part should be like below: SELECT department, COUNT(*) as "Number of employees". Hint 2: Don't forget to use WHERE, and please distinguish between WHERE and HAVING. 3) All employees with a JOB_ID of SA_REP, SA_MAN, IT_PROG will receive a salary increase of $1,200, and their commission will be cut in half. List employee Id, first name, last name, job id, department id, new salary, and new commission for these employees. Display the new commission with two digits to the right of the decimal point. Use the column names "NEW-SALARY" and "NEW-COMMISSION" for the generated columns. Your query will only display employees in departments 60 and 80. Employees within department 80 should be listed first. For employees with the same department, sort the list by salary.
List the total number of parts ordered on each day. Rank your results in ascending order on order date. List the full name and mailing address of customers who placed an order on 04-AUG-2013. If same customer has placed more than one order on this day, only show his/her information once. List the order date, order number, total quoted price for each order placed between 03-AUG-2013 and 06-AUG-2013. List the average commission rate (rename to AVG_RATE) and the highest total commission (rename to MAX_COMMISSION) of sales representatives who sold SG class items. List the warehouse number and the total number of orders from each warehouse. List the full name of the sales representatives, order number, and total quoted price for each order (rename to TQP) handled by each sales representative. List the part number and part description of parts with units on hand higher than the average units on hand across all parts. List the full name and credit limit of customer who bought a Gas Grill. List the warehouse number and total dollar amount of all items sold from that warehouse. Rename the calculated column TOTAL_VALUE. TOTAL_VALUE=SUM(NUMBER_ORDERED*QUOTED_PRICE). List the part description of the part with the most units on hand. List the part description of the part that was sold the most (in terms of total number ordered). List the full name and number of different parts for each order each customer has placed. Rename the calculated column as TOTAL_PARTS. List the order date of an order containing a part description with letter D (or d) in it. List the customer name of those who purchased products with a unit price of more than $150. List sales representative number and the number of customers handled by each sales representative who got paid at most as the average total commission. Rename the number of
Recommended Textbooks
Computer Science and Information Technology
Introduction to Programming Using Python
Computer Science - An Overview
18,000,000+
Students on Numerade
Trusted by students at 8,000+ universities
Watch the video solution with this free unlock.
EMAIL
PASSWORD