Saturday, October 15, 2016

02 Anatomy of a SQL Statement

  • ·         Display data from two or more related tables.- join
  • ·         A symbol used to perform an operation on some values.- Arithmetic operator
  • ·         An implementation of an attribute or relationship in a table.- column
  • ·         The capability in SQL to choose the columns in a table that you want returned from a query.- projection
  • ·         A value that is unavailable, unassigned, unknown, or inapplicable.- null
  • ·         Renames a column heading.- column alias
  • ·         A mathematical equation.- Arithmetic expression
  • ·         The capability in SQL to choose the rows in a table returned from a query.- selection
  • ·         Retrieves information from the database -SELECT
  • ·         Specifies the columns to be displayed. - select clause
  • ·         Specifies the table containing the column listed in the select clause from clause
  • ·         An individual SQL statement. - SQL command. (SELECT and FROM are keywords.)
  • ·         Part of a SQL statement.- clause 

Now you know the basics of a SELECT statement, it’s time to practice what you've learned.
1. Write a SQL statement that demonstrates projection and selection.
SELECT constraint_name
FROM user_constraints
WHERE table_name in (upper('f_shifts'), upper('F_STAFFS'));
2. Write a query that displays the last_name and email addresses for all the people in the DJs on Demand d_client table. The column headings should appear as “Client” and “Email Address.”

SELECT last_name AS "Client", email AS "Email Address"
FROM d_clients; 
3. The manager of Global Fast Foods decided to give all employees at 5%/hour raise + a $.50 bonus/hour. However, when he looked at the results, he couldn't figure out why the new raises were not as he predicted. Ms. Doe should have a new salary of $7.59, Mr. Miller's salary should be $11.00, and Monique Tuttle should be $63.50. He used the following query. What should he have done?
SELECT last_name, salary *.05 +.50
FROM f_staffs;
He assumed wrong that if he has to give 5% raise, he should multiply by ‘.05’. It should be multiplied by ‘1.05’ and rounded to two digits after decimal. Precedence he knows about correctly.  (*/+- grouped, */ being higher precedence than +-  and then left to right in statement  for the two in same group)
SELECT last_name, ROUND(salary*1.05 +.50, 2) as "Salary"
FROM f_staffs;

4. Which of the following would be the easiest way to see all rows in the d_songs table?
a. SELECT id, title, duration, artist, type_code
b. SELECT columns
d. SELECT all
5. If tax = 8.5% * car_cost and license = car_cost * .01%, which value will produce the largest car payment?
a. Payment = (car_cost * 1.25) + 5.00 - (tax) - (license)
b. Payment = car_cost * 1.25 + 5.00 - (tax - license)
6. In the example below, identify the keywords, the clause(s), and the statement(s):
SELECT employee_id, last_name
FROM employees;
SELECT employee_id, last_name
FROM employees
Select Clause:
 SELECT employee_id, last_name
FROM Clause:
FROM employees

7. Label each example as SELECTION, PROJECTION, or JOIN.
a. Please give me Mary Adam's email address. SELECTION
b. I will need each customer's name and the order_total for their order. JOIN
c. I would like only the manager_id column, and none of the other columns. PROJECTION
8. Which of the following statements are true?
a. null * 25 = 0;
b. null * 6.00 = 6.00
c. null * .05 = null
d. (null + 1.00) + 5.00 = 5.00
9. How will the column headings be labeled in the following example?
SELECT bear_id bears, color AS Color, age “age”
FROM animals;
a. bears, color, age
c. BEARS, COLOR, age
d. Bears, Color, Age
10.Which of the following words must be in a SELECT statement in order to return all rows?
a. SELECT only
c. FROM only

d. SELECT * only

No comments:

Post a Comment