1 / 17

CSC309 Tutorial: SQL

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

ivie
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSC309 Tutorial: SQL

  2. Outline • SQL: Overview • SQL: Data Definition Language • SQL: Queries • The Pointbase Database

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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 - -

  8. 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’

  9. 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%’

  10. SQL: Aggregation Operators • SELECT AVG(salary)FROM employeeWHERE title = 'Programmer‘ • SELECT COUNT(*)FROM employee

  11. 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___ John progmer 1 50000.00 Tom manager 2 80000.00 Bob progmer 1 40000.00 David tester 3 30000.00 Result: DEPT MINIMUM 1 40000.00 2 80000.00 3 30000.00

  12. 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___ John progmer 1 50000.00 Tom manager 2 80000.00 Bob progmer 1 40000.00 David tester 3 30000.00 Result: DEPT MIN_SALARY 1 40000.00 2 80000.00

  13. 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___ John progmer 1 50000.00 Tom manager 2 80000.00 Bob progmer 1 40000.00 David tester 3 30000.00 Result: DEPT MINAGE 2 80000.00 1 40000.00

  14. 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’

  15. 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;

  16. 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

  17. 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/

More Related