SYNTAX: SELECT *|{[DISTINCT] column [alias],...} FROM table;
In the syntax:
SELECT Is a list of one or more columns
* Selects all columns
DISTINCT Suppresses duplicates
column|expression Selects the named column or the expression
alias Gives different headings to the selected columns
FROM table Specifies the table containing the columns
-------------------------------------------------------------------------
*****SELECTING ALL COLUMNS*****
SELECT *FROM departments;
*****SELECTING SPECIFIC COLUMNS*****
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM departments;
SELECT DEPARTMENT_ID,LOCATION_ID FROM departments;
-------------------------------------------------------------------------
*****COLUMN HEADING DEFAULTS*****
SELECT LAST_NAME,HIRE_DATE,SALARY FROM employees;
-------------------------------------------------------------------------
*****USING AIRTHEMATIC OPERATIONS*****
SELECT LAST_NAME,SALARY,SALARY+300 FROM employees;
SELECT LAST_NAME,SALARY,SALARY+300, salary-100 FROM employees;
Retrieving Data Using the SQL SELECT Statement
-------------------------------------------------------------------------
*****OPERATOR PRECEDENCE*****
SELECT LAST_NAME,SALARY,12*SALARY+100 FROM employees;
SELECT LAST_NAME,SALARY,SALARY+300-100 FROM employees;
-------------------------------------------------------------------------
*****DEFINING A NULL VALUE*****
SELECT LAST_NAME,JOB_ID,SALARY,COMMISSION_PCT FROM employees;
-------------------------------------------------------------------------
*****NULL VALUES IN ARITHEMATIC EXPRESSIONS*****
SELECT LAST_NAME,JOB_ID,12*SALARY*COMMISSION_PCT FROM employees;
-------------------------------------------------------------------------
*****USING COLUMN ALIASES*****
SELECT LAST_NAME AS NAME,SALARY AS SAL FROM employees;
-------------------------------------------------------------------------
*****USING CONCATENATION OPERATOR*****
SELECT LAST_NAME||JOB_ID AS "EMPLOYEES" FROM employees;
-------------------------------------------------------------------------
*****USING LITERAL CHARACTER STRING*****
SELECT LAST_NAME || ' is a ' ||JOB_ID AS "EMPLOYEE DETAILS" FROM employees;
SELECT LAST_NAME ||' : 1MONTH SALARY = '||SALARY MONTHLY FROM EMPLOYEES;
-------------------------------------------------------------------------
*****ALTERNATIVE QUOTE (q) OPERATOR*****
SELECT DEPARTMENT_NAME || ' DEPARTMENTS MANAGER ID: '|| MANAGER_ID AS "DEPARTMENT AND MANAGER" FROM DEPARTMENTS;
-------------------------------------------------------------------------
*****DUPLICATE ROWS*****
SELECT DEPARTMENT_ID FROM EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT first_name, last_name, job_id, salary*12 AS "Yearly Sal" FROM employees;
-------------------------------------------------------------------------
*******-----Restricting and Sorting Data-----*****
Use the WHERE clause to restrict rows of output:
– Use the comparison conditions
– Use the BETWEEN, IN, LIKE, and NULL operators
– Apply the logical AND, OR, and NOT operators
• Use the ORDER BY clause to sort rows of output:
• Use ampersand substitution to restrict and sort output at
run time
SYNTAX:
SELECT {*|[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]] ;
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE logical expression(s)];
Limiting the Rows That Are Selected
• Restrict the rows that are returned by using the WHERE
clause:
• The WHERE clause follows the FROM clause.
-------------------------------------------------------------------------
*****USING THE WHERE CLAUSE*****
SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE department_id=10;
-------------------------------------------------------------------------
*****CHARACTER STRINGS AND DATES*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'WHALEN';
SELECT LAST_NAME FROM EMPLOYEES WHERE HIRE_DATE = '17-OCT-03';
-------------------------------------------------------------------------
*****USING COMPARISION OPERATORS*****
SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE salary<=3000;
SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE salary>=3000;
-------------------------------------------------------------------------
*****USING BETWEEN OPERATORS*****
SELECT LAST_NAME,SALARY FROM employees WHERE SALARY BETWEEN 2500 AND 3500;
SELECT LAST_NAME FROM EMPLOYEES WHERE last_name BETWEEN 'KING' AND 'SMITH';
-------------------------------------------------------------------------
*****USING IN OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IN (100,101,200);
SELECT EMPLOYEE_ID,MANAGER_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME IN ('KOCHHAR','VARGAS');
SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ('Hartstein', 'Vargas');
-------------------------------------------------------------------------
*****USING LIKE OPERATOR*****
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%';
SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '%05';
-------------------------------------------------------------------------
*****USING WILDCARD CHARACTERS*****
SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;
SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
-------------------------------------------------------------------------
*****USINIG NULL CONDITION*****
SELECT LAST_NAME,MANAGER_ID FROM employees WHERE MANAGER_ID IS NULL;
SELECT LAST_NAME,JOB_ID,COMMISSION_PCT FROM employees WHERE commission_pct IS NULL;
-------------------------------------------------------------------------
*****USING AND OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY FROM EMPLOYEES WHERE SALARY >=10000 AND JOB_ID LIKE '%MAN%' ;
-------------------------------------------------------------------------
*****USING OR OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY FROM employees WHERE SALARY >=10000 OR JOB_ID LIKE '%MAN%';
-------------------------------------------------------------------------
*****USING NOT OPERATOR*****
SELECT LAST_NAME,JOB_ID FROM employees WHERE JOB_ID NOT IN ('IT_PROG','ST_CLERK','SA_REP');
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE JOB_ID NOT IN ('AC_ACCOUNT','AD_VP');
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE SALARY NOT BETWEEN 10000 AND 15000;
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE last_name NOT LIKE '%A%';
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE commission_pct IS NOT NULL;
-------------------------------------------------------------------------
*****RULES OF PRECEDENCE*****
SELECT LAST_NAME,JOB_ID,SALARY FROM employees WHERE JOB_ID = 'SA_REP' OR job_id= 'AD_PRES' AND SALARY >15000;
SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;
-------------------------------------------------------------------------
*****USING ORDER BY CLAUSE*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM EMPLOYEES ORDER BY hire_date ;
-------------------------------------------------------------------------
*****USING SORTING*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM EMPLOYEES ORDER BY hire_date DESC;
SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12 AS ANNSAL FROM employees ORDER BY ANNSAL;
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM employees ORDER BY 4;
SELECT LAST_NAME,DEPARTMENT_ID,SALARY FROM employees ORDER BY DEPARTMENT_ID, salary DESC;
-------------------------------------------------------------------------
*****Using the Single-Ampersand Substitution Variable*****
SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;
SELECT EMPLOYEE_ID FROM employees;
-------------------------------------------------------------------------
*****Character and Date Values with Substitution Variables*****
SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title' ;
SELECT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT * FROM EMPLOYEES;
-------------------------------------------------------------------------
*****Specifying Column Names, Expressions, and Text*****
SELECT employee_id, last_name, job_id,&column_name FROM employees WHERE &condition ORDER BY &order_column ;
-------------------------------------------------------------------------
*****Using the Double-Ampersand Substitution Variable*****
SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name ;
-------------------------------------------------------------------------
*****Using the DEFINE Command*****
DEFINE employee_num = 101 SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;
-------------------------------------------------------------------------
SYNTAX: SELECT *|{[DISTINCT] column [alias],...} FROM table;
In the syntax:
SELECT Is a list of one or more columns
* Selects all columns
DISTINCT Suppresses duplicates
column|expression Selects the named column or the expression
alias Gives different headings to the selected columns
FROM table Specifies the table containing the columns
-------------------------------------------------------------------------
*****SELECTING ALL COLUMNS*****
SELECT *FROM departments;
*****SELECTING SPECIFIC COLUMNS*****
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM departments;
SELECT DEPARTMENT_ID,LOCATION_ID FROM departments;
-------------------------------------------------------------------------
*****COLUMN HEADING DEFAULTS*****
SELECT LAST_NAME,HIRE_DATE,SALARY FROM employees;
-------------------------------------------------------------------------
*****USING AIRTHEMATIC OPERATIONS*****
SELECT LAST_NAME,SALARY,SALARY+300 FROM employees;
SELECT LAST_NAME,SALARY,SALARY+300, salary-100 FROM employees;
Retrieving Data Using the SQL SELECT Statement
-------------------------------------------------------------------------
*****OPERATOR PRECEDENCE*****
SELECT LAST_NAME,SALARY,12*SALARY+100 FROM employees;
SELECT LAST_NAME,SALARY,SALARY+300-100 FROM employees;
-------------------------------------------------------------------------
*****DEFINING A NULL VALUE*****
SELECT LAST_NAME,JOB_ID,SALARY,COMMISSION_PCT FROM employees;
-------------------------------------------------------------------------
*****NULL VALUES IN ARITHEMATIC EXPRESSIONS*****
SELECT LAST_NAME,JOB_ID,12*SALARY*COMMISSION_PCT FROM employees;
-------------------------------------------------------------------------
*****USING COLUMN ALIASES*****
SELECT LAST_NAME AS NAME,SALARY AS SAL FROM employees;
-------------------------------------------------------------------------
*****USING CONCATENATION OPERATOR*****
SELECT LAST_NAME||JOB_ID AS "EMPLOYEES" FROM employees;
-------------------------------------------------------------------------
*****USING LITERAL CHARACTER STRING*****
SELECT LAST_NAME || ' is a ' ||JOB_ID AS "EMPLOYEE DETAILS" FROM employees;
SELECT LAST_NAME ||' : 1MONTH SALARY = '||SALARY MONTHLY FROM EMPLOYEES;
-------------------------------------------------------------------------
*****ALTERNATIVE QUOTE (q) OPERATOR*****
SELECT DEPARTMENT_NAME || ' DEPARTMENTS MANAGER ID: '|| MANAGER_ID AS "DEPARTMENT AND MANAGER" FROM DEPARTMENTS;
-------------------------------------------------------------------------
*****DUPLICATE ROWS*****
SELECT DEPARTMENT_ID FROM EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT first_name, last_name, job_id, salary*12 AS "Yearly Sal" FROM employees;
-------------------------------------------------------------------------
*******-----Restricting and Sorting Data-----*****
Use the WHERE clause to restrict rows of output:
– Use the comparison conditions
– Use the BETWEEN, IN, LIKE, and NULL operators
– Apply the logical AND, OR, and NOT operators
• Use the ORDER BY clause to sort rows of output:
• Use ampersand substitution to restrict and sort output at
run time
SYNTAX:
SELECT {*|[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]] ;
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE logical expression(s)];
Limiting the Rows That Are Selected
• Restrict the rows that are returned by using the WHERE
clause:
• The WHERE clause follows the FROM clause.
-------------------------------------------------------------------------
*****USING THE WHERE CLAUSE*****
SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE department_id=10;
-------------------------------------------------------------------------
*****CHARACTER STRINGS AND DATES*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'WHALEN';
SELECT LAST_NAME FROM EMPLOYEES WHERE HIRE_DATE = '17-OCT-03';
-------------------------------------------------------------------------
*****USING COMPARISION OPERATORS*****
SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE salary<=3000;
SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE salary>=3000;
-------------------------------------------------------------------------
*****USING BETWEEN OPERATORS*****
SELECT LAST_NAME,SALARY FROM employees WHERE SALARY BETWEEN 2500 AND 3500;
SELECT LAST_NAME FROM EMPLOYEES WHERE last_name BETWEEN 'KING' AND 'SMITH';
-------------------------------------------------------------------------
*****USING IN OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IN (100,101,200);
SELECT EMPLOYEE_ID,MANAGER_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME IN ('KOCHHAR','VARGAS');
SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ('Hartstein', 'Vargas');
-------------------------------------------------------------------------
*****USING LIKE OPERATOR*****
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%';
SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '%05';
-------------------------------------------------------------------------
*****USING WILDCARD CHARACTERS*****
SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;
SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
-------------------------------------------------------------------------
*****USINIG NULL CONDITION*****
SELECT LAST_NAME,MANAGER_ID FROM employees WHERE MANAGER_ID IS NULL;
SELECT LAST_NAME,JOB_ID,COMMISSION_PCT FROM employees WHERE commission_pct IS NULL;
-------------------------------------------------------------------------
*****USING AND OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY FROM EMPLOYEES WHERE SALARY >=10000 AND JOB_ID LIKE '%MAN%' ;
-------------------------------------------------------------------------
*****USING OR OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY FROM employees WHERE SALARY >=10000 OR JOB_ID LIKE '%MAN%';
-------------------------------------------------------------------------
*****USING NOT OPERATOR*****
SELECT LAST_NAME,JOB_ID FROM employees WHERE JOB_ID NOT IN ('IT_PROG','ST_CLERK','SA_REP');
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE JOB_ID NOT IN ('AC_ACCOUNT','AD_VP');
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE SALARY NOT BETWEEN 10000 AND 15000;
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE last_name NOT LIKE '%A%';
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE commission_pct IS NOT NULL;
-------------------------------------------------------------------------
*****RULES OF PRECEDENCE*****
SELECT LAST_NAME,JOB_ID,SALARY FROM employees WHERE JOB_ID = 'SA_REP' OR job_id= 'AD_PRES' AND SALARY >15000;
SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;
-------------------------------------------------------------------------
*****USING ORDER BY CLAUSE*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM EMPLOYEES ORDER BY hire_date ;
-------------------------------------------------------------------------
*****USING SORTING*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM EMPLOYEES ORDER BY hire_date DESC;
SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12 AS ANNSAL FROM employees ORDER BY ANNSAL;
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM employees ORDER BY 4;
SELECT LAST_NAME,DEPARTMENT_ID,SALARY FROM employees ORDER BY DEPARTMENT_ID, salary DESC;
-------------------------------------------------------------------------
*****Using the Single-Ampersand Substitution Variable*****
SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;
SELECT EMPLOYEE_ID FROM employees;
-------------------------------------------------------------------------
*****Character and Date Values with Substitution Variables*****
SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title' ;
SELECT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT * FROM EMPLOYEES;
-------------------------------------------------------------------------
*****Specifying Column Names, Expressions, and Text*****
SELECT employee_id, last_name, job_id,&column_name FROM employees WHERE &condition ORDER BY &order_column ;
-------------------------------------------------------------------------
*****Using the Double-Ampersand Substitution Variable*****
SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name ;
-------------------------------------------------------------------------
*****Using the DEFINE Command*****
DEFINE employee_num = 101 SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;
-------------------------------------------------------------------------
SYNTAX: SELECT *|{[DISTINCT] column [alias],...} FROM table;
In the syntax:
SELECT Is a list of one or more columns
* Selects all columns
DISTINCT Suppresses duplicates
column|expression Selects the named column or the expression
alias Gives different headings to the selected columns
FROM table Specifies the table containing the columns
-------------------------------------------------------------------------
*****SELECTING ALL COLUMNS*****
SELECT *FROM departments;
*****SELECTING SPECIFIC COLUMNS*****
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM departments;
SELECT DEPARTMENT_ID,LOCATION_ID FROM departments;
-------------------------------------------------------------------------
*****COLUMN HEADING DEFAULTS*****
SELECT LAST_NAME,HIRE_DATE,SALARY FROM employees;
-------------------------------------------------------------------------
*****USING AIRTHEMATIC OPERATIONS*****
SELECT LAST_NAME,SALARY,SALARY+300 FROM employees;
SELECT LAST_NAME,SALARY,SALARY+300, salary-100 FROM employees;
Retrieving Data Using the SQL SELECT Statement
-------------------------------------------------------------------------
*****OPERATOR PRECEDENCE*****
SELECT LAST_NAME,SALARY,12*SALARY+100 FROM employees;
SELECT LAST_NAME,SALARY,SALARY+300-100 FROM employees;
-------------------------------------------------------------------------
*****DEFINING A NULL VALUE*****
SELECT LAST_NAME,JOB_ID,SALARY,COMMISSION_PCT FROM employees;
-------------------------------------------------------------------------
*****NULL VALUES IN ARITHEMATIC EXPRESSIONS*****
SELECT LAST_NAME,JOB_ID,12*SALARY*COMMISSION_PCT FROM employees;
-------------------------------------------------------------------------
*****USING COLUMN ALIASES*****
SELECT LAST_NAME AS NAME,SALARY AS SAL FROM employees;
-------------------------------------------------------------------------
*****USING CONCATENATION OPERATOR*****
SELECT LAST_NAME||JOB_ID AS "EMPLOYEES" FROM employees;
-------------------------------------------------------------------------
*****USING LITERAL CHARACTER STRING*****
SELECT LAST_NAME || ' is a ' ||JOB_ID AS "EMPLOYEE DETAILS" FROM employees;
SELECT LAST_NAME ||' : 1MONTH SALARY = '||SALARY MONTHLY FROM EMPLOYEES;
-------------------------------------------------------------------------
*****ALTERNATIVE QUOTE (q) OPERATOR*****
SELECT DEPARTMENT_NAME || ' DEPARTMENTS MANAGER ID: '|| MANAGER_ID AS "DEPARTMENT AND MANAGER" FROM DEPARTMENTS;
-------------------------------------------------------------------------
*****DUPLICATE ROWS*****
SELECT DEPARTMENT_ID FROM EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT first_name, last_name, job_id, salary*12 AS "Yearly Sal" FROM employees;
-------------------------------------------------------------------------
*******-----Restricting and Sorting Data-----*****
Use the WHERE clause to restrict rows of output:
– Use the comparison conditions
– Use the BETWEEN, IN, LIKE, and NULL operators
– Apply the logical AND, OR, and NOT operators
• Use the ORDER BY clause to sort rows of output:
• Use ampersand substitution to restrict and sort output at
run time
SYNTAX:
SELECT {*|[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]] ;
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE logical expression(s)];
Limiting the Rows That Are Selected
• Restrict the rows that are returned by using the WHERE
clause:
• The WHERE clause follows the FROM clause.
-------------------------------------------------------------------------
*****USING THE WHERE CLAUSE*****
SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE department_id=10;
-------------------------------------------------------------------------
*****CHARACTER STRINGS AND DATES*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'WHALEN';
SELECT LAST_NAME FROM EMPLOYEES WHERE HIRE_DATE = '17-OCT-03';
-------------------------------------------------------------------------
*****USING COMPARISION OPERATORS*****
SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE salary<=3000;
SELECT LAST_NAME,SALARY FROM EMPLOYEES WHERE salary>=3000;
-------------------------------------------------------------------------
*****USING BETWEEN OPERATORS*****
SELECT LAST_NAME,SALARY FROM employees WHERE SALARY BETWEEN 2500 AND 3500;
SELECT LAST_NAME FROM EMPLOYEES WHERE last_name BETWEEN 'KING' AND 'SMITH';
-------------------------------------------------------------------------
*****USING IN OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IN (100,101,200);
SELECT EMPLOYEE_ID,MANAGER_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME IN ('KOCHHAR','VARGAS');
SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ('Hartstein', 'Vargas');
-------------------------------------------------------------------------
*****USING LIKE OPERATOR*****
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%';
SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '%05';
-------------------------------------------------------------------------
*****USING WILDCARD CHARACTERS*****
SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;
SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
-------------------------------------------------------------------------
*****USINIG NULL CONDITION*****
SELECT LAST_NAME,MANAGER_ID FROM employees WHERE MANAGER_ID IS NULL;
SELECT LAST_NAME,JOB_ID,COMMISSION_PCT FROM employees WHERE commission_pct IS NULL;
-------------------------------------------------------------------------
*****USING AND OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY FROM EMPLOYEES WHERE SALARY >=10000 AND JOB_ID LIKE '%MAN%' ;
-------------------------------------------------------------------------
*****USING OR OPERATOR*****
SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY FROM employees WHERE SALARY >=10000 OR JOB_ID LIKE '%MAN%';
-------------------------------------------------------------------------
*****USING NOT OPERATOR*****
SELECT LAST_NAME,JOB_ID FROM employees WHERE JOB_ID NOT IN ('IT_PROG','ST_CLERK','SA_REP');
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE JOB_ID NOT IN ('AC_ACCOUNT','AD_VP');
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE SALARY NOT BETWEEN 10000 AND 15000;
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE last_name NOT LIKE '%A%';
SELECT LAST_NAME,JOB_ID FROM EMPLOYEES WHERE commission_pct IS NOT NULL;
-------------------------------------------------------------------------
*****RULES OF PRECEDENCE*****
SELECT LAST_NAME,JOB_ID,SALARY FROM employees WHERE JOB_ID = 'SA_REP' OR job_id= 'AD_PRES' AND SALARY >15000;
SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;
-------------------------------------------------------------------------
*****USING ORDER BY CLAUSE*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM EMPLOYEES ORDER BY hire_date ;
-------------------------------------------------------------------------
*****USING SORTING*****
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM EMPLOYEES ORDER BY hire_date DESC;
SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12 AS ANNSAL FROM employees ORDER BY ANNSAL;
SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,HIRE_DATE FROM employees ORDER BY 4;
SELECT LAST_NAME,DEPARTMENT_ID,SALARY FROM employees ORDER BY DEPARTMENT_ID, salary DESC;
-------------------------------------------------------------------------
*****Using the Single-Ampersand Substitution Variable*****
SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;
SELECT EMPLOYEE_ID FROM employees;
-------------------------------------------------------------------------
*****Character and Date Values with Substitution Variables*****
SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title' ;
SELECT DEPARTMENT_ID,JOB_ID FROM EMPLOYEES;
SELECT * FROM EMPLOYEES;
-------------------------------------------------------------------------
*****Specifying Column Names, Expressions, and Text*****
SELECT employee_id, last_name, job_id,&column_name FROM employees WHERE &condition ORDER BY &order_column ;
-------------------------------------------------------------------------
*****Using the Double-Ampersand Substitution Variable*****
SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name ;
-------------------------------------------------------------------------
*****Using the DEFINE Command*****
DEFINE employee_num = 101 SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;
-------------------------------------------------------------------------
No comments:
Post a Comment