In the last few days I have developed a small database within SQL Server, with the aim of improving my knowledge.
For now, I need to create a view (for the analytics team), to which the following data should be reported in the view.
➤ 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
For the moment I am stuck in this part, and therefore, I would need the query to compose this view by reporting the above data.
---
The last question I ask you is:
To create the view I would first have to create a relationship between the items, for example: Between items in the item table and items in the order table? -
-- The query I'm using to create the view, but I think something is missing or not correct.
-
-
USE [TestExec]
-
GO
-
-
CREATE VIEW OrdersToShip AS
-
SELECT O.[ID_Orders]
-
,O.[DataInsert]
-
,C.[Name]
-
,C.[Surname]
-
,O.[Shipping]
-
,A.[Article]
-
,A.[Description]
-
,O.[Quantity]
-
,O.[Quantity] * A.[Unit_Cost]
-
,TP.[Description]
-
,O.[Quantity] *
-
,O.[Quantity] * A.[Net_Weight_Kg]
-
-
FROM ORDERS O
-
INNER JOIN CUSTOMERS C ON O.Client_ID = C.Client_ID
-
INNER JOIN ARTICLES A ON O.ARTICLE = A.ARTICLE
-
INNER JOIN TYPEPAYMENT TP ON O.PAYMENT_ID = TP.ID_PAYMENT
-
-
GO
-
-
-- 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,
-
[DataInsert] [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,
-
[DataInsert] [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,
-
[DataInsert] [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
-
-
0 20623 Sign in to post your reply or Sign up for a free account.
Similar topics
by: M. Mehta |
last post by:
It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?
Thanks
M. Mehta
Please follow my example below:
created 2 tables:
|
by: ljlevend2 |
last post by:
Is there any way to create a local server during runtime? For example, if
you add an existing Web Site to a Solution from within Visual Studio (by
right clicking the solution in the Solution...
|
by: qfchen |
last post by:
Hi
I'm using Visual Studio 2005 for database application. the database is MS access. I need to create a view in server explorer, when I right click on view folder, I can't see "Add New View" from...
|
by: zgh1970 |
last post by:
Hi, All,
We have a database on db2 udb aix.
The database has federated server to db2 mainframe.
Now We have already catalog database of db2/400 on the db2 UDB aix
server.
I tried to create new...
|
by: NithyaJai |
last post by:
Hai,
I got a doubt on creating a view?
Is that passible to create a view in MS Access 2000.......
if so plz help me to do it...
Thanks....
|
by: WayneW |
last post by:
I need to create a view of tables in 2 databases. Is this possible?
|
by: nirmalsingh |
last post by:
hai all, i can view only solution explorer , i cant view server explorer window. i am new to .net. what should i do to view server explorer?
...
|
by: rupinder03 |
last post by:
I want to create tree view in data grid in vb.net 2005.can anyone tell me hw to create it................its urgent.......plz reply soon..............
|
by: kensushi |
last post by:
Hi,
Im somewhat new to oracle SP writing. I need to do the following from the SP
1. execute the query
2. create a view and populate with the result of the above query
3 query that view and...
|
by: Liena |
last post by:
Hi, How can I redirect from one page to anothe in an specific view from a multiview control? How do I do this in ASP. NET using C#?
For ex:
prtected void Button_Click(object sender, EventArgs...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |