SQL Server interview questions and answers for freshers

Create a Database

First, we’ll create a new database. Let’s call it ShopDB.

# Create a new Database name : ShopDB
CREATE DATABASE ShopDB;
GO

Below is a database structure for Product, Charge, and Invoice tables in SQL Server. This includes the creation of tables with appropriate data types, primary keys, and foreign keys to establish relationships between the tables.

Product Table
The Product table will store information about each product.

# Create Product table
CREATE TABLE Product (
ProductID INT IDENTITY(1,1) PRIMARY KEY,
ProductName NVARCHAR(100) NOT NULL,
Category NVARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
CreatedDate DATETIME DEFAULT GETDATE()
);

Invoice Table
The Invoice table will store information about each invoice.

# Create Invoice Table
CREATE TABLE Invoice (
InvoiceID INT IDENTITY(1,1) PRIMARY KEY,
InvoiceNumber NVARCHAR(50) NOT NULL,
InvoiceDate DATETIME NOT NULL,
CustomerID INT NOT NULL,
TotalAmount DECIMAL(10, 2) NOT NULL,
CreatedDate DATETIME DEFAULT GETDATE()
);

Charge Table
The Charge table will store information about charges assigned to products in each invoice. It will have foreign keys linking to both the Product and Invoice tables.

#Create ChargeTable
CREATE TABLE Charge (
ChargeID INT IDENTITY(1,1) PRIMARY KEY,
ProductID INT NOT NULL,
InvoiceID INT NOT NULL,
ChargeAmount DECIMAL(10, 2) NOT NULL,
ChargeDate DATETIME DEFAULT GETDATE(),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID),
FOREIGN KEY (InvoiceID) REFERENCES Invoice(InvoiceID)
);

Relationships and Constraints
ProductID in Charge references ProductID in Product.
InvoiceID in Charge references InvoiceID in Invoice.

# Inserting Sample Products Data
INSERT INTO Product (ProductName, Category, Price)
VALUES
(‘Product A’, ‘Category 1’, 25.00),
(‘Product B’, ‘Category 2’, 50.00),
(‘Product C’, ‘Category 1’, 75.00);

 

# Inserting Sample Invoices Data
INSERT INTO Invoice (InvoiceNumber, InvoiceDate, CustomerID, TotalAmount)
VALUES
(‘INV001’, ‘2024-05-01’, 1, 150.00),
(‘INV002’, ‘2024-05-02’, 2, 75.00),
(‘INV003’, ‘2024-05-03’, 3, 200.00);

 

# Inserting Sample Charge
INSERT INTO Charge (ProductID, InvoiceID, ChargeAmount)
VALUES
(1, 1, 25.00),
(2, 1, 50.00),
(3, 1, 75.00),
(3, 2, 75.00),
(1, 3, 25.00),
(2, 3, 50.00),
(3, 3, 125.00);

So before going to question and answer , we have created database , tables and inserted sample data for our practice. Now we will use the same database and practice question and answer.

1. List all products available in the Product table.
SELECT * FROM Product;

2. Find the total number of products.
SELECT COUNT(*) AS TotalProducts FROM Product;

3. Retrieve all invoices generated in the last month.
SELECT * FROM Invoice
WHERE InvoiceDate >= DATEADD(month, -1, GETDATE());

4. List all charges assigned to a specific product (e.g., ProductID = 5).
SELECT * FROM Charge
WHERE ProductID = 5;

5. Calculate the total charge amount for a specific invoice (e.g., InvoiceID = 10).
SELECT SUM(ChargeAmount) AS TotalChargeAmount
FROM Charge
WHERE InvoiceID = 10;

6. Find the product with the highest price.
SELECT TOP 1 *
FROM Product
ORDER BY Price DESC;

7. List all products along with their assigned charges.
SELECT p.ProductID, p.ProductName, c.ChargeAmount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID;

8. Retrieve invoices and their total charge amounts.
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID;

9. Find all invoices for a specific customer (e.g., CustomerID = 3).
SELECT * FROM Invoice
WHERE CustomerID = 3;

10. Retrieve the product names and their prices for all products with a price greater than $100.
SELECT ProductName, Price
FROM Product
WHERE Price > 100;

11. List all products that have not been assigned any charges.
SELECT p.ProductID, p.ProductName
FROM Product p
LEFT JOIN Charge c ON p.ProductID = c.ProductID
WHERE c.ProductID IS NULL;

12. Find the average charge amount for all invoices.
SELECT AVG(ChargeAmount) AS AverageChargeAmount
FROM Charge;

13. Retrieve the details of the most recent invoice.
SELECT TOP 1 *
FROM Invoice
ORDER BY InvoiceDate DESC;

14. List all invoices that have a total charge amount greater than $500.
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
HAVING SUM(c.ChargeAmount) > 500;

15. Find the total revenue generated from all invoices.
SELECT SUM(c.ChargeAmount) AS TotalRevenue
FROM Charge c;

16. List all products, their prices, and the total charges assigned to each.
SELECT p.ProductID, p.ProductName, p.Price, SUM(c.ChargeAmount) AS TotalCharges
FROM Product p
LEFT JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName, p.Price;

17. Retrieve all products that have been assigned charges and display their total charge amount.
SELECT p.ProductID, p.ProductName, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName;

18. Find the invoice(s) with the highest total charge amount.
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
ORDER BY TotalChargeAmount DESC
FETCH FIRST 1 ROWS ONLY;