05

Use Joins

  1. Write a SQL statement to know which salesman are working for which customer.
select name as Salesperson, cust_name as Customer
from salesman inner join customer
on salesman.salesman_id=customer.salesman_id;
  1. Write a SQL statement to find the list of customers who appointed a salesman for their jobs who gets a commission from the company is more than 12%.
select customer.cust_name as Customer
from customer natural join salesman
where salesman.commission > 0.12;
  1. Write a SQL statement to find the list of customers who appointed a salesman for their jobs who does not live in the same city where their customer lives, and gets a commission is above 12%.
select distinct customer.cust_name as Customer
from customer join salesman
where salesman.city != customer.city
  and salesman.commission > 0.12;
  1. 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.
select *
from salesman natural join customer natural join orders;
  1. Write a SQL statement to make a list in ascending order for the customer who works either through a salesman or by own.
select customer.cust_name as Customer
from customer
order by customer.cust_name asc;
  1. Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice-versa.
select *
from customer left outer join salesman
on customer.salesman_id = salesman.salesman_id;

Create course, prereq tables & insert the values as given in the following tables

course

course_id title dept_name credits
BIO-301 Genetics Biology 4
CS-190 Game Design Comp. Sci. 4
CS-315 Robotics Comp. Sci. 3

prereq

course_id prereq_id
BIO-301 BIO-101
CS-190 CS-101
CS-347 CS-101 
use 20200198db;
set foreign_key_checks = 0;
drop table if exists course, prereq;
set foreign_key_checks  = 1;

create table course (
  course_id varchar(20),
  title varchar(20),
  dept_name varchar(20),
  credits int,

  primary key(course_id)
);

create table prereq (
  course_id varchar(20),
  prereq_id varchar(20),

  primary key(course_id, prereq_id)
##   foreign key(course_id) references course(course_id),
##   foreign key(prereq_id) references course(course_id)
);

insert into course values
("BIO-301", "Genetics" , "Biology", 4),
("CS-190", "Game Design", "Comp. Sci.", 4),
("CS-315", "Robotics", "Comp. Sci.", 3);

insert into prereq values
("BIO-301", "BIO-101"),
("CS-190", "CS-101"),
("CS-347", "CS-101");
  1. Perform left outer join to get the following output.
course_id title dept_name credits prereq_id
BIO-301 Genetics Biology 4 BIO-101
CS-190 Game Design Comp. Sci. 4 CS-101
CS-315 Robotics Comp. Sci. 3 null 
select *
from course
natural left outer join prereq;
  1. Perform right outer join to get the following output.
course_id title dept_name credits prereq_id
BIO-301 Genetics Biology 4 BIO-101
CS-190 Game Design Comp. Sci. 4 CS-101
CS-347 null null null CS-101 
select course_id, title, dept_name, credits, prereq_id
from course
natural right outer join prereq;
  1. Perform full outer join to get the following output.
course_id title dept_name credits prere_id
BIO-301 Genetics Biology 4 BIO-101
CS-190 Game Design Comp. Sci. 4 CS-101
CS-315 Robotics Comp. Sci. 3 null
CS-347 null null null CS-101 
select *
from course
full join prereq;
## only thing that worked
Last Updated: 2023-01-25 ; Contributors: AhmedThahir

Comments