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