-
Write the SELECT statement to display the average hourly_pay, display the heading as ‘Average Pay’.
- SELECT ROUND(AVG(emp_hourly_pay),2) AS 'Average Pay'
- FROM employees;
-
3. Write the SELECT statement that returns the employee’s first name and hourly pay for only those employee’s pays that are less than the average of all the employees. Use a subquery.
- SELECT emp_first_name, emp_hourly_pay
- FROM employees
- WHERE emp_hourly_pay>
- (SELECT AVG(emp_hourly_pay)
- FROM employees);
-
5. Write a SELECT statement to display the project number and the number of employees assigned to each project.
- SELECT project_number, count(*)
- FROM project
- GROUP BY project_number;
-
What are the aggregate functions?
- COUNT ( [ALL|DISTINCT] expression)
- COUNT (*)
- AVG ( [ALL|DISTINCT] expression)
- SUM ( [ALL|DISTINCT] expression)
- MAX ( [ALL|DISTINCT] expression)
- MIN ( [ALL|DISTINCT] expression)
-
summary data is group
- SELECT selection_list
- FROM table
- [ WHERE search_conditions ]
- [ GROUP BY group_by_list ]
- [ HAVING search_condition ]
- [ ORDER BY order_by_list ]
-
Db2 SUM
CREATE TABLE sum_demo(c1 INT);
INSERT INTO sum_demo(c1) VALUES(1),(2),(3),(3),(NULL);
sum all
sum but don't include duplicates
SUM(ALL | DISTINCT expression)
- SELECT SUM(c1) FROM sum_demo;
- SELECT SUM(DISTINCT c1) total_distinctFROM sum_demo;
-
Write the statement to get author_id, first_name, and last_name for all authors whos last name is Anderson regardless of case.
- SELECT author_id, first_name, last_name
- FROM authors
- WHERE UPPER(last_name) = 'ANDERSON';
-
Write the select statement to concatenate first and last names
- SELECT CONCAT(FIRSTNME, LASTNAME)
- FROM DSN8A10.EMP;
-
basic query
- SELECT column1, column2....columnN
- FROM table_name
- WHERE CONDITION
- ORDER BY column_name {ASC|DESC};
-
query unique
- SELECT DISTINCT column1, column2....columnN
- FROM table_name;
-
query in range
- SELECT column1, column2....columnN
- FROM table_name
- WHERE column_name BETWEEN val-1 AND val-2;
-
The person table has the following rows.
- 1, 'Joe', 'Smith', 182
- 2, 'Kim', 'Good' , 175
- 3, 'Mark', 'Allan' , 168
- 4, 'Sam', , 187
- 5, 'Bob', 'Claw' ,
- 6, 'David', 'Ellie' , 174
- Find all the row with names that start with J.
- Select * FROM PERSON
- WHERE LEFT(FNAME,1) = 'J';
-
The person table has the following rows.
- 1, 'Joe', 'Smith', 182
- 2, 'Kim', 'Good' , 175
- 3, 'Mark', 'Allan' , 168
- 4, 'Sam', , 187
- 5, 'Bob', 'Claw' ,
- 6, 'David', 'Ellie' , 174
- Join the initials
- select concat(left(fname,1),left(lname,1))
- from person;
-
The person table has the following rows.
- 1, 'Joe', 'Smith', 182
- 2, 'Kim', 'Good' , 175
- 3, 'Mark', 'Allan' , 168
- 4, 'Sam', , 187
- 5, 'Bob', 'Claw' ,
- 6, 'David', 'Ellie' , 174
- Find all the rows with name that start with 'Jo' without using LEFT or LIKE
- SELECT * FROM person
- WHERE SUBSTR(fname,1,2) = 'Jo';
-
Find all the rows where the price is 16.30 to the nearest dime.
- SELECT * FROM tableName
- WHERE ROUND(PRICE,1) = 16.3
-
What is CASE (with an expression)?
- A CASE expression allows an expression to be selected based on the evaluation of one or more conditions.
- Like a COBOL EVALUATE.
- CASE field_name
- WHEN 'A' THEN someA
- WHEN 'B' THEN someB
- END
-
What is CASE (without an expression)
- A CASE expression allows an expression to be selected based on the evaluation of one or more conditions.
- Like a COBOL EVALUATE.
- CASE
- WHEN fieldname = 'A' THEN someA
- WHEN fieldname ='B' THEN someB
- ELSE someC
- END
- The ELSE is optional.
-
What is COALESCE?
- The COALESCE function returns the value of the first nonnull expression.
- COALESCE(expression1, expression2,...)
- If expression1 is not null
- return expression 1
- else if expression 2 is not null
- return expression2
- else if expression3 is not null
- ...
|
|
