Short answer: Yes, it can be NULL or duplicate. I want to explain why a foreign key might need to be null or might need to be unique or not unique. First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That … Read more
Create a diagram for existing database schema or its subset as follows: Click File → Data Modeler → Import → Data Dictionary. Select a DB connection (add one if none). Click Next. Check one or more schema names. Click Next. Check one or more objects to import. Click Next. Click Finish. The ERD is displayed. … Read more
There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it. A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit. Basically, PostgreSQL and MySQL supports the non-standard: SELECT… LIMIT y OFFSET x Oracle, DB2 and MSSQL supports the … Read more
There is no such thing as “auto_increment” or “identity” columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger: Table definition: CREATE TABLE departments ( ID NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL); ALTER TABLE departments ADD ( CONSTRAINT dept_pk PRIMARY KEY (ID)); CREATE SEQUENCE dept_seq … Read more
I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS. SELECT t1.* FROM mytable t1 LEFT OUTER JOIN mytable t2 ON (t1.UserId = t2.UserId … Read more
SELECT owner, table_name FROM dba_tables This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE … Read more
You can use a subquery for this like select * from ( select * from emp order by sal desc ) where ROWNUM <= 5; Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information. Update: To limit the result with both lower and upper bounds things get … Read more