Transaction System Database using SQL
Samath
Posted by Samath
Last Updated: January 20, 2024
SQL

Create and select the Transaction System database for operation, with the CREATE DATABASE and the USE statement.

Create Database Transaction_System;

Use Transaction_System;

 

Create Salesperson Table and insert record into the table.

CREATE TABLE Salesperson(ID INT primary key NOT NULL,
						 s_Name varchar(50),
						 Age INT,
						 Salary float);

INSERT INTO Salesperson(ID,s_Name,Age,Salary)VALUES(1,'Abe',61,140000);
INSERT INTO Salesperson(ID,s_Name,Age,Salary)VALUES(2,'Bob',34,44000);
INSERT INTO Salesperson(ID,s_Name,Age,Salary)VALUES(5,'Chris',34,40000);
INSERT INTO Salesperson(ID,s_Name,Age,Salary)VALUES(7,'Dan',41,55000);
INSERT INTO Salesperson(ID,s_Name,Age,Salary)VALUES(8,'Ken',57,115000);
INSERT INTO Salesperson(ID,s_Name,Age,Salary)VALUES(11,'Joe',38,38000);

 

Create Customer Table and insert record into the table.

CREATE TABLE Customer(ID INT primary key NOT NULL,
						 c_Name varchar(50),
						 City varchar(50),
						 Industry_Type char);

INSERT INTO Customer(ID,c_Name,City,Industry_Type)VALUES(4,'Samsonic','pleasant','J');
INSERT INTO Customer(ID,c_Name,City,Industry_Type)VALUES(6,'Panasung','oaktown','J');
INSERT INTO Customer(ID,c_Name,City,Industry_Type)VALUES(7,'Samony','jackson','B');
INSERT INTO Customer(ID,c_Name,City,Industry_Type)VALUES(9,'Orange','jackson','B');

 

Create Orders Table and insert record into the table.

CREATE TABLE Orders(O_Number INT primary key NOT NULL,
						 order_date date,
						cust_id  INT ,
						salesperson_id INT,
						 Amount INT,
						foreign key(salesperson_id) REFERENCES Salesperson(ID),
						foreign key(cust_id) REFERENCES Customer(ID));

INSERT INTO Orders(O_Number,order_date,cust_id ,salesperson_id,Amount)VALUES(10,'8/2/6',4,2,540);
INSERT INTO Orders(O_Number,order_date,cust_id ,salesperson_id,Amount)VALUES(20,'1/5/9',4,8,1800);
INSERT INTO Orders(O_Number,order_date,cust_id ,salesperson_id,Amount)VALUES(30,'7/7/5',9,1,460);
INSERT INTO Orders(O_Number,order_date,cust_id ,salesperson_id,Amount)VALUES(40,'1/9/8',7,2,2400);
INSERT INTO Orders(O_Number,order_date,cust_id ,salesperson_id,Amount)VALUES(50,'2/3/8',6,7,600);
INSERT INTO Orders(O_Number,order_date,cust_id ,salesperson_id,Amount)VALUES(60,'3/2/8',6,7,720);
INSERT INTO Orders(O_Number,order_date,cust_id ,salesperson_id,Amount)VALUES(70,'5/6/8',9,7,150);

 

The names of all salespeople that have an order with Samsonic.

SElECT Salesperson.s_Name FROM Salesperson, Orders WHERE
Salesperson.ID = Orders.salesperson_id AND cust_id = '4';

 

The names of all salespeople that do not have any order with Samsonic.

SELECT Salesperson.s_Name FROM Salesperson 
WHERE Salesperson.ID NOT IN(
SELECT Orders.salesperson_id FROM Orders, Customer 
WHERE Orders.cust_id = Customer.ID 
AND Customer.c_Name = 'Samsonic');

 

The names of salespeople that have 2 or more orders.

SELECT s_Name
FROM Orders, Salesperson
WHERE Orders.salesperson_id = Salesperson.id 
GROUP BY s_Name, salesperson_id
HAVING COUNT( salesperson_id ) >1;

 

Write a SQL statement to insert rows into a table called highAchiever(Name, Age), where a salesperson must have a salary of 100,000 or greater to be included in the table.

CREATE TABLE highAchiever(ID INT primary key NOT NULL auto_increment,
						 H_name varchar(50),
						 Age INT);

insert into highAchiever (H_name, age) 
(select s_Name, age from salesperson where salary >= 100000);

 
Default Image