Introduction to Structured Query Language (SQL)

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