An hour with Database and SQL

An hour with Database and SQL

• 1.
  AN HOUR WITH DATABASEAND SQL MARIHACKS LEARNATHON IRAJ HEDAYATI
• 2.
  RELATIONAL DATABASE • Entitiesand their relations • One-to-one relation • Many-to-Many • Many-to-one relation Mathematics Emma Emma Caleb Caleb fills fills Seat 1 Seat 2 History
• 3.
  TABLE • Table orsometimes called Relation • Each entity is a table • Sometimes a relation is a table (e.g. many-to-one) • Each table is a set of Rows (also called Record or Tuple • Each Row consists a set of Columns (also called Attributes) • Key: there are different key definition but we focus only on Primary Key and Foreign Key • Schema is structure and design of database (tables and relations) ID Name Telephone Age 123456 Caleb 514222345 6 17 456789 Emma 514111987 6 16 Column Row Primary Key Unique for each row
• 4.
  DESIGN SCHEMA • Foreach entity add a table that reflects attributes of that entity • For each entity select (or add) a unique column as primary key • Relations: • One-to-One and Many-to-One and One-to-Many: in the source table (e.g. student) add a column that holds primary key of destination table (e.g. seat). This is called Foreign Key) • Many-to-Many: add a table for this relation. It has Foreign Keys to each entity involved in this relation (e.g. Student ID and Course ID). It is like breaking it down to two One- to-Many and Many-to-One relations.
• 5.
  PRACTICE
• 6.
  SYSTEM REQUIREMENTS • Anin-memory database: H2 • Download: http://www.h2database.com/html/download.html • H2 is JVM based, so you need install Oracle Java 7 or later • Start using H2: • http://www.h2database.com/html/tutorial.html#tutorial_starting_h2_console • Prepare schema: • https://s3-us-west-2.amazonaws.com/prod-c2g/db/Winter2013/files/social.sql
• 7.
  SQL A language toquery a database
• 8.
  INTRO • Data DefinitionLanguage (DDL): define schema • Data Manipulation Language (DML): • INSERT • UPDATE • SELECT
• 9.
  INSERT INSERT INTO table_name(column1, column2, column3, ...) VALUES (value1, value2, value3, ...); INSERT INTO highschooler (id, name, grade) VALUES (1510, 'Jordan', 9); INSERT INTO highschooler VALUES (1510, 'Jordan', 9); Both are the same. Because we provided values for each column and they are in the same order.
• 10.
  UPDATE UPDATE table_name SET column1= value1, column2 = value2, ... WHERE condition; UPDATE highschooler SET name='Caleb' WHERE id=1468
• 11.
  SELECT SELECT column1, column2,column3, … FROM table1 WHERE c1 AND c2 OR c3 • FROM: which table? • WHERE: filter rows • SELECT: what to return? • Column names • A function • STAR
• 12.
  SELECT • SELECT *FROM highschooler; • SELECT name FROM highschooler WHERE grade=9; • SELECT COUNT(*) FROM highschooler WHERE grade=10; • SELECT * FROM highschooler WHERE grade>=10 AND (name LIKE 'A%' OR id>1700) • SELECT * FROM highschooler ORDER BY name; • SELECT grade, COUNT(*) FROM highschooler GROUP BY grade;
• 13.
  SELECT (JOIN) • Joinoperation is a cartesian product (also called FULL OUTER JOIN) • INNER JOIN only return those that match SELECT * FROM highschooler AS t1 JOIN likes AS t2 SELECT * FROM highschooler AS t1 INNER JOIN likes AS t2 ON t1.ID=t2.ID1
• 14.
  JOIN PRACTICE • Wholike who? SELECT * FROM ( SELECT * FROM highschooler AS t1 INNER JOIN likes AS t2 ON t1.ID=t2.ID1 ) AS t3 INNER JOIN highschooler AS t4 ON t4.ID=t3.ID2
• 16.
  PRACTICE • Find thenames of all students who are friends with someone named Gabriel • For every student who likes someone 2 or more grades younger than themselves, return that student's name and grade, and the name and grade of the student they like. • For every pair of students who both like each other, return the name and grade of both students. Include each pair only once, with the two names in alphabetical order. • Find all students who do not appear in the Likes table (as a student who likes or is liked) and return their names and grades. Sort by grade, then by name within each grade.
• 17.
  MORE RESOURCES • StanfordUniversity online learning SQL • https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/courseware/ch-sql/seq-vid- introduction_to_sql/ • H2 Tutorial: • http://www.h2database.com/html/tutorial.html • W3School SQL commands + practice: • https://www.w3schools.com/sql/ • Practice SQL • https://sqlzoo.net/