And Franchise Colleges HSQ - DATABASES & SQL 3 SQL Overview By MANSHA NAWAZ Section 3 SQL Overview 1
OVERVIEW • • Library Database – ERD Library Database - Tables Structured Query Language (SQL) SQL Usage SQL Commands SQL Data Definition Language (DDL) SQL Data Manipulation Language (DML) Section 3 SQL Overview 2
Library Database – ERD The library model used to demonstrate SQL ERD: Library Section 3 SQL Overview 3
Library Database - Tables Publisher (publisher-code, publisher-name) The library tables used to demonstrate SQL Limit (borrower-status, loan-limit) Borrower (bor_no, bor-name, bor-state, addr 1, addr 2, town, postcode) Copies (access_no, isbn, buy-price, buy_date) Book (isbn, pub_code, title, pub_date, now_price) Authorship (isbn, author_name) Loan (access_no, bor_no, loan-date) Reservation (bor_no, isbn, reserve-date) • The data tables are on the website. Section 3 SQL Overview 4
Library Database - Tables The library tables used to demonstrate SQL LIBRARY DATABASE MS Access 2000 Section 3 MS SQL – Query Analyzer Meta File SQL Overview MS SQL – Enterprise Manager 5
Structured Query Language (SQL) • SQL was developed by IBM in the 1970 s for use in System R. It is the de facto standard as well as being an ISO and ANSI standard. • It is often embedded in general purpose programming languages. • The first SQL standard, in 1986, provided basic language constructs for defining and manipulating tables of data; a revision in 1989 added language extensions for referential integrity and generalised integrity constraints. • Another revision in 1992 provided facilities for schema manipulation and data administration, as well as substantial enhancements for data definition and data manipulation. • SQL is the most popular, and now standard, language for manipulating data in a relational database. Developers and Users can use SQL to interrogate databases. Section 3 • • Creating, Accessing Searching Modifying SQL Overview 6
SQL Usage • SQL used by many different DBMS: • Oracle • Microsoft SQL Server • MS Access • Sybase etc. • Database Management System (DBMS) interfaces provided in MS Access and MS SQL Server are wrote in SQL. • Initially we will examine SQL under MS Access – non-standard sql • In the main we use MS SQL Server 2000 – standard sql • SQL is the most popular, and now standard, language for manipulating tables in a relational database is SQL. Section 3 SQL Overview 7
SQL Commands • SQL are a combination of commands classified as: • Data Definition Language (DDL) – DDL is used to specify the data in a database. – The DDL statements define database objects, eg databases, tables, views, indexes, users, constraints, user-defined data types: CREATE, DROP, ALTER – A full guide is provided here • Data Manipulation Language (DML) – DML is used to access the data in a database. – The DML statements manipulate data: SELECT, INSERT, DELETE, UPDATE – A full guide is provided here • Data Control Language (DCL) – DCL is used to control access to the data in a database. – The DCL statements control access to data: GRANT, DENY, REVOKE Section 3 SQL Overview 8
Data Definition Language (DDL) • SQL provides a Data Definition Language (DDL) to create and maintain defined database objects Databases Tables Views Indexes Users Constraints 1. CREATE TABLE 2. CREATE TABLE and Foreign Keys 3. CREATE INDEX user-defined data types: • This is a demonstration guide to using SQL 4. CREATE VIEW • SQL examples generally based on the Library Database 5. CREATE TRIGGER • 6. DROP TABLE The DDL statements build our Data Model Section 3 SQL Overview 9
CREATE TABLE • • SQL command to generate a new table. Syntax: – create table "tablename“ – ("column 1" "data type" [optional constraint], "column 2" "data type" [optional constraint], "column 3" "data type" [constraint]); – Data types: char, int, varchar, etc. – Constraints: identity, primary key, default Examples of Create Table create table students ( student. ID char(9) not null primary key, first. Name varchar(15) not null, last. Name varchar(20) not null, grade char(2), ) Section 3 SQL Overview 10
CREATE TABLE and Foreign Keys The syntax for describing tables takes the following format. CREATE TABLE publisher ( pub_code CHAR(4) NOT NULL, pub_name CHAR(20) NOT NULL, CONSTRAINT pub_primary_key PRIMARY KEY (pub_code)); • The NOT NULL indicates that a value must always be present for that attribute. – The primary key is defined through a ‘constraint’. • Notice that the constraint is named. • If a table is created, but later a new column needs to be added: ALTER TABLE table_name ADD column_name data_type; Section 3 SQL Overview 11
CREATE TABLE and Foreign Keys • Defining both primary and foreign keys. CREATE TABLE BOOK ( isbn CHAR(8) NOT NULL, pub_code CHAR(4), title CHAR(40), pub_date CHAR(4), now_price NUMBER(10, 2), CONSTRAINT bookpublisher FOREIGN KEY (pub_code) REFERENCES publisher (Pub_code), CONSTRAINT book_primary_key PRIMARY KEY (isbn)); Section 3 SQL Overview 12
CREATE INDEX • Improving database performance. – Indexes are essentially "look up" tables on a given set of attributes, and can improve the performance of a database. • UNIQUE indexes provide the facility for the implementation of table identifiers this is how a primary key constraint works. CREATE UNIQUE INDEX pub_index ON publisher (pub_code); • To create a secondary (non unique index: CREATE INDEX res_index ON reservation (reserve_date); • DROP INDEX - Remove an index DROP INDEX res_index; Section 3 SQL Overview 13
CREATE VIEW - Virtual Tables • Views are virtual tables that are derived from other views or from tables that exist in a database (so called "base" tables). • So to create a view of just the publisher_names from the Publisher table. CREATE VIEW publish_name(publishers) AS SELECT pub_name FROM publisher; Section 3 SQL Overview 14
• The view would therefore form the following virtual table. SELECT * FROM publish_name • Another example: CREATE VIEW books_at_499 AS SELECT title, now_price FROM book WHERE now_price = 4. 99; SELECT * FROM now_price = 4. 99; Section 3 SQL Overview 15
Views using Multiple tables • Creating a VIEW with 2 base tables. CREATE VIEW loan_names(bookloan, member) AS SELECT loan. access_no, borrower. bor_name FROM loan, borrower WHERE borrower. bor_no = loan. bor_no; SELECT * FROM loan_names; Section 3 SQL Overview 16
VIEWS of VIEWS • Views can, once created, be used in SELECT statements, as if they actually exist as a table. SELECT title FROM books_at_499; • Views can also be defined based on other views. CREATE VIEW smithloan AS SELECT * FROM loan_names WHERE member = 'Smith*'; • DROP VIEW - Remove an existing view • Section 3 DROP VIEW loan_names; SQL Overview 17
CREATE TRIGGER • A trigger is a set of SQL code that is automatically executed whenever an associated action is performed Example: • To have the entire table outputted whenever we insert an entry Create Trigger disp. All. Entries on students for insert as select * from students Section 3 SQL Overview 18
DROP TABLE • Remove an existing table DROP TABLE table_name; • To eliminate the publisher table, you would use: DROP TABLE publisher; • To eliminate the student table, you would use: DROP TABLE students Section 3 SQL Overview 19
End of Data Definition Language Section 3 SQL Overview 20
Data Manipulation Language (DML) • SQL is a Data Manipulation Language (DML) used to develop and manipulate data in a relational database is SQL. • This is a demonstration guide to using SQL • SQL examples generally based on the Library Database • The DML statements manipulate data: 1. SELECT 2. SELECT & FROM 3. WHERE CLAUSE 4. ORDER BY 5. DISTINCT 6. BETWEEN and LIKE 7. IN and NOT IN 8. Joining Tables 9. Aliases for Tables 10. More on Joins 11. Examining the Joins 12. Sub-Queries 13. INSERT 14. CREATE TRIGGER 15. DELETE 16. UPDATE Section 3 SQL Overview 21
DML OVERVIEW • • DML is used for the manipulation of data in a database via by applications and/or directly by end-users. As a relational database is a collection of tables with associated data it can be seen to be made up of two parts. The first part is the structure of the tables and their columns and the second part is the data that will be stored in these columns to form the database. SQL provides language facilities that enable the definition of the structure of the tables and their columns and language facilities for the maintenance and manipulation of the data that will be stored in the tables. In this module we are concerned with the manipulation and maintenance of the data that is stored in the database. The part of the SQL language that covers this area is know as the Data Manipulation Language or DML for short. The DML is the subset of the SQL language which invokes actions from the DBMS to manipulate data in the database. The actions in the database are guaranteed by the DBMS to maintain the integrity of the data in the database. The application programs issue DML commands to change the contents of the database to reflect changes in the real world data. Using DML statements you can: Select data from a table, which can be specified precisely using clauses in the SQL statement. – – – Insert data values into tables as rows. Update existing data values in rows. Delete certain rows from certain tables. SQL contains DML commands such as INSERT, UPDATE, and DELETE Section 3 SQL Overview 22
SELECT • • Select is used to retrieve desired portions of the database Optional conditional statement where is used to discriminate among the entries Conditions: <, =, <>, LIKE. . . And also use wildcard operator % Syntax: – select "column 1" [, "column 2", etc] – from "tablename" – [where "condition"] note: [] = optional Examples of Select • Matching any last names that end in ‘s’: select first. Name, last. Name from students where last LIKE '%s‘ • • Getting all fields of all entries back: select * from students Finding Darryn select * from students where first. Name = ‘Darryn' Section 3 SQL Overview 23
Other things we can do with Select • Select is the most powerful command, and it deserves more attention • Other things that we can do with select: – – – Finding minimum, maximum, average etc. values Group entries by similar values in specific columns Performing arithmetic or sorting on entries Joining tables Countless others Section 3 SQL Overview 24
SELECT & FROM • • A simple example SELECT bor_state FROM limit; If we want to see all columns in a table then we could write them out explicitly, as follows: SELECT access_no, isbn, buy_price, buy_date FROM copies; or: SELECT * FROM copies; Section 3 SQL Overview 25
WHERE CLAUSE SELECT * FROM book WHERE now_price > 5; • If we only wanted book details for the books with isbn '86007906', then the query would be: SELECT * FROM book WHERE isbn = '86007906'; • If we wanted book details for books with NOW_price 4. 99 or 5. 99, SELECT * FROM book WHERE now_price = 4. 99 OR now_price = 5. 99; • AND, OR & NOT can be used in WHERE statements. Section 3 SQL Overview 26
ORDER BY • If you wish to be sure of a sequence of output then the ORDER BY clause is appended to the basic SQL statement. SELECT * FROM publisher ORDER BY pub_name; Or. . SELECT * FROM book WHERE pub_date > '1989' ORDER BY pub_date, title; Section 3 SQL Overview 27
DISTINCT • Some retrievals may result in table outputs that have duplicate rows. – For instance, the following query produces a table with duplicate rows. SELECT bor_no FROM reservation; • gives: To eliminate the duplicates, the DISTINCT operator is applied to the column in the SELECT statement, as follows. SELECT DISTINCT bor_no FROM reservation; gives: Section 3 SQL Overview 28
BETWEEN and LIKE • • BETWEEN SELECT * FROM book WHERE now_price BETWEEN 4. 45 AND 5. 49; LIKE SELECT * FROM author WHERE author_name LIKE '%Pr%'; – Note the % character for a wildcard rather than – Why not Section 3 * * use as usual? SQL Overview 29
IN and NOT IN • These are used to find rows where values of the required attribute match any one of a set of values, or fail to match any of the set of values. SELECT * FROM book WHERE now_price IN [ 4. 99, 5. 99 ] ; – This would look for details of books with a current price of 4. 99 or 5. 99. • NOT IN provides the reverse of IN, so; SELECT * FROM book WHERE pub_date NOT IN ['1980', '1986']; – What does this do? Section 3 SQL Overview 30
Joining Tables • The most important aspect of SQL! • Sometimes, in order to satisfy a query, data from more than one table needs to be output or manipulated as part of that query. SELECT bor_name FROM borrower, reservation WHERE borrower. bor_no = reservation. bor_no; • This lists the names of the borrowers for every RESERVATION. (Why are some repeated? ) Section 3 SQL Overview 31
Aliases for Tables • This allows you to declare a short name for a table inside an SQL statement. SELECT b. bor_no, b. bor_name, r. isbn FROM borrower b, reservation r WHERE b. bor_no = r. bor_no ; • This stops SQL code being quite so longwinded. – Strictly you don't need to use an alias for an attribute that is in only one table within the query. – In this case we only need the alias for bor_no. • The main purpose of aliases is to allow the use of a table more than once. Section 3 SQL Overview 32
More on Joins • Supposing we had wanted, for all reservations, the bor_no and bor_name along with the title of each book. BOOK(isbn, pub-code, title, pub-date, now-price) BORROWER(bor-no, bor-name, bor-state, addr 1, addr 2, town, postcode) RESERVATION(bor_no, isbn, reserve-date) • Can you see the attributes to join on? Section 3 SQL Overview 33
• The bor_name is in the borrower table and the title is in the book table • The reservation table has to be joined with the other two tables. • Notice: bor_no is in both reservation and borrower. isbn is in both reservation and book. • So … can you write the query? SELECT br. bor_no, br. bor_name, bk. isbn, bk. title FROM borrower br, reservation r, book bk WHERE br. bor_no = r. bor_no AND r. isbn = bk. isbn; Section 3 SQL Overview 34
Examining the Joins • • Notice how simple it is to write the syntax for the two joins. The original tables - can you see how it works? BOOK(isbn, pub-code, title, pub-date, now-price) BORROWER(bor-no, bor-name, bor-state, addr 1, addr 2, town, postcode) RESERVATION(bor_no, isbn, reserve-date) Section 3 SQL Overview 35
Sub-Queries • Sub-queries can be used as an alternative to joining tables. Sub-queries simply allow you to write a join in another, often simpler way. • A standard join: SELECT bor_name FROM borrower, reservation WHERE borrower. bor_no = reservation. bor_no; – This finds the name of all borrowers who have made reservations. • Written as a sub-query: SELECT bor_name FROM borrower WHERE bor_no IN (SELECT bor_no FROM reservation); Section 3 SQL Overview 36
Sub-Queries cont. • Sub-queries can be very useful because they can retrieve data without introducing aliases. • For example: Find all the books published by publishers other then the publisher who published 'Foundation and Empire': SELECT isbn, title FROM book WHERE pub_code NOT IN (SELECT pub_code FROM book WHERE title = 'Foundation and Empire'); • We can imagine that the lower half of the query feeds the PUB_CODE of any matching books up to the top level. Section 3 SQL Overview 37
INSERT • • Insert an entry into a table Syntax: – insert into "tablename“ – (first_column, . . . last_column) values – (first_value, . . . last_value); Example of Insert into students (student. ID, first. Name, last. Name) values('u 63773941', 'Tarik', 'Borogovac') Section 3 SQL Overview 38
CREATE TRIGGER • A trigger is a set of SQL code that is automatically executed whenever an associated action is performed Example: • To have the entire table outputted whenever we insert an entry Create Trigger disp. All. Entries on students for insert as select * from students Section 3 SQL Overview 39
DELETE • Used to delete a record matching certain criteria • Syntax: delete from "tablename“ where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; Example of delete from students where last. Name = ‘Campbell' Section 3 SQL Overview 40
UPDATE • • Used to change a record. Syntax: update "tablename" set "columnname" = "newvalue" [, "nextcolumn" = "newvalue 2". . . ] where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; Example of Update update students set first. Name = 'darryn', last. Name='campbell' where student. ID='u 63773941' Section 3 SQL Overview 41
FURTHER READING • Review the attached set of SQL notes over the next four weeks to reinforce your SQL skills. – Data Models are converted to schemas which are readable by the database management system. – Notation standardised through SQL and XML – Schemas describe data. – Described as tables (relations) for relational databases. – Definition of Columns of tables based on domains (sets of valid values). – Metadata describes data. Schemas form part of the metadata for a system. Key Relational and SQL Concepts (part 1 of 3) SQL_Data_Definitio n SQL Sub Queries Section 3 Key Relational and SQL Concepts (part 2 of 3) Domains Metadata SQL Groups SQL Overview Key Relational and SQL Concepts (part 3 of 3) XML SQL Data Definition Views 42
End of Data Manipulation Language Section 3 SQL Overview 43
Скачать презентацию And Franchise Colleges HSQ — DATABASES SQL
6e7b49211ef62bdcbb187afca15d379b.ppt