SQL DROP TABLE

Summary: in this tutorial, you will learn how to use the SQL DROP TABLE statement to remove one or more tables in a database.

Introduction to SQL DROP TABLE statement

As the database evolves, we will need to remove obsolete or redundant tables from the database. To delete a table, we use the DROP TABLE statement.

The following illustrates the syntax of the DROP TABLE statement.

DROP TABLE [IF EXISTS] table_name;
Code language: SQL (Structured Query Language) (sql)

To drop an existing table, you specify the name of the table after the DROP TABLE clause. If the table that is being dropped does not exist, the database system issues an error.

To prevent the error of removing a nonexistent table, we use the optional clause IF EXISTS. If we use the IF EXISTS option, the database system will not throw any error if we remove a non-existent table. Some database systems throw a warning or a notice instead.

Notice that not all database systems support the IF EXISTS option. The ones that do support the IF EXISTS option are MySQL, PostgreSQL, and SQL Server 2016.

The DROP TABLE statement removes both data and structure of a table permanently. Some database systems require the table must be empty before it can be removed from the database. This helps you prevent accidentally deleting a table that is still in use.

To delete all data in a table, you can use either the DELETE or TRUNCATE TABLE statement.

To drop a table that is referenced by a foreign key constraint of another table, you must disable or remove the foreign constraint before removing the table.

SQL DROP TABLE examples

Let’s create a new table for practicing the DROP TABLE statement.

The following statement creates a new table named emergency_contacts that stores the emergency contacts of employees.

CREATE TABLE emergency_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    relationship VARCHAR(50) NOT NULL,
    employee_id INT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

The following statement drops the emergency_contacts table:

DROP TABLE emergency_contacts;
Code language: SQL (Structured Query Language) (sql)

SQL DROP TABLE – removing multiples tables

The DROP TABLE statement allows you to remove multiple tables at the same time. To do this, you need to specify a list of comma-separated tables after the DROP TABLE clause as follows:

DROP TABLE table_name1,table_name2,...;
Code language: SQL (Structured Query Language) (sql)

The database system then deletes all tables one by one.

Summary

• Use SQL DROP TABLE statement to delete one or more tables from the database.

 

SQL DISTINCT

Summary: in this tutorial, you will learn how to use the SQL DISTINCT operator to remove duplicates from a result set

Introduction to SQL DISTINCT operator

To remove duplicate rows from a result set, you use the DISTINCT operator in the SELECT clause as follows:

SELECT DISTINCT
    column1, column2, ...
FROM
    table1;
Code language: SQL (Structured Query Language) (sql)

If you use one column after the DISTINCT operator, the DISTINCT operator uses values in that column to evaluate duplicates.

If you use two or more columns, the DISTINCT will use the combination of values in those columns to evaluate the duplicate.

Note that the DISTINCT only removes the duplicate rows from the result set. It doesn’t delete duplicate rows in the table.

If you want to select two columns and remove duplicates in one column, you should use the GROUP BY clause instead.

SQL DISTINCT examples

We will use the employees table in the sample database to demonstrate how the DISTINCT operator works.

1) Using SQL DISTINCT operator on one column example

The following statement selects the salary data from the salary column of the employees table and sorts them from high to low:

SELECT 
    salary
FROM
    employees
ORDER BY salary DESC;
Code language: SQL (Structured Query Language) (sql)

Try It

+----------+
| salary   |
+----------+
| 24000.00 |
| 17000.00 |
| 17000.00 |
| 14000.00 |
| 13500.00 |
| 13000.00 |
| 12000.00 |
| 12000.00 |
| 11000.00 |
| 10000.00 |
|  9000.00 |
|  9000.00 |
...

The result set has some duplicates. For example, 17000, 12000, and 9000.

The following statement uses the DISTINCT operator to select unique values from the salary column of the employees table:

SELECT 
    DISTINCT salary
FROM
    employees
ORDER BY salary DESC;
Code language: SQL (Structured Query Language) (sql)

Try It

+----------+
| salary   |
+----------+
| 24000.00 |
| 17000.00 |
| 14000.00 |
| 13500.00 |
| 13000.00 |
| 12000.00 |
| 11000.00 |
| 10000.00 |
|  9000.00 |
Code language: plaintext (plaintext)

As you can see, the result set doesn’t contain any duplicate salary values.

2) Using SQL DISTINCT operator on multiple columns example

The following statement selects the job id and salary from the employees table:

SELECT
	job_id,
	salary
FROM
	employees
ORDER BY
	job_id,
	salary DESC;
Code language: SQL (Structured Query Language) (sql)

Try It

+--------+----------+
| job_id | salary   |
+--------+----------+
|      1 |  8300.00 |
|      2 | 12000.00 |
|      3 |  4400.00 |
|      4 | 24000.00 |
|      5 | 17000.00 |
|      5 | 17000.00 |
|      6 |  9000.00 |
|      6 |  8200.00 |
...
Code language: plaintext (plaintext)

The result set has some duplicate rows e.g., job id 5 salary 17000. It means that there are two employees with the same job id and salary.

The following statement uses the DISTINCT operator to remove the duplicate values in job id and salary:

SELECT DISTINCT
	job_id,
	salary
FROM
	employees
ORDER BY
	job_id,
	salary DESC;
Code language: SQL (Structured Query Language) (sql)

