IS 4420 Final

9. Implement query 7 using IN and subquery.

9. select city, count(cust_id) from customer where cust_id in (select cust_id from ordertable) group by city order by city;

index

An index is a table consisting of key-pointer pairs. A search key in the index file is associated with a pointer to the block containing a record that has the search key. creates a key column and a corresponding pointer column

How to add a foreign key to a table

alter table orderline modify (FOREIGN KEY (product_id) REFERENCES ordertable (order_id), FOREIGN KEY (order_id) REFERENCES product (product_id));

1. List ID, Name, and Price for all products with price greater than the average product price.

1. Select Product_ID, Product_Name, Product_Price from Product where product_Price > (select AVG(PRODUCT_PRICE)from PRODUCT);

10. List ID for all products, which have NOT been ordered since 28-OCT-2008. Sort your results by product_id in ascending order. Use Minus for this query.

10. Select product_id from product Minus Select product_id from orderline natural join ordertable where order_date>'28-OCT-2008' order by product_id;

11. List ID for all Arizona customers, who have placed order(s) since 28-OCT-2008. Sort your results by cust_id in ascending order. Use Intersect for this query.

11. Select cust_id from customer where state='AZ' Intersect Select cust_id from ordertable where order_date>'28-OCT-2008' Order by cust_id;

12. Implement query 11 using IN and subquery.

12. Select cust_id from customer where state='AZ' and cust_id in ( Select cust_id from ordertable where order_date>'28-OCT-2008') Order by cust_id;

13. List ID for all California customers and all customers who have placed order(s) since 28-OCT-2008. Sort your results by cust_id in ascending order. Use Union for this query.

13. Select cust_id from customer where state='CA' Union Select cust_id from ordertable where order_date>'28-OCT-2008' Order by cust_id;

14. List ID, Name and total quantity ordered for all products with total quantity ordered greater than 10.

14. Select product_id, product_name, sum(quantity) from orderline natural join product group by product_id, product_name having sum(quantity)>10;

15. List ID, Name and total quantity ordered for all products, which have been ordered more than 6 by Utah customers.

15. Select product.product_id, product_name, sum(quantity) from orderline, product,customer,ordertable where orderline.product_id=product.product_id and ordertable.order_id=orderline.order_id and ordertable.cust_id=customer.cust_id and state='UT' group by product.product_id, product_name having sum(quantity)>6;

2. For each product, list its ID and total quantity ordered. Products are listed in ascending order of product_id.

2. Select PRODUCT_ID, Sum(Quantity) from orderline Group by Product_ID order by Product_ID;

3. For each product, list its ID and total quantity ordered. Products are listed in ascending order of total quantity ordered.

3. select Product_ID, Sum(quantity)from orderline group by Product_ID order by SUM(QUANTITY);

4. For each product, list its ID, Name and total quantity ordered. Products are listed in ascending order of product_id.

4. select orderline.Product_ID, Product_name, Sum(quantity)from orderline, product where orderline.product_ID = product.product_ID group by orderline.product_ID, product_name order by orderline.product_ID;

5. List name for all customers, who have placed order(s) after 23-OCT-2008. Each customer name appears exactly once. Customer names are sorted in ascending alphabetical order. Use equal join for this query.

5. select distinct(cust_name)from customer,ordertable where customer.cust_ID = ordertable.cust_ID and ORDER_DATE > '23-OCT-2008' order by customer.cust_name;

6. Implement query 5 using IN and subquery.

6. select cust_name from customer where cust_ID in (select cust_ID from ordertable where ORDER_DATE > '23-OCT-2008') order by customer.cust_name;

7. For each city, list number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order. Use equal join for this query

7. select city, count(distinct customer.cust_id) from customer, ordertable where customer.cust_id=ordertable.cust_id group by city order by city;

8. Implement query 7 using natural join

8. select city, count(distinct cust_id) from customer natural join ordertable group by city order by city;

Syntax to create a table

