1. Create database employeedb.

Create table employee with attributes

emp_id, emp_name, emp_address, emp_salary, emp_phone. Insert 10
records. And display result.

Create the database and use it

CREATE DATABASE employeedb;
USE employeedb;

Create the employee table

CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
emp_address VARCHAR(100),
emp_salary DECIMAL(10, 2),
emp_phone VARCHAR(15)
);

Insert data into the employee table

INSERT INTO employee (emp_id, emp_name, emp_address, emp_salary, emp_phone)
VALUES
(1, 'Saurav Maharjan', 'Kalanki', 500000, 9822089942),
(2,'Ram Gurung','Naikap',25000,98028974579),
(3,'Amar Shrestha','Naikap',30000,9848749875),
(4,'Sita Magar','Naikap',1000000,98237598498),
(5,'Hari Lal','Bihar',5000,9858734857),
(7,'Rejina Khanal', 'Italy',300000000,17973947985),
(8,'Usha Goutame','kalanki',0,9192631770),
(9,'Manoj Magar','Satungal',35000,9844549875),
(10,'Migen Thapa','Naikap',30000,9848769475);

Display the records in the employee table

SELECT * FROM employee;

1
 2. Create Table students (s_id, s_name, s_roll, s_age, s_phone) and
insert 5 records. Add primary key constraints to s_id and unique
constraints to name. Display records and demonstrates.

CREATE TABLE students (

s_id INT PRIMARY KEY,
s_name VARCHAR(50) UNIQUE,
s_roll INT,
s_age INT,
s_phone VARCHAR(15)
);

INSERT INTO students (s_id, s_name, s_roll, s_age, s_phone) VALUES

(1, 'Saurav Maharjan', '12', 22, 9822089942),
(2,'Ram Gurung','24',21,98028974579),
(3,'Yubak Dahal','14',19,9848749875),
(4,'Rejina Khanal','41',22,17973947985),
(5,'Usha Goutame','5',23,9192631770);

SELECT * FROM students;

2
 3. Create above student table using default constraints in any column
and truncate the result and again insert the records and see the
result. Code to create table with default constraints:

CREATE TABLE students2 (

s_id INT PRIMARY KEY,
s_name VARCHAR(50) UNIQUE,
s_roll INT DEFAULT 0,
s_age INT DEFAULT 18,
s_phone VARCHAR(15) DEFAULT 'N/A'
);

TRUNCATE TABLE students2;

INSERT INTO students2 (s_id, s_name, s_roll, s_age, s_phone) VALUES

(1, 'Saurav Maharjan', '12', 22, 9822089942),
(2,'Ram Gurung','24',21,98028974579),
(3,'Yubak Dahal','14',19,9848749875),
(4,'Rejina Khanal','41',22,17973947985),
(5,'Usha Goutame','5',23,9192631770);

SELECT * FROM students;

3
4. Create table customer (c_id, c_name, c_address, c_phone) and orders
(o_id, o_name, o_qty, o_amt, c_id) and insert 10 records in customer and 5
records in orders tables respectively. Write query to retrieve records.
i. SQL to retrieve customers details.
ii. SQL to retrieve customer details whose order amount is maximum.
iii. SQL to find customers name whose address is Kathmandu.
iv. SQL to count orders of each customer.
v. SQL to delete records whose order amount > 4000.
vi. give 10% discount to those customers whose order amount is less than 3000
vii. truncate the records from customers tables.

CREATE TABLE customer (

c_id INT PRIMARY KEY,
c_name VARCHAR(50),
c_address VARCHAR(100),
c_phone VARCHAR(15));

INSERT INTO customer (c_id, c_name, c_address, c_phone)

VALUES
(1, 'Saurav Maharjan', 'Thankot', '9822089942'),
(2, 'Prabin Gurung', 'Naikap', '98028974579'),
(3, 'Amar Shrestha', 'Kathmandu', '9848749875'),
(4, 'Sita Magar', 'Kathmandu', '98237598498'),
(5, 'Hari Lal', 'Bihar', '9858734857'),
(7, 'Usha Goutame', 'Italy', '17973947985'),
(8, 'Yubak Dahal', 'Kalanki', '9192631770'),
(9, 'Manoj Magar', 'Satungal', '9844549875'),
(10, 'Ram Thapa', 'Kathmandu', '9848769475');

