dbms lab5

/*NAME : B.MAHIDHAR REDDY
ROLL NO : CS20B1091 */

------- QUESTION 1 --------------

SHOW DATABASES;
CREATE DATABASE lab5;
USE lab5;
SHOW TABLES;

CREATE TABLE Agent (
AGENT_CODE VARCHAR(5) PRIMARY KEY NOT NULL,
AGENT_NAME VARCHAR(20) NOT NULL,
WORKING_AREA VARCHAR(20) NOT NULL,
COMMISSION FLOAT,
PHONE_NO INT,
COUNTRY VARCHAR(10)
);

INSERT INTO Agent VALUES ('Ac001', 'Ramesh', 'Bangalore', '0.15', '0331234567', 'India');
INSERT INTO Agent VALUES ('Ac002', 'Dinesh', 'Bangalore', '0.25', '0331234568', NULL);
INSERT INTO Agent VALUES('Ac003', 'Suresh', 'Mumbai', '0.35', '0331234569', 'London');
INSERT INTO Agent VALUES ('Ac004', 'Kamlesh', 'New Jersey', '0.68', '0331234564', NULL);
INSERT INTO Agent VALUES('Ac005', 'Kartik', 'Chennai', '0.73', '0331234563', 'India');

SELECT * FROM Agent;

CREATE TABLE Orders(
ORD_NUM INT PRIMARY KEY NOT NULL,
ORD_AMOUNT INT NOT NULL,
ADVANCE_AMOUNT INT,
ORD_DATE DATE NOT NULL,
CUST_CODE VARCHAR(4) NOT NULL,
AGENT_CODE VARCHAR(5) NOT NULL,
DESCRIPTION VARCHAR(20)
);

INSERT INTO Orders VALUES('004', '200', '3000', '2020-08-15', 'C004', 'Ac001', 'Masala Kulcha');
INSERT INTO Orders VALUES ('007', '600', '5000', '2020-09-17', 'C006', 'Ac003', 'Biryani');
INSERT INTO Orders VALUES ('008', '700', '100', '2019-02-19', 'C007', 'Ac005', NULL);
INSERT INTO Orders VALUES ('009', '10000', '600', '2010-03-21', 'C009', 'Ac008', 'Masala Dosa');
INSERT INTO Orders VALUES ('010', '20', '600', '2012-04-21', 'C006', 'Ac005', NULL);

SELECT * FROM Orders;

-- a). Find ord_num, ord_amount, ord_date, cust_code and agent_code
-- from the table Orders working_area of Agent table must be Bangalore.
SELECT ORD_NUM, ORD_AMOUNT, ORD_DATE, CUST_CODE, AGENT_CODE FROM Orders WHERE AGENT_CODE IN (SELECT AGENT_CODE FROM Agent WHERE WORKING_AREA="Bangalore");

-- b). Retrive ord_num, ord_amount, cust_code and agent_code from the table orders
-- where the agent_code of orders table must be the same agent_code of agents table
-- and agent_name of agents table must be Ramesh.
SELECT ORD_NUM, ORD_AMOUNT, CUST_CODE, AGENT_CODE FROM Orders WHERE AGENT_CODE IN (SELECT AGENT_CODE FROM Agent WHERE AGENT_NAME="Ramesh");

----------------- QUESTION 2 ---------------------

CREATE DATABASE lab5_2;
USE LAB5_2;
SHOW TABLES;

CREATE TABLE Salesman(
salesman_id VARCHAR(10) PRIMARY KEY NOT NULL,
name VARCHAR(20) NOT NULL,
city VARCHAR(10) NOT NULL,
commission FLOAT
);

INSERT INTO Salesman VALUES ('si123@06', 'Lakshmi', 'Kolkata', '0.5');
INSERT INTO Salesman VALUES ('si123@09', 'Ganesh', 'London', '0.6');
INSERT INTO Salesman VALUES ('si123@90', 'Dinesh', 'London', '0.3');
INSERT INTO Salesman VALUES ('si123@10', 'Joseph', 'Chennai', '0.6');
INSERT INTO Salesman VALUES ('si123@19', 'Mahesh', 'Hyderabad', '0.65');
INSERT INTO Salesman VALUES ('si123@26', 'Paul Adam', 'London', '0.1');
INSERT INTO Salesman VALUES ('si123@67', 'Rahul', 'Delhi', '0.4');

SELECT * FROM Salesman;

CREATE TABLE Orders(
ord_no INT PRIMARY KEY NOT NULL,
purch_amt INT NOT NULL,
ord_date DATE,
customer_id VARCHAR(5),
salesman_id VARCHAR(10),
FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id)
);

INSERT INTO Orders VALUES ('123', '600', '2010-08-20', '003cd', 'si123@19');
INSERT INTO Orders VALUES ('576', '750', '2018-02-20', '004cd', 'si123@19');
INSERT INTO Orders VALUES ('579', '800', '2012-05-20', '004cd', 'si123@26');
INSERT INTO Orders VALUES ('600', '60000', '2021-01-20', '006cd', 'si123@10');
INSERT INTO Orders VALUES ('700', '745', '2021-01-26', '007cd', 'si123@09');
INSERT INTO Orders VALUES ('800', '860', '2019-01-29', '007cd', 'si123@26');

SELECT * FROM Orders;

-- a). Display all the orders from the orders table issued by the salesman 'Paul Adam'.
SELECT * FROM Orders WHERE salesman_id IN (SELECT salesman_id FROM Salesman WHERE name="Paul Adam");

-- b). Display all the orders for the salesman who belongs to the city London.
SELECT * FROM Orders WHERE salesman_id IN (SELECT salesman_id FROM Salesman WHERE city="London");