{"id":87276,"date":"2021-03-25T09:00:16","date_gmt":"2021-03-25T03:30:16","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=87276"},"modified":"2021-03-13T18:06:29","modified_gmt":"2021-03-13T12:36:29","slug":"having-clause-in-sql","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/having-clause-in-sql\/","title":{"rendered":"Using Views And Having Clause In SQL"},"content":{"rendered":"

In this tutorial, we will understand what SQL Views are and how to create views.<\/p>\n

First, let us understand the views briefly. After that, we will move to the usage of having command.<\/p>\n

Towards the end, we will learn about Syntax and discuss the examples of views and the having command.<\/p>\n

What are SQL Views?<\/h3>\n

Views are tables created by the user virtually by the SQL command containing the required conditionals.<\/p>\n

By using views we get the flexibility to decide which part of data more specifically how many rows and columns of data we need in our resulting virtual table.<\/p>\n

A view can contain rows and columns from one or multiple tables of the same database.<\/p>\n

Syntax:<\/strong><\/p>\n

CREATE VIEW viewName AS\r\nSELECT column1, column2, ...\r\nFROM tableName\r\nWHERE condition;\r\n<\/pre>\n
Advantages of Using Views in SQL<\/h3>\n
We have various advantages of using views:<\/p>\n
    \n
  1. Security:<\/strong> We can hide sensitive data by using views in SQL.<\/li>\n
  2. Simplicity:<\/strong> We have multiple tables in a database, with the help of views we can select the content columns required by us specifically.<\/li>\n
  3. Consistency:<\/strong> Many times in a SQL query, we need complex logic. By using views, we can hide the complex formulas and logic and avoid inconsistencies occurring in the data.<\/li>\n<\/ol>\n
    SQL Demo Database<\/h3>\n
    We will be using two tables in this tutorial i.e. DataFlair_emp1 and DataFlair_emp2. Let us view both of the tables.<\/p>\n
    Syntax:\u00a0<\/strong><\/p>\n
    SELECT * FROM tableName ;\r\n<\/pre>\n
    Query1:\u00a0<\/strong><\/p>\n
    SELECT * FROM DataFlair_emp1 ;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"View<\/a><\/p>\n
    Query2:<\/strong><\/p>\n
    SELECT * FROM DataFlair_emp2 ;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"View<\/a><\/p>\n
    Let us move forward by using examples:<\/p>\n
    1. Create SQL View using only one table<\/h4>\n
    Views can be created using one or multiple tables.<\/p>\n
    Syntax:<\/strong><\/p>\n
    CREATE VIEW viewName AS\r\nSELECT column1, column2, ...\r\nFROM tableName\r\nWHERE condition;\r\n<\/pre>\n
    Example 1:<\/strong> Let us create a view from our DataFlair_emp1 table.<\/p>\n
    Query:<\/strong><\/p>\n
    CREATE VIEW salaryAndpost AS\r\nSELECT post, salary\r\nFROM dataflair_emp1 ;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"Create<\/a><\/p>\n
    Here we can see our created view.<\/p>\n
    2. Creating views from multiple tables in SQL<\/h4>\n
    Syntax:<\/strong><\/p>\n
    CREATE VIEW viewName AS\r\nSELECT tableName1.column1, tableName1.column2,tableName2.column1 ...\r\nFROM tableName1, tableName2\r\nWHERE condition;\r\n<\/pre>\n
    Example 1:<\/strong> Let us create a view using tables DataFlair_emp1 and DataFlair_emp2.<\/p>\n
    Query:<\/strong><\/p>\n
    CREATE VIEW salaryPostAge AS\r\nSELECT dataflair_emp1.post, dataflair_emp1.salary , dataflair_emp2.age\r\nFROM dataflair_emp1 , dataflair_emp2\r\nWHERE dataflair_emp1.emp_id = dataflair_emp2.emp_id;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"Create<\/a><\/p>\n
    Here we can see the view we created and its contents.<\/p>\n
    3. Updating SQL View<\/h4>\n
    We can update an already created view by using CREATE OR REPLACE query<\/p>\n
    Syntax:<\/strong><\/p>\n
    CREATE OR REPLACE VIEW viewName AS\r\nSELECT tableName1.column1, tableName1.column2,tableName2.column1 ...\r\nFROM tableName1, tableName2\r\nWHERE condition;\r\n<\/pre>\n
    Example 1:<\/strong> Let us update our SalaryPostAge view and include the emp_id in the view as well.<\/p>\n
    Query:<\/strong><\/p>\n
    CREATE OR REPLACE VIEW salaryPostAge AS\r\nSELECT dataflair_emp1.post, dataflair_emp1.salary , dataflair_emp2.age , dataflair_emp1.emp_id\r\nFROM dataflair_emp1 , dataflair_emp2\r\nWHERE dataflair_emp1.emp_id = dataflair_emp2.emp_id;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"update<\/a><\/p>\n
    Here we can see our updated view and the records.<\/p>\n
    4. Inserting a row in a View in SQL<\/h4>\n
    Syntax:<\/strong><\/p>\n
    INSERT INTO viewName (column1, column2,column3,......) \r\nVALUES(value1,value2,value3,....);\r\n<\/pre>\n
    Example 1:<\/strong> Let us insert a row in the SalaryAndPost view.<\/p>\n
    Query:<\/strong><\/p>\n
    INSERT INTO salaryAndpost (post, salary) \r\nVALUES(\"HR Intern\" , 6000);<\/pre>\n
    Output:<\/strong><\/p>\n
    \"insert<\/a><\/p>\n
    Here we can see the records we inserted into the database.<\/p>\n
    5. Deleting a row from the View in SQL<\/h4>\n
    Syntax:<\/strong><\/p>\n
    DELETE FROM viewName\r\nWHERE condition ;\r\n<\/pre>\n
    Example 1:<\/strong> Let us delete the record which has a salary of 18000 in salaryAndpost view.<\/p>\n
    Query:<\/strong><\/p>\n
    DELETE FROM salaryAndpost \r\nWHERE salary = 18000 ;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"delete<\/a><\/p>\n
    Here we can see the record corresponding to salary 18,000 is deleted.<\/p>\n
    6. Deleting the View in SQL<\/h4>\n
    Syntax:<\/strong><\/p>\n
    DROP VIEW viewName;\r\n<\/pre>\n
    Example 1:<\/strong> Let us delete the view salaryAndpost.<\/p>\n
    Query:<\/strong><\/p>\n
    DROP VIEW salaryAndpost;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"drop<\/a><\/p>\n
    Here we can see the SalaryAndPost view no longer exists.<\/p>\n
    7. Creating a View by using the WITH CHECK option<\/h4>\n
    The With Check is a very useful clause for the updatable views. This helps us to prevent the insertion of rows with null values.<\/p>\n
    We can use the WITH CHECK clause in the CREATE VIEW statement for the views which would be updated by us in the future. We can write the create view command as follows:<\/p>\n
    Syntax:<\/strong><\/p>\n
    CREATE VIEW viewName AS\r\nSELECT column1,column2,....\r\nFROM  tableName\r\nWHERE column1 IS NOT NULL\r\nWITH CHECK OPTION;\r\n<\/pre>\n
    Example 1:<\/strong> Let us create a view with the CHECK option.<\/p>\n
    Query:<\/strong><\/p>\n
    CREATE VIEW salaryPost AS\r\nSELECT salary, post\r\nFROM  dataflair_emp1\r\nWHERE post IS NOT NULL\r\nWITH CHECK OPTION;\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"view<\/a><\/p>\n
    Here we can observe the new view we created with a check.<\/p>\n
    Let us try to insert a row with the null value in this view.<\/p>\n
    Query:<\/strong><\/p>\n
    INSERT INTO salaryPost (salary) \r\nVALUES(10000);\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"null<\/a><\/p>\n
    Here we view our view after inserting the record.<\/p>\n
    Here the default value null was being used for the post column. Thus the insert query gives an error and we are unable to insert data into the column.<\/p>\n
    If we specify both the columns then we can easily insert the required data.<\/p>\n
    Example Query:<\/strong><\/p>\n
    INSERT INTO salaryPost (salary, post) \r\nVALUES(20000,\"Manager\");\r\n<\/pre>\n
    Output:<\/strong><\/p>\n
    \"insert<\/a><\/p>\n
    Here we can see the new row with data (20000, \u201cManager\u201d) is inserted in our view without any error.<\/p>\n
    HAVING Clause in SQL<\/h3>\n
    We use HAVING clauses at places where we can\u2019t use the WHERE clause. We can\u2019t use where clause with the aggregate functions. So in those queries, we use the Having clause to specify the conditions. Some of the most popular aggregate functions are:<\/p>\n