SQL Select

SQL Select Example One

Q: What films did director Stanley Kubrick direct?

   
 Select *
   From Director_Film_Table
  Where Director_Last_Name equals 'Kubrick';

   
 Stanley, Kubrick, A Clockwork Orange
 Stanley, Kubrick, Barry Lyndon
 Stanley, Kubrick, Dr. Strangelove
 Stanley, Kubrick, Eyes Wide Shut
 Stanley, Kubrick, Full Metal Jacket
 Stanley, Kubrick, Killer's Kiss
 Stanley, Kubrick, Killing, The
 Stanley, Kubrick, Lolita
 Stanley, Kubrick, Paths of Glory
 Stanley, Kubrick, Shining, The
 Stanley, Kubrick, Spartacus
 Stanley, Kubrick, 2001: A Space Odyssey

Note: Using the * in the select gets all columns in the table. We get Stanley Kubrick in each row returned, even though we don't really need it every time.

Also Note: This SQL brings back only Stanley Kubrick's movies because he is the only director with Kubrick as a last name. If more than one director had the surname of Kubrick we would need to specify "Where Director_Last_Name equals 'Kubrick' and Director_First_Name equals 'Stanley'" in the SQL.

SQL Select Example Two - Specifing Columns

Q: What films did director Stanley Kubrick make (and don't show Stanley, Kubrick in each row).

   
 Select Film_Title
   From Director_Film_Table
  Where Director_Last_Name equals 'Kubrick';

   
 A Clockwork Orange
 Barry Lyndon
 Dr. Strangelove
 Eyes Wide Shut
 Full Metal Jacket
 Killer's Kiss
 Killing, The
 Lolita
 Paths of Glory
 Shining, The
 Spartacus
 2001: A Space Odyssey

SQL Select Example Three - Using Two Tables

Q: What films did director Stanley Kubrick make and what year were they released?

   
 Select Director_Film_Table.Film_Title, Film_Year
   From Director_Film_Table,
        Film_Table
  Where Director_Last_Name equals 'Kubrick'
    and Director_Film_Table.Film_Title = Film_Table.Film_Title;

   
 A Clockwork Orange, 1971
 Barry Lyndon, 1975
 Dr. Strangelove, 1964
 Eyes Wide Shut, 1999
 Full Metal Jacket, 1987
 Killer's Kiss, 1955
 Killing, The, 1956
 Lolita, 1962
 Paths of Glory, 1957
 Shining, The, 1980
 Spartacus, 1960
 2001: A Space Odyssey, 1968

Note: We use the column Film_Title to link the tables together. This works because the values will be exactly the same in both tables for those fields.

Note for Microsoft Access Users: Access might not work with this SQL. You might need to use a subselect to make this query work. You can see the full syntax for subselect on our subselect page. Here is the same query with a subselect for MS Access users and subselect lovers:

 Select Film_Title, Film_Year
   From Film_Table
  Where Film_Title in
            (Select Film_Title
               From Director_Film_Table
              Where Director_Last_Name equals 'Kubrick');

Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.

Comment by Larry on 2008-09-04 Rate this Comment

Depending on what DBMS you are using you may need single or double quotes in your select statement. I'm showing these examples with a ' as that is how MySQL (which many of this site's readers are using) needs it, but if you are using a different DB you may need ".