SQL Intro

1
SQL Intro

• Dagmar Kusiak

2
Chapter Objectives

• Intro to SQL (structured query language)
• Example database
• THREE parts to SQL
• DDL (data definition language)
• DML (data manipulation language)
• DCL (data control language)

3
Example Database
4
Example database continued
5
DDL

• Creating tables
• GUI Interface
• Create table/alter table commands

6
DDL continued
GUI Interface
7
DDL continued
Create table and alter table commands
8
DDL continued
9
DDL continued

• Drop command
• Drop lttablenamegt

10
DML

• SQL Commands
• SELECT
• INSERT
• UPDATE
• DELETE

11
DML continued

• SELECT
• retrieve information from the database

12
DML continued

• SELECT Example
• SELECT ltcolumn1, column2..gt FROM lttablenamegtwhere
  ltconstraintgt
• Retrieve info about all the projects
• SELECT FROM PROJECT
• SELECT ProjectID, Name, Department, MaxHours FROM
  PROJECT
• Retrieve only the projects from the Finance
  department
• SELECT from PROJECT WHERE PROJECT.department
  Finance

13
DML continued
14
DML continued
15
DML continued
16
DML continued

• Aggregate functions
• SUM()
• AVG()
• COUNT()
• MIN()
• MAX()
• JOINS
• OTHER
• Distinct
• Order by
• Group by
• Having
• like

17
DML continued

• SUM
• Total hours that people in the Finance department
  spend on a project
• SELECT max(MaxHours) from PROJECT
• GROUP by department

18
DML continued

• JOINS
• Gathering information from multiple tables

19
DML continued
20
DML continued

• INSERT
• Insert rows into the database

21
DML continued

• INSERT Example
• INSERT INTO lttablenamegt (ltcolumn1, column2,)
  VALUES (ltvalue1, value2gt)
• INSERT row in the EMPLOYEE table
• INSERT INTO EMPLOYEE (EmployeeNumber, Name,
  Phone, Department) VALUES (100, Mary Jacobs,
  285-8879, Accounting)
• INSERT INTO EMPLOYEE VALUES (100, Mary Jacobs,
  285-8879, Accounting)

22
DML continued
23
DML continued
24
DML continued

• UPDATE
• Change a row of data

25
DML continued

• UPDATE Example
• UPDATE lttablenamegt SET ltcolumnname valuegt WHERE
  ltconstraintgt
• Change Mary Jacobs EmployeeNumber from 100 to 200
• UPDATE EMPLOYEE SET EmployeeNumber 200 where
  Name Mary Jacobs

26
DML continued
27
DML continued

• DELETE
• delete a row of data

28
DML continued

• DELETE Example
• DELETE lttablenamegtWHERE ltcolumnname valuegt
• DELETE the row with EmployeeNumber 200
• DELETE EMPLOYEE WHERE EmployeeNumber 200

29
DCL

• Control permissions to users
• GRANT
• DENY
• REVOKE

30
DCL continued

• GRANT
• Grant permissions to a user

31
DML continued
32
DCL continued

• GRANT Example
• GRANT ltpermissionnamegt ON lttablenamegt TO
  ltusernamegt

33
DCL continued

• DENY
• deny permissions to a user

34
DCL continued

• DENY Example
• DENY ltpermissionnamegt ON lttablenamegt TO ltusernamegt

35
DCL continued

• REVOKE
• revoke permissions to a user

36
DCL continued

• REVOKE Example
• REVOKE ltpermissionnamegt ON lttablenamegt TO
  ltusernamegt