Try It

+--------+----------+
| job_id | salary   |
+--------+----------+
|      1 |  8300.00 |
|      2 | 12000.00 |
|      3 |  4400.00 |
|      4 | 24000.00 |
|      5 | 17000.00 |
|      6 |  9000.00 |
|      6 |  8200.00 |
...
Code language: plaintext (plaintext)

Note that you still see the duplicate in the job_id column because the DISTINCT operator uses values from both job_id and salary to evaluate the duplicate, not just values in the job_id column.

SQL DISTINCT and NULL

In the database, NULL means unknown or missing data.

Unlike values like numbers, strings, dates, etc. NULL does not equal anything, even itself. The following expression will return unknown (or NULL):

NULL=NULL
Code language: PHP (php)

Typically, the DISTINCT operator treats all NULL the same. Therefore, the DISTINCT operator keeps only one NULL in the result set.

Note that this behavior may be different between database products.

For example, the following statement returns the distinct phone numbers of employees:

SELECT DISTINCT phone_number
FROM employees
ORDER BY phone_number;
Code language: SQL (Structured Query Language) (sql)

Try It

+--------------+
| phone_number |
+--------------+
| NULL         |
| 515.123.4444 |
| 515.123.4567 |
| 515.123.4568 |
| 515.123.4569 |
| 515.123.5555 |
...
Code language: plaintext (plaintext)

Notice that the query returns only one NULL in the result set.

Summary

• Use DISTINCT operator in the SELECT clause to remove duplicate rows from the result set.

 

SQL WHERE

Summary: in this tutorial, you will learn how to use the SQL WHERE clause to filter rows based on specified conditions.

Introduction to SQL WHERE clause

To select specific rows from a table, you use a WHERE clause in the SELECT statement. The following illustrates the syntax of the WHERE clause in the SELECT statement:

SELECT 
    column1, column2, ...
FROM
    table_name
WHERE
    condition;
Code language: SQL (Structured Query Language) (sql)

The WHERE clause appears immediately after the FROM clause. The WHERE clause contains one or more logical expressions that evaluate each row in the table. If a row that causes the condition evaluates to true, it will be included in the result set; otherwise, it will be excluded.

Note that SQL has three-valued logic which are TRUE, FALSE, and UNKNOWN. It means that if a row causes the condition to evaluate to FALSE or NULL, the row will not be returned.

Note that the logical expression that follows the WHERE clause is also known as a predicate. You can use various operators to form the row selection criteria used in the WHERE clause.

The following table shows the SQL comparison operators:

Operator	Meaning
=	Equal to
<> (!=)	Not equal to
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal

To form a simple expression, you use one of the operators above with two operands that can be either column name on one side and a literal value on the other, for example:

salary > 1000
Code language: SQL (Structured Query Language) (sql)

It asks a question: “Is salary greater than 1000?”.

Or you can use column names on both sides of an operator such as:

min_salary < max_salary
Code language: SQL (Structured Query Language) (sql)

This expression asks another question: “Is the min salary less than the max salary?”.

The literal values that you use in an expression can be numbers, characters, dates, and times, depending on the format you use:

• Number: use a number that can be an integer or a decimal without any formatting e.g., 100, 200.5
• Character: use characters surrounded by either single or double quotes e.g., “100”, “John Doe”.
• Date: use the format that the database stores. It depends on the database system e.g., MySQL uses 'yyyy-mm-dd' format to store the date data.
• Time: use the format that the database system uses to store the time. For example, MySQL uses 'HH:MM:SS' to store time data.

Besides the SELECT statement, you can use the WHERE clause in the UPDATE or DELETE statement to specify which rows to be updated or deleted.

SQL WHERE examples

We will use the employees table to demonstrate how to select data from the table using the WHERE clause.

SQL WHERE clause with numeric comparison examples

The following query finds employees who have salaries greater than 14,000 and sorts the result set based on the salary in descending order.

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	salary > 14000
ORDER BY
	salary DESC;

Try It

 The query you provided is selecting the employee_id, department, and salary columns from the company table. It is ordering the results by employee_id and then applying a LIMIT of 5 rows. Additionally, it includes an OFFSET of 3, meaning it will skip the first 3 rows and start retrieving results from the 4th row onward.

Here's the modified query:

sql
SELECT employee_id, department, salary
FROM company
ORDER BY employee_id
LIMIT 5 OFFSET 3;

This query will retrieve 5 rows from the company table, starting from the 4th row (due to the OFFSET 3) and ordered by employee_id.

Note: The table name company is used as an example here. Make sure to replace it with the actual name of your table in the database.

Select employee_id ,department,salary from company order by desc

To sort the result of your query in descending order, you can use the DESC keyword in the ORDER BY clause. Here's the modified query:

sql
SELECT employee_id, department, salary
FROM company
ORDER BY employee_id DESC;

In this query, the employee_id, department, and salary columns are selected from the company table. The ORDER BY clause is used to sort the results in descending order based on the employee_id column.

If you want to sort by multiple columns, you can specify them separated by commas within the ORDER BY clause. For example, to sort by department in ascending order and then by salary in descending order, you can modify the query as follows:

sql
SELECT employee_id, department, salary
FROM company
ORDER BY department ASC, salary DESC;

In this modified query, the results will be sorted first by the department column in ascending order and then by the salary column in descending order.