SQL

1. SQL CMSC 461 Michael Wilson

2. Finally, some code • This is where the theory and practice actually come together • Basically taking the relational algebra and mapping it all to a query language • The concepts are very close to what we’ve already seen, but there are some nuances

3. One true SQL? • There are ISO/ANSI SQL standards • In my experience, different DBMSes have different/proprietary extensions that can make raw SQL queries pretty DBMS specific • The extensions aren’t exactly frequent • Invariably, it affects whatever thing you’re working on

4. DBMS to focus on? • I firmly believe that PostgreSQL is going to be a big deal • MySQL is getting mired in some drama relating to being owned by Oracle • Forked off into MariaDB • Therefore, I’m going to focus on Postgres • Also, AWS RDS supports Postgres now • You are welcome to use whatever you wish for projects • RDS supports MySQL and PostgreSQL, but you’re not limited to AWS

5. Before we get started • PostgreSQL’s SQL language documentation • http://www.postgresql.org/docs/9.3/static/sql.html • Use it! • PostgreSQL is case insensitive and insensitive to whitespace between commands • Can spread a command across multiple lines or type it all on one line

6. Getting started • Most DBMSes can have multiple databases • Each database can consist of many tables • If we configure PostgreSQL using AWS RDS, then we don’t have to create the database ourselves • This can be a little complex in Postgres • We’re going to assume the database is created

7. Connecting to your database using psql • psql –h <hostname> -d <database_name> -U <username> • Command line PostgreSQL client • On success, this will drop you to a prompt

8. Connecting to your database using psql

9. Table creation • Before you can do anything, you need to create a table • Basic syntax: • CREATE TABLE table_name (column_namedata_type,column_namedata_type, ….);

10. Table creation • This is effectively creating a new relation • We’ll be referencing this a lot • We’ll also be building on it, so expect to learn new features about table creation as we go • This syntax will create a basic table with columns

11. Data types • Some basic ones to know • Integer (signed 4 byte integer) • Numeric (numbers with high precision) • Kind of like a double, but has EXACT calculations • Boolean (true/false) • Varchar (character strings of variable lengths) • Text(unlimited length strings) • Not part of SQL standard

12. Data types • Databases support TONS of data types • Numbers, dates, booleans, money, geometric shapes, JSON, etc. • Some are part of the SQL standard, some aren’t • Best to refer to the PostgreSQL documentation for guidance on data types • Lots of nuances, recommendations, etc.

13. Table creation example

14. Table creation example • CREATE TABLE AddressBook (address varchar(256),daysSinceContact integer,contactPhoneNumbervarchar(64),numberTypevarchar(16),contactNamevarchar(256));

15. Table insertion • Figure it might be handy to actually add data to our table • This is like adding a tuple to the relation • Basic syntax: • INSERT INTO <table> (column_name1, column_name2, … column_name_n)values (value_1, value_2, …, value_n)

16. Table insertion • You can specify the columns in any order you want • As long as you specify the values in the corresponding order

17. Table insertion • A note • If you recall, tuples are explicitly unique in databases • This is not the case in tables • They can be made this way, but this is not the default behavior

18. Table insertion example • INSERT INTO AddressBook (address, daysSinceContact, contactPhoneNumber, numberType, contactName)VALUES (’21 Jump Street’, 40, ‘123 4567’, ‘Cell’, ‘Johnny’)

19. Table insertion example

20. Enough for today • See you next class!