Databases for Developer answers | Infinity CS Academy

Which of the following statements are true about heap organized tables?

They are the default table type in Oracle Database

They enforce an order on how the data is physically stored

They can group similar rows from different tables together

You can partition a heap table

Which of the following are valid types of table in Oracle Database?

Which of the following table types can you use to place some physical order on your data?

Which of the following choices will create a table?

create table bricks ( color varchar2(30), weight number );

create table bricks ( color, weight );

create table ( color varchar2(30), weight number );

create table bricks ( color varchar2(30), weight number ) organization heap;

Which of the following data types store numeric data in Oracle Database?

You can use a DATE data type to store time zone information

True

False

Which of the following data types can you use to store character data (strings)?

Which of the following choices create the table toys with a varchar2 column?

create table toys ( toy_name varchar2 );

create table toys ( toy_name varchar2(1) );

create table toys ( toy_name varchar2(100) );

create table toys ( toy_name varchar2(4000) );

create table toys ( toy_name varchar2(9999999) );

What should you consider when deciding which table and column to store your facts in?

Which real world objects your application will store details of

The properties of these real world objects the people using your application need to know

Which programming language your application uses

The performance requirements for your application

Document based storage prevents data inconsistencies?

True

False

A fully normalized database using the relational model stores each fact once

True

False

You're building a book catalogue for a library. A colleague has proposed the following table to store these details: create table books ( book_title varchar2(100), pulication_date date, author_name varchar2(100) ); What are the disadvantages of storing the author's name on the book table?

It's possible to store different names for an author

If you need to change an author's name, you need to update a row for each book they've published

You only need to access one table to get a book's details and its author's name

Quiz Which of the following table types allow you to store private data for a session?

Which of the following statements are true about index-organized tables (IOTs)?

An IOT must have a primary key

The properties of these real world objects the people using your application need to know

An IOT increases the work to fetch rows by primary key

Which of the following data types can you use to store binary data?

You're building a website for a vehicle retailer. It sells cars and vans. For both cars and vans, the application needs to store and display their: registration number price mileage You're creating the database tables to store this information. Which of the following table designs could you use to store this information?

create table vehicles ( registration_number varchar2(10), mileage integer, price number(10, 2), vehicle_type varchar2(10) -- car or van ); T

create table cars ( registration_number varchar2(10), mileage integer, price number(10, 2) ); create table vans ( registration_number varchar2(10), mileage integer, price number(10, 2) );

create table prices ( price number(10, 2), vehicle_registration_number varchar2(10) );

Which of the following statements are true about the date data type?

It includes a date and time

You can use it to store fractional seconds

You can use it to store time zone information

You should always use a date or timestamp to store datetime data

Which of the following characters can you use as wildcards in like conditions in Oracle Database?

A select * query will return all the visible columns in a table?

True

False

Which of the following where clauses will return rows from a table that store a NULL value in the color column?

Given the toys table with the columns toy_name, colour, and weight which stores these rows: TOY_NAME COLOR WEIGHT Baby Turtle green 10 Miss Snuggles red 5 Cuteasaurus blue 15 Which of the following queries will return this row: TOY_NAME COLOR WEIGHT Baby Turtle green 10

select * from toys where toy_name = 'Baby Turtle';

select * from toys where toy_name = 'Miss Snuggles';

select * from toys where color = 'green';

select * from toys where weight = 10;

select * from toys where toy_name = 'green';

Which of the following are valid SQL join types?

A full outer join returns all the rows from both tables in the join?

True

False

Which of the following statements are true about left outer joins?

The keyword "outer" is mandatory in ANSI syntax

It returns all the rows from the table on the left side of the join

Oracle syntax uses the plus operator (+) to indicate an outer join

Placing non-null conditions against the right table in the where clause using ANSI joins makes the query an inner join

Outer joins are also known as "outside" joins

You have tables storing details of bricks and colors: BRICKS COLOR SHAPE green cube yellow prism red cylinder COLORS COLOR RGB_HEX_VALUE red FF0000 green 00FF00 blue 0000FF You want to return the rows from each table which have a matching color in the other. Which of the following queries join the tables, returning these rows (in any order): COLOR SHAPE RGB_HEX_VALUE red cylinder FF0000 green cube 00FF00

select b.color, b.shape, r.rgb_hex_value from bricks b, colors r where b.color = r.color;

select b.color, b.shape, r.rgb_hex_value from bricks b join colors r on b.color = r.color;

select b.color, b.shape, r.rgb_hex_value from bricks b inner join colors r on b.color = r.color;

select b.color, b.shape, r.rgb_hex_value from bricks b cross join colors r;

Which of the following functions can you use to aggregate rows?

Which of the following statements about group by are true?

Yes. Group by combines multiple rows with the same value into one. For example, if you have a table storing colors, like so: COLOR Blue Blue Green Red Red If you group by color, you'll get three rows in your output, one for each color: COLOR Blue Green Red

All the columns in the group by clause must appear in the select clause

