Rauf

Create View

For example, let's say we have two tables: table_tennis_players and badminton_players. Both tables include player ratings. We want to create a view that combines the top 10 players from both sports based on their ratings. Here's how:

  
-- Creating a view for top 10 players across table tennis and badminton
  CREATE VIEW top_10_players AS
  SELECT player_name, sport, rating
  FROM (
      SELECT player_name, 'Table Tennis' AS sport, rating
      FROM table_tennis_players
      UNION ALL
      SELECT player_name, 'Badminton' AS sport, rating
      FROM badminton_players
  ) combined_players
  ORDER BY rating DESC
  LIMIT 10;
  
-- The above query combines both tables and sorts players by rating.
      

Viewing the View

Once a view is created, you can easily query it just like a table. For example, to see all the data in the view:

  
-- Selecting all data from the view
  SELECT * FROM top_10_players;
  
-- This retrieves the top 10 players as defined in the view.
      

You can also perform aggregations or apply filters on the view:

  
-- Counting the number of players in each sport
  SELECT sport, COUNT(*) AS player_count
  FROM top_10_players
  GROUP BY sport;
  
-- This groups players by sport and counts them.
      

Temporary Views

Temporary views are created for use within a session and are not stored permanently in the database. Here’s how to create one:

  
-- Creating a temporary view
  CREATE TEMPORARY VIEW temp_top_players AS
  SELECT player_name, rating
  FROM table_tennis_players
  WHERE rating > 90;
  
-- This temporary view filters table tennis players with ratings above 90.
  
  
-- Viewing the temporary view
  SELECT * FROM temp_top_players;
  
-- Temporary views are session-specific and disappear once the session ends.
      

Common Table Expressions, Views for Partitioning, and Views for Securing

Common Table Expressions (CTEs)

CTEs are temporary result sets defined within a query. They simplify complex queries by breaking them into smaller parts:

  
-- Using a CTE to find top players
  WITH top_table_tennis AS (
      SELECT player_name, rating
      FROM table_tennis_players
      ORDER BY rating DESC
      LIMIT 5
  ),
  
       top_badminton AS (
      SELECT player_name, rating
      FROM badminton_players
      ORDER BY rating DESC
      LIMIT 5
  )
  SELECT * FROM top_table_tennis
  UNION ALL
  SELECT * FROM top_badminton;
  
-- This combines top 5 players from each sport using a CTE.
      

Views for Partitioning

Views can be used to logically partition data. For example, partitioning players by region:

  
-- Partitioning players by region
  CREATE VIEW regional_players AS
  SELECT player_name, region, rating
  FROM table_tennis_players
  WHERE region = 'Asia';
  
-- This view filters players from the Asian region.
      

Views for Securing Data

Views can limit the data visible to certain users, enhancing security. For example:

  
-- Securing sensitive data
  CREATE VIEW public_player_info AS
  SELECT player_name, rating
  FROM table_tennis_players;
  
-- This view excludes sensitive columns like contact details.