SQL default output is a bag (allows dupicates)

  • SQL outputs by default duplicate values

    Example: 

       SELECT name   
   FROM   dependent                                


    
   Output:

         NAME
         ----------
	 Alice
	 Abner
	 Alice
	 Elizabeth
	 Joy
	 Micheal
	 Theodore

  • Reason:

    • removing duplicates will require additional processing

Removing duplicates from SQL output: DISTINCT

  • To remove duplicate values, use the DISTINCT keyword in the SELECT clause

    Example: 

       SELECT DISTINCT name                      
   FROM   dependent 


    
   Output:

      NAME
      ----------
      Alice
      Theodore
      Joy
      Abner
      Micheal
      Elizabeth

  SQL's "all attributes" selector *  

  • The SELECT * clause will select all attributes in the relations in the FROM clause

    Example: 

        SELECT *
    FROM   department
    WHERE  dname = 'Research';


  Output:

      DNAME              DNUMBER MGRSSN    MGRSTARTDA
      --------------- ---------- --------- ----------      
      Research                 5 333445555 22-MAY-78

    All attributes in the relation department are selected for output