1 of 29

Introduction to SQL

Rongji Yao, Utku Tuluk

NYU Shanghai Library

Fall 2021

​

2 of 29

Content

  • Overview

………Overview of Database

………Software for Demo, SQL-Teaching Website

  • Basic concept in Database

………Entity, Attribute, Data

………Table, Relationship Database

  • Introduction to SQL

………Syntax, Example and Exercise

  • What will we use in real development

………Python Django

3 of 29

Overview

Information

Table

Database

Data

Record

Extract

Store

Query with SQL

Answer with Data

User

Design(with SQL)

4 of 29

Overview

SQL = Structured Query Language

When you ask a question to search engine

​

Use natural language

When you ask a question to Database?

​

Use Structured Query Language

5 of 29

Overview-Software for database Demo

MySQL Workbench for demonstrations and screenshots

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs.

6 of 29

Overview-SQL-Teaching Website for this workshop

W3school

Link: https://www.w3schools.com/sql/

​

Perfect study website for SQL beginner!

Syntax

Example

Demo Data

Exercise

7 of 29

1.1 Database-Entity, Attribute and Data

  • Entity: Noun to describe
    • Person/Place/Object/Event
  • Attributes: Characteristics of an entity
    • Person: Name, Birthday, Gender, Race, Nationality...
  • Data: Value of attributes
    • Name: Jack/Tom/Peter/Barry
    • Birthday: 5/31/2001
    • Gender: Female/Male
    • Race: Yellow/Black/White
    • Nationality: US/UK/CN/JP

How we describe the world?

8 of 29

1.2 Database-Reality to Table

Each line data is a record of Entity 加slides介绍Mysql Workbench

  • Entity→Table Name: Noun to describe
    • Person/Place/Object/Event
  • Attributes→Column: Characteristics of an entity
    • Person: Name, Birthday, Gender, Race, Nationality...
  • Data→Row: Record of entities
    • 1 Jack Wang 2001-05-01 ...
    • 2 Tom William 1999-09-01 ...

9 of 29

1.3 Database-To define a table

  • Table Name: Entity to describe
    • Mostly using Entity name
  • Column Name: Attributes of Entity
  • Column Datatype: 
    • INT: ID, Order Number
    • VARCHAR: Name, Address
    • DECIMAL: Account balance
    • DATE: Birthday, Create date
  • Column Attribute:
    • NN(Not Null): must be filled out with values when this row created
    • PK(Primary Key): Unique to identify a row in the table, must be NM
    • FK(Foreign Key): Referencing to PK of Other Table, to make sure

10 of 29

1.3 Database-To define a table

  • Column Datatype:
    • INT: integer
      • ID: 370702199907290816
      • Order Number: 30001202111030312
    • VARCHAR: character
      • Name: Peter
      • Address: 5 Metrotech, Brooklyn
    • DECIMAL: decimal
      • Account balance: 250.5
    • DATE: date type
      • Birthday: 2000-3-1
      • Create date: 2021-11-2

11 of 29

1.3 Database-To define a table

  • Column Attribute:
    • NN(Not Null): must be filled out with values when this row created

Insert this line into table

Report with error because the department_id and school_id are null

12 of 29

1.3 Database-To define a table

  • Column Attribute:
    • PK(Primary Key): Unique to identify a row in the table, must be NN

Two person have same Name, Birthday, Gender, Race, Nationality values, we could use PK to identify them.

13 of 29

1.3 Database-To define a table

No school_id = 4 in School table!

  • Column Attribute:
    • FK(Foreign Key): Referencing to PK of Other Table

14 of 29

1.4 Database-Relationship Between Tables (entity)

  • One to One
    • Student and Account
      • In NYU, one student has only one account
    • Car and plate
      • One car has only one plate
  • One to N
    • School and department
      • one school has many departments: Tandon has CSE, ECE, MFE...
    • County and state
      • one county has many states: US has NY, NJ, FL, MO...
  • N to M
    • Student and major(program)
      • one student can take more than one program/major simultaneously & one program can be taken by many students
    • Student and course
      • One student can take many courses and one course can be taken by many students

15 of 29

1.5 Database-Tables to Database

  • Database: organized collection of logically related entities

16 of 29

1.5 Database-Tables to Database

  • Why N:M needs a new table?
  • If not
    • Update both table when you need to modify previous data

​

​

yz1121 double major in CS

What DB program does?

1. Reconstruct “student_belongto_Dept” (may take more time if searching and deleting are needed)

2. Reconstruct “Department_belongto”(may take more time if searching and deleting are needed)

Hard to find whether a student is in a department...

String operation takes too much time and computing resource!

​

17 of 29

1.5 Database-Tables to Database

  • Why N:M needs a new table?
  • If so
    • Only need to update the internal table

​

More structured

Easier to insert, update, delete, collect

​

​

18 of 29

2 Introduction to SQL Syntax

For each statement/clause/function:

How we learn SQL today?

1. Learn syntax

2. Example

3. Do exercise

Using Demo Database

19 of 29

2 Introduction to SQL Syntax

Demo Database Exercise

Figure out Relationships between :

Customers&OrderID

Employees&OrderID

Suppliers&OrderID

20 of 29

2 Introduction to SQL Syntax

Demo Database Exercise

Figure out Relationship between :

Suppliers&Products

21 of 29

2 Introduction to SQL Syntax

Demo Database Exercise

Figure out Relationship between :

Categories&Products

22 of 29

2 Introduction to SQL Syntax

Demo Database Exercise

Figure out Relationship between :

OrderID&OrderDetail

OrderDetail&Products

​

23 of 29

2 Introduction to SQL Syntax

Demo Database Exercise

24 of 29

2 Introduction to SQL Syntax

Let us go to the W3school!

​

W3schools SQL

25 of 29

3 Real-life applications

  • Django
  • MTV
  • Examples

26 of 29

3.1 Django

What is Django?

​

“Django is a high-level Python web framework that encourages rapid development and clean, pragmatic design. ... It’s free and open source.” - djangoproject.com

​

27 of 29

3.2 Django MTV

Model, Template, View

Image from https://data-flair.training/blogs/django-mtv-architecture/

28 of 29

Django Code Example

https://gist.github.com/uetuluk/24dc5e960708f58f1003b2c2ef8bf0db

29 of 29

Term List

•Entity: Noun to describe

•Attributes: Characteristics of an entity

•Data: Values of attributes 

•Table Name: Entity in table

•Column: Attributes in table

•Row: data in table

•Column Name: Name of column

•Column Datatype: Datatype of column

•Column Attribute: Attribute of column

•Relationship: Relationship between tables, including: one to one, one to N and N to M

• MySQL Workbench: A visual tool for MySQL database

​