|
Size: 1480
Comment:
|
Size: 1766
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 1: | Line 1: |
| The Basics | SQL and phpPgAdmin for BaBase |
| Line 3: | Line 3: |
| SQL (Structured Query Language) is the standard programming language for creating and retrieving information that is stored in a relational database. The user interface or front-end (what you see online) is called phpPgAdmin. The following pages are meant as a basic introduction to SQL, phpPgAdmin, and query writing. Please see BaBase documentation for specifics about the database. There are many resources online for more detailed information about SQL. There are links to SQL tutorials and documentation through the Babase Wiki Case sensitivity |
The Basics 2 |
| Line 6: | Line 5: |
| SQL language is case insensitive but user-defined identifiers (table names, column names, and table entries) are case sensitive. For example, the SQL SELECT statement could be written and SELECT, SeLect, or select but the table biograph is case sensitive and must be written as is. Conventions in SQL syntax |
|
| Line 9: | Line 6: |
| In this document I have followed some SQL syntax conventions when writing query statements. | Case sensitivity 2 |
| Line 11: | Line 8: |
| UPPERCASE Uppercase indicates SQL language or commands | |
| Line 13: | Line 9: |
| lowercase Lowercase indicates user defined items such as tables and columns specific to BaBase. | Conventions in SQL syntax 2 |
| Line 15: | Line 11: |
| Quotation marks | |
| Line 17: | Line 12: |
| All quotation marks in SQL are straight quotes (such as ' and "), not curly quotes (such as ‘ and “). Curly quotes will not work in the SQL window. Be careful when cutting and pasting from text editors. Sometimes the default is curly quotes and you will receive an error message when you run the query. You can delete the | Quotation marks 2 Data Types 3 Character 3 Number 3 Date 3 Time 3 Retrieving Data from a Table 3 Retrieving columns with SELECT and FROM 4 Eliminating duplicate rows with DISTINCT 4 Filtering rows with WHERE 4 Comparisons 5 Combining and negating conditions with AND, OR, and NOT 6 Matching patterns with LIKE 6 Range filtering with BETWEEN 7 List filtering with IN 7 Testing for blanks or nulls with NULL 7 Sorting rows with ORDER BY 8 Creating column aliases with AS 8 Operators and Functions 9 Performing Arithmetic Operations 9 Summarizing and Grouping Data 9 Aggregate Functions 9 COUNT 9 MIN and MAX 10 AVG 10 SUM 10 FLOOR and CEILING 10 Grouping rows with GROUP BY 11 Filtering groups with HAVING 11 Retrieving Data from Multiple Tables 12 Qualifying column names 12 Creating joins with JOIN or WHERE 12 Selecting Columns from two or more tables with WHERE 12 Join two or more tables using JOIN 13 Schemas 15 Saving your query result INTO a new table 16 Queries with multiple SELECT statements 16 Temporary tables 16 Deleting tables 16 Importing Tables into your schema 16 Exporting Tables 17 Sandbox 17 Permissions 17 Permissions 18 Views 18 Saving your queries 18 A Couple Useful Queries 19 Calculate Age 19 |
SQL and phpPgAdmin for BaBase
The Basics 2
Case sensitivity 2
Conventions in SQL syntax 2
Quotation marks 2
- Data Types 3
Character 3
Number 3
Date 3
Time 3
Retrieving Data from a Table 3
Retrieving columns with SELECT and FROM 4
Eliminating duplicate rows with DISTINCT 4
Filtering rows with WHERE 4
Comparisons 5
Combining and negating conditions with AND, OR, and NOT 6
Matching patterns with LIKE 6
Range filtering with BETWEEN 7
List filtering with IN 7
Testing for blanks or nulls with NULL 7
Sorting rows with ORDER BY 8
Creating column aliases with AS 8
Operators and Functions 9
Performing Arithmetic Operations 9
Summarizing and Grouping Data 9
Aggregate Functions 9
COUNT 9
MIN and MAX 10
- AVG 10
SUM 10
FLOOR and CEILING 10
Grouping rows with GROUP BY 11
Filtering groups with HAVING 11
- Retrieving Data from Multiple Tables 12
Qualifying column names 12
- Creating joins with JOIN or WHERE 12
Selecting Columns from two or more tables with WHERE 12
Join two or more tables using JOIN 13
Schemas 15
Saving your query result INTO a new table 16
Queries with multiple SELECT statements 16
Temporary tables 16
Deleting tables 16
Importing Tables into your schema 16
Exporting Tables 17
Sandbox 17
Permissions 17
Permissions 18
Views 18
Saving your queries 18
A Couple Useful Queries 19
Calculate Age 19