CREATE TABLE customer ( cust_id number(11,0) not null, cust_name varchar2(25) not null, street varchar2(30), city varchar2(20), state varchar2(2), zipcode varchar2(5), CONSTRAINT customer_pk PRIMARY KEY (cust_id) );

Remove a row from a table

DELETE FROM table_name where condition; Delete rows satisfying the condition. For example, delete from product where product_id=101;

Add a row to a table

INSERT INTO table_name ({list of attributes})values ({list of values}); insert into product (product_id, product_name) values (102, 'Apple Laptop'); insert into product (product_name, product_id) values ('Printer',103);

GROUP BY

Indicate categorization of results

HAVING

Indicate the conditions under which a category (group) will be included

Where

Indicate the conditions under which a row will be included in the result

From

Indicate the table(s) or view(s) from which data will be obtained

Intersect

List ids for all Utah customers who have placed order(s): Select cust_id from customer where state='UT' intersect Select cust_id from ordertable; List ids for all ordered products with price higher than $50: Select product_id from product where product_price>50 intersect Select product_id from orderline;

Minus (difference operator)

List ids for all customers who have NOT placed order(s): Select cust_id from customer minus Select cust_id from ordertable; List ids for all Utah customers who have NOT placed order(s): Select cust_id from customer where state='UT' minus Select cust_id from ordertable;

Select function

List the columns (and expressions) that should be returned from the query

ORDER BY

Sorts the result according to specified criteria

Change table contents

UPDATE table_name SET attribute = new value; Change all rows. For example, update product set product_price=100.00; UPDATE table_name SET attribute = new value where condition; Update rows satisfying the condition. For example, update product set product_price = 59 where product_id=103; update product set product_price = 988.72 where product_id=102;

4. List name for all departments located in Houston. Use the NATURAL JOIN operation for this data retrieval requirement.

a. DEPTL(DNUMBER, DLOCATION) ← σ DLOCATION = HOUSTON (DEPT_LOCATIONS) b. DEPTL_DEPT ← DEPTL * DEPARTMENT c. RESULT ← π DNAME (DEPTL_DEPT)

5. List SSN for all employees, who works on project "ProductX".

a. PRJ ← σ PNAME = ProductX (PROJECT) b. PRJ_WRKS ← PRJ (PNUMBER=PNO) WORKS_ON c. PRJ_WRKS_EMP ← PRJ_WRKS (ESSN=SSN) EMPLOYEE d. RESULT ← π SSN (PRJ_WRKS_EMP)

3. List name for all projects, which is NOT managed by department number 5 but is located in Houston. Use the DIFFERENCE operation for this data retrieval requirement.

a. PROJECT_5 ← σ DNUM = 5 (PROJECT) b. PROJECT_H ← σ PLOCATION = HOUSTON (PROJECT) c. RESULT ← π PNAME (PROJECT_H - PROJECT_5)

Relational Algebra List name, birth date and relationship for all dependents of employee John B. Smith.

a. π DEPENDENT_NAME ,BDATE, RELATIONSHIP (EMPLOYEE (SSN=ESSN) AND (FNAME = John) AND (MINIT = B) AND (LNAME = SMITH) DEPENDENT)

Materialized views

contain data. create materialized view AZCustomer as select * from customer where state = 'AZ'; Advantages of using materialized views: Simplify query command Provide data security no need to create view each time view is queried Disadvantages of using materialized views: difficult to keep a materialized view consistent with its base tables

Create an index

create index pname on product(product_name);

CROSS JOIN

select * from customer cross join ordertable; EQUIJOIN: select customer.cust_name from customer,ordertable where customer.cust_id=ordertable.cust_id;

Display created indexes for a table

select index_name from dba_indexes where table_name='PRODUCT'

Relational Algebra List all male employees, who do NOT work in department number 5 but have salary higher than $35,000

σ (SEX = M) AND NOT(DNO=5) and (SALARY > 35000) (EMPLOYEE)