sql.md

Week 2 - SQL

Concepts

• RDBMS
• Sublanguages: DDL, DML, DQL
• Set Operators: Union, Intersect, Except
• Subqueries
• Normalization: 1st vs 2nd vs 3rd Normal Forms
• Multiplicity: Foreign keys
• Joins: Inner, Outer, Cross
• Functions: Aggregate vs Scalar

Syntax

• DDL:
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
• DML:
  • INSERT
  • UPDATE
  • DELETE
• DQL:
  • SELECT
  • Combining Queries: UNION/INTERSECT/EXCEPT
  • Scalar Subqueries
  • Subquery Expressions
  • Joined Tables
• Functions:
  • Aggregate Functions
  • Scalar Functions:
    • Date/Time
    • Math
    • Strings

JDBC

• Java JDBC API

Standard Library

• java.sql
  • DriverManager
  • Connection
  • Statement
    • PreparedStatement
    • CallableStatement
  • ResultSet
  • SQLException

Questions

SQL

• What is SQL?
• What are its sublanguages?
• What is multiplicity?
• What is cardinality?
• What is a candidate key?
• What is referential integrity?
• What are the different constraints of a column?
• what are the differences between GROUP BY and ORDER BY?
• What is the difference between IN and EXISTS?
• What are subqueries?
• What is the differenece between an aggregate function and a scalar function?
• What are the different joins in SQL?
• What are the different set operations in SQL?
• What is the difference between joins and set operations?
• What is normalization?
• What are the requirements for the first three normal forms?

JDBC

• What is JDBC?
• What dependencies are required to connect to an RDBMS?
• What are the main interfaces of JDBC?
• In what order are they used?
• What are the different types of statements?
• What is SQL Injection?
• What is a DAO?
• Describe client-server communication: how does it apply to PostgreSQL?
• What does a connection URL for PostgreSQL look like?
• How do you connect to a PostgreSQL server with a client? With Java?
• Why is connection pooling important?