| 1 |
lars |
1 |
Apr 18 19:03:16 XML_Query2XML [info] QUERY: SELECT
|
|
|
2 |
s.*,
|
|
|
3 |
manager.employeeid AS manager_employeeid,
|
|
|
4 |
manager.employeename AS manager_employeename,
|
|
|
5 |
d.*,
|
|
|
6 |
department_head.employeeid AS department_head_employeeid,
|
|
|
7 |
department_head.employeename AS department_head_employeename,
|
|
|
8 |
e.*,
|
|
|
9 |
sa.*,
|
|
|
10 |
c.*,
|
|
|
11 |
al.*,
|
|
|
12 |
ar.*,
|
|
|
13 |
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
|
|
|
14 |
(SELECT
|
|
|
15 |
COUNT(*)
|
|
|
16 |
FROM
|
|
|
17 |
sale, employee, employee_department
|
|
|
18 |
WHERE
|
|
|
19 |
sale.employee_id = employee.employeeid
|
|
|
20 |
AND
|
|
|
21 |
employee_department.employee_id = employee.employeeid
|
|
|
22 |
AND
|
|
|
23 |
employee_department.department_id = d.departmentid
|
|
|
24 |
) AS department_sales,
|
|
|
25 |
(SELECT
|
|
|
26 |
COUNT(*)
|
|
|
27 |
FROM
|
|
|
28 |
employee, employee_department, department
|
|
|
29 |
WHERE
|
|
|
30 |
employee_department.employee_id = employee.employeeid
|
|
|
31 |
AND
|
|
|
32 |
employee_department.department_id = department.departmentid
|
|
|
33 |
AND
|
|
|
34 |
department.store_id = s.storeid
|
|
|
35 |
) AS store_employees,
|
|
|
36 |
(SELECT
|
|
|
37 |
COUNT(*)
|
|
|
38 |
FROM
|
|
|
39 |
employee, employee_department
|
|
|
40 |
WHERE
|
|
|
41 |
employee_department.employee_id = employee.employeeid
|
|
|
42 |
AND
|
|
|
43 |
employee_department.department_id = d.departmentid
|
|
|
44 |
) AS department_employees
|
|
|
45 |
FROM
|
|
|
46 |
store s
|
|
|
47 |
LEFT JOIN employee manager ON s.manager = manager.employeeid
|
|
|
48 |
LEFT JOIN department d ON d.store_id = s.storeid
|
|
|
49 |
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
|
|
|
50 |
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
|
|
|
51 |
LEFT JOIN employee e ON e.employeeid = ed.employee_id
|
|
|
52 |
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
|
|
|
53 |
LEFT JOIN customer c ON c.customerid = sa.customer_id
|
|
|
54 |
LEFT JOIN album al ON al.albumid = sa.album_id
|
|
|
55 |
LEFT JOIN artist ar ON ar.artistid = al.artist_id
|
|
|
56 |
ORDER BY
|
|
|
57 |
s.storeid,
|
|
|
58 |
manager.employeeid,
|
|
|
59 |
d.departmentid,
|
|
|
60 |
department_head.employeeid,
|
|
|
61 |
ed.employee_id,
|
|
|
62 |
ed.department_id,
|
|
|
63 |
e.employeeid,
|
|
|
64 |
sa.saleid,
|
|
|
65 |
c.customerid,
|
|
|
66 |
al.albumid,
|
|
|
67 |
ar.artistid
|
|
|
68 |
Apr 18 19:03:16 XML_Query2XML [info] DONE
|