Articles in the SQL category in Technology Tips.
Composite key, or composite primary key, refers to cases where more than one column is used to specify the primary key of a table. In such cases, all foreign keys will also need to include all the columns in the composite key. Note that the columns that make up a composite key can be of different data types.
Below is the SQL syntax for specifying a composite key:
In database design, it is a good practice to have a primary key for each table. There are two ways to specify a primary key: The first is to use part of the data as the primary key. For example, a table that includes information on employees may use Social Security Number as the primary key. This type of key is called a natural key. The second is to use a new field with artificially-generated values whose sole purpose is to be used as a primary key. This is called a surrogate key.
A surrogate key has the following characteristics:
Sometimes, it may be necessary to rename a table. There is no standard way to rename a table, and the implementation varies by RDBMS. Below we discuss how we can rename a table in MySQL, Oracle, and SQL Server.
In MySQL, we can rename a table using one of the following methods:
Method 1
RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME
Method 2
ALTER TABLE OLD_TABLE_NAME
RENAME TO NEW_TABLE_NAME
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 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);
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.
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.