SQL queries, part I
Table of contents:
- How to display length of fieald in table?
- How to select unique name?
- Dipslay all departments - example
- Select employee with WHERE clause, checking employee with the name equal to: ‘Jessica’
- Check employee with the last name starting from: ‘J’
- Check employee with: ‘e’ character in name
- Example of filtering on data value
- Create a column aliases
Ok, let’s practice basic database queries, SQL Oracle Database. Below is the database schema
- Database Schema
-
How to display length of fieald in table?
select first_name, length(first_name) from employees; Output:
FIRST_NAME LENGTH(FIRST_NAME)
-------------------- ------------------
Ellen 5
Sundar 6
Mozhe 5
David 5
Hermann 7
Shelli 6
Amit 4
Elizabeth 9
Sarah 5
David 5
Laura 5
FIRST_NAME LENGTH(FIRST_NAME)
-------------------- ------------------
Harrison 8
Alexis 6
Anthony 7
Gerald 6
Nanette 7
John 4
Kelly 5
Karen 5
Curtis 6
Lex 3
Julia 5-
How to select unique name?
select distinct(first_name) from employees;Output:
Ellen
Mozhe
Hermann
Alberto
Britney
Jean
Timothy
Ki
Nancy
Peter
Michael
Shelley
Steven
Samuel
Christopher
Lindsey
Sigal
Jose Manuel
...- (standard test data)
Using SELECT DISTINCT in #SQL removes duplicates rows
— SQL Daily (@sqldaily) September 13, 2021
But often adding this is a sign you should change your query e.g.
To find all the departments with employees earning > X
Instead of
SELECT DISTINCT ... JOIN
Use
EXISTS for just the depts
or
GROUP BY to count the emps pic.twitter.com/hV7bbiVQNL
-
Dipslay all departments
select * from Departments;Output:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
...Joining tables on columns with the same name?
— SQL Daily (@sqldaily) May 18, 2021
Instead of checking they're equal in the ON clause you can use USING
t1 JOIN t2
USING ( col_on_both_tables )
This deduplicates the join columns so they only appear once in SELECT *@vlad_mihalcea discusses https://t.co/e3ZurGCaNC pic.twitter.com/00ERxNXDFV
-
Select employee with WHERE clause, checking employee with the name equal to: ‘Jessica’
select first_name, last_name, salary from employee
where first_name = 'Jessica';Jessica Elliott 21000
Jessica Sullivan 21000
Jessica Bennett 83000-
Check employee with the last name starting from: ‘J’
select employee_id, first_name, last_name
from employee
where last_name like 'J%';Ouptut:
9 Kathleen Jones
44 Philip Jordan
45 Lori Jacobs
99 Mark Jackson
108 Frank JonesFor some search conditions estimating how many rows it returns is hard, e.g.
— SQL Daily (@sqldaily) September 8, 2021
WHERE col LIKE '%A1%'
Often leading to suboptimal plans => slow #SQL@vldbb shows how the hint
/*+ dynamic_sampling(5) */
in Oracle Database helps improve these estimateshttps://t.co/h1kHiEYo4E pic.twitter.com/HU5D8mcsCX
-
Check employee with: ‘e’ character in name
select employee_id, first_name, last_name
from employee
where last_name like '%e%';EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 Michelle Foster
2 Cheryl Turner
4 Patrick Berry
5 Doris Powell
9 Kathleen Jones
11 Norma Henry
12 Evelyn Gonzalez
14 Bruce Mitchell
17 Barbara Morales
18 Ralph Anderson
19 Lois Butler
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
23 Teresa Bell
24 Anthony Rice
25 Joseph Berry
26 Alice Perry
27 Ralph Montgomery
28 Roy Lane
29 Kenneth Bennett
33 Donna Martinez
35 Robert Gilbert
38 David Bailey
39 Fred Wallace -
Example of filtering on data value
select employee_id, first_name, last_name, salary, department_id, hire_date
from employee
where first_name = 'John' or last_name = 'Diaz';Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID HIRE_DATE
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ------------- ---------
81 Joyce Diaz 113000 2 03-NOV-10
116 John Diaz 75000 2 25-MAY-16
150 Heather Diaz 63000 5 03-AUG-12
173 John Hayes 85000 8 23-MAR-14
177 Craig Diaz -
Create a column aliases
select e.employee_id as "Employee", e.first_name as "First name", e.last_name as "Last name", e.salary as "Salary", e.salary/12 as "1 month salary",
e.salary/56 as "56/salary", e.salary * 12 as "Salary multiply per 12"
from employee e;Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID HIRE_DATE
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ------------- ---------
81 Joyce Diaz 113000 2 03-NOV-10
116 John Diaz 75000 2 25-MAY-16
150 Heather Diaz 63000 5 03-AUG-12
173 John Hayes 85000 8 23-MAR-14
177 Craig Diaz 114000 5 24-DEC-13
no rows selected
Employee First name Last name Salary 1 month salary 56/salary Salary multiply per 12
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- -------------- ---------- ----------------------
1 Michelle Foster 48000 4000 857.142857 576000
2 Cheryl Turner 79000 6583.33333 1410.71429 948000
3 Carolyn Hudson 47000 3916.66667 839.285714 564000
4 Patrick Berry 51000 4250 910.714286 612000
5 Doris Powell 117000 9750 2089.28571 1404000
6 Jessica Elliott 21000 1750 375 252000
7 Sean Burns 51000 4250 910.714286 612000
8 Ann Bowman 34000 2833.33333 607.142857 408000
9 Kathleen Jones 92000 7666.66667 1642.85714 1104000
10 Scott Cruz 32000 2666.66667 571.428571 384000
11 Norma Henry Reference:
My site is free of ads and trackers. Was this post helpful to you? Why not
Disqus is great for comments/feedback but I had no idea it came with these gaudy ads.