Csc309 tutorial sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 17

CSC309 Tutorial: SQL PowerPoint PPT Presentation


  • 97 Views
  • Uploaded on
  • Presentation posted in: General

CSC309 Tutorial: SQL. Outline. SQL: Overview SQL: Data Definition Language SQL: Queries The Pointbase Database. SQL: Overview. SQL (Structured Query Language) is a standard language for RDBMS. allows user to create new and modify existing databases

Download Presentation

CSC309 Tutorial: 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Csc309 tutorial sql

CSC309 Tutorial: SQL


Outline

Outline

  • SQL: Overview

  • SQL: Data Definition Language

  • SQL: Queries

  • The Pointbase Database


Sql overview

SQL: Overview

  • SQL (Structured Query Language)

    • is a standard language for RDBMS.

    • allows user to create new and modify existing databases

    • allows user to pose complex queries to the database and retrieve data

  • Some common RDBMS that support SQL: Oracle, Sybase, MS SQL Server, Access, IBM DB2, etc.

  • In this course, we use the Pointbase database.

    http://www.pointbase.com


Sql data definition language

SQl: Data Definition Language

  • Use SQL DDL to create or deleted tables. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.

    The most important DDL statements in SQL are: 

    CREATE TABLE - creates a new database table

    ALTER TABLE - alters (changes) a database table

    DROP TABLE - deletes a database table

    CREATE INDEX - creates an index (search key)

    DROP INDEX - deletes an index


Sql create

SQL: CREATE

  • Create a table:

    Syntax: CREATE TABLE tbl_name( col1 data_type,col2 data_type, ... )

    Example: CREATE TABLE employee ( ID int, Name varchar(30),

    Title varchar(20), Dept varchar(20),

    Salary decimal(10,2) )

    Data types: may vary from system to system


Sql drop alter

SQL: DROP, ALTER

  • Alter a table (Add/Delete Columns)

    Syntax: ALTER TABLE table_name ADD column_name datatype

    Example: ALTER TABLE employee ADD Address varchar

    Syntax: ALTER TABLE table_name DROP COLUMN column_name

    Note: Some systems may not support dropping columns of a table

  • Destroy a table

    DROP TABLE table_name


Sql insert

SQL: INSERT

  • Insert data to the table

    insert an entire row

    Example: INSERT INTO employee

    VALUES (111, ‘John’, ‘programmer’, ‘testing’, 50000.0)

    insert data to only specific columns

    Example: INSERT INTO employee (id, name, title)

    VALUES (211, ‘Tom’, ‘manager’)

    ID NAME TITLE DEPT SALARY

    111 John programmer testing 50000.00

    211 Tom manager - -


Sql update delete

SQL: UPDATE, DELETE

  • Update the table with new data

    Syntax: UPDATE table_name SET column_name = new_value

    WHERE column_name = some_value

    Example: UPDATE employee SET salary=60000.0

    WHERE name=‘John’

  • Delete data from the table

    Syntax:DELETE FROM table_name

    WHERE column_name =some_value

    Example: DELETE FROM employee

    WHERE name=‘Tom’


Sql select

SQL: SELECT

