DEV Community

ccsunny
ccsunny

Posted on

Some demos of SQL's table views

What is a table view in SQL?

here are some examples of how a view can be created and used in MySQL:

  1. Create a simple view that selects all columns from the "customers" table:
CREATE VIEW customer_view AS
SELECT *
FROM customers;
Enter fullscreen mode Exit fullscreen mode
  1. Create a view that aggregates data from the "orders" table by customer ID:
CREATE VIEW order_summary AS
SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id;
Enter fullscreen mode Exit fullscreen mode
  1. Create a view that joins two tables and selects specific columns:
CREATE VIEW sales_report AS
SELECT customers.first_name, customers.last_name, orders.order_date, order_items.unit_price, order_items.quantity
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id;
Enter fullscreen mode Exit fullscreen mode

These views can then be used like a regular table in a query. For example, you can query the customer_view created in example 1 like this:

SELECT * FROM customer_view;
Enter fullscreen mode Exit fullscreen mode

I hope this helps! Let me know if you have any more questions.

show full tables in <your_database_name> where TABLE_type like 'VIEW';
Enter fullscreen mode Exit fullscreen mode

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay