Database System Concepts
Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
Bartleby Related Questions Icon

Related questions

Question

SQL code for: 

Create a stored procedure “setRelocationFee” to set the relocation fee for a given
employee. If the employee’s office is in San Francisco, the relocation fee is $10000; if
the employee’s office is in Boston, the relocation fee is $8000; if the employee’s office
is in London, the relocation fee is $20000; if the employee works in other offices, the
relocation fee is $15000.
Below is a sample statement to test your stored procedure.
set @employeeID = 1501;
call setRelocationFee(@employeeID, @relocationfee);
select @employeeID, @relocationfee;  

Having this database 

CREATE TABLE `offices` (

  `officeCode` varchar(10) NOT NULL,

  `city` varchar(50) NOT NULL,

  `phone` varchar(50) NOT NULL,

  `addressLine1` varchar(50) NOT NULL,

  `addressLine2` varchar(50) DEFAULT NULL,

  `state` varchar(50) DEFAULT NULL,

  `country` varchar(50) NOT NULL,

  `postalCode` varchar(15) NOT NULL,

  `territory` varchar(10) NOT NULL,

  PRIMARY KEY (`officeCode`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

CREATE TABLE `employees` (

  `employeeNumber` int(11) NOT NULL,

  `lastName` varchar(50) NOT NULL,

  `firstName` varchar(50) NOT NULL,

  `extension` varchar(10) NOT NULL,

  `email` varchar(100) NOT NULL,

  `officeCode` varchar(10) NOT NULL,

  `reportsTo` int(11) DEFAULT NULL,

  `jobTitle` varchar(50) NOT NULL,

  PRIMARY KEY (`employeeNumber`),

  KEY `reportsTo` (`reportsTo`),

  KEY `officeCode` (`officeCode`),

  CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`),

  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE 'offices' (
officeCode' varchar(10) NOT NULL,
`city' varchar(50) NOT NULL,
'phone varchar(50) NOT NULL,
`addressLine1' varchar(50) NOT NULL,
addressLine2` varchar(50) DEFAULT NULL,
'state varchar(50) DEFAULT NULL,
`country' varchar(50) NOT NULL,
`postalCode' varchar(15) NOT NULL,
'territory' varchar(10) NOT NULL,
PRIMARY KEY (`officeCode')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE 'employees' (
'employeeNumber' int(11) NOT NULL,
`lastName' varchar(50) NOT NULL,
'firstName' varchar(50) NOT NULL,
'extension varchar(10) NOT NULL,
`email` varchar(100) NOT NULL,
'officeCode' varchar(10) NOT NULL,
reportsTo` int(11) DEFAULT NULL,
`jobTitle` varchar(50) NOT NULL,
PRIMARY KEY ('employeeNumber"),
KEY 'reportsTo` (`reportsTo'),
KEY 'officeCode` (`officeCode'),
March 10, 2023 at 9:02 AM
CONSTRAINT `employees ibfk 2` FOREIGN KEY (`officeCode') REFERENCES offices` (`officeCode"),
CONSTRAINT 'employees ibfk 1 FOREIGN KEY (reports To`) REFERENCES employees` (`employeeNumber')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
expand button
Transcribed Image Text:CREATE TABLE 'offices' ( officeCode' varchar(10) NOT NULL, `city' varchar(50) NOT NULL, 'phone varchar(50) NOT NULL, `addressLine1' varchar(50) NOT NULL, addressLine2` varchar(50) DEFAULT NULL, 'state varchar(50) DEFAULT NULL, `country' varchar(50) NOT NULL, `postalCode' varchar(15) NOT NULL, 'territory' varchar(10) NOT NULL, PRIMARY KEY (`officeCode') ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE 'employees' ( 'employeeNumber' int(11) NOT NULL, `lastName' varchar(50) NOT NULL, 'firstName' varchar(50) NOT NULL, 'extension varchar(10) NOT NULL, `email` varchar(100) NOT NULL, 'officeCode' varchar(10) NOT NULL, reportsTo` int(11) DEFAULT NULL, `jobTitle` varchar(50) NOT NULL, PRIMARY KEY ('employeeNumber"), KEY 'reportsTo` (`reportsTo'), KEY 'officeCode` (`officeCode'), March 10, 2023 at 9:02 AM CONSTRAINT `employees ibfk 2` FOREIGN KEY (`officeCode') REFERENCES offices` (`officeCode"), CONSTRAINT 'employees ibfk 1 FOREIGN KEY (reports To`) REFERENCES employees` (`employeeNumber') ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Expert Solution
Check Mark
Knowledge Booster
Background pattern image
Computer Science
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Text book image
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Text book image
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
Text book image
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education