240907-rise.bsg-mysql-hp2

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

1. Find the product id and unit list price of products belonging to the 'Yoghurt' category.


SELECT 
	product_id, 
	unit_list_price
FROM 
	products
WHERE 
	product_category = 'yoghurt';

-----------
  
2. Find all the information (i.e., all columns) of the managers with null values in their first name.

SELECT 
   	*
FROM
	managers
WHERE
	first_name IS NULL;

-----------
  
3. Find the sales ID and sales date where quantity sold on that date is greater than or equal to 2100.

SELECT 
	sales_id, 
	sales_date, 
	quantity_sold
FROM
	sales
WHERE
	quantity_sold >= 2100;

-----------

4. Find the manager ID and the sum of quantity sold  for all products (except those with ID 7001001) for each of the managers in the sales table and sort the output by manager ID in descending order.

SELECT 
	sales_manager_id,
	SUM(quantity_sold) AS quant_sold
FROM
	sales
WHERE
	product_id <> 7001001
GROUP BY  
	sales_manager_id
ORDER BY
	sales_manager_id DESC;

-----------

5. Find the product ID, product name, and unit production cost of the products with maximum unit production cost below $1.10 and sort the output by production cost in ascending order (HINT: Use HAVING).

SELECT 
    product_id,
    product_name,
    MAX(unit_production_cost) AS unit_production_cost
FROM
    products
GROUP BY 1 , 2
HAVING 
	MAX(unit_production_cost) < 1.10
ORDER BY 
	unit_production_cost DESC;


SELECT 
    product_id,
    product_name,
	unit_production_cost
FROM
    products
WHERE 
	unit_production_cost < 1.10
ORDER BY 
	unit_production_cost DESC;

-----------

6. Find the product ID and sales date with the highest quantity sold from sales transacted after 30 Oct 2021 (exclusive) except for products with IDs 7001001 and 7001002.


SELECT 
    product_id, 
	sales_date, 
	quantity_sold
FROM
    sales
WHERE
    sales_date > '2021-10-30'
    AND 
	product_id NOT IN (7001001 , 7001002)
ORDER BY 
	quantity_sold desc
LIMIT 1;



SELECT 
    product_id, 
	sales_date, 
	quantity_sold
FROM
    sales
WHERE
    sales_date > '2021-10-30';

Create Schema demo;

CREATE TABLE demo.emp_info (
    emp_id CHAR(4),
    emp_name VARCHAR(20),
    skills VARCHAR(40),
    exp INT
);

INSERT INTO demo.emp_info
VALUES
("A123", "Rohit Jain", "SQL|C|R|Python|Tableau", 7),
("A124", "Aaina Singh", "SQLC|R|Tableau", 4),
("A125", "Mark John", "C|Python|Java", 10),
("A126", "Sam Keith", "SQL|C", 2),
("A127", "Kenny Ford", "SQL|C|R|Python|Power BI", 5);

CREATE TABLE demo.emp_sal_desig (
    emp_id CHAR(4),
    desig VARCHAR(20),
    salary FLOAT
);

INSERT INTO demo.emp_sal_desig
VALUES
("A123", "L3", 4500),
("A126", "L1", 2500),
("A121", "L2", 3500),
("A122", "L5", 9500);

[root@jiaoyou mysql]# pwd
/var/lib/mysql
[root@jiaoyou mysql]# ls -ls
338256 -rw-rw---- 1 mysql mysql 346030080 2010-04-22 08:08 ibdata1
626812 -rw-rw---- 1 mysql mysql 641222072 2010-01-26 07:17 mysql-bin.000008
316892 -rw-rw---- 1 mysql mysql 324173772 2010-03-25 12:51 mysql-bin.000009
52724 -rw-rw---- 1 mysql mysql  53931666 2010-04-12 12:13 mysql-bin.000010
10136 -rw-rw---- 1 mysql mysql  10359639 2010-04-22 08:32 mysql-bin.000011

mysql> SHOW BINARY LOGS; 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 | 641222072 | 
| mysql-bin.000009 | 324173772 | 
| mysql-bin.000010 |  53931666 | 
| mysql-bin.000011 |  10360680 | 
+------------------+-----------+


Solution

PURGE BINARY LOGS TO 'mysql-bin.010';

sudo killall mysqld
sudo /Applications/XAMPP/xamppfiles/bin/mysql.server start

ALTER DATABASE database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SELECT id, account_id, amount, created_at, null as type
 	FROM subscription_invoice
UNION ALL
SELECT id, account_id, amount, created_at, 
	`type` as type
FROM subscription_payment
ORDER BY created_at DESC

Db::getInstance()->executeS("DELETE FROM product_images WHERE id_product NOT IN (" . join($allProductIds,',') . ")")

CREATE TABLE firends (
	id INTEGER,
  	firstname VARCHAR(64)
);

CURRENT_TIMESTAMP	

CURRENT_TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

SELECT ticker,`datetime`
FROM (
  SELECT ticker, MAX(`datetime`) `datetime`
  FROM Bloomberg_hist_data
  GROUP BY ticker
) t ORDER BY `datetime`

UPDATE deltadb.transactions_temp
SET ticker =(CASE CEIL(RAND()*2)
              WHEN 1 THEN '3CN:SP'
              WHEN 2 THEN 'AGV:SP'
          END);