Assignment on Database Management System 1

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Author
Name of the University
Author Note

1DATABASE MANAGEMENT SYSTEM
1. Show sums of line units for each invoice.
select inv_number, sum(line_units) as total from line group by inv_number;
2. Show the details of the products that do not have a value for the attribute v_code.
select * from product where v_code='';
3. Show the details of the invoices whose subtotal is greater than 24 but less than 76.
select * from invoice where inv_subtotal<76 and inv_subtotal>24;

2DATABASE MANAGEMENT SYSTEM
4. Show the details of the invoice who has the minimum subtotal.
select *, min(inv_subtotal) from invoice;
5. Show the codes and names of the vendors who supplied products.
select v_code, v_name from vendor where v_order in (‘Y’)';
6. Using EXCEPT show the codes of the vendors who did not supply any products.
select v_code from vendor except select v_code from vendor where v_order!='N';
7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.
select v_code, v_name from vendor where v_order not in ('Y');

3DATABASE MANAGEMENT SYSTEM
8. List the names and codes of vendors and the number of products each vendor has supplied.
select vendor.v_code,vendor.v_name, count(product.p_code) as number from product, vendor
where product.v_code=vendor.v_code group by v_name;
9. Show the details of the employees who are located in area code 615.
select * from emp where emp_areacode=’615’;

4DATABASE MANAGEMENT SYSTEM
10. Using inner join, list the details of the products whose line price is greater than 99.
select p.* from product p, line l where p.p_code=l.p_code and l.line_price>99;