1 / 16

An introduction to SQL

An introduction to SQL. 1/21/2014 – See chapter 2.3 and 6.1 PostgreSQL - http://www.postgresql.org/docs/9.3/static/index.html Acknowledgement – Thanks to Dr. Mayfield for the lab activity. SQL Query language. Divided into “ clauses ” each of which does a particular piece of work.

elia
Download Presentation

An introduction to SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. An introduction to SQL 1/21/2014 – See chapter 2.3 and 6.1 PostgreSQL - http://www.postgresql.org/docs/9.3/static/index.html Acknowledgement – Thanks to Dr. Mayfield for the lab activity.

  2. SQL Query language • Divided into “clauses” each of which does a particular piece of work. • Two kinds of languages • DDL – Data Definition Language – Manipulates the database schema • DML – Data Manipulation Language – Manipulates the database data

  3. DDL (2.3) • CREATE – Makes new database objects • CREATE TABLE … • CREATE USER … • CREATE DATABASE … • ALTER – Changes database objects • ALTER TABLE …

  4. DML – 6.1 • SELECT – Read only query to examine data from the database. May be used in conjunction with update statements. • UPDATE – Update existing data • INSERT – Insert new rows • DELETE – Delete existing rows

  5. Important clauses • SELECT – Chooses the individual fields (columns) from the database (not necessarily a particular table) • FROM – Chooses which table(s) we are working with • WHERE – Provides criteria for the selection

  6. PostgreSQL • Relational DBMS use SQL but each has their own dialects and supported features. • PostgreSQL is: • “PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.” (www.postgresql.org/about)

  7. Our server • db.cs.jmu.edu • Accessible from JMU networks only • Hardware • 2*12 Core Opteron 2.2 GHz • 48 GB RAM (plus 48 GB swap) • 6*275 GB Disks (1.5 TB free) • Software • Linux 3.8 (Ubuntu 12.04.4) • PostgreSQL 9.1

  8. Additional server • For those interested in exploring MySQL it is available on: • daisy.cs.jmu.edu • See me if you want an account.

  9. Logging in • From the command line: • psql -h db.cs.jmu.edu-U username • To change your password: • \ password • Your db account is separate from your JMU account! • Do it now

  10. Create a table using DDL CREATE TABLE movie ( id integer, title text, year integer, genres text, mpaa text, budget text ); White space does not matter but style dictates indentation for readability. Case does not matter for the keywords like CREATE and TABLE but convention has us capitalize them. Do it now!

  11. Loading the db • Choices – You can hand enter the data …. • OR – You can load it from a file. • Today we will load from a file.

  12. Load the database • Download • http://w3.cs.jmu.edu/mayfiecs/cs474/movies.csv.gz • About 1.4 MB • Uncompress • gunzip movies.csv.gz • Now 3.8 MB • With psql • \copy movie FROM movies.csv WITH CSV HEADER; • ANALYZE VERBOSE movie; • 63,508 rows • NOTE: \ is an escape which says to do this outside of psql

  13. Basic psql commands General • \?help on psqlcommands • \h: help on SQL syntax • \iexecute commands from file • \q: quit psql (or Ctrl-D) Browsing • \d:list tables, views, etc • \d NAME: describe table, NAME,/etc Important • Ctrl-C cancels the current query • Tab completion is your friend!

  14. Your first DML query SELECT title -- what you want from the db FROM movie -- the table to operate on WHERE year = 2012; -- the criterion Use up/down arrows and page up/down g or G: go to first / last line / or ?: search forward / backward Press ‘h’ for help Press ‘q’ to quit

  15. Explore • Write another query that includes more than one field in the select and a different criterion. • You can use AND and OR to combine criteria. • Multiple fields are separated by commas in the SELECT statement.

  16. Homework • Download any data set from: • http://archive.ics.uci.edu/ml/ • Create the table • Import the data into your database • Write several queries exploring the data set. • See HW03 in Canvas

More Related