CREATE TABLE orders (

o_id INT PRIMARY KEY,
o_name VARCHAR(50),
o_qty INT,
o_amt DECIMAL(10, 2),
c_id INT,
FOREIGN KEY (c_id) REFERENCES customer(c_id));

INSERT INTO orders (o_id, o_name, o_qty, o_amt, c_id)

VALUES
(1, 'Macbook', 5, 80000.00, 7),
(2, 'PS5', 2, 75000.00, 10),
(3, 'Xiomi Note 9', 1, 29000.00, 4),
(4, 'iphone', 2, 250.00, 1),
(5, 'Wireless Adapter', 5, 2000.00, 2);

SELECT * FROM customer;

4
ii. Code to retrieve customers detail whose order amount is maximum.

SELECT c.*
FROM customer c
INNER JOIN (
SELECT c_id, MAX(o_amt) AS max_amt
FROM orders
GROUP BY c_id
) o ON c.c_id = o.c_id;

iii. To find customers name whose address is Kathmandu:

SELECT c_name
FROM customer
WHERE c_address = 'Kathmandu';

iv. To count orders of each customer:

SELECT c.c_name, COUNT(o.o_id) AS order_count

5
FROM customer c
LEFT JOIN orders o ON c.c_id = o.c_id
GROUP BY c.c_id, c.c_name;

v. To delete records whose order amount > 4000:

DELETE FROM orders
WHERE o_amt > 4000.00;
Output:

vi. To give 10% discount to those customers whose order amount is less than 3000:
UPDATE orders
SET o_amt = o_amt * 0.9
WHERE o_amt < 3000.00;

vii. To truncate the records from the customer table:

TRUNCATE TABLE customer;

6
5. Create table Customers and insert as following:

Id Name Age Address Salary

1 Ramesh 32 Kathmandu 2000
2 Khilan 25 Kupondole 1500
3 Kaushik 23 Kalimati 2000
4 Chaitali 25 Kalanki 6500
5 Hardik 27 Tripureshowr 8500
6 Komal 22 Teku 4500
7 Muffy 24 Caushala 10000

CREATE TABLE customers (

id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
address VARCHAR(100),
salary DECIMAL(10, 2)
);

INSERT INTO customers (id, name, age, address, salary)

VALUES
(1, 'Ramesh', 32, 'Kathamandu', 2000),
(2, 'Khilan', 25, 'Kupondole', 1500),
(3, 'Kaushik', 23, 'Kalimati', 2000),
(4, 'Chaitali', 25, 'Kalanki', 6500),
(5, 'Hardik', 27, 'Tripureshwor', 8500),
(6, 'Komal', 22, 'Teku', 4500),
(7, 'Muffy', 24, 'Gaushala', 10000);

i. SQL to display records whose salary is greater than 5000.

SELECT * FROM customers
WHERE salary > 5000;

7
ii. SQL to delete records whose address is Kathmandu or Teku.
DELETE FROM customers
WHERE address IN ('Kathamandu', 'Teku');

iii. SQL to update address to Kalanki whose age is greater than 25.
UPDATE customers
SET address = 'Kalanki'
WHERE age > 25;

iv. SQL to display records whose age is greater than 22 and address is Kathmadnu.
SELECT * FROM customers
WHERE age > 22 AND address = 'Kathamandu';

v. SQL to display name, age and address whose salary is less than 5000.
SELECT name, age, address FROM customers
WHERE salary < 5000;

8
6. Books Tables (bookid, titlem authorfirst,
authorlast,category,qty,price):
Create this table and insert 5 records.
• SQL to display top 3 records.
• SQL to display the records whose price is between 200 and 300.
• SQL to display the records of those books whose qty is greater than
those books whose price is greater than 500.
• SQL to update qty whose prices is greater than 500.
• SQL to delete those records qty is greater than 5 or prices is less than
3000.
CREATE TABLE Books (
bookid INT PRIMARY KEY,
title VARCHAR(100),
authorfirst VARCHAR(50),
authorlast VARCHAR(50),
category VARCHAR(50),
qty INT,
price DECIMAL(10, 2)
);

