1 / 30

Introduction to Structured Query Language (SQL)

Introduction to Structured Query Language (SQL). COM S 461 2013-Fall Instructor: Ying Cai Iowa State University. SQL: What and Why ? (1/2). Programming Language:. Instructions. Result. Programmers. Computer. SQL: What and Why ? (2/2). Query Language:. Query. Result. DB Users.

efrat
Download Presentation

Introduction to Structured Query Language (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. Introduction toStructured Query Language (SQL) COM S 461 2013-Fall Instructor: Ying Cai Iowa State University

  2. SQL: What and Why? (1/2) Programming Language: Instructions Result Programmers Computer

  3. SQL: What and Why? (2/2) Query Language: Query Result DB Users DBMS (Database Server)

  4. Contents • Setup a Database Server on your Computer. • How to define data using SQL: • Data Definition Language (DDL) • Create Tables/Update Tables… • How to manipulate/access data using SQL: • Data Manipulation Language (DML) • Insert Data/Update Data/Read Data… • SQL Functions

  5. Setup Database Server (1/2) • Step 1. Download (Installer/Source code available) • MySQL • http://dev.mysql.com/downloads/ • MariaDB • https://downloads.mariadb.org/ • Step 2. Setup & Configuration • Port. • Remote Access. • Root User. • Step 3. Connect to server. • You’ll need: User/PSW, Server IP/Port.

  6. Setup Database Server (2/2) The Clinet/Server Structure Client 1 DB Connection Client 2 Database Server Client 3

  7. Programming with SQL

  8. Data Definition Language • (Basic) Keywords of DDL:

  9. Syntax of CREATE • Create a new database. • Create a new table CREATE DATABASE db_name; • Data types: • INT/INTEGER(size) • DECIMAL(size,d) • CHAR(size) • VARCHAR(size) • Date(yyyymmdd) CREATE TABLE table_name ( column_1 INT, column_2 CHAR(10) NOT NULL, column_3 VARCHAR(256), column_4 INT DEFAULT 0, … … ) ;

  10. Syntax of CREATE • Create a new database. • Create a new table CREATE DATABASE db_name; • Data types: • INT/INTEGER(size) • DECIMAL(size,d) • CHAR(size) • VARCHAR(size) • Date(yyyymmdd) CREATE TABLE table_name ( column_1 INT, column_2 CHAR(10) NOT NULL, column_3 VARCHAR(256), column_4 INT DEFAULT 0, … … ) ;

  11. Syntax of ALTER • Add a column to an exiting table. • Change a column of an exiting table. • Delete a column from an exiting table. ALTER TABLE table_name ADD column_namedata_type; ALTER TABLE table_name ALTER COLUMN column_namenew_data_type; or CHANGDE COLUMN old_namenew_namedata_type; or MODIFY COLUMNB column_namenew_data_type; ALTER TABLE table_name DROP COLUMN column_name;

  12. Syntax of DROP • Delete a Database • Delete a Table. DROP DATABASE db_name DROP TABLE table_name

  13. Data Manipulation Language • (Basic) Keywords of DML:

  14. Syntax of SELECT • Use SELET to get desired data in given format: • Example 1: Get students’ Uid and name, who have GPA higher than 3.5, sorted by their GPA in descending order. SELECT column_1 column_2 … FROM table_1 table_2 … WHERE condition ORDER BY expression ASC/DESC SELECT uid, student_name FROM students WHERE gpa > 3.5 ORDER BY gpaDESC

  15. Syntax of SELECT • Example 2: Get all information about female students who is older than 20. • Example 3: Get the average speed of all cars in a table where only moving distance and time are stored. SELECT * FROM students WHERE ( 2013 – YoB ) > 20 AND gener= ‘F’ SELECT (distance/time) AS speed FROM cars WHERE time > 0

  16. Syntax of SELECT • Select from Multiple Tables: (Multiple Table Query) • The DBMS will combine the two tables to generate a new Virtual Table (Cross Join). SELECT * FROM students course

  17. Syntax of SELECT • Select from Multiple Tables: (Multiple Table Query) • The DBMS will combine the two tables to generate a new Virtual Table (Cross Join). SELECT * FROM students course

  18. Syntax of SELECT • Example 4: Show the uid and name of all students who registered for the Database course. SELECT student.uid student.name FROM students course WHERE course.name = “Database” AND student.id IN course.students

  19. Syntax of SELECT • Example 5: We have three tables: • Show the name of each course and the name of the TA assigned to this course. Student Course TA SELECT course.name, student.name FROM students course TA WHERE Courese.Cid= TA.Cid AND Student.Uid= TA.Uid

  20. Syntax of SELECT • Select Distinct values (No duplicates) • Example 6: Show students with distinct birthday and name. SELECT DISTINCT column_1, column_2 … FROM table_1, table_2 … WHERE conditions … SELECT DISTINCT Name DoB FROM Student Course

  21. Syntax of SELECT • Select a given number of items SELECT TOP NUMBER column_1 column_2 FROM table_1 table_2 WHERE conditions… or SELECT column_1 column_2 FROM table_1 table_2 WHERE conditions… LIMIT NUMBER

  22. Syntax of SELECT • Example 7: Show the name of 100 students with the highest GPA. SELECT name FROM students ORDER BY gpa DESC LIMIT 100

  23. Syntax of Update • Update the value of existing item(s). Update table_name SET column_1 = value_1 column_2 = value_2 … … WHERE conditions…

  24. Syntax of Insert • Insert new items into a table INSERT INTO table_name VALURES (value_1, value_2 … ), # row 1 (value_1, value_2 … ), # row 2 …. …

  25. Syntax of Delete • Delete items from a table • DELETE without any condition will delete all items in the table, which is forbidden by many DBMS. DELETE FROM table_name WHERE conditions…

  26. SQL Functions • AVG() • Calculate the Average of selected data column • ORDER BY/LIMIT is no longer necessory. SELECT AVG( column_name| expression) AS value_name FROM table_1, table_2 … WHERE conditions …

  27. SQL Functions • COUNT () • Count the number of items selected. • MAX () / MIN () • Return the Maximal/Minimal value in selected data. SELECT COUNT( column_name| expression) AS value_name FROM table_1, table_2 … WHERE conditions … SELECT MAX/MIN( column_name| expression) AS value_name FROM table_1, table_2 … WHERE conditions …

  28. SQL Functions • SUM() • Calculate the sum of selected values. SELECT SUM( column_name| expression) AS value_name FROM table_1, table_2 … WHERE conditions …

  29. SQL Leaning resources • General Introductions and Examples: W3CSchool • http://www.w3schools.com/sql/ • SQL reference • MySQL: http://dev.mysql.com/doc/refman/5.6/en/ • T-SQL (Microsoft): http://technet.microsoft.com/en-us/library/ms189826(v=sql.90).aspx • OracleSQL: http://docs.oracle.com/cd/B19306_01/server.102/b14200/toc.htm

  30. Q&A

More Related