A) We have following relation orders(order_id,customer_id,order_date,amount). Create table with Primary Key, otherrelevant constraints in given schema.1) Find out the number of orders for each customer by customer_id and show only customer_id with number of orders above 5.2) Find out the total amount by order_id and order_date.B) Find the sum of a user-inserted number's first and last digits using PL/pgSQL function
Question
A) We have following relation orders(order_id,customer_id,order_date,amount). Create table with Primary Key, otherrelevant constraints in given schema.1) Find out the number of orders for each customer by customer_id and show only customer_id with number of orders above 5.2) Find out the total amount by order_id and order_date.B) Find the sum of a user-inserted number's first and last digits using PL/pgSQL function
Solution
A)
- To create the table with the given schema, you can use the following SQL command:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
- To find out the number of orders for each customer by customer_id and show only customer_id with number of orders above 5, you can use the following SQL command:
SELECT customer_id, COUNT(order_id) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;
- To find out the total amount by order_id and order_date, you can use the following SQL command:
SELECT order_id, order_date, SUM(amount) as total_amount
FROM orders
GROUP BY order_id, order_date;
B) To find the sum of a user-inserted number's first and last digits using PL/pgSQL function, you can use the following function:
CREATE OR REPLACE FUNCTION sum_first_last(n INT)
RETURNS INT AS $$
DECLARE
first_digit INT;
last_digit INT;
BEGIN
last_digit := n % 10;
WHILE n > 0 LOOP
first_digit := n % 10;
n := n / 10;
END LOOP;
RETURN first_digit + last_digit;
END;
$$ LANGUAGE plpgsql;
You can then call this function with a number to get the sum of the first and last digits:
SELECT sum_first_last(12345); -- Returns 6 (1 + 5)
Similar Questions
We have following relationorders(order_id,customer_id,order_date,amount)1) Find out the number of orders for each customer by customer_id.2) Find out the total amount by order_id and order_date.3) Find out the number of orders for each customer by customer_id. Show onlycustomer_id with number of orders above 5
Query the customer_number from the orders table for the customer who has placed the largest number of orders.It is guaranteed that exactly one customer will have placed more orders than any other customer.The orders table is defined as follows:ColumnTypeorder_number (PK)intcustomer_numberintorder_datedaterequired_datedateshipped_datedatestatuschar(15)commentchar(200)Sample Inputorder_numbercustomer_numberorder_daterequired_dateshipped_date112017-04-092017-04-132017-04-12222017-04-152017-04-202017-04-18332017-04-162017-04-252017-04-20442017-04-182017-04-282017-04-25Sample Outputcustomer_number3ExplanationThe customer with number '3' has two orders, which is greater than either customer '1' or '2' because each of them only has one order.So the result is customer_number '3'. Follow up: What if more than one customer have the largest number of orders, can you find all the customer_number in this case?Optionsselect customer_numberfrom ( select customer_number, count(*) as cnt from orders group by customer_number) as eorder by e.cnt desclimit 1;select customer_number select customer_number, count(*) as cnt from orders group by customer_number) as eorder by e.cnt desclimit 1;select customer_numberfrom ( select customer_number, count(*) as cnt from orders group by customer_number) as elimit 1;select customer_numberfrom ( select customer_number, count(*) as cnt group by customer_number) as eorder by e.cnt desclimit 1;
Create a table named orders with columns "order_id" (int), "customer_id" (int), "order_date" (date), "total_amount" (decimal), and "status" (varchar). The table should be configured with constraints such that "order_id" and "customer_id" cannot be empty, and "order_id" serves as the primary key.
Table: OrdersOrderIDCustomerIDOrderDateAmount11012023-01-01500.0021022023-01-03150.0031012023-01-04200.0041032023-01-07700.0051022023-01-10300.00i. Write an SQL query to count the number of orders.ii. Write an SQL query to calculate the total amount of all orders.iii.Write an SQL query to find the total amount of orders for each customer.iv. Write an SQL query to find the largest order amount.v. Write an SQL query to find customers with a total order amount greater than 500.Answer text Question 3EditViewInsertFormatToolsTableHelp
Consider the below orders table:Column NameDatatypeConstraintorder_idNumberPKOrder_dateDate Order_modevarchar Customer_idNumber Order_totalNumber(8,2) There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?Select one:a.INSERT INTO orders ( order_id ,order _date,order_mode , (SELECT customer_id FROM customersWHERE cust_last_name ='Roberts' ANDcredit_limit =600) , order _total )VALUES (1,'10-mar-2007','direct' ,& &customer_id,1000);b.INSERT INTO( SELECT o.order_id,o.order,o.order_mode,c.customer_id,o.order_totalFROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' and c.credit_limit =600)VALUES (1,'10-mar-2007','direct' ,& &customer_id,1000);FROM customersWHERE cust_last_name ='Roberts' ANDcredit_limit =600) ,1000 );c.INSERT INTO orders ( order_id ,order _date,order_mode , (SELECT customer_id FROM customersWHERE cust_last_name ='Roberts' ANDcredit_limit =600) , order _total )VALUES (1,'10-mar-2007','direct' ,& customer_id,1000);d.INSERT INTO orders VALUES( 1,'10-mar-2007','direct', (SELECT customer_id FROMcustomers WHERE cust_last_name ='Roberts' ANDcredit_limit =600) ,1000 );
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.