We all know that the MAX function can be used to find the largest value in SQL. How, then, can we write a single-pass SQL that can be used across different database systems to find the second largest value in a column? Single-pass means only one SQL query gets executed, as opposed to having multiple SQL statements using temporary tables to store intermediate results.
sql
Find Duplicates in SQL
SQL does not provide a built-in capability to find duplicates in a table. Fortunately, it is fairly easy to write a SQL query that does it. The idea is to count the number of occurrences for each value, and then use the HAVING condition to show only the values that appear more than once.
SELECT COLUMN_NAME, COUNT(*)
FROM TABLE_NAME
GROUP BY COLUMN_NAME
HAVING (COUNT(*) > 1);
Copy a table in SQL
In a relational database, sometimes there is a need to copy a table in SQL. This post talks about several different scenarios on doing this, and how to use SQL to accomplish each scenario.
Copy a table with all data
To copy a table with all the data, simply create a new table and populate the table with SELECT * from the original table. This will copy over the table structure as well as all the data that was in the original table.
The syntax you would use is
CREATE TABLE TABLE_NAME_2
SELECT * FROM TABLE_NAME 1;
Please note that this will not copy over the constraints or indexes associated with this table.