Part 3: Introduction to SQL

1722 days ago, 656 views
PowerPoint PPT Presentation
Section 3: Prologue to SQL. Outline of The SQL Question Dialect Information Definition Fundamental Inquiry Structure Extra Essential Operations Set Operations Invalid Qualities Total Capacities Settled Subqueries Alteration of the Database. History.

Presentation Transcript

Slide 1

Section 3: Introduction to SQL Overview of The SQL Query Language Data Definition Basic Query Structure Additional Basic Operations Set Operations Null Values Aggregate Functions Nested Subqueries Modification of the Database

Slide 2

History IBM Sequel dialect created as a component of System R extend at the IBM San Jose Research Laboratory Renamed Structured Query Language (SQL) ANSI and ISO standard SQL: SQL-86 SQL-89 SQL-92 SQL:1999 (dialect name got to be Y2K agreeable!) SQL:2003 Commercial frameworks offer most, if not all, SQL-92 highlights, in addition to differing highlight sets from later benchmarks and extraordinary exclusive elements. Not all cases here may chip away at your specific framework.

Slide 3

Data Definition Language Allows the particular of an arrangement of relations as well as data about every connection, including: The pattern for every connection. The area of qualities related with each trait. Trustworthiness imperatives Security and approval data for every connection. The arrangement of records to be kept up for every relations. - > physical level The physical stockpiling structure of every connection on circle. - > physical level

Slide 4

Domain Types in SQL char(n). Settled length character string, with client determined length n. varchar(n). Variable length character strings, with client indicated most extreme length n. int. Number (a limited subset of the whole numbers that is machine-subordinate). smallint. Little whole number (a machine-subordinate subset of the whole number area sort). numeric(p,d). Settled point number, with client determined accuracy of p digits, with d digits to one side of decimal point. For instance, 34.2 is numeric(3,1). genuine, twofold accuracy. Coasting point and twofold accuracy skimming point numbers, with machine-subordinate exactness. float(n). Drifting point number, with client determined exactness of at any rate n digits. More are shrouded in Chapter 4.

Slide 5

Create Table Construct A SQL connection is characterized utilizing the make table summon : make table r ( A 1 D 1 , A 2 D 2 , ..., A n D n , <integrity-limitation 1 >, ..., <integrity-imperative k >); r is the name of the connection each An i is a trait name in the mapping of connection r D i is the information kind of qualities in the space of property An i Example : make table educator ( ID scorch (5), name varchar (20) not invalid, dept_name varchar (20), pay numeric (8,2)); embed into teacher values ('10211', 'Smith', 'Science', 66000); 字串加單引號 embed into educator values ('10211', invalid , 'Science', 66000); - > off-base! embed into educator values ('10211', 'Smith', 'Science', invalid ); - > right!

Slide 6

Integrity Constraints in Create Table not invalid essential key ( A 1 , ..., A n ) outside key ( A m , ..., A n ) references r Example: Declare ID as the essential key for educator. make table educator ( ID burn (5), name varchar (20) not invalid, dept_name varchar (20), compensation numeric (8,2), essential key ( ID ), outside key (dept_name ) references office ); essential key statement on a characteristic consequently guarantees not invalid

Slide 7

And a Few More Relation Definitions make table division ( dept_name varchar (20), building varchar (15), spending plan numeric (12, 2), essential key (dept_name) ) ; make table educates ( ID varchar (5) , course_id varchar (8), sec_id varchar (8), semester varchar (6), year numeric (4,0), essential key (ID, course_id, sec_id, semester, year) , remote key ( ID ) references teacher, remote key ( course_id, sec_id, semester, year ) references area ); Note: 1. An essential key can comprise of many traits. 2. A table can have numerous outside keys.

Slide 8

Drop and Alter Table Constructs drop table charge erases all data about the dropped connection from the database. 如果只刪除資料而不刪除定義的話,請使用 erase 語法 . change table charge is utilized to add ascribes to a current connection: modify table r include A D; where An is the name of the credit to be added to connection r and D is the area of A. All tuples in the connection are appointed invalid as the incentive for the new characteristic. adjust table r drop A; where An is the name of a trait of connection r

Slide 9

Relational Schemas educator ( ID, name, dept_name, compensation) instructs ( ID, course_id, sec_id, semester, year ) course ( course_id , title, dept_name, credits) segment ( course_id, sec_id, semester, year , building, room_number, time_slot_id) understudy ( ID , name, dept_name, tot_cred) takes ( ID, course_id, sec_id, semester, year , review) office ( dept_name , building, spending plan)

