Introduction to Database Management Systems Assignment

This database assignment solution demonstrates the creation of three tables: 'emp', 'products', and 'invoice', along with data insertion into each table. The assignment covers a range of SQL queries, including selecting data based on specific criteria (e.g., area code, attribute code, subtotal ranges), finding minimum values, using `EXCEPT` and `NOT IN` clauses to identify vendors, and aggregating data using `GROUP BY` to count products supplied by each vendor. Furthermore, the solution includes adding a new attribute to the 'emp' table and updating data within the table. Finally, the solution employs an inner join to retrieve product details based on a line price condition. This provides a comprehensive practical guide to database design and SQL query writing.

Running head: INTRODUCTION TO DATABASE MANAGEMENT
INTRODUCTION TO DATABASE MANAGEMENT
Name of the Student:
Name of the University:
Author Note:

1INTRODUCTION TO DATABASE MANAGEMENT
Table Creation
Employee Table
create table emp (vendor_code number primary key, vendor_name varchar2(20), area_code
number, city varchar2(20));
desc emp;
Products Table
create table products (product_code number primary key, product_name varchar2(20),
vendor_code number, attribute_code varchar2(10), product_price number, foreign key
(vendor_code) references emp(vendor_code));
desc products;

2INTRODUCTION TO DATABASE MANAGEMENT
Invoice Table
create table invoice (invoice_code number primary key, product_code number, product_price
number, vendor_code number, subtotal number, line_price number, quantity number,foreign
key (product_code) references products(product_code), foreign key (vendor_code) references
emp(vendor_code));
desc invoice;

3INTRODUCTION TO DATABASE MANAGEMENT
Data Insertion and Display
Employee Table
insert into emp values (100, 'Tony Stark', 605, 'Melbourne');
insert into emp values (101, 'Chris Evans', 608, 'Sydney');
insert into emp values (102, 'Bruce Banner', 610, 'Victoria');
insert into emp values (103, 'Chris Hemsworth', 615, 'Adelaide');
insert into emp values (104, 'James Rhodes', 620, 'Canberra');
select * from emp;
Products Table
insert into products values (201, 'Samsung', 101,'Null',3000);
insert into products values (202, 'Phillips', 102,'E201',13000);
insert into products values (203, 'Dell', 100,'E304',24000);
insert into products values (204, 'Rolex', 104,'F443',35000);
insert into products values (205, 'Intel', 102,'D122',7500);

4INTRODUCTION TO DATABASE MANAGEMENT
select * from products;
Invoice Table
insert into invoice values (501, 202, 13000,102,45,12000,4);
insert into invoice values (502, 203, 24000,100,85,21000,2);
insert into invoice values (503, 204, 35000,104,65,30,1);
insert into invoice values (504, 205, 7500,102,53,5600,8);
insert into invoice values (505, 203, 24000,100,15,19500,2);
select * from invoice;

5INTRODUCTION TO DATABASE MANAGEMENT
Queries
1. Show the details of the vendors who are located in area code 615.
select * from emp where area_code=615;
2. Show the details of the products that do not have a value for the attribute v_code.
select * from products where attribute_code='Null';
3. Show the details of the invoices whose subtotal is greater than 25 but less than 75.
select * from invoice where subtotal>25 and subtotal<75

6INTRODUCTION TO DATABASE MANAGEMENT
4. Show the details of the invoice who has the minimum subtotal.
select * from invoice where subtotal= (select min(subtotal) from invoice);
5. Show the codes and names of the vendors who supplied products.
select vendor_code,vendor_name from emp where vendor_code in (select vendor_code from
products);
6. Using EXCEPT show the codes of the vendors who did not supply any products.
select vendor_code from emp except select vendor_code from products;

7INTRODUCTION TO DATABASE MANAGEMENT
7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any
products.
select vendor_name, vendor_code from emp where vendor_code not in (select vendor_code
from products);
8. List the codes of vendors and the number of products each vendor has supplied, i.e.
vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
select vendor_code, sum(quantity) as No_of_products from invoice group by vendor_code;

8INTRODUCTION TO DATABASE MANAGEMENT
9. List the names and codes of vendors and the number of products each vendor has
supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy
products etc.
select vendor_name, vendor_code, sum(quantity) from (select emp.vendor_name,
emp.vendor_code, invoice.quantity from emp, invoice where invoice.vendor_code =
emp.vendor_code) group by (vendor_code, vendor_name);
10. Add a new attribute (field) status varchar(6) to the EMP table.
alter table emp add status varchar(20);
select * from emp;

9INTRODUCTION TO DATABASE MANAGEMENT
11. Update status for employee '100' to 'Temporary'.
update emp set status='Temporary' where vendor_code=100;
select * from emp;
12. Using inner join, list the details of the products whose line price is greater than 100.
select products.products_code, products.products_name, products.vendor_code,
products.attribute_code, products.products_price, invoice.invoice_code, invoice.subtotal,
invoice.quantity, invoice.line_price from products, invoice where
products.product_code=invoice.product_code and line_price>100;