Upgrade to remove ads
Only $2.99/month

CIS 3060 Database MGMT Test 2

Terms in this set (57)

ALTER TABLE
Changes a table's structure ex: adds, modifies, or deletes attributes or constraints)
COMMIT
Permanently saves data changes
ROLLBACK
Restores data to their original values
SQL> Desc
Describe
any table by placing the name of the table after the word
Select TABLE_NAME From TABS;
list the names of all of the tables you have created
INSERT INTO
statement to add data to a table you have created
SELECT * FROM TABLENAME;
Views the Data in Your Tables
Select Statements
SELECT {columns}
FROM {tables}
WHERE {conditions};
distinct
Limits values to unique values to prevent cardinaility
ORDER BY
This provides an alphabetical listing of the items
Group By operators
Sums,
Averages, Minimums, Maximums, and Counts
Example of a Group By operators
SQL> Select Sum(PRICE) From ITEM;
Example of a Between Values
SQL> select trip_name,
2 max_grp_size
3 from trip
4 where max_grp_size between 8 and 12;
Example of Selecting Null Row Values
SQL> Select Customer_Num,
2 Customer_Name,
3 Rep_Num
4 From Customer
5 Where Rep_Num IS NULL;
Example of using or
SQL> Select Trip_Id,
2 Trip_Name,
3 Season,
4 State,
5 Distance
6 From Trip
7 Where Season = 'Summer'
8 OR State = 'VT';
Example of using in
SQL> Select Trip_Id,
2 Trip_Name,
3 State,
4 Type,
5 Season
6 From TRIP
7 Where Season IN ('Summer', 'Early Fall');
Example using Group By
SQL> Select Rep_Num,
2 SUM(Balance),
3 AVG(Balance),
4 MAX(Balance),
5 MIN(Balance),
6 COUNT(Balance)
7 From CUSTOMER
8 GROUP BY Rep_Num;
LIKE
replaces an equal sign
example of LIKE
SQL> Select *
2 From Trip
3 Where Trip_Name LIKE '%L%';
join tables
They connect things through primary key and FK relationships
Create view
the view has no data and never changes
update statement regarding the View
only can happen if primary key has to be included in the view
Example of a join
SQL> Select First_Name,
2 Last_Name,
3 Customer_Name
4 From REP,
5 CUSTOMER
6 Where REP.Rep_Num = CUSTOMER.Rep_Num;
Example of a join 2
SQL> Select First_Name,
2 Last_Name,
3 Customer_Name
4 REP.Rep_Num
5 From REP,
6 CUSTOMER
7 Where REP.Rep_Num = CUSTOMER.Rep_Num;
Example using alias
SQL> Select First_Name,
2 Last_Name,
3 Customer_Name
4 a.Rep_Num
5 From REP a,
6 CUSTOMER b
7 Where a.Rep_Num = b.Rep_Num;
Nested Join example
SQL> Select Customer_Name
2 From CUSTOMER
3 Where Customer_Num IN (Select Customer_Num
4 From ORDERS
5 Where Order_Date = '12-OCT-15');

CUSTOMER_NAME
-----------------------------------
Toys Galore
The Everything Shop
Three Table Join
SQL> Select ORDERS.Order_Num,
2 Order_Date,
3 ITEM.Item_Num,
4 Description
5 From ORDERS,
6 ITEM,
7 ORDER_LINE
8 Where ORDERS.Order_Num = ORDER_LINE.Order_Num
9 And ITEM.Item_Num = ORDER_LINE.Item_Num;
Inner Join
SQL> Select First_Name,
2 Last_Name,
3 Customer_Name
4 From REP
5 INNER JOIN CUSTOMER
6 ON REP.Rep_Num = CUSTOMER.Rep_Num;
1.List the item number, description, and price for all items
SELECT ITEM_NUM,
DESCRIPTION,
PRICE
FROM ITEM;
2.List the names of cutomers with credit limits of 10,000 or more
SELECT CUSTOMER_NAME
FROM CUSTOMER
WHERE CREDIT_LIMIT >= 10000;
3. List the order number for each order placed by customer placed by customer number 126 on 10/15/2015
SELECT ORDER_NUM
FROM ORDERS
WHERE CUSTOMER_NUM = '126'
AND ORDER_DATE = '15-OCT-2015';
4. List item #, desc, and on-hand value (units on hand*price) of each item in category Toy. (On-hand value is really units on hand times cost, but there is no cost.) Assign the name On_Hand_Value in computation.
SELECT ITEM_NUM,
DESCRIPTION,
ON_HAND * PRICE AS ON_HAND_VALUE
FROM ITEM
WHERE CATEGORY = 'TOY';
5. Use the In operator to list the Item # and desc of each item in Category GME or PZL.
SELECT ITEM_NUM,
DESCRIPTION
FROM ITEM
WHERE CATEGORY IN ('GME', 'PZL');
6.How many cust have balances that are more than their credit limits?
SELECT COUNT(*)
FROM CUSTOMER
WHERE BALANCE > CREDIT_LIMIT;
7. What is the Average distance and the average max group size for each type of trip
SELECT TYPE,
AVG(DISTANCE),
AVG(MAX_GRP_SIZE)
FROM TRIP
GROUP BY TYPE
ORDER BY TYPE;
8. For @ order placed on 10/15/15 list order # & customer name that placed
SELECT ORDER_NUM,
ORDERS.CUSTOMER_NUM,
CUSTOMER_NAME
FROM ORDERS,
CUSTOMER
WHERE ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM
AND ORDER_DATE = '15-OCT-2015';
9. Use the IN operator to find the # and name of each customer that placed a order on

