SQL Homework Solution on Data Manipulation

• 10th Nov, 2021
• 15:56 PM

DDL script:

DML Script:

 

CREATE TABLE [dbo].[Employee](
    [Employee_id] [int] IDENTITY(1,1) NOT NULL,
    [Employee_password] [varchar](50) NULL,
    [Store_id] [int] NULL,
    [Employee_Fname] [varchar](50) NULL,
    [Employee_Lname] [varchar](50) NULL,
    [Employee_Middle_Int] [varchar](50) NULL,
    [Employee_Position] [varchar](50) NULL,
    [Email_ID] [varchar](100) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Employee_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Store] FOREIGN KEY([Store_id])
REFERENCES [dbo].[Store] ([Store_id])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Store]
GO

CREATE TABLE [dbo].[Store](
    [Store_id] [int] IDENTITY(1,1) NOT NULL,
    [Store_name] [varchar](50) NULL,
    [Supplier_id] [int] NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
    [Store_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Store]  WITH CHECK ADD  CONSTRAINT [FK_Store_Supplier] FOREIGN KEY([Supplier_id])
REFERENCES [dbo].[Suppliers] ([Supplier_id])
GO

ALTER TABLE [dbo].[Store] CHECK CONSTRAINT [FK_Store_Supplier]
GO

CREATE TABLE [dbo].[Orders](
    [Order_id] [int] IDENTITY(1,1) NOT NULL,
    [Customer_id] [int] NULL,
    [Amount] [int] NULL,
    [Order_date] [date] NULL,
    [Ship_date] [date] NULL,
    [Status] [varchar](50) NULL,
    [Product_id] [int] NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [Order_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customer] FOREIGN KEY([Customer_id])
REFERENCES [dbo].[Customers] ([Cust_id])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customer]
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Products] FOREIGN KEY([Product_id])
REFERENCES [dbo].[Products] ([Product_id])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Products]
GO

CREATE TABLE [dbo].[Products](
    [Product_id] [int] IDENTITY(1,1) NOT NULL,
    [Product_name] [varchar](50) NULL,
    [Product_Description] [varchar](50) NULL,
    [Quantity] [int] NULL,
    [Discount] [int] NULL,
    [Product_Cost] [int] NULL,
    [Store_id] [int] NOT NULL,
    [Categoryid] [int] NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [Product_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Category] FOREIGN KEY([Categoryid])
REFERENCES [dbo].[Category] ([Categoryid])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Category]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Store] FOREIGN KEY([Store_id])
REFERENCES [dbo].[Store] ([Store_id])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Store]
GO

CREATE TABLE [dbo].[Customers](
    [Cust_id] [int] IDENTITY(1,1) NOT NULL,
    [Cust_Fname] [varchar](50) NULL,
    [Cust_Lname] [varchar](50) NULL,
    [Cust_Phone_number] [int] NULL,
    [Cust_Address] [varchar](200) NULL,
    [Cust_City] [varchar](50) NULL,
    [Cust_State_id] [int] NULL,
    [Cust_zipcode] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [Cust_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Customers]  WITH CHECK ADD  CONSTRAINT [FK_Customers_State] FOREIGN KEY([Cust_State_id])
REFERENCES [dbo].[State] ([State_id])
GO

ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FK_Customers_State]
GO

CREATE TABLE [dbo].[Suppliers](
    [Supplier_id] [int] IDENTITY(1,1) NOT NULL,
    [Supplier_Fname] [varchar](50) NULL,
    [Supplier_Lname] [varchar](50) NULL,
    [Supplier_city] [varchar](50) NULL,
    [Supplier_state] [int] NULL,
    [Supplier_zipcode] [varchar](50) NULL,
    [Phone_number] [int] NULL,
    [Email_id] [varchar](50) NULL,
 CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED 
(
    [Supplier_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Suppliers]  WITH CHECK ADD  CONSTRAINT [FK_Suppliers_State] FOREIGN KEY([Supplier_state])
REFERENCES [dbo].[State] ([State_id])
GO

ALTER TABLE [dbo].[Suppliers] CHECK CONSTRAINT [FK_Suppliers_State]
GO

CREATE TABLE [dbo].[State](
    [State_id] [int] IDENTITY(1,1) NOT NULL,
    [State_Name] [varchar](50) NULL,
 CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED 
(
    [State_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Category](
    [Categoryid] [int] IDENTITY(1,1) NOT NULL,
    [Categoryname] [varchar](50) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
    [Categoryid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


DML Script:

--Delete order by unique identifier
•    DELETE Orders WHERE Order_id=21;

-- update the price of the product by using unique identifier
•    UPDATE Products SET Product_Cost=60 WHERE Product_id=10;

-- Add minimum 3 of your data manipulation
•    UPDATE Products SET Product_Cost=Product_Cost*Discount WHERE Product_id=9
•    DELETE Employee WHERE Employee_Fname='Sarjan'
•    UPDATE Orders SET Status='Delivered' WHERE YEAR(Order_date)=2019
•    DELETE Orders WHERE DAY(Order_date)=15

Report Script:

-- Minimum 3 own created queries
•    SELECT Customer_id, [6] AS Oranges, [5] AS Pickles
FROM 
( SELECT Customer_id, Product_id, Amount
FROM Orders 
) ps
PIVOT
( SUM (Amount)
FOR Product_id IN ( [6], [5])
) AS pvt

•    SELECT Customers.Cust_Fname, SUM(Orders.Amount) FROM Orders 
JOIN Customers ON Customers.Cust_id=Orders.Customer_id 
JOIN Products ON Products.Product_id=Orders.Product_id 
WHERE Orders.Status='Delivered' 
GROUP BY Customers.Cust_Fname

•    SELECT SUM(Products.Quantity),Category.Categoryname AS QTY 
FROM Products JOIN Category ON Category.Categoryid=Products.Categoryid 
GROUP BY Category.Categoryname