Download Retrieving Data Using the SQL SELECT Statement-Introduction to Database Systems-Lecture Handouts and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!
Lecture 2
1) Retrieving Data Using the SQL SELECT Statement
Selecting All Columns
SELECT *
FROM departments;
Selecting Specific Columns
SELECT department_id, location_id FROM departments;
SELECT last_name FROM employees;
Using Arithmetic Operators
SELECT last_name, salary, salary + 300 FROM employees;
Operator Precedence
1) SELECT last_name, salary, 12*salary+
FROM employees;
SELECT last_name, salary, 12*(salary+100) FROM employees;
Defining a Null Value
SELECT last_name, job_id, salary, commission_pct FROM employees;
Null Values in Arithmetic Expressions
SELECT last_name, 12salarycommission_pct FROM employees;
Using Column Aliases
SELECT last_name AS name, commission_pct comm FROM employees;
SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees;
Concatenation Operator
SELECT last_name||job_id AS "Employees" FROM employees;
Using Literal Character Strings
SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;
Duplicate Rows
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;
Displaying Table Structure
DESCRIBE employees
SELECT first_name FROM employees WHERE first_name LIKE 'S%' ;
SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;
Using the NULL Conditions
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;
Using the AND Operator
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= AND job_id LIKE '%MAN%' ;
Using the OR Operator SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%' ;
Using the NOT Operator
SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
Using the ORDER BY Clause SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;
Sorting
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ;
SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal ;
3) SELECT last_name, department_id, salary
FROM employees ORDER BY department_id, salary DESC;
3) Using Single-Row Functions to Customize
output
Using Case-Manipulation Functions
SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins';
SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins';
Using the Character-Manipulation Functions
SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP';
Using the NVL Function
SELECT last_name, salary, NVL(commission_pct, 0), (salary12) + (salary12*NVL(commission_pct, 0)) AN_SAL FROM employees;
Using the NVL2 Function SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80);
Using the NULLIF Function
SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;
Using the COALESCE Function SELECT last_name, COALESCE(manager_id,commission_pct, -1) comm FROM employees ORDER BY commission_pct;
CASE Expression SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10salary WHEN 'ST_CLERK' THEN 1.15salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;
Using the DECODE Function
SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10salary, 'ST_CLERK', 1.15salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees;
SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHERE department_id = 80;
