Hi!
1. Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student.
Create table student(id SERIAL PRIMARY KEY,name varchar(50),age int);
i created a table student wher the id value should be unique ,let it be primary key,then name is give varchar datatype,for age the data type is int.
*2. Create a table employees where name and email cannot be empty, but phone_number can be optional.
*
create table employee(e_id serial primary key,name varchar(50) not null,email va
rchar(101) not null,phone_number int);
here a table employee is created with fields employee id,name ,email,phone number where e_id is primary key ,name and email are not null
3. Create a table users where both username and email must be unique across all records
CREATE table users(u_id SERIAL PRIMARY KEY,username varchar(50) unique,email var
char(101) unique);
here a table users is created where user id is u_id which is the primary key in the table,while username and email are unique.
*4. Create a table products where price must always be greater than 0 and stock cannot be negative.
*
create table products(p_id serial primary key,price int check(price > 0),
product_name varchar(101),stock int check(stock>=0))
in the above code the table product haave primary key p_id,product name, price which should be greater than 0 and stock must not be negative
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.
create table orders(o_id serial primary key,order_name varchar(101),status varc
har(50) default 'pending',created_at timestamp);
here the table order has a order id as primary key, order_name,status is set default as pending,and created_at use timestamp datatype.
6.Create a table accounts where account_number must be unique and not null
balance must always be greater than or equal to 0
CREATE TABLE accounts (account_id SERIAL PRIMARY KEY,account_number VARCHAR(20) UNIQUE NOT NULL,balance DECIMAL(15,2) default 0 CHECK (balance >= 0));
create table accounts account_number is unique to prevent duplicates, and uses a CHECK balance is not negative.
*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.
*
CREATE TABLE enrollments (e_id SERIAL PRIMARY KEY,s_id INT,c_id INT,UNIQUE (student_id, course_id));
sql
create table enrollments unique values student_id and course_id. This prevents a student from being registered for the same course more than once while still allowing them to take many different courses.
8.Create two tables:departments with id and name employees with id, name, and department_id Ensure that department_id in employees must exist in departments.
CREATE TABLE departments (id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,department_id INT REFERENCES departments(id));
sql
the departments table contain id,name and the employee table contain id,name,department_it which exist in departments table foreign key.
9.Modify the previous foreign key example so that:When a department is deleted, all related employees are also deleted,When a department ID is updated, it reflects in the employees table
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) ON DELETE CASCADE ON UPDATE CASCADE);
the table departments contain id,name.where teh table employees contain id,name,department_id as reference from table departments which delete all related employees when deleted ,When a department ID is updated, it reflects in the employees table.
Top comments (0)