Public and Employee Information Systems

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What type of information is stored in the `government_maintenance` table?

Company financial data

Human resources management records

Public facility maintenance details (correct)

Employee records and salaries

Which field in the `hr_dt` table represents the annual income actually received by an employee?

total_annual_income_due

attendance_days

monthly_fixed_income

total_annual_income_actual (correct)

The `company_dt` table tracks various financial metrics. What does the `total_assets_budget_execution_rate` column measure?

The efficiency of budget utilization regarding total assets (correct)

The total assets compared to liabilities

The projected total assets for the coming year

The percentage of total assets allocated to budgets

Which of the following is NOT a field in the `government_maintenance` table?

monthly_bonus_subtotal Signup and view all the answers

What is the purpose of the `attendance_days` field in the `hr_dt` table?

To record the total days an employee has worked in a month Signup and view all the answers

In the `company_dt` table, what does the `interest_bearing_liabilities` column represent?

Total liabilities that incur interest Signup and view all the answers

Which of the following fields in the `hr_dt` table indicates the employee's official identification number?

id_number Signup and view all the answers

What does the `installation_date` field in the `government_maintenance` table signify?

The date when the facility was installed Signup and view all the answers

What does the `interest_bearing_liabilities_budget_execution_rate` represent?

Total interest-bearing liabilities budget execution rate Signup and view all the answers

Which of the following is NOT an element included in the `customer_information` table?

Store name Signup and view all the answers

What is the purpose of comparing current values to previous periods in YoY analysis?

To assess performance trends over time Signup and view all the answers

In the context of the contract_dt table, what is the contract_amount field used for?

To specify the monetary value of the contract Signup and view all the answers

Which field indicates whether a customer is part of a group in the customer_information table?

sfqzkh Signup and view all the answers

Which execution rate is related to the total equity budget in the financial context provided?

total_equity_budget_execution_rate Signup and view all the answers

What is the significance of the `asset_liability_ratio` in financial reporting?

It compares total assets against total liabilities. Signup and view all the answers

In the context of analyzing the `rental_assets_total`, what does the budget execution rate indicate?

The performance in achieving rental asset budget goals Signup and view all the answers

What SQL function is advised against using for YoY analysis according to the provided guidelines?

LAG() Signup and view all the answers

To get detailed store metrics under a specific region, which logical condition must be applied?

Region name equals XX and stores are its subordinates Signup and view all the answers

What does a customer's assessment level of '96' or '0' indicate?

The customer is a VIP client. Signup and view all the answers

When comparing current financial assets, what indicates a downgrade for a VIP customer?

The current balance is below 10,000 but was above 10,000 last month. Signup and view all the answers

What is the correct format for data dates?

yyyyMMdd Signup and view all the answers

Which customer assessment levels indicate an upgrade to VIP status?

95, 97, 98, and 99 Signup and view all the answers

What defines the falling below threshold for VIP customer status?

Financial assets are below 10,000 while last month's average was 10,000 or above. Signup and view all the answers

How are upgrades and downgrades evaluated based on the assessment levels?

They are evaluated against previous assessment levels. Signup and view all the answers

Which of the following does NOT represent a factor in VIP customer status determination?

The customer's geographical location. Signup and view all the answers

What data is displayed when assessing the upgraded or downgraded status?

Data date, customer ID, customer name, current and previous assessment levels, and VIP type. Signup and view all the answers

In conditions for an upgrade to VIP, which statement is TRUE?

The customer should have a previous month asset level greater than 10,000. Signup and view all the answers

What is the implication when the assessment level remains unchanged?

The customer is neither upgraded nor downgraded. Signup and view all the answers

Study Notes

Public Facility Information Query

Table: government_maintenance
Columns:
- facility_id: Facility ID
- type: Facility type
- installation_date: Installation date
- lastMaintenance_date: Last maintenance date
- maintenance_content: Maintenance description
- maintenance_cost: Maintenance cost
- city: City
- district: Administrative district
- street: Street
- detailed_address: Detailed address

Employee Information Query

Table: hr_dt
Columns:
- serial_number: Serial number
- employee_id: Employee ID
- employee_record: Employee record
- employee_number: Employee number
- name: Name
- id_number: ID number
- yearly_dynamic: Yearly dynamic
- on_record_dynamic: On-record dynamic
- non_on_record_full_year: Full year non-on-record
- company_code: Company code
- company: Company
- department_name: Department name
- position_name: Position name
- total_annual_income_due: Total annual income due
- total_annual_income_actual: Total annual income actual
- monthly_fixed_income: Monthly fixed income
- monthly_allowances_subtotal: Monthly allowances subtotal
- night_shift_allowance: Night shift allowance
- high_temperature_allowance: High temperature allowance
- overseas_allowance: Overseas allowance
- audit_allowance: Audit allowance
- monthly_bonus_subtotal: Monthly bonus subtotal
- month: Month
- payable: Payable salary
- actual: Actual salary
- attendance_days: Attendance days
- corporate_level: Corporate level
- sector: Sector/Plate
- scope: Scope
- nature: Nature
- hr_management: HR management

Financial Information Query

