UNION Query

SQL: UNION Query


The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.
Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION query is:
select field1, field2, . field_n
from tables
UNION
select field1, field2, . field_n
from tables;


Example #1
The following is an example of a UNION query:
select supplier_id
from suppliers
UNION
select supplier_id
from orders;

In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.

Example #2 - With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
The supplier_name / company_name fields are in position #2 in the result set.

Frequently Asked Questions


Question:  I need to compare two dates and return the count of a field based on the date values. For example, I have a date field in a table called last updated date. I have to check if trunc(last_updated_date >= trun(sysdate-13).
Answer:  Since you are using the COUNT function which is an aggregate function, we'd recommend using a UNION query. For example, you could try the following:
SELECT a.code as Code, a.name as Name, count(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
 and a.status = 1
 and b.status = 1
 and b.Ncode <> 'a10'
 and trunc(last_updated_date) <= trunc(sysdate-13)
group by a.code, a.name
UNION
SELECT a.code as Code, a.name as Name, count(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
 and a.status = 1
 and b.status = 1
 and b.Ncode <> 'a10'
 and trunc(last_updated_date) > trunc(sysdate-13)
group by a.code, a.name;

The UNION query allows you to perform a COUNT based on one set of criteria.
trunc(last_updated_date) <= trunc(sysdate-13)
As well as perform a COUNT based on another set of criteria.
trunc(last_updated_date) > trunc(sysdate-13)

UNION ALL Query

SQL: UNION ALL Query


The UNION ALL query allows you to combine the result sets of 2 or more "select" queries. It returns all rows (even if the row exists in more than one of the "select" statements).
Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION ALL query is:
select field1, field2, . field_n
from tables
UNION ALL
select field1, field2, . field_n
from tables;


Example #1
The following is an example of a UNION ALL query:
select supplier_id
from suppliers
UNION ALL
select supplier_id
from orders;

If a supplier_id appeared in both the suppliers and orders table, it would appear multiple times in your result set. The UNION ALL does not remove duplicates.

Example #2 - With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION ALL
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
The supplier_name / company_name fields are in position #2 in the result set.

INTERSECT Query

SQL: INTERSECT Query


The INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.
The syntax for an INTERSECT query is:
select field1, field2, . field_n
from tables
INTERSECT
select field1, field2, . field_n
from tables;


Example #1
The following is an example of an INTERSECT query:
select supplier_id
from suppliers
INTERSECT
select supplier_id
from orders;

In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.

Example #2 - With ORDER BY Clause
The following is an INTERSECT query that uses an ORDER BY clause:
select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
INTERSECT
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
The supplier_name / company_name fields are in position #2 in the result set.

MINUS Query

SQL: MINUS Query


The MINUS query returns all rows in the first query that are not returned in the second query.
Each SQL statement within the MINUS query must have the same number of fields in the result sets with similar data types.
The syntax for an MINUS query is:
select field1, field2, . field_n
from tables
MINUS
select field1, field2, . field_n
from tables;


Example #1
The following is an example of an MINUS query:
select supplier_id
from suppliers
MINUS
select supplier_id
from orders;

In this example, the SQL would return all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.

Example #2 - With ORDER BY Clause
The following is an MINUS query that uses an ORDER BY clause:
select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
MINUS
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
The supplier_name / company_name fields are in position #2 in the result set.

UPDATE Statement

SQL: UPDATE Statement


The UPDATE statement allows you to update a single record or multiple records in a table.
The syntax for the UPDATE statement is:
UPDATE table
SET column = expression
WHERE predicates;


Example #1 - Simple example
Let's take a look at a very simple example.
UPDATE suppliers
SET name = 'HP'
WHERE name = 'IBM';

This statement would update all supplier names in the suppliers table from IBM to HP.

Example #2 - More complex example
You can also perform more complicated updates.
You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.
For example:
UPDATE suppliers
SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
  ( SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id);
Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.
Learn more about the EXISTS condition.

Practice Exercise #1:
Based on the suppliers table populated with the following data, update the city to "Santa Clara" for all records whose supplier_name is "NVIDIA".
CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);



INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5004, 'NVIDIA', 'New York');

Solution:
The following SQL statement would perform this update.
UPDATE suppliers
SET city = 'Santa Clara'
WHERE supplier_name = 'NVIDIA';

The suppliers table would now look like this:
SUPPLIER_ID SUPPLIER_NAME CITY
5001 Microsoft New York
5002 IBM Chicago
5003 Red Hat Detroit
5004 NVIDIA Santa Clara

Practice Exercise #2:
Based on the suppliers and customers table populated with the following data, update the city in the suppliers table with the city in the customers table when the supplier_name in the suppliers table matches the customer_name in the customers table.
CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);



INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5005, 'NVIDIA', 'LA');


CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);



INSERT INTO customers (customer_id, customer_name, city)
VALUES (7001, 'Microsoft', 'San Francisco');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7002, 'IBM', 'Toronto');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7003, 'Red Hat', 'Newark');

Solution:
The following SQL statement would perform this update.
UPDATE suppliers
SET city = ( SELECT customers.city
FROM customers
WHERE customers.customer_name = suppliers.supplier_name)
WHERE EXISTS
  ( SELECT customers.city
    FROM customers
    WHERE customers.customer_name = suppliers.supplier_name);
The suppliers table would now look like this:
SUPPLIER_ID SUPPLIER_NAME CITY
5001 Microsoft San Francisco
5002 IBM Toronto
5003 Red Hat Newark
5004 NVIDIA LA

INSERT Statement

SQL: INSERT Statement


The INSERT statement allows you to insert a single record or multiple records into a table.
The syntax for the INSERT statement is:
INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);


Example #1 - Simple example
Let's take a look at a very simple example.
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(24553, 'IBM');

This would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.

Example #2 - More complex example
You can also perform more complicated inserts using sub-selects.
For example:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

By placing a "select" in the insert statement, you can perform multiples inserts quickly.
With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL statement before performing the insert.
SELECT count(*)
FROM customers
WHERE city = 'Newark';


Frequently Asked Questions


Question:  I am setting up a database with clients. I know that you use the "insert" statement to insert information in the database, but how do I make sure that I do not enter the same client information again?
Answer:  You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
Learn more about the EXISTS condition.

Question:  How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer:  The following is an example of how you might insert 3 rows into the suppliers table in Oracle.
INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

DELETE Statement

SQL: DELETE Statement


The DELETE statement allows you to delete a single record or multiple records from a table.
The syntax for the DELETE statement is:
DELETE FROM table
WHERE predicates;


Example #1 - Simple example
Let's take a look at a simple example:
DELETE FROM suppliers
WHERE supplier_name = 'IBM';

This would delete all records from the suppliers table where the supplier_name is IBM.
You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL statement before performing the delete.
SELECT count(*)
FROM suppliers
WHERE supplier_name = 'IBM';


Example #2 - More complex example
You can also perform more complicated deletes.
You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.
For example:
DELETE FROM suppliers
WHERE EXISTS
  ( select customers.name
     from customers
     where customers.customer_id = suppliers.supplier_id
     and customers.customer_name = 'IBM' );

This would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and the customer_id is the same as the supplier_id.
Learn more about the EXISTS condition.
If you wish to determine the number of rows that will be deleted, you can run the following SQL statement before performing the delete.
SELECT count(*) FROM suppliers
WHERE EXISTS
  ( select customers.name
     from customers
     where customers.customer_id = suppliers.supplier_id
     and customers.customer_name = 'IBM' );


Frequently Asked Questions


Question:  How would I write an SQL statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?
Answer:  You could try something like this:
DELETE FROM TableA
WHERE NOT EXISTS
  ( select *
     from TableB
     where TableA .field1 = TableB.fieldx
     and TableA .field2 = TableB.fieldz );

Local Temporary tables

SQL: Local Temporary tables


Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.
The basic syntax is:
DECLARE LOCAL TEMPORARY TABLE table_name ( ...);

Global Temporary tables

SQL: Global Temporary tables


Global temporary tables are distinct within SQL sessions.
The basic syntax is:
CREATE GLOBAL TEMPORARY TABLE table_name ( ...);

For example:
CREATE GLOBAL TEMPORARY TABLE supplier
( supplier_id numeric(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50)
)


This would create a global temporary table called supplier .

DROP TABLE Statement

SQL: DROP TABLE Statement


The DROP TABLE statement allows you to remove a table from the database.
The basic syntax for the DROP TABLE statement is:
DROP TABLE table_name;

For example:
DROP TABLE supplier;
This would drop table called supplier.

ALTER TABLE Statement

SQL: ALTER TABLE Statement


The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table

The basic syntax for renaming a table is:
ALTER TABLE table_name
 RENAME TO new_table_name;

For example:
ALTER TABLE suppliers
 RENAME TO vendors;

This will rename the suppliers table to vendors.

