Embedded Files
Views are virtual tables, presenting data collected and combined from one or more tables in a format tailored to specific needs and queries. As we step through this section, we'll unravel the SQL scripts that define these Views, shedding light on how they simplify complex queries into context-specific information.
company_info_full
company_info_full
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `company_info_full` AS
SELECT
`company`.`id`,
`company`.`name` AS `company_name`,
`company`.`address` AS `address`,
`city`.`name` AS `city`,
`company`.`zip_code` AS `zip_code`,
`country`.`name` AS `country`,
`k`.`name` AS `organization_type`,
`r`.`name` AS `registration_body`,
`company`.`registration_date`,
(`company`.`total_asset` - `company`.`total_liability`) AS `net_value`,
(SELECT
COUNT(*)
FROM
`employee`
WHERE
(`employee`.`company_id` = `company`.`id`)) AS `number_of_employees`,
(SELECT
COUNT(*)
FROM
crew
INNER JOIN
employee ON crew.crew_id = employee.id
WHERE
employee.company_id = company.id) AS crew,
(SELECT
COUNT(*)
FROM
staff
INNER JOIN
employee ON staff.staff_id = employee.id
WHERE
employee.company_id = company.id) AS staff,
(SELECT
COUNT(*)
FROM
company_shareholder
WHERE
company_shareholder.company_id = company.id) AS number_of_shareholders,
(SELECT
COUNT(*)
FROM
company_film
WHERE
company_film.company_id = company.id) AS total_films_produced,
(SELECT
COUNT(*)
FROM
company_grant
WHERE
company_grant.company_id = company.id) AS total_grants_applied,
(SELECT
COUNT(grant_request.id)
FROM
grant_request
INNER JOIN
company_grant ON grant_request.id = company_grant.grant_id
WHERE
company_grant.company_id = company.id
AND grant_request.status = 'Approved') AS approved_grants,
(SELECT
COUNT(grant_request.id)
FROM
grant_request
INNER JOIN
company_grant ON grant_request.id = company_grant.grant_id
WHERE
company_grant.company_id = company.id
AND grant_request.status = 'Pending') AS pending_grants,
(SELECT
COUNT(grant_request.id)
FROM
grant_request
INNER JOIN
company_grant ON grant_request.id = company_grant.grant_id
WHERE
company_grant.company_id = company.id
AND grant_request.status = 'Denied') AS denied_grants
FROM
((((`company`
JOIN `city` ON ((`company`.`city_id` = `city`.`id`)))
JOIN `country` ON ((`company`.`country_code` = `country`.`code`)))
JOIN `kind_of_organization` `k` ON ((`company`.`kind_of_organization_id` = `k`.`id`)))
JOIN `registration_body` `r` ON ((`company`.`registration_body_id` = `r`.`id`)));
employee_info_full
employee_info_full
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `employee_info_full` AS
SELECT
`e`.`id` AS `id`,
`e`.`first_name` AS `first_name`,
`e`.`middle_name` AS `middle_name`,
`e`.`last_name` AS `last_name`,
`e`.`date_of_birth` AS `date_of_birth`,
`c`.`name` AS `company`,
`e`.`employee_role` AS `employee_role`,
(CASE
WHEN
`e`.`employee_role` IN (SELECT
`role`.`name`
FROM
`role`)
THEN
'Crew'
WHEN
`e`.`employee_role` IN (SELECT
`department`.`name`
FROM
`department`)
THEN
'Staff'
END) AS `division`,
`e`.`date_started` AS `date_started`,
`e`.`email_address` AS `email_address`,
`p`.`phone` AS `phone`,
`p`.`description` AS `description`
FROM
((`employee` `e`
JOIN `phone_number` `p`)
JOIN `company` `c`)
WHERE
((`e`.`id` = `p`.`employee_id`)
AND (`e`.`company_id` = `c`.`id`))
payroll
payroll
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `payroll` AS
SELECT
`ss`.`employee_id` AS `employee_id`,
`e`.`first_name` AS `first_name`,
`e`.`middle_name` AS `middle_name`,
`e`.`last_name` AS `last_name`,
`c`.`name` AS `company`,
`d`.`name` AS `department`,
`ss`.`job_level` AS `job_level`,
`ss`.`working_hours` AS `working_hours`,
`ss`.`salary` AS `salary`
FROM
((((`staff_salary` `ss`
JOIN `staff` `s` ON ((`ss`.`employee_id` = `s`.`employee_id`)))
JOIN `employee` `e` ON ((`s`.`employee_id` = `e`.`id`)))
JOIN `department` `d` ON ((`s`.`department_id` = `d`.`id`)))
JOIN `company` `c` ON ((`e`.`company_id` = `c`.`id`)))
ORDER BY `e`.`first_name` , `e`.`last_name`
grant_info_full
grant_info_full
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `grant_info_full` AS
SELECT
`g`.`id` AS `id`,
`g`.`title` AS `grant_title`,
`g`.`funding_organization` AS `funding_organization`,
`g`.`application_date` AS `application_date`,
`g`.`deadline` AS `deadline`,
`g`.`maximum_monetary_value` AS `maximum_monetary_value`,
`g`.`desired_amount` AS `desired_amount`,
`g`.`status` AS `status`,
(SELECT
COUNT(0)
FROM
`company_grant`
WHERE
(`company_grant`.`grant_id` = `g`.`id`)) AS `how_many_companies_applied_for_grant`,
GROUP_CONCAT(`c`.`name`
ORDER BY `c`.`name` ASC
SEPARATOR ', ') AS `companies_that_applied_for_grant`
FROM
((`grant_request` `g`
LEFT JOIN `company_grant` `cg` ON ((`cg`.`grant_id` = `g`.`id`)))
LEFT JOIN `company` `c` ON ((`cg`.`company_id` = `c`.`id`)))
GROUP BY `g`.`id`
ORDER BY `g`.`title`
company_film_full
company_film_full
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `company_film_full` AS
SELECT
`f`.`movie_code` AS `movie_code`,
`f`.`title` AS `movie_title`,
`f`.`release_year` AS `release_year`,
GROUP_CONCAT(`c`.`name`
ORDER BY `c`.`name` ASC
SEPARATOR ', ') AS `production_companies`
FROM
((`company_film` `cf`
JOIN `company` `c` ON ((`cf`.`company_id` = `c`.`id`)))
JOIN `film` `f` ON ((`cf`.`film_movie_code` = `f`.`movie_code`)))
GROUP BY `f`.`movie_code`
ORDER BY `f`.`title`
shareholder_info_full
shareholder_info_full
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `shareholder_info_full` AS
SELECT
`shareholder`.`id` AS `id`,
`shareholder`.`first_name` AS `first_name`,
`shareholder`.`last_name` AS `last_name`,
`shareholder`.`place_of_birth` AS `place_of_birth`,
`country`.`name` AS `nationality`,
`shareholder`.`mothers_maiden_name` AS `mothers_maiden_name`,
`shareholder`.`fathers_first_name` AS `fathers_first_name`,
`shareholder`.`personal_telephone` AS `personal_telephone`,
`shareholder`.`national_insurance_number` AS `national_insurance_number`,
`shareholder`.`passport_number` AS `passport_number`,
(SELECT
COUNT(0)
FROM
`company_shareholder`
WHERE
(`company_shareholder`.`shareholder_id` = `shareholder`.`id`)) AS `total_companies_shareholder_owns_shares_in`,
GROUP_CONCAT(`company`.`name`
ORDER BY `company`.`name` ASC
SEPARATOR ', ') AS `companies_shareholder_owns_shares_in`
FROM
(((`shareholder`
JOIN `country` ON ((`shareholder`.`country_code` = `country`.`code`)))
LEFT JOIN `company_shareholder` ON ((`company_shareholder`.`shareholder_id` = `shareholder`.`id`)))
LEFT JOIN `company` ON ((`company_shareholder`.`company_id` = `company`.`id`)))
GROUP BY `shareholder`.`id`
ORDER BY CONCAT(`shareholder`.`first_name`,
' ',
`shareholder`.`last_name`)
Page updated
Google Sites
Report abuse