1 / 30

Structured Query Language (SQL)

Structured Query Language (SQL). Structured Query Language (SQL). SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute),

mora
Télécharger la présentation

Structured Query Language (SQL)

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. Structured Query Language (SQL)

  2. Structured Query Language (SQL) • SQL (pronounced "ess-que-el") stands for Structured Query Language. • SQL is used to communicate with a database. According to ANSI (American National Standards Institute), • it is the standard language for relational database management systems. • SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database

  3. Structured Query Language (SQL) • Common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. • most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. • Standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database

  4. History of SQL • “Sequel” project was initiated by IBM Research Lab in San Jose : 1974-79 • First RDBMS to implement SQL: SQL/DS • Other products: INGRES, Oracle, Sybase, Informix • ISO and ANSI ratified SQL92 standard -> SQL3 • Implemented in both mainframe and client/server

  5. Benefits of a Standardized Relational Language • Reduced training costs. • Productivity. • Application portability. • Application longevity. • Reduced dependence on a single vendor. • Cross-system communication.

  6. Terminology • Data Definition Language (DDL): • Commands that define a database, including creating, altering, and dropping tables and establishing constraints. • Data Manipulation Language (DML) • Commands that maintain and query a database. • Data Control Language (DCL) • Commands that control a database, including administering privileges and committing data.

  7. Simple SELECT statements • query the database and retrieve selected data that match the criteria that you specify. • format of a simple select statement: select "column1"[,"column2",etc] from "tablename" • [where "condition"]; [] = optional

  8. Select Command Description • The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns. • *The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results. • *The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

  9. Conditional selections used in where clause: • = Equal • > Greater than • < Less than • >= Greater than or equal to • <= Less than or equal to • <> Not equal to • LIKE

  10. Like • Pattern matching operator can also be used in the conditional selection of the where clause. • Selects only rows that are "like" what you specify. • Percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example: • Select first, last, city from empinfo • where first LIKE 'Er%'; • This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes.

  11. Simple Select Retrieve the record for Team 20. SELECT * FROM TEAMS WHERE TEAMNUM=20; 20 Yankees New York Simpson

  12. Defining a Database • CREATE DATABASE databasename • CREATE TABLE tablename (column_name datatype [NULL|NOT NULL][column_contraint_clause]... column_name datatype [NULL|NOT NULL][column_contraint_clause]);

  13. Constraint Clause • CONSTRAINT constraint_name [PRIMARY KEY primary_key|FOREIGN KEY foreign_key REFERENCES primary_key] • Establishes referential integrity between two tables

  14. Modifying Table Structure • ALTER TABLE table_name ADD (column_name data_type) … (column_name data_type) ) • Removing Table: DROP TABLE table_name

  15. Inserting, Updating, and Deleting Data • INSERT INTO table_name VALUES (column1_value, column2_value … ) • To delete all rows: DELETE FROM table_name • UPDATE table_name SET column_name = new_column_value

  16. Processing operations • Selection: selection of a subset of rows in table(s) • Projection: selection of a subset of columns from table(s) • Join: joining of columns from 2 or more tables based on a certain condition • Result of the above operations is a “relation”

  17. Processing Single Tables • SELECT: list of columns from base tables to be projected in to the result table • FROM: identifies the table from which columns will be chosen to appear in the result table • WHERE: includes the conditions for row selection within a singe table • conditions can be simple or a combination

  18. Retrieve Table Retrieve the entire Teams table. SELECT * FROM TEAMS; 12 Dodgers Los Angeles Wilson 15 Giants San Francisco Johnson 20 Yankees New York Simpson 24 Tigers Detroit Corbin

  19. Simple Project Find the numbers and names of all of the teams. SELECT TEAMNUM, TEAMNAME FROM TEAMS; 12 Dodgers 15 Giants 20 Yankees 24 Tigers

  20. Combination of Conditions Which players, over 27 years old, have player numbers of at least 1000? SELECT PLAYNUM, PLAYNAME FROM PLAYERS WHERE AGE>27 AND PLAYNUM>=1000; 1131 Johnson 5410 Smith 8366 Gomez

  21. ANDs and ORs Which players are over 30 years old or are less than 22 years old and have a player number less than 2000? SELECT * FROM PLAYERS WHERE AGE>30 OR (AGE<22 AND PLAYNUM<2000); 358 Stevens 21 523 Doe 32 8366 Gomez 33

  22. Between Which players are between 25 and 27 years old? SELECT PLAYNUM, PLAYNAME FROM PLAYERS WHERE AGE BETWEEN 25 AND 27; 1779 Jones 2007 Dobbs 4280 Cohen 5410 Smith

  23. Using Expressions • In the selection conditions, expressions (muliplication/addition) or stored functions (sum, average, min or max) can be used • What is the total values of product in inventory? • SELECT product_name, unit_price, on_hand, unit_price*on_hand AS value FROM product_t

  24. Using Function • How many different items were ordered on order number 1004? • SELECT COUNT(*) FROM product_order_t WHERE order_id = 1004

  25. String Comparison Which teams are based in Detroit? SELECT TEAMNUM, TEAMNAME FROM TEAMS WHERE TEAMNAME=‘Detroit’; 24 Tigers

  26. Like Find all of the players whose last names begin with “S”. (use of wildcard) SELECT PLAYNUM, PLAYNAME FROM PLAYERS WHERE PLAYNAME LIKE ‘S%’; 358 Stevens 5410 Smith 8093 Smith

  27. Distinct To list unique values List the names of the companies that manufacture bats for the league. SELECT DISTINCT MANUF FROM BATS; Acme General United Modern

  28. IN and NOT IN Lists • List all customers who live in Eastern States • SELECT customer_name, city, state FROM customer_t WHERE state IN (‘NY’, ‘NJ’, ‘MD’, ‘DC’, ‘CT’)

  29. Processing Multiple Tables • Multiple tables are related by a JOIN operation using foreign key referencing of primary key • FROM: list multiple table • WHERE: foreign_key = primary_key and other conditions for join

  30. Simple Join • What are the names of customers who placed orders? • SELECT customer_t.customer_name FROM customer_t, order_t WHERE customer_t.customerid = order_t.customerid

More Related