| Consulta com SELECT | |
O comando SELECT extrai dados da base de dados, implementando os operadores da álgebra relacional.
Para ver todas as tabelas que o utilizador tem disponíveis podemos consultar DICTIONARY:
select * from dictionary; |
TABLE_NAME COMMENTS
------------------------------ ----------------------------------------------------------------------------
USER_RESOURCE_LIMITS Display resource limit of the user
USER_PASSWORD_LIMITS Display password limits of the user
USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
USER_CLUSTERS Descriptions of user's own clusters
ALL_CLUSTERS Description of clusters accessible to the user
USER_CLU_COLUMNS Mapping of table columns to cluster columns
USER_COL_COMMENTS Comments on columns of user's tables and views
ALL_COL_COMMENTS Comments on columns of accessible tables and views
USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee
ALL_COL_PRIVS Grants on columns for which the user is the grantor, grantee, owner,
or an enabled role or PUBLIC is the grantee
USER_COL_PRIVS_MADE All grants on columns of objects owned by the user
ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor
USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee
ALL_COL_PRIVS_RECD Grants on columns for which the user, PUBLIC or enabled role is the grantee
ALL_ENCRYPTED_COLUMNS Encryption information on all accessible columns
USER_ENCRYPTED_COLUMNS Encryption information on columns of tables owned by the user
USER_DB_LINKS Database links owned by the user
ALL_DB_LINKS Database links accessible to the user
USER_INDEXES Description of the user's own indexes
ALL_INDEXES Descriptions of indexes on tables accessible to the user
USER_IND_COLUMNS COLUMNs comprising user's INDEXes and INDEXes on user's TABLES
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
USER_IND_EXPRESSIONS Functional index expressions in user's indexes and indexes on user's tables
ALL_IND_EXPRESSIONS FUNCTIONAL INDEX EXPRESSIONs on accessible TABLES
USER_JOIN_IND_COLUMNS Join Index columns comprising the join conditions
ALL_JOIN_IND_COLUMNS Join Index columns comprising the join conditions
USER_OBJECTS Objects owned by the user
ALL_OBJECTS Objects accessible to the user
USER_PROCEDURES Description of the users own procedures
ALL_PROCEDURES Description of all procedures available to the user
ALL_STORED_SETTINGS Parameter settings for objects accessible to the user
USER_STORED_SETTINGS Parameter settings for objects owned by the user
USER_PLSQL_OBJECT_SETTINGS Compiler settings of stored objects owned by the user
ALL_PLSQL_OBJECT_SETTINGS Compiler settings of stored objects accessible to the user
ALL_ARGUMENTS Arguments in object accessible to the user
USER_ARGUMENTS Arguments in object accessible to the user
USER_RESUMABLE Resumable session information for current user
USER_ROLE_PRIVS Roles granted to current user
USER_SYS_PRIVS System privileges granted to current user
USER_SEQUENCES Description of the user's own SEQUENCEs
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
USER_SYNONYMS The user's private synonyms
ALL_SYNONYMS All synonyms for base objects accessible to the user and session
USER_TABLES Description of the user's own relational tables
USER_OBJECT_TABLES Description of the user's own object tables
USER_ALL_TABLES Description of all object and relational tables owned by the user's
ALL_TABLES Description of relational tables accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_ALL_TABLES Description of all object and relational tables accessible to the user
USER_TAB_COLS Columns of user's tables, views and clusters
ALL_TAB_COLS Columns of user's tables, views and clusters
USER_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
USER_NESTED_TABLE_COLS Columns of nested tables
ALL_NESTED_TABLE_COLS Columns of nested tables
USER_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
USER_TAB_HISTOGRAMS Histograms on columns of user's tables
ALL_TAB_HISTOGRAMS Histograms on columns of all tables visible to user
USER_TAB_COMMENTS Comments on the tables and views owned by the user
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
USER_TAB_PRIVS Grants on objects for which the user is the owner, grantor or grantee
ALL_TAB_PRIVS Grants on objects for which the user is the grantor, grantee, owner,
or an enabled role or PUBLIC is the grantee
...
|
Para vermos quais as tabelas do utilizador consultamos a vista USER_TABLES. Para vermos todos os objectos do utilizador consultamos a vista USER_OBJECTS:
select object_name,object_type,created,last_ddl_time,status from user_objects; |
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_ STATUS ----------------- ------------------ --------- --------- ------- BONUS TABLE 12-OCT-02 12-OCT-02 VALID DEPT TABLE 12-OCT-02 12-OCT-02 VALID DUMMY TABLE 12-OCT-02 12-OCT-02 VALID EMP TABLE 12-OCT-02 12-OCT-02 VALID SALGRADE TABLE 12-OCT-02 12-OCT-02 VALID |
Para listar todos os números de departamento, nomes de empregado e número do chefe da tabela de Empregados (EMP):
select deptno,ename,mgr from emp; |
DEPTNO ENAME MGR
---------- ---------- ----------
20 SMITH 7902
30 ALLEN 7698
30 WARD 7698
20 JONES 7839
30 MARTIN 7698
30 BLAKE 7839
10 CLARK 7839
20 SCOTT 7566
10 KING
30 TURNER 7698
20 ADAMS 7788
30 JAMES 7698
20 FORD 7566
10 MILLER 7782
14 rows selected.
|
Para listar todas as colunas da tabela emp:
SELECT * FROM emp; |
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. |
Realizado por Turtle Learning ®. Última alteração em 2011-02-26
Topo