SQLize Online / PHPize Online  /  SQLtest Online

create table l_employees (employee_id varchar(50) not null, first_name varchar(50), last_name varchar(50), dept_code varchar(50), hire_date date, credit_limit decimal(10,2), phone_number varchar(50), manager_id varchar(50), constraint l_employees_pk primary key (employee_id)); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('201', 'Susan', 'Brown', 'Exe', '06/01/1992', 30.00, '3484', ''); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('202', 'Jim', 'Kern', 'Sal', '08/15/1995', 25.00, '8722', '201'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('203', 'Martha', 'Woods', 'Shp', '02/01/1997', 25.00, '7591', '201'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('204', 'Ellen', 'Owens', 'Sal', '07/01/1996', 15.00, '6830', '202'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('205', 'Henry', 'Perkins', 'Sal','03/01/1998', 25.00, '5286', '202'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('206', 'Carol', 'Rose', 'Act', '10/15/1997', 15.00, '3829', '201'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('207', 'Dan', 'Smith', 'Shp', '12/01/1996', 25.00, '2259', '203'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('208', 'Fred', 'Campbell', 'Shp', '04/01/1997', 25.00, '1752', '203'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('209', 'Paula', 'Jacobs', 'Mkt', '03/17/1998', 15.00, '3357', '201'); insert into l_employees (employee_id , first_name , last_name , dept_code , hire_date , credit_limit , phone_number , manager_id ) values ('210', 'Nancy', 'Hoffman', 'Sal', '02/15/1996', 25.00, '2974', '203'); create table l_foods (supplier_id varchar(50), product_code varchar(50), menu_item int, description varchar(50), price decimal(10,2), price_increase varchar(20), constraint l_foods_pk primary key (supplier_id, product_code)); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('ASP','FS', 1, 'FRESH SALAD', 2.00, '$0.25'); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('ASP','SP', 2, 'SOUP OF THE DAY', 1.50,''); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('ASP','SW', 3, 'SANDWICH', 3.50, '$0.40'); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('CBC','GS', 4, 'GRILLED STEAK', 6.00, '$0.70'); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('CBC','SW', 5, 'HAMBURGER', 2.00, '$0.30'); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('FRV','BR', 6, 'BROCCOLI', 1.00, '$0.05'); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('FRV','FF', 7, 'FRESH FRIES', 1.50, ''); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('JBR','AS', 8, 'SODA', 1.25, '$0.25'); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('JBR','VR', 9, 'COFFEE', 0.85, '$0.15'); insert into l_foods (supplier_id, product_code, menu_item, description, price, price_increase) values('VSB','AS', 10, 'DESERT', 3.00, '$0.50'); create table l_lunch_items (lunch_id int, item_number int, supplier_id varchar(50), product_code varchar(50), quantity int); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(1,1, 'ASP', 'FS', 1); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(1,2, 'ASP', 'SW', 2); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(1,3, 'JBR', 'VR', 2); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(2,1, 'ASP', 'SW', 2); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(2,2, 'FRV', 'FF', 1); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(2,3, 'JBR', 'VR', 2); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(2,4, 'VSB', 'AS', 1); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(3,1, 'ASP', 'FS', 1); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(3,2, 'CBC', 'GS', 1); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(3,3, 'FRV', 'FF', 1); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(3,4, 'JBR', 'VR', 1); insert into l_lunch_items (lunch_id, item_number, supplier_id, product_code, quantity) values(3,5, 'JBR', 'AS', 1); create table l_lunches (lunch_id int not null, lunch_date date, employee_id varchar(10), date_entere date, constraint l_lunches_pk primary key (lunch_id)); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (1,'16-NOV-2011', '201', '13-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (2,'16-NOV-2011', '207', '13-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (3, '16-NOV-2011', '203', '13-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (4, '16-NOV-2011', '204', '13-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (6, '16-NOV-2011', '202', '13-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (7, '16-NOV-2011', '210', '13-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (8, '25-NOV-2011', '201', '14-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (9, '25-NOV-2011', '208', '14-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (12, '25-NOV-2011', '204', '14-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (13,'25-NOV-2011', '207', '18-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (15,'25-NOV-2011', '205', '21-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (16, '05-DEC-2011', '201', '21-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (17, '05-DEC-2011', '210', '21-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (20, '05-DEC-2011', '205', '24-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (21, '05-DEC-2011', '203', '24-OCT-2011'); insert into l_lunches (lunch_id,lunch_date,employee_id,date_entere) values (22,'05-DEC-2011', '208', '24-OCT-2011'); select a.employee_id, a.first_name, a.last_name,sum(c.quantity* d.price) as TOTAL from l_employees a join l_lunches b on a.employee_id = b.employee_id join l_lunch_items c on b.lunch_id = c.lunch_id join l_foods d on c.supplier_id = d.supplier_id and c.product_code= d.product_code group by a.employee_id, a.first_name, a.last_name order by a.employee_id;