INSERT INTO Books (bookid, title, authorfirst, authorlast, category, qty, price)
VALUES
(1, 'Overcoming gravity', 'Steven', 'Low', 'Fitness', 1, 900.00),
(2, 'Dracula', 'Bram', 'Stoker', 'Fiction', 5, 200.00),
(3, 'Ghost in the wires ', 'Kevin', 'Mitnick', 'Biography', 15, 130.00),
(4, 'Social Engineering', 'Christopher', 'Hadnagy', 'Science', 20, 250.00),
(5, 'Outwitting The Devil', 'Napoleon', 'Hill', 'Self-help', 8, 600.00);

SELECT * FROM Books

LIMIT 3;

SQL to display the records whose price is between 200 and 300:
SELECT * FROM Books
WHERE price BETWEEN 200 AND 300;

9
SQL to display the records of those books whose qty is greater than those books whose
price is greater than 500:
SELECT * FROM Books
WHERE qty > (
SELECT qty FROM Books
WHERE price > 500
);

SQL to update qty whose prices is greater than 500:

UPDATE Books
SET qty = qty + 5
WHERE price > 500;

SQL to delete those records whose qty is greater than 5 or prices is less than 3000:
DELETE FROM Books
WHERE qty > 5 OR price < 3000.00;

10
7. Students (s_id, s_name, s_age, s_address, s_marks) Create this table and
insert 10 records:
• SQL to display records in ascending order using name attribute.
• SQL to find of mark according to name.
• SQL to display distinct mark in descending order
• SQL to count id according to address where count is greater than 3

CREATE TABLE Students (

s_id INT PRIMARY KEY,
s_name VARCHAR (50),
s_age INT,
s_address VARCHAR (100),
s_marks DECIMAL (5, 2)
);

INSERT INTO Students (s_id, s_name, s_age, s_address, s_marks)

VALUES
(1, 'Yubak Dahal', 'Kalanki', 50000, 9822089942),
(2,'Saurav Maharjan','Naikap',25000,98028974579),
(3,'Amar Shrestha','Naikap',30000,9848749875),
(4,'Sital Magar','Naikap',100000,98237598498),
(5,'Hira Lal','Bihar',5000,9858734857),
(7,'Usha Goutame’,'Italy',30000000,17973947985),
(8,'Achut Bhandaai','kalanki',50000,9192631770),
(9,'Manoj Magar','Satungal',35000,9844549875),
(10,'Miraj Thapa','Naikap',30000,9848769475);

SELECT * FROM Students

ORDER BY s_name ASC;

11
Code to find of mark according to name:
SELECT s_marks
FROM Students
WHERE s_name = 'Sita Magar’;

Code to display distinct mark in descending order:

SELECT DISTINCT s_marks
FROM Students
ORDER BY marks DESC;

Code to count id according to address where count is greater than 3:

SELECT s_address, COUNT(s_id) AS id_count
FROM Students
GROUP BY s_address
HAVING COUNT(s_id) > 3;

12
8. Inner join Operation in SQL

Table CUSTOMERS:

ID Name Age ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Mp 4500.00
7 Muffy 24 Indore 10000.00

CREATE TABLE CUSTOMERS (

ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT,
ADDRESS VARCHAR(100),
SALARY DECIMAL(10, 2)
);

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'MP', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

Table 2- ORDERS Table

OID DATE CUSTOMER_ID AMOUNT

102 2009-10-08 00:00:00 3 3000
100 2009-10-08 00:00:00 3 1500
101 2009-11-20 00:00:00 2 1560
103 2008-05-20 00:00:00 4 2060
CREATE TABLE ORDERS (
OID INT PRIMARY KEY,
DATE TIMESTAMP,
CUSTOMER_ID INT,
AMOUNT DECIMAL (10, 2),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID)
);

