1. Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student.
We create a students table with id, name, and age. Since id should uniquely identify each student and cannot be null, we make it the primary key.
create table students (id serial primary key, name varchar(100), age int);
2. Create a table employees where name and email cannot be empty, but phone_number can be optional.
We create an employees table with name, email, and phone_number. Since name and email are mandatory, we apply not null, while phone_number is left optional.
create table employees (id serial primary key, name varchar(100) not null, email varchar(100) not null, phone_number varchar(20));
3. Create a table users where both username and email must be unique across all records.
We create a users table and ensure that username and email are unique by adding unique constraints so no duplicates are allowed.
create table users (id serial primary key, username varchar(100) unique, email varchar(100) unique);
4. Create a table products where price must always be greater than 0 and stock cannot be negative.
We create a products table and use check constraints to ensure price is greater than 0 and stock is zero or more.
create table products (id serial primary key, price numeric check (price > 0), stock int check (stock >= 0));
5. Create a table orders where status should default to 'pending' if no value is provided, and created_at should store the current timestamp automatically.
We create an orders table where status gets a default value of 'pending' if not given, and created_at automatically stores the current timestamp.
create table orders (id serial primary key, status varchar(50) default 'pending', created_at timestamp default current_timestamp);
6. Create a table accounts where account_number must be unique and not null and balance must always be greater than or equal to 0
We create an accounts table where account_number is required and unique, and balance is restricted using a check so it never goes below zero.
create table accounts (id serial primary key, account_number varchar(100) unique not null, balance numeric check (balance >= 0));
7. Create a table enrollments where a student can enroll in multiple courses, but the combination of student_id and course_id must be unique.
We create an enrollments table and apply a composite unique constraint on student_id and course_id so the same pair cannot repeat.
create table enrollments (student_id int, course_id int, unique (student_id, course_id));
8. Create two tables: departments and employees, ensuring department_id in employees must exist in departments.
We create departments first, then employees, and use a foreign key so department_id in employees must match an existing id in departments.
create table departments (id serial primary key, name varchar(100)); create table employees (id serial primary key, name varchar(100), department_id int references departments(id));
9. Modify the previous foreign key example so that when a department is deleted, all related employees are also deleted and when a department ID is updated, it reflects in the employees table
We extend the foreign key by adding cascade rules so deletes and updates in departments automatically reflect in employees.
alter table employees drop constraint employees_department_id_fkey; alter table employees add constraint employees_department_id_fkey foreign key (department_id) references departments(id) on delete cascade on update cascade;
Top comments (0)