Notes

Week-9
1.After which keyword do you specify join clauses
eg the something = something?
-ON

Which columns are usually joined?
-PKS WITH FKS

When using the ANSI notation, what is/are the key words?
-INNER OUTER JOIN RIGHT LEFT

Where do you specify the multiple tables used in joins?
-FROM clause

What is the term used when the join has been omitted in a multiple table statement?
-Cartesian product

What are the maximum number tables you can use in a join?
-No maximum

What order should the tables appear when using the ANSI notation?
-cascading order

WEEK 10
Where does the nested statement appear?
-Where

What are the key words used for nested statements?
-IN

What is the additional keyword used for negative statements?
-NOT

Is order important if you wanted to see customers who haven’t ordered anything?
-YES

Which table would appear first, customers or orders?
-customers

Which method is more efficient a join or a sub query?
-join

WEEK 6
What is the method to retrieve data in this format from a REF
address(street, city, country)?
- DEREF (reference_column)

What is the best method to retrieve data in this format from a REF ?
-DOT Notation, relational_column,object_table_attribute

What is the additional bit used in querying tables with VArrays and Nest?
-pseudo table

Where is the error in the following code?
-FROM invoices

What is the word that means a short alternative name for a table?
-ALIAS

What is another name for the reference used to reference an object table?
-OID

How do you query an object type?
-Do Not Store



Fill in the blanks to write the query to provide a list of addresses in a column format for sites. The address column is based on address_type(street,city,country). Query all columns in the order shown.

sites(site_id, address)

SELECT
S.SITE_ID
,
S.ADDRESS.STREET
,
S.ADDRESS.STREET
,
S.ADDRESS.COUNTRY

FROM sites s;


Fill in the blanks below to query the classroom column, which uses a VArray type based on classroom_type (room_number, capacity, description), in sites table

SELECT
s
.site_id,
c
.room_number,
c
.capacity
FROM
sites
s, TABLE(
s.classroom
) c
WHERE site_id =1;


WEEK 8
List the 5 statistical functions mentioned in the lecture


SELECT MAX(attribute) FROM tablename;

SELECT AVG(attribute) FROM tablename;

SELECT MIN(attribute) FROM tablename;

SELECT SUM(attribute) FROM tablename;

SELECT COUNT(attribute) FROM tablename;



What is the keyword used to remove duplicates?
DISTINCT

What is a synonym for ANY?
SOME


What are the wildcards symbol used in SQL?
% and _


Where do you specify aliases?
-FROM

WEEK-5

A relational table called customers stores information about contact details in a varray called contact_varray_type, based on a type called contact_type. contact_type has 3 attributes: name, detail, notes

Fill in the blanks to insert 1 row into customers with the information below in the order shown:

customer_id: 1234
contact: EMAIL [email protected] PRIMARY CONTACT
contact: MOBILE PHONE 07777 642135 WEEK DAYS WORKING HOURS 9-5
contact: WORK EMAIL [email protected] ONLY FOR EMERGENCY
-------
--viewing table prospective_customers
DESC prospective_customers;
DESC social_media_type;

INSERT INTO prospective_customers(prospective_customer_id,company_name,contact_name,social_media_available)
VALUES (80000, 'IT Pvt.Ltd', 'RAM',
social_media_varray_type(
social_media_type('EMAIL','milangmail.com'),
social_media_type('PHONE', '014-66429'),
social_media_type('FACEBOOK','Hello'))
);



A relational table called adverts stores information about its advertising media in a varray called media_varray_type, based on a type called media_type. media_type has 3 attributes name, description, area

Fill in the blanks to insert 1 row into adverts with the information below in the order shown:

advert_id: 1234
media: TWITTER PUNCHY INTRO PROMO INTRODUCTION PROMPTS
media: FLICKR IMAGE CAPTURE PRODUCT IMAGE SHARE
media: FACEBOOK GROUP SOCIAL DEVELOPING EXISTING CUSTOMER, SUPPLIER FEEDBACK AND INTERACTION
-----
INSERT INTO prospective_customers(prospective_customer_id,company_name,contact_name,social_media_available)
VALUES (80001, 'IT Pvt.Ltd', 'RAM',
social_media_varray_type(
social_media_type('EMAIL','[email protected]'),
social_media_type('PHONE', '014-663344'),
social_media_type('FACEBOOK','DATABASE'))
);
WEEK 7
Which type of query limits the number of columns, where do you specify this?
---projection , in the SELECT list

Which type of query limits the number of rows, where do you specify this?
--RESTRICTION, in the where clause

Where does the term Cartesian Product come from?
---RENE DESCARTES,his formulation of analytic geometry to the concept

How many columns should appear in the second statement of an intersect?
---The same as the first statement

What is the order of key words in a standard query?
---SELECT FROM WHERE

What are the two rules for union, intersect and minus?
---The same number of columns in both statements, with the same data types

If the first and second statements have 3 columns, how many are shown in the result set?
----3

What happens to the result set of a query when you log off?
---it disappear , the result of query is temporary

WEEK4


What is the command to insert into an object table?
----The same as a relational table



What is the term used when selecting the OID from an object table?
----REF()


What values goes inside the parenthesis when selecting a OID?
----The object table alias



Where is the data stored in a relational table referencing an object table?
---In both the relational and the object table




You can insert and store data in an object type
-----False