go.unc.edu/lbjones | Sample Queries: 01 | 03 | 04 | 06 | 07 | 09 | 11

INLS 161 SQL Data

Practice Live SQL (Structured Query Language)

This app is connected to a MySQL database at pearl.ils.unc.edu. The data is read-only, so it is not possible to change or enter data. However, you are free to run queries on the data on the SQL entry page.

Book Database Tables and Fields Reference

tblAuthor tblBook tblPublisher tblConnection tblOrder
SELECT
field(s)
tblAuthor_ID tblAuthor_Name TblBook_ID tblBook_Title
tblBook_Date
tblBook_ShelfLocation
tblBook_Language
tblBook_Topic
tblBook_RetailPrice
tblBook_Copies
tblBook_Publisher_ID
tblPublisher_ID
tblPublisher_Name
tblPublisher_City
tblPublisher_State
tblPublisher_Country
tblPublisher_Phone (all null)
tblPublisher_ZipCode (all null)
tblConnection_ID tblConnection_tblAuthor_ID
tblConnection_tblBook_ID
tblOrder_ID
tblOrder_tblBook_ID
tblOrder_NumberOfBooksOrdered
tblOrder_DateOfOrder
tblOrder_DateOfOrderReceived
tblOrder_DiscountPrice
FROM
table
tblAuthor tblBook tblPublisher tblConnection tblOrder
WHERE
you reference the field
followed by critera
Reference your field then use an operator =, <, >, IN, BETWEEN, LIKE, AND, OR, % (wildcard)
ORDER BY
the field
ascending or descending
ASC, DESC
Example you can copy and paste to get started:

SELECT tblBook_Title, tblBook_Topic
FROM tblBook
WHERE tblBook_Title LIKE '%freedom%'
ORDER BY tblBook_Title

Learn SQL

Link to all the SQL lessons at w3Schools: Learn SQL at W3schools

Links to the w3Schools lessons that will get you started:

Reminder: Any lesson that deals with update, delete, or insert will not work with this READ ONLY data.

Even More Examples

EXACT                                                      DON'T Copy and Paste --Comments
---------------------------------------------------------------------------------
SELECT tblBook_Title FROM tblBook WHERE tblBook_Title  =     'Simple Heraldry'  --only one exactly
SELECT *             FROM tblBook WHERE tblBook_Title  LIKE  'Simple Heraldry'  --only one exactly
SELECT tblBook_Title FROM tblBook WHERE tblBook_Title  LIKE   'asia'            --why none?

WILDCARDS
---------------------------------------------------------------------------------
SELECT tblBook_Title FROM tblBook WHERE tblBook_Title  LIKE  '%'                --gets ALL records
SELECT tblBook_Title FROM tblBook WHERE tblBook_Title  LIKE  'asia%'            --begins with asia
SELECT tblBook_Title FROM tblBook WHERE tblBook_Title  LIKE  '%asia'            --ends with asia
SELECT tblBook_Title FROM tblBook WHERE tblBook_Title  LIKE  '%asia%'           --contains asia

MATH SYMBOLS
---------------------------------------------------------------------------------
SELECT tblBook_Title, tblBook_Copies FROM tlbBook WHERE tblBook_Copies >  6
SELECT tblBook_Title, tblBook_Copies FROM tlbBook WHERE tblBook_Copies =  3
SELECT tblBook_Title, tblBook_Copies FROM tlbBook WHERE tblBook_Copies <  1

TWO RECORDS SO YOU CAN SEE THE CONTEXT
---------------------------------------------------------------------------------
SELECT tblBook_Title, tblBook_Topic  FROM tblBook WHERE topic  LIKE literature

BOOLEAN "AND" and "OR"
---------------------------------------------------------------------------------
SELECT tblBook_Title                 FROM tblBook WHERE tblBook_Title  LIKE  '%war%' AND LIKE  '%pacific%'
SELECT tblBook_Title                 FROM tblBook WHERE tblBook_Title  LIKE  '%war%' OR  LIKE  '%pacific%'
SELECT tblBook_Title, tblBook_Topic  FROM tblBook WHERE tblBook_Topic  LIKE  'ethics' OR LIKE 'physics'