CREATE TABLES

Tables with constraints

QN:1 

Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student.

Query:

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT,
age INT
);


since, id should uniquely identify each student.. we use PRIMARY KEY

QN:2

 Create a table employees where name and email cannot be empty, but phone_number can be optional.

Query:

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
phone_number TEXT
);


since, name and email should not be empty.. we use NOT NULL.. phone is optional so no constraint

QN:3 

Create a table users where both username and email must be unique across all records.

Query:

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE,
email TEXT UNIQUE
);


since, username and email should not repeat.. we use UNIQUE

QN:4

 Create a table products where price must always be greater than 0 and stock cannot be negative.

Query:

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
price INT CHECK (price > 0),
stock INT CHECK (stock >= 0)
);


since, price should be greater than 0 and stock should not be negative.. we use CHECK

QN: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.

Query:

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status TEXT DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

since, we need default values.. we use DEFAULT for status and timestamp

QN: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

Query:

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
account_number TEXT UNIQUE NOT NULL,
balance INT CHECK (balance >= 0)
);


since, account_number should not be empty and must be unique.. and balance should not go negative

QN: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.

Query:

CREATE TABLE enrollments (
student_id INT,
course_id INT,
UNIQUE (student_id, course_id)
);


since, same student should not enroll same course again.. so we use combination UNIQUE

QN:8

 Create two tables: departments with id and name and employees with id, name, and department_id. Ensure that department_id in employees must exist in departments.

Query:

CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name TEXT
);

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);


since, employee must belong to valid department.. so we use FOREIGN KEY

QN: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

Query:

CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name TEXT
);

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);


since, we need automatic delete and update.. so we use CASCADE

Comments

Post a Comment

Popular posts from this blog

THE DELIVERY MAN

  The Delivery Man – Python List Operations  Introduction Lists in Python are used to store multiple items in a single variable. They are flexible, allowing us to add, remove, update, and manipulate elements easily. In this task, different list operations are performed to simulate a delivery man's workflow. 1. Creating a list and accessing an item items = ["Notebook", "Pencil", "Eraser", "Ruler", "Marker"] print(items[2]) Approach: Indexing Why: Lists use zero-based indexing, so the third item is accessed using index 2. 2. Adding a new item items.append("Glue Stick") print(items) Approach: append() method Why: It adds an item at the end of the list, which is ideal for adding new deliveries. 3. Inserting an item items.insert(2, "Highlighter") print(items) Approach: insert() method Why: Used when we need to add an item at a specific position instead of the end. 4. Removing an item items.remove("Ruler...
Read more

EC2 LAUNCHING

  EC2 Step 1: Launch EC2 Instance Go to: AWS Console --> EC2 --> Launch Instance Configure: Name : my-web-server AMI : Amazon Linux 2 (easy for beginners) Instance Type : t2.micro (free tier) Step 2: Key Pair Create or select a key pair. Step 3: Network Settings  Allow these: SSH (port 22) --> for login  HTTP (port 80) --> for web access Step 4: Launch Instance Click Launch Instance Step 5: Connect to Instance Use: EC2 --> Connect --> EC2 Instance Connect (easiest) OR via terminal: with this command. for this you have to download the pem files while selecting the key pair.  ssh -i your-key.pem ec2-user@<public-ip> Step 6: Install Web Server For Amazon Linux: sudo yum update -y sudo yum install httpd -y Step 7: Start Web Server sudo systemctl start httpd sudo systemctl enable httpd Step 8: Add a Web Page echo "Hello from EC2" | sudo tee /var/www/html/index.html Step 9: Get Public IP Go to EC2 d...
Read more

SORT A LINKED LIST USING MERGE SORT

  Sort a Linked List using Merge Sort Introduction Sorting a linked list efficiently is a common problem in data structures. Unlike arrays, linked lists do not allow direct access to elements, so algorithms like quicksort are not ideal. Merge Sort is the best choice for linked lists because: It does not require random access It works efficiently with node-based structures Problem Statement Given the head of a linked list: Sort the list in ascending order Return the sorted linked list Example Input: 10 -> 30 -> 20 -> 40 -> 60 -> 50 Output: 10 -> 20 -> 30 -> 40 -> 50 -> 60 Solution Code class Solution: def mergeSort(self, head): if not head or not head.next: return head mid = self.getMiddle(head) next_to_mid = mid.next mid.next = None left = self.mergeSort(head) right = self.mergeSort(next_to_mid) return self.merge(left, right) def getMiddle(self,...
Read more