Exercise 1

Creating tables and inserting rows into the table

1. Create a table called CUSTOMER with the following columns and attributes.

2. insert the following rows into the customer table.

3. Create table named PRODUCT in the following structure.

4. insert the following rows into the product table.
5. Create a table named invoice in the following structure

6. insert the following rows into the INVOICE table data
7. create a table called customercopy with the same structure of customer table with out copying any rows into it using create table as select ...... statement.
8. insert all the rows from customer table to customercopy table.
9. Create a table called productcopy with only prod_id, prod_name, price columns and copy all the rows from product table in one create table as select ..... statement.
10. create a table called invoicecopy with the following columns inv_no, customer name and total price and data from invoices and customer table using create table as select.... statement.

Result

CUSTOMER TABLE

PRODUCT TABLE

INVOICE TABLE

Exercise 2 (Simple Selects)

1. Select all the rows from customer table.
2. Select customer number, customer name, city from the customer table.
3. Select all the information from the customer table for customer 5002.
4. Select customer number and customer name who are from Texas (TX).
5. Select customer address, city, state and zip who's customer number is less than 5003.
6. Select the customer number, name who's customer numbers greater than 5002 and less than 5010.
7. Select customer name and address who are from Dallas, Texas.
8. Select customer name and city order the result set based on city in alphabatical order.
9. Select customer name and city order the result set based on city in reverse alphabatical order.
10. Select customer name who's name is AtoZ and city is Dallas and state is California.

Exercise 3 ( Groups and functions )

1. Select invoice number, product id, price from invoice table for 1008 invoice.
2. Select the count of products in invoice 1007.
3. Select all the invoices in which product id 107 exists.
4. Select the count of invoices for customer 5002.
5. Select all the invoice numbers which has more than 10 qty sold, less than 5% discount and the final price is more than 5000.
6. Select different product ids from the invoices table.
7. Select all the invoice numbers for customer 5002, product 108 invoiced in march.
8. Select the different invoice numbers where month of invoice dates is Jan or March or May
9. Select invoices in which there are only 2 different products sold.
10. Select invoices for the month of Jan and invoice total greater than 2000.

Exercise 4 (Joins)

1. Select the different product id, product name for the customer 5003.
2. Select the invoice number, customer name, sum of the price for every invoice for customer number 5001.
3. Select customer number, customer name, product name which the customer bought.
4. Select customer number, customer name and total amount for each customer.
5. Select customer number, customer name product name and count of the products they bought.
6. Select customer number, customer name, product name and count of products bought by each customer.
7. Select customer number, name, invoice number for 'CA' state customers.
8. Select customer name, different product name bought for from 'TX' state and name starts with 'A'.
9. Select customers who has atleast one invoice.
10. Select customers who has atleast 4 invoices.

Exercise 5 ( Selects with Group by, Exists, Joins )

1. Select all product ids and names which does not exists in the invoices table.

2. Select product id, name, total number of items sold, if there is no row for a particular product in invoices table then show the total items sold for that product as 0.

3. Select customer name, customer city, state, no of invoices, total invoices amount where customer has atleast 5 invoices and the total amount of the invoices exceeds 6000.

4. Select customer name, customer city, state where he did not have any corresponding invoice record.

5. Select product id, name and reorder information in the following format ie if the qty on hand is less than reorder level then display 'REACHED LOW' otherwise display 'ITS OK'

   PRODUCT ID	PRODUCT NAME	REORDER INFORMATION
   1001	MOUSE	REACHED LOW
   1002	KEY BOARD	ITS OK

6. Select customers who bought the produts whose price is more than 1000.

7. Display the state, number of customers , total number of invoices, total amount. for each state you should get only one row in the result so that we can see which state has more customers.

8. Select city, state, number of customers so that we will know which city has more customers.

9. Select same products bought by customer 5001 and 5002.

10. Select different products bought by customer 5002 and 5003.

Exercise 6 (Data Dictionary I)

1. List all the user tables from the database.
2. List all the tables created by james.
3. List all the objects in the database created by james.
4. List the different objects in the database.
5. List all the stored procedures in the database.
6. List all the system stored procedurs in the database.
7. List all the rules except the ones which is created by james.
8. List all the valid users in the current database.
9. List all the users in the Server.
10. List all the defaults in the current database.

Exercise 7 ( Data Control Language)

1. Create a user named james.
2. Grant Select permission on CUSTOMER table to james.
3. Grant Insert permission on CUSTOMER table to james.
4. Create a group called CUST_REP.
5. Authorize Insert, Update, Select on EMP, DEPT, CUST, ORDER, REF_CD tables to CUST_REP group.
6. Authorize Delete on ORDER table to CUST_REP group.
7. Authroize CUST_REP role to james, steven, mark and william.
8. Display the group names in which james is a member.
9. Unauthorize the Update on CUST table from CUST_REP group.
10. Display the Group name and number of users in that group respectively.