[go: up one dir, main page]
Scribd
Upload
287 views3 pages

DP3 Practice Activities - Answers

This document contains practice activities for logical comparisons, sorting rows, and introduction to functions in SQL. It includes examples of queries using logical operators like AND, OR and NOT. It also demonstrates sorting results in ascending and descending order using the ORDER BY clause. Finally, it provides examples of single-row and multiple-row functions like AVG, COUNT, MAX, MIN and SUM used to calculate and return values from a table.

Uploaded by

gery sumual
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
287 views3 pages

DP3 Practice Activities - Answers

This document contains practice activities for logical comparisons, sorting rows, and introduction to functions in SQL. It includes examples of queries using logical operators like AND, OR and NOT. It also demonstrates sorting results in ascending and descending order using the ORDER BY clause. Finally, it provides examples of single-row and multiple-row functions like AVG, COUNT, MAX, MIN and SUM used to calculate and return values from a table.

Uploaded by

gery sumual
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Practice Activity for DP 3.1 – 3.

3
Gery J. Sumual (01123574291-53)

3.1 Logical Comparisons and Precedence Rules


Vocabulary: NOT, AND, Precedence, OR
Try It / Solve It:
1. SELECT code, description FROM d_themes WHERE code >200 AND description IN('Tropical',
'Football', 'Carnival');
SELECT code, description FROM d_themes WHERE code >200 OR description IN('Tropical',
'Football', 'Carnival');
These two nearly identical queries produce different results because the first one requires
both conditions to be True, it means the resulting data must have code above 200 and must
have one of the three specified descriptions; For the second one, the data only requires
either condition to be True in order to be returned.
2. SELECT last_name
FROM f_staffs
WHERE last_name LIKE '%e%' and last_name LIKE '%i%';
3. SELECT *
FROM f_staffs
WHERE salary > 6.50 and staff_type !='Order Taker';
4. SELECT last_name
FROM employees
WHERE last_name LIKE 'D%' and last_name LIKE '%a%' and last_name LIKE '%e%';
5. SELECT *
FROM d_venues
WHERE NOT loc_type = 'Private Home';
6. c. NOT, AND, OR.
7. SELECT first_name, last_name, hire_date, salary
FROM employees
WHERE hire_date BETWEEN '01-jun-1998' AND '31-may-1999' AND salary < 8000 AND
last_name LIKE '%en%';
8. SELECT first_name, last_name, hire_date, salary, email,commission_pct
FROM employees
WHERE hire_date >= '01-jan-1996' AND salary > 9000 AND commission_pct IS NULL;
3.2 Sorting Rows
Vocabulary: ASC, DESC, ORDER BY clause.
Try It / Solve It:
1. SELECT employee_id AS "Number", first_name, last_name
FROM employees
ORDER BY "Number";
2. SELECT employee_id AS "Number", first_name, last_name
FROM employees
ORDER BY "Number";
3. SELECT title "Our Collection"
FROM d_cds
ORDER BY "Our Collection" DESC;
4. SELECT first_name, last_name, ID_num, parking_num
FROM students
WHERE year = ‘First Year’
ORDER BY last_name, first_name DESC;
5. SELECT department_id, last_name, Manager_id
FROM employees
WHERE employee_id < 125
ORDER BY department_id DESC, last_name DESC;
Extension Activities:
1. Selection
2. ORDER BY
3. SELECT, FROM, COLUMN
4. a, b, c
5. c
6. d
7. a, b, c, d
8. b, c
9. a, d
10. a, b
11. WHERE last_name LIKE ‘St%’;
12. Less than 1900 and more than 2100
13. a. WHERE department_id NOT IN (101, 102, 203);
b. WHERE last_name = ‘King’
c. WHERE start_date = ’05-MAY-1998’
d. WHERE salary BETWEEN 5000 AND 7000;
e. WHERE id != 10;
3.3 Introduction to Functions
Try It / Solve It:
1. a. single-row function
b. multiple row function
c. single-row function
d. multiple row function
e. single-row function
f. single-row function
2. AVG: to calculate and return the average of the input values
COUNT: to count the amount of data in the specified range of the input
MAX: to compare and return the maximum value among the specified input values
MIN: to compare and return the minimum value among the specified input values
SUM: to calculate the sum of the specified input values
3. SELECT AVG(salary)
FROM employees; (output: 8775)
SELECT COUNT(salary)
FROM employees; (output: 20)
SELECT MAX(salary)
FROM employees; (output: 24000)
SELECT MIN(salary)
FROM employees; (output: 2500)
SELECT SUM(salary)
FROM employees; (output: 175500)

You might also like