MySQL - Derived Tables

To derive a table from another database table through Node.js program, we need to execute the SELECT statement using the query() function of the mysql2 library as follows −

sql ="SELECT column_name(s) FROM (subquery) AS derived_table_name";
con.query(sql);

To derive a table from another database table through Node.js program, we need to execute the SELECT statement using the JDBC function executeUpdate() as follows −

String sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
statement.executeQuery(sql);

To derive a table from another database table through Node.js program, we need to execute the SELECT statement using the execute() function of the MySQL Connector/Python as follows −

sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
cursorObj.execute(sql);

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE SAMPLE(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25),SALARY DECIMAL(18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  sql = "INSERT INTO SAMPLE VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),(2, 'Khilan', 25, 'Delhi', 1500.00 ),(3, 'kaushik', 23, 'Kota', 2000.00 ),(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),(5, 'Hardik', 27, 'Bhopal', 8500.00 ),(6, 'Komal', 22, 'MP', 4500.00 ),(7, 'Muffy', 24, 'Indore', 10000.00 );"
  con.query(sql);

  //retrieving a derived table
  sql = "SELECT ID, NAME, SALARY FROM (SELECT * FROM SAMPLE) AS DERIVED_SAMPLE;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { ID: 1, NAME: 'Ramesh', SALARY: '2000.00' },
  { ID: 2, NAME: 'Khilan', SALARY: '1500.00' },
  { ID: 3, NAME: 'kaushik', SALARY: '2000.00' },
  { ID: 4, NAME: 'Chaitali', SALARY: '6500.00' },
  { ID: 5, NAME: 'Hardik', SALARY: '8500.00' },
  { ID: 6, NAME: 'Komal', SALARY: '4500.00' },
  { ID: 7, NAME: 'Muffy', SALARY: '10000.00' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DerivedTable {public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String username = "root";
    String password = "password";
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        System.out.println("Connected successfully...!");

        //Lock table....
        String sql = "SELECT tutorial_title, tutorial_author FROM tutorials_tbl WHERE tutorial_id IN (SELECT tutorial_id FROM tutorials_tbl)";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("Table derived successfully...!");
        while (resultSet.next()) {
            System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2));
            System.out.println();
        }

        connection.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}
}

Output

The output obtained is as shown below −

Connected successfully...!
Table derived successfully...!
Learn PHP John Paul
Learn MySQL Abdul S
JAVA Tutorial Sanjay
Python Tutorial Sasha Lee
Hadoop Tutorial Chris Welsh

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl'
# Main query with a derived table (subquery)
main_query = """
SELECT d.tutorial_id, d.tutorial_title, d.tutorial_author, d.submission_date
FROM (
    SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
    FROM tutorials_tbl
    WHERE submission_date >= '2023-01-01'
) AS d
WHERE d.tutorial_author LIKE '%Paul%'
"""
cursorObj = connection.cursor()
cursorObj.execute(main_query)
result = cursorObj.fetchall()
print("Derived Table Result:")
for row in result:
    print(f"| {row[0]:<11} | {row[1]:<15} | {row[2]:<15} | {row[3]:<15} |")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Derived Table Result:
| 1           | Learn PHP       | John Paul       | <15 |