13
INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES
(102, '2009-10-08 00:00:00', 3, 3000),
(100, '2009-10-08 00:00:00', 3, 1500),
(101, '2009-11-20 00:00:00', 2, 1560),
(103, '2008-05-20 00:00:00', 4, 2060);

SQL code to create inner join:

SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

14
9. The following tables write a SQL query to find the salesperson and
customer who belongs to same city. Return Salesman, cust_name and city.

Sample table: Salesman

salesman_id | name| city | commission
------------------+----------------+----------------+----------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome |0.13
5003 | Lauson Hen | San Jose |0.12

Sample table: customer

customer_id |cust_name |city | grade | salesman_id
-----------------+---------------------+---------------------+--------+--------------
3002 | Nick Rimando | New York |100 |5001
3007 | Brad Davis | New York |200 |5001
3005 | Graham Zusi | California |200 |5002
3008 | Julian Green | London |300 |5002
3004 | Fabian Johnson | Paris |300 |5006
3009 | Geoff Cameron | Berlin |100 |5003
3003 | Jozy Altidor | Moscow |200 |5007
3001 | Brad Guzan | London | 300 |5005

CREATE TABLE Salesman (

salesman_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
commission DECIMAL(5, 2)
);

INSERT INTO Salesman (salesman_id, name, city, commission)

VALUES
(5001, 'James Hoog', 'New York', 0.15),
(5002, 'Nail Knite', 'Paris', 0.13),
(5005, 'Pit Alex', 'London', 0.11),
(5006, 'Mc Lyon', 'Paris', 0.14),
(5007, 'Paul Adam', 'Rome', 0.13),
(5003, 'Lauson Hen', 'San Jose', 0.12);

CREATE TABLE customer (

customer_id INT PRIMARY KEY,
cust_name VARCHAR(50),
city VARCHAR(50),
grade INT,
salesman_id INT
);

15
INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id)
VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3008, 'Julian Green', 'London', 300, 5002),
(3004, 'Fabian Johnson', 'Paris', 300, 5006),
(3009, 'Geoff Cameron', 'Berlin', 100, 5003),
(3003, 'Jozy Altidor', 'Moscow', 200, 5007),
(3001, 'Brad Guzan', 'London', NULL, 5005);

SQL Query to find the salesperson and customer who belongs to same city:
SELECT salesman.name AS "Salesman",
customer.cust_name, customer.city
FROM salesman,customer
WHERE salesman.city=customer.city;

16
10. From the following tables write a SQL query to find those orders where
the order amount exists between 500 and 2000. Return ord_no, purch_amt,
cust_name, city.

ord_no purch_amt ord_date customer_id Salesman_id

70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001

CREATE TABLE orders (

ord_no INT PRIMARY KEY,
purch_amt DECIMAL(10, 2),
ord_date DATE,
customer_id INT,
salesman_id INT
);

INSERT INTO orders (ord_no, purch_amt, ord_date, customer_id, salesman_id)

VALUES
(70001, 150.50, '2012-10-05', 3005, 5002),
(70009, 270.65, '2012-09-10', 3001, 5005),
(70002, 65.26, '2012-10-05', 3002, 5001),
(70004, 110.50, '2012-08-17', 3009, 5003),
(70007, 948.50, '2012-09-10', 3005, 5002),
(70005, 2400.60, '2012-07-27', 3007, 5001),
(70008, 5760.00, '2012-09-10', 3002, 5001),
(70010, 1983.43, '2012-10-10', 3004, 5006),
(70003, 2480.40, '2012-10-10', 3009, 5003),
(70012, 250.45, '2012-06-27', 3008, 5002),
(70011, 75.29, '2012-08-17', 3003, 5007),
(70013, 3045.60, '2012-04-25', 3002, 5001);

CREATE TABLE customers (

customer_id INT PRIMARY KEY,
cust_name VARCHAR(50),
City VARCHAR(50),
Grade INT,
salesman_id INT
);

