1.
Employee Table
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Gender NVARCHAR(10),
DateOfBirth DATE,
PhoneNumber NVARCHAR(15),
Email NVARCHAR(100),
HireDate DATE,
Department NVARCHAR(50),
Salary DECIMAL(10, 2)
);
2. Product Table
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(100),
Category NVARCHAR(50),
Manufacturer NVARCHAR(100),
Price DECIMAL(10, 2),
StockQuantity INT,
DateAdded DATE,
ExpiryDate DATE,
SupplierID INT,
Description NVARCHAR(MAX)
);
3. Student Table
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Gender NVARCHAR(10),
DateOfBirth DATE,
Address NVARCHAR(200),
PhoneNumber NVARCHAR(15),
Email NVARCHAR(100),
EnrollmentDate DATE,
Course NVARCHAR(100)
);
4. Order Table
CREATE TABLE [Order] (
OrderID INT PRIMARY KEY,
CustomerID INT,
ProductID INT,
OrderDate DATE,
Quantity INT,
TotalAmount DECIMAL(10, 2),
ShippingAddress NVARCHAR(200),
ShippingDate DATE,
Status NVARCHAR(50),
PaymentMethod NVARCHAR(50)
);
5. Book Table
CREATE TABLE Book (
BookID INT PRIMARY KEY,
Title NVARCHAR(200),
Author NVARCHAR(100),
Publisher NVARCHAR(100),
PublishedDate DATE,
ISBN NVARCHAR(20),
Price DECIMAL(10, 2),
Genre NVARCHAR(50),
Pages INT,
Stock INT
);
6. Customer Table
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Email NVARCHAR(100),
PhoneNumber NVARCHAR(15),
Address NVARCHAR(200),
City NVARCHAR(50),
PostalCode NVARCHAR(10),
Country NVARCHAR(50),
RegistrationDate DATE
);
7. Invoice Table
CREATE TABLE Invoice (
InvoiceID INT PRIMARY KEY,
CustomerID INT,
InvoiceDate DATE,
DueDate DATE,
TotalAmount DECIMAL(10, 2),
Discount DECIMAL(5, 2),
Tax DECIMAL(5, 2),
Status NVARCHAR(50),
PaymentDate DATE,
Remarks NVARCHAR(MAX)
);
8. Supplier Table
CREATE TABLE Supplier (
SupplierID INT PRIMARY KEY,
SupplierName NVARCHAR(100),
ContactName NVARCHAR(50),
PhoneNumber NVARCHAR(15),
Email NVARCHAR(100),
Address NVARCHAR(200),
City NVARCHAR(50),
Country NVARCHAR(50),
PostalCode NVARCHAR(10),
EstablishedDate DATE
);
9. Appointment Table
CREATE TABLE Appointment (
AppointmentID INT PRIMARY KEY,
CustomerID INT,
EmployeeID INT,
AppointmentDate DATE,
AppointmentTime TIME,
Service NVARCHAR(100),
Location NVARCHAR(200),
Status NVARCHAR(50),
CreatedDate DATE,
Notes NVARCHAR(MAX)
);
10. Vehicle Table
CREATE TABLE Vehicle (
VehicleID INT PRIMARY KEY,
Make NVARCHAR(50),
Model NVARCHAR(50),
Year INT,
VIN NVARCHAR(20),
LicensePlate NVARCHAR(15),
OwnerID INT,
RegistrationDate DATE,
Color NVARCHAR(30),
InsuranceProvider NVARCHAR(100)
);
1. **Employee Table**
INSERT INTO Employee (EmployeeID, FirstName, LastName, Gender, DateOfBirth,
PhoneNumber, Email, HireDate, Department, Salary)
VALUES (1, 'John', 'Doe', 'Male', '1990-01-15', '1234567890',
'john.doe@example.com', '2022-01-01', 'IT', 75000.00);
2. **Product Table**
INSERT INTO Product (ProductID, ProductName, Category, Manufacturer, Price,
StockQuantity, DateAdded, ExpiryDate, SupplierID, Description)
VALUES (101, 'Laptop', 'Electronics', 'Dell', 899.99, 50, '2023-05-15', '2025-05-
15', 1, '15-inch display, 8GB RAM, 256GB SSD');
3. **Student Table**
INSERT INTO Student (StudentID, FirstName, LastName, Gender, DateOfBirth, Address,
PhoneNumber, Email, EnrollmentDate, Course)
VALUES (1001, 'Jane', 'Smith', 'Female', '2001-04-22', '123 Main St, Cityville',
'0987654321', 'jane.smith@example.com', '2023-09-01', 'Computer Science');
4. **Order Table**
INSERT INTO [Order] (OrderID, CustomerID, ProductID, OrderDate, Quantity,
TotalAmount, ShippingAddress, ShippingDate, Status, PaymentMethod)
VALUES (5001, 1, 101, '2023-10-05', 2, 1799.98, '456 Elm St, Cityville', '2023-10-
07', 'Shipped', 'Credit Card');
5. **Book Table**
INSERT INTO Book (BookID, Title, Author, Publisher, PublishedDate, ISBN, Price,
Genre, Pages, Stock)
VALUES (201, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Scribner', '1925-04-10',
'9780743273565', 10.99, 'Fiction', 180, 100);
6. **Customer Table**
INSERT INTO Customer (CustomerID, FirstName, LastName, Email, PhoneNumber, Address,
City, PostalCode, Country, RegistrationDate)
VALUES (301, 'Alice', 'Johnson', 'alice.johnson@example.com', '1122334455', '789
Oak St', 'Metropolis', '12345', 'USA', '2023-08-01');
7. **Invoice Table**
INSERT INTO Invoice (InvoiceID, CustomerID, InvoiceDate, DueDate, TotalAmount,
Discount, Tax, Status, PaymentDate, Remarks)
VALUES (9001, 301, '2023-10-01', '2023-10-30', 500.00, 10.00, 25.00, 'Paid', '2023-
10-10', 'Thank you for your prompt payment.');
8. **Supplier Table**
INSERT INTO Supplier (SupplierID, SupplierName, ContactName, PhoneNumber, Email,
Address, City, Country, PostalCode, EstablishedDate)
VALUES (501, 'Tech Supplies Inc.', 'Bob Williams', '2233445566',
'bob.williams@techsupplies.com', '101 Pine St', 'TechCity', 'USA', '54321', '2010-
07-15');
9. **Appointment Table**
INSERT INTO Appointment (AppointmentID, CustomerID, EmployeeID, AppointmentDate,
AppointmentTime, Service, Location, Status, CreatedDate, Notes)
VALUES (10001, 301, 1, '2023-11-05', '10:30:00', 'Consultation', 'Office 123',
'Scheduled', '2023-10-15', 'Follow-up required');
10. **Vehicle Table**
INSERT INTO Vehicle (VehicleID, Make, Model, Year, VIN, LicensePlate, OwnerID,
RegistrationDate, Color, InsuranceProvider)
VALUES (1001, 'Toyota', 'Camry', 2020, '1HGBH41JXMN109186', 'ABC123', 301, '2020-
05-20', 'Silver', 'AllState Insurance');
General INSERT INTO Syntax
For all these tables, the syntax follows this pattern:
INSERT INTO TableName (Column1, Column2, Column3, ...)
VALUES (Value1, Value2, Value3, ...);
1. **Employee Table**
UPDATE Employee
SET PhoneNumber = '9876543210', Email = 'new.john.doe@example.com', Salary =
80000.00
WHERE EmployeeID = 1;
2. **Product Table**
UPDATE Product
SET Price = 950.99, StockQuantity = 45
WHERE ProductID = 101;
3. **Student Table**
UPDATE Student
SET Address = '456 New St, Cityville', Course = 'Data Science'
WHERE StudentID = 1001;
4. **Order Table**
UPDATE [Order]
SET Status = 'Delivered', ShippingDate = '2023-10-06'
WHERE OrderID = 5001;
5. **Book Table**
UPDATE Book
SET Price = 12.99, Stock = 90
WHERE BookID = 201;
6. **Customer Table**
UPDATE Customer
SET City = 'Uptown', PhoneNumber = '9988776655'
WHERE CustomerID = 301;
7. **Invoice Table**
UPDATE Invoice
SET TotalAmount = 550.00, Tax = 27.50, Discount = 12.00
WHERE InvoiceID = 9001;
8. **Supplier Table**
UPDATE Supplier
SET Email = 'updated.bob.williams@techsupplies.com', PhoneNumber = '4455667788'
WHERE SupplierID = 501;
9. **Appointment Table**
UPDATE Appointment
SET Status = 'Completed', Notes = 'Appointment successfully completed.'
WHERE AppointmentID = 10001;
10. **Vehicle Table**
UPDATE Vehicle
SET Color = 'Black', InsuranceProvider = 'State Farm'
WHERE VehicleID = 1001;
General UPDATE Syntax
The general format of the UPDATE statement is as follows:
UPDATE TableName
SET Column1 = NewValue1, Column2 = NewValue2, ...
WHERE Condition;
- **TableName**: The table you want to update.
- **SET Column = Value**: Specifies the column(s) and the new value(s).
- **WHERE**: Identifies which row(s) to update.
1. **Employee Table**
DELETE FROM Employee
WHERE EmployeeID = 1;
2. **Product Table**
DELETE FROM Product
WHERE ProductID = 101;
3. **Student Table**
DELETE FROM Student
WHERE StudentID = 1001;
4. **Order Table**
DELETE FROM [Order]
WHERE OrderID = 5001;
5. **Book Table**
DELETE FROM Book
WHERE BookID = 201;
6. **Customer Table**
DELETE FROM Customer
WHERE CustomerID = 301;
7. **Invoice Table**
DELETE FROM Invoice
WHERE InvoiceID = 9001;
8. **Supplier Table**
DELETE FROM Supplier
WHERE SupplierID = 501;
9. **Appointment Table**
DELETE FROM Appointment
WHERE AppointmentID = 10001;
10. **Vehicle Table**
DELETE FROM Vehicle
WHERE VehicleID = 1001;
General DELETE Syntax
The general format of the DELETE statement is as follows:
DELETE FROM TableName
WHERE Condition;
- **TableName**: The table from which you want to delete rows.
- **WHERE**: Identifies which row(s) to delete.
DELETE FROM TableName; -- This will delete all rows in the table!