You can use a having clause to filter aggregations for each group

To use aggregate functions you must have a group by clause

Which of the following group by extensions can you use to get subtotals for the columns within it?

You have the table clothes which stores these rows: CLOTHING_TYPE CLOTHING_SIZE shirt XS shirt XXXL skirt M trousers L Mark a choice as correct if it displays the following single row of data: CT ---- 4

select count (*) ct from clothes;

select count ( clothing_type ) ct from clothes;

select count ( distinct clothing_type ) ct from clothes;

select count ( distinct clothing_size ) ct from clothes;

select count ( * ) ct from clothes group by clothing_size;

You have a table, BRICKS, which stores details of the COLOR and SHAPE of bricks. The rows in the table are: COLOR SHAPE blue cube blue cylinder green cube red cylinder red rectangular cuboid yellow rectangular cuboid Which of the following queries will return the rows from this table where the value for COLOR is green or red? i.e. these rows: COLOR SHAPE green cube red cylinder red rectangular cuboid

select * from bricks where color = 'green' or color = 'red';

select * from bricks where color like 'green' or color like 'red';

select * from bricks where color = 'green' and color = 'red';

select * from bricks where color in ('green', 'red');

select * from bricks where not color in ('yellow', 'blue');

Your application has the tables countries and currencies. These store country and currency details respectively. The rows in these tables are (column names in bold): Countries COUNTRY_NAME CURRENCY_CODE USA USD India INR China CNY Currencies CURRENCY_CODE CURRENCY_NAME INR India Rupee EUR Euro GBP British Pound Using these data, you run the following query that left joins currencies and countries: select * from currencies curr left join countries coun on coun.currency_code = curr.currency_code; Which of the following choices contain a row this query will return? Note: For clarity the text "<null>" indicates a null value and the column headings: CURRENCY_CODE CURRENCY_NAME COUNTRY_NAME CURRENCY_CODE appear in every choice.

CURRENCY_CODE CURRENCY_NAME COUNTRY_NAME CURRENCY_CODE INR India Rupee USA USD

CURRENCY_CODE CURRENCY_NAME COUNTRY_NAME CURRENCY_CODE INR India Rupee India INR

CURRENCY_CODE CURRENCY_NAME COUNTRY_NAME CURRENCY_CODE GBP British Pound <null> <null>

CURRENCY_CODE CURRENCY_NAME COUNTRY_NAME CURRENCY_CODE <null> <null> USA USD

CURRENCY_CODE CURRENCY_NAME COUNTRY_NAME CURRENCY_CODE <null> <null> China CNY

People FULL_NAME HEIGHT_IN_CM Mr. Tall 200 Master Medium 170 Miss Tiny 140 FULL_NAME and HEIGHT_IN_CM are the columns which hold people's names and their heights respectively. Which of the following queries only return rows for people 170 cm and smaller? i.e. given the table above, the query should return the following two rows: FULL_NAME HEIGHT_IN_CM Master Medium 170 Miss Tiny 140 Please select all that apply

select * from people where height_in_cm < 210;

select * from people where height_in_cm < 170;

select * from people where height_in_cm >= 140;

select * from people where height_in_cm between 0 and 170;

Which of the following statements are true about a cross join?

If you don't specify a join condition for a table, the result is the cross join

A cross join returns the Cartesian product of the tables

The result of an unfiltered cross join contains fewer rows than the sum of the rows in the joined tables

A cross join is the most common type of join you'll use

You can use aggregate functions directly in a where clause like this: where count(*) > 10

True

False

Which of the following statements about INSERT are true?

You must specify the columns after the table name

The values clause specifies the data you're adding to a table

You can copy the contents of one table to another using an insert with a subquery

Running a single row insert 10,000 times is always faster than one multirow insert that adds 10,000 rows

You can insert rows into two or more tables in one statement with a multitable insert

When you insert rows into a table, other users can see them before you issue a commit

True

False

Which of the following can you use to do a multi-table insert?

You create this table to store brick details: create table bricks ( color varchar2(10), shape varchar2(10), weight number ); Which of the following inserts will add a row to bricks that has: The color red The shape cylinder A null value for weight? i.e. after the insert, the querying the table should return this row: select * from bricks; COLOR SHAPE WEIGHT red cylinder <null> NB: <null> above means the value null.

insert into bricks values ( 'red', 'cylinder' );

insert into bricks values ( 'red', 'cylinder', null );

insert into bricks ( color, shape, weight ) values ( 'red', 'cylinder', null );

insert into bricks ( color, shape ) values ( 'red', 'cylinder' );

insert into bricks ( weight, color, shape ) values ( 'red', 'cylinder', null );

Which of the following statements about update are true?

An update overwrites existing values in a row

You specify new values for columns in the set clause

It changes all the rows that match the where clause

You can change all the columns of a table in a single update

Only one user can update a given row in a table at a time

True

False

Which of the following statements are true about database transactions?

Every transaction only changes one table

You use transactions to make the smallest set of changes which leave your data in a consistent state

