Jeff Hardy
IST210 Lab 5. 7/19/09
1. Display the employee_id, last_name, department_id and the name of the department (the name will come from the demo_department table) for all employees. You will join department and employee.
SELECT employee_id, last_name,name
FROM demo_employee, demo_department
WHERE demo_employee.department_id = demo_department.department_id;
employee_id last_name name
------
7369 SMITH RESEARCH
7499 ALLEN SALES
7505 DOYLE SALES
7506 DENNIS SALES
7507 BAKER OPERATIONS
7521 WARD SALES
7555 PETERS SALES
7557 SHAW SALES
7560 DUNCAN SALES
7564 LANGE SALES
7566 JONES RESEARCH
7569 ALBERTS RESEARCH
7600 PORTER SALES
7609 LEWIS OPERATIONS
7654 MARTIN SALES
7676 SOMMERS OPERATIONS
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7789 WEST SALES
7799 FISHER RESEARCH
7820 ROSS SALES
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7902 FORD RESEARCH
7916 ROBERTS RESEARCH
7919 DOUGLAS RESEARCH
7934 MILLER ACCOUNTING
7950 JENSEN SALES
7954 MURRAY SALES
(32 row(s) affected)
2.Write a SELECT statement that will return data on customers in the state of Texas. In addition, the statement will return data about the salesperson assigned to the customer. This is done by joining the salesperson_id of the demo_customer table to the employee_id of demo_employee table.
Display the customer_name, salesperson_id, last_name of the salesman (employee), and the commission the salesman (employee) has earned. Order the data by the value of the commission in descending order.
SELECTname, salesperson_id, last_name, commission
FROM demo_customer, demo_employee
WHERE demo_employee.employee_id = demo_customer.salesperson_id ANDstate='TX'
ORDERBY commission DESC;
name salesperson_idlast_name commission
------
BOB'S FAMILY SPORTS 7654 MARTIN 1400.00
WHEELS AND DEALS 7789 WEST 1000.00
JUST BIKES 7789 WEST 1000.00
JOE'S BIKE SHOP 7789 WEST 1000.00
BOB'S SWIM, CYCLE, AND RUN 7789 WEST 1000.00
HIT, THROW, AND RUN 7564 LANGE 300.00
AL'S PRO SHOP 7564 LANGE 300.00
THE POWER FORWARD 7560 DUNCAN NULL
AL AND BOB'S SPORTS 7560 DUNCAN NULL
(9 row(s) affected)
3. Display data from the demo_sales_order table by performing a Three Table Join with the demo_sales_order, demo_customer and demo_employee tables. You will be joining salesperson_id of demo_customer with employee_id of demo_employee. You will also be joining the foreign key of demo_sales_order with the primary key of demo_customer. Select only records where the salesperson’s last_name = ‘TURNER’.
Display salesperson_id, last_name of the joined salesperson/employee, customer_id, state on the demo_customer record, and the total on the demo_sales_order record.
ORDER the resulting data sorted first by salesperson_id and then by state.
SELECT salesperson_id, last_name,state, total
FROM demo_sales_order, demo_customer, demo_employee
WHERE demo_customer.salesperson_id = demo_employee.employee_id
AND demo_sales_order.customer_id = demo_customer.customer_id
AND last_name ='TURNER'
ORDERBY salesperson_id,stateDESC;
salesperson_id last_name state total
------
7844 TURNER MN 6400.00
7844 TURNER CA 3.40
7844 TURNER CA 102.50
7844 TURNER CA 4450.00
7844 TURNER CA 730.00
7844 TURNER CA 46370.00
(6 row(s) affected)
4. Write a select statement that will join the demo_sales_order, demo_item, and demo_product tables. The demo_item table is the primary table for the linkages. Display the order_id, customer_id, item_id, and the product description.
Select only records for product_id = 103121.
Sort display by order_id
SELECT demo_sales_order.order_id, customer_id, item_id,description
FROM demo_sales_order, demo_item, demo_product
WHERE demo_item.order_id = demo_sales_order.order_id
AND demo_item.product_id = demo_product.product_id
AND demo_product.product_id = 103121
ORDERBY demo_item.order_id;
order_id customer_id item_id description
------
522 204 2 WIFF SOFTBALL BAT II
527 204 2 WIFF SOFTBALL BAT II
530 208 1 WIFF SOFTBALL BAT II
533 206 1 WIFF SOFTBALL BAT II
546 208 2 WIFF SOFTBALL BAT II
557 208 2 WIFF SOFTBALL BAT II
566 201 2 WIFF SOFTBALL BAT II
577 206 1 WIFF SOFTBALL BAT II
(8 row(s) affected)
5. Display the number of employees by job function within departments.
This is a join of the tables demo_employee, demo_department and demo_job. The SELECT command will involve a multiple grouping.
The resulting display will show the department name, the job_function from the demo_job table, and the count of the records for the GROUPING.
SELECTname, job_function,count(*)as'Number of Employees'
FROM demo_employee, demo_department, demo_job
WHERE demo_employee.job_id = demo_job.job_id
AND demo_employee.department_id = demo_department.department_id
GROUPBYname, job_function;
name job_function Number of Employees
------
RESEARCH ANALYST 4
ACCOUNTING CLERK 1
RESEARCH CLERK 3
SALES CLERK 3
ACCOUNTING MANAGER 1
OPERATIONS MANAGER 1
RESEARCH MANAGER 2
SALES MANAGER 3
ACCOUNTING PRESIDENT 1
SALES SALESPERSON 11
OPERATIONS STAFF 2
(11 row(s) affected)