Scribd
Upload
52 views7 pages

SQL Database Assignment Example

The document describes the creation of 3 database tables - Salesman, Customer, and Orders - to store information about salesmen, customers, and orders. The Salesman table stores details of salesmen like name, city, commission, and salary. The Customer table links customers to salesmen and stores customer details. The Orders table links orders to customers and salesmen to track purchase amounts and dates. Sample data is inserted into each table to demonstrate the structure and relationships between the tables.

Uploaded by

Md Tariful Islam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
52 views7 pages

SQL Database Assignment Example

The document describes the creation of 3 database tables - Salesman, Customer, and Orders - to store information about salesmen, customers, and orders. The Salesman table stores details of salesmen like name, city, commission, and salary. The Customer table links customers to salesmen and stores customer details. The Orders table links orders to customers and salesmen to track purchase amounts and dates. Sample data is inserted into each table to demonstrate the structure and relationships between the tables.

Uploaded by

Md Tariful Islam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

Assignment of Database

Submitted By Submitted To

Name : Md. Tariful Islam Sheikh Moonwara Anjum Monisha


ID : 21225103521 Lecturer
Intake : 49 Dept. of CSE BUBT
Sec : 10
-- create database and table

-- Create the Salesman table

CREATE TABLE Salesman (

s_id INT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

city VARCHAR(255),

commission DECIMAL(10, 2),

salary DECIMAL(10, 2)

);

INSERT INTO Salesman (s_id, name, city, commission, salary)

VALUES

(1, 'John Smith', 'New York', 0.15, 50000.00),

(2, 'Alice Johnson', 'Los Angeles', 0.12, 48000.50),

(3, 'Robert Brown', 'Chicago', 0.14, 52000.75),

(4, 'Mary Davis', 'Houston', 0.13, 49000.25),

(5, 'Michael Lee', 'San Francisco', 0.16, 51000.00);

-- Create the Customer table

CREATE TABLE Customer (

c_id INT PRIMARY KEY,

c_name VARCHAR(255) NOT NULL,

city VARCHAR(255),

grade INT,

s_id INT,

FOREIGN KEY (s_id) REFERENCES Salesman(s_id)

);
INSERT INTO Customer (c_id, c_name, city, grade, s_id)

VALUES

(1, 'Alice Johnson', 'Los Angeles', 4, 2),

(2, 'Bob Smith', 'New York', 3, 1),

(3, 'Charlie Brown', 'Chicago', 2, 3),

(4, 'David Davis', 'Houston', 3, 4),

(5, 'Eve Williams', 'San Francisco', 4, 5);

-- Create the Order table

CREATE TABLE Orders (

o_no INT PRIMARY KEY,

purchase_amount DECIMAL(10, 2),

order_date DATE,

c_id INT,

s_id INT,

FOREIGN KEY (c_id) REFERENCES Customer(c_id),

FOREIGN KEY (s_id) REFERENCES Salesman(s_id)

);

INSERT INTO Orders (o_no, purchase_amount, order_date, c_id, s_id)

VALUES

(101, 250.00, '2023-07-15', 1, 1),

(102, 150.50, '2023-07-20', 2, 2),

(103, 300.75, '2023-07-25', 3, 3),

(104, 175.25, '2023-07-30', 4, 4),

(105, 200.00, '2023-08-05', 5, 5);


INSERT INTO Orders (o_no, purchase_amount, order_date, c_id, s_id)

VALUES

(113, 250.00, '2023-07-15', 1, 1),

(1001, 150.50, '2023-07-20', 2, 2),

(1060, 300.75, '2023-07-25', 3, 3),

(106, 175.25, '2023-07-30', 4, 4),

(1017, 200.00, '2023-08-05', 5, 5);

1)

SELECT name, city

FROM Salesman

WHERE city = 'Chittagong';

2)

SELECT C.c_name, O.o_no, O.purchase_amount

FROM Customer C

JOIN Orders O ON C.c_id = O.c_id

WHERE O.order_date NOT IN ('2023-07-27', '2023-07-29');


3)

SELECT *

FROM Salesman

WHERE commission BETWEEN 0.12 AND 0.14;


4)

UPDATE Customer

SET c_name = 'Karim'

WHERE c_name = 'Rahim';

5)

SELECT name

FROM Salesman

ORDER BY salary DESC

LIMIT 1 OFFSET 1;
SELECT * FROM salesman;

SELECT * FROM customer;

SELECT * FROM orders;

You might also like