Database SQL query

saugust-CompanyDB.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -------------------------------------------------------- -- -- Table structure for table `company` -- CREATE TABLE IF NOT EXISTS `company` ( `company_name` varchar(30) NOT NULL DEFAULT '', `city` varchar(20) DEFAULT NULL, PRIMARY KEY (`company_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `company` -- INSERT INTO `company` (`company_name`, `city`) VALUES ('Cathedral Software', 'San Francisco'), ('First Bank Corporation', 'Brooklyn'), ('IBM', 'San Jose'), ('Integrated Data Systems', 'Westchester'), ('Multnomah Software', 'Westchester'), ('Small Bank Corporation', 'Palo Alto'), ('Sneakers', 'Chicago'); -- -------------------------------------------------------- -- -- Table structure for table `company2` -- CREATE TABLE IF NOT EXISTS `company2` ( `company_name` varchar(30) NOT NULL DEFAULT '', `city` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`company_name`,`city`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `company2` -- INSERT INTO `company2` (`company_name`, `city`) VALUES ('August Software', 'Westchester'), ('Cathedral Software', 'Palo Alto'), ('Cathedral Software', 'San Francisco'), ('Cathedral Software', 'Santa Clara'), ('First Bank Corporation', 'Brighton'), ('First Bank Corporation', 'Brooklyn'), ('IBM', 'San Jose'), ('IBM', 'Yorktown Heights'), ('Integrated Data Systems', 'Westchester'), ('Small Bank Corporation', 'Palo Alto'), ('Small Bank Corporation', 'Santa Clara'); -- -------------------------------------------------------- -- -- Table structure for table `employee` -- CREATE TABLE IF NOT EXISTS `employee` ( `employee_name` varchar(20) NOT NULL DEFAULT '', `street` varchar(30) DEFAULT NULL, `city` varchar(20) DEFAULT NULL, PRIMARY KEY (`employee_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `employee` -- INSERT INTO `employee` (`employee_name`, `street`, `city`) VALUES ('Adams', 'Nassau', 'Princeton'), ('August', 'Riggs', 'Westchester'), ('Brooks', 'North', 'San Jose'), ('Curry', 'North', 'San Jose'), ('Ferris', 'Loyola', 'Westchester'), ('Glenn', 'Park', 'Pittsfield'), ('Green', 'Walnut', 'Stamford'), ('Hayes', 'Main', 'San Francisco'), ('Johnson', 'Alma', 'Palo Alto'), ('Jones', 'Main', 'San Jose'), ('Laikin', 'Berryman', 'Culver City'), ('Lindsay', 'Park', 'Pittsfield'), ('Moira', 'Nancy', 'Kentwood'), ('Schmidt', 'Nardian', 'Los Angeles'), ('Smith', 'North', 'Westchester'), ('Turner', 'Alma', 'Palo Alto'), ('Williams', 'Nassau', 'Princeton'), ('Young', 'Loyola', 'Westchester'); -- -------------------------------------------------------- -- -- Table structure for table `employee2` -- CREATE TABLE IF NOT EXISTS `employee2` ( `employee_name` varchar(20) NOT NULL DEFAULT '', `street` varchar(30) DEFAULT NULL, `city` varchar(20) DEFAULT NULL, PRIMARY KEY (`employee_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `employee2` -- INSERT INTO `employee2` (`employee_name`, `street`, `city`) VALUES ('Adams', 'Nassau', 'Princeton'), ('August', 'Riggs', 'Westchester'), ('Brooks', 'North', 'San Jose'), ('Cox', 'National', 'Rockville'), ('Curry', 'North', 'San Jose'), ('Draskovic', 'Faraday', 'Reston'), ('Ferris', 'Loyola', 'Westchester'), ('Glenn', 'Park', 'Pittsfield'), ('Green', 'Walnut', 'Stamford'), ('Hayes', 'Main', 'San Francisco'), ('Johnson', 'Alma', 'Palo Alto'), ('Jones', 'Main', 'San Jose'), ('Kent', '80th', 'Culver City'), ('Laikin', 'Berryman', 'Culver City'), ('Lindsay', 'Park', 'Pittsfield'), ('Malcolm', 'Paddock', 'Jurassic Part'), ('Moira', 'Nancy', 'Kentwood'), ('Price', 'Library', 'Reston'), ('Ryan', 'Pennsylvania', 'District of Columbia'), ('Schmidt', 'Nardian', 'Los Angeles'), ('Smith', 'North', 'Westchester'), ('Turner', 'Alma', 'Palo Alto'), ('Williams', 'Nassau', 'Princeton'), ('Woody', 'Emerson', 'Culver City'), ('Young', 'Loyola', 'Westchester'); -- -------------------------------------------------------- -- -- Table structure for table `manages` -- CREATE TABLE IF NOT EXISTS `manages` ( `employee_name` varchar(20) NOT NULL DEFAULT '', `manager_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`employee_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Add foreign key constraint -- ALTER TABLE manages ADD CONSTRAINT manages_employee_name_FK FOREIGN KEY (employee_name) REFERENCES employee(employee_name) ; ALTER TABLE manages ADD CONSTRAINT manages_manager_name_FK FOREIGN KEY (manager_name) REFERENCES employee(employee_name) ; -- -- Dumping data for table `manages` -- INSERT INTO `manages` (`employee_name`, `manager_name`) VALUES ('Adams', 'Ferris'), ('Curry', 'Brooks'), ('Glenn', 'Green'), ('Johnson', 'Moira'), ('Jones', 'Brooks'), ('Laikin', 'Adams'), ('Lindsay', 'Glenn'), ('Smith', 'August'), ('Turner', 'Johnson'), ('Williams', 'Adams'), ('Young', 'Glenn'); -- -------------------------------------------------------- -- -- Table structure for table `works` -- CREATE TABLE IF NOT EXISTS `works` ( `employee_name` varchar(20) NOT NULL DEFAULT '', `company_name` varchar(30) DEFAULT NULL, `salary` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`employee_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Add foreign key constraint -- ALTER TABLE works ADD CONSTRAINT works_employee_name_FK FOREIGN KEY (employee_name) REFERENCES employee(employee_name) ; ALTER TABLE works ADD CONSTRAINT works_company_name_FK FOREIGN KEY (company_name) REFERENCES company(company_name) ; -- -- Dumping data for table `works` -- INSERT INTO `works` (`employee_name`, `company_name`, `salary`) VALUES ('Adams', 'Sneakers', 90000.00), ('August', 'Multnomah Software', 150000.00), ('Brooks', 'IBM', 85000.00), ('Curry', 'IBM', 70000.00), ('Ferris', 'First Bank Corporation', 100000.00), ('Glenn', 'Small Bank Corporation', 102000.00), ('Green', 'Small Bank Corporation', 110000.00), ('Hayes', 'Cathedral Software', 60000.00), ('Johnson', 'First Bank Corporation', 99000.00), ('Jones', 'IBM', 75000.00), ('Laikin', 'First Bank Corporation', 91000.00), ('Lindsay', 'Small Bank Corporation', 60000.00), ('Moira', 'First Bank Corporation', 17000.00), ('Schmidt', 'Integrated Data Systems', 120000.00), ('Smith', 'Multnomah Software', 50000.00), ('Turner', 'First Bank Corporation', 30000.00), ('Williams', 'Sneakers', 67000.00), ('Young', 'Small Bank Corporation', 57000.00); -- -------------------------------------------------------- -- -- Table structure for table `works2` -- CREATE TABLE IF NOT EXISTS `works2` ( `employee_name` varchar(20) NOT NULL DEFAULT '', `company_name` varchar(30) NOT NULL DEFAULT '', `city` varchar(20) NOT NULL DEFAULT '', `salary` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`employee_name`,`company_name`,`city`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Add foreign key constraint -- ALTER TABLE works2 ADD CONSTRAINT works2_employee_name_FK FOREIGN KEY (employee_name) REFERENCES employee(employee_name) ; ALTER TABLE works2 ADD CONSTRAINT works2_company_name_FK FOREIGN KEY (company_name) REFERENCES company(company_name) ; -- -- Dumping data for table `works2` -- INSERT INTO `works2` (`employee_name`, `company_name`, `city`, `salary`) VALUES ('Adams', 'Sneakers', 'Chicago', 90000.00), ('August', 'Multnomah Software', 'Westchester', 150000.00), ('Brooks', 'IBM', 'San Jose', 85000.00), ('Curry', 'IBM', 'San Jose', 70000.00), ('Ferris', 'First Bank Corporation', 'Brooklyn', 100000.00), ('Glenn', 'Small Bank Corporation', 'Palo Alto', 102000.00), ('Green', 'Small Bank Corporation', 'Palo Alto', 110000.00), ('Hayes', 'Cathedral Software', 'San Francisco', 60000.00), ('Johnson', 'First Bank Corporation', 'Brooklyn', 99000.00), ('Jones', 'IBM', 'San Jose', 75000.00), ('Laikin', 'First Bank Corporation', 'Brooklyn', 91000.00), ('Lindsay', 'Small Bank Corporation', 'Palo Alto', 60000.00), ('Moira', 'First Bank Corporation', 'Brooklyn', 17000.00), ('Moira', 'Multnomah Software', 'Westchester', 67000.00), ('Schmidt', 'Integrated Data Systems', 'Westchester', 120000.00), ('Smith', 'Multnomah Software', 'Westchester', 50000.00), ('Turner', 'First Bank Corporation', 'Brooklyn', 30000.00), ('Williams', 'Sneakers', 'Chicago', 67000.00), ('Young', 'Small Bank Corporation', 'Palo Alto', 57000.00); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;