Slide 10

Relational Instances area course

Slide 11

More Relational Instances office shows teacher

Slide 12

Basic Query Structure A commonplace SQL question has the frame: select A 1 , A 2 , ..., A n from r 1 , r 2 , ..., r m where P; An i speaks to a characteristic R i speaks to a connection P is a predicate. The consequence of a SQL inquiry is a connection. Take note of: A SQL inquiry is typically finished with the semicolon ";", however it may shift in various programming or diverse programming dialects. (For instance, 在 程式中要加 .)

Slide 13

The select Clause The select statement list the traits fancied in the consequence of an inquiry compares to the projection operation of the social variable based math Example: discover the names of all teachers: ( 參照 occurrence ) select name from educator; NOTE: SQL names are case heartless (i.e., you may utilize upper-or lower-case letters.) E.g., Name ≡ NAME ≡ name Some individuals utilize capitalized wherever we utilize intense text style.

Slide 14

The select Clause (Cont.) SQL permits copies in relations and additionally in inquiry comes about. To compel the end of copies, embed the catchphrase unmistakable after select . Discover the office names of all teachers, and expel copies select particular dept_name from educator; A reference mark in the select statement means "all properties" select * from teacher; The select proviso can contain number juggling expressions including the operation, +, –,  , and/, and working on constants or attributes of tuples. The accompanying question would give back a connection that is the same as the teacher connection, with the exception of that the estimation of the trait salary is separated by 12. select ID, name, compensation/12 from educator; <- A question can have no "where".

Slide 15

The where Clause The where provision indicates conditions that the outcome must fulfill Corresponds to the determination predicate of the social polynomial math. To discover all teachers in Comp. Sci. dept with compensation > 70000 select name from educator where dept_name = " Comp. Sci.' and pay > 70000; Comparison results can be joined utilizing the sensible connectives and, or, and not. Examinations can be connected to consequences of number juggling expressions. 注意: 字串用單引號 不等於是用 <>

Slide 16

The from Clause The from statement records the relations required in the question Corresponds to the Cartesian item operation of the social polynomial math. Locate the Cartesian item teacher X educates select  from educator, educates; creates each conceivable "educator – instructs" combine , with all qualities from both relations. Cartesian item not exceptionally valuable straightforwardly, but rather helpful consolidated with where-provision condition ( choice operation in social variable based math).

Slide 17

Cartesian Product shows teacher

Slide 18

Joins For all educators who have shown courses, discover their names and the course ID of the courses they instructed. select name, course_id from educator, instructs where instructor.ID = teaches.ID; Find the course ID, semester, year and title of each course offered by the Comp. Sci. division ( 參照 case ) select segment .course_id, semester, year, title from area, course where segment .course_id = course.course_id and dept_name = 'Comp. Sci.'; Join: Cartesian item + 限制式 PS. 相同屬性出現在兩個以上的表格,前面必須加註 來源表格

Slide 19

Try Writing Some Queries in SQL Find the titles of courses in the Comp. Sci. division that have 3 credits. Reply: Find the course_ids and sec_ids which were offered by a teacher named "Einstein"; ensure there are no copies in the outcome. Reply:

Slide 20

Natural Join Natural join matches tuples with similar qualities for every single regular property, and holds just a single duplicate of every normal segment 注意: basic characteristics 通常是 essential key 或 outside key select * from teacher common join instructs ; <- 有些軟體不支援此語法 !

Slide 21

Natural Join (Cont.) Danger in regular join: inconsequential characteristics with same name which get likened inaccurately Example: List the names of educators alongside the titles of courses that they show Incorrect variant (compares course.dept_name with instructor.dept_name) select name , title from teacher common join shows normal join course ; <- 錯誤的原因在於 teacher 和 course 不用同一系 ! Redress variant select name , title from educator regular join instructs , course where instructs . course_id = course . course_id ; <- 在 from 裡使用逗號 "," ,所以 course 是和前一個 joined connection 做 cartesian item. 注意:大部分軟體所支援的 join 語法會在第四章補充。

Slide 22

The Rename Operation The SQL permits renaming relations and traits utilizing the as provision: old-name as new-name "as" 放在 select statement: select ID, name, pay/12 as monthly_salary from educator; Note: "as" 是在最後做,所以新定義的屬性名稱不能用在 question 其