INLS 161 SQL Data

Practice Live SQL (Structured Query Language)

This app is connected to a MariaDB 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:

• SQL home
• intro
• syntax
• select
• distinct
• where
• And, Or, Not
• Order By

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'