When we run the create or the replace view statement, MySQL checks whether it exists in the database. Create or replace View: These keywords serve to create or replace the existing View.The syntax is following: Create or replace View view_name The CREATE VIEW and REPLACE VIEW keywords are used together. The REPLACE VIEW is used to change the definition of a view. The query is as follows: mysql> Select first_name, last_name, case when gender='M' then 'Male' when gender ='F' then 'Female' end as 'Gender', hire_date from vwEmployees Similarly, instead of the printing F, the query must return Female. Instead of printing M, the query must return Male. The output of the gender column returned by the View is either M or F. We print the value of the gender in some meaningful format. We can use the CASE or IF function on the output of the View. Query output: Use the Control-Flow Function on the View The query is as follows: mysql> select * from vwemployees where Year(hire_date)>=1990 Let’s retrieve the list of the employees whose joining year is greater or equal to 1990. We can use the date-time function on the View output. Query Output: Use the Date-Time Function on the View The query is as follows: mysql> select * from vwemployees where emp_no select gender, count(emp_no)as 'Total Employees' from vwEmployees group by gender We want to retrieve the list of the employees whose employee number is less than 10005. The query is as follows: mysql> select * from vwEmployees where gender='F' In our case, we want to retrieve the list of female employees from the vwEmployees. We can filter the View output using the WHERE clause. Output: Filter the Data of the View Using the WHERE Clause To view the data of vwEmployees, run the following query: mysql> select * from vwEmployees To access the data of the View, we can use the SELECT query. The definition of the View is following: Create view vwEmployees Suppose we want to create a view that populates the list of employees. The query is specified after the AS keyword. The select * from is the query that generates the View using base_table.view_name is the desired name of the View.The syntax is following: Create View view_name The Create View statement creates a new view in a database. Now, let us clarify the statements to manage the MySQL views. I have inserted some dummy records into the tblemployee table using the following query: INSERT INTO `EltechDB`.`tblemployees` The following query creates a tblemployee table: CREATE TABLE tblemployees ( The following query creates the EltechDB database: Create database EltechDB In that database, I have created a table named tblemployee. Sample Database Setupįor the demonstration, I have installed MySQL Server and created a dummy database named EltechDB. This article will explain the Create View, Replace View and Drop View statements usage in the MySQL server. If we store any customer’s sensitive information in the table and want to show only the primary details, we can create a view. The views also apply to hide the actual name and details of the table. Moreover, when we want to change the query definition, we can change the view’s definition instead of making changes in the application. In such cases, it is preferable to create an SQL view and encapsulate the business logic within it. The changes made in a query must reflect in the entire application. However, due to business requirement, you must change that query logic. You use this query in the application code frequently. Suppose you have a complex SQL query using that uses multiple joins and complex business logic. When we run the SELECT query to populate the data, it executes a query that creates the view (View definition). Unlike physical tables, views do not store data in a database. The data is stored in a way that makes analysis easy, and it gives a full view of the entire organisation.An SQL view is a virtual table or a result-set generated by the SELECT query. It speeds up the analysis and the processing of data as compared with having isolated databases.Ĭorrect. Since a data warehouse is an integrated repository, it contains data on the various aspects of the business, thus helping analysts draw deeper insights. It enables analysts to create holistic business reports easily using information from multiple departments.Ĭorrect. It is time-variant and contains information on past data. A data warehouse stores current as well as historical data, which may not be the case with locally stored files in MS Excel, etc.Ĭorrect. It contains data from different data sources.ĭata persistence. A data warehouse is an integrated repository of data. It helps integrate data from multiple source systems, enabling a central view across the enterprise.Ĭorrect.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |