lab work 2.2.txt

QUESTION :- 1

CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(50),
    products VARCHAR(100),
    order_date DATE
);

INSERT INTO orders (order_id, customer_name, products, order_date) VALUES
(1, 'Alice', 'Laptop, Mouse, Keyboard', '2026-01-29'),
(2, 'Bob', 'Monitor, HDMI , Cable', '2026-01-29'),
(3, 'Charlie', 'USB , Drive, Headphones', '2026-01-29');

CREATE TABLE order_details(
    order_id INT,
    product_name VARCHAR(50)
);

INSERT INTO order_details (order_id,product_name) VALUES (101,Laptop),
(102,'Mouse'),
(103,'Keyboard'),
(104,'CPU'),
(105,'mobile');

QUESTION :- 2

CREATE TABLE student_courses (
    student_id INT,
    student_name VARCHAR(50),
    course_id INT,
    course_name VARCHAR(50),
    PRIMARY KEY (student_id, course_id) 
);

INSERT INTO student_courses (student_id, student_name, course_id, course_name) VALUES
(1, 'Alice', 101, 'Math'),
(1, 'Alice', 102, 'Physics'),
(2, 'Bob', 101, 'Math'),
(2, 'Bob', 103, 'Chemistry');

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);


CREATE TABLE student_course_mapping (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

QUESTION :- 3

employees table

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(50),
    department_id INT,
    department_name VARCHAR(50),
    manager_name VARCHAR(50)
);

INSERT INTO employees VALUES
(1,'Ali',10,'IT','Ahmed'),
(2,'Sara',20,'HR','John');


employees table

CREATE TABLE employees_new (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    department_id INT
);

INSERT INTO employees_new VALUES
(1,'Ali',10),
(2,'Sara',20);

departments table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    manager_name VARCHAR(50)
);


INSERT INTO departments VALUES
(10,'IT','Ahmed'),
(20,'HR','John');



QUESTION :- 4

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

INSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 1200.00);
INSERT INTO products (product_id, product_name, price) VALUES (2, 'Mouse', 25.50);
INSERT INTO products (product_id, product_name, price) VALUES (3, 'Keyboard', 49.99);

INSERT INTO products (product_id, product_name, price) VALUES (1, 'Monitor', 250.00);

QUESTION :- 5

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO customers (customer_id, customer_name, email) VALUES (1, 'Alice Smith', 'alice.smith@example.com');
INSERT INTO customers (customer_id, customer_name, email) VALUES (2, 'Bob Johnson', 'bob.johnson@example.com');
INSERT INTO customers (customer_id, customer_name, email) VALUES (3, 'Charlie Brown', 'charlie.brown@example.com');

QUESTION :- 6

CREATE TABLE orders_new (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO orders_new VALUES
(1,1,1,'2024-01-10',2),
(2,2,2,'2024-01-11',5),
(3,3,3,'2024-01-12',1);

Invalid insert (ERROR)
INSERT INTO orders_new VALUES
(4,10,1,'2024-01-13',1);

QUESTION :- 7

SELECT o.order_id, c.customer_name, p.product_name, o.quantity
FROM orders_new o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

QUESTION :- 8

CREATE TABLE orders_cascade (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE CASCADE
);


DELETE FROM customers WHERE customer_id = 1;