Sub Query:
A subquery in MySQL is a query, which is nested into another SQL query and embedded with SELECT, INSERT, UPDATE or DELETE statement along with the various operators.
We can also nest the subquery with another subquery. A subquery is known as the inner query, and the query that contains subquery is known as the outer query.
The inner query executed first gives the result to the outer query, and then the main/outer query will be performed. MySQL allows us to use subquery anywhere, but it must be closed within parenthesis. All subquery forms and operations supported by the SQL standard will be supported in MySQL also.
Eg: select * from emp_personal where id in (select id from project where skill =:skill);
Join:
JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed whenever you need to fetch records from two or more tables.
Eg: SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Stored Procedure:
Stored Procedures are created to perform one or more DML operations on Database. It is nothing but the group of SQL statements that accepts some input in the form of parameters and performs some task and may or may not returns a value.
CREATE or REPLACE PROCEDURE INC_SAL(eno IN NUMBER, up_sal OUT NUMBER)
IS
BEGIN
UPDATE emp_table SET salary = salary+1000 WHERE emp_no = eno;
COMMIT;
SELECT sal INTO up_sal FROM emp_table WHERE emp_no = eno;
END;
Execute Script:
Statements can be of different types.
No input or output parameters are accepted.
The operation runs any script that does not involve a SQL projection.
Eg:update project set skill='SAP' where id = 104
Execute DDL:
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
description VARCHAR(255),
price SMALLINT,
discount TINYINT
Query Single:
Query single operation in the same way as you invoke the Select operation; however, you always get a single result using the Query single operation, regardless of the number of records returned by the actual SQL query.
The insert, update, and delete operations can be used for the cases in which each input parameter can take only one value. Alternatively, bulk operations allow you to run a single query using a set of parameters values.
You can avoid unnecessary steps by doing a bulk operation so that:
- The query is parsed only once.
- Only one database connection is required since a single statement is executed.
- Network overhead is minimized.
- RDBMS can execute the bulk operation automically.
Query:
insert into project(id,skill,proj_name) values (:eid,:tech,:proj)
payload: [
GitHub:
https://github.com/VISHNUKVM/MuleSoft/blob/87b403429b94bb93f197d02bc69c31d09fdad982/db-sub-query.jar
References:
https://www.geeksforgeeks.org
No comments:
Post a Comment