Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

34 cards

()

Section 1

Preview this deck

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

Front

1 / 34

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

7 years ago

Date created

Mar 1, 2020

Cards (34)

Section 1

(34 cards)

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

Front

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

Back

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

Front

MAX

Back

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".

Front

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

Back

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.

Front

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

Back

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

Front

SUM

Back

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

Front

From

Back

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

Front

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

Back

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).

Front

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

Back

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

Front

AVG

Back

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.

Front

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;

Back

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

Front

FROM orders o, customers c

Back

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

Front

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

Back

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

Front

LOWER

Back

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.

Front

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

Back

Determine how many orders have been placed by each customer.

Front

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

Back

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

Front

ROUND(x, 0)

Back

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"."

Front

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

Back

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.

Front

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

Back

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

Front

UPPER

Back

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

Front

COUNT

Back

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

Front

In or Or

Back

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.

Front

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

Back

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

Front

MONTHS_BETWEEN

Back

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

Front

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

Back

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.

Front

90

Back

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

Front

Select and Where

Back

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.

Front

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

Back

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

Front

Self

Back

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

Front

orders.order#

Back

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

Front

Single-row function

Back

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?

Front

Cartesian join

Back

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

Front

Equality join

Back

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.

Front

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 (+);

Back

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

Front

functions

Back