A transaction ends when you issue a commit

Two or more transactions changing the same rows at the same time can lead to deadlock

A database transaction always transfers money from one person to another

You create the table toys and add the following rows: create table toys ( toy_name varchar2(30), price number ); insert into toys values ( 'Baby Turtle' , 5 ); insert into toys values ( 'Purple Ninja', 9 ); insert into toys values ( 'Cuteasaurus' , 20 ); commit; Which of the following choices will increase the price of all the rows by one? So after a correct choice, the prices of the toys are: select * from toys; TOY_NAME PRICE Baby Turtle 6 Purple Ninja 10 Cuteasaurus 21

update toys set price = 6 where toy_name = 'Baby Turtle'; update toys set price = 10 where toy_name = 'Purple Ninja'; update toys set price = 21 where toy_name = 'Cuteasaurus';

update toys set price + 1;

update toys set price = price + 1;

update toys set price = ( 6, 10, 21 );

Which of the following statements can you use to remove rows from a table in Oracle Database?

Truncate commits

True

False

Quiz Which of the following are benefits of using soft deletes to "remove" rows from your application? of the following statements can you use to remove rows from a table in Oracle Database?

You can recover "deleted" rows without going to backup

It makes your code more straightforward

It can make it harder for users to cover up cases of fraud or other double-dealing

It means you have less data in the table

You store the following animal details in this table: create table animals ( animal_name varchar2(30), birth_date date, species varchar2(30) ); insert into animals values ( 'Lonesome George', date'1912-01-01', 'tortoise' ); insert into animals values ( 'Smoky', date'1943-07-07', 'dog' ); insert into animals values ( 'Xiang Xiang', date'2001-09-09', 'panda' ); commit; Which of the following choices remove all the rows except the one for Lonesome George? So after a correct choice, when you query animals the only row you get is: select * from animals; ANIMAL_NAME BIRTH_DATE SPECIES Lonesome George 01-JAN-12 tortoise

delete animals;

delete animals where animal_name <> 'Lonesome George';

delete animals where species in ( 'dog', 'panda' );

delete animals where birth_date >= date'1920-01-01';

truncate table animals where animal_name <> 'Lonesome George';

Your application stores toy details in this table: create table toys ( toy_id integer, toy_name varchar2(30), colour varchar2(10), price number(10, 2) ); Which of the following choices will insert a row into it?

insert into toys values ( 1, 10.99, 'Mr. Snuggles', 'blue' );

insert into toys values ( 2, 'Mr. Snuggles', 'blue', 10.99 );

insert into toys ( toy_id, toy_name, colour ) values ( 3, 'Mr. Snuggles', 'blue', 10.99 );

insert into toys ( toy_id, price, toy_name, colour ) values ( 4, 10.99, 'Mr. Snuggles', 'blue' );

insert into toys ( toy_id, price ) values ( 5, 10.99 );

Quiz Which of the following statements about lost updates are true?

A lost update happens when one user overwrites another's changes

Insert statements can cause lost updates

You can stop lost updates with optimistic locking

Lost updates can only happen in stateful applications

You use "select for update" to stop lost updates in stateless applications

True or false: In the following statement, Oracle Database will add each row in toys into at most one of blue_toys, cheap_toys or expensive_toys: insert first when colour = 'blue' then into blue_toys values (toy_name, price) when price >= 0 then into cheap_toys values (toy_name, price, colour) when price > 20 then into expensive_toys values (toy_name, price, colour) select toy_name, price, colour from toys;

True

False

Which of the following statements are true about deadlocks?

Deadlock happens when two or more people try to lock the same rows in a different order

You have to manually kill sessions to resolve deadlocks

You can use optimistic locking to avoid deadlocks

To reduce the chance of deadlock, at the start of your transaction you should lock all the rows you intend to change in a table with "select ... for update"

You've created this table to store details about bricks: create table bricks ( colour varchar2(10), shape varchar2(10) ); Which of the following choices add a row to the table and then remove it, so when you run this after the choice: select * from bricks; It returns no rows? You can assume that the table is empty before you run each choice. Please select all that apply.

insert into bricks (colour, shape) values ('blue', 'cube'); delete bricks;

insert into bricks (colour, shape) values ('blue', 'cube'); delete bricks where colour = 'green';

insert into bricks (colour, shape) values ('blue', 'cube'); truncate table bricks;

insert into bricks (colour, shape) values ('blue', 'cube'); rollback;

insert into bricks (colour, shape) values ('blue', 'cube'); update bricks set colour = null, shape = null;

Week 1: Tables

Week 2:Columns and Data Types

Week 3:Data Modeling

Week 4: Foundations: Tables, Columns and Modeling Review

Week 5:Select and Where

Week 6:Joins

Week 7:Aggregates and Group By

Week 8:Select, Joins and Group By Review

Week 9:Insert and Commit

Week 10:Update and Transactions

Week 11:Delete and Truncate

Week 12:Foundations: Insert, Update and Delete Review

Subscribe Form