Adding column(s) to a table

Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
 ADD column_name column-definition;

For example:
ALTER TABLE supplier
 ADD supplier_name  varchar2(50);

This will add a column called supplier_name to the supplier table.

Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
ADD ( column_1 column-definition,
  column_2 column-definition,
  ...
  column_n column_definition );
For example:
ALTER TABLE supplier
ADD ( supplier_name varchar2(50),
  city varchar2(45) );
This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table

Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
 MODIFY column_name column_type;

For example:
ALTER TABLE supplier
 MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
MODIFY ( column_1 column_type,
  column_2 column_type,
  ...
  column_n column_type );
For example:
ALTER TABLE supplier
MODIFY ( supplier_name varchar2(100) not null,

city varchar2(75)   );
This will modify both the supplier_name and city columns.

Drop column(s) in a table

Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
 DROP COLUMN column_name;

For example:
ALTER TABLE supplier
 DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
 RENAME COLUMN old_name to new_name;

For example:
ALTER TABLE supplier
 RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.
Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!

Practice Exercise #1:
Based on the departments table below, rename the departments table to depts.
CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);


Solution:
The following ALTER TABLE statement would rename the departments table to depts:
ALTER TABLE departments
 RENAME TO depts;


Practice Exercise #2:
Based on the employees table below, add a column called salary that is a number(6) datatype.
CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);


Solution:
The following ALTER TABLE statement would add a salary column to the employees table:
ALTER TABLE employees
 ADD salary number(6);


Practice Exercise #3:
Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.
CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);


Solution:
The following ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:
ALTER TABLE customers
ADD ( contact_name varchar2(50),
  last_contacted date );

Practice Exercise #4:
Based on the employees table below, change the employee_name column to a varchar2(75) datatype.
CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);


Solution:
The following ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):
ALTER TABLE employees
 MODIFY employee_name varchar2(75);


Practice Exercise #5:
Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.
CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50),  
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);


Solution:
The following ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:
ALTER TABLE customers
MODIFY ( customer_name varchar2(50) not null,
  state varchar2(2) );

Practice Exercise #6:
Based on the employees table below, drop the salary column.
CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);


Solution:
The following ALTER TABLE statement would drop the salary column from the employees table:
ALTER TABLE employees
 DROP COLUMN salary;


Practice Exercise #7:
Based on the departments table below, rename the department_name column to dept_name.
CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);


Solution:
The following ALTER TABLE statement would rename the department_name column to dept_name in the departments table:
ALTER TABLE departments
 RENAME COLUMN department_name to dept_name;

CREATE a table from another table

SQL: CREATE a table from another table


You can also create a table from an existing table by copying the existing table's columns.
It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax #1 - Copying all columns from another table

The basic syntax is:
CREATE TABLE new_table
  AS (SELECT * FROM old_table);

For example:
CREATE TABLE suppliers
  AS (SELECT *
         FROM companies
         WHERE id > 1000);
This would create a new table called suppliers that included all columns from the companies table.
If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table

The basic syntax is:
CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n FROM old_table);

For example:
CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
          FROM companies
          WHERE id > 1000);
This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.
Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables

The basic syntax is:
CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
          FROM old_table_1, old_table_2, ... old_table_n);

For example:
CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
          FROM companies, categories
          WHERE companies.id = categories.id
          AND companies.id > 1000);
This would create a new table called suppliers based on columns from both the companies and categories tables.

Acknowledgements: We'd like to thank Dave M. for contributing to this solution!

Frequently Asked Questions


Question: How can I create a table from another table without copying any values from the old table?
Answer: To do this, the basic syntax is:
CREATE TABLE new_table
  AS (SELECT * FROM old_table WHERE 1=2);
For example:
CREATE TABLE suppliers
  AS (SELECT * FROM companies WHERE 1=2);
This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.

CREATE TABLE Statement

SQL: CREATE TABLE Statement


The CREATE TABLE statement allows you to create and define a table.
The basic syntax for a CREATE TABLE statement is:
CREATE TABLE table_name
( column1 datatype null/not null,
  column2 datatype null/not null,
  ...
);

Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

For example:
CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50)
);



Practice Exercise #1:
Create a customers table that stores customer ID, name, and address information. The customer ID should be the primary key for the table.
Solution:
The CREATE TABLE statement for the customers table is:
CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);



Practice Exercise #2:
Based on the departments table below, create an employees table that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.
CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);


Solution:
The CREATE TABLE statement for the employees table is:
CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);