SQL

In a Cartesian join, linking a table that contains 10 rows to a table that contains 9 rows will result in ____ rows being displayed in the output.

90

The ____ function calculates the average of the numeric values in a specified column.

AVG

Which of the following types of joins refers to results consisting of each row from the first table being replicated from every row in the second table?

Cartesian join

The ____ function can be used to determine the number of rows containing a specified value.

COUNT

Which of the following types of joins is created by matching equivalent values in each table?

Equality join

Which of the following is an example of assigning "o" as a table alias for the ORDERS table in the FROM clause?

FROM orders o, customers c

The JOIN keyword is included in which of the following clauses?

From

Which of the following is used to create an outer join in a where clause?

In or Or

Which of the following functions can be used to convert a character string to lower-case letters?

LOWER

The ____ function can be used to determine the largest value stored in a specified column.

MAX

Which of the following functions is used to determine the number of months between two date values?

MONTHS_BETWEEN

Which of the following functions will round the numeric data to no decimal places?

ROUND(x, 0)

An employees table was added to the justlee books database to track employee information. Write a query that will display a list of each employee name, job title, and managers name. Include ALL employees in the list and sort by manager name.

SELECT A. EMPNO , A. FNAME|| ' ' || A. LNAME "EMPLOYEE NAME" , B . FNAME|| ' ' ||B . LNAME "MANAGER NAME" FROM EMPLOYEES a, EMPLOYEES b WHERE A. MGR = B . EMPNO (+);

Write a query that will return the firstname and lastname of all customers with the firstname capitalized and the following letters in lower case.

SELECT INITCAP (firstname) "First Name", initcap (lastname) "Last Name" FROM customers;

Determine the average retail price of books by publisher name and category. Include only the categories children and computers and the groups with an average retail price greater than $50.

SELECT name, category, AVG(retail) FROM books JOIN publisher USING(pubid) WHERE category IN('COMPUTER', 'CHILDREN') GROUP BY name, category having avg(retail) > 50;

The _____ function is used to calculate the total amount stored in a numeric field

SUM

In Oracle 12c, tables can be linked through which clause(s)?

Select and Where

Which of the following types of joins refers to joining a table to itself?

Self

Which of the following returns one row of results for each record processed?

Single-row function

Which of the following functions can be used to convert a character string to upper-case letters?

UPPER

Which of the following refers to a predefined block of code?

functions

Write a query that will join tables based on an INEQUALITY. Using the traditional method, show what gift a customer will receive when they buy a book. The filed needed are title (from books) and gift (from the promotions table). Additional fields needed to complete this query include: retail (from books), and minretail and maxretail (from promotions).

select b.title, p.gift from books b, promotion p where b.retail between p.minretail and p.maxretail;

Complete the same query in question 21 using the Join....using method

select b.title, pubid, p.contact, p.phone from publisher p join books b using (pubid);

In which of the following examples is the ORDERS table used as a column qualifier?

orders.order#

Using the books table, write a query that will count how many categories our bookstore has. Include a keyword that you learned back in our first few weeks that will keep it from displaying duplicate results.

select COUNT(DISTINCT CATEGORY) "Categories" from books;

Do the same query in question 29, but this time limit the results to just the COOKING category. 29. "Determine average profit generated by books in the books table. Please remember to calculate profit first. Give the group function output the alias "Total Profit"."

select avg(retail-cost) "total profit" from books where category = 'COOKING';

Determine average profit generated by books in the books table. Please remember to calculate profit first. Give the group function output the alias "Total Profit".

select avg(retail-cost) "total profit" from books;

Using the traditional method create a query that will display a list of the title of each book (book table) and the name and phone number of the person at the Publishers (publisher table) whom you would need to contact to reorder each book

select b.title, p.contact, p.phone from publisher p natural join books b;

Write a query that will show charge status, the total of each charge status, the average of fine amount plus court fee, and the standard deviation of fine amount plus court fees. Group the results by charge status. Add appropriate aliases for the average total charge and standard deviation columns.

select charge_status, count(*), avg(fine_amount+court_fee) "Avg Total Charge", stddev(fine_amount+court_fee) "Standard Deviation" from crime_charges group by charge_status;

Display the number of books with a retail price less than $50.

select count(*) from books where retail < 50;

Determine how many orders have been placed by each customer.

select customer#, count(distinct order#) "Orders" from orders group by customer#;

Create a query that will return the firstname and lastname of JASMINE LEE from the customers table, when her name is entered in lower case.

select firstname, lastname from customers where lower(lastname) = 'LEE';

Using the months between function, write a query that will show title, orderdate and pubdate. To accomplish this you will need to join 3 tables: books table to orderitems table and then join orders. Include the alias "Months Between" for the function column.

select title, MONTHS_BETWEEN(orderdate, pubdate) "Months Between" from books Join orderitems using(isbn) join orders using (order#);

Using the books table, write a query that will show % of profit for each book sold. Include the title and assign an alias called Profit for the arithmetic operation. To get the % of profit for each book sold, subtract retail from cost, then divide cost by *100. ROUND the Profit output.

select title, ROUND(((retail-cost)/retail*100),0)||'%' AS "Profit Percent" from books;