Database Queries Assignment - SQL Code, Results, Screenshots, Details

This assignment presents solutions to a series of database queries. The solution includes the SQL code for each query, screenshots of the code, and the corresponding results. The queries cover a range of database operations, such as selecting specific data based on conditions, joining tables, using aggregate functions like SUM and COUNT, and employing set operations like EXCEPT. The assignment requires students to use MySQL Workbench, write SQL queries, and present the code, screenshots, and results in a structured format. The queries address various aspects of database management, including data retrieval, filtering, and manipulation, demonstrating a practical application of SQL skills. The assignment is designed to help students understand and apply SQL concepts in a real-world context, providing a comprehensive solution for learning and review.

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;