Magento 2: Raw SQL Commands

Use DI of constructor to set up some composite objects

public function __construct(
       \Magento\Framework\App\ResourceConnection $resource,
       \Magento\Catalog\Model\ProductFactory $factory
)
{
        $this->resource = $resource;
        $this->factory = $factory;
}

An example how to fetch data with RAW SQL

/*
  * @return \Magento\Framework\DB\Adapter\AdapterInterface
  */
public function getConnection()
{
        return $this->resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
}

public function getOrder($productId)
{
        $resource = $this->factory->create()->getResource();
        $select = $this->getConnection()->select()->from(
              ['product' => $resource->getTable('catalog_product_entity')],
              ['entity_id',  'type_id',  'sku']
        );

      $select->joinLeft(
            ['order' => $resource->getTable('sales_order_item')],
            'product.entity_id = order.product_id',
            ['order_id', 'name', ']
      );

     $select->where('product.entity_id = :product_id');

     $bind = ['product_id' => (int)productId];

    return $this->getConnection()->fetchAssoc($select, $bind);
}

An example how to insert data into table

public function setData($stockId)
{
       $connection = $this->getConnection();

       $columns = [
            'product_id' => 'csi.product_id',
            'warehouse_id' => 'w.warehouse_id',
            'qty' => new \Zend_Db_Expr('IFNULL(csi.qty,0)'),
            'available_qty' => new \Zend_Db_Expr('(IFNULL(csi.qty,0) -  IFNULL(SUM(soi.qty_ordered),0))'),
            'ship_qty' => new \Zend_Db_Expr('IFNULL(SUM(soi.qty_ordered),0)')
       ];

        $select = $this->getConnection()->select();
        $select->from(
            ['csi' => $connection->getTable('cataloginventory_stock_item')],
            $columns
        )->joinLeft(
            ['soi' => $connection->getTable('sales_order_item')],
            'soi.product_id = csi.product_id and soi.qty_shipped = 0 and soi.qty_invoiced > 0 and soi.product_type IN ("simple")',
            []
        )->joinLeft(
            ['w' => $connection->getTable('warehouse')],
            'w.stock_id = csi.stock_id',
            []
        )->joinLeft(
            ['cpe' => $connection->getTable('catalog_product_entity')],
            'cpe.entity_id = csi.product_id',
            []
        )
        ->where(sprintf('csi.stock_id=%s and cpe.type_id="simple"', $stock_id))
         ->group(['csi.product_id']);

        $query = $select->insertFromSelect(
               $connection->getTable('warehouse_item'),
               array_keys($columns)
        );

        $connection->getConnection()->query($query);
}