
SQL Task 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