SIMPLE SINGLE TABLE RETRIEVAL

Example 1

Retrieve all information about students (‘*’ means all attributes)
SELECT * FROM STUDENT;

STUID LNAME FNAME MAJOR CREDITS
S1001 Smith Tom History 90
S1010 Burns Edward Art 63
S1015 Jones Mary Math 42
S1002 Chin Ann Math 36
S1020 Rivera Jane CIS 15
S1013 McCarchy Owen Math 9

Example 2

Find the last name, ID, and credits of all students
SELECT LNAME, STUID, CREDITS FROM STUDENT;

LNAME STUID CREDITS
Smith S1001 90
Burns S1010 63
Jones S1015 42
Chin S1002 36
Rivera S1020 15
McCarthy S1013 9

Example 3

Find all information about students who are math majors
SELECT * FROM STUDENT WHERE MAJOR = ‘Math’;

STUID LNAME FNAME MAJOR CREDITS
S1015 Jones Mary Math 42
S1002 Chin Ann Math 36
S1013 McCarthy Owen Math 9

Example 4

Find the student ID of all History
SELECT STUID FROM STUDENT WHERE MAJOR = ‘History’;

STUID

S1001

Example 5

Retrieve a list of all majors that currently have students
SELECT DISTINCT MAJOR FROM STUDENT;

MAJOR

Art

CIS

History

Math

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>