20 SQL Handy Guide
1. To find the highest salary
SELECT name, max(column) AS SALARY FROM table;
2. To find the second highest salary
SELECT name, MAX(column) AS 2ndSALARY FROM table WHERE < (SELECT MAX (salary) FROM table);
3. Formula to find the nth salary
SELECT * FROM EMPLOYEE ORDER BY salary DESC LIMIT n-1,1;
4. To find distinct nth salary
SELECT * FROM EMPLOYEE WHERE SALARY = (SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1,1);
5. COALESCE: Returns the first non-null value in a list:
SELECT COALESCE(NULL, NULL, NULL, ‘W3Schools.com’, NULL, ‘Example.com’);
6. how to find null values in a table
SELECT column_names. FROM table_name. WHERE column_name IS NULL;
7. To update a null value in SQL
update my_table_name
set my_field_name = null
where my_field_name = ‘’
8. Diff bet ORDER BY and Group by:
ORDER BY allows you to sort the result set according to different criteria, such as the first sort by name from a-z, then sort by the price highest to lowest.
GROUP BY will aggregate records by the specified columns which allow you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).
9. Order by type use
ORDER BY column_name and DESC;
10. Types of aggregate function in SQL:
AVG — calculates the average of a set of values.
COUNT — counts rows in a specified table or view.
MIN — gets the minimum value in a set of values.
MAX — gets the maximum value in a set of values.
SUM — calculates the sum of values.
11. Use IN to fetch the multiple row/entries from the table
SELECT * FROM logistic_v1.users WHERE id IN (1,5,11);
Use NOT IN to avoid the specific row/entries from the table
SELECT * FROM logistic_v1.users WHERE id NOT IN (1,5,11);
Use IN to fetch the entry according to the statements/condition
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
12. The CONCAT() function adds two or more strings together.
SELECT CONCAT(‘SQL’, ‘ is’, ‘ fun!’);
13. UNION: Combines the result set of two or more SELECT statements (only distinct values)
UNION ALL: Combines the result set of two or more SELECT statements (allows duplicate values)
14. Enlist the tables from the DB.
SHOW TABLES FROM db_name;
15. To find the total numbers of tables from the selected database
SELECT count(*) AS TOTALNUMBEROFTABLES
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘db_name’;
16. To find the versions and details
SHOW VARIABLES LIKE “%version%”;
And in command line type mysql -v
17. Differences between Stored Procedure and User Defined Function in SQL Server
User-Defined Function:
The function must return a value.
Will allow only Select statements, it will not allow us to use DML statements.
It will allow only input parameters, doesn’t support output parameters.
It will not allow us to use try-catch blocks.
Transactions are not allowed within functions.
We can use only table variables, it will not allow using temporary tables.
Stored Procedures can’t be called from a function.
Functions can be called from a select statement.
A UDF can be used in the join clause as a result set.
Stored Procedure:
Stored Procedure may or not return values.
Can have select statements as well as DML statements such as insert, update, delete, and so on.
It can have both input and output parameters.
For exception handling, we can use try-catch blocks.
Can use transactions within Stored Procedures.
Can use both table variables as well as a temporary table in it.
Stored Procedures can call functions.
Procedures can’t be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute the Stored Procedure.
Procedures can’t be used in Join clause
18. Data redundancy is defined as the storing of the same data in multiple locations. An example of data redundancy is saving the same file five times to five different disks.
19. An insertion anomaly is the inability to add data to the database due to the absence of other data. For example, assume Student_Group is defined so that null values are not allowed. … Update, deletion, and insertion anomalies are very undesirable in any database. Anomalies are avoided by the process of normalization.[1]
20. There are 3 types of languages in the DBMS as mentioned below:
DDL: DDL is Data Definition Language which is used to define the database and schema structure by using some set of SQL Queries like CREATE, ALTER, TRUNCATE, DROP, and RENAME.
DCL: DCL is Data Control Language which is used to control the access of the users inside the database by using some set of SQL Queries like GRANT and REVOKE.
DML: DML is Data Manipulation Language which is used to do some manipulations in the database like Insertion, Deletion, etc. by using some set of SQL Queries like SELECT, INSERT, DELETE and UPDATE.
