Sunday, April 14, 2019

Stored Procedure In Mysql

1. How to create stored procedure in mysql ?

CREATE DATABASE javaiq_db;

CREATE TABLE company (
  company_id int PRIMARY KEY NOT NULL AUTO_INCREMENT ,
  name TEXT NOT NULL
);

CREATE TABLE profit (
  profit_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name TEXT,
  company_id int,
  CONSTRAINT fk_company  FOREIGN KEY (company_id)
 REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee (
  employee_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  first_name TEXT,
  last_name TEXT,
  company_id int,
  CONSTRAINT fk_companys FOREIGN KEY (company_id)
    REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee_profit (
  employee_id int,
  profit_id int,
  CONSTRAINT fk_employee FOREIGN KEY (employee_id)
  REFERENCES employee (employee_id) MATCH SIMPLE ,
  CONSTRAINT fk_profit FOREIGN KEY (profit_id)
  REFERENCES profit (profit_id) MATCH SIMPLE
);

INSERT INTO company (company_id,name) VALUES (1,'JavaIQ');
INSERT INTO employee (employee_id,first_name, last_name, company_id) VALUES (1,'Shubh','Gupta',1);
INSERT INTO profit (profit_id,name,company_id) VALUES (1,'Food',1);
INSERT INTO profit (profit_id,name,company_id) VALUES (2,'Breakfast',1);

Related Tutorials

No comments:

Post a Comment