Create a specific view in SQL server

Question

1.00/5 (1 vote)

See more:

In the last few days I developed a small database inside the SQL Server, seeking to improve my knowledge.

Now, I need to create a view for the analysis team, where the data reported below points to. How should I write the query to make this view?

Here is the data that I must enter in the view, for this analysis team:

- Order ID
- Date and time of order creation
- Customer's first and last name
- Delivery date
- Item code and description
- Quantity demanded
- Amount to be paid
- Payment Type Description
- Total order weight
- Total order volume

Tables and fields inside the database:

What I have tried:

SQL

-- Articles table

CREATE TABLE [DW]. [Articles] (
[Article] [nvarchar] (30) NOT NULL,
[Description] [nvarchar] (150) NOT NULL,
[Unit_di_misura] [nvarchar] (20) NOT NULL,
[Pieces_per_Box] [nvarchar] (20) NOT NULL,
[Box_for_Pallet] [int] NOT NULL,
[Unit_Cost] [float] NOT NULL,
[Net_Weight_Kg] [float] NOT NULL,
[Height_cm] [int] NOT NULL,
[Length_cm] [int] NOT NULL,
[Width_cm] [int] NOT NULL,
[Insert] [datetime] NOT NULL

-- Customers table

CREATE TABLE [DW]. [Customers] (
[Client_ID] [int] IDENTITY (1,1) NOT NULL,
[Name] [nvarchar] (50) NOT NULL,
[Surname] [nvarchar] (50) NOT NULL,
[Social_Reason] [nvarchar] (80) NULL,
[CF_PIVA] [nvarchar] (30) NOT NULL,
[Email] [nvarchar] (80) NOT NULL,
[Telephone] [nvarchar] (30) NOT NULL,
[Address] [nvarchar] (50) NOT NULL,
[CAP] [int] NOT NULL,
[City] [nvarchar] (50) NOT NULL,
[Province] [nvarchar] (50) NOT NULL,
[Country] [nvarchar] (50) NOT NULL,
[Insert] [datetime] NOT NULL,
CONSTRAINT [PK_ID_Cliente] PRIMARY KEY CLUSTERED 

-- Orders table

CREATE TABLE [DW]. [Orders] (
[ID_Orders] [int] IDENTITY (1,1) NOT NULL,
[Customer] [nvarchar] (80) NOT NULL,
[Client_ID] [int] NOT NULL,
[Article] [nvarchar] (30) NOT NULL,
[Quantity] [int] NOT NULL,
[Delivery] [date] NULL,
[Payment] [nvarchar] (50) NOT NULL,
[Payment_ID] [int] NULL,
[Insert] [datetime] NOT NULL,
[Shipping] [date] NULL,
 CONSTRAINT [PK_Ordini] PRIMARY KEY CLUSTERED

-- Payment type table

CREATE TABLE [DW]. [TypePayment] (
[ID_Payment] [int] IDENTITY (1,1) NOT NULL,
[Payment] [nvarchar] (50) NOT NULL,
[Description] [nchar] (150) NULL,
 CONSTRAINT [PK_ID_Payment] PRIMARY KEY CLUSTERED

Posted 11-Jan-22 13:26pm

iMati

Updated 17-Oct-22 22:12pm

Add a Solution

Comments

CHill60 12-Jan-22 3:49am

And where is the query that will get the data you want?

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2022-01-12T00:43:00

Solution 1

Your database schema misses foreign key relationships, that makes it a bad design.

Your query Skelton should be like this

SQL

SELECT <Required Columns> .....
FROM ORDERS O
INNER JOIN CUSTOMERS C ON O.CUSTOMER = C.CLIENT_ID
INNER JOIN ARTICLES A ON O.ARTICLE = A.ARTICLE
INNER JOIN TYPEPAYMENT TP ON O.PAYMENT_ID = TP.ID_PAYMENT

You can tweek the query as per your requirement.

Posted 12-Jan-22 0:43am

_Asif_

Comments

iMati 12-Jan-22 11:35am

Thank you very much for your time. I've made several attempts, but I still haven't gotten a positive solution. For example, in SELECT I entered this data:

A.Articolo AS A, A.Descrizione, O.ID_Ordini, O.Cliente, O.Articolo AS O, O.Quantita, O.ID_Pagamento, O.Inserimento, TP.ID_Pagamento AS TP

CHill60 · Answer 2 · 2022-01-12T06:33:00

Solution 1 won't work because of this line

SQL

INNER JOIN CUSTOMERS C ON O.CUSTOMER = C.CLIENT_ID

Based on the schema you have shown us that is attempting to match an [nvarchar] (80) to an [int] and that simply won't work.

Because you haven't given us any foreign keys to go by I can only suggest that it is supposed to be

SQL

INNER JOIN CUSTOMERS C ON O.Client_ID = C.Client_ID

At first glance it looks as if your select statement should be something like this

SQL

SELECT O.[ID_Orders]
,O.[Insert]
,C.[Name]
,C.[Surname]
,O.[Shipping] -- assume Shipping Date is the same as Delivery date
,A.[Article]
,A.[Description] 
,O.[Quantity]
,O.[Quantity] * A.[Unit_Cost]
,TP.[Description]
,O.[Quantity] * 
,O.[Quantity] * A.[Net_Weight_Kg]

Note the use of the table aliases that tell us which table we have taken the data from. But there is no obvious means of calculating the "Total order volume" - unless they are all rectangular prisms and you want to use

SQL

O.[Quantity] * O.[Height_cm] * O.[Length_cm] * O.[Width_cm]

But this is a moot point as you have a fundamental flaw in your design.

Orders can have many order items or "articles" in your design so there should be another table that lists the lines/items/articles per order, that table should link to the Articles table to get the attributes of each article, but it is this Order Item table that should contain the article ID and the Quantity of that article in this order.

As your design currently stands you can only have one Article per Order.

Once you have worked out what your query should be then here are the instructions for creating a view CREATE VIEW (Transact-SQL) - SQL Server | Microsoft Docs[^]

Syed Mubashshir Hasan · Answer 3 · 2022-10-17T22:12:00

In the order table, you perform an INNER JOIN against

[ID_Orders] [int] IDENTITY (1,1) NOT NULL,

Which is the same name as the view you're attempting to create. That is the issue. I'm assuming you already have a table with that name. If that is the case, simply rename the view you are creating. To avoid this, prefixing tables with T_ and views with V_ is a useful naming convention.

To create a view that takes data from two or more tables to give an output in a simplified manner. To understand this better you create a view by taking data from two table names. Here, read this article by scaler topics on how to create views in sql from single and multiple tables.

CREATE VIEW view_name AS  
SELECT column1, column2...column N 
FROM table1, table2...table N 
WHERE condition;

Create a specific view in SQL server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Create a specific view in SQL server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us