SQL queries review

Concept Review

• What does (relational) DB bring?
  • It’s actually a collection of info organized to afford efficient retrieval.
  • DBMS: software packages designed to store, access, manage DBs.
• In relational DB:
  • Every thing is table.
  • Under the basic structure, relational algebra is working, closure & optimize.
  • Always keep in mind of algebra optimization when querying. | DBMS | Database | Schema | Table | Attribute |
    | — | — | — | — | — |
    | Property Firm | House | Floor Plan | Room | Decoration |

Join

• Be used to apply Non-Equi-Joins

  select e.emp_id, e.fname
  from employee e Inner Join product p
  On e.s_date>= p.date_offer
  And e.s_date< p.date_retire

• Self-Joins and use subqueries as tables

Aggregation

Be careful about implicit/explicit groups.
Example 1: group ~ product_cd = ‘CHK’

SELECT MAX(avail_balance) max_balance,
-> MIN(avail_balance) min_balance,
-> AVG(avail_balance) avg_balance,
-> SUM(avail_balance) tot_balance,
-> COUNT(*) num_accounts
-> FROM account
-> WHERE product_cd = 'CHK';

Example 2: group ~ product_cd

SELECT product_cd,
-> MAX(avail_balance) max_balance,
-> MIN(avail_balance) min_balance,
-> AVG(avail_balance) avg_balance,
-> SUM(avail_balance) tot_balance,
-> COUNT(*) num_accts
-> FROM account
-> GROUP BY product_cd;

Example 3: Multicolumn Grouping, understanding: what being grouped on will perform as “P Key” on the resulting table.

SELECT product_cd, open_branch_id,
-> SUM(avail_balance) tot_balance
-> FROM account
-> GROUP BY product_cd, open_branch_id;

Example 4: Expressions

SELECT MAX(pending_balance - avail_balance) max_uncleared
-> FROM account;

SELECT EXTRACT(YEAR FROM start_date) year,
-> COUNT(*) how_many
-> FROM employee
-> GROUP BY EXTRACT(YEAR FROM start_date);

String manipulation (RE)

Keyword: Like
| Search Expression | Interpretation |
| — | — |
| F% | Strings beginning with F |
| %t | Strings ending with t |
| %bas% | Strings containing the substring ‘bas’ |
| __t_ | Four-character strings with a t in the third position |

Keyword: REGEXP
E.g. Find all employees whose last name starts with F or G.

SELECT emp_id, fname, lname
FROM employee
WHERE lname REGEXP '^[FG]';

Mistakes collection when Writing queries

• If you want to generate a column/set, must select the data from table in the first place.
  Demo.