Question

Database questions:

USE THE FOLLOWING SQL CODE TO SOLVE NEXT QUESTIONS:

CREATE TABLE ROBOT

(

Serial_no INT NOT NULL,

Model VARCHAR(20) NOT NULL,

Manufacturer VARCHAR(20) NOT NULL,

Price INT NOT NULL,

PRIMARY KEY (Serial_no)

);

INSERT INTO ROBOT VALUES (1, 'Scara','Epson', 23200);

INSERT INTO ROBOT VALUES (2, 'ASSISTA','Mitsubishi', 17500);

INSERT INTO ROBOT VALUES (3, 'Lego Mindstorm','NXT', 650);

INSERT INTO ROBOT VALUES (4, 'Yumi','ABB', 40000);

INSERT INTO ROBOT VALUES (5, 'Pepper','Foxconn', 1600);

INSERT INTO ROBOT VALUES (6, 'Humanoid','Honda', 30000);

SELECT * FROM ROBOT ;

/*===================================================*/

CREATE TABLE OPTIONS

(

Serial_Num INT NOT NULL,

Option_name VARCHAR(20) NOT NULL,

Price INT NOT NULL,

PRIMARY KEY (Serial_num, Option_name),

FOREIGN KEY (Serial_num) REFERENCES ROBOT(Serial_no)

);

INSERT INTO OPTIONS VALUES (1, 'Self power', 10000);

INSERT INTO OPTIONS VALUES (1, 'Sense surrounding', 20000);

INSERT INTO OPTIONS VALUES (5, 'Extra Speed', 30000);

INSERT INTO OPTIONS VALUES (2, 'Self power', 40000);

INSERT INTO OPTIONS VALUES (3, 'Motion Balance', 50000);

INSERT INTO OPTIONS VALUES (4, 'Custom look', 60000);

SELECT * FROM OPTIONS;

/*===================================================*/

CREATE TABLE SALESPERSON

(

Salesperson_id INT NOT NULL,

SName VARCHAR(20) NOT NULL,

Phone VARCHAR(20) NOT NULL,

PRIMARY KEY (Salesperson_id)

);

INSERT INTO SALESPERSON VALUES (111, 'Jameco Electronics', '0252354565');

INSERT INTO SALESPERSON VALUES (222,'RobotShop', '0231236455');

INSERT INTO SALESPERSON VALUES (333,'RoboRealm', '0287678790');

SELECT * FROM SALESPERSON ;

/*===================================================*/​

CREATE TABLE SALE

(

Salesperson_id INT NOT NULL,

Serial_no INT NOT NULL,

MyDate DATE NOT NULL,

Sale_Price INT NOT NULL,

PRIMARY KEY (Salesperson_id, Serial_no),

FOREIGN KEY (Salesperson_id) REFERENCES SALESPERSON (Salesperson_id),

FOREIGN KEY (Serial_no) REFERENCES ROBOT(Serial_no)

);

INSERT INTO SALE VALUES (111, 1, DATE'2018-9-23', 22000);

INSERT INTO SALE VALUES (222, 1, DATE'2019-9-22', 21400);

INSERT INTO SALE VALUES (111, 4, DATE'2017-7-21', 37000);

INSERT INTO SALE VALUES (333, 3, DATE'2020-6-14', 470);

SELECT * FROM SALE ;

Q1: Write a query to display all serial numbers of robots that don’t have sales using complex queries

Q2: Write a query that displays the sales price and dynamically assign a deal category to it as follows:

- If the sales price less than or equal 10,000 à accepted-deal price

- If the sales price ranges between 20,000 and 25,000 à average-deal price

- If the sales price greater than 50,000 à rejected-deal price

Answer 1

CREATE DATABASE ROBOTDB;

USE ROBOTDB;

CREATE TABLE ROBOT(
Serial_no INT NOT NULL,
Model VARCHAR(20) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
Price INT NOT NULL,
PRIMARY KEY (Serial_no)
);

INSERT INTO ROBOT VALUES (1, 'Scara','Epson', 23200);
INSERT INTO ROBOT VALUES (2, 'ASSISTA','Mitsubishi', 17500);
INSERT INTO ROBOT VALUES (3, 'Lego Mindstorm','NXT', 650);
INSERT INTO ROBOT VALUES (4, 'Yumi','ABB', 40000);
INSERT INTO ROBOT VALUES (5, 'Pepper','Foxconn', 1600);
INSERT INTO ROBOT VALUES (6, 'Humanoid','Honda', 30000);
SELECT * FROM ROBOT ;

/*===================================================*/
CREATE TABLE OPTIONS
(
Serial_Num INT NOT NULL,
Option_name VARCHAR(20) NOT NULL,
Price INT NOT NULL,
PRIMARY KEY (Serial_num, Option_name),
FOREIGN KEY (Serial_num) REFERENCES ROBOT(Serial_no)
);

INSERT INTO OPTIONS VALUES (1, 'Self power', 10000);
INSERT INTO OPTIONS VALUES (1, 'Sense surrounding', 20000);
INSERT INTO OPTIONS VALUES (5, 'Extra Speed', 30000);
INSERT INTO OPTIONS VALUES (2, 'Self power', 40000);
INSERT INTO OPTIONS VALUES (3, 'Motion Balance', 50000);
INSERT INTO OPTIONS VALUES (4, 'Custom look', 60000);
SELECT * FROM OPTIONS;

/*===================================================*/
CREATE TABLE SALESPERSON
(
Salesperson_id INT NOT NULL,
SName VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL,
PRIMARY KEY (Salesperson_id)
);

INSERT INTO SALESPERSON VALUES (111, 'Jameco Electronics', '0252354565');
INSERT INTO SALESPERSON VALUES (222,'RobotShop', '0231236455');
INSERT INTO SALESPERSON VALUES (333,'RoboRealm', '0287678790');
SELECT * FROM SALESPERSON ;

/*===================================================*/​
CREATE TABLE SALE
(
Salesperson_id INT NOT NULL,
Serial_no INT NOT NULL,
MyDate DATE NOT NULL,
Sale_Price INT NOT NULL,
PRIMARY KEY (Salesperson_id, Serial_no),
FOREIGN KEY (Salesperson_id) REFERENCES SALESPERSON (Salesperson_id),
FOREIGN KEY (Serial_no) REFERENCES ROBOT(Serial_no)
);

INSERT INTO SALE VALUES (111, 1, DATE'2018-9-23', 22000);
INSERT INTO SALE VALUES (222, 1, DATE'2019-9-22', 21400);
INSERT INTO SALE VALUES (111, 4, DATE'2017-7-21', 37000);
INSERT INTO SALE VALUES (333, 3, DATE'2020-6-14', 470);
SELECT * FROM SALE ;


/* Query 1:  */
SELECT Serial_no FROM ROBOT WHERE Serial_no not in (
        SELECT ROBOT.Serial_no from ROBOT, SALE WHERE ROBOT.Serial_no=SALE.Serial_no);

/* Query 2: */
SELECT Sale_Price,
        CASE WHEN Sale_Price <= 10000 THEN 'à accepted-deal price'
        WHEN Sale_Price >= 20000 and Sale_Price <= 25000 THEN 'à average-deal price'
        WHEN sale_Price > 50000 THEN 'à rejected-deal price'
        END AS DealCategory
FROM SALE;

Screesnshots of queries and output: