Design Product Table in SQL when you don't know how is foreign keys works in SQL 🤣
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- -- Table structure for table `products` -- CREATE TABLE `products` ( `product_id` int(100) NOT NULL, `product_cat` int(100) NOT NULL, `product_brand` int(100) NOT NULL, `product_title` varchar(255) NOT NULL, `product_price` int(100) NOT NULL, `product_desc` text NOT NULL, `product_image` text NOT NULL, `product_keywords` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Convert Current Date to MSSQL format 🤗
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SET @DD = DATEPART(dd, GETDATE()) SET @MM = DATEPART(mm, GETDATE()) SET @YYYY = DATEPART(yyyy, GETDATE()) IF LEN(@DD) = '1' BEGIN SET @DD = '0' + @DD END IF LEN(@MM) = '1' BEGIN SET @MM = '0' + @MM END SET @DT_D = @DD + '.' + @MM + '.' + @YYYY |
The design of menu table in SQL 😝
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE IF NOT EXISTS `upmenu_block` ( `id_foto_b` int(5) NOT NULL auto_increment, `id_sec` int(5) NOT NULL, `f_b1` varchar(100) NOT NULL, `f_b2` varchar(100) NOT NULL, `f_b3` varchar(100) NOT NULL, `f_b4` varchar(100) NOT NULL, `f_o1` varchar(200) NOT NULL, `f_o2` varchar(200) NOT NULL, `f_o3` varchar(200) NOT NULL, `f_o4` varchar(200) NOT NULL PRIMARY KEY (`id_foto_b`) ) |
Do we really need this SQL ? It's supposed to be always 0 Rows Affected. 🙃
1
|
UPDATE `options_table` SET `option_value_ru` = 'Оutlet' WHERE `option_value_ru` LIKE 'Outlet' |
Select statement with many null in SQL
1 2 3 4 |
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, type_table_of_name_tag_value () FROM DUAL WHERE dummy = 'Z' |
Design log event table including ALL keys in SQL
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `log_event` ( `id` bigint(20) NOT NULL auto_increment, `logtime` datetime default NULL, `etype` text, `module` text, `edata` text, `session` text, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `id_2` (`id`) ) |
How to round numbers and remove 0 in SQL
1
|
SELECT REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(LTRIM(' 850.0000'),'0',' ')),' ','0'),'.',' ')),' ','.') |
User Administrator relation in SQL
1 2 3 4 |
CREATE TABLE IF NOT EXISTS `ls_user_administrator` ( `user_id` int(11) unsigned NOT NULL, UNIQUE KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Is Number function in SQL 😚
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
create function is_numeric ( @a varchar(100) ) returns int as begin declare @retval int set @a = replace(@a, '0', '') set @a = replace(@a, '1', '') set @a = replace(@a, '2', '') set @a = replace(@a, '3', '') set @a = replace(@a, '4', '') set @a = replace(@a, '5', '') set @a = replace(@a, '6', '') set @a = replace(@a, '7', '') set @a = replace(@a, '8', '') set @a = replace(@a, '9', '') set @a = replace(@a, '.', '') if len(rtrim(ltrim(@a))) > 0 set @retval = 0 else set @retval = 1 return @retval end |
Store Procedure in SQL not good example
1 2 3 4 5 |
if @SubDepartmentID = 0 set @SubDepartmentID = null if @QuoteID = 0 set @QuoteID = null if @PartnerID = 0 set @PartnerID = null if @QuoteID = 0 set @QuoteID = null if @SubDepartmentID = 0 set @SubDepartmentID = null |
Nice example how to use When and Case in SQL 🤣
1
|
SELECT a.*, ads.*, CASE ads.operation_id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE 3 END AS sort |
This SQL query look unreal 😆
1 2 3 4 5 6 7 8 9 10 11 |
select Outcomes.ship,count(*) from Outcomes where outcomes.result!='sunk' group by ship HAVING count(*)>=2 union select ships.name,count(*) from ships where ships.launched!=NULL group by name HAVING count(*)>=2 |
How to check phone number in SQL 🤣
1 2 3 |
if @OWNER_PHONE <> @OLD_PHONE set @PHONE = @OWNER_PHONE if @PHONE <> @OLD_PHONE set @OWNER_PHONE = @PHONE set @PHONE = @OWNER_PHONE |