Scribd Logo
Upload

Welcome to Scribd!

  • 38 views

    Exercise 5

    Uploaded by

    Akhash Aravindh
    The document contains examples of SQL queries used to create tables, insert data, and perform joins on multiple tables. Some key points: - Tables are created for salesmen, customers, orders, employees, departments, and salespersons. - Data is inserted into the tables. - Joins are performed between the tables to retrieve related data like salesperson and customer in the same city, employee details with department, and order details with customer, salesperson, etc. - Aggregate functions like COUNT are used to find departments with more than two employees. - Outer joins are used to generate reports with customer details even if they don't have orders.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Exercise 5

    Uploaded by

    Akhash Aravindh
    38 views14 pages
    The document contains examples of SQL queries used to create tables, insert data, and perform joins on multiple tables. Some key points: - Tables are created for salesmen, customers, orders, employees, departments, and salespersons. - Data is inserted into the tables. - Joins are performed between the tables to retrieve related data like salesperson and customer in the same city, employee details with department, and order details with customer, salesperson, etc. - Aggregate functions like COUNT are used to find departments with more than two employees. - Outer joins are used to generate reports with customer details even if they don't have orders.

    Original Title

    EXERCISE-5

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains examples of SQL queries used to create tables, insert data, and perform joins on multiple tables. Some key points: - Tables are created for salesmen, customers, orders, employees, departments, and salespersons. - Data is inserted into the tables. - Joins are performed between the tables to retrieve related data like salesperson and customer in the same city, employee details with department, and order details with customer, salesperson, etc. - Aggregate functions like COUNT are used to find departments with more than two employees. - Outer joins are used to generate reports with customer details even if they don't have orders.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    38 views14 pages

    Exercise 5

    Uploaded by

    Akhash Aravindh
    The document contains examples of SQL queries used to create tables, insert data, and perform joins on multiple tables. Some key points: - Tables are created for salesmen, customers, orders, employees, departments, and salespersons. - Data is inserted into the tables. - Joins are performed between the tables to retrieve related data like salesperson and customer in the same city, employee details with department, and order details with customer, salesperson, etc. - Aggregate functions like COUNT are used to find departments with more than two employees. - Outer joins are used to generate reports with customer details even if they don't have orders.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 14

    EXERCISE -5

    Creating the table:

    create table salesman(salesman_id int primary key,name varchar(30),city


    varchar(30),commission float)

    Inserting the values:

    insert into salesman values(5001 ,'JamesHoog ','NewYork',0.15);


    insert into salesman values(5002,'NailKnite','Paris',0.13);
    insert into salesman values(5005,'PitAlex','London',0.11);
    insert into salesman values(5006,'McLyon','Paris',0.14);
    insert into salesman values(5003,'LausonHen','SanJose',0.12);
    insert into salesman values(5007,'PaulAdam','Rome',0.13);

    select * from salesman


    create table customer(customer_id int primary key,cust_name varchar(30),city
    varchar(30),grade int,salesman_id int)

    insert into customer values(3002,'Nick Rimando','newyork',100,5001)


    insert into customer values(3007,'Brad Davis','newyork',200,5001)
    insert into customer values(3005,'Graham Zusi','california',200,5002)
    insert into customer values(3008,'Julian Green','london',300,5002)
    insert into customer values(3004,'Fabian Johnson','paris',300,5006)
    insert into customer values(3009,'Geoff Cameron','berlin',100,5003)
    insert into customer values(3003,'Jozy Altidor','mascow',200,5007)
    insert into customer values(3001,'Brad Guzan','london','',5005)

    select * from customer


    1.From the following tables write a SQL query to find the salesperson and customer who
    reside in the same city. Return Salesman, cust_name and city.

    select salesman.name,customer.cust_name,customer.city from salesman,customer where


    salesman.city=customer.city;

    2.Write a SQL query to combine each row of the salesman table with each row of the
    customer table

    select * from salesman cross join customer


    3.Write a SQL statement to create a Cartesian product between salesperson and customer, i.e.
    each salesperson will appear for all customers and vice versa for that salesperson who
    belongs to that city.

    select * from salesman cross join customer where salesman.city=customer.city;

    4.Write a SQL statement to create a Cartesian product between salesperson and customer, i.e.
    each salesperson will appear for every customer and vice versa for those salesmen who
    belong to a city and customers who require a grade

    select * from salesman cross join customer where salesman.city is not null and
    customer.grade>0;
    5.Write a SQL statement to make a Cartesian product between salesman and customer i.e.
    each salesman will appear for all customers and vice versa for those salesmen who must
    belong to a city which is not the same as his customer and the customers should have their
    own grade.
    select * from salesman cross join customer where salesman.city!=customer.city and
    customer.grade>0;
    Creating the table edetails:

    create table edetails (emp_idno int, emp_fname varchar(30), emp_lname varchar(20),


    emp_dept int);
    insert into edetails values(127323 ,'Michale','Robbin' ,57);
    insert into edetails values(526689,' Carlos','Snares' ,63);
    Inserting the values:

    insert into edetails values(843795, 'Enric' , 'Dosio' , 57);


    insert into edetails values(328717, 'Jhon ' ,' Snares ', 63);
    insert into edetails values(444527 ,'Joseph' , 'Dosni', 47);
    insert into edetails values(659831,' Zanifer', 'Emily', 47);
    insert into edetails values(847674,' Kuleswar ','Sitaraman ',57);
    insert into edetails values(748681,' Henrey','Gabriel' , 47);
    insert into edetails values(555935,'Alex',' Manuel' , 57);
    insert into edetails values(539569,'George','Mardy',27);
    insert into edetails values(733843,' Mario' ,' Saule' , 63);
    insert into edetails values(631548,'Alan', 'Snappy' ,27);
    insert into edetails values(839139, 'Maria','Foster' ,57);
    select *from edetails;

    Creating the table emdept:

    create table emdept(dept_code int,dept_name varchar(20),dept_allot int);

    Inserting the values:

    insert into emdept values(57, 'IT',65000);


    insert into emdept values(63,'Finance',15000);
    insert into emdept values(47,' HR ', 240000);
    insert into emdept values(27,' RD ',55000);
    insert into emdept values(89 ,'QC',75000);

    select *from emdept


    6.From the following tables write a SQL query to find the departments with budgets more
    than Rs. 50000 and display the first name and last name of employees.

    select emp_fname,emp_lname,emp_dept from edetails where emp_dept IN (select dept_code


    from emdept where dept_allot>50000);
    7.From the following tables write a SQL query to display all the data of employees including
    their department

    select * from edetails INNER JOIN emdept ON edetails.emp_dept=emdept.dept_code;

    8.From the following tables write a SQL query to find the names of departments where more
    than two employees are employed. Return dpt_name.

    select emp_dept,dept_name from edetails INNER JOIN emdept ON emp_dept=dept_code


    GROUP BY emp_dept,dept_name HAVING COUNT(*)>2;

    9.From the following tables write a SQL query to display the first and last names of each
    employee, as well as the department name and sanction amount.
    select emp_fname,emp_lname,dept_name,dept_allot from edetails INNER JOIN emdept ON
    edetails.emp_dept=emdept.dept_code;

    Creating the customer table:


    create table cust1(customer_id int,cust_name varchar(30),city varchar(30),grade
    int,salesman_id int);

    Inserting the values:

    insert into cust1 values(3002,'Nick Rimando','New York',100,5001);


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

    select * from cust1


    Creating the table order:

    create table ord(ord_no int,purch_amt float,ord_date date,customer_id int,salesman_id


    int);

    Inserting the values:

    insert into ord values(70001,150.5,'2012-10-05',3005,5002);


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

    Creating the table sales:


    create table sales(salesman_id int,name varchar(30),city varchar(30),commission
    float);

    insert into sales values(5001,'James Hoog','New york',0.15);


    insert into sales values(5002,'Nail Knite','Paris',0.13);
    insert into sales values(5005,'Pit Alex','London',0.11);
    insert into sales values(5006,'Mc Lyon','Paris',0.14);
    insert into sales values(5003,'Lauson Hen','San Jose',0.12);
    insert into sales values(5007,'Paul Adam','Rome',0.13);

    10.From the following tables write a SQL query to find the details of an order. Return
    ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission.

    Select ord_no,ord_date,purch_amt,cust_name,grade,commission,name from


    ord,cust1,sales;
    11.From the following tables write a SQL query to list all salespersons along with customer
    name, city, grade, order number, date, and amount.
    select name as salesperson,cust_name,grade,ord_no,ord_date,purch_amt from
    ord,cust1,sales;
    12.SQL statement to generate a report with customer name, city, order number, order date,
    order amount, salesperson name, and commission to determine if any of the existing
    customers have not placed orders or if they have placed orders through their salesman or by
    themselves

    select cust_name,ord_no,ord_date,purch_amt from cust1 LEFT OUTER JOIN ord on


    cust1.customer_id=ord.customer_id;

    You might also like