SELECT , Column , alias

SELECT Statement

================

Projection: columns that are returned by a query

Selection: rows that are returned by a query

Joining: bring data from multiple tables by specifying link between them

SELECT -> is useful to read the data from the database. it is also useful to

generate the report. it is useful to retrieve the data from the database.

SYNTAX:

SELECT *|[Unique/distinct/all] column/expression [AS] [alias],

column2/expression2 [AS] [alias],............

FROM table;

DISTINCT/Unique -> useful to display non-duplicate rows

ALL -> displays all values

alias -> gives selected columns different heading names

; -> all sql statement should be terminated with semicolon.

SELECT username FROM all_users;

SELECT TABLE_NAME FROM USER_TABLES;

DESC USER_TABLES;

DESCRIBE user_tables;

DESC user_tables;

SELECT table_name FROM tabs;

DESC EMP;

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL

FROM EMP;

SELECT * FROM EMP;

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP;

DESC DEPT;

SELECT * FROM DEPT;

SELECT DEPTNO, DNAME, LOC  FROM DEPT;

SELECT deptno, dname FROM dept;

DESC DEPT;

SELECT dname, loc, deptno FROM dept;

DESC emp;

SELECT empno, sal FROM emp;

SELECT deptno FROM emp;

SELECT  ALL deptno FROM emp;

SELECT DISTINCT deptno FROM emp;

SELECT deptno, job FROM emp;

SELECT DISTINCT DEPTNO, DISTINCT JOB FROM EMP; --INVALID

SELECT DISTINCT DEPTNO,JOB FROM EMP;

SELECT UNIQUE DEPTNO, JOB FROM EMP;

desc emp;

show user;

show all;

SELECT comm FROM emp;

SELECT DISTINCT COMM FROM EMP;

Column Alias

============

SELECT EMPNO, SAL, DEPTNO FROM EMP;

SELECT EMPNO, SAL salary, DEPTNO FROM EMP;

SELECT empno, sal AS salary, deptno FROM EMP;

Column Alias is the different column heading in the output.

Column headings are displayed in UPPERCASE by default and can be

overridden with an alias.

SELECT empno, sal AS salary, deptno FROM emp;

SELECT empno, sal AS "salary", deptno FROM emp;

Alias Rules:

Alias name should start with Alphabetical letters only(a-z or A-Z).

Alias name can contain alphatetic letters(a-z or A-Z), numbers(0-9), $, _, #.

Alias name should be between (1-30) characters

SELECT empno, sal AS $salary FROM emp; -- invalid

SELECT empno, sal AS "$salary" FROM emp;

SELECT empno, sal, deptno AS department id FROM emp;

SELECT empno, sal, deptno AS "department id" FROM emp;

SELECT empno AS employee_id, sal AS salary FROM emp;

SELECT empno employee_id, sal salary FROM emp;

SELECT empno e, sal s FROM emp;

SELECT empno, sal Monthly Salary FROM emp;    -- invalid

SELECT empno, sal "Monthly Salary" FROM emp;

SELECT empno "employee_id", sal salary FROM emp;

select ename, sal*12  from emp;

select ename, sal*12 annual_salary from emp;

SELECT ENAME, SAL FROM EMP;

SELECT ENAME SAL FROM EMP;

SELECT ename, sal salary$ FROM emp;