practical 10.sql

USE collgePr1;


























CREATE TABLE employee_24012011080(
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept VARCHAR(30),
    salary INT
);

CREATE INDEX idx_dept ON employee_24012011080(dept);

SELECT * FROM employee_24012011080 WHERE dept = 'IT';


CREATE TABLE order_info_24012011080 (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    order_amount DECIMAL(10,2),
    order_date DATE
);
INSERT INTO order_info_24012011080 VALUES
(1, 'Rakesh', 2500, '2024-01-12'),
(2, 'Amit', 8000, '2024-01-13'),
(3, 'Dev', 12000, '2024-01-15'),
(4, 'Niraj', 5000, '2024-01-17'),
(5, 'Yash', 3000, '2024-01-18');
SELECT * FROM order_info_24012011080;

CREATE INDEX idx_order
ON order_info_24012011080(order_amount);

SHOW INDEXES FROM order_info_24012011080;


CREATE TABLE order_no_seq (
    seq_id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(50)
);
INSERT INTO order_no_seq (description) VALUES ('First');
INSERT INTO order_no_seq (description) VALUES ('Second');
INSERT INTO order_no_seq (description) VALUES ('Third');
SELECT * FROM order_no_seq;




-- create the table that receives the generated seq_id
DROP TABLE IF EXISTS order_no_seq;

CREATE TABLE order_no_seq_24012011080 (
  seq_id INT NOT NULL PRIMARY KEY,
  description VARCHAR(100)
);

-- create the table that holds the current sequence value
DROP TABLE IF EXISTS order_seq_24012011080;

CREATE TABLE order_seq_24012011080 (
  seq_value INT NOT NULL
);
INSERT INTO order_seq_24012011080 (seq_value) VALUES (1);



DROP TRIGGER IF EXISTS trg_order_seq;

DELIMITER $$
CREATE TRIGGER trg_order_seq
BEFORE INSERT ON order_no_seq_24012011080
FOR EACH ROW
BEGIN
    -- increment by 3
    UPDATE order_seq_24012011080
    SET seq_value = seq_value + 3;

    -- assign the new sequence value to the new row
    SET NEW.seq_id = (SELECT seq_value FROM order_seq_24012011080 LIMIT 1);
END$$
DELIMITER ;


INSERT INTO order_no_seq_24012011080 (description) VALUES ('Order 1');
INSERT INTO order_no_seq_24012011080 (description) VALUES ('Order 2');
INSERT INTO order_no_seq_24012011080 (description) VALUES ('Order 3');

SELECT * FROM order_no_seq_24012011080;





SELECT seq_value AS current_value
FROM order_seq_24012011080;


SELECT seq_value + 3 AS next_value
FROM order_seq_24012011080;



DROP TABLE IF EXISTS order1_24012011080;

CREATE TABLE order1_24012011080 (
    onum INT PRIMARY KEY,
    odate DATE
);



DROP TRIGGER IF EXISTS trg_order1_seq;

DELIMITER $$
CREATE TRIGGER trg_order1_seq
BEFORE INSERT ON order1_24012011080
FOR EACH ROW
BEGIN
    -- increment sequence by 3
    UPDATE order_seq_24012011080
    SET seq_value = seq_value + 3;

    -- set onum = new sequence value
    SET NEW.onum = (SELECT seq_value FROM order_seq_24012011080 LIMIT 1);

    -- set order date = system date
    SET NEW.odate = CURDATE();
END$$
DELIMITER ;



INSERT INTO order1_24012011080 VALUES (NULL, NULL);
INSERT INTO order1_24012011080 VALUES (NULL, NULL);
INSERT INTO order1_24012011080 VALUES (NULL, NULL);

SELECT * FROM order1_24012011080;



DROP TABLE order_seq_24012011080;



DROP INDEX idx_order ON order_info_24012011080;



DROP TABLE IF EXISTS employee_demo;

CREATE TABLE employee_demo_24012011080 (
    emp_no INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT
);
INSERT INTO employee_demo_24012011080 VALUES
(101, 'Rakesh', 25000),
(102, 'Amit', 40000),
(103, 'Dilip', 55000),
(104, 'Yash', 28000),
(105, 'Dev', 60000);


CREATE VIEW v_emp AS
SELECT emp_no, name, salary
FROM employee_demo_24012011080
WHERE salary > 30000;

SELECT * FROM v_emp;


CREATE TABLE IF NOT EXISTS order_info_24012011080 (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    order_amount DECIMAL(10,2),
    order_date DATE
);


CREATE VIEW vw_order AS
SELECT order_date, order_amount
FROM order_info_24012011080;

SELECT * FROM vw_order;



DROP TABLE IF EXISTS customer_24012011080;

CREATE TABLE customer_24012011080 (
    cust_no INT PRIMARY KEY,
    cust_name VARCHAR(50),
    city VARCHAR(50),
    phone VARCHAR(20)
);
INSERT INTO customer_24012011080 VALUES
(1, 'Rakesh', 'Surat', '9876543210'),
(2, 'Amit', 'Vadodara', '9998887776'),
(3, 'Dev', 'Rajkot', '9123456789'),
(4, 'Neha', 'Surat', '9988776655');


CREATE VIEW vw_cust AS
SELECT cust_no, cust_name, city
FROM customer_24012011080;

SELECT * FROM vw_cust;




CREATE VIEW vw_cust AS
SELECT cust_no, cust_name, city
FROM customer_24012011080;



CREATE VIEW vw_order1 AS
SELECT order_date, order_amount
FROM order_info_24012011080;

INSERT INTO vw_order1 VALUES ('2021-09-21', 6000);