Transaction System Database using SQL

Posted by Samath
  2193
December 25, 2014

 

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);