Database: SELECT Columns Intro

The SELECT command can be used with one table to select columns for the resulting table. This uses the Student table.

SELECT *

All of a table can be produced with

SELECT *
    FROM Student;

Result:

student_id	student_fname	student_lname	student_gpa
1	Mickey	Mouse	2.5
4	Minnie	Mouse	3.8
2	Donald	Duck	2.0
3	Peter	Rabbit	3.6

SELECT field,...

To see the names of all students

SELECT student_fname, student_lname
    FROM Student;

Result:

student_fname	student_lname
Mickey	Mouse
Minnie	Mouse
Donald	Duck
Peter	Rabbit

Arbitrary field order

The fields can be specified in any order.

SELECT student_lname, student_fname
    FROM Student;

Result:

student_lname	student_fname
Mouse	Mickey
Mouse	Minnie
Duck	Donald
Rabbit	Peter

Column aliases

If the internal field names are not suitable, create an alias with AS.

SELECT student_lname AS Last, student_fname AS First
    FROM Student;

Result:

Last	First
Mouse	Mickey
Mouse	Minnie
Duck	Donald
Rabbit	Peter

Inserting constant columns

SELECT can be used to create new columns with constants in them. The column title varies with the DBMS used (eg, Access uses EXPR1001, MySQL uses the constant). You can choose a column name using AS, described above. Note: The ANSI standard says use single quotes around string constants and double quotes around aliases, but quotes may be treated differently in some DBMSes.

SELECT student_fname, ' has last name ', student_lname
    FROM Student;

Result:

student_fname	has last name	student_lname
Mickey	has last name	Mouse
Minnie	has last name	Mouse
Donald	has last name	Duck
Peter	has last name	Rabbit