17
INSERT INTO customers (customer_id, cust_name, City, Grade, salesman_id)
VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3008, 'Julian Green', 'London', 300, 5002),
(3004, 'Fabian Johnson', 'Paris', 300, 5006),
(3009, 'Geoff Cameron', 'Berlin', 100, 5003),
(3003, 'Jozy Altidor', 'Moscow', 200, 5007),
(3001, 'Brad Guzan', 'London', NULL, 5005);

SQL query to find those orders where order amount exists between 500 and 2000.
Return ord_no, purch_amt, cust_name, city:

SELECT a.ord_no,a.purch_amt,
b.cust_name,b.city
FROM orders a,customers b
WHERE a.customer_id=b.customer_id
AND a.purch_amt BETWEEN 500 AND 2000;

18
11. From the following tables write a SQL query to find the salesperson(s)
and the customer(s) he handles. Return Customer Name, city, Salesman,
commission.

Sample table: customer and salesman

customer_id cust_name city grade salesman_id

3002 Nick Rimando New York 100 5001
3007 Brad Davis New York 200 5001
3005 Graham Zusi California 200 5002
3008 Julian Green London 300 5002
3004 Fabian Johnson Paris 300 5006
3009 Geoff Cameron Berline 100 5003
3003 Jozy Altidor Moscow 200 5007
3001 Brad Guzan London 200 5005

Salesman_id name city Commission

5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5007 Paul Adam Rome 0.13
5003 Lauson Hen San Jose 0.12

CREATE TABLE customer (

customer_id INT PRIMARY KEY,
cust_name VARCHAR(50),
city VARCHAR(50),
grade INT,
salesman_id INT
);

INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id)

VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3008, 'Julian Green', 'London', 300, 5002),
(3004, 'Fabian Johnson', 'Paris', 300, 5006),
(3009, 'Geoff Cameron', 'Berlin', 100, 5003),
(3003, 'Jozy Altidor', 'Moscow', 200, 5007),
(3001, 'Brad Guzan', 'London', NULL, 5005);

CREATE TABLE salesman (

salesman_id INT PRIMARY KEY,

19
 name VARCHAR(50),
city VARCHAR(50),
commission DECIMAL(5, 3)
);

INSERT INTO salesman (salesman_id, name, city, commission)

VALUES
(5001, 'James Hoog', 'New York', 0.150),
(5002, 'Nail Knite', 'Paris', 0.135005),
(5005, 'Pit Alex', 'London', 0.110),
(5006, 'Mc Lyon', 'Paris', 0.140),
(5007, 'Paul Adam', 'Rome', 0.130),
(5003, 'Lauson Hen', 'San Jose', 0.120);

SQL query to find the salesperson(s) and the customer(s) he handle.

Return Customer Name, city, Salesman, commission

SELECT a.cust_name AS "Customer Name",
a.city, b.name AS "Salesman", b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id=b.salesman_id;

Output:

20
12. From the following tables write a SQL query to find those salespersons
who received a commission from the company more than 12%. Return
Customer Name, customer city, Salesman, commission.

Sample table: customer

customer_id cust_name city grade salesman_id

3002 Nick Rimando New York 100 5001
3007 Brad Davis New York 200 5001
3005 Graham Zusi California 200 5002
3008 Julian Green London 300 5002
3004 Fabian Johnson Paris 300 5006
3009 Geoff Cameron Berline 100 5003
3003 Jozy Altidor Moscow 200 5007
3001 Brad Guzan London 200 5005

Sample table: salesman

Salesman_id name city Commission

5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5007 Paul Adam Rome 0.13
5003 Lauson Hen San Jose 0.12

INSERT INTO customer (customer_id, cust_name, city, grade, salesman_id)

VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3008, 'Julian Green', 'London', 300, 5002),
(3004, 'Fabian Johnson', 'Paris', 300, 5006),
(3009, 'Geoff Cameron', 'Berlin', 100, 5003),
(3003, 'Jozy Altidor', 'Moscow', 200, 5007),
(3001, 'Brad Guzan', 'London', NULL, 5005);