SELECT [ALL | DISTINCT] column1[,column2]FROM table1[,table2][WHERE "conditions"][GROUP BY "column-list"][HAVING "conditions][ORDER BY "column-list" [ASC | DESC] ]

Example: comparison operators

SELECT *

FROM employee

WHERE salary>50000.0

SELECT *

FROM employee

WHERE Name LIKE ‘jo%’


Sql aggregation operators

SQL: Aggregation Operators

  • SELECT AVG(salary)FROM employeeWHERE title = 'Programmer‘

  • SELECT COUNT(*)FROM employee


Sql group by

SQL: GROUP BY

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns.

SELECT dept, MIN(salary) AS MINIMUM

FROM employee

GROUP BY dept

Original table:

NAME TITLE DEPT__SALARY___

Johnprogmer 150000.00

Tommanager 280000.00

Bobprogmer 140000.00

David tester 330000.00

Result:

DEPT MINIMUM

1 40000.00

2 80000.00

3 30000.00


Sql having

SQL: HAVING

The HAVING clause allows you to specify conditions on the rows for each

group - in other words, which rows should be selected will be based on the conditions you specify. The HAVING clause should follow the GROUP BY clause.

SELECT dept, MIN(salary) AS MIN_SALARY

FROM employee

GROUP BY dept

HAVING MIN(salary)>=40000.0

Original table:

NAME TITLE DEPT__SALARY___

Johnprogmer 150000.00

Tommanager 280000.00

Bobprogmer 140000.00

David tester 330000.00

Result:

DEPT MIN_SALARY

1 40000.00

2 80000.00


Sql order by

SQL: ORDER BY

ORDER BY allows you to display the results of your query in a sorted order (either ascending order (ASC) or descending order (DESC) based on the columns that you specify to order by. Default is by ASC.

SELECT dept, MIN(salary) AS MIN_SALARY

FROM employee

GROUP BY dept

HAVING MIN(salary)>=40000.0

ORDER BY dept DESC

Original table:

NAME TITLE DEPT__SALARY___

Johnprogmer 150000.00

Tommanager 280000.00

Bobprogmer 140000.00

David tester 330000.00

Result:

DEPT MINAGE

2 80000.00

1 40000.00


Sql boolean operators

SQL: Boolean Operators

The AND operator can be used to join two or more conditions in the WHERE clause. Both sides of the AND condition must be true in order for the condition to be met and for those rows to be displayed.

SELECT * FROM employee

WHERE salary>=50000 AND title=‘programmer’

The OR operator can be used to join two or more conditions in the WHERE clause also. However, either side of the OR operator can be true and the condition will be met

SELECT * FROM employee

WHERE title=‘tester’ OR title=‘programmer’


Sql in between

SQL: IN, BETWEEN

The IN conditional operator is a set membership test operator. It is used to test whether or not a value (stated before the keyword IN) is "in" the list of values provided after the keyword IN.

SELECT *FROM employeeWHERE name IN (‘John', 'Roberts', 'Ruiz');

The BETWEEN conditional operator is used to test whether or not a value (stated before the keyword BETWEEN) is "between" the two values stated after the keyword BETWEEN.

SELECT *

FROM employeeWHERE salary BETWEEN 40000 AND 60000;


Sql table join

SQL: Table Join

Table employee:

ID NAME TITLE DEPT_____

111 John programmer 1

211 Tom manager 2

112 Bob programmer 1

311 David tester 3

Table: contact

ID ADDRESS PHONENUM

111 23 Yonge ST (416) 123-4567

112 34 Yonge ST (416) 223-4567

211 45 Yonge ST (416) 143-4567

311 56 Yonge ST (416) 153-4567

Queries involve more than one table.

SELECT name, address, phonenum

FROM employee, contact

WHERE employee.id=contact.id

AND employee.title=‘programmer’

Result:

NAME ADDRESS PHONENUM

John 23 Yonge ST (416) 123-4567

Bob 34 Yonge ST (416) 223-4567


The pointbase database

The Pointbase Database

  • Pointbase is a full-featured, Java written relational database.

    CDF Resource: http://www.cdf.toronto.edu/~t2aboels/#JDBC

  • You can start PointBase in three different ways:

    Using PointBase Commander

    Using PointBase Console

    Using a JDBC Application

  • Using Pointbase Database on CDF

    • Create pointbase.ini file to tell pointbase where to store the database

      A typicall pointbase.ini will have a line: database.home=<full-directory-path>

    • To start pointbase commander:

      java –cp .:/h/u2/csc309h/lib/poitbase/pbembedded41ev.jar:/h/u2/csc309h/lib/pointbase/pbtools41ev.jar

      com.poitbase.tools.toolsCommander

    • You can then issue any SQL command to interact with the DB, for example, to check that pointbase was installed successfully, type “select tablename from systables”

    • More examples at http://www.cdf.toronto.edu/~t2aboels/resources/JDBC/pointbase/example/


  • Login