What are my tables?
We should expect to find a system table that includes a list of tables. We should expect this to contain a great deal of extra data that is hard to understand.
SELECT * FROM cat
What are the columns of the cia table?
SELECT * FROM all_tab_columns
WHERE table_name='CIA'
Get the first 10 rows of the gisq.cia table.
Specific to Oracle
SELECT * FROM cia WHERE rownum<=10
Get the 11th to the 20th rows of the cia table - by population.
SELECT name, population FROM (
SELECT name, population, rownum n FROM (
SELECT name,population FROM cia
ORDER BY population DESC)
)
WHERE n BETWEEN 11 AND 20
What version of the software am I using?
Many implementations of SQL are being developed continuously and new releases are common. Usually there is a version number.
select * from v$version
What is the syntax to view structure of table?
What is the syntax to view structure of table?
See also What are the columns of the ci table?
If you have access to the SQL code which created the table the primary key can be seen easily. The primary key may be specified in one of two ways:
SELECT a.owner, a.table_name, b.column_name
FROM all_constraints a, all_cons_columns b
WHERE a.constraint_type='P'
AND a.constraint_name=b.constraint_name
AND a.table_name = 'CASTING'
Return a sequential record count for all records returned
this should be simple - I would like to get a consecutive numbering count/id for each record returned from a query: eg: select * from tablex gives multiple rows like:
select rownum, field1, field2, field3
from table_name;
We should expect to find a system table that includes a list of tables. We should expect this to contain a great deal of extra data that is hard to understand.
| Specific to Oracle |
| See also user_tables and user_catalog |
What are the columns of the cia table?
| Specific to Oracle |
| When working with the sqlplus interface you can simply ask DESCRIBE cia; Notice that the table name is in upper case. See also USER_TAB_COLUMNS |
SELECT * FROM all_tab_columns
WHERE table_name='CIA'
Get the first 10 rows of the gisq.cia table.
Specific to Oracle
SELECT * FROM cia WHERE rownum<=10
Get the 11th to the 20th rows of the cia table - by population.
| Specific to Oracle |
| This seems very complicated, seemingly the rownum is calculated after the WHERE clause runs so WHERE rownum>1 always gives zero rows. |
SELECT name, population, rownum n FROM (
SELECT name,population FROM cia
ORDER BY population DESC)
)
WHERE n BETWEEN 11 AND 20
What version of the software am I using?
Many implementations of SQL are being developed continuously and new releases are common. Usually there is a version number.
| Specific to Oracle |
| Thanks to Mark Folley |
What is the syntax to view structure of table?
What is the syntax to view structure of table?
See also What are the columns of the ci table?
| Specific to Oracle |
| From within sqlplus we can use the DESCRIBE command. Outside sqlplus we can access the view all_tab_columns SELECT * FROM all_tab_columns WHERE table_name='CIA' How can you determine the primary key using SQL? |
CREATE TABLE cia (name PRIMARY KEY, population INTEGER)or, where the primary key is composite:
CREATE TABLE casting(movieid INTEGER,
actorid INTEGER,
PRIMARY KEY (movieid, actorid)
)
If this is not possible then implementation specific commands may work.| Specific to Oracle |
| We can use the system tables: all_constraints and all_cons_columns. For a primary key the constraint_type is 'P'. We also need the all_cons_columns view to find the actual columns. |
FROM all_constraints a, all_cons_columns b
WHERE a.constraint_type='P'
AND a.constraint_name=b.constraint_name
AND a.table_name = 'CASTING'
Return a sequential record count for all records returned
this should be simple - I would like to get a consecutive numbering count/id for each record returned from a query: eg: select * from tablex gives multiple rows like:
field1, field2, field3, ... field1, field2, field3, ... field1, field2, field3, ...What I want is:
1, field1, field2, field3, ... 2, field1, field2, field3, ... 3, field1, field2, field3, ... 4, field1, field2, field3, ...
| Specific to Oracle |
| Use the psuedo-column ROWNUM. Note that ROWNUM is evaluation prior to the ORDER BY clause. To apply ROWNUM to an ordered result, use an inline view. |
select rownum, field1, field2, field3
from table_name;
select rownum, field1, field2, field3
from (select field1, field2, field3
from table_name
order by field1, field2, field3);