1 / 17

SQL: Data Manipulation I

SQL: Data Manipulation I. Chapter 5 CIS 458 Sungchul Hong. Last Class. EERD Superclass Subclass Option/mandatory And/Or. This Class. SQL (Structured Query Language) Single Table Operation Multiple tables Operation Subquery v.s. Join. Introduction to SQL. Structured Query Language

josef
Download Presentation

SQL: Data Manipulation I

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

More Related