10/15/15
SELECT CUSTOMER_NUM,
CUSTOMER_NAME
FROM CUSTOMER
WHERE CUSTOMER_NUM IN
(SELECT CUSTOMER_NUM
FROM ORDERS
WHERE ORDER_DATE = '15-OCT-2015');
10. Find the # and name of each customer that did not place an order on 10/15/15
SELECT CUSTOMER_NUM,
CUSTOMER_NAME
FROM CUSTOMER
WHERE CUSTOMER_NUM NOT IN
(SELECT CUSTOMER_NUM
FROM ORDERS
WHERE ORDER_DATE = '15-OCT-2015');
11. List the order #, order date for each order that contains an order line for an Fire Engine .
SELECT ORDERS.ORDER_NUM,
ORDER_DATE
FROM ORDERS,
ORDER_LINE,
ITEM
WHERE ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM
AND ORDER_LINE.ITEM_NUM = ITEM.ITEM_NUM
AND DESCRIPTION = 'Fire Engine';
12. List Item Num, desc, price, and category for each item that has a unit price greater than the unit price of every item in category GME. Use either or all.
SELECT ITEM_NUM,
DESCRIPTION,
PRICE,
CATEGORY
FROM ITEM
WHERE PRICE > ALL
(SELECT PRICE
FROM ITEM
WHERE CATEGORY = 'GME');
13. List Trip name of each trip that has the type Biking and that has Rita Boyers as a Guide
SELECT TRIP_NAME
FROM TRIP,
GUIDE,
TRIP_GUIDES
WHERE TRIP.TRIP_ID = TRIP_GUIDES.TRIP_ID
AND GUIDE.GUIDE_NUM = TRIP_GUIDES.GUIDE_NUM
AND LAST_NAME = 'Boyers'
AND FIRST_NAME = 'Rita'
AND TYPE = 'Biking';
14. Insert into NONGAME, ITEM_NUM, DESC, On_Hand, Class, and Price from the ITem Table except Category "GME"
CREATE TABLE NONGAME
(ITEM_NUM CHAR(4) PRIMARY KEY,
DESCRIPTION CHAR(30),
ON_HAND DECIMAL(4,0),
CATEGORY CHAR(3),
PRICE DECIMAL(6,2) );


SELECT *
FROM NONGAME;
15. DELETE FROM NONGAME WHERE CATEGORY = "PZL";
DELETE FROM NONGAME
WHERE CATEGORY = 'PZL';
16.
Add column to NonGAME named ON_HAND_VALUE with a 7-digit, two decimal place and

set all values to ON_HAND_VALUE is ON_HAND * PRICE.
ALTER TABLE NONGAME
ADD ON_HAND_VALUE DECIMAL(7,2);

UPDATE NONGAME
SET ON_HAND_VALUE = ON_HAND * PRICE;

SELECT *
FROM NONGAME;
17. The distance for the pontook reservoir tour trip has been increased to an unknown #. Change Paddling table to reflect the change.
UPDATE PADDLING
SET DISTANCE = NULL
WHERE TRIP_NAME = 'Pontook Reservoir Tour';

SELECT *
FROM PADDLING;
18. Create View named Item_Order. it consists of the item num, desc, price, order num, order date, number ordered, and quoted price for all the orders lines currently on file
CREATE VIEW ITEM_ORDER AS
SELECT ITEM.ITEM_NUM,
DESCRIPTION,
PRICE,
ORDER_LINE.ORDER_NUM,
ORDER_DATE,
NUM_ORDERED,
QUOTED_PRICE
FROM ITEM,
ORDER_LINE,
ORDERS
WHERE ITEM.ITEM_NUM = ORDER_LINE.ITEM_NUM
AND ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM;
19. write and execute the command to retrieve the item num, descr, order num, and quoted price that exceeds $100
SELECT ITEM_NUM,
DESCRIPTION,
ORDER_NUM,
QUOTED_PRICE
FROM ITEM_ORDER
WHERE QUOTED_PRICE > 100;
Write but do not execute, the commands to grant the following privileges


20. User Ashton must be able to retrieve data from the item table
GRANT SELECT
ON ITEM
TO ASHTON;
21. Users Kelly and Morgan must be able to add new orders and orderlines
GRANT INSERT
ON ORDERS
TO KELLY,
MORGAN;

GRANT INSERT
ON ORDER_LINE
TO KELLY,
MORGAN;
22. User James must be able to change the price for all items
GRANT UPDATE (PRICE)
ON ITEM
TO JAMES;
23. User Danielison must be able delete Customer
GRANT DELETE
ON CUSTOMER
TO DANIELSON;
24. All users must be able to retrieve each customers number, name , street, city, state, and postal code
GRANT SELECT (CUSTOMER_NUM,
CUSTOMER_NAME,
STREET,
CITY,
STATE,
POSTAL_CODE)
ON CUSTOMER
TO PUBLIC;
25. User Perez must be able to create index on the ORDERS table
GRANT INDEX
ON ORDERS
TO PEREZ;
26. User Washington must be able change structure of the Item Table
GRANT ALTER
ON ITEM
TO WASHINGTON;
27. User Grinstead must have all privileges on the Order table
GRANT ALL
ON ORDERS
TO GRINSTEAD;
28. Create Index named Item_Index1 on the Item_NUm column in the Order_LIne Table
CREATE INDEX ITEM_INDEX1
ON ORDER_LINE(ITEM_NUM);
29. Create Index named Item_Index2 on the Category column in the Item Table
CREATE INDEX ITEM_INDEX2
ON ITEM(CATEGORY);
YOU MIGHT ALSO LIKE...