INSERT INTO salesman (salesman_id, name, city, commission)

VALUES
(5001, 'James Hoog', 'New York', 0.15),
(5002, 'Nail Knite', 'Paris', 0.13),
(5005, 'Pit Alex', 'London', 0.11),
(5006, 'Mc Lyon', 'Paris', 0.14),
(5007, 'Paul Adam', 'Rome', 0.13),
(5003, 'Lauson Hen', 'San Jose', 0.12);

21
SQL query to find those salespersons who received a commission from the company
more than 12%. Return Customer Name, customer city, Salesman, commission:

SELECT a.cust_name AS "Customer Name",

a.city, b.name AS "Salesman", b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id=b.salesman_id
WHERE b.commission>.12;

22
13. From the following tables write a SQL query to find those salespersons
do not live in the same city where their customers live and received a
commission from the company more than 12%. Return Customer Name,
customer city, Salesman,salesman city, commission.

customer_id cust_name city grade salesman_id

3002 Nick Rimando New York 100 5001
3007 Brad Davis New York 200 5001
3005 Graham Zusi California 200 5002
3008 Julian Green London 300 5002
3004 Fabian Johnson Paris 300 5006
3009 Geoff Cameron Berline 100 5003
3003 Jozy Altidor Moscow 200 5007
3001 Brad Guzan London 200 5005

Salesman_id name city Commission

5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5007 Paul Adam Rome 0.13
5003 Lauson Hen San Jose 0.12

SQL query to find those salespersons do not live in the same city where their customers
live and received a commission from the company more than 12%. Return Customer
Name, customer city, Salesman,salesman city, commission.

SELECT a.cust_name AS "Customer Name",

a.city, b.name AS "Salesman", b.city,b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id=b.salesman_id
WHERE b.commission>.12
AND a.city<>b.city;

23
14. Write a SQL statement to make a join on the tables salesman, customer
and orders in such a form that the same column of each table will appear
once and only the relational rows will come.

Sample table: orders

ord_no purch_amt ord_date customer_id salesman_id

70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001

Sample table: customers

customer_id cust_name city grade salesman_id

3002 Nick Rimando New York 100 5001
3007 Brad Davis New York 200 5001
3005 Graham Zusi California 200 5002
3008 Julian Green London 300 5002
3004 Fabian Johnson Paris 300 5006
3009 Geoff Cameron Berline 100 5003
3003 Jozy Altidor Moscow 200 5007
3001 Brad Guzan London 200 5005

Sample table : salesman

Salesman_id name city Commission

5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5007 Paul Adam Rome 0.13
5003 Lauson Hen San Jose 0.12

24
SQL query to make a join on the tables salesman, customer and orders in such a form
that the same column of each table will appear once and only the relational rows will
come:

SELECT *
FROM orders
NATURAL JOIN customer
NATURAL JOIN salesman;

25
15. From the following tables write a SQL query to find those customers
whose grade less than 300. Return cust_name,customer city, grade,
Salesman, saleman city. The result should be ordered by ascending
customer_id.

Sample table: customer

customer_id cust_name city grade salesman_id

3002 Nick Rimando New York 100 5001
3007 Brad Davis New York 200 5001
3005 Graham Zusi California 200 5002
3008 Julian Green London 300 5002
3004 Fabian Johnson Paris 300 5006
3009 Geoff Cameron Berline 100 5003
3003 Jozy Altidor Moscow 200 5007
3001 Brad Guzan London 200 5005

Sample table: salesman

Salesman_id name city Commission

5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5007 Paul Adam Rome 0.13
5003 Lauson Hen San Jose 0.12

SQL query to find those customers whose grade less than 300. Return cust_name,
customer city, grade, Salesman, saleman city. The result should be ordered by ascending
customer_id:

SELECT
c.cust_name AS "Customer Name",
c.city AS "Customer City",
c.grade,
s.name AS "Salesman",
s.city AS "Salesman City"

FROM customer c
JOIN salesman s ON c.salesman_id = s.salesman_id
WHERE c.grade < 300
ORDER BY c.customer_id ASC;

26
27