SQL integer integer division, and integer decimal Division

 https://stackoverflow.com/questions/3443672/integer-division-in-sql-server

SELECT cast(151/6 AS DECIMAL(9,2))
SELECT 151/6

NOTE the above two commands will product integer result as 151 is integer and 6 is integer, 151/6 will produce integer 25

For SQL to get decimal result, at least one of the numeber has to be decimal 

SELECT 151/CAST(6 AS DECIMAL (9,2))

MYSQL FIND_IN_SET() usage and Laravel implication

 FIND_IN_SET used in select

https://www.w3schools.com/sql/func_mysql_find_in_set.asp

The FIND_IN_SET() function returns the position of a string within a list of strings.

• If string is not found in string_list, this function returns 0
• If string or string_list is NULL, this function returns NULL
• If string_list is an empty string (""), this function returns 0

FIND_IN_SET("a", "s,q,l")
0

FIND_IN_SET used in where

https://stackoverflow.com/questions/16209040/mysql-find-in-set-in-where-clause

*Caution, this will cause a full table scan, should be using column IN (value1,value2)

SELECT * FROM employee WHERE FIND_IN_SET(1,comma_separated_column_name)

Here is my example 

The query selects all trips that have 1 in the weekend column

Laravel usage

https://stackoverflow.com/questions/35594450/find-in-set-in-laravel-example

$colname = 'css'
$query = DB::table('tags_value')
         ->whereRaw('FIND_IN_SET(?,Tags)', [$colname])
         ->get();

MYSQL Group By deep dive, problems and issues only_full_group_by mode, Group by hits the first matching result

https://stackoverflow.com/questions/6572110/order-by-date-and-time-before-group-by-name-in-mysql

SELECT * 
FROM (
    SELECT * FROM table_name
    ORDER BY date ASC, time ASC 
) AS sub
GROUP BY name

GROUP BY groups on the first matching result it hits. If that first matching hit happens to be the one you want then everything should work as expected.

https://stackoverflow.com/questions/45484068/what-are-the-benefits-of-only-full-group-by-mode

MYSQL/SQL Group by comes with only_full_group_by mode, meaning, the item you want to group by has to be in select.

only_full_group_by = on tells MySQL engine: Do not apply GROUP BY when you have doubt about what results to show and throw an error. only apply it if the command specifically tells you what to do. i.e. when the command is full and complete!

only_full_group_by = off tells MySQL engine: always apply GROUP BY and if you have doubt about what results to choose, just pick one randomly!

You will not turn it off if you use GROUP BY properly!

Example:

Table: users

 id   |  name
----------------
  1      ali
  2      john
  3      ali

When you use GROUP BY on the name column:

SELECT * FROM users GROUP BY name;

There are two possible results:

  1      ali
  2      john     

OR

  2      john
  3      ali

MYSQL does not know what result to choose! Because there are different ids but both have name=ali.

Solution1:

only selecting the name field:

SELECT name FROM users GROUP BY name;

result:

  ali
  john     

This is a perfect solution. removing columns that makes GROUP BY confused. This means you know what you're doing. Usually, you do not need
those columns, but if you need them, go to solution3!

Solution2:

Turning off only_full_group_by. MYSQL will show you one of the two possible results RANDOMLY!! (It's ok if you do not really care what id it will choose)

Solution3

Use an Aggregate function like MIN(), MAX() to help MYSQL to decide what it must choose.

For example, I want the minimum id:

SELECT MIN(id), name FROM users GROUP BY name;

result:

  1      ali
  2      john     

It will choose the ali row which has the minimum id.

https://www.w3schools.com/sql/sql_having.asp 

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

https://www.w3schools.com/sql/sql_orderby.asp

ORDER BY Several Columns Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

Example

SELECT * FROM Customers
ORDER BY Country, CustomerName;

classic 1=1 -- SQL Injection

 https://stackoverflow.com/questions/24843689/whats-the-meaning-of-admin-or-1-1?answertab=active#tab-top

This is a classic SQL injection.

See this fiddle while I explain it: SQLfiddle

In this example, there are 5 users being added to the table. Your query is then run. The expected result would be to return the password value for the admin user only.

However, by adding 1=1, which is a true statement, all passwords are returned.

Another way to help visualize this, is to add parenthesis so that you can see how everything is evaluated.

SELECT pass FROM users WHERE (user_name = 'admin')              OR (1=1) -- '
                                 ^ Pulls only the admin user        ^ Pulls everything because 1=1

So, we are selecting the password from the table where the user name is admin. We are also pulling the password from the table where ever 1=1 - which is always true. Each row is evaluated to true, thus all passwords are returned.

The final -- ' is used to comment out the rest of your query.

SELECT pass from users WHERE user_name = 'admin' or (1=1) -- 'and permission='superadmin'

Normally, (if the 1=1 hadn't been injected), you'd pull the password for the user with user_name of admin and superadmin permissions. You've now commented that out, and it isn't executed. This is how the entire table of passwords can be returned.

SQL concat

https://www.mssqltips.com/sqlservertip/2985/concatenate-sql-server-columns-into-a-string-with-concat/

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as MailingName
FROM Person.Person

As you can see in the screen shot below the MailingName is NULL for any row that has NULL for any one of the name columns. The only rows that have MailingName filled in have a value for all the title, firstname, middlename, and lastname columns. This could be corrected by wrapping ISNULL(column,'') around all the columns in the concatenated field to account for any values having nulls, but that code gets long, messy, and hard to read.

Below is an example is using ISNULL along with the plus sign for concatenation. The ISNULL function will replace NULL values with the value noted in the second parameter, which in this example is an empty string.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,'') + ' ' + ISNULL(LastName,'') as MailingName
FROM Person.Person

As you can see in the example below, the MailingName is no longer NULL as it replaced the NULL values with an empty string. This achieves the same as using the CONCAT() function, but requires a lot more code and readability.

The next set of code is using the new CONCAT() function that is in SQL Server 2012 and later versions. It replaces NULL values with an empty string of type VARCHAR(1). This code is much easier to read and write when you need to have NULL code handling in place.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName) as MailingName
FROM Person.Person

If you see the results of this, all MailingName values are present, even if they have some of the columns set to NULL.

As you can see this new function is very handy and behaves much different that the old form of concatenation. Instead of evaluating to NULL if any if the columns cont