Practice Questions
1. Create a table called 'employees' with columns 'id', 'name', 'age' and add a
primary key constraint on the 'id' column.
2. Create a foreign key constraint that references the "id" column of the
"employees" table on the "department" table's "employee_id" column.
3. Add a foreign key constraint on the 'department' column in the 'employees'
table, referencing the 'department_id' column in the 'departments' table.
4. Add a new column "email" to the "employees" table with the "not null"
constraint and a default value of "[email protected]".
5. Add not null constraints on the 'name' and 'email' columns in the 'employees'
table.
6. Add a unique constraint on the 'email' column in the 'employees' table.
7. Add an auto_increment constraint on the 'id' column in the 'orders' table.
8. Create an enum column 'status' in the 'orders' table with allowed values
'pending', 'confirmed', and 'delivered'.
9. Add a default constraint on the 'status' column in the 'orders' table with the
value 'pending'.
10. Add a check constraint to ensure the 'age' column in the 'employees' table
has values greater than or equal to 18.
11. Create an index on the 'name' column in the 'employees' table.
12. Create tables 'employees_a' and 'employees_b' with the following fields:
/*
employees_a:
- id
- name
- department
employees_b:
- id
- name
- department
*/
-- insert values for the `employees_a` and `employees_b` with the following data
/*
employees_a : (1, 'John', 'Sales'), (2, 'Jane', 'Marketing'), (3, 'Jim', 'HR')
employees_b : (2, 'Jane', 'Marketing'), (4, 'Jack', 'IT')
*/
13. Write a SQL query to combine the data from 'employees_a' and 'employees_b'
using UNION operation and return the unique values in table 'employees_c'.
14. Write a SQL query to combine the data from 'employees_a' and 'employees_b'
using UNION ALL operation and return the unique values in table
'employees_d'.
15. Compare the results of 'employees_c' and 'employees_d' to understand the
difference between UNION and UNION ALL operations.
