SELECT
customer_id,
customer_name,
COUNT(order_id) as total
FROM
customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY
customer_id,
customer_name
HAVING
COUNT(order_id) > 5
ORDER BY
COUNT(order_id) DESC;
---
UPDATE
customers
SET
totalorders = ordersummary.total
FROM
(
SELECT
customer_id,
count(order_id) As total
FROM
orders
GROUP BY
customer_id
) As ordersummary
WHERE
customers.customer_id = ordersummary.customer_id
---
SELECT
*
FROM
sometable
UNION ALL
SELECT
*
FROM
someothertable;
---
SET
NAMES 'utf8';
---
CREATE TABLE `PREFIX_address` (
`id_address` int(10) unsigned NOT NULL auto_increment,
`id_country` int(10) unsigned NOT NULL,
`id_state` int(10) unsigned default NULL,
`id_customer` int(10) unsigned NOT NULL default '0',
`id_manufacturer` int(10) unsigned NOT NULL default '0',
`id_supplier` int(10) unsigned NOT NULL default '0',
`id_warehouse` int(10) unsigned NOT NULL default '0',
`alias` varchar(32) NOT NULL,
`company` varchar(64) default NULL,
`lastname` varchar(32) NOT NULL,
`firstname` varchar(32) NOT NULL,
`address1` varchar(128) NOT NULL,
`address2` varchar(128) default NULL,
`postcode` varchar(12) default NULL,
`city` varchar(64) NOT NULL,
`other` text,
`phone` varchar(16) default NULL,
`phone_mobile` varchar(16) default NULL,
`vat_number` varchar(32) default NULL,
`dni` varchar(16) DEFAULT NULL,
`date_add` datetime NOT NULL,
`date_upd` datetime NOT NULL,
`active` tinyint(1) unsigned NOT NULL default '1',
`deleted` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id_address`),
KEY `address_customer` (`id_customer`),
KEY `id_country` (`id_country`),
KEY `id_state` (`id_state`),
KEY `id_manufacturer` (`id_manufacturer`),
KEY `id_supplier` (`id_supplier`),
KEY `id_warehouse` (`id_warehouse`)
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
CREATE TABLE `PREFIX_alias` (
`id_alias` int(10) unsigned NOT NULL auto_increment,
`alias` varchar(255) NOT NULL,
`search` varchar(255) NOT NULL,
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`id_alias`),
UNIQUE KEY `alias` (`alias`)
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
CREATE TABLE `PREFIX_carrier` (
`id_carrier` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_reference` int(10) unsigned NOT NULL,
`id_tax_rules_group` int(10) unsigned DEFAULT '0',
`name` varchar(64) NOT NULL,
`url` varchar(255) DEFAULT NULL,
`active` tinyint(1) unsigned NOT NULL DEFAULT '0',
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`shipping_handling` tinyint(1) unsigned NOT NULL DEFAULT '1',
`range_behavior` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_module` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_free` tinyint(1) unsigned NOT NULL DEFAULT '0',
`shipping_external` tinyint(1) unsigned NOT NULL DEFAULT '0',
`need_range` tinyint(1) unsigned NOT NULL DEFAULT '0',
`external_module_name` varchar(64) DEFAULT NULL,
`shipping_method` int(2) NOT NULL DEFAULT '0',
`position` int(10) unsigned NOT NULL default '0',
`max_width` int(10) DEFAULT 0,
`max_height` int(10) DEFAULT 0,
`max_depth` int(10) DEFAULT 0,
`max_weight` int(10) DEFAULT 0,
`grade` int(10) DEFAULT 0,
PRIMARY KEY (`id_carrier`),
KEY `deleted` (`deleted`, `active`),
KEY `id_tax_rules_group` (`id_tax_rules_group`)
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
CREATE TABLE IF NOT EXISTS `PREFIX_specific_price_rule` (
`id_specific_price_rule` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`id_shop` int(11) unsigned NOT NULL DEFAULT '1',
`id_currency` int(10) unsigned NOT NULL,
`id_country` int(10) unsigned NOT NULL,
`id_group` int(10) unsigned NOT NULL,
`from_quantity` mediumint(8) unsigned NOT NULL,
`price` DECIMAL(20, 6),
`reduction` decimal(20, 6) NOT NULL,
`reduction_type` enum('amount', 'percentage') NOT NULL,
`from` datetime NOT NULL,
`to` datetime NOT NULL,
PRIMARY KEY (`id_specific_price_rule`),
KEY `id_product` (
`id_shop`, `id_currency`, `id_country`,
`id_group`, `from_quantity`, `from`,
`to`
)
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
UPDATE
`PREFIX_configuration`
SET
value = '6'
WHERE
name = 'PS_SEARCH_WEIGHT_PNAME'
---
UPDATE
`PREFIX_hook_module`
SET
position = 1
WHERE
id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayPayment'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'cheque'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayPaymentReturn'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'cheque'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayHome'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'homeslider'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionAuthentication'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'statsdata'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionShopDataDuplication'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'homeslider'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayTop'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'blocklanguages'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionCustomerAccountAdd'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'statsdata'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayCustomerAccount'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'favoriteproducts'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayAdminStatsModules'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'statsvisits'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayAdminStatsGraphEngine'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'graphvisifire'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayAdminStatsGridEngine'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'gridhtml'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayLeftColumnProduct'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'blocksharefb'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionSearch'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'statssearch'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionCategoryAdd'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'blockcategories'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionCategoryUpdate'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'blockcategories'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionCategoryDelete'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'blockcategories'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'actionAdminMetaSave'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'blockcategories'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayMyAccountBlock'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'favoriteproducts'
)
OR id_hook = (
SELECT
id_hook
FROM
`PREFIX_hook`
WHERE
name = 'displayFooter'
)
AND id_module = (
SELECT
id_module
FROM
`PREFIX_module`
WHERE
name = 'blockreinsurance'
)
---
ALTER TABLE
`PREFIX_employee`
ADD
`bo_color` varchar(32) default NULL AFTER `stats_date_to`
---
INSERT INTO `PREFIX_cms_category_lang`
VALUES
(
1, 3, 'Inicio', '', 'home', NULL, NULL,
NULL
)
---
INSERT INTO `PREFIX_cms_category`
VALUES
(1, 0, 0, 1, NOW(), NOW(), 0)
---
UPDATE
`PREFIX_cms_category`
SET
`position` = 0
---
ALTER TABLE
`PREFIX_customer`
ADD
`note` text AFTER `secure_key`
---
ALTER TABLE
`PREFIX_contact`
ADD
`customer_service` tinyint(1) NOT NULL DEFAULT 0 AFTER `email`
---
INSERT INTO `PREFIX_specific_price` (
`id_product`, `id_shop`, `id_currency`,
`id_country`, `id_group`, `priority`,
`price`, `from_quantity`, `reduction`,
`reduction_type`, `from`, `to`
) (
SELECT
dq.`id_product`,
1,
1,
0,
1,
0,
0.00,
dq.`quantity`,
IF(
dq.`id_discount_type` = 2, dq.`value`,
dq.`value` / 100
),
IF (
dq.`id_discount_type` = 2, 'amount',
'percentage'
),
'0000-00-00 00:00:00',
'0000-00-00 00:00:00'
FROM
`PREFIX_discount_quantity` dq
INNER JOIN `PREFIX_product` p ON (p.`id_product` = dq.`id_product`)
)
---
DROP
TABLE `PREFIX_discount_quantity`
---
INSERT INTO `PREFIX_specific_price` (
`id_product`, `id_shop`, `id_currency`,
`id_country`, `id_group`, `priority`,
`price`, `from_quantity`, `reduction`,
`reduction_type`, `from`, `to`
) (
SELECT
p.`id_product`,
1,
0,
0,
0,
0,
0.00,
1,
IF(
p.`reduction_price` > 0, p.`reduction_price`,
p.`reduction_percent` / 100
),
IF(
p.`reduction_price` > 0, 'amount',
'percentage'
),
IF (
p.`reduction_from` = p.`reduction_to`,
'0000-00-00 00:00:00', p.`reduction_from`
),
IF (
p.`reduction_from` = p.`reduction_to`,
'0000-00-00 00:00:00', p.`reduction_to`
)
FROM
`PREFIX_product` p
WHERE
p.`reduction_price`
OR p.`reduction_percent`
)
---
ALTER TABLE
`PREFIX_product`
DROP
`reduction_price`,
DROP
`reduction_percent`,
DROP
`reduction_from`,
DROP
`reduction_to`
---
INSERT INTO `PREFIX_configuration` (
`name`, `value`, `date_add`, `date_upd`
)
VALUES
(
'PS_SPECIFIC_PRICE_PRIORITIES',
'id_shop;id_currency;id_country;id_group',
NOW(), NOW()
),
('PS_TAX_DISPLAY', 0, NOW(), NOW()),
(
'PS_SMARTY_FORCE_COMPILE', 1, NOW(),
NOW()
),
(
'PS_DISTANCE_UNIT', 'km', NOW(), NOW()
),
(
'PS_STORES_DISPLAY_CMS', 0, NOW(),
NOW()
),
(
'PS_STORES_DISPLAY_FOOTER', 0, NOW(),
NOW()
),
(
'PS_STORES_SIMPLIFIED', 0, NOW(),
NOW()
),
(
'PS_STATSDATA_CUSTOMER_PAGESVIEWS',
1, NOW(), NOW()
),
(
'PS_STATSDATA_PAGESVIEWS', 1, NOW(),
NOW()
),
(
'PS_STATSDATA_PLUGINS', 1, NOW(),
NOW()
)
---
INSERT INTO `PREFIX_configuration` (
`name`, `value`, `date_add`, `date_upd`
)
VALUES
(
'PS_CONDITIONS_CMS_ID',
IFNULL(
(
SELECT
`id_cms`
FROM
`PREFIX_cms`
WHERE
`id_cms` = 3
),
0
),
NOW(),
NOW()
)
---
CREATE TEMPORARY TABLE `PREFIX_configuration_tmp` (`value` text)
---
SET
@defaultOOS = (
SELECT
value
FROM
`PREFIX_configuration`
WHERE
name = 'PS_ORDER_OUT_OF_STOCK'
)
---
UPDATE
`PREFIX_product` p
SET
`cache_default_attribute` = 0
WHERE
`id_product` NOT IN (
SELECT
`id_product`
FROM
`PREFIX_product_attribute`
)
---
INSERT INTO `PREFIX_hook` (
`name`, `title`, `description`, `position`
)
VALUES
(
'processCarrier', 'Carrier Process',
NULL, 0
)
---
INSERT INTO `PREFIX_stock_mvt_reason_lang` (
`id_stock_mvt_reason`, `id_lang`,
`name`
)
VALUES
(1, 1, 'Order'),
(1, 2, 'Commande'),
(2, 1, 'Missing Stock Movement'),
(
2, 2, 'Mouvement de stock manquant'
),
(3, 1, 'Restocking'),
(3, 2, 'Réassort')
---
INSERT INTO `PREFIX_meta_lang` (
`id_lang`, `id_meta`, `title`, `url_rewrite`
)
VALUES
(
1,
(
SELECT
`id_meta`
FROM
`PREFIX_meta`
WHERE
`page` = 'authentication'
),
'Authentication',
'authentication'
),
(
2,
(
SELECT
`id_meta`
FROM
`PREFIX_meta`
WHERE
`page` = 'authentication'
),
'Authentification',
'authentification'
),
(
3,
(
SELECT
`id_meta`
FROM
`PREFIX_meta`
WHERE
`page` = 'authentication'
),
'Autenticación',
'autenticacion'
)
---
LOCK TABLES `admin_assert` WRITE
---
UNLOCK TABLES
---
DROP
TABLE IF EXISTS `admin_role`
---
SELECT
*
FROM
-- This is another comment
MyTable # One final comment
/* This is a block comment
*/
WHERE
1 = 2;
---
SELECT
-- This is a test
---
SELECT
Test
FROM
Test
WHERE
(MyColumn = 1)
)
AND (
(
(SomeOtherColumn = 2);
WARNING: unclosed parentheses or section
---
ALTER TABLE
`test_modify`
MODIFY
`id` INT(11) UNSIGNED NOT NULL;
---
ALTER TABLE
`test_change`
CHANGE
`id` `_id` BIGINT(20) UNSIGNED NULL;
---
SELECT
*
LIMIT
1;
SELECT
a,
b,
c,
d
FROM
e
LIMIT
1, 2;
SELECT
1,
2,
3
WHERE
a in (1, 2, 3, 4, 5)
and b = 5;
---
SELECT
count - 50
WHERE
a - 50 = b
WHERE
1
and -50
WHERE
-50 = a
WHERE
a = -50
WHERE
1
/*test*/
-50
WHERE
1
and -50;
---
SELECT
@
and b;
---
SELECT
@"weird variable name";
---
SELECT
:pdoParam;
---
SELECT
"no closing quote
---
SELECT
[sqlserver]
FROM
[escap[e]]d style];
---
SELECT
a
FROM
b
LEFT OUTER JOIN c on (d = f);
---
WITH
cte AS (
SELECT
a,
b
FROM
table
),
RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
SELECT
1,
0,
1
UNION ALL
SELECT
n + 1,
next_fib_n,
fib_n + next_fib_n
FROM
fibonacci
WHERE
n < 10
)
SELECT
*
FROM
fibonacci;
---
WITH
cte1 AS (
SELECT
a,
b
FROM
table1
),
cte2 AS (
SELECT
c,
d
FROM
table2
)
SELECT
b,
d
FROM
cte1
JOIN cte2
WHERE
cte1.a = cte2.c;
---
SELECT
a,
GROUP_CONCAT(b, '.') OVER (
ORDER BY
c
GROUPS
BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
EXCLUDE NO OTHERS
) AS no_others,
GROUP_CONCAT(b, '.') OVER (
ORDER BY
c
GROUPS
BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
EXCLUDE CURRENT ROW
) AS current_row,
GROUP_CONCAT(b, '.') OVER (
ORDER BY
c
GROUPS
BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
EXCLUDE GROUP
) AS grp,
GROUP_CONCAT(b, '.') OVER (
ORDER BY
c
GROUPS
BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
EXCLUDE TIES
) AS tie,
GROUP_CONCAT(b, '.') FILTER (
WHERE
c != 'two'
) OVER (
ORDER BY
a
) AS filtered,
CONVERT(
VARCHAR(20),
AVG(SalesYTD) OVER (
PARTITION BY
TerritoryID
ORDER BY
DATEPART(yy, ModifiedDate)
),
1
) AS MovingAvg,
AVG(starting_salary) OVER w2 AVG,
MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
MAX(starting_salary) OVER (
w1
ORDER BY
hire_date
),
LISTAGG(arg, ',') OVER (
PARTITION BY
part
ORDER BY
ord
ROWS
BETWEEN 1 PRECEDING
AND 1 FOLLOWING
) AS LISTAGG_ROWS,
LISTAGG(arg, ',') OVER (
PARTITION BY
part
ORDER BY
ord
RANGE
BETWEEN 1 PRECEDING
AND 1 FOLLOWING
) AS LISTAGG_RANGE,
MIN(Revenue) OVER (
PARTITION BY
DepartmentID
ORDER BY
RevenueYear
ROWS
BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING
) AS MinRevenueBeyond
FROM
t1
WINDOW
w1 AS (
PARTITION BY
department,
division
),
w2 AS (
w1
ORDER BY
hire_date
);