Парни, помогите нужно для учёбы кое что )
Вот задание:
http://s45.radikal.ru/i109/1005/97/4763c08df784.jpg
Вот база данных:
Помогите )
Вот задание:
http://s45.radikal.ru/i109/1005/97/4763c08df784.jpg
Вот база данных:
- Код:
/*
MySQL Data Transfer
Source Host: localhost
Source Database: rubd
Target Host: localhost
Target Database: rubd
Date: 25.05.2010 22:03:25
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`customer_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) NOT NULL,
`address` varchar(45) NOT NULL,
`city` varchar(45) NOT NULL,
`state` varchar(45) NOT NULL,
`zip_code` int(10) unsigned NOT NULL,
`area_code` int(10) unsigned NOT NULL,
`phone_number` varchar(45) NOT NULL,
`salesperson_id` int(10) unsigned NOT NULL,
`credit_limit` varchar(45) NOT NULL,
`comments` varchar(45) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1501 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for depertment
-- ----------------------------
DROP TABLE IF EXISTS `depertment`;
CREATE TABLE `depertment` (
`deparment_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) NOT NULL,
`location_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`deparment_id`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`employee_id` int(10) unsigned NOT NULL auto_increment,
`last_name` varchar(45) NOT NULL,
`first_name` varchar(45) NOT NULL,
`middle_initial` varchar(45) NOT NULL,
`manager_id` int(10) unsigned NOT NULL,
`job_id` int(10) unsigned NOT NULL,
`hire_date` date NOT NULL,
`salary` varchar(45) NOT NULL,
`commission` int(10) unsigned NOT NULL,
`department_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM AUTO_INCREMENT=211 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for item
-- ----------------------------
DROP TABLE IF EXISTS `item`;
CREATE TABLE `item` (
`item_id` int(10) unsigned NOT NULL auto_increment,
`order_id` int(10) unsigned NOT NULL,
`product_id` int(10) unsigned NOT NULL,
`actual_price` int(10) unsigned NOT NULL,
`quantity` varchar(45) NOT NULL,
`total` int(10) unsigned NOT NULL,
PRIMARY KEY (`item_id`,`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=510 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for job
-- ----------------------------
DROP TABLE IF EXISTS `job`;
CREATE TABLE `job` (
`job_id` int(10) unsigned NOT NULL auto_increment,
`function` varchar(45) NOT NULL,
PRIMARY KEY (`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=671 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for location
-- ----------------------------
DROP TABLE IF EXISTS `location`;
CREATE TABLE `location` (
`location_id` int(10) unsigned NOT NULL auto_increment,
`regional_group` varchar(45) NOT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for price
-- ----------------------------
DROP TABLE IF EXISTS `price`;
CREATE TABLE `price` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`list_price` int(10) unsigned NOT NULL,
`min_price` int(10) unsigned NOT NULL,
PRIMARY KEY (`product_id`,`start_date`)
) ENGINE=MyISAM AUTO_INCREMENT=219 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`description` varchar(45) NOT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=210 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for sales_order
-- ----------------------------
DROP TABLE IF EXISTS `sales_order`;
CREATE TABLE `sales_order` (
`order_id` int(10) unsigned NOT NULL auto_increment,
`order_date` date NOT NULL,
`customer_id` int(10) unsigned NOT NULL,
`ship_date` date NOT NULL,
`total` int(10) unsigned NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=146 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `customer` VALUES ('1050', 'vasia pupkin', 'usa', 'new york', 'new york', '350222', '54644', '8797984', '110', '50000', 'sdfa');
INSERT INTO `customer` VALUES ('1100', 'petya nitochkin', 'usa', 'new castle', 'verjinia', '157775', '4578', '456456', '120', '30000', 'hsrgh');
INSERT INTO `customer` VALUES ('1150', 'lexa lexa', 'usa', 'chikago', 'marevel', '243422', '7877', '2384723', '130', '40000', 'ksdgjh');
INSERT INTO `customer` VALUES ('1200', 'momo lic he', 'russia', 'rim', 'zadrip', '343423', '4764', '4565644', '140', '100000', 'vdjhvgfkgh');
INSERT INTO `customer` VALUES ('1250', 'necro ment', 'uar', 'Piter', 'dede', '343423', '1233', '8656456', '150', '150000', 'bbhfee');
INSERT INTO `customer` VALUES ('1300', 'sasha moxhach', 'ukraina', 'kiev', 'fik', '464464', '3332', '3976789', '160', '1000', 'wdggsw');
INSERT INTO `customer` VALUES ('1350', 'ivan pupkin', 'russia', 'Piter', 'podgorod', '353535', '1278', '8737837', '170', '150505', 'sdlgfsddfksg');
INSERT INTO `customer` VALUES ('1400', 'tata zaza', 'nigeria', 'viborg', 'zemla', '2564473', '3457', '76437645', '180', '450000', 'sdjkgsd');
INSERT INTO `customer` VALUES ('1450', 'dik advokat', 'tushkanchik', 'rim', 'color', '23443', '2434', '43434432', '190', '3455337', 'sdgfjksdf');
INSERT INTO `customer` VALUES ('1500', 'gus hidding', 'forma', 'froma', 'ferg', '34547', '3457', '374576', '200', '234834', 'jsdhgfdhgf');
INSERT INTO `depertment` VALUES ('20', 'lenta', '10');
INSERT INTO `depertment` VALUES ('21', 'key', '11');
INSERT INTO `depertment` VALUES ('22', 'km', '12');
INSERT INTO `depertment` VALUES ('23', 'o key', '14');
INSERT INTO `depertment` VALUES ('24', 'zimbabva', '13');
INSERT INTO `depertment` VALUES ('25', 'ikea', '15');
INSERT INTO `depertment` VALUES ('26', 'maxidom', '16');
INSERT INTO `depertment` VALUES ('27', 'domovoi', '17');
INSERT INTO `depertment` VALUES ('28', 'D&C', '18');
INSERT INTO `depertment` VALUES ('29', 'Russia', '19');
INSERT INTO `depertment` VALUES ('30', 'paterson', '20');
INSERT INTO `employee` VALUES ('110', 'petrov', 'peta', '200', '0', '1', '2005-12-12', '5000', '150', '20');
INSERT INTO `employee` VALUES ('120', 'sidorov', 'sasha', '300', '110', '3', '2006-07-17', '1250', '200', '21');
INSERT INTO `employee` VALUES ('130', 'ivanova', 'masha', '400', '150', '3', '2007-04-19', '1500', '250', '22');
INSERT INTO `employee` VALUES ('140', 'pavlov', 'lesha', '500', '110', '670', '2005-06-24', '850', '300', '23');
INSERT INTO `employee` VALUES ('150', 'mihailova', 'nastia', '600', '0', '670', '2000-07-28', '1000', '350', '24');
INSERT INTO `employee` VALUES ('170', 'lechenkov', 'aleksei', '800', '270', '7', '2001-07-12', '15000', '450', '26');
INSERT INTO `employee` VALUES ('180', 'maslov', 'evgenyi', '900', '110', '8', '2005-01-31', '1700', '500', '27');
INSERT INTO `employee` VALUES ('190', 'bogdanov ', 'artem', '1000', '290', '9', '2006-06-28', '700', '550', '28');
INSERT INTO `employee` VALUES ('200', 'lemenovskyi', 'sergei', '1100', '300', '10', '2007-08-08', '7000', '600', '29');
INSERT INTO `employee` VALUES ('160', 'pozdnakov', 'dmitriy', '700', '150', '670', '2002-04-20', '10000', '400', '25');
INSERT INTO `employee` VALUES ('210', 'terentev', 'maksim', '800', '150', '3', '1988-04-30', '500', '100', '30');
INSERT INTO `item` VALUES ('500', '100', '200', '2500', '3000', '5500');
INSERT INTO `item` VALUES ('501', '110', '202', '100', '20', '120');
INSERT INTO `item` VALUES ('503', '105', '203', '205', '25', '230');
INSERT INTO `item` VALUES ('502', '115', '201', '3000', '3050', '6050');
INSERT INTO `item` VALUES ('504', '120', '204', '500', '100', '600');
INSERT INTO `item` VALUES ('505', '125', '205', '3200', '1500', '4566');
INSERT INTO `item` VALUES ('506', '130', '206', '4000', '180', '300');
INSERT INTO `item` VALUES ('507', '135', '207', '300', '500', '1200');
INSERT INTO `item` VALUES ('508', '140', '208', '450', '20', '3566');
INSERT INTO `item` VALUES ('509', '145', '209', '4444', '150', '5445');
INSERT INTO `job` VALUES ('8', 'obslugivanie');
INSERT INTO `job` VALUES ('7', 'lechenie');
INSERT INTO `job` VALUES ('6', 'straxovanie');
INSERT INTO `job` VALUES ('1', 'zakupka');
INSERT INTO `job` VALUES ('2', 'prodaga');
INSERT INTO `job` VALUES ('3', 'clerk');
INSERT INTO `job` VALUES ('4', 'dostavka');
INSERT INTO `job` VALUES ('5', 'logistika');
INSERT INTO `job` VALUES ('9', 'testirovanie');
INSERT INTO `job` VALUES ('10', 'prognozirovanie');
INSERT INTO `job` VALUES ('670', 'ekskavatorshik');
INSERT INTO `location` VALUES ('10', 'omsk');
INSERT INTO `location` VALUES ('11', 'viborg');
INSERT INTO `location` VALUES ('12', 'london');
INSERT INTO `location` VALUES ('13', 'Piter');
INSERT INTO `location` VALUES ('14', 'usa');
INSERT INTO `location` VALUES ('15', 'kanada');
INSERT INTO `location` VALUES ('16', 'meksika');
INSERT INTO `location` VALUES ('17', 'kazahstan');
INSERT INTO `location` VALUES ('18', 'mongolia');
INSERT INTO `location` VALUES ('19', 'rim');
INSERT INTO `location` VALUES ('20', 'dallas');
INSERT INTO `price` VALUES ('201', '2006-07-31', '2007-05-31', '2500', '2850');
INSERT INTO `price` VALUES ('202', '2006-06-07', '2006-08-31', '2800', '3100');
INSERT INTO `price` VALUES ('203', '2006-05-01', '2006-05-31', '1000', '1260');
INSERT INTO `price` VALUES ('204', '1987-09-02', '2007-02-05', '100', '150');
INSERT INTO `price` VALUES ('205', '2006-06-01', '2006-06-28', '3000', '3500');
INSERT INTO `price` VALUES ('210', '2007-01-28', '2007-08-26', '1000', '2000');
INSERT INTO `price` VALUES ('209', '2004-09-14', '2006-06-13', '550', '1000');
INSERT INTO `price` VALUES ('208', '1988-08-28', '2001-01-31', '300', '500');
INSERT INTO `price` VALUES ('207', '2004-05-14', '2006-06-15', '4500', '5000');
INSERT INTO `price` VALUES ('206', '2005-01-01', '2007-05-12', '1500', '4500');
INSERT INTO `product` VALUES ('201', 'tyaletnaya_bymaga');
INSERT INTO `product` VALUES ('200', 'pivo');
INSERT INTO `product` VALUES ('202', 'tea');
INSERT INTO `product` VALUES ('203', 'bananas');
INSERT INTO `product` VALUES ('204', 'icecream');
INSERT INTO `product` VALUES ('205', 'rom');
INSERT INTO `product` VALUES ('206', 'cha-cha');
INSERT INTO `product` VALUES ('207', 'les');
INSERT INTO `product` VALUES ('208', 'krem');
INSERT INTO `product` VALUES ('209', 'vodka');
INSERT INTO `sales_order` VALUES ('100', '2006-12-25', '1000', '2007-10-30', '1500');
INSERT INTO `sales_order` VALUES ('105', '2005-11-15', '1050', '2006-02-02', '2000');
INSERT INTO `sales_order` VALUES ('110', '2007-10-18', '1100', '2007-11-03', '2500');
INSERT INTO `sales_order` VALUES ('115', '2005-12-30', '1150', '2006-03-17', '3000');
INSERT INTO `sales_order` VALUES ('120', '2006-03-03', '1200', '2007-06-18', '3500');
INSERT INTO `sales_order` VALUES ('125', '2005-04-16', '1250', '2006-07-16', '4000');
INSERT INTO `sales_order` VALUES ('130', '2006-01-18', '1300', '2007-08-13', '4500');
INSERT INTO `sales_order` VALUES ('135', '2004-02-06', '1350', '2008-09-17', '5000');
INSERT INTO `sales_order` VALUES ('140', '2006-03-28', '1400', '2007-10-20', '5500');
INSERT INTO `sales_order` VALUES ('145', '2005-09-30', '1450', '2007-12-29', '6000');
Помогите )