SQL: Data Manipulation I

1. SQL: Data Manipulation I Chapter 5 CIS 458 Sungchul Hong

2. Last Class • EERD • Superclass • Subclass • Option/mandatory • And/Or

3. This Class • SQL (Structured Query Language) • Single Table Operation • Multiple tables Operation • Subquery v.s. Join

4. Introduction to SQL • Structured Query Language • Create the database and relation structures • Perform basic data management tasks, such as insertion, modification, and deletion of data. • Perform both simple and complex queries. • SQL major components • Data Definition Language (DDL) • Data Manipulation Language (DML) • 2 ways to use SQL • Use SQL interactively • Use SQL statements in a procedural language.

5. Introduction to SQL (2) • It is a non-procedural language. • Specify what information you require • It is a free format language. • SQL can be used by a range of users including Database Administrators (DBA), management personnel, application developers, and many other types of end-user.

6. Introduction to SQL (3) • ISO standard SQL … 1992 • Extensions or dialect provided by vendors.

7. Importance of SQL • SQL is the first and, so far, only standard database language to gain wide acceptance. • SQL has also become a Federal Information Processing Standard (FIPS), to which conformance is required for all sales of DBMSs to the US government. • Specialized implementations of SQL are beginning to appear that are directed at new market, such as Online Analytical Processing (OLAP)

8. Writing SQL Commands • SQL statement • Reserved words (fixed part of the SQL Lang.) • User-defined words. • Tables, columns, views, indexes, … • Terminator (;) • Components of an SQL statements are case insensitive. (except literal character data)

9. SQL • SELECT ss#, Name, address, gpa • FROM student • WHERE gpa >= 3.0;

10. Suggested SQL Format • Each clause in a statement should begin on a new line; • The beginning of each clause should line up with the begging of other clauses; • If a clause has several parts, they should each appear on a separate line and be indented under the start of the clause to show the relationship. • Upper-case letters are used to represent reserved words. • Lower-case letters are used to represent user-defined words

11. SQL Format SELECT FName, LName, DName FROM Staff, Department WHERE Dno = DNumber AND Sex =‘F’;

12. Data Manipulation • SELECT --- to query data in the database; • INSERT --- to insert data into a table; • UPDATE --- to update data in a table; • DELETE --- to delete data from a table.

13. Simple Queries • SELECT [DISTINCT|ALL] • {*|[columnExpression[AS newName • ]][,…]} • FROM TableName [alias], […] • [WHERE conditon] • [GROUP BY columnlList][HAVING condition] • [ORDER BY columnList]

14. Example 5.1 – 5.9 • Retrieve all columns, all rows • SELECT staffNo, fName, lName, position, sex, DOB, slary, branchNO • FROM staff; • SELECT * • FROM staff;

15. Retrieve specific columns, all rows (5.2) • SELECT staffNo, fName, lName, salary • FROM Staff;

16. Use of DISTINCT (5.3) • SELECT propertyNo • FROM Viewing; • SELECT DISTINCT propertyNo • FROM Viewing;

17. Calculated Field (5.4) • SELECT staffNo, fName, lName, salary/12 • FROM Staff; • Calculated field, computed or derived field • SELECT staffNo, fName, lName, salary/12 AS monthlySalary • FROM Staff