Table: company_dt
Columns:
- company_id: Company ID
- company_name: Company name
- year: Year
- month: Month
- total_assets: Total assets
- total_assets_budget: Total assets budget value
- total_assets_budget_execution_rate: Total assets budget execution rate
- interest_bearing_liabilities: Interest-bearing liabilities total amount
- interest_bearing_liabilities_budget: Interest-bearing liabilities total amount budget value
- interest_bearing_liabilities_budget_execution_rate: Interest-bearing liabilities total amount budget execution rate
- total_liabilities: Total liabilities
- total_liabilities_budget: Total liabilities budget value
- total_liabilities_budget_execution_rate: Total liabilities budget execution rate
- rental_assets_total: Total rental assets
- rental_assets_total_budget: Total rental assets budget value
- rental_assets_total_budget_execution_rate: Total rental assets budget execution rate
- total_equity: Total equity
- total_equity_budget: Total equity budget value
- total_equity_budget_execution_rate: Total equity budget execution rate
- paid_in_capital: Total paid-in capital
- paid_in_capital_budget: Total paid-in capital budget value
- paid_in_capital_budget_execution_rate: Total paid-in capital budget execution rate
- inventory: Inventory
- inventory_budget: Inventory budget value
- inventory_execution_rate: Inventory execution rate
- asset_liability_ratio: Asset liability ratio

Contract Information Query

Table: contract_dt
Columns:
- contract_id: Contract ID
- contract_amount: Contract amount
- store_id: Store ID
- region_id: Region ID
- date: Date
- store_name: Store name
- region_name: Region name

Customer Information Query - 兴业银行

Table: customer_information
Columns:
- cqrq: Data date
- khid: Customer ID
- khname: Customer name
- zjlx: Main certificate type
- zjid: Certificate number
- sjhm: Mobile number
- dykhcjdm: Current month assessment level
- sfqzkh: Is it a group customer
- sfjckh: Is it a basic customer
- khzt: Customer status
- zhjrzcye: Comprehensive financial assets balance
- hnzzcye: Total assets balance within the bank
- bwbckye: Domestic and foreign currency deposit balance
- bwbhqckye: Domestic and foreign currency demand deposit balance
- bwbdqckye: Domestic and foreign currency time deposit balance
- rmbckye: RMB deposit balance
- rmbhqye: RMB demand deposit balance
- rmbdqye: RMB time deposit balance
- zhjrzcnrj: Comprehensive financial assets annual average daily balance
- hnzzcnrj: Total assets annual average daily balance
- bwbcknrj: Domestic and foreign currency deposit annual average daily balance
- bwbhqcknrj: Domestic and foreign currency demand deposit annual average daily balance
- bwbdqcknrj: Domestic and foreign currency time deposit annual average daily balance
- rmbhqnrj: RMB demand deposit annual average daily balance
- rmbdqnrj: RMB time deposit annual average daily balance
- rmbcknrj: RMB deposit annual average daily balance
- sytrkhcjdm: Last month's assessment level
- sytrkhkhcjdm: Last month's customer assessment level code
- sytrcjdm: Last month's customer level code
- sytrkhsjcjdm: Last month's customer actual level code
- sytrchcjlxdm: Last month's customer level type code
- sytrsfqzkh: Last month's whether it is a group customer
- sytrzhjrzcyrj: Last month's comprehensive financial assets monthly average daily balance
- sytrhnzzcyrj: Last month's total assets monthly average daily balance
- sytrbwbckyrj: Last month's domestic and foreign currency deposit monthly average daily balance
- sytrbwblcyrj: Last month's domestic and foreign currency wealth management monthly average daily balance
- sytrrmbhqyrj: Last month's RMB demand deposit monthly average daily balance
- sytrrmdqyrj: Last month's RMB time deposit monthly average daily balance
- sytrrmbckyrj: Last month's RMB deposit monthly average daily balance
- qxl: Permission column

Key Definitions

Year-on-Year Analysis: Comparing values from the current year and the previous year.
Month-on-Month Analysis: Comparing values from the current month and the previous month.
"XXXX年X月某X日": Date in the format "yyyyMMdd". For example, "20231231" represents December 31, 2023.
"当日": The current date in the format "yyyyMMdd". For example, if the current date is December 31, 2023, it will be "20231231."

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

This quiz covers essential information about public facilities maintenance and employee records systems. It explores data columns including facility types, maintenance dates, employee dynamics, and departmental information. Assess your understanding of how this data is structured and utilized in public administration and human resources.

Public and Employee Information Systems

Choose a study mode

Podcast

Questions and Answers

What type of information is stored in the government_maintenance table?

Which field in the hr_dt table represents the annual income actually received by an employee?

The company_dt table tracks various financial metrics. What does the total_assets_budget_execution_rate column measure?

Which of the following is NOT a field in the government_maintenance table?

What is the purpose of the attendance_days field in the hr_dt table?

In the company_dt table, what does the interest_bearing_liabilities column represent?

Which of the following fields in the hr_dt table indicates the employee's official identification number?

What does the installation_date field in the government_maintenance table signify?

What does the interest_bearing_liabilities_budget_execution_rate represent?

Which of the following is NOT an element included in the customer_information table?