Which of the following is used to create view for these relations together
What is VIEW?A VIEW is actually a query and the output of the query becomes the content of the view. The VIEW can be treated as a base table and it can be QUERIED, UPDATED, INSERTED INTO, DELETED FROM and JOINED with other tables and views. Show
A VIEW is a data object which does not contain any data. Its contents are the resultant of a base table. They are operated just like the base table but they don’t contain any data of their own. A view can be accessed with the use of SQL SELECT statement like a table. A view can also be made up by selecting data from more than one tables. SQL CREATE VIEWSyntax: CREATE [RECURSIVE] VIEW view_name {[(column[, ...])] | [OF udt_name [UNDER supertype_name [REF IS column_name {SYSTEM GENERATED | USER GENERATED | DERIVED}] [column_name WITH OPTIONS SCOPE table_name]]]} AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]Parameters:
Example: Sample table: agents To create a view 'agentview' as the table 'agents', the following SQL statement can be used: SQL Code: CREATE VIEW agentview AS SELECT * FROM agents;Output: To execute query on this view SQL Code: SELECT * FROM agentview;SQL Create View with WHEREHere we are going to discuss the usage of WHERE clause along with the VIEW command to store records in the view based on certain conditions. Example: Sample table: agents To create a view 'agentview' as the table 'agents' with the following condition - 1. 'working_area' must be 'Bangalore', the following SQL statement can be used: SQL Code: CREATE VIEW agentview AS SELECT * FROM agents WHERE working_area=’Bangalore’;Output: To execute query on this view SQL Code: SELECT * FROM agentview;SQL Create View with specific columns and WHEREHere we are going to discuss, how some specific columns of another table can make a view in CREATE VIEW statement. Example: Sample table: agents To create a view 'agentview' with the columns agent_code, agent_name and working_area of the table 'agents' with the following condition - 1.'working_area' must be 'Bangalore', the following SQL statement can be used: SQL Code: CREATE VIEW agentview AS SELECT agent_code,agent_name,working_area FROM agents WHERE working_area='Bangalore';Output: To execute query on this view SQL Code: SELECT * FROM agentview;SQL Create View with arithmetic expressionHere we are discussing that, arithmetic expression can also be used to create a view in a CREATE VIEW statement. Example: Sample table: customer To create a view 'myclient' with three columns 'client_name', 'client_no' and 'outspercent' from the table 'customer' with following conditions - 1. 'outspercent' column must be created with 'outstanding_amt'*100/('opening_amt'+'receive_amt') from the customer table, 2. 'cust_country' of 'customer' table must be 'USA', 3. 'outspercent' must be greater than 50, the following SQL statement can be used: SQL Code: CREATE VIEW myclient(client_name,client_no,outspercent) AS SELECT cust_name,cust_code,outstanding_amt*100/(opening_amt+receive_amt) FROM customer WHERE cust_country='USA' AND outstanding_amt*100/(opening_amt+receive_amt)>50;Output: To execute query on this view SQL Code: SELECT * FROM myclient;SQL Create View with AND operatorHere we are going to discuss the usage of WHERE clause and AND operator along with the CREATE VIEW command. Example: Sample table: agents To create a view 'agentview' as the table 'agents' with following conditions- 1. 'working_area' must be ' 'Bangalore', 2. 'commission' must be greater than .1, the following SQL statement can be used : SQL Code: CREATE VIEW agentview AS SELECT * FROM agents WHERE working_area='Bangalore' AND commission>.1;Output: To execute query on this view SQL Code: SELECT * FROM agentview;SQL Create View with INHere we are going to discuss the usage of IN operator along with the VIEW command in a CREATE VIEW statement. Example: Sample table: agents To create a view 'agentview' as the table 'agents' with the following condition - 1. 'agent_code' must be any of the following agent_codes - 'A006', 'A004', 'A001', 'A009', the following SQL statement can be used: SQL Code: CREATE VIEW agentview AS SELECT * FROM AGENTS WHERE agent_code IN ('A006','A004','A001','A009');Output: To execute query on this view SQL Code: SELECT * FROM agentview;SQL Create View with BETWEENHere we are going to discuss the usage of BETWEEN operator along with the VIEW command in a CREATE VIEW statement. Example: Sample table: customer To create a view 'customerview' as the table 'customer' with the following condition - 1. 'cust_name' must begin with the letter from 'A' through 'J', the following SQL statement can be used: SQL Code: CREATE VIEW customerview AS SELECT * FROM customer WHERE cust_name BETWEEN 'A' AND 'J';Output: To execute query on this view SQL Code: SELECT * FROM customerview;SQL Create View with LIKEHere we are going to discuss the usage of LIKE operator along with the VIEW command in a CREATE VIEW statement. Example: Sample table: customer To create a view 'agentview' as the table 'agents' with the following condition - 1. 'cust_name' must not begin with the letter 'M', the following SQL statement can be used: SQL Code: CREATE VIEW agentview AS SELECT * FROM agents WHERE agent_name NOT LIKE 'M%';Output: To execute query on this view SQL Code: SELECT * FROM agentview;SQL Create View with HAVINGHere we are discussing the usage of aggregate COUNT() and HAVING clause along with the CREATE VIEW command. The aggregate function COUNT can’t be used as a predicate with WHERE clause but HAVING can be used. Example: Sample table: customer To create a view 'countgrade' with two columns 'grade' and 'gradecount' from the table 'customer' with following conditions - 1. 'gradecount' column creating with count(*) from the customer table, 2. unique 'grade' must be within the group, 3. number of grades per group must be 3, the following SQL statement can be used: SQL Code: CREATE VIEW countgrade(grade,gradecount) AS SELECT grade,COUNT(*) FROM customer GROUP BY grade HAVING count(*)=3;Output: To execute query on this view SQL Code: SELECT * FROM countgrade;SQL Create View with order by in descending orderHere we are going to discuss the usage of ORDER BY along with the CREATE VIEW command which arranges the view in an order. Example: Sample table: agents To create a view 'agentview' with the columns 'agent_name', 'working_area' and 'commission' of the table 'agents' with the following condition - 1. 'agent_name' and 'commission' must be arranged in descending order, the following SQL statement can be used: SQL Code: CREATE VIEW agentview AS SELECT agent_name, working_area, commission FROM AGENTS ORDER BY agent_name,commission DESC;Output: To execute query on this view SQL Code: SELECT * FROM agentview;SQL Create a View from a ViewHere, we are going to discuss how to create a SQL VIEW from a VIEW using CREATE VIEW statement. Example: Sample table: agents Sample table: orders The statement bellow creates the VIEW 'myview' - SQL Code: CREATE VIEW myview AS SELECT b.ord_date,a.agent_code,a.agent_name FROM agents a, orders b WHERE a.agent_code=b.agent_code AND b.ord_amount=( SELECT MAX(ord_amount) FROM orders c WHERE c.ord_date=b.ord_date);To create a view 'myview1' from the view 'myview' with following conditions - 1. 'a' and 'b' are the aliases of 'myview' view, 2. unique 'agent_code' and 'agent_name' combination form 'myview' of alias 'a' will comes once which satisfies the condition bellow: i). the number of rows for alias 'b' must be less than or equal to 5 which satisfies the condition bellow : a). 'agent_code' of alias 'a' and alias 'b' must be same, the following SQL statement can be used: SQL Code: CREATE VIEW myview1 AS SELECT DISTINCT agent_code,agent_name FROM myview a WHERE 5<=( SELECT COUNT(*) FROM myview b WHERE a.agent_code=b.agent_code);Output: To execute query on this view SQL Code: SELECT * FROM myview1;Practice SQL Exercises
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus. Previous: Union What is a view any relation?Explanation: A relation that is not a part of the schema but is a virtual relation is called as view. Views improve writability as viewing the entire logical model is not feasible. ADVERTISEMENT. ADVERTISEMENT. 2.
What is create view in SQL?In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Which one of the following is true related to views?
What is the use of view?Views can join and simplify multiple tables into a single virtual table. Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data. Views can